Writing a Dremio client

Dremio can be connected using REST API, ODBC and JDBC APIs. In this short blog, we'll see how we can use REST and ODBC.

REST API

Generating a Dremio token

import requests, json

url = "https://{{Dremio URL}}/apiv2/login"
token = ""
loginData = '''
{
	"userName": "''' + username + '''", 
	"password": "''' + password + '''"
}'''
headers = {'content-type':'application/json'}

response = requests.post(url, headers=headers, \
	data=loginData, verify=False)
response_status = response.status_code

if (response_status == 401):
	print ("Exception: 401!")
	raise ValueError("Exception: 401!")
elif (response_status == 500):
	print ("Exception: 500!")
	raise ValueError("Exception: 500!")
elif (response_status == 200):
	data = json.loads(response.text)
	# retrieve the login token
	token = '_dremio{authToken}'\
    	.format(authToken= data['token'])

Make Subsequent Calls

We can then call required REST APIs with the Dremio token received.

# Get Catalog by Path
url = "https://{{Dremio URL}}/api/v3/catalog"
headers = {'content-type':'application/json', \
      'authorization':'{authToken}'\
      .format(authToken=dremioToken)}
response = requests.get(url, headers=headers, verify=False)
data = json.loads(response.text)

ODBC

Dremio supports JDBC or ODBC interfaces. In this blog, we'll use ODBC to connect the server. We need to setup the ODBC driver as a prerequisite.

import pyodbc, pandas
host = "{{host name}}"
pwd = "{{token}}"
port = "{{dremio port i.e. 31010}}"
uid = "{{user id}}"
driver = "{{driver name}}"

cnxn = pyodbc.connect\
			("Driver={};\
			ConnectionType=Direct;\
			HOST={};PORT={};\
			AuthenticationType=Plain;\
			SSL=1;UID={};PWD={};\
			DisableCertificateVerification=1"\
			.format(driver, host,port,uid,pwd),\
			autocommit=True)
			
sql = '''SELECT * FROM "INFORMATION_SCHEMA"."TABLES"'''
dataframe = pandas.read_sql(sql,cnxn)
print (dataframe)

References:
REST API reference
Dremio ODBC drivers
Python integration with ODBC