SAP HANA / SAP S/4HANA

This example demonstrates how to use the SAPHANAConnector in ydata-sdk to read from and write to SAP HANA, including the database underneath SAP S/4HANA.

Installation

This connector needs the saphana flavour:

pip install "ydata-sdk[saphana]"

It installs hdbcli, SAP's official HANA client, which is distributed under the SAP Developer License rather than an open-source license.

The connector talks SQL to the HANA database directly, so it can read any table, CDS view or calculation view the database user has access to. SAP application-level authorizations don't apply at this layer: grant the database user only the privileges it needs.

Connecting

Field	Description
hostname	The HANA host.
port	For a multitenant system, the system database's SQL port, 3<instance>13 (e.g. 30013 for instance 00). You can also connect straight to a tenant's own SQL port.
database	The tenant database to connect to, e.g. S4H. Leave it out when connecting to a tenant's own port.
schema	The default schema. On S/4HANA, the schema that holds the ABAP tables: SAPHANADB on a standard install (older systems may use SAPABAP1 or SAP<SID>).

Connections are TLS-encrypted by default, as S/4HANA 2023 requires. Server certificates are not validated by default, because on-premise systems often use self-signed certificates: pass validate_certificate=True when the certificate is CA-issued (HANA Cloud and most production systems), and encrypt=False only for a system without TLS.

Working with S/4HANA

• Reflect only the tables you need. An S/4HANA schema holds on the order of 100k tables. get_database_schema() refuses to reflect more than 500 at once, so pass a subset with get_database_schema(tables=[...]). For the same reason, build a MultiDataset from a reflected subset instead of calling read_database().
• Table relationships are not inferred. S/4HANA doesn't declare foreign keys in the database, so a MultiDataset built from it has no relationships between tables.
• Dates are strings. ABAP date (DATS) and time (TIMS) columns are stored as "YYYYMMDD" and "HHMMSS" text, with "00000000" for an empty date. They are read as strings; convert them where you need real dates.
• Write to your own schema. Never write into the ABAP schema, which SAP manages.

Example Code

"""Example of the SAP HANA connector, reading from an SAP S/4HANA system."""
import pandas as pd

from ydata.connectors import SAPHANAConnector
from ydata.dataset import MultiDataset

if __name__ == "__main__":

    USERNAME = "insert-username"
    PASSWORD = "insert-password"
    HOSTNAME = "insert-hostname"
    # Multitenant systems: the system database's SQL port, 3<instance>13.
    PORT = 30013
    # The tenant database to route the session to.
    TENANT_DATABASE = "S4H"
    # The schema that holds the ABAP tables. SAPHANADB on a standard install.
    ABAP_SCHEMA = "SAPHANADB"

    conn_str = {
        "hostname": HOSTNAME,
        "username": USERNAME,
        "password": PASSWORD,
        "port": PORT,
        "database": TENANT_DATABASE,
        "schema": ABAP_SCHEMA,
    }

    # Connections are TLS-encrypted by default. Validate the server certificate
    # when it is CA-issued (HANA Cloud, most production systems).
    conn = SAPHANAConnector(conn_string=conn_str, validate_certificate=True)

    # Test the connection
    conn.test()

    # An S/4HANA schema holds ~100k tables, so reflect only the ones you need.
    # Calling get_database_schema() without `tables` raises on a schema this size.
    schema = conn.get_database_schema(tables=["MARA", "MAKT"])
    print(schema)

    # Read a whole table: the material master
    materials = conn.get_table(table="MARA")
    print(materials.to_pandas().head())

    # Read a sample of a large table
    customers = conn.get_table_sample(table="KNA1", sample_size=1000)
    print(customers.to_pandas().head())

    # Read a query: CDS views and joins work like any other HANA SQL
    sales_items = conn.query(query=f"""
        SELECT h.VBELN, h.ERDAT, h.KUNNR, i.POSNR, i.MATNR, i.KWMENG, i.NETWR
        FROM {ABAP_SCHEMA}.VBAK AS h
        JOIN {ABAP_SCHEMA}.VBAP AS i ON i.MANDT = h.MANDT AND i.VBELN = h.VBELN
    """)

    # ABAP dates (DATS) are read as "YYYYMMDD" strings, with "00000000" for
    # an empty date. Convert them explicitly where you need real dates.
    sales_items_df = sales_items.to_pandas()
    sales_items_df["ERDAT"] = pd.to_datetime(sales_items_df["ERDAT"].replace("00000000", None),
                                             format="%Y%m%d")

    # Read a sample of a query
    sample = conn.query_sample(query=f"SELECT * FROM {ABAP_SCHEMA}.VBAK", sample_size=1000)

    # Read several tables at once
    tables = conn.get_tables(tables=["VBAK", "VBAP"])
    print({name: dataset.shape for name, dataset in tables.items()})

    # Build a MultiDataset (e.g. for the MultiTableSynthesizer) from a subset of
    # tables. read_database() would try to reflect the whole schema, so pass
    # the reflected subset as the schema instead.
    sales = MultiDataset(connector=conn, schema=conn.get_database_schema(tables=["VBAK", "VBAP"]))
    print(sales)

    # Write a Dataset to a table. Write to a schema of your own: never write
    # into the ABAP schema, which SAP manages.
    conn.write_table(data=sample,
                     name="SALES_ORDERS_SAMPLE",
                     schema_name="SYNTHETIC_DATA",
                     if_exists="replace")