Dataset query fails when not using a where clause

user053667 · November 2020

I'm trying to get all the data from my dataset by executing a simple query: Here's what my code looks like:

rows = cnxn.execute(sqlcmd)
for row in rows:
    dataset_id = '609f128a-ac54-4f4f-a71e-81362f9fa154'
    table_name = 'orders'
    query_url = "https://api.domo.com/v1/datasets/query/execute/" + dataset_id
    myobj = {"sql": "SELECT * FROM TABLE where \"Order ID\"= 16000472"}
    query_result = requests.post(url=query_url, json=myobj, headers=headers)

Now this works fine, but when I remove the where clause from the SQL query

SELECT * FROM TABLE;

I get the following error:

b'There was a problem executing the SQL query: Underlying service error: Internal Server Error'

I want to get all the records in my dataset, what I'm doing wrong here?

jaeW_at_Onyx · November 2020

If i had to guess you have an incredibly large dataset. What if you just write a FOR LOOP and then stitch the data together in Python ( i assume you're using Python).

keep in mind, your dataset has to fit in memory. it maybe beneficial to write your data to csv between each iteration of the for loop.

also, executing iteratively means that you don't have to always do a SELECT * you can exclude stuff you've already captured.

GrantSmith · November 2020

Hi @user053667

Since you're wanting to pull all of the data in a dataset - instead of using the query have you attempted to just export the dataset itself using the api?

https://api.domo.com/v1/datasets/{DATASET_ID}/data?includeHeader=true&fileName=output.csv

This would export your dataset to the output.csv file. You should be able to remove the fileName parameter to return a string of the text.

Also it appears that you're using Python - have you looked into the Domo Python SDK (https://github.com/domoinc/domo-python-sdk)?

jaeW_at_Onyx · November 2020

If i had to guess you have an incredibly large dataset. What if you just write a FOR LOOP and then stitch the data together in Python ( i assume you're using Python).

keep in mind, your dataset has to fit in memory. it maybe beneficial to write your data to csv between each iteration of the for loop.

also, executing iteratively means that you don't have to always do a SELECT * you can exclude stuff you've already captured.

user053667 · November 2020

I've also tried exporting the dataset after posting the question but I'm still getting the same error:

b'{"status":400,"statusReason":"Bad Request","message":"Underlying service error: Internal Server Error","toe":"RR33APLF4C-G2HGO-TDGQU"}'

user053667 · November 2020

@jaeW_at_Onyx, I tried selecting top 10 rows ('SELECT TOP 10 * FROM TABLE') to test out what you're saying, but I'm getting the same error.

Hi, @GrantSmith , I've already tried exporting the dataset still getting same errror.

GrantSmith · November 2020

For testing, since you said it works with a where clause have you tried replacing your WHERE clause with

WHERE 1=1

to return all the records?

user053667 · November 2020

@GrantSmith Haha, I've also tried that too, it still wouldn't work.

But I've found when using `LIMIT 50` it works, so I'm implementing a solution similar to what @jaeW_at_Onyx suggested.

Thanks both of you!

xywust2014 · December 2022

I get the same error. It seems that the ds_query function in DOMO does not support the where statement here. It will be great if DOMO could include this in the ds_query function.

Dataset query fails when not using a where clause

Best Answer

Answers

Categories