Connecting Python and Dremio (windows)

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)

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)

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?

@MuffiSan For Windows,

Configure this way:

  1. Download & Install Driver: Dremio
  2. Configure ODBC: Dremio

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.

@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:
image

1 Like

@lenoyjacob alright, thanks a lot :slight_smile:

@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

@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.

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

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

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)

Does the below file exist?

Yeah its is there in C folder

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)')

Your example code returns the same error for me:

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.")