SAP HANA is an in-memory relational database and application platform that provides high-performance analytics and real-time data processing.
|Capture Deletes||check||All tables|
|Custom Data||check||All tables and fields|
|Data Blocking||check||Column level, table level, and schema level|
Follow our step-by-step SAP HANA setup guide to connect SAP HANA with your destination using Fivetran connectors.
You can use this connector to sync data from your SAP HANA database.
After you grant us access, Fivetran connects to your SAP HANA database. We check your source tables for primary key columns.
If a source table has primary key column(s), we create a one-time shadow table and four types of triggers for the source table in your
FIVETRAN_DB schema. We use the shadow table to store changes of the source table. We incrementally update these source tables in the destination. We also capture the deleted records for incremental tables in each sync using the shadow tables.
We create the shadow table using all the primary keys of the source table and two extra columns. The two additional columns are:
fivetran_optype- The column represents the Data Manipulation Language (DML) operation on the record. We use the following three values for the different types of operations:
- 1 - DELETE
- 2 - UPDATE
- 3 - INSERT
fivetran_updatedat- A current timestamp column to track the last updated datetime of a record.
We name the shadow table using the
HADP_SHADOW_SCHEMANAME_SELECTED_TABLE_FIVETRAN format. For example, if your table name is
T503K and your schema name is
SAPHANADB, then the name of the shadow table will be
We create four types of triggers on the table to track the three DML operations, based on the event type
AFTER. We name the triggers using the
HADP_SCHEMANAME_SELECTED_TABLE_TYPE_OF_DML_OPERATION_EVENT_TYPE format. For example, if your table name is
T503K and your schema name is
SAPHANADB, then the name of the four triggers will be:
We periodically flush the last seven days’ data from the shadow tables. We pull a full dump of all existing data from your SAP HANA database. Using a change data capture process, we pull incremental updates at regular intervals.
Once you deselect a source table from the connector dashboard, we remove the shadow table and the corresponding triggers from your database in the next sync.
If a source table doesn’t have any primary key column, we mark the table as
Reimported and completely import the whole table during each sync. We automatically capture the deletes for the re-imported tables based on their sync time.
If data in the source changes (for example, you add new columns, new tables, or change a data type), Fivetran automatically detects and persists these changes into your destination. For every schema within the SAP HANA databases that you connect, we create a different schema in your destination that maps directly to its native schema so that the data is in a familiar format to work with.
Fivetran tries to replicate the exact schema and tables from your source SAP HANA to your destination. You can include tables that reside in your HANA database.
We retrieve all the available tables from your database except the user-defined tables.
Fivetran generated columnslink
Fivetran adds one extra column to every table that is added to your destination to give you insight into the progress and changes of your connector:
||INSTANT||(UTC TIMESTAMP) indicates the time when Fivetran last successfully synced the row|
Fivetran also adds one extra column to every table that does not have a primary key column to keep records unique:
||STRING||ID to keep records unique when there is no primary key|
Type transformation and mappinglink
As we extract your data, we always do our best to match SAP HANA data types to types supported by Fivetran. In some cases, we don’t accept source types, so we automatically change it to the closest type possible or sometimes don’t load that data at all. Our system automatically skips columns of data types that we don’t accept or transform, such as BLOB.
The following table shows how your SAP HANA data types get transformed to a destination type:
|SAP HANA Type||Destination Type||Fivetran Supported|
Fivetran data types not supported by your destination may also get converted when we load the data into your destination. For more information about how the data is transformed from an internal Fivetran type to a type supported by your destination, visit the individual destination pages.
Contact our support team if you need a data type that we are missing.
Excluding source datalink
On your Fivetran dashboard, you can exclude tables and columns from syncing. On the connector details page, go to the Schema tab and deselect the columns or tables you would like to omit from syncing.
When a new source is connected, we first create the shadow tables and triggers for all the source tables on your database.
Then, we copy every row from every table in the schema that you have connected (except for the ones that you have excluded through the dashboard) and add Fivetran generated columns. We copy the rows by executing a
SELECT statement on each table.
Once the initial sync is complete, we start performing incremental updates at regular intervals using one of the following ways:
Incremental: Tables that have a corresponding shadow table are incrementally updated. We save the sync state, so we start syncing from the point where we left during the next sync.
Re-import: Tables that don’t have a corresponding shadow table are re-imported. We re-import tables using the following conditions:
Table Import Duration Import Frequency 3 seconds or less Every sync Between 3 seconds and 5 minutes Twice a day More than 5 minutes Once a week
Fivetran maintains shadow tables for the source tables that are incrementally updated. We recreate the shadow tables if you:
Delete the triggers present on the base tables
Add a new primary key
Delete an existing primary key
Change an existing primary key’s datatype
Delete the shadow table
We re-apply the triggers on the base tables and continue with the incremental syncs. However, this may cause data inconsistencies in the destination. On your connector dashboard, we display a Warning notification for these tables. To ensure data integrity, we recommend that you perform a complete re-sync for these tables.
- We track deleted records for all tables.
- Tables have a
_fivetran_deletedcolumn in the destination. After each re-import, we mark any row that no longer exists in the source table as deleted in the destination.