Dataset query fails when not using a where clause

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? 

Tagged:

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    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.

Answers

  • GrantSmith
    GrantSmith Indiana 🟤

    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)?

  • 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"}'
  • @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
    GrantSmith Indiana 🟤

    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? 

  • @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!

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!