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

Dremio University
1

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

Screenshot 2022-08-30 at 7.00.25 PM
Screenshot 2022-08-30 at 7.00.25 PM2338×1584 503 KB

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

2

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.

1 Like
3

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:

Screenshot 2022-08-31 at 12.54.07 PM
Screenshot 2022-08-31 at 12.54.07 PM2042×578 111 KB

4

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

@BandanaPandey What version of Dremio are you on?

6

Hey @lenoyjacob

I have downloaded the community edition on mac,

Screenshot 2022-08-31 at 9.38.42 PM
Screenshot 2022-08-31 at 9.38.42 PM1372×892 68.9 KB

7

@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

8

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.

9

hi @BandanaPandey ,
Were you able to connect through Arrow flight? Cause I’m also facing the same issue