variable expansion in SQL query endpoint

fjavanderspek
fjavanderspek Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 8 ✭✭

Goodday,

is it possible to use variables (instance-level, project-level, otherwise) in SQL query endpoint SQL statements, in an API service? And if so, can they be used as database object identifiers? (schema name, table name, etc)

Kind regards

Answers

  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 317 Dataiker

    Hi @fjavanderspek
    ,

    It is not possible to reference variables from the API node. Usually you would instead retrieve parameters and pass them to your endpoint.

    However, if you really were interested in using variables you could:
    (1) Switch to creating a Python endpoint
    (2) Your Python API service would need to create a remote API client to interact with your design/automation nodes remotely
    (3) You could then use the regular Python functions like get_variables() to retrieve your variables in your code
    (4) Finally, you could build your SQL query using your retrieved variable(s) and then create a SQL query using client.sql_query()

    Thank you,
    Sarina

  • fjavanderspek
    fjavanderspek Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 8 ✭✭

    Just to make sure I understand correctly: we can't use parameters passed to APINodeClient.sql_query as database object identifiers, right? (e.g. pass a table name 'customers' and have a SQL query endpoint with a statement defined as 'SELECT * FROM ?', where we pass the table name as parameter). If we want to use dynamic database object identifiers, we should look for a way other than using SQL Query endpoints, such as the way you've suggested (using the client.sql_query); is my understanding correct?

  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 317 Dataiker

    Hi @fjavanderspek
    ,

    Indeed, it's not possible to pass the tablename/schema as parameters in a SQL Query endpoint. So indeed, your understanding is correct.

    Thanks,
    Sarina

Setup Info
    Tags
      Help me…