Streaming Data to SAP HANA

The SAP HANA Output adapter uses multiple parallel ODBC connections to load information from Event Stream Processor into the SAP HANA server. On UNIX platforms, only 64-bit ODBC drivers and driver managers are supported.

Prerequisites

Before you run the adapter:

Install SAP HANA SPS 07 or higher.
Install an SAP HANA ODBC client. SAP recommends that you use the latest version of the SAP HANA ODBC client available, but it must be at least version 1.0.73. Install the same bit version of the client and driver as your Event Stream Processor installation. For example, if you install a 32bit version of ESP, also install a 32bit version of the HANA ODBC client and driver.
Install an ODBC driver manager.
If Event Stream Processor needs to pass multibyte characters to the SAP HANA database, specify the CHAR_AS_UTF8=1 property. For Windows, specify this property using the HANA ODBC driver advanced setup. In Linux, edit your .odbc.ini file to include this property.
If you are running on UNIX, use unixODBC 2.3.1 or higher:
- For version 2.3.1, create a symbolic link under <2.3.1 installation folder>/lib as follows:
```
ln -s libodbc.so.2.0.0 libodbc.so.1
```
  This link is required because ESP links to libodbc.so.1, which unixODBC 2.3.1 has renamed libodbc.so.2. With the link, ESP will now use libodbc.so.2.
In a Linux environment, specify the SAP HANA client libraries in your LD_LIBRARY_PATH.
To connect to SAP HANA through SSL on Solaris, use OpenSSL 1.0.1i:
- Export LD_LIBRARY_PATH_64=[open SSL installation]/lib:$STREAMING_HOME/lib:[unixODBC installation]/lib:$LD_LIBRARY_PATH_64
Note If the HANA database to which you are writing is on a HANA cluster, refer the to Configuring to Support HANA Failover in the SAP Event Stream Processor: Configuration and Administration Guide for details on how to add the other nodes in a HANA cluster to the database service definition.

Make sure that the Data Service has the ODBC Driver Library set correctly. Always use streamingdbodbc_lib for Windows (regardless of version). On other platforms, use streamingdbodbc64_lib if you are working with the SAP HANA adapter. See SAP Event Stream Processor: Configuration and Administration Guide for more information.

Using the Adapter

When the adapter gets a request to shutdown, it first processes the data it received before that request. The adapter gets the shutdown signal either when the project is stopping, the ESP server is shutting down, or the adapter itself is stopped. Because the amount of time it takes for the adapter to shutdown depends on the amount of data being processed, the adapter may appear to be slow shutting down.

To optimize performance, send consecutive UPDATES, DELETES, or INSERTS whenever possible. Switching between opcodes (INSERT, DELETE, UPDATE) is computationally expensive and the adapter can only accumulate rows with the same opcode in any batch. As soon as the opcode changes, the previous batch is committed to the database and a new batch is started, even if the previous batch is not full. Batching for UPDATES is done on a single thread to preserve the order of operations. This is important if multiple UPDATES are done for the same row.

Rows

Some helpful information about different types of rows in Event Stream Processor.

Row Type	Definition
Posted Row	A row that the ESP engine passes to the adapter.
Skipped Row	A row that the adapter ignores because the opcode does not match the database warehouse mode. See parameters below for accepted database warehouse modes.
Processed Row	A row that is posted and is not skipped, sitting in an internal buffer waiting to be written to a file, or sitting in a partially written file waiting to be loaded.
Bad Row	A row that is passed to the adapter but cannot be converted into the format required for writing it to the file. For example, if the range of a numeric value does not fit into the type of the database column. A bad row can also be a row that was not loaded because it was contained in a file that failed to load. If a file fails to load, the number of bad rows is incremented by the number of rows in the file.

Property Label	Description
Database Service Name	Property ID: service Type: `string` (Required for adapter operation and schema discovery) The name of the database service that represents the SAP HANA database into which information will be loaded. You must define an ODBC service for the database service. See the SAP Event Stream Processor: Configuration and Administration Guide for more information. No default value.
Target Database Table Name	Property ID: table Type: `tables` (Required for adapter operation; optional only if you are using schema discovery) A string value representing the name of the SAP HANA database table into which the adapter loads data. No default value.
Target Database Schema Name	Property ID: sourceSchema Type: `string` (Optional; required when you attach adapters to ESP windows manually rather than using schema discovery) Specify the database schema that contains the table into which you want to load data.
Field Mapping	Property ID: permutation Type: `permutation` (Advanced) Mapping between the Event Stream Processor schema columns and SAP HANA table columns. If no permutation is specified, the Event Stream Processor schema columns must match the SAP HANA table schema exactly (same column names, order, and count). The table and column names are case sensitive so to preserve original casing, use quotations. For example: create table Table1 (Col1 integer) create table "Table1" ("Col1" integer) creates two separate tables and can be accessed using these parameter values: ...properties table='TABLE1', permutation='StreamingCol1=COL1'; ...properties table='Table1', permutation='StreamingCol1=Col1'; If the Data Warehouse Mode property is OFF, and you want to specify a permutation, include a mapping for at least one column of the primary key of the ESP window attached to the adapter. Without mapping at least one primary key column, the adapter fails to start. The format for this property is: <streaming_columnname>=<database_columnname>:<streaming_columnname>=<database_columnname> Use a colon to separate mappings. No default value.
Bulk Batch Size	Property ID: bulkBatchSize Type: `integer` (Advanced) Number of rows that can be inserted, deleted, or updated in a database table before a commit occurs. When reconnecting, the adapter only sends the last number of rows specified in the bulkInsertArraySize parameter. To avoid data loss on reconnect, set bulkBatchSize to the same value as bulkInsertArraySize.
Bulk Insert Array Size	Property ID: bulkInsertArraySize Type: `integer` (Advanced) Number of rows simultaneously inserted, updated, or deleted from a database table. This option must be a divisor of the bulkBatchSize property.
Idle Buffer Write Delay in Msec	Property ID: idleBufferWriteDelayMSec Type: `integer` (Advanced) Specifies the number of milliseconds that a database table may sit idle with uncommitted data available for insert, update, or delete. Default value is 1000.
Buffer Age Limit in Msec	Property ID: bufferAgeLimitMSec Type: `integer` (Advanced) Forces the loading of any data that has been in existence longer than the time limit. Specify a value in milliseconds between 1 and 65535. Default value is 10000. This is applicable for inserts, updates, and deletes.
Delay Between Reconnection Attempts	Property ID: reconnectAttemptDelayMSec Type: `int` (Advanced) Number of milliseconds between attempts to reconnect to the SAP HANA server. Default value is 1000.
Maximum Number of Reconnection Attempts	Property ID: maxReconnectAttempts Type: `int` (Advanced) Number of attempts at reconnecting to the SAP HANA server before stopping. Use -1 to retry an unlimited number of times. Default value is 1.
Data Warehouse Mode	Property ID: dataWarehouseMode Type: `choice` (Advanced) Specifies the type of data warehousing mode the adapter uses. Valid values are: ON updates are converted to inserts, and deletes are ignored. INSERTONLY only inserts are processed, and updates and deletes are ignored. OFF (default) all inserts, updates and deletes are processed as such. ARCHIVE updates and deletes become inserts. The opcode must be stored as a column in the database table. See the opcodeColumnName property. This column must be of characters type. The required length for the opcode column is one. Use this mode if you wish to store incoming data as a series of events. If you want to specify a field mapping, or permutation, map at least one column of the primary key of the ESP window attached to the adapter. Without mapping of at least one primary key column, the adapter fails to start.
Maximum Queue Size	Property ID: maxQueueSize Type: `integer` (Advanced) Specifies the maximum size of the internal queue used to buffer incoming ESP rows before they can be loaded into SAP HANA. If the rate of incoming ESP rows is faster than the rate the adapter loads them intoSAP HANA, the queue fills up and the incoming data flow slows down. If you do not specify a value, or specify a value of 0, then the default value (2 * number of DB loading threads * bulkArraySize) is used. Express the maximum queue size as the maximum number of rows to accept in the buffer. The size of the buffer is limited by the machine’s memory capacity and the ESP integer datatype. If the size you enter exceeds your machine’s memory capacity, the adapter might be unable to start. This is applicable to inserts, updates, and deletes.
Timestamp Column Name	Property ID: timestampColumnName Type: `string` (Advanced) If a column name is provided, the time at which the record is added to the bulk array is stored in that column of the database record. The column name for this property cannot be used in the mapping between Event Stream Processor schema columns and SAP HANA table columns (permutation). If this property is empty, there is no timestamp stored. The inserted value will be in UTC time zone. The column must be a timestamp column.
Operation Code Column Name	Property ID: opcodeColumnName Type: `string` (Required if adapter is running in the ARCHIVE data warehouse mode; advanced for other data warehouse modes) The name of the database column that the adapter uses to store the operation code. If specified for other modes, the opcode is written into the specified column.
Only Base Content	Property ID: onlyBase Type:`boolean` (Advanced) If true, the adapter processes only the base data of the window or stream to which it is attached. No further message flow is processed. The default value is false.
Include Base Content	Property ID: outputBase Type: `boolean` (Optional) If set to true, the adapter outputs the initial stream contents in addition to stream updates. If this option is enabled and the adapter is running in GD mode, once the adapter has done a GD commit on the entire base data, the ESP Server does not redeliver the base data on adapter restart and only sends deltas that are saved for delivery. The default value is false.
PropertySet	Property ID: propertyset Type: `string` (Advanced) Specifies the name of the property set. Property sets are reusable sets of properties that are stored in the project configuration file. Using these sets allows you to move adapter configuration properties out of the CCL file and into the CCR file. If you specify the same properties in the project configuration file and the ATTACH ADAPTER statement, the values in the property set override the values defined in the ATTACH ADAPTER statement. No default value.
Timezone For Statistics	Property ID: timezoneForStats Type: `string` (Advanced) Controls the time zone in which midnight statistics for the adapter roll over. For example, if the adapter is left at its default value of GMT, midnight statistics reset at midnight GMT. This setting does not affect any other dates processed or returned by this adapter. The default value is GMT.
Number of Threads	Property ID: threadCount Type: `integer` (Advanced) The number of threads processing incoming data to be loaded into or deleted from the database. For optimal performance, specify a value based on your available hardware resources. Take into consideration the number of available CPU cores you have on the machine running the adapter instance, and sample different values to determine which one produces optimal performance. The default value is 1.
Runs Adapter in GD Mode	Property ID: enableGDMode Type: `boolean` (Advanced) Specifies whether the adapter runs in guaranteed delivery (GD) mode. GD ensures that data continues to be processed in the case that the ESP Server fails, or the destination (third-party server) fails or does not respond for a long time. See Guaranteed Delivery in the SAP Event Stream Processor: Developer Guide for details on enabling GD for your project. The default value is false.
Enable the Adapter Internal Cache Mode	Property ID: enableGDCache Type: `boolean` (Advanced) If set to true, only rows that can be recovered (that is, checkpointed) by the ESP Server on restart are sent to the database. Other rows are cached internally by the adapter. When this option is enabled, you may see a significant increase in latency depending on how frequently the attached stream delivers checkpoint messages. Streams support three modes related to GD: GD not supported, GD supported, and GD supported with checkpoint messages. This setting is ignored if the attached stream does not support GD and does not deliver checkpoint message. The default value is true.
GD Batch Size	Property ID: gdBatchSize Type: `int` (Advanced) Specify after how many message blocks (transactions or envelopes) the adapter issues a commit command to the database and a GD commit to the stream to which the adapter is attached. The default value is 10. Increasing this value improves performance at the expense of latency (specifically, how soon rows are available in the database to be consumed by other applications.) Increasing this value also increases memory consumption in the ESP Server because the uncommitted rows need to be preserved for redelivery in case of failure.
Use SQL UPSERT statement for all INSERT and UPDATE Operations	Property ID: useUpsert Type: `boolean` (Optional) Executes inserts and updates together in the same batch for performance optimization. Recommended only in cases where expected input contains mainly updates with occasional inserts and deletes. If set to false, the adapter uses SQL INSERT for inserts and SQL UPDATE for updates. If set to true, the adapter uses SQL UPSERT to execute, in a single thread, inserts and updates in the same batch. Deletes run on multiple threads if the threadCount property value is >1. The target HANA table must have a primary key defined. Default value is false.

Custom Statistics

The SAP HANA Output adapter maintains statistics to show the status of the adapter and to track its loading activities. Enable the time-granularity option in the project configuration (.ccr) file to get these custom statistics reported by the _ESP_Adapter_Statistics metadata stream:

Rows	Bytes	Average Rows
Received in the last hour Received since midnight Loaded in the last hour Loaded since midnight Skipped* in the last hour Skipped* since midnight Rows in queue	Received since midnight Loaded since midnight Skipped since midnight	Received, per second, over the last minute Received, per second, over the last hour Loaded, per second, over the last minute Loaded, per second, over the last hour

* A row is skipped when the opcode does not match the adapter's current warehousing mode. For example, if the data warehousing mode is INSERTONLY, and the adapter passes in a delete or an update, this results in a skipped row.

Use statistics of rows in queue to monitor the SAP HANA Output adapter for potential bottlenecks and to obtain optimal adapter performance. For example, if you notice that the queue size is often at its maximum value, you can edit values for the adapter's bulkBatchSize, bulkInsertArraySize, and threadCount properties to increase performance. Moreover, you can also consider devoting additional resources to streaming data to the adapter if the queue size is often less than the bulkInsertArraySize.

You can also obtain additional latency statistics through the _ESP_Connectors metadata stream. See the SAP Event Stream Processor: Configuration and Administration Guide for more information.