Qlik Replicate- Full load data into CT table

[Megha_More]

Hi Team,

I want to replicate full load data into CT table only,  is there any solution on this ?

Thanks,

Megha

[john_wang]

Hello @Megha_More ,

Thanks for reaching out to Qlik Community!

Yes, we may have a chance to achieve that. Do you mind sharing the purpose, and what's the source & target endpoint types ?

Best Regards,

John.

[Megha_More]

Hi @john_wang ,

Basically another team need to build a logic , so they want a single table to be created.

So source is IBM DB2 and target is Google Cloud BigQuery.

Thanks,

Megha

[DesmondWOO]

Hi @Megha_More ,

Yes. it’s important to bear in mind that except column header__change_mask, other header columns do not accept Null values. So, you need to add those header columns in your transformation page.

Regards,
Desmond

[Megha_More]

Hi,

Can you please elaborate on what steps need to be taken to achieve the mentioned scenario?

Thanks,

Megha 

[john_wang]

Hello @Megha_More ,

We'd like to confirm if it works, in progress now. Will get back to you shortly.

Regards,

John.

[Heinvandenheuvel]

In DEV you let Replicate create a __CT table or tables and get some sample data values in there.

Next you define a dedicated task which uses TRANSFORMATION to ADD all the header fields you selected { (*)change_seq, (*)change_oper, change_mask,  stream_position,  operation, transaction_id,  (*) timestamp } to the table columns with target tables renamed to __CT.  This task will be different and separate from the main task to avoid table naming collisions as per @john_wang 's next reply.

You need to fill those manually added header columns with 'reasonable'  data values in the context of a fullload. You'll probably use a formatted 'localtime' for some. Change_seq will be tricky as it has to be unique and preferably in sorter order before  future CDC provided changes. You may want to use unixepoch + subsecond modifier for that and/or the base table PK or a combination there-off. maybe, just maybe, use the millisecond timestamp and just add a bit of substr(hex(randomblob))) to that 'hoping' for uniqueness.

fwiw,

Hein.

[john_wang]

Hello @Megha_More ,

Besides @Heinvandenheuvel comment, looks like it does not work for most of the target endpoints (except streaming types of targets, eg Kafka). In the beginning my idea is:

1- Run a regular store changes task to let Replicate creates the table "XXX__ct" (for example the original source table name is XXX).

2- Rename the table name to "XXX__ct" in table setting (so far the Full Load data will write to this table). Certainly we need set the Full Load setting to "TRUNCATE before loading" (rather than DROP and CREATE).

3- Set the "Change Table Settings" --> "Suffix" value to NULL (so far the CDC data will write to table "XXX__ct" too). Certainly we need set the Change Table Creation to "Delete old changes and store changes in existing change table" (rather than DROP and CREATE).

Unfortunately the task failed with error:

The Change Table name 'SCOTT.XXX__ct' cannot be the same as the target table name

Qlik Replicate detects the target table name and store change table name are duplicate then the task stopped forcedly. I'm afraid the above approach does not work (sanity test done on Oracle to SQL Server task, based on Replicate 2023.5).

In short, I do not think it's doable. The alternative option is CREATE VIEW in BigQuery to concatenate the Base table and __ct table to meet your downstream apps requirements. 

Regards,

John.

[Megha_More]

Hi @john_wang ,

All over conclusion is that, it is not a ideal way to do, right ?

Thanks,

Megha

[john_wang · Accepted solution]

HI @Megha_More ,

Yes, looks to me no way at present.

Regards,

John.