Skip to main content

Development

How to Connect Hortonworks Hive from Qlikview with ODBC driver

As with most BI tools, QlikView can use Apache Hive (via ODBC connection) as the SQL access to data in Hadoop. Here we are going to talk about qlikview how to connect Hortonworks Hive via ODBC.

Prerequisites

1.Those are versions of each component we installed in Hortonworks

Hue HDP Hadoop Hive-Hcatalog Ambari HBase Hortonworks ODBC Driver
2.3.1 2.1.2 2.4.0 0.13.0 1.5.1 0.98.0 1.4.8.1008

2.We need to make sure HiveServer2 started.

01

3.Here are the configuration for hive server2. Please note the authentication is NONE.

Key Value
hive.server2.authentication NONE
hive.server2.enable.doAs FALSE
hive.server2.long.polling.timeout 5000
hive.server2.max.start.attempts 30
hive.server2.table.type.mapping CLASSIC
hive.server2.tez.default.queues default
hive.server2.tez.initialize.default.sessions FALSE
hive.server2.tez.sessions.per.default.queue 1
hive.server2.thrift.http.max.worker.threads 500
hive.server2.thrift.http.min.worker.threads 5
hive.server2.thrift.http.path cliservice
hive.server2.thrift.http.port 10001
hive.server2.thrift.max.worker.threads 500
hive.server2.thrift.min.worker.threads 5
hive.server2.thrift.port 10000

 

Configure ODBC connection

1.Install the 64 bits Hortonworks Hive ODBC Driver, you can download ODBC Driver from here.

2.Configure ODBC connection

Open Control Panel, search for ODBC and open ODBC Data Source Administrator, in System DSN tab click Add, configure the data source as below, click OK.

02

3.Click Test

03

Connect to ODBC data source

1.Open QlikView 11 client, (close the Wizard if it appears), click File -> Edit Script, in Data tab choose ODBC as Database and click Connect, select the Data Source you created in previous step and type the User ID/Password:

04

2.The ODBC connection code are generated automatically in scripts

05

3.Click File -> Reload to load data

Tips for Hive ODBC Connection Issue

The ODBC connection might failed for some reasons. Here are some tips for trouble shooting.

1.Make sure hive server2 started.

a) You can verify that port 10000 is in use once you start hiveserver2.

sudo netstat -tulpn | grep 10000

b) HiveServer2 supports a new command shell Beeline that works with HiveServer2. It’s a JDBC client that is based on the SQLLine CLI.

bin/beeline

!connect jdbc:hive2://HadoopLab2:10000

2.Make sure authentication mechanism of hive server2 is proper. You should select mechanism base on hive configuration file. Here we set ‘NONE’ in configuration file. But when mechanism ‘No Authentication’ is selected, the test results always are timeout, and when ‘username’ selected, any value entered in username input box, test result is successfully. It’s weird.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us
TwitterLinkedinFacebookYoutubeInstagram