SAP Hana Cloud

11 min

The SAP Hana Cloud connector writes Litmus Edge (LE) DeviceHub messages into a table in an SAP HANA Cloud database over the SQL endpoint. It also works with an on-premise SAP HANA database. The connector writes rows in batches with the SAP HANA driver's native bulk insert, and uses one transaction for each batch.

The connector also supports Execute Command for ad-hoc SQL queries. Execute Command is read-only by default.

Supported communication

Outbound (write data from LE to the database): Yes

Inbound (read data from the database to LE): No

Execute Command (ad-hoc SQL query): Yes. Read-only by default.

Compatibility

The connector supports the following products. Other products and versions might work.

Product	Version
SAP HANA Cloud	-
SAP HANA Platform	2.0

Connector configuration

Field	Type	Description	Required	Default
Name	String	Name of this connector instance.	Yes	-
Hostname	String	DNS name or IP address of the SQL endpoint, for example <instance-id>.hana.<region>.hanacloud.ondemand.com. The host must be reachable from the LE system. Length is 3 to 255 characters.	Yes	-
Port	Integer	Port of the SQL endpoint. SAP HANA Cloud uses 443. On-premise systems use 3<instance-nr>15, for example 30015. Range is 1 to 65535.	Yes	443
Authentication method	Dropdown	How the connector authenticates: password or x509.	No	password
Username	String	Database user. Applies only when Authentication method is password.	Conditional	-
Password	String (Secret)	Password for the database user. Applies only when Authentication method is password.	Conditional	-
Client Certificate	String (Secret)	PEM client certificate for x509 authentication. Its subject must be mapped to a database user on the instance. Applies only when Authentication method is x509.	Conditional	-
Client Private Key	String (Secret)	PEM private key that matches the client certificate. The key must not be password encrypted. Applies only when Authentication method is x509.	Conditional	-
TLS mode	Dropdown	Transport security: disable, require, or verify-full.	No	verify-full
CA Certificate	String (Secret)	CA certificate that the connector uses to validate the server. Leave it empty to trust the system certificate store, which already covers SAP HANA Cloud. Applies only when TLS mode is verify-full.	No	-
TLS server name	String	Host name to check against the server certificate, instead of Hostname. Use it when you reach the instance through an IP address or a forwarding proxy. Applies only when TLS mode is require or verify-full.	No	-
Tenant database	String	Tenant database to redirect to on a multi-tenant system. Leave it empty for SAP HANA Cloud, where the SQL endpoint already addresses the database.	No	-
Connection timeout (seconds)	Integer	How long the connector waits for one connection attempt, in seconds. This covers the login handshake, the CURRENT_SCHEMA lookup, and each liveness check. Range is 1 to 300.	No	10
Schema	String	Schema that holds the target table. Leave it empty to use the connecting user's CURRENT_SCHEMA.	No	-
Table	String	Target table that the connector writes into. The table must exist in the schema unless you enable Create table.	Yes	-
Mapping	Object	Key-value pairs that map payload data to the columns of your table. The key is a column in your table. The value is a payload field name such as deviceId, the matching default-schema column name such as device_id, or static text with an @ prefix, such as @plant-a. Leave it empty to use the default table schema.	No	-
Create table	Boolean	Create the target table with the default table schema if it doesn't exist. The database user needs CREATE TABLE permission on the schema.	Yes	Disabled
Commit timeout	Integer	Maximum time in milliseconds that one batch insert transaction can take before the connector treats it as failed and retries it. Range is 10 to 65535.	No	30000
Batch timeout	Integer	Maximum time in milliseconds after the previous commit attempt before the connector writes a batch that isn't full. Range is 10 to 65535.	No	500
Max transaction size	Integer	Maximum number of messages in one batch insert. The connector writes a full batch without waiting for Batch timeout. Range is 1 to 65535.	No	500
Bulk insert count	Integer	Number of messages the connector buffers before it writes a batch. A value of 0 uses Max transaction size. Range is 0 to 65535.	No	0
Write mode	Dropdown	When a message counts as delivered: sync or async.	No	sync
Parallel transactions	Integer	Maximum number of batch inserts that run at the same time, each on its own database connection. Values above 1 increase throughput, but the connector can then write rows out of publish order. Range is 1 to 64.	No	1
Parallel Publish Count	Integer	Number of messages the connector publishes at the same time. In sync write mode, this value limits how many rows a batch can collect. Range is 1 to 4000.	No	1000
Allow data modification via Execute Command	Boolean	Allow Execute Command to run statements that change data or the database structure. When disabled, Execute Command accepts only SELECT and WITH statements.	No	Disabled

Additional attributes

Field	Type	Description	Required	Default
Throttling limit	Integer	Maximum outbound messages per second. A value of 0 disables throttling. Minimum is 0.	No	0
Persistent storage	Boolean	Store outbound queue messages on disk. If the connection drops or the system loses power, the messages are retained and sent once the connection is restored.	No	Enabled
Persistent storage size limit	Integer	Maximum disk space in GB used for persisted data. Applies only when Persistent storage is enabled. Minimum is 1.	Conditional	1
Queue Mode	Dropdown	Order in which the connector sends buffered messages. fifo sends the earliest message first. lifo sends the latest message first. Applies only when Persistent storage is enabled.	No	lifo
Enable Logging	Boolean	Write connection and publish error messages to the connector log. Logging can increase CPU usage, so enable it only for debugging.	No	Disabled

Authentication methods

Value	Behavior
password	The connector logs in with a database user name and password. Requires Username and Password.
x509	The connector uses SAP HANA X.509 client certificate authentication. Requires Client Certificate, Client Private Key, and a TLS mode other than disable. The server derives the database user from the certificate's subject mapping.

TLS modes

Value	Behavior
disable	No TLS. The connector sends the login and data in cleartext. This works only with on-premise systems that allow unencrypted connections, because SAP HANA Cloud refuses them.
require	The connector uses TLS but doesn't validate the server certificate.
verify-full	The connector uses TLS and validates the server certificate chain and host name. Use this mode for SAP HANA Cloud.

Identifier case

SAP HANA stores unquoted identifiers in upper case. For example, CREATE TABLE my_table creates a table named MY_TABLE. Enter Schema and Table exactly as the database catalog stores them, which is usually upper case. Mapping keys match column names without regard to case.

Default table schema

When you enable Create table and the target table doesn't exist, the connector creates it with the following schema.

CREATE COLUMN TABLE SCHEMA_NAME.TABLE_NAME
(
    id          BIGINT       NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    record_uuid NVARCHAR(36) NOT NULL,
    arrived_at  TIMESTAMP    NOT NULL,
    device_id   NVARCHAR(64)     NULL,
    register_id NVARCHAR(64)     NULL,
    tag_name    NVARCHAR(64)     NULL,
    datatype    NVARCHAR(32)     NULL,
    value       NCLOB            NULL,
    success     BOOLEAN          NULL,
    PRIMARY KEY (id)
)

The value column is an NCLOB, so it accepts readings of any length. SAP HANA doesn't allow LOB columns in ORDER BY, GROUP BY, SELECT DISTINCT, or comparison predicates. If you need to filter or sort on the value, create the table yourself with an NVARCHAR(n) value column, and use Mapping. For mapping examples, see the integration guide.

Write modes

Write mode	Behavior
sync	The connector acknowledges a message only after it commits the batch that holds the message. If the database is unreachable, the message stays in persistent storage, and the connector writes it after it reconnects.
async	The connector acknowledges a message once it buffers it. If a batch fails to write, the connector drops it and counts the messages as dropped.

Caution: In async write mode, or with Persistent storage disabled, you can lose data during a database outage. Use async only when throughput matters more than delivery.

Execute Command

Use the IT Hub ProcessorIT Hub Processor to run an ad-hoc SQL query through the connector. The connector streams the result back one message per row, on the response topic supplied in the request.

By default, Execute Command accepts only SELECT and WITH statements, and rejects other statements before they reach the database. To allow statements that change data, enable Allow data modification via Execute Command.

Caution: When you enable Allow data modification via Execute Command, the connector runs each statement as supplied and reports writes as OK with no row count. The database user's privileges are the only limit on what a statement can do, so grant that user only the privileges your deployment needs.

Add a ConnectorAdd a Connector

Manage ConnectorsManage Connectors

IT Hub ProcessorIT Hub Processor