Dataset query fails when not using a where clause

user053667
user053667 Member
edited March 2023 in SQL DataFlows

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 Coach
    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • 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.

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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
    xywust2014 Member
    edited 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.