New to Dataiku DSS? Try out our NEW Quick Start Programs today and get onboarded on the product in just one hour! Let's go

Reuse Dataiku variables list in SQL

osk
Level 1
Reuse Dataiku variables list in SQL

Hi there, 



I have one question related to global variables in DSS. Namely, is it possible to define a list as global variable and then use this in an SQL statement? What I am thinking of is something like this:



Global variable:



{"test_list": ["A", "B"]} 



SQL Statement



SELECT *



FROM test



WHERE "test_col" IN ('${test_list}')



Thanks a lot for your help!



Best, 



Oliver



 

0 Kudos
7 Replies
jereze
Dataiker
Dataiker

Hi,



You query




SELECT *
FROM test
WHERE "test_col" IN ('${test_list}')


will be executed as




SELECT *
FROM test
WHERE "test_col" IN ('["A","B"]')


Then you need to cast this string to an array, and the how depends on your SQL database.



EDIT: here is an example for PostgreSQL using the jsonb type and the ? operator.




SELECT *
FROM test
WHERE '${test_list}'::jsonb ? "test_col";


 

Jeremy, Product Manager at Dataiku
osk
Level 1
Author
Great, thanks for your help. Do you know a solution of how to do this in a Amazon AWS database? I only found a solution using SPLIT_PART or JSON_EXTRACT_ARRAY_ELEMENT_TEXT where one has to specify a position of the word which should be returned whereas I want to return all words in the list. Cheers!
0 Kudos
jereze
Dataiker
Dataiker
Which database? MySQL, PostgreSQL, ... all have different syntaxes/functions.
Jeremy, Product Manager at Dataiku
0 Kudos
osk
Level 1
Author
Sorry, wanted to answer that in the previous reply. I use Amazon Redshift which is based on an older version of PostgreSQL 8.0.2. Cheers!
0 Kudos
osk
Level 1
Author
Hi jereze,
I tried to play around with it again but do not seem to find a solution. Did you have a chance to look into this? Thanks a lot for your help!
0 Kudos
jereze
Dataiker
Dataiker
I personally don't use Redshift but I can give you an example using PostgreSQL (I updated the orginal answer). If you really need to work with Postgresql Array type (and not the json type), you can have a look at this: https://stackoverflow.com/questions/47359288/convert-a-text-to-an-array-postgresql
Jeremy, Product Manager at Dataiku
0 Kudos
osk
Level 1
Author
I think the method you describe is unfortunately not supported in Redshift. Nevertheless, thanks a lot for your help! I will ask in the AWS community and if there is a possibility post it here.
0 Kudos
Labels (2)
A banner prompting to get Dataiku DSS