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.
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.
3.Click Test
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:
2.The ODBC connection code are generated automatically in scripts
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.
