CODEWITHSUNDEEP
pythonhivepyodbc
Gaurang Shah
Gaurang Shah

Reputation: 12910

Python - connect to hive using Pyodbc

I am trying to connect to hive server using PyODBC however I am getting error. Not sure how to resolve it.

HiveServer is running on EMR.

Code

import pyodbc
cnxnstr = 'Driver={/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so};' \
          'HiveServerType=2;' \
          'Host=10.164.7.50;' \
          'Port=10001;TransportMode=HTTP;' \
          'UID=gshah03;' \
          'AuthMech=3;' \
          'ThriftTransport=SASL;' \
          'Schema=my_database;' \
           'PWD=mypass$'
cnxn = pyodbc.connect(cnxnstr, autocommit=True)

Error

Error: ('HY000', '[HY000] [unixODBC][Hortonworks][ThriftExtension] (5) Error occurred while contacting server: invalid sasl status. This could be because you are trying to establish a non-SSL connection to a SSL-enabled server. (5) (SQLDriverConnect)')

I tried adding this option as well, but not working 'sslverify=0; Option=3;'

Hive-Site.xml

<property>
    <name>hive.server2.enable.doAs</name>
    <value>false</value>
  </property>
  <property>
    <name>hive.server2.thrift.port</name>
    <value>10000</value>
  </property>
  <property>
    <name>hive.server2.thrift.http.port</name>
    <value>10001</value>
  </property>

Update:

with 10000 port, I am getting following error:

Error

Error: ('HY000', '[HY000] [unixODBC][Hortonworks][Hardy] (34) Error from server: connect() failed: Connection refused. (34) (SQLDriverConnect)')

Upvotes: 0

Views: 3250

Answers (1)

Aditya
Aditya

Reputation: 13

  1. Create odbc driver dsn (Navigate through ODBC data source Administrator 64 bit )under User DSN.
  2. Click on Add (select cloudera ODBC driver for Apache Hive, if its not present download the latest one from cloudera site) 3.Server/Hosts: Your Server Name DSN Name and Description: You can give you as per your wish Port: Port Number Authentication: Username and Password Transport: HTTP HTTP Path: HTTP path (In HTTP options)       Require SSL: Select cacerts.pem(Its present in clouder odbc driver folder-lib) and also check self signed server certificate - Click ok
  3. Click on test button , you should see a successful connection

Once the dsn is created , use that dsn in python using pyodbc and build a connection. Using this connection you can query out you hive tabes.

Upvotes: 1

Related Questions