Update redshift table column with dataiku project variable.

Solved!
Ankur30
Level 3
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

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

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 3
Author

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

0 Kudos

Labels

?
Labels (4)
A banner prompting to get Dataiku