Mapping columns
By default, when a writer's Tables property maps source tables to target tables, the first column in the source table is written to the first column in the target table, the second column in the source table is written to the second column in the target table, and so on. This requires that the target table have at least the same number of columns as the source table and that the data types are compatible. For example:
Source table Emp:
column name | id | birthDate | name | salary |
data type | int | date | text | double |
Target table Employee:
column name | EMP_ID | EMP_DOB | EMP_NAME | SAL | DEPT |
data type | number | datetime | varchar | number | varchar |
In this case, assuming the DEPT column in the target table allows nulls, all you need is Tables:'Emp,Employee'.
When the target table does not match the source table, use the ColumnMap function to map individual columns in the source and target tables. The syntax is:
'<source table>,<target table> ColumnMap(<target column>=<source column>,...),...'
Note
Tables are mapped source first, but columns are mapped target first.
Example 1: columns in different order and extra column in target
Source table Emp:
column name | ID | NAME | DOB |
Target table Employee:
column name | EMP_ID | EMP_SSN | EMP_DOB | EMP_NAME |
Since the target table's columns are in a different order, you would use ColumnMap as follows:
Tables:'Emp,Employee ColumnMap(EMP_NAME=NAME,EMP_ID=ID,EMP_DOB=DOB)'
The unmapped EMP_SSN column in the target table must allow nulls.
Example 2: some columns have the same name
If a column has the same name in both the source and target, there is no need to specify it in ColumnMap, so long as at least one other column is mapped. For example:
Source table Emp:
column name | ID | NAME | ADDRESS | DOB |
Target table Employee:
column name | EMP_ID | DOB | NAME | ADDRESS |
Tables:'Emp,Employee ColumnMap(EMP_ID=ID)'
The NAME, ADDRESS, and DOB columns will be mapped automatically.
Example 3: extra column in source
Any source columns that are not in ColumnMap and do not exist in the target will be omitted. For example:
Source table Emp:
column name | ID | NAME | DOB | ADDRESS |
Target table Employee:
column name | EMP_ID | EMP_DOB | EMP_NAME |
Tables:'Emp,Employee ColumnMap(EMP_NAME=NAME,EMP_ID=ID,EMP_DOB=DOB)'
Modifying output using ColumnMap
When using ColumnMap (see Mapping columns), in place of the source column name you may specify a field in the METADATA or USERDATA map, an environment variable, or a static string. You may use this to overwrite data from the input stream or to supply data for columns in the target table for which the input stream has no values.
See also Changing and masking field values using MODIFY and Masking functions for other ways to modify output.
Note
Field names are case-sensitive.
When using ColumnMap, target tables must be specified with three-part names for Azure Synapse Writer (<database>.<schema>.<table>) and Snowflake Writer <DATABASE>.<SCHEMA>.<TABLE>.
For example, to write the CDC log timestamp in the METADATA map to the target column CDCTIMESTAMP:
... ColumnMap(EMP_NAME=NAME,EMP_ID=ID,EMP_DOB=DOB,CDCTIMESTAMP=@METADATA(TimeStamp))'
To specify a field in the USERDATA map (see Adding user-defined data to WAEvent streams or Adding user-defined data to JSONNodeEvent streams), use @USERDATA(<field name>):
... ColumnMap(EMP_NAME=NAME,EMP_ID=ID,EMP_DOB=DOB,EMP_CITY=@USERDATA(city))'
To write the Striim server's $HOSTNAME environment variable to the target column STRIIMSERVER:
... ColumnMap(EMP_NAME=NAME,EMP_ID=ID,EMP_DOB=DOB,STRIIMSERVER=$HOSTNAME)'
To write a static string, the syntax is:
... ColumnMap(<field name>='<string>')'
You may modify multiple tables by using wildcards. For example:
SRC_SCHEMA.%,TGT_SCHEMA.% COLUMNMAP(CDCTIMESTAMP=@METADATA(TimeStamp))