Unable to connect to Dremio through demio_client using Jupyter Notebook on MacOS

BandanaPandey · August 30, 2022, 1:37pm

I am unable to connect to dremio using dremio_client library on Jupyter NoteBook

Below are the commands which I tried on Jupyter:

from dremio_client import init

client = init('~/.config/dremio_client/')
results = client.query('SELECT * FROM demo_table')
results

And this is the output which I am receiving, I have attached the screenshot below

Also I tried to debug it and I found that this api api/v3/catalog is the issue, it is returning HTML content,
So I tried to access the same api using curl like this:

curl -X GET http://localhost:9047/api/v3/catalog -H "Content-Type: application/json" -H "Authorization: user_token"

and it response of the above api was this:

<!--

    Copyright (C) 2017 Dremio Corporation

    Licensed under the Apache License, Version 2.0 (the "License");
    you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

        http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.

-->
<!DOCTYPE html>

<html>
  <head>
    <title>Dremio</title>
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta charset="utf-8">
    <meta http-equiv="Pragma" content="no-cache">
    <meta http-equiv="Expires" content="-1">
    <script type="text/javascript">
      window.dremioConfig = {
          serverEnvironment: "PRODUCTION",
          serverStatus: "OK",
          environment: "PRODUCTION",
          commit: "27f36e19b9c97f6e0da9de58baa9cefec6498535\nWith uncommitted changes:\nM dac/ui/npm-shrinkwrap.json",
          ts: "Wed Jul 19 2017 08:21:36 GMT+0000 (UTC)",
          language: undefined,
          useRunTimeLanguage: undefined,
          intercomAppId: "gdcxa2zo",
          shouldEnableBugFiling: false,
          shouldEnableRSOD: false,
          supportEmailTo: "",
          supportEmailSubjectForJobs: "",
          outsideCommunicationDisabled: false
        };
    </script>


    <link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png">
    <link rel="icon" type="image/png" href="/favicon-32x32.png" sizes="32x32">
    <link rel="icon" type="image/png" href="/favicon-16x16.png" sizes="16x16">
    <link rel="manifest" href="/manifest.json">
    <link rel="mask-icon" href="/safari-pinned-tab.svg" color="#55bfce">
    <meta name="theme-color" content="#2b3a4b">

  <link href="/style.45b736e736ded57c7d60156a62efd9ab.css" rel="stylesheet"></head>
  <body>
    <div id="root">
    </div>
  <script type="text/javascript" src="/vendor.4f9b93ca2e53d2a94faa.js"></script><script type="text/javascript" src="/bundle.4f9b93ca2e53d2a94faa.js"></script></body>
</html>

It would be very helpful, if someone can help me out here.
Thanks in advance

lenoyjacob · August 31, 2022, 1:42am

Hey @BandanaPandey, welcome to Dremio Community. Letting you know that the (unofficial) dremio_client library hasn’t been updated in a while (potentially even deprecated). Here are a few ways instead (minimal code) to connect to Dremio:

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

Via REST:
See more Dremio APIs here: Dremio

#!pip install requests

import requests
import json
import time

host = 'your_host'
port = '9047'
uid = 'your_user'
pwd = 'your_pass'

#Authenticate
headers = {'content-type': 'application/json'}
response = requests.post('http://'+host+':'+port+'/apiv2/login', headers=headers, data = json.dumps({"userName":uid,"password":pwd}))
headers={'content-type':'application/json', 'authorization':'_dremio'+response.json()['token']}

#Query
sql = 'select * from sys.options limit 3'
job_id = requests.post('http://'+host+':'+port+'/api/v3/sql', headers=headers, data = json.dumps({"sql":sql}))

#Wait till results; there are better ways to do this. For example, see how the old dremio client did it here: https://github.com/rymurr/dremio_client/blob/10e9b22f2cade17d25671b615a04b1c13752593f/dremio_client/util/query.py#L47...
time.sleep(5)
results = requests.get('http://'+host+':'+port+'/api/v3/job/'+job_id.json()['id']+'/results?offset=0&limit=500', headers=headers)

#Print
results.json()

Via Arrow Flight:

#!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.memory"
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)

Let me know if you need the ODBC script for Dremio versions lower than v22.

BandanaPandey · August 31, 2022, 7:24am

Hey @lenoyjacob Thanks for the reply.

I tried the same via Arrow flight on Mac

import pyarrow
from pyarrow import flight
import pandas

host = 'localhost'
port = '31010'
uid = 'bandana'
pwd = 'dummy_pwd'

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

But I got this error:

BandanaPandey · August 31, 2022, 8:23am

Also @lenoyjacob
when I tried via REST Api

#!pip install requests

import requests
import json
import time

host = 'your_host'
port = '9047'
uid = 'your_user'
pwd = 'your_pass'

#Authenticate
headers = {'content-type': 'application/json'}
response = requests.post('http://'+host+':'+port+'/apiv2/login', headers=headers, data = json.dumps({"userName":uid,"password":pwd}))
headers={'content-type':'application/json', 'authorization':'_dremio'+response.json()['token']}

#Query
sql = 'select * from sys.options limit 3'
job_id = requests.post('http://'+host+':'+port+'/api/v3/sql', headers=headers, data = json.dumps({"sql":sql}))
print(job_id.text)

#Wait till results; there are better ways to do this. For example, see how the old dremio client did it here: https://github.com/rymurr/dremio_client/blob/10e9b22f2cade17d25671b615a04b1c13752593f/dremio_client/util/query.py#L47...
time.sleep(5)
results = requests.get('http://'+host+':'+port+'/api/v3/job/'+job_id.json()['id']+'/results?offset=0&limit=500', headers=headers)

#Print
results.json()

And printed the job_id

The print statement is returning this:

<!--

    Copyright (C) 2017 Dremio Corporation

    Licensed under the Apache License, Version 2.0 (the "License");
    you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

        http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.

-->
<!DOCTYPE html>

<html>
  <head>
    <title>Dremio</title>
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta charset="utf-8">
    <meta http-equiv="Pragma" content="no-cache">
    <meta http-equiv="Expires" content="-1">
    <script type="text/javascript">
      window.dremioConfig = {
          serverEnvironment: "PRODUCTION",
          serverStatus: "OK",
          environment: "PRODUCTION",
          commit: "27f36e19b9c97f6e0da9de58baa9cefec6498535\nWith uncommitted changes:\nM dac/ui/npm-shrinkwrap.json",
          ts: "Wed Jul 19 2017 08:21:36 GMT+0000 (UTC)",
          language: undefined,
          useRunTimeLanguage: undefined,
          intercomAppId: "gdcxa2zo",
          shouldEnableBugFiling: false,
          shouldEnableRSOD: false,
          supportEmailTo: "",
          supportEmailSubjectForJobs: "",
          outsideCommunicationDisabled: false
        };
    </script>


    <link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png">
    <link rel="icon" type="image/png" href="/favicon-32x32.png" sizes="32x32">
    <link rel="icon" type="image/png" href="/favicon-16x16.png" sizes="16x16">
    <link rel="manifest" href="/manifest.json">
    <link rel="mask-icon" href="/safari-pinned-tab.svg" color="#55bfce">
    <meta name="theme-color" content="#2b3a4b">

  <link href="/style.45b736e736ded57c7d60156a62efd9ab.css" rel="stylesheet"></head>
  <body>
    <div id="root">
    </div>
  <script type="text/javascript" src="/vendor.4f9b93ca2e53d2a94faa.js"></script><script type="text/javascript" src="/bundle.4f9b93ca2e53d2a94faa.js"></script></body>
</html>

Because of which it is throwing the below mentioned error:

---------------------------------------------------------------------------
JSONDecodeError                           Traceback (most recent call last)
File /usr/local/lib/python3.10/site-packages/requests/models.py:971, in Response.json(self, **kwargs)
    970 try:
--> 971     return complexjson.loads(self.text, **kwargs)
    972 except JSONDecodeError as e:
    973     # Catch JSON-related errors and raise as requests.JSONDecodeError
    974     # This aliases json.JSONDecodeError and simplejson.JSONDecodeError

File /usr/local/lib/python3.10/site-packages/simplejson/__init__.py:525, in loads(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, use_decimal, **kw)
    521 if (cls is None and encoding is None and object_hook is None and
    522         parse_int is None and parse_float is None and
    523         parse_constant is None and object_pairs_hook is None
    524         and not use_decimal and not kw):
--> 525     return _default_decoder.decode(s)
    526 if cls is None:

File /usr/local/lib/python3.10/site-packages/simplejson/decoder.py:370, in JSONDecoder.decode(self, s, _w, _PY3)
    369     s = str(s, self.encoding)
--> 370 obj, end = self.raw_decode(s)
    371 end = _w(s, end).end()

File /usr/local/lib/python3.10/site-packages/simplejson/decoder.py:400, in JSONDecoder.raw_decode(self, s, idx, _w, _PY3)
    399         idx += 3
--> 400 return self.scan_once(s, idx=_w(s, idx).end())

JSONDecodeError: Expecting value: line 1 column 1 (char 0)

During handling of the above exception, another exception occurred:

JSONDecodeError                           Traceback (most recent call last)
Input In [5], in <cell line: 24>()
     18 #print(job_id.text)
     19 
     20 # print(job_id.json())
     21 
     22 #Wait till results; there are better ways to do this. For example, see how the old dremio client did it here: https://github.com/rymurr/dremio_client/blob/10e9b22f2cade17d25671b615a04b1c13752593f/dremio_client/util/query.py#L47...
     23 time.sleep(5)
---> 24 results = requests.get('http://'+host+':'+port+'/api/v3/job/'+job_id.json()['id']+'/results?offset=0&limit=500', headers=headers)

File /usr/local/lib/python3.10/site-packages/requests/models.py:975, in Response.json(self, **kwargs)
    971     return complexjson.loads(self.text, **kwargs)
    972 except JSONDecodeError as e:
    973     # Catch JSON-related errors and raise as requests.JSONDecodeError
    974     # This aliases json.JSONDecodeError and simplejson.JSONDecodeError
--> 975     raise RequestsJSONDecodeError(e.msg, e.doc, e.pos)

JSONDecodeError: Expecting value: line 1 column 1 (char 0)

lenoyjacob · August 31, 2022, 3:43pm

@BandanaPandey What version of Dremio are you on?

BandanaPandey · August 31, 2022, 4:10pm

Hey @lenoyjacob

I have downloaded the community edition on mac,

lenoyjacob · August 31, 2022, 4:29pm

@BandanaPandey That’s possibly the culprit. v1.0.8 is old.

Try again on something newer:
v22.1.1 → https://download.dremio.com/community-server/22.1.1-202208230402290397-a7010f28/dremio-community-22.1.1-202208230402290397-a7010f28.tar.gz
v21.2.0 → https://download.dremio.com/community-server/21.2.0-202205262146080444-038d6d1b/dremio-community-21.2.0-202205262146080444-038d6d1b.tar.gz

BandanaPandey · September 1, 2022, 6:10am

Hey @lenoyjacob ,
Yes now the rest apis are working after I have installed the newer version.
Thank You so much.

Let me try with arrow flight.

Topic		Replies	Views
ODBC Demio Connector for MongoDB	25	2749	September 15, 2018
Connect Dremio with ipython notebook	8	4711	May 28, 2022
Unable to get dremio-client to work locally	6	1380	September 9, 2022
Python connectivity with Dremio using pyodbc Dremio University	1	165	February 23, 2024
Connect to Dremio Cloud using Arrow Flight, from Jupyter Notebook? Dremio Cloud	4	1491	July 19, 2022

Unable to connect to Dremio through demio_client using Jupyter Notebook on MacOS

Related Topics