SQL API ENDPOINT (Passing multiple values in test query)

anish_anand

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,

Liev

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!

anish_anand

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

Liev

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.

Connor

Hi @Liev
,

In that documentation you attached it doesn't say if this functionality is accessible from a deployed API could you confirm if it is or not?

Thank you for all your responses.

Connor