REST API, help needed

1

Can please someone explain this part of the code ?

path = [‘"@elbert"’, ‘wta’, ‘matches’]
my username: omi
DB: test-space
Folder: nametesting
table: abc_testing.txt

def querySQL(query):
queryResponse = apiPost(‘sql’, body={‘sql’: query})
jobid = queryResponse[‘id’]
return jobid

path = [‘"@elbert"’, ‘wta’, ‘matches’]
path = ‘.’.join([str(x) for x in path])
query = “SELECT * FROM {source} WHERE winner_name = ‘Serena Williams’ or loser_name = ‘Serena Williams’”.format(source=path)
jobid = querySQL(query)

2

It’s the fully qualified name of the table or view.
@elbert = user’s home folder
wta = sub-folder
matches = table

3

Thank you for replying back. So I have been using this format but I get key error:id every single time when I run this part of the code. Any thoughts on that?

Also, @albert could be the bucket name?

4

@umar If you open the failed jobs, you will see download profile. Can you send the downloaded zip file and we can see the exact error

Thanks
Bali

5

I don’t see download profile because I have not made it to that step yet. Below is the format of my code that I took from Dremio REST API | Dremio this website

import json
import requests

username = ‘’
password = ‘’
headers = {‘content-type’:‘application/json’}
dremioServer = ‘https://myserver/apiv2’ # I took the port out

def apiGet(endpoint):
return json.loads(requests.get(‘{server}/apiv2/{endpoint}’.format(server=dremioServer, endpoint=endpoint), headers=headers).text)

def apiPost(endpoint, body=None):
text = requests.post(‘{server}/apiv2/{endpoint}’.format(server=dremioServer, endpoint=endpoint), headers=headers, data=json.dumps(body)).text

if (text):
return json.loads(text)
else:
return None

def apiPut(endpoint, body=None):
return requests.put(‘{server}/apiv2/{endpoint}’.format(server=dremioServer, endpoint=endpoint), headers=headers, data=json.dumps(body)).text

def apiDelete(endpoint):
return requests.delete(‘{server}/apiv2/{endpoint}’.format(server=dremioServer, endpoint=endpoint), headers=headers)

def login(username, password):

we login using the old api for now

loginData = {‘userName’: username, ‘password’: password}
response = requests.post(‘http://demo.drem.io:9047/apiv2/login’, headers=headers, data=json.dumps(loginData))
data = json.loads(response.text)

retrieve the login token

token = data[‘token’]
return {‘content-type’:‘application/json’, ‘authorization’:‘_ dremio{authToken}’.format(authToken=token)}

headers = login(username, password)

I used apiv2 instead of api/v3 becuase api/v3 was not working for me. Since logon is still using apiv2 so, I made the changes for rest of the code. Upto this point my code runs fine even if I use the port id or don’t. I do see my userid, name, email, token printed, but the job fails when I run the next part of the code shown below.

def querySQL(query):
queryResponse = apiPost(‘sql’, body={‘sql’: query})
jobid = queryResponse[‘id’]
return jobid

path = [‘"@myusername"’, ‘mainbucket’, ‘foldername’, ‘tablename’]
path = ‘.’.join([str(x) for x in path])
query = ‘’‘SELECT * FROM “@myusername”.mainbucket.foldername.tablename’‘’.format(source=path)
jobid = querySQL(query)

I get keyerror id by running the above piece of code. if i put port back here dremioServer = ‘https://myserver/apiv2’, I get ('connection aborted., remote disconnected (‘remote end closed connection without response’)

How do I resolve this issue? Is this has to do with the code or the way I’m putting my sql code in there?
I also tried different way of putting sql code where i took username out ( ‘’‘SELECT * FROM mainbucket.foldername.tablename’‘’.format(source=path) ) but this did not work either.

6

@umar It does look like a formatting issue, can you first run the SQL on UI, make sure it works then grab that and use it via API?

7

select * from “mainbucket”.“foldername”.“tablename” is the query that works for me in dremio UI.

Below is how I implement this query in rest api code
query = ‘’’ SELECT * FROM “mainbucket”.“foldername”.“tablename” ‘’'.format(source=path)

I’m still getting connection aborted error. Can you please assist me with the format I should be using in the code?

8

@umar Are you using the below API?

https://docs.dremio.com/software/rest-api/sql/