Full load the table AFTER the load completes

[nabeelaslam1994]

Hello Team,

I have a scenario. When loading data into target in FULL load mode, Usually Qlik truncates the target table BEFORE and then loads the latest data. Is there a way to set an option to truncate the target table AFTER the data load completes?

Thanks,

Nabeel

[lyka]

Hi Nabeel,

What is your use case? The option to truncate before loading is used to prepare the target table. In case the table is not empty, then replicate truncates it before loading.

In your case, what will be the purpose of truncating the table after full load?

Thanks

Lyka

[Michael_Litz · Accepted solution]

Hi Nabeel,

I can imagine that your use case is to keep the old data intact until the new date is loaded so you dont have an empty table.

Please let me know if this is correct.

One way to accomplish this would be to do the truncate of the table from outside of replicate, after the load finishes. You will need to set the task to do nothing on full load.

The problem will be that when you are loading the "same" data a second time you would get a Primary Key violation, You could mitigate this by manually adding a new column to the table - call it "InsertDate" and then populate it with the expression for todays date,

The second step would be to manually adjust the primary key on the target to include the InsertDate field - thus making the records unique.

Then when the new (second) full load is done you would truncate from outside of replicate using the insertdate field. Possible mis use of word truncate here - just a delete with a where clause using the insertdate.

Let me know if this makes sense to you.

Thank you,
Michael Litz
Technical Support Engineer

[nabeelaslam1994]

Hi Lyla,

I wanted to make sure that the load process completes before it truncates the table otherwise if it fails, we still have an empty table sitting.

Nabeel

[Heinvandenheuvel]

Hello Nabeel.

You problem description is a bit odd 'truncate table after load', but @Michael_Litz  figured it out anyway. I don't  think he has a workable solution though.

You are not the only one with the desire to have something, anything, while the full load is going. A couple of years ago Replicate implemented a feature for a SQLserver customer to achieve this using temp tables. That is, load to a temporary tablename, and only when that succeeds delete (not truncate) the main table and rename the temp table into its place. That solution had problems and was not pursued in general,  but it  _does_ work and is still there for  SQL Server family targets. If that's what you have you can enable it IN DEV and test by setting the INTERNAL parameter  loadToTempTables. Now if this does work for you and you want to adopt it, I would recommend a 'notification' to support with that key word indicating your intend to use it and asking whether you can expect the feature to remain available to give them a heads-up.

You could do something similar 'by hand' . To do so you would create a global transformation rule to rename each table perhaps by appending  _TMP or prefixing with TMP_ (easier to spot in listings).You do this in a 'clone' of the main task, or by a temporay edit.  After the fullload tasks completes (with its CDC as desired) stop it, and  and run the main tasks (reverting the globale transfomration edit)  'by timestamp' and 'lables already loaded. This workaround will ofcourse require double the target DB space for a while and requires 'watching' the task allthough that last is not so critical as the CDC will keep the temp tables in sync. A similar solution woul load to an entire temp dabase and rename that.

There is one more potential variant on this solution but is it not for the faint of hard and still makes partial data visible for the duration of the load. One could change the SYNTAX for the  target DB to a private syntax. IN that JSON definition of the private syntax you would change the syntax of the TRUNCATE statement to a rename. This would 'move away' instead of throw away the old data such that you can rename back after issues while you  investigate those issues. You would have to reviews for yourself under which circumstance rename would fail where truncate would have worked.

Existing syntax 

"truncate_table":	"TRUNCATE TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC}",

UNTESTED potential alternative  syntax 

"truncate_table":	"RENAME TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ${QO}TMP_${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC}",

Good luck,

Hein