Business Discovery and Visualizing Your Data in HDP Using QlikView

Qlik™ Connect to HDP

Introduction

Welcome to the QlikView (Business Discovery Tools) tutorial developed by Qlik™. The tutorial is designed to help you get connected with QlikView within minutes, to access data from the Hortonworks Sandbox or Hortonworks Data Platform (HDP). QlikView will allow you to immediately gain personalized analytics and discover insights into data residing in the Sandbox or HDP. As with most BI tools, QlikView can use Apache Hive (via ODBC connection) as the SQL access to data in Hadoop.

QlikView has two modes of ingesting data

  • Data loaded to QlikView In-Memory Associative Data Store
    • Pros – Sub Second Response Time, Data Compression, only accesses Data from HDP at time of load (Less Load on HDP)
    • Con – Data Size Limitation, Data Duplication
  • QlikView Hybrid Solution – QlikView Direct Discovery on top of Hadoop
    • Pro – Ability to analyze Big Data. Data is left in HDP, workload is pushed down to HDP
    • Con – Response time is higher. May have to use Tez to dramatically improve response time on large datasets

This Tutorial will cover loading data into Qlikview In-Memory Associative Data Store; with simple modifications to the load script, the application can be configured to run the Qlikview Hybrid Solution where data resides inside HDP.

Prerequisites

Prerequisite 1: Download QlikView Desktop Personal Edition

Prerequisite 2: Working HDP Cluster (Please choose one of the below. This tutorial uses Sandbox)

Prerequisite 3: Download HDP Hive ODBC Driver

Overview

Step 1:  Install QlikView Desktop Personal Edition. (QlikView is Windows Only Application) The current version of QlikView of this writing is QlikView 11 SR5.

Step 2:  Install HDP Hive ODBC driver and Connect QlikView to HDP.

  • Execute file HortonworksHiveODBC64.msi downloaded from HDP Hive ODBC Driver
  • Click Next on the Hortonworks Hive ODBC Driver Setup Wizard Welcome screen, and complete the wizard setup. Reboot as requested.
  • Open ODBC Manager to set up ODBC Connection with HDP. Click “Add” and New Data Source Window will show up.
  • Select Hortonworks Hive ODBC Driver
  • Enter credentials for HDP Sandbox. Use Hive Server Type 2 to support Kerberos Authentication.
  • Enter Credentials and test.

Step 3: QlikView Integration with HDP Hive – See the associated tutorial with step by step screen shots

In this section, we will explore how QlikView can explore data in HDP. The tutorial will walk through steps to setup QlikView to Load Data from Hive. Please review HDP Tutorial and load sample data sets into Hortonworks Hive before starting this section.

1. Start QlikView Desktop Personal Edition

Description: HDP ODBC TEST

2. File –> New Click Cancel when the Wizard Comes up

Description: HDP ODBC TEST

3. Click on the Script Icon to Open QlikView Script

Description: HDP ODBC TEST

4. Click Connect and QlikView ODBC Manager will come up.

Description: HDP ODBC TEST

5. Select your HIVE ODBC Connection enter your credentials and test your connection

Description: HDP ODBC TEST

6. Click OK to clear connection test and OK on the QlikView ODBC Manager and a connection string will appear on Script Editor

Description: HDP ODBC TEST

7. Click Select and QlikView Selection Window will Appear with all of your Tables

Description: HDP ODBC TESTDescription: HDP ODBC TEST

8. Choose your sample table and select okay to add to HIVE table load script for selected sample table.

Description: HDP ODBC TEST

9. Click Okay to exit Script Editor or Reload to Load Data immediately into QlikView

Reload will force user to save the QlikView document with a new name.
Description: HDP ODBC TEST

10. Click Reload

11. The QlikView Document will pull in HDP Table into QlikView in Memory Associative Data Store. In this example we will pull in **_sample_07 _**table. Please note 823 lines were extracted from HDP Hive and inserted into QlikView In-Memory Associative Data Store. Description: HDP ODBC TEST

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

12. Right click and choose “select fields”

Description: HDP ODBC TEST

13. Click Add All to add all fields to current QlikView Sheet.

Description: HDP ODBC TEST

14. Verify all fields were successfully loaded by inspecting the data in QlikView. Select Items inside QlikView to see the Associative nature of QlikView.

Description: HDP ODBC TEST

Once the data is imported you are ready to go. Now you can use QlikView to create insightful data visualization applications with data from Hadoop, Hortonworks Sandbox and Hortonworks Data Platform.

You can Download QlikView Files for this Tutorial in this Link: Download Qlik HDP Sample QVW

More Information

To help you learn more about QlikView view these videos:

New To QlikView Videos – Series of 1 to 3 minutes video on QlikView Topics from loading data to designing charts to creating buttons.

Leave a Reply

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

If you have specific technical questions, please post them in the Forums

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Try this tutorial with :

These tutorials are designed to work with Sandbox, a simple and easy to get started with Hadoop. Sandbox offers a full HDP environment that runs in a virtual machine.