> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# SAP HANA external table gem
> Read and write from SAP HANA
export const execution_engine_0 = "Prophecy Automate"
This gem runs in {execution_engine_0}.
## 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:
1. Open your pipeline in the [Studio](/data-analysis/development/studio/studio).
2. Click on **Source/Target** in the canvas.
3. Select **Source** or **Target** from the dropdown.
4. Click on the gem to open the configuration.
In the **Type** tab, select **Hana**. Then, click **Next**.
In the **Location** tab, set your connection details and table location. To learn more, jump to [Source location](#source-location) and [Target location](#target-location).
In the **Properties** tab, set the table properties. To learn more, jump to [Source
properties](#source-properties) and [Target properties](#target-properties).
In the **Preview** tab, load a sample of the data and verify that it looks correct.
## 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](/data-analysis/environment/connections/hana) in the Prophecy fabric you will use. |
| Read Using | How to define the table location.- **Table**: Provide the schema and table name.
- **Query**: Select the table using a SQL query.
|
| 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](/data-analysis/environment/connections/hana) 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](/data-analysis/gems/source-target/external-table/hana/identity-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 the `product_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_quantity` changes from **100 → 150**, and `last_restocked` changes from **2025-07-15 → 2025-08-01**.
* `category` and `price` remain unchanged.
##### Product 2003
* The key matches an existing row, so the system updates the record.
* `stock_quantity` changes from **20 → 0**, and `last_restocked` changes from **2025-07-20 → 2025-07-30**.
* `category` and `price` remain unchanged.
##### Product 2005
* The key does not exist, so a new row is inserted.
* Only the merge columns receive values: `stock_quantity` is set to **85** and `last_restocked` is set to **2025-08-02**.
* `category` and `price` are set to **null** because they are not in the specified merge columns.