Connecting Python and Dremio (windows)

Dremio University
1

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)

2

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

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?

4

@MuffiSan For Windows,

Configure this way:

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

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.

6

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

@lenoyjacob alright, thanks a lot :slight_smile:

8

@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

Unbenannt
Unbenannt1368×540 156 KB

9

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

10

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

11

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

12

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)

13

Does the below file exist?

14

Yeah its is there in C folder