Copy data from SAP HANA using Azure Data Factory

This article outlines how to use the Copy Activity in Azure Data Factory to copy data from an SAP HANA database. It builds on the copy activity overview article that presents a general overview of copy activity.

Tip

To learn ADF's overall support on SAP data integration scenario, see SAP data integration using Azure Data Factory whitepaper with detailed introduction, comparsion and guidance.

Supported capabilities

This SAP HANA connector is supported for the following activities:

You can copy data from SAP HANA database to any supported sink data store. For a list of data stores supported as sources/sinks by the copy activity, see the Supported data stores table.

Specifically, this SAP HANA connector supports:

  • Copying data from any version of SAP HANA database.
  • Copying data from HANA information models (such as Analytic and Calculation views) and Row/Column tables.
  • Copying data using Basic or Windows authentication.

Tip

To copy data into SAP HANA data store, use generic ODBC connector. See SAP HANA sink with details. Note the linked services for SAP HANA connector and ODBC connector are with different type thus cannot be reused.

Prerequisites

To use this SAP HANA connector, you need to:

  • Set up a Self-hosted Integration Runtime. See Self-hosted Integration Runtime article for details.
  • Install the SAP HANA ODBC driver on the Integration Runtime machine. You can download the SAP HANA ODBC driver from the SAP Software Download Center. Search with the keyword SAP HANA CLIENT for Windows.

Getting started

You can use one of the following tools or SDKs to use the copy activity with a pipeline. Select a link for step-by-step instructions:

The following sections provide details about properties that are used to define Data Factory entities specific to SAP HANA connector.

Linked service properties

The following properties are supported for SAP HANA linked service:

Property Description Required
type The type property must be set to: SapHana Yes
connectionString Specify information that's needed to connect to the SAP HANA by using either basic authentication or Windows authentication. Refer to the following samples.
In connection string, server/port is mandatory (default port is 30015), and username and password is mandatory when using basic authentication. For additional advanced settings, refer to SAP HANA ODBC Connection Properties
You can also put password in Azure Key Vault and pull the password configuration out of the connection string. Refer to Store credentials in Azure Key Vault article with more details.
Yes
userName Specify user name when using Windows authentication. Example: user@domain.com No
password Specify password for the user account. Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. No
connectVia The Integration Runtime to be used to connect to the data store. A Self-hosted Integration Runtime is required as mentioned in Prerequisites. Yes

Example: use basic authentication

{
    "name": "SapHanaLinkedService",
    "properties": {
        "type": "SapHana",
        "typeProperties": {
            "connectionString": "SERVERNODE=<server>:<port (optional)>;UID=<userName>;PWD=<Password>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: use Windows authentication

{
    "name": "SapHanaLinkedService",
    "properties": {
        "type": "SapHana",
        "typeProperties": {
            "connectionString": "SERVERNODE=<server>:<port (optional)>;",
            "userName": "<username>", 
            "password": { 
                "type": "SecureString", 
                "value": "<password>" 
            } 
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

If you were using SAP HANA linked service with the following payload, it is still supported as-is, while you are suggested to use the new one going forward.

Example:

{
    "name": "SapHanaLinkedService",
    "properties": {
        "type": "SapHana",
        "typeProperties": {
            "server": "<server>:<port (optional)>",
            "authenticationType": "Basic",
            "userName": "<username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

For a full list of sections and properties available for defining datasets, see the datasets article. This section provides a list of properties supported by SAP HANA dataset.

To copy data from SAP HANA, the following properties are supported:

Property Description Required
type The type property of the dataset must be set to: SapHanaTable Yes
schema Name of the schema in the SAP HANA database. No (if "query" in activity source is specified)
table Name of the table in the SAP HANA database. No (if "query" in activity source is specified)

Example:

{
    "name": "SAPHANADataset",
    "properties": {
        "type": "SapHanaTable",
        "typeProperties": {
            "schema": "<schema name>",
            "table": "<table name>"
        },
        "schema": [],
        "linkedServiceName": {
            "referenceName": "<SAP HANA linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}

If you were using RelationalTable typed dataset, it is still supported as-is, while you are suggested to use the new one going forward.

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by SAP HANA source.

SAP HANA as source

To copy data from SAP HANA, the following properties are supported in the copy activity source section:

Property Description Required
type The type property of the copy activity source must be set to: SapHanaSource Yes
query Specifies the SQL query to read data from the SAP HANA instance. Yes
packetSize Specifies the network packet size (in Kilobytes) to split data to multiple blocks. If you have large amount of data to copy, increasing packet size can increase reading speed from SAP HANA in most cases. Performance testing is recommended when adjusting the packet size. No.
Default value is 2048 (2MB).

Example:

"activities":[
    {
        "name": "CopyFromSAPHANA",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SAP HANA input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SapHanaSource",
                "query": "<SQL query for SAP HANA>"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

If you were using RelationalSource typed copy source, it is still supported as-is, while you are suggested to use the new one going forward.

Data type mapping for SAP HANA

When copying data from SAP HANA, the following mappings are used from SAP HANA data types to Azure Data Factory interim data types. See Schema and data type mappings to learn about how copy activity maps the source schema and data type to the sink.

SAP HANA data type Data factory interim data type
ALPHANUM String
BIGINT Int64
BINARY Byte[]
BINTEXT String
BLOB Byte[]
BOOL Byte
CLOB String
DATE DateTime
DECIMAL Decimal
DOUBLE Double
FLOAT Double
INTEGER Int32
NCLOB String
NVARCHAR String
REAL Single
SECONDDATE DateTime
SHORTTEXT String
SMALLDECIMAL Decimal
SMALLINT Int16
STGEOMETRYTYPE Byte[]
STPOINTTYPE Byte[]
TEXT String
TIME TimeSpan
TINYINT Byte
VARCHAR String
TIMESTAMP DateTime
VARBINARY Byte[]

Lookup activity properties

To learn details about the properties, check Lookup activity.

Next steps

For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see supported data stores.