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