topic Re: Column Ordinal Position in Qlik Replicate

Column Ordinal Position

simonB2020 — Sat, 02 Mar 2024 22:18:33 GMT

For an existing CDC task, target AWS Redshift...

Does QRs "COPY" execution explicitly declare a table's columns & their order ?

Asking because my source may change the order of columns, even if the number & names remain unchanged.

Thanks.

Re: Column Ordinal Position

john_wang — Sun, 03 Mar 2024 02:30:43 GMT

Hello @simonB2020 ,

Thanks for reaching out to Qlik Community!

You are right, Qlik Replicate uses explicit columns order during the data processing.

In Full Load staget, Qlik Replicate read data from source DB and generate CSV data files (*step 1), send the CSV files to storage (eg S3 in your scenario) then issue a COPY command to load Data Files from S3 to Redshift final tables.

In CDC stage, the data are read from source DB transaction log (rather than from DB source tables), the CSV files (*step 1) are sent to "net change table". And in the last step, the data are merged from "net change table" to final tables.

The columns order are explicitly defined while the CSV data files are generated, until to the last step of data processing. If you want to know the details you may set TARGET_LOAD/TARGET_APPLY to Trace (or Verbose), rerun the task and check the columns order in task log files.

A sample of logging level setting:

Hope this helps.

John.

Re: Column Ordinal Position

SushilKumar — Wed, 06 Mar 2024 09:19:30 GMT

Hello @simonB2020

Asking because my source may change the order of columns, even if the number & names remain unchanged.

in such case i am not sure how to are going to Change the order of columns without recreate the table. if you add any column, it may be added in the last of the table. if you recreate the table them object id may change causing replicate to fetch the data for that as it required old object id.

until you refresh the metadata. request you to test the use case in lower Environment and check post keep_CSV_file and keep_Error_file to check the Sequence of columns fetched by the QR.

Regards,

Sushil Kumar