Join us on Wednesday, June 3rd for a deep dive into Customer Predictive Analytics Learn more

Reuse Dataiku variables list in SQL

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
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
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
Dataiker
Dataiker
Which database? MySQL, PostgreSQL, ... all have different syntaxes/functions.
Jeremy, Product Manager at Dataiku
0 Kudos
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
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
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
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)