Skip to content

Latest commit

 

History

History
133 lines (95 loc) · 12.6 KB

README.md

File metadata and controls

133 lines (95 loc) · 12.6 KB
Raw

conduit-connector-sap-hana

General

The SAP HANA connector is one of Conduit plugins. It provides SAP HANA source and destination connectors.

Prerequisites

How to build it

Run make build.

Testing

Run make test to run all the unit and integration tests.

Source

The SAP HANA source connects to the database using the provided connection and starts creating records for each table row and each detected change. It supports getting a snapshot from the table and detects CDC (Change Data Captured) changes.

Configuration options

Name Description Required Example By default
table The name of a table in the database that the connector should read from. true users
orderingColumn The name of a column that the connector will use for ordering rows. Its values must be unique and suitable for sorting, otherwise, the snapshot won't work correctly. true id
primaryKeys Comma separated list of column names that records could use for their Key fields. By default connector uses primary keys from table, if these don't exist, the connector will use orderingColumn. false id
snapshot Whether or not to take a snapshot of the entire table before starting cdc mode, default value is true. false false
batchSize Size of rows batch. false 100 1000
auth.mechanism Mechanism type of auth. Valid types: DSN, Basic, JWT, X509. false JWT DSN
auth.dsn DSN connection string Required for DSN auth type. hdb://user:password@host443?TLSServerName=name
auth.host Sap Hana database host. Required for Basic, JWT, X509 auth types. hdb://hanacloud.ondemand.com:443
auth.username Sap Hana user Required for Basic type. hbadmin
auth.password Sap Hana password Required for Basic type. pass
auth.token JWT token Required for JWT type. jwt_token
auth.clientCertFilePath Path for certification file Required for X509 type. /tmp/file.cert
auth.clientKeyFilePath Path for key file Required for X509 type. /tmp/key.cert

Snapshot

By default, when the connector starts for the first time, snapshot mode is enabled, which means that existing data will be read. To skip reading existing data, change config parameter snapshot to false. All rows which exist in a table at the time the snapshot started, are considered part of the snapshot. When all snapshot records are returned, the connector switches to listening for CDC changes.

Change Data Capture (CDC)

This connector implements CDC features for DB2 by adding a tracking table and triggers to populate it. The tracking table has the same name as a target table with the prefix CONDUIT_, and the suffix of the time when the pipeline started, with the format "hhmmss". For example for table PRODUCTS the tracking table's name will look like CONDUIT_PRODUCTS_213315. The tracking table has the same columns as the target table plus two additional columns:

name description
CONDUIT_TRACKING_ID Autoincrement index for the position.
CONDUIT_OPERATION_TYPE Operation type: INSERT, UPDATE, or DELETE.

The connector saves information about update, delete, insert table operations inside tracking table. For example if user inserts a new row into table, the connector will save all new columns values inside the tracking table
with CONDUIT_OPERATION_TYPE = INSERT

Triggers have a name pattern of CD_{{TABLENAME}}_{{OPERATION_TYPE}}_{{SUFFIXNAME}}. For example: CD_PRODUCTS_INSERT_213315

Queries to retrieve CDC from a tracking table are very similar to queries in a Snapshot iterator, but with CONDUIT_TRACKING_ID ordering column.

The connector cleans up the tracking table every 5 seconds.

Iterator saves the last CONDUIT_TRACKING_ID to the position from the last successfully recorded row.

If connector stops, it will parse position from the last record and will try to get row where {{CONDUIT_TRACKING_ID}} > {{position.CDCLastID}}.

Please pay attention

The tracking table and the triggers are not automatically removed when a pipeline is deleted. That needs to be done manually, for example by using the following commands:

  DROP TABLE CONDUIT_{{YOUR_TABLE_NAME}}_{{SUFFIXNAME}};
  DROP TRIGGER CD_{{TABLENAME}}_INSERT_{{SUFFIXNAME}};
  DROP TRIGGER CD_{{TABLENAME}}_UPDATE_{{SUFFIXNAME}};
  DROP TRIGGER CD_{{TABLENAME}}_DELETE_{{SUFFIXNAME}};

CDC FAQ

Is it possible to add/remove/rename column to table?

Yes. You have to stop the pipeline and do the same changes with the conduit tracking table. For example:

ALTER TABLE CLIENTS
ADD COLUMN phone VARCHAR(18);

ALTER TABLE CONDUIT_CLIENTS_{suffix}
    ADD COLUMN phone VARCHAR(18);

I accidentally removed tracking table.

You have to restart pipeline, tracking table will be recreated by the connector.

Destination

The Sap Hana Destination takes a sdk.Record and parses it into a valid SQL query.

Configuration Options

Name Description Required Example
table The name of a table in the database that the connector should write to, by default. true users
auth.mechanism Mechanism type of auth. Valid types: DSN, Basic, JWT, X509. By default is DSN. false DSN
auth.dsn DSN connection string Required for DSN auth type. hdb://user:password@host443?TLSServerName=name
auth.host Sap Hana database host. Required for Basic, JWT, X509 auth types. hdb://hanacloud.ondemand.com:443
auth.username Sap Hana user Required for Basic type. hbadmin
auth.password Sap Hana password Required for Basic type. pass
auth.token JWT token Required for JWT type. jwt_token
auth.clientCertFilePath Path for certification file Required for X509 type. /tmp/file.cert
auth.ClientKeyFilePath Path for key file Required for X509 type. /tmp/key.cert

Table name

If a record contains a saphana.table property in its metadata it will be inserted in that table, otherwise it will fall back to use the table configured in the connector. Thus, a destination can support multiple tables in a single connector, as long as the user has proper access to those tables.