All Products
Search
Document Center

DataWorks:SAP HANA data source

Last Updated:Nov 16, 2023

DataWorks provides SAP HANA Reader and SAP HANA Writer for you to read data from and write data to SAP HANA data sources. This topic describes the capabilities of synchronizing data from or to SAP HANA data sources.

Limits

Data type mappings

The following table lists the data type mappings based on which SAP HANA Reader converts data types.

Category

SAP HANA data type

Integer

INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT

Floating point

FLOAT, DOUBLE, and DECIMAL

String

VARCHAR, CHAR, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT

Date and time

DATE, DATETIME, TIMESTAMP, TIME, and YEAR

Boolean

BIT and BOOLEAN

Binary

TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB, and VARBINARY

Important
  • Data types that are not listed in the preceding table are not supported.

  • SAP HANA Reader processes TINYINT(1) as an integer data type.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the Reader and Writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the Reader and Writer in the code editor.

Code for SAP HANA Reader

  • Configure a synchronization task to read data from a table that is not sharded

    {
        "type":"job",
        "version":"2.0",// The version number. 
        "steps":[
            {
                "stepType":"saphana",// The plug-in name. 
                "parameter":{
                    "column":[// The names of the columns. 
                        "id"
                    ],
                    "connection":[
                        {   "querySql":["select a,b from join1 c join join2 d on c.id = d.id;"], // The SQL statement that is used to read data from the source table. 
                            "datasource":"",// The name of the data source. 
                            "table":[// The name of the source table. The table name must be enclosed in brackets []. 
                                "xxx"
                            ]
                        }
                    ],
                    "where":"",// The WHERE clause. 
                    "splitPk":"", // The shard key. 
                    "encoding":"UTF-8"// The encoding format. 
                },
                "name":"Reader",
                "category":"reader"
            },
            {
                "stepType":"stream",
                "parameter":{},
                "name":"Writer",
                "category":"writer"
            }
        ],
        "setting":{
            "errorLimit":{
                "record":"0"// The maximum number of dirty data records allowed. 
            },
            "speed":{
                "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
                "concurrent":1, // The maximum number of parallel threads. 
                "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
            }
        },
        "order":{
            "hops":[
                {
                    "from":"Reader",
                    "to":"Writer"
                }
            ]
        }
    }
  • Configure a synchronization task to read data from SAP HANA tables in sharded databases

    Note

    When you configure a synchronization task to read data from SAP HANA tables in sharded databases, you can select multiple SAP HANA tables that have the same schema.

    {
        "type": "job",
        "version": "1.0",
        "configuration": {
            "reader": {
                "plugin": "saphana",
                "parameter": {
                    "connection": [
                        {
                            "table": [
                                "tbl1",
                                "tbl2",
                                "tbl3"
                            ],
                            "datasource": "datasourceName1"
                        },
                        {
                            "table": [
                                "tbl4",
                                "tbl5",
                                "tbl6"
                            ],
                            "datasource": "datasourceName2"
                        }
                    ],
                    "singleOrMulti": "multi",
                    "splitPk": "db_id",
                    "column": [
                        "id", "name", "age"
                    ],
                    "where": "1 < id and id < 100"
                }
            },
            "writer": {            
            }
        }
    }

Parameters in code for SAP HANA Reader

Parameter

Description

username

The username that is used to log on to the SAP HANA database.

password

The password that is used to log on to the SAP HANA database.

column

The names of the columns from which you want to read data. To read data from all the columns in the source table, set this parameter to an asterisk (*).

Note

If a column name that you want to specify contains forward slashes (/), you must specify the column name in the format of \"your_column_name\" for escaping. For example, if the column name is /abc/efg, it must be specified as \"/abc/efg\".

table

The name of the table from which you want to read data.

jdbcUrl

The JDBC URL that is used to connect to SAP HANA. Example: jdbc:sap://127.0.0.1:30215?currentschema=TEST.

splitPk

The field that is used for data sharding when SAP HANA Reader reads data. If you configure this parameter, the source table is sharded based on the value of this parameter. Data Integration then runs parallel threads to read data.

You can specify a field of an integer data type for the splitPk parameter. If the source table does not contain fields of integer data types, you can leave this parameter empty.

Code for RestAPI Writer

In the following code, a synchronization task is configured to write data to an SAP HANA database:

{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"saphana",// The plug-in name. 
            "parameter":{
                "postSql":[],// The SQL statement that you want to execute after the synchronization task is run. 
                "datasource":"",// The name of the data source. 
                "column":[// The names of the columns. 
                    "id",
                    "value"
                ],
                "batchSize":1024,// The number of data records to write at a time. 
                "table":"",// The name of the table to which you want to write data. 
                "preSql":[
                     "delete from XXX;" // The SQL statement that you want to execute before the synchronization task is run. 
                   ]
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{// The maximum number of dirty data records allowed. 
            "record":"0"
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Parameters in code for SAP HANA Writer

Parameter

Description

Required

Default value

datasource

The name of the data source. It must be the same as the name of the added data source. You can add data sources by using the code editor.

Yes

No default value

table

The name of the table to which you want to write data.

Yes

No default value

column

The names of the columns to which you want to write data. Separate the names with commas (,), such as "column": ["id", "name", "age"].

If you want to write data to all the columns in the destination table, set this parameter to an asterisk (*), such as "column":["*"].

Note

If a source column name that you specify contains forward slashes (/), you must escape the column name in the format of \"your_column_name\" by using backslashes (\). For example, if the column name is /abc/efg, it must be escaped as \"/abc/efg\".

Yes

No default value

preSql

The SQL statement that you want to execute before the synchronization task is run. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. For example, you can set this parameter to the following SQL statement that is used to delete outdated data:

truncate table tablename

Note

If you specify multiple SQL statements, whether all the statements can be successfully executed cannot be ensured.

No

No default value

postSql

The SQL statement that you want to execute after the synchronization task is run. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. For example, you can set this parameter to the alter table tablenameadd colname timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP SQL statement that is used to add a timestamp.

No

No default value

batchSize

The number of data records to write at a time. Set this parameter to an appropriate value based on your business requirements. This greatly reduces the interactions between Data Integration and SAP HANA and increases throughput. If you set this parameter to an excessively large value, an out of memory (OOM) error may occur during data synchronization.

No

1024