Unable to connect to Dremio through demio_client using Jupyter Notebook on MacOS

Dremio University
2

Hey @BandanaPandey, welcome to Dremio Community. Letting you know that the (unofficial) dremio_client library hasn’t been updated in a while (potentially even deprecated). Here are a few ways instead (minimal code) to connect to Dremio:

Via ODBC (v22 and above only):

# !wget https://download.dremio.com/arrow-flight-sql-odbc-driver/arrow-flight-sql-odbc-driver-LATEST.x86_64.rpm
# !sudo yum localinstall -y arrow-flight-sql-odbc-driver-LATEST.x86_64.rpm 

import pyodbc
import pandas

host = 'your_host'
port = 32010
uid = 'your_user'
pwd = 'your_pass'

#If you're using a MacOS, the driver is located here -> /Library/Dremio/ODBC/lib/libarrow-flight-sql-odbc.dylib; the below is for linux:
driver = "/opt/arrow-flight-sql-odbc-driver/lib64/libarrow-odbc.so.0.9.1.168"

#Set UseEncryption accordingly below:
cnxn=pyodbc.connect("Driver={};ConnectionType=Direct;HOST={};PORT={};AuthenticationType=Plain;UID={};PWD={}".format(driver,host,port,uid,pwd),autocommit=True,UseEncryption=False)

sql = 'select * from sys.options limit 3'

data = pandas.read_sql(sql, cnxn)

print(data)

Via REST:
See more Dremio APIs here: Dremio

#!pip install requests

import requests
import json
import time

host = 'your_host'
port = '9047'
uid = 'your_user'
pwd = 'your_pass'

#Authenticate
headers = {'content-type': 'application/json'}
response = requests.post('http://'+host+':'+port+'/apiv2/login', headers=headers, data = json.dumps({"userName":uid,"password":pwd}))
headers={'content-type':'application/json', 'authorization':'_dremio'+response.json()['token']}

#Query
sql = 'select * from sys.options limit 3'
job_id = requests.post('http://'+host+':'+port+'/api/v3/sql', headers=headers, data = json.dumps({"sql":sql}))

#Wait till results; there are better ways to do this. For example, see how the old dremio client did it here: https://github.com/rymurr/dremio_client/blob/10e9b22f2cade17d25671b615a04b1c13752593f/dremio_client/util/query.py#L47...
time.sleep(5)
results = requests.get('http://'+host+':'+port+'/api/v3/job/'+job_id.json()['id']+'/results?offset=0&limit=500', headers=headers)

#Print
results.json()

Via Arrow Flight:

#!pip install pyarrow

import pyarrow
from pyarrow import flight
import pandas

host = 'your_host'
port = '32010'
uid = 'your_user'
pwd = 'your_pass'

#Connect
client = flight.FlightClient('grpc+tcp://' + host + ':' + port)

#Authenticate
bearer_token = client.authenticate_basic_token(uid, pwd) 
options = flight.FlightCallOptions(headers=[bearer_token])

#Query
sql= "SELECT * FROM sys.memory"
info = client.get_flight_info(flight.FlightDescriptor.for_command(sql),options)
reader = client.do_get(info.endpoints[0].ticket, options)

#Print
df=reader.read_all()
print(df)

Let me know if you need the ODBC script for Dremio versions lower than v22.

1 Like