Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Reuse Dataiku variables list in SQL
Added on March 4, 2019 7:46PM
Likes: 0
Replies: 7
osk
Registered Posts: 9 ✭✭✭✭
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
Answers
-
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"; -
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!
-
Which database? MySQL, PostgreSQL, ... all have different syntaxes/functions.
-
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!
-
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! -
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
-
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.
