Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!READ MORE

Update redshift table column with dataiku project variable.

Solved!
Ankur30
Level 2
Level 2
Update redshift table column with dataiku project variable.

Hi 

 

I have one requirement where I want to update a column in redshift table with current date/time for every time a scenario is triggered in dataiku. 

Can you please help me with code/explanation on how I can achieve that.

 

Best Regards,

Ankur.

 

0 Kudos
1 Solution
AlexT
Dataiker
Dataiker

Hi @Ankur30 ,

I think for this use case you use add reporters in a scenario to write to your table. This will automatically write the timestamp of when the scenario is completed. 

1) Create or use an existing dataset with a timestamp column and any other columns you wish in this example I added outcome and scenario_name by updating the schema. 

Screenshot 2021-10-19 at 16.19.47.png

2) In Scenario under reporters configured correctly notably the timestamp column need to map to a timestamp column name in the dataset. The data needs to be defined in the format 

 {"column_name":"$variable_name"}

In the below example :

{"scenario_name":"${scenarioName}","outcome":"${outcome}"}

Screenshot 2021-10-19 at 16.20.40.png

3) After Refreshing my sample I can see the last columns were added.

Screenshot 2021-10-19 at 16.26.24.png

You can use a filesystem-like dataset and then sync to Redshift or use a redshift table directly.

Let me if this works for you and if this was what you were looking for.   

View solution in original post

2 Replies
AlexT
Dataiker
Dataiker

Hi @Ankur30 ,

I think for this use case you use add reporters in a scenario to write to your table. This will automatically write the timestamp of when the scenario is completed. 

1) Create or use an existing dataset with a timestamp column and any other columns you wish in this example I added outcome and scenario_name by updating the schema. 

Screenshot 2021-10-19 at 16.19.47.png

2) In Scenario under reporters configured correctly notably the timestamp column need to map to a timestamp column name in the dataset. The data needs to be defined in the format 

 {"column_name":"$variable_name"}

In the below example :

{"scenario_name":"${scenarioName}","outcome":"${outcome}"}

Screenshot 2021-10-19 at 16.20.40.png

3) After Refreshing my sample I can see the last columns were added.

Screenshot 2021-10-19 at 16.26.24.png

You can use a filesystem-like dataset and then sync to Redshift or use a redshift table directly.

Let me if this works for you and if this was what you were looking for.   

Ankur30
Level 2
Level 2
Author

Thank you @AlexT  for the detailed explanation. It worked.

0 Kudos

Labels

?
Labels (4)
A banner prompting to get Dataiku