variable expansion in SQL query endpoint
![fjavanderspek](https://us.v-cdn.net/6038231/uploads/Dataiku/nAvatar16.png)
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 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 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 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