Reuse Dataiku variables list in SQL

osk

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

jereze

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";

osk

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!

jereze

Which database? MySQL, PostgreSQL, ... all have different syntaxes/functions.

osk

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!

osk

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!

jereze

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

osk

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.