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?
Best 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"1
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!**3 -
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"1 -
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"}'
0 -
@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.
0 -
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!**1 -
@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!
1 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive