Population of Target Dataset with Autogenerated Primary Key

chiraggiri
Level 2
Population of Target Dataset with Autogenerated Primary Key

I have input dataset in the form of excel file with following format:

WORK_IDWORK_NAMERF_NAME

 

Target Dataset if postgresql table with following def:

CREATE TABLE dl_insight_zone.DIM_INITIATIVE(
INIT_UNIQUE_ID SERIAL PRIMARY KEY,
WORK_ID varchar(80),
WORK_NAME text,
RF_NAME text
);

I want to create auto-generated primary key with each record insertion. However, I am not able to do that. Please suggest options with sync/prepare recipe or any other way to do this.

0 Kudos
1 Reply
ZachM
Dataiker

Hi @chiraggiri ,

You can accomplish this by using a Window recipe to create an auto-incrementing column, and then setting the column as the primary key:

  1. Create a Window recipe with the input set to your Excel dataset and the output set to the Postgres dataset.
  2. Configure the Window recipe as follows. The prod_rate_value column can be set to any column (this is the column that the recipe will sort the rows by when adding the auto-incrementing column. 42D540EA-DB50-4D60-AD3F-F243ED429D8E.png

     

    84602F83-FA39-48F7-BD93-137C93B9F4F3.png

     

  3. The output dataset should now have an auto-incrementing column like this: 2D422A1E-52D3-4546-97B8-84834CC9D050_1_201_a.jpeg

     

  4. Change the table creation mode of the output dataset in SettingsAdvanced. Set the table creation mode to Manually define, and mark the auto-incrementing column as the primary key: EF425BE6-5A1D-4851-B01D-5515C64C4BAC_1_201_a.jpeg
  5. Rebuild the dataset. The auto-incrementing column should now be the primary key.

Here's a picture of how it looks in the flow. prod_rate is the Excel dataset, and prod_rate_window is the Postgres dataset:image.png

 

Feel free to let me know if you have any questions.

 

Thanks,

Zach

0 Kudos