Skip to main content

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))

If the same table is specified in ColumnMap both explicitly and by a wildcard, the explicit mapping will be used and the wildcard mapping will be ignored