Population of Target Dataset with Autogenerated Primary Key

Options
chiraggiri
chiraggiri Registered Posts: 6 ✭✭✭

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.

Tagged:

Answers

  • Zach
    Zach Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 153 Dataiker
    Options

    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 Settings > Advanced. 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

Setup Info
    Tags
      Help me…