Reuse Dataiku variables list in SQL

osk
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

Tagged:

Answers

  • jereze
    jereze Alpha Tester, Dataiker Alumni Posts: 190 ✭✭✭✭✭✭✭✭
    edited July 18

    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
    osk Registered Posts: 9 ✭✭✭✭
    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
    jereze Alpha Tester, Dataiker Alumni Posts: 190 ✭✭✭✭✭✭✭✭
    Which database? MySQL, PostgreSQL, ... all have different syntaxes/functions.
  • osk
    osk Registered Posts: 9 ✭✭✭✭
    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
    osk Registered Posts: 9 ✭✭✭✭
    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
    jereze Alpha Tester, Dataiker Alumni Posts: 190 ✭✭✭✭✭✭✭✭
    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
    osk Registered Posts: 9 ✭✭✭✭
    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.
Setup Info
    Tags
      Help me…