Check out the first Dataiku 8 Deep Dive focusing on Productivity on October 29th Read More

SQL API ENDPOINT (Passing multiple values in test query)

Level 1
SQL API ENDPOINT (Passing multiple values in test query)

Hi,

I am trying to create a SQL API endpoint using a select query to my database.

select * from table where state in (?) and city in (?)

parameters

param1, param2

 

The query works fine, however problem arises when i try to filter records for multiple values of a parameter.

Just wanted to check where am i going wrong with the current JSON test query (attached screenshots)?

Thanks,

0 Kudos
3 Replies
Dataiker
Dataiker

Hi @anish_anand 

Indeed this I believe is a limitation since the IN clause is not fully supported and the array parameter you pass into the query won't work as you expect from pure SQL.

In the past, where I've had to use IN clauses I modify this to be a Python function endpoint.

Inside the endpoint, I receive a parameter and check if it's a list or a single value and create the SQL statement inside a dedicated method. Then I can execute that query against the SQL connection I require through DSS. See here for how this might work.

I hope this helps!

Level 1
Author

Hi @Liev ,

Thanks for your response!

That makes sense. So, I cannot use SQL API endpoint to filter for a vector of values,i.e let's say multiple country names. I was originally thinking that the error was because of the way i was testing query (defining parameter values) in jSON.

I went through the link that you shared. Few questions, since I am new to this -

1. Is there any function that i can use to fetch the host and apiKey names for my project? That is because i am working on a client's DSS server and do not have privileges to some operations-

client = DSSClient(host, apiKey)

 2. How will the below function parameter change if i try to read a redshift/RDS table?

streamed_query = client.sql_query('select * from train_set', connection='local_postgres', type='sql')

 
Thanks

0 Kudos
Dataiker
Dataiker

Hi @anish_anand 

1 - Without appropriate credentials, I don't see how you'd be able to access the system even if you were able to create the endpoint. DSS won't know who you are.

2 - You would modify the connection name to match the name given to your Redshift/RDS connection. The syntax itself will need to be compatible with the RDS engine you use.

Labels (1)