How to specify column mapping in AWS Data pipeline?

Question

I am using AWS data pipeline to copy data from RedShift to MySql in RDS. The data is copied to MySQL. In the pipeline the insert query is specified as below:

insert into test_Employee(firstname,lastname,email,salary) values(?,?,?,?);

Is there any way for me to specify the column name for the source table in place of ? in the above query? I tries adding the column names for the source table but that does not seem to work. Currently the column names in both the source and destination table are same.

Thanks for your time. Let me know if any other information is required.

Answer 1

Specifying columns instead of ? wouldn't work because insert SQL queries know nothing about your source datasource. AWS Copy activity just passes the parameters to this query in the same order you selected them from the source dataset.

However column names of destination table (test_Employee) in insert query don't have to match the order of columns specified in the DDL, so you can change this query to match the order of columns in the source table. E.g if your source dataset has following columns: email,first_name,last_name,salary

Insert query:

insert into test_Employee(email,firstname,lastname,salary) values(?,?,?,?);

Note. As you can see the column names of the source and destination table don't have to match.