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

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

Bearing in mind that -

• 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

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. 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.

Installation

Prequisites

• A working OpenLink Request Broker instance (Enterprise Edition Installation Guide (Windows))
• A working JDBC connection to an AWS Athena instance

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.

[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

[9] Open the 32-bit or 64-bit ODBC Administrator corresponding to the bitness of your ODBC application, and click Add to create a new DSN. For this demo, we're working with 64-bit Power BI, so we'll use the 64-bit Administrator.

[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?

Simply repeat steps #8-13 above, using the 32-bit ODBC Administrator instead of the 64-bit Administrator. The 32-bit DSN setup should be otherwise identical to the 64-bit.

Connecting to Athena using Microsoft Power BI

[15] Launch Power BI , select Get Data → ODBC, and click Connect.

[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

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

• 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

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 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

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

• Free Evaluation License Generation & Installer Archive Download Page
• Latest JDBC 1.8 Database Agent binary (ODBC connections to Athena will not work without this update being installed)
• JDBC-to-ODBC Bridge options - for the Latest Release of Java Virtual Machine, and Beyond
• What are Multi-Tier ODBC-JDBC Bridge Drivers?
• New 32- and 64-Bit ODBC SDK & Runtime for Mac OS X
• OpenLink Data Access Drivers - Secure & High-Performance ODBC, JDBC, ADO.NET, OLE DB Drivers for a wide range of database management systems
• OpenLink Virtuoso - Multi-Model RDBMS & Middleware Platform
• Using Power BI as a launch point for Semantic Web exploration

Connecting Microsoft Power BI to Amazon Athena using ODBC was originally published in OpenLink ODBC, JDBC, ADO.NET Data Access Drivers Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.