Open in app

Sign in

Write

Sign in

Connecting Qlik Sense to Amazon Athena using OpenLink’s Enterprise Edition ODBC -JDBC Bridge (32- and 64-bit)

Daniel Heward-Mills
OpenLink ODBC, JDBC, ADO.NET Data Access Drivers Blog
Published in
8 min readOct 18, 2017

Situation Analysis

Qlik Sense is a Guided Analytics Tool that enables its users to discover deeper insights by building their own rich, guided analytics applications using a flexible, business-ready solution.

Qlik Sense natively supports an ODBC data access interface, which provides access to numerous data sources. However, it doesn’t provide access to data sources that are only JDBC-accessible (and usable with JDBC 1.8), which leaves this genre of data source inaccessible without appropriate middleware.

Athena, an AWS cloud-hosted Query Service for data saved to Amazon S3 buckets, is an example of a data source that’s only JDBC-accessible.

Challenge

During this era of Digital Transformation, Organizations are increasingly seeking to monitor their KPIs (Key Performance Indicators) on dashboards (produced with tools like Qlik Sense) that access data from a variety of system types that includes:

  • Systems Of Record (SOR) oriented data — typically ODBC-accessible, SQL-RDBMS-hosted data
  • Systems Of Engagement (SOE) oriented data — typically activity logs associated with customer and prospect interactions (which is where Athena comes into play, due to broad use of S3 buckets for activity log storage)

Solution

An ODBC-JDBC Bridge Driver that enables provides 32- and 64-bit ODBC-compliant applications with access to JDBC data sources associated with 32- or 64-bit Java Virtual Machine (JVM) instances.

In this post, I demonstrate the how the Enterprise Edition of OpenLink’s ODBC-JDBC Bridge Drivers applies its Multi-Tier architecture and unique strengths to solving the following challenges:

  1. Qlik Senseconnectivity to Amazon Athena via ODBC
  2. Negating confusion posed by 32- and 64-bit editions of Qlik Sense on Windows that need to connect to Amazon Athena via a 32- or 64-bit JVM instance

The Enterprise Edition of the OpenLink ODBC-JDBC Bridge Driver provides a multi-tier architecture which enables mixing of 32-bit client and 64-bit server (or 64-bit client and 32-bit server) components; i.e., it is possible for you to configure an ODBC Data Source Name (DSN) based on a 32-bit ODBC driver for use by a 32-bit ODBC application (e.g., Qlik Sense’s 32-bit edition), that connects to a JDBC Data Source in a 32- or 64-bit JVM. Likewise, you can configure an ODBC DSN bound to 64-bit ODBC driver for use by a 64-bit ODBC application (e.g. Qlik Sense), that connects to a JDBC Data Source in a 32-bit or 64-bit JVM.

Installation

Prequisites

ODBC Data Source Name (DSN) Setup & Configuration

Request Broker and ODBC-JDBC Bridge Agent (Server Components)

Database Agent Installation

  • Download and install the latest JDBC 1.8 Database Agent binary. ODBC connections to Athena will not work without this update being installed

Bridge Agent Configuration

This section is optional, for basic connections to most JDBC data sources. For purposes of this tutorial, you can skip ahead to the Data Source Name (DSN) Configuration section.

Make sure to enable pop-ups in your browser; otherwise, the following setup wizard window will not appear.

[1] Access the web-based administrator at http://localhost:8000 and navigate to Server Components Administration → Database Agent Administration → Database Settings By Wizard. When asked to authenticate, use the default username and password (that is, admin and admin), or whatever you set during installation.

[2] Authenticate using your administrator credentials. The default username and password are both “admin”.

[3] Select the appropriate JDBC Bridge Agent for your Java environment. The numbers here refer to the common JDK/JRE versioning; for Java 8, a/k/a JDK/JRE 1.8, you should start with generic_jdbc18.

[4] Add a description/comment for this agent configuration, if desired.

[5] Authentication credentials can be left blank for this tutorial. If set here, these values will over-ride client-provided username and password.

Various options are available in several panes that follow. These are used to improve compatibility with various ODBC applications and JDBC drivers that don’t fully comply with their respective standards. None of these are necessary for this tutorial.

[6] Navigate to the Client-Server Process Mapping & Control pane, and set a max number of Openlink Clients to be accepted by a single instance of the bridge agent. If an additional Client connection is requested and permitted by license and other settings, this will cause an additional instance of the Agent to be launched.

[7] Enable “Emulate Prepared Queries” functionality (via Environment Variable setting) which is mandatory for any JDBC Driver that isn’t equipped with “Prepared Query” functionality e.g., the current JDBC Driver for Athena.

[8] Configure ODBC-JDBC Agent to support up to 5 concurrent client connections per instance.

[9] Reinitialize configuration settings to reflected changes above with regards to subsequent ODBC sessions.

[10] Exit the wizard, and an ODBC DSN can now be configured.

Client Component Configuration

Data Source Name (DSN) for Generic ODBC Driver

[11] Configure JDBC Connectivity Settings for the “JDBC 1.8” connectivity domain.

[12] Ensure that ODBC DSN settings are functional.

Connecting Qlik Senseto Amazon Athena

[13] Open Qlik Sense, and create a new app.

[14] Open your newly created app and Click on Data load editor

[15] Navigate on Create new connection -> ODBC, and select your DSN.Be sure to uncheck Use 32-bit connection If you are using a 64-Bit Generic ODBC Client.

[16] Upon successful connection, you will be able to explore and select data from Amazon Athena. Click on select data to open up Qlik Sense’s data selection tool

[17] Use the Owner (Schema) and Table sections to select data for your report. Edit the SQL query if needed, and click on Insert script.

Your SQL query will be inserted into the Qlik Script editor. Click Load data to bring statements from your Athena instance.

To start making a report, click on the Navigation manager and select App Overview

Create and open a new sheet. Click on the Edit Icon to begin building your report.

Click Charts -> Table to build a basic table for this sample report.

Select a field, then click Add Dimension

A table will be created and populated with field information. Add any additional fields as required. When satisfied, your table is ready to be published.

Client Components Troubleshooting

32- and 64-bit Editions of Qlik Sense

Qlik Sense’s bitness can generally be determined by checking the location of the application. On 64-bit Windows, the 64-bit Qlik Sense folder is located in C:\Program Files\, and the 32-bit Qlik Sense folder is located in C:\Program Files (x86)\.

  • The 64-bit Qlik Senseapplication requires a 64-bit ODBC driver.
  • The 32-bit Qlik Sense application requires a 32-bit ODBC driver.

Server Components Troubleshooting

License Manager

If you failed to point the installer program to your JDBC-ODBC Driver’s license file, or are upgrading from a Free Evaluation License to a Perpetual License, you may encounter the following error message:

[OpenLink][ODBC] License not found

This message is resolved by copying your license file (oplrqb.lic) from the folder into which it was downloaded over to folder named: \Program Files\OpenLink Software\UDA\bin\.

Once the license is in place you will need to restart the license manager by performing the following steps:

  1. Opening the Windows service manager
  2. Locate and double click on OpenLink License Manager
  3. Stop the OpenLink License Manager and OpenLink Request BrokerServices
  4. Restart the OpenLink License Manager and OpenLink Request BrokerServices

Note: You can manually ensure the License Manager is directed to directory where your License Files are situated by adding: +directory <directory-name> to the License Manager Service properties.

32- and 64-Bit Edition JVMs

The nature of your Java Virtual Machine (JVM) can be determined by examining your user and system PATH variables via the System Environment Variable Manager provided by Windows.

  • 64 Bit Java Runtime Engine (JRE) and Developer Kit (JDK) will be located in C:\Program Files\Java\{JDK or JRE Version}\…
  • 32 Bit Java Runtime Engine (JRE) and Developer Kit (JDK) will be located in C:\Program Files (x86) \Java\{JDK or JRE Version}\…
  • %JAVA_HOME% environment variable, and its placement in %PATH% determines precedence
  • The System %PATH% variable takes precedence over User %PATH% if both collide.
  • If your JDBC data source uses a 64-bit JVM, you will need the 64-bit server components. Use of a 32-bit JVM will require 32-bit server components.

JDBC Demo Applications

In addition to the above, you can also use our JDBC Demo applications to simplify determination of the nature of the JVM to which your Athena JDBC Driver is bound.

Related

Odbc
AWS
Big Data
Analytics
Data Visualization

--

--

Daniel Heward-Mills
OpenLink ODBC, JDBC, ADO.NET Data Access Drivers Blog

Written by Daniel Heward-Mills

110 Followers
Editor for

Consultant @ OpenLink Software: https://www.linkedin.com/in/daniel-heward-mills-a0940465/

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams