topic Re: Qlik Replicate- Full load data into CT table in Qlik Replicate

Qlik Replicate- Full load data into CT table

Megha_More — Mon, 06 Nov 2023 10:28:08 GMT

Hi Team,

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

Thanks,

Megha

Re: Qlik Replicate- Full load data into CT table

john_wang — Mon, 06 Nov 2023 10:43:36 GMT

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.

Re: Qlik Replicate- Full load data into CT table

Megha_More — Mon, 06 Nov 2023 11:03:19 GMT

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

Re: Qlik Replicate- Full load data into CT table

DesmondWOO — Mon, 06 Nov 2023 15:19:58 GMT

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

Re: Qlik Replicate- Full load data into CT table

Megha_More — Tue, 07 Nov 2023 04:25:20 GMT

Hi,

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

Thanks,

Megha

Re: Qlik Replicate- Full load data into CT table

john_wang — Tue, 07 Nov 2023 04:45:35 GMT

Hello @Megha_More ,

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

Regards,

John.

Re: Qlik Replicate- Full load data into CT table

Heinvandenheuvel — Tue, 07 Nov 2023 05:53:22 GMT

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.

Re: Qlik Replicate- Full load data into CT table

john_wang — Tue, 07 Nov 2023 05:26:52 GMT

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.

Re: Qlik Replicate- Full load data into CT table

Megha_More — Wed, 08 Nov 2023 05:41:03 GMT

Hi @john_wang ,

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

Thanks,

Megha

Re: Qlik Replicate- Full load data into CT table

john_wang — Fri, 17 Nov 2023 13:20:10 GMT

HI @Megha_More ,

Yes, looks to me no way at present.

Regards,

John.