Defining relations between source and target using ColumnMap and KeyColumns
Note
The following is not relevant when the writer is in Append Only mode since all source operations are written to the target as inserts.
When a writer is in Merge mode, use one of the following approaches to allow Striim to determine whether an operation is an UPDATE, and if so which target table row to update, or an INSERT, in which case it will add a new row. See Mapping columns for more information about using ColumnMap.
The following writers support the KeyColumns syntax:
Azure Synapse Writer
BigQuery Writer
Cosmos DB Writer (see discussion of
KeyColumnsin the notes for the Collections property)Database Writer (in Striim 4.2.0.4 and later only): this writer is always in merge mode (that is, inserts, updates, and deletes in the source are handled as inserts, updates, and deletes in the target
Databricks Writer
Fabric Data Warehouse Writer
Hive Writer (see discussion of
KeyColumnsin the notes for the Tables property)MongoDB Writer (see discussion of
KeyColumns)Snowflake Writer
Primary key in source and corresponding column at same position in target
source table (emp) | target table (employee) |
|---|---|
id (primary key) | empid |
name | empname |
dob | empdob |
Since the target column empid that corresponds to the source column's primary key id is in the same (first) position in the table, no additional syntax is required:
Tables:'emp,employee'
Primary key in source and corresponding column at different position in target
source table (emp) | target table (employee) |
|---|---|
id (primary key) | empname |
name | empdob |
dob | empid |
Since the target column empid that corresponds to the source column's primary key id is in a different position in the table, add a ColumnMap to define which target column corresponds to the source's primary key:
Tables:'emp,employee ColumnMap(empid=id,empname=name,empdob=dob)'
Note
Tables are mapped source first, but columns are mapped target first.
No primary key in source but has unique identifier at different position
source table (emp) | target table (employee) |
|---|---|
id | empname |
name | empid |
dob | empdob |
Since the source table has no primary key, use both ColumnMap and KeyColumn to map a column with values that uniquely identify the row:
Tables:'emp,employee ColumnMap (empid=id,empname=name,empdob=dob) KeyColumn (empid)'
Note
Alternatively, for Oracle Reader sources only, specify KeyColumns in the reader's Table's property (see Specifying key columns for tables without a primary key).
Target has a unique identifier that has a different name in the source
source table (emp) | target table (employee) |
|---|---|
id | name |
name | dob |
dob | uuid |
If target has a column with values that uniquely identify the row but a column with matching values does not exist in the source, use KeyColumns to specify that target column.
Tables:'emp,employee ColumnMap (uuid=id) KeyColumns(uuid)'
Note
When using KeyColumns in the target, the source's Compression property (if any) must be set to True.
No primary key in source but a combination of target columns can serve as unique identifier
source table (emp) | target table (employee) |
|---|---|
deptid | deptid |
empid | empid |
name | name |
dob | dob |
If the source table has no primary key and no single column in the target contains values that uniquely identify the row, use KeyColumns to specify multiple columns in the target with values that can be concatenated to create unique identifiers. In this example, the values of empid are unique for each value of deptid, so the combination of the two uniquely identifies each row:
Tables:'emp,employee KeyColumns(deptid,empid)'
Note
When using KeyColumns in the target, the source's Compression property (if any) must be set to True.
No primary key in source and no additional syntax
When there is no primary key in the source and you do not use any of the above methods to deal with the issue, Striim will concatenate all the values of each target row to create unique identifiers. This will degrade performance, especially when the writer's Optimized Merge property is True, so we strongly recommend using one of the above methods.