Datasets API: Filtering?

user02003 · March 2019

Hi,

so we're trying to filter results from a query to the dataset api (as documented here https://developer.domo.com/docs/dataset-api-reference/dataset in the v1/datasets/<dataset_id> path) on a dataset to a specific range of values for one particular field (ie only return rows where field x is greater than this value and less than this other value). How exactly to form the query for this is not documented anywhere - I was wondering if anybody could point us in the right direction.

Thanks

n8isjack · March 2019

The API does not allow for filtering data, unfortunately. You will have to download the whole file and then parse and filter it programmatically.

I don't have a great workaround suggestion for you, but here is one advanced possibility:

Using a Blend (Data Fusion):

The api will let you download a blend dataset. The advantage here is that you can form an inner join to enforce filtering. It is not intended for this purpose but will work. You can do it manually or programmatically.

Manually:

Create a dataset of the allowed values. Say that you want to filter age from 10-40. Make a dataset with 30 rows, kind of like this:

Allowed_Ages
10
11
12
...
40

In the Blend, join it to the original file and make it an 'inner join' so that the `age` must match an `Allowed_Ages` value or it will filter the row out.

You can manually upload a new file to change the filter.

Programmatically:

Create the same dataset as above, but using the dataset api. Manually create the Blend to inner join the files.

When your script runs to download the data, first have it upload a new dataset to replace the `Allowed_Ages`. Then have it pull the Blend results which will automatically use the last values uploaded as a filter.

user11539 · July 2019

I know this was a while ago, but I don't want others to be misled. You can certainly filter the results of a query as documented here: https://developer.domo.com/docs/dataset-api-reference/dataset#Query%20a%20DataSet

You will need an OAuth token by hitting https://api.domo.com/oauth/token

Post to

https://api.domo.com/v1/datasets/query/execute/{your dataset Guid}

with headers:

Authorization: bearer + token
Content-Type: application/json

and body:

{"sql": "select `my field 1`,`my field 2` from table where `my field 1` > 393"}

zmyrenee · July 2019

Hi, I am actually trying to do the same thing. However, I encountered an error code 'Content type \'application/octet-stream\' not supported'. Do you know what happened and how to resolve it?

guitarhero23 · July 2019

@zmyrenee Do you have in your headers that you want to receive octet-stream back instead of something json? I'm not very familiar with octet-stream but it appears that the api should return json by default so unless you have "Accept: Application/octet-stream" or something like that in the header I'd be surprised you'd get that error.

zmyrenee · July 2019

@guitarhero23 Actually, not, I am not having octet-stream in my header.

I am writing a python code which like this:

url_query = 'https://api.domo.com/v1/datasets/query/execute/' + str(data_id)
r_query = requests.post(url_query,
             headers={'Accept': 'application/json',
                      'Authorization': 'Bearer {}'.format(access_token)},
                  data=json.dumps({"sql": "SELECT * FROM table"}))

The data_id is the id of the data I would like to connect in DOMO. The access_token is working, as I can use it in the code below and it worked.

url_retrieve = 'https://api.domo.com/v1/datasets/' + str(data_id) + '/data?includeHeader=true'
r_retrieve = requests.get(url_retrieve,
                          headers={'Accept': 'text/csv',
                                   'Authorization': 'Bearer {}'.format(access_token)})

In the query code, if I do not specify data=json.dumps{...}, it will give me the error: Content type \'application/x-www-form-urlencoded;charset=UTF-8\' not supported

guitarhero23 · July 2019

I just tried this out myself in Python using the requests library as you did and I can confirm I'm getting the same thing.

If I do json.dumps(data) I get

"Content type 'application/octet-stream' not supported"

If i don't do json dumps I get

"Content type 'application/x-www-form-urlencoded;charset=UTF-8' " not supported

I will research and see what I can find because it's not clear in the API guide.

Also for whatever reason query is not available in the Python SDK

zmyrenee · July 2019

@guitarhero23

I was researching this for a couple of days without any answers. After discussion with you, I did find the answer! See reference link: https://2.python-requests.org/en/master/user/quickstart/

In this link, the author says: Using the JSON the parameter in the request will change the Content-Type in the header to application/JSON.

I guess that might be the reason!

url_query = 'https://api.domo.com/v1/datasets/query/execute/' + str(data_id)
r_query = requests.post(url_query,
headers={'Accept': 'application/json',
'Authorization': 'Bearer {}'.format(access_token)},
json ={"sql": "SELECT * FROM table"})

Thank you so much!

guitarhero23 · July 2019

I'm not sure if this is a step forward or backward but if you add

"Content-Type": "application/json"

to the headers and don't do json.dumps() you get this back instead

"Unrecognized token 'sql': was expecting ('true', 'false' or ""'null')"

For the hell of it I replaced 'sql' with 'true' and get the following

'Cannot construct instance of ' 
'`com.domo.icebox.client.query.execute.model.IceBoxQueryRequest` ' 
'(although at least one Creator exists): no ' 
 'boolean/Boolean-argument constructor/factory method to ' 
'deserialize from boolean value (true)'

guitarhero23 · July 2019

Wow, yes that worked. Awesome!

Datasets API: Filtering?

Comments

Categories