Population of Target Dataset with Autogenerated Primary Key

Options
Tags
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.

Answers

  • Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 153 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 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

Welcome!

It looks like you're new here. Sign in or register to get started.

Welcome!

It looks like you're new here. Sign in or register to get started.