Cannot POST virtual_dataset from sample source data

1

So, I just did a docker pull on Dremio (really enjoying this software, btw) and I’m trying to build an app that uses the API’s to call Dremio. I manually added the ‘Samples’ data source through the UI, but I’m having an issue with the API. I can’t seem to do the POST request to create a virtual dataset from any of the files in the source location.

Here’s my POST request:
payload = {
“entityType”: “dataset”,
“path”: [
“First Space”,
“test”
],
“type”: “VIRTUAL_DATASET”,
“sql”: “SELECT * FROM SF_incidents2016”,
“sqlContext”: [“Samples”, “samples.dremio.com”]
}
headers = {
‘content-type’: ‘application/json’
‘authorization’: f’_Dremio{token}’
}
response = requests.post(f’{dremioServer}/api/v3/catalog, data=json.dumps(payload), headers=headers)

This post request returns an error saying that “Table ‘SF_incidents2016’ not found”. I am very new to this program, but I thought I was following the documentation to the letter. Can anyone help me figure out what I’m missing?

Thank you

2

@mosiermt

That seems to be a problem with the SQL - dataset names need to be qualified, so in your case you want:

 "sql": "SELECT * FROM \"SF_incidents2016\"",

Note the " around SF_incidents2016.

3

I am still getting the same error here. I’ve tried adding the file type as well, but that doesn’t seem to do it either.

4

That is probably because the file name is SF_incidents2016.json, so you need:

 "sql": "SELECT * FROM \"SF_incidents2016.json\"",
5

Yeah, that’s what I was saying I tried. Still no luck.

Heres the complete error message from the docker logs:

2019-10-11 18:55:35,886 [out-of-band-observer] INFO query.logger - {“queryId”:“225f2fd8-285c-1e47-8d69-fc6c37b0c700”,“schema”:"[Samples, “samples.dremio.com”]",“queryText”:“SELECT * FROM “SF_incidents2016.json””,“start”:1570820135452,“finish”:1570820135881,“outcome”:“FAILED”,“username”:“admin”}

6

And you can open SF_incidents2016.json just fine in the UI?

7

yes I can, and it renders fully as well. I’ve been messing with it a bit and I think the problem is the “sqlContext” field. Changing it makes no difference, even if the path provided doesn’t exist, so I think that might have a part in this. Is there a way I can see in the logs what path the server is looking in?

8

The body should be:

{
  "entityType": "dataset",
  "type": "VIRTUAL_DATASET",
  "path": [
    "space",
    "datasetname"
  ],
  "sql": "SELECT * FROM \"SF_incidents2016.json\"",
  "sqlContext": [
    "Samples",
    "samples.dremio.com"
  ]
}