Connecting Python and Dremio (windows)

MuffiSan · October 24, 2022, 10:40am

Im working on D103 right now and i´m stuck at the python part. In the example the driver path gets called to a file named “libdrillodbc”. How does it work on windows? This file does not exist with the ODBC Windows installation. The task only speaks of using Dremio Connector.

Python file so far:

import pyodbc
Import pandas as pd

host=‘XXX’
port=XXX
uid =‘consumers’
pwd = ‘dremio123’
driver = ‘???’
cnxn = pyodbc.connect(“Driver={};ConnectionType=Direct;HOST={};PORT={};AuthenticationType=Plain;UID={};PWD={}”.format(driver,host,port,uid,pwd),autocommit=True)

print(data)

lenoyjacob · October 24, 2022, 3:40pm

Hi @MuffiSan, Welcome to the Dremio Community. See if the following code will help you:

Via ODBC (Dremio 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)

MuffiSan · October 24, 2022, 3:58pm

Hey i just startet with Dremio.
I´m on Windows and installed the odbc driver but on windows there is no libarrow-odbc file. What is the input for driver = “???” on Windows?

lenoyjacob · October 24, 2022, 4:02pm

@MuffiSan For Windows,

Configure this way:

Download & Install Driver: Dremio
Configure ODBC: Dremio

MuffiSan · October 24, 2022, 5:43pm

I did, but i dont know what this driver = “/opt/arrow-flight-sql-odbc-driver/lib64/libarrow-odbc.so.0.9.1.168” is supposed to be on Windows. I the Windows installation i dont get this file.

lenoyjacob · October 24, 2022, 7:04pm

@MuffiSan, if you configured the DSN on Windows (step 2 in my post above) and the connection to your host works (you can test this while configuring the DSN); then you don’t need to specify the driver. Just use the DSN instead.

So something like this would work on Dremio v22 & above:

import pyodbc
import pandas

#Windows
DSN = "Arrow Flight SQL ODBC DSN"
cnxn=pyodbc.connect(DSN=DSN,autocommit=True)

sql = 'select * from sys.options limit 3'
data = pandas.read_sql(sql, cnxn)
print(data)

Here’s a screenshot:

MuffiSan · October 25, 2022, 7:43am

@lenoyjacob alright, thanks a lot

MuffiSan · October 25, 2022, 11:50am

@lenoyjacob For some reason i get this Error. I filled in the ODBC URL i got when launching Virtual Lab. Its still running for 18 hours. Is this happening because of the virtual Lab or did i do something wrong here? ODBC URL is: 104.196.50.166:19014

lenoyjacob · October 25, 2022, 3:45pm

@MuffiSan, here are a few ideas:

→ Go to the Advanced tab (in configuring DSN) and confirm whether Encryption is needed or not. Turn off if no encryption set-up was done.
→ Confirm Dremio server version is at least v22.0.0.
→ Check for any firewalls on the Windows machine blocking outgoing access.

MuffiSan · October 25, 2022, 6:00pm

@lenoyjacob ah i think thats the problem with the Dremio University Lab.

aali · February 23, 2024, 7:36pm

I am using windows and facing same kind of error
InterfaceError: (‘IM003’, ‘[IM003] Specified driver could not be loaded due to system error 127: The specified procedure could not be found. (Arrow Flight SQL ODBC Driver, C:\Softwares\arrow_flight\arrow-flight-sql-odbc.dll). (160) (SQLDriverConnect)’)
Any idea,how i can fix

aali · February 23, 2024, 8:07pm

I am using windows and odbc connection is successfully tested. Still I am unable to connect and getting below error
InterfaceError: (‘IM003’, ‘[IM003] Specified driver could not be loaded due to system error 127: The specified procedure could not be found. (Arrow Flight SQL ODBC Driver, C:\Softwares\arrow_flight\arrow-flight-sql-odbc.dll). (160) (SQLDriverConnect)’)

import pyodbc
import pandas

#Windows
DSN = “Arrow Flight SQL ODBC DSN”
cnxn=pyodbc.connect(DSN=DSN,autocommit=True)
print (cnxn)

query = ‘SELECT * FROM “Data Platfrom Archive”.ci1.ur_sms_traffic limit 10’ # Adjust the query as needed

sql = ‘select * from sys.options limit 3’

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

lenoyjacob · February 23, 2024, 8:22pm

Does the below file exist?

aali · February 24, 2024, 10:11pm

Yeah its is there in C folder

astrologos · April 29, 2025, 7:36pm

I am having the same issue on Windows Server 2022 v 10.0.20348. Was this ever resolved?
Please assist!

Driver installed: arrow-flight-sql-odbc-0.9.4.1186-win64.msi

arrow-flight-sql-odbc.dll exists in: “C:\Program Files\Dremio Connector\Arrow Flight SQL ODBC Driver”

“C:\Program Files\Dremio Connector\Arrow Flight SQL ODBC Driver” added to system PATH

Visual Studio C++ Redistributable 2015-2022 x64 installed: https://aka.ms/vs/17/release/vc_redist.x64.exe

ODBC connection set up and tested successfully in ODBC Data Source Administrator x64.

Code:

import pyodbc
cnxn = pyodbc.connect("DSN=Apache Arrow Flight SQL", autocommit=True)
cursor1 = cnxn.cursor()

Output:

python odbc_connect.py
Traceback (most recent call last):
  File "D:\Golden Copy\odbc_connect.py", line 2, in <module>
    cnxn = pyodbc.connect("DSN=Apache Arrow Flight SQL", autocommit=True)
pyodbc.InterfaceError: ('IM003', '[IM003] Specified driver could not be loaded due to system error  127: The specified procedure could not be found. (Arrow Flight SQL ODBC Driver, C:\\Program Files\\Dremio Connector\\Arrow Flight SQL ODBC Driver\\arrow-flight-sql-odbc.dll). (160) (SQLDriverConnect)')

astrologos · April 30, 2025, 1:21pm

Your example code returns the same error for me:

klimnyk · May 5, 2025, 10:18pm

try sqlalchemy-dremio

import pandas as pd
from sqlalchemy import create_engine, text

DREMIO_UID = "your_dremio_username"
DREMIO_PWD = "your_dremio_password"
DREMIO_SERVER = "your_dremio_server_address"
DREMIO_PORT = "32010"
CONNECTION_PARAMS = 'UseEncryption=false&disableCertificateVerification=true'

connection_string = f'dremio+flight://{DREMIO_UID}:{DREMIO_PWD}@{DREMIO_SERVER}:{DREMIO_PORT}?{CONNECTION_PARAMS}'

engine = None
try:
    print("Attempting to create SQLAlchemy engine...")
    engine = create_engine(connection_string, echo=False)
    print("Engine created successfully.")

    sql_query = 'SELECT 1'

    print(f"Attempting to execute query: {sql_query}")
    with engine.connect() as connection:
        print("Connection established.")
        result = connection.execute(text(sql_query))
        print("Query executed.")

        if result.returns_rows:
            print("Query returned data. Fetching results...")
            df = pd.DataFrame(result.fetchall(), columns=result.keys())
            print("Query results:")
            print(df)
        else:
            print("Query did not return any data.")

except Exception as e:
    print(f"\nAn error occurred: {e}")
    print("Check your connection credentials (username, password, server, port).")
    print("Ensure the Dremio server is accessible and the 'dremio-flight-connector' is installed (`pip install dremio-flight-connector sqlalchemy`).")

finally:
    if engine:
        print("\nClosing database connection...")
        engine.dispose()
        print("Connection closed.")

Topic		Replies	Views
Regarding ODBC connection to dremio from python Dremio University	1	1809	December 30, 2022
Connect Dremio with ipython notebook	8	4979	May 28, 2022
Connecting Dremio with Python Dremio Cloud	1	146	August 26, 2024
ODBC vs Arrow flight for python?	5	2288	February 12, 2021
Connect to dremio from python on a macos	2	1205	November 3, 2017

Connecting Python and Dremio (windows)

sql = ‘select * from sys.options limit 3’

Related topics