This gem runs in .
Overview
The SAP HANA Source and Target gems let you connect Prophecy pipelines to SAP HANA tables for reading and writing data. This page outlines how to configure SAP HANA sources and targets using the appropriate connections, locations, and properties.Create a SAP HANA gem
To create a SAP HANA Source or Target gem in your pipeline:Add a Source or Target gem to the pipeline
- Open your pipeline in the Studio.
- Click on Source/Target in the canvas.
- Select Source or Target from the dropdown.
- Click on the gem to open the configuration.
Set location details
In the Location tab, set your connection details and table location. To learn more, jump to Source location and Target location.
Set table properties
In the Properties tab, set the table properties. To learn more, jump to Source
properties and Target properties.
Source configuration
Use these settings to configure a SAP HANA Source gem for reading data.Source location
| Parameter | Description |
|---|---|
| Format type | Table format for the source. For SAP HANA tables, set to hana. |
| Select or create connection | Select or create a new SAP HANA connection in the Prophecy fabric you will use. |
| Read Using | How to define the table location.
|
| Schema (Table only) | Schema in SAP HANA where the table is located. |
| Name (Table only) | Name of the SAP HANA table to read data from. |
| Query (Query only) | SQL query used to retrieve a table. |
Source properties
Infer or manually configure the schema of your Source gem. Optionally, add a description for your table.Target configuration
Use these settings to configure a SAP HANA Target gem for writing data.Target location
| Parameter | Description |
|---|---|
| Format type | Table format for the source. For SAP HANA tables, set to Hana. |
| Select or create connection | Select or create a new SAP HANA connection in the Prophecy fabric you will use. |
| Schema | Schema in SAP HANA where the target table resides or will be created. Select the + sign on the field to use a configuration variable or secret in your schema definition. |
| Name | Name of the SAP HANA table to write data to. Select the + sign on the field to use a configuration variable or secret in your table definition. If the table doesn’t exist, it will be created automatically. |
Target properties
Review schema of your Target gem and optionally update the metadata of columns in the schema. You can also add a description for your table.Generated columns
Generated columns are specific to SAP HANA and auto-generate values for new rows. You can define generated columns in the Properties tab via column-level metadata. Learn more in Generated columns.Write options
Control how data is written into the target table during each run of the pipeline. Choose whether to overwrite, append, or merge rows.| Mode | Description |
|---|---|
| Wipe & Replace Table | Deletes the existing table and creates a new one with the incoming data. |
| Write Fresh Table; Error if Exists | Creates a new table, and fails if a table with the same name already exists. |
| Append Row | Inserts all incoming rows to the existing table without modifying existing data. |
| Merge - Upsert Row | Updates if keys match, insert if not. |
| Merge - SCD 2 | Stores and manages the current and historical data over time. |
| Merge - Update Row | Only updates columns where key matches. |
| Merge - Delete Row | Deletes rows where key matches. |
| Merge - Delete Row if Exists; Otherwise Insert | Deletes rows where key matches, otherwise insert a new row. |
| Merge - Delete Row if Exists; then Insert | Deletes rows where key matches and always inserts new rows. |
Advanced options
Configure the following for Wipe & Replace Table, Write Fresh Table; Error if exists, and Append Row modes.| Parameter | Description |
|---|---|
| Parallelism | The number of insert queries to execute in parallel. |
| Rows Per Insert | The number of rows included in each insert query to the SAP HANA database. |
Merge parameters
Configure the following for any Merge.| Parameter | Required For | Description |
|---|---|---|
| Merge condition | All merge modes | Specify one or more key columns that determine how incoming rows match existing rows in the target table. These columns act as the unique identifiers. |
| Merge columns | Upsert row, Update row, Delete row if exists; otherwise insert | Select the specific columns to update or write when a key match is found. This controls which fields are modified during updates or inserts. |
Example: Merge - Upsert Row
This example demonstrates how the Upsert row merge mode works when updating an SAP HANA table.Existing table in SAP HANA
Your SAP HANA table currently contains the following product inventory data:| product_id | category | price | stock_quantity | last_restocked |
|---|---|---|---|---|
| 2001 | Electronics | 99.99 | 100 | 2025-07-15 |
| 2002 | Apparel | 29.99 | 50 | 2025-07-10 |
| 2003 | Electronics | 149.99 | 20 | 2025-07-20 |
Merge configuration
You configure the merge using the following settings:- Merge Mode: Upsert Row (update if key matches, insert if not)
- Unique Key:
product_id - Merge Columns:
stock_quantity,last_restocked(only these columns are updated/inserted)
Incoming data
New inventory updates are coming through your pipeline:| product_id | category | stock_quantity | last_restocked |
|---|---|---|---|
| 2001 | Electronics | 150 | 2025-08-01 |
| 2003 | Gaming | 0 | 2025-07-30 |
| 2005 | Apparel | 85 | 2025-08-02 |
Result
The Target gem processes each incoming row by checking if theproduct_id already exists in the target table:
After the Target gem completes, your table looks like this:
| product_id | category | price | stock_quantity | last_restocked |
|---|---|---|---|---|
| 2001 | Electronics | 99.99 | 150 | 2025-08-01 |
| 2002 | Apparel | 29.99 | 50 | 2025-07-10 |
| 2003 | Electronics | 149.99 | 0 | 2025-07-30 |
| 2005 | null | null | 85 | 2025-08-02 |
Product 2001
- The key matches an existing row, so the system updates the record.
stock_quantitychanges from 100 → 150, andlast_restockedchanges from 2025-07-15 → 2025-08-01.categoryandpriceremain unchanged.
Product 2003
- The key matches an existing row, so the system updates the record.
stock_quantitychanges from 20 → 0, andlast_restockedchanges from 2025-07-20 → 2025-07-30.categoryandpriceremain unchanged.
Product 2005
- The key does not exist, so a new row is inserted.
- Only the merge columns receive values:
stock_quantityis set to 85 andlast_restockedis set to 2025-08-02. categoryandpriceare set to null because they are not in the specified merge columns.