Is going through the process of adding a table to a task the only method for identifying which field(s) is/are going to be used as the Primary Key?
We currently maintain our task files external to Qlik, and new tables are added as JSON entries directly in to each task file. This hasn't been an issue, however, we want to make some changes to the fields chosen to represent the Primary Key.
Our understanding is that Qlik first checks to see if the source table has any fields marked as Primary Key, and uses these first if they exist. If not it then identifies any source Indexes marked as Unique and uses the first when ordered alphanumerically.
Is this correct?
Secondly, our understanding is that if we do want to change the primary key field list away from the default Qlik has identified then we need make the following additions to the JSON:
For each field identified by Qlik as a default PK field, which we want to continue to pass to target but not as a PK field
[cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[transform_columns] add [column_name] (with field name) and [action] (set as keep)
[cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[pk_manipulated].[segments] remove the same field entry
The challenge is that when adding a table for the first time we want to be able to identify exactly what JSON entries are required, rather than having to add the table to the UI, amend the PK, export the task, undo the changes, save the task. Just so we can understand what is required.
I should also point out that when I did some testing and only included the fields that I wanted in my target PK in the [cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[pk_manipulated].[segments] list, if I did not have the entry in [cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[transform_columns] to tell Qlik to "keep" the removed PK fields then when actually using the task it would still use all the default fields it had originally identified as the PK
In general Replicate detects the table's primary key (PK) and unique indexes (UIs) automatically. The order and priority of the PK/UI are (in descending):
1. if a PK exists, the PK will be used and ignore other UIs. (PK is one and only one if it exists) 2. If no PK exists and the table has a single UI, then the UI will be used 3. If no PK exists and the table has multiple UIs, then Replicate will select the first UI (by name alphabetically ordered ascending list)
Hope this helps.
Regards,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Thanks @john_wang I really appreciate that confirmation.
Are there any thoughts on the second question, our goal being to move to a declarative primary key model, ideally this would not include having to first understand what Qlik will do by default (so that we can add the unwanted fields to the "KEEP" action)
Secondly, our understanding is that if we do want to change the primary key field list away from the default Qlik has identified then we need make the following additions to the JSON:
For each field identified by Qlik as a default PK field, which we want to continue to pass to target but not as a PK field
[cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[transform_columns] add [column_name] (with field name) and [action] (set as keep)
[cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[pk_manipulated].[segments] remove the same field entry
The challenge is that when adding a table for the first time we want to be able to identify exactly what JSON entries are required, rather than having to add the table to the UI, amend the PK, export the task, undo the changes, save the task. Just so we can understand what is required.
Hi @john_wang , the key is that we want to deviate from the identified primary key, via code only. We're replicating to Kafka and do not want to do so via Primary Key/Index.
I've done some further testing and it seems the issue is that when you modify the JSON and define the fields you want as the Key in Kafka via [cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[pk_manipulated].[segments] the Replicate UI for the Primary Key still shows the original default key unless you add the entries [cmd.replication_definition].[tasks].[manipulations].[table_manipulation].[transform_columns] add [column_name] (with field name) and [action] (set as keep).
However, even though the UI shows the old Key, it still picks up the JSON and uses that
