Reuse Dataiku variables list in SQL
Options
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.
