Identifying primary keys from tables already in Qlik Replicate

[chrish_dexcom]

I understand Qlik requires tables to have a primary key in order to perform replication.  Is there a way to see a list of those fields that Qlik is using for primary keys for a given replication function?  I tried exporting the task (from the web UI) which connects to the database source but that doesn't seem to have any details about the tables' primary keys.  

Does anyone know how I can pull this information out of Qlik Replicate somehow?

[KellyHobson · Accepted solution]

Hey @chrish_dexcom ,

Within the UI, if you go to Designer mode- > Table Settings -> Transform tab you can see the keys identified on a table.

Best,

Kelly

[SwathiPulagam]

Hi @chrish_dexcom ,

Replicate will store all the table metadata in metastore.sqllite file. You can get the table metadata from there.

Thanks,

Swathi

[chrish_dexcom]

Wow, thank you for the lightning fast reply!!  For your first suggestion (Designer Mode/Table Settings), that would be limited to a single table at a time correct?

For the second suggestion, could you let me know where that metastore.sqllite file resides?  Would I need console access to the server in order to retrieve it?  Also is there anything available from the API that might help here?

[SwathiPulagam · Accepted solution]

Hi @chrish_dexcom ,

Using the dynamic_metadata.sqlite you will get the table definitions for all the tables in a particular task.

You will find this in the below location:

C:\Program Files\Attunity\Replicate\data\tasks\<Task Name>\dynamic_metadata.sqlite

Run the below command to get the metadata dump:

repctl dumpmetadata "C:\Program Files\Attunity\Replicate\data\tasks\<Task name>\dynamic_metadata.sqlite"

table_defs folder will be created and inside the Target file json you can search with "is_pk": true.

You can write a script to find out which tables have PK.

Thanks,

Swathi

[chrish_dexcom]

Thank you!  So when I run that command (repctl dumpmetadata) it will generate that "table_defs" folder which has the files I need within?  Sorry but I've never worked with sqlite data before so this is all rather new to me.

[SwathiPulagam]

Hi @chrish_dexcom ,

Yes, you are correct.

Thanks,

Swathi

[chrish_dexcom]

When we try your suggestion,  a sqlite file is generated but we are still getting an error in the console (see attached).  I suspect that might be preventing the "table_defs" folder from being created?

[Heinvandenheuvel]

>> a sqlite file is generated but we are still getting an error in the console

The Replicate data location is probably NOT in the default location and needs to be provided on the repctl command line.

First try something like 'repctl version' or 'repctl help' then go for the desirable command.

Try this

fail!! repctl help

success?? repctl -d d:\attunity\data help

Hein