Python connectivity with Dremio using pyodbc

Dremio University
1

I am connecting dremio with Jupyter notebook and getting following error while connecting with pyodbc.

**Error : **
Error connecting to Dremio: (‘08S01’, ‘[08S01] [Apache Arrow][Flight SQL] (500) Flight returned unavailable error, with message: failed to connect to all addresses. Please ensure your encryption settings match the server. (500) (SQLDriverConnect)’)

I have explored the following link but still could not fix my isye

Following is the code

import pyodbc

Dremio connection details

dremio_host = ‘YYYYYYY’
username = ‘dremio_admin’
password = ‘XXXXXX’

Adjust DRIVER and PORT according to your setup

connection_string = f"“”
DRIVER={{Arrow Flight SQL ODBC Driver}};
PORT=32010; # Adjust this
ConnectionType=Direct;
HOST={dremio_host}; Arrow Flight SQL ODBC Driver
AuthenticationType=Plain;
UseEncryption =false;
TrustServerCertificate=yes;
UID={username};
PWD={password};
“”"

print(connection_string)

Connect to Dremio

try:
connection = pyodbc.connect(connection_string, autocommit=True)
print(“Connected to Dremio successfully.”)

# Execute a query
cursor = connection.cursor()
cursor.execute('SELECT * FROM "Data Platfrom Archive".ci1."ur_sms_traffic"')  # Adjust the query as needed
for row in cursor:
    print(row)

cursor.close()

except Exception as e:
print(f"Error connecting to Dremio: {e}")
finally:
if ‘connection’ in locals() and connection:
connection.close()
print(“Connection closed.”)

2

@aali Welcome to Dremio Community!

I have edited your post to remove your admin password. Please change your Admin password immediately as it has been exposed! Also please consider locking down access to your Dremio environment via a firewall as well.

For you question, there are two methods for a python environment:

via pyodbc:

# Install the driver
# !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 
# Find other drivers for other OS's here: https://download.dremio.com/arrow-flight-sql-odbc-driver/

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)

#Your query
sql = 'select * from sys.options limit 3'

data = pandas.read_sql(sql, cnxn)
print(data)

via arrow flight (recommended):

#!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.options limit 3'
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)

Hope that helps!