Connecting Microsoft Power BI to Amazon Athena using ODBC

Connecting an ODBC Application to a JDBC Data Source using OpenLink Software’s Enterprise Edition ODBC-JDBC Bridge Driver

Daniel Heward-Mills
Sep 5, 2017 · 9 min read

The Big Data meme brings attention to a need for agile interactions with Systems of Engagement that generate data with several key characteristics, including:

  • High Volume
  • High Velocity
  • High Variety

Amazon Athena (Athena) is a Big Data Query Service that provides SQL DDL (Data Definition Language) and DML (Data Manipulation Language) operations on Relations derived from a variety of Database Document Types (CSV, ORC, Parquet, and others) created by Systems of Engagement that have been saved to Amazon S3 Buckets.

Microsoft Power BI is a Business Intelligence (BI) & Analytics tool that provides a productive solution for Data Visualization, Reporting, and Key Performance Indicator (KPI) Dashboards.

Challenge

  • Power BI is an ODBC compliant application for Business Intelligence and Analytics, that lacks JDBC connectivity
  • Power BI for Windows is available in both 32-bit and 64-bit editions
  • Athena is only accessible via JDBC — and on some platforms the host Java Virtual Machine (JVM) is only 64-Bit

— how does an organization exploit the collective virtues of Power BI and Amazon Athena?

Solution

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. Power BI connectivity to Amazon Athena via ODBC
  2. Negating confusion posed by 32- and 64-bit editions of Power BI 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., Power BI), 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. Power BI), that connects to a JDBC Data Source in a 32-bit or 64-bit JVM.

Multi-Tier Architecture of the OpenLink ODBC-JDBC Bridge Driver

Installation

Prequisites

ODBC Data Source Name (DSN) Setup & Configuration

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

Database Agent Installation

Bridge Agent Configuration

[0] First, ensure that pop-ups are enabled in your browser; otherwise, the 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.

[2] Authenticate using your administrator credentials. The default username and password are bothadmin.

[3] Select the appropriate JDBC Bridge Agent for your Java environment. The numbers here are tied 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.

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.

[5] Click Next > buttons until you reach the Startup Parameters pane, and 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.

[6] Click Next > buttons until you reach the Client-Server Process Mapping & Control pane, and set a maximum number of Clients Sessions to be handled 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. This setting is used to manage system resource consumption by each Agent instance.

[7] Clicking on save will save the details you’ve set, and tell the Request Broker to reload those settings. Note: Some settings will not take effect until the Request Broker service is stopped and restarted, typically through the Services control panel.

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

Data Source Name (DSN) Configuration

[10] For best results, you should pick the non-Unicode driver (“OpenLink Generic ODBC Driver [7.0]”) if you’re working with any single-byte (8-bit) codepage or character set. You must (and should only) pick the Unicode driver (“OpenLink Generic ODBC Driver (Unicode) [7.0]”) if you’re working with a multi-byte (9-bit or larger) codepage or character set.

[11] Name your data source (e.g., Athena 64-Bit Test), and enter your Request Broker’s hostname and listen port in the Server text box (default is localhost:5000).

[12] For Domain, choose the Agent Name that corresponds to the Agent configuration made in step 7, above; Jdbc 1.8 for this example). Input your JDBC Driver’s classname in Name, and your JDBC URL where obvious. We’re using the Type 4 JDBC Driver for Virtuoso, classname virtuoso.jdbc4.Driver, and connecting to a local Virtuoso instance, via URL jdbc:virtuoso://localhost:1111/. You can tick the "Connect now to verify" box, and input username and password for the JDBC target now, to check the basic setup when you click the Next > button. For this test, we’ll use the default Virtuoso user, dba, with the customized password we set during installation

[13] Set any additional connection parameters your connection may require.

[14] Following a successful data source test, this DSN can be used by any 64-bit ODBC compliant application.

What about 32-Bit ODBC Compliant Applications?

Connecting to Athena using Microsoft Power BI

[16] Select the AWS Athena ODBC DSN you configured above (Athena 64-Bit Test) from the menu; leave the "Connection string" text entry box blank; enter your SQL query (e.g., SELECT * FROM cloudfront_logs LIMIT 50;); and click OK.

[17] Enter your AWS Athena credentials and Click Connect.

[18] Review the sample results, and if they are satisfactory, click Load.

[19] Following query execution, field (column) names from the Athena result set will appear in a collapsible list on the right-hand side.

[20] Ticking the box next to a field name will add that column to the table in the main section of the window, and populate it with its respective values.

Client Components Troubleshooting

32- and 64-bit Editions of Power BI

  • The 64-bit Power BI application requires a 64-bit ODBC driver.
  • The 32-bit Power BI application requires a 32-bit ODBC driver.

Server Components Troubleshooting

License Manager

[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 Broker Services
  4. Restart the OpenLink License Manager and OpenLink Request Broker Services

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

  • 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

Related

OpenLink ODBC, JDBC, ADO.NET Data Access Drivers Blog

Articles related to ODBC, JDBC, and ADO.NET Data Access Standards Exploitation

Daniel Heward-Mills

Written by

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

OpenLink ODBC, JDBC, ADO.NET Data Access Drivers Blog

Articles related to ODBC, JDBC, and ADO.NET Data Access Standards Exploitation

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade