Mapping columns
By default, when the Tables property of DatabaseWriter, KuduWriter, or RedshiftWriter 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)'