Connecting to Amazon Athena using 32-bit and 64-bit ODBC Applications

Situation Analysis

Big Data deployed to Amazon S3 Buckets (e.g., massive HTTP, OS, and related logs) is now accessible via the SQL Relational abstraction functionality of Athena — a Big Data Analytics service provided by the Amazon AWS Cloud. Naturally, many are finding they need to access this data using a wide range of existing Business Intelligence, Analytics, and Report Writing tools.

Challenge

Athena is only accessible via a JDBC Driver while most Business Intelligence, Analytics, and Report Writing tools are ODBC-compliant.

In addition, these applications are a mix of 32-bit and 64-bit binaries. 32-bit applications require 32-bit ODBC drivers; they cannot connect to 64-bit ODBC drivers. 64-bit applications require 64-bit ODBC drivers; they cannot connect to 32-bit ODBC drivers.

Many have heard of an ODBC-to-JDBC Bridge Driver, but the common conception of this option is a Single-Tier architecture — where the ODBC application loads the ODBC driver into memory, and the ODBC driver then loads the Java environment to which it bridges. In this scenario, the bitness-matching goes deeper — 32-bit Single-Tier ODBC drivers require a 32-bit Java environment, and 64-bit Single-Tier ODBC drivers require a 64-bit Java environment.

It’s easy enough to have both 32-bit and 64-bit variants of an ODBC driver on the same machine. It’s much harder to have both 32-bit and 64-bit Java, and to keep all the PATH and other environment variables set properly as you switch between 32-bit and 64-bit ODBC applications.

Solution

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., you can configure an ODBC Data Source Name (DSN) based on a 32-bit ODBC driver for use by a 32-bit ODBC application, that connects to a JDBC Data Source in a 64-bit Java Environment … or an ODBC Data Source Name (DSN) based on a 64-bit ODBC driver for use by a 64-bit ODBC application, that connects to a JDBC Data Source in a 32-bit Java Environment.

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

Installation

Prequisites

• A Working OpenLink Request Broker Instance and JDBC Database Agent (Enterprise Edition Installation Guide (Windows))
• A working JDBC connection to an Amazon 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.

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

Configure Client Component of Multi-Tier ODBC

Data Source Name (DSN) for 32-Bit Generic ODBC Driver

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

[12] Ensure that ODBC DSN settings are functional.

32-Bit ODBC Application Test

Test connectivity to Athena from a 32-bit ODBC- compliant application by using the 32-Bit edition of the C++ Demo bundled with our Windows Installer.

[13] Select C++ Demo application via explorer or its application icon.

[14] Select ODBC DSM associated with 32-Bit Generic ODBC Driver.

[15] Provide JDBC connectivity credentials for accessing Athena instance.

[16] Enter SQL Query processing UI.

[17] Type and execute SQL Query.

[18] View results from Athena presented in this 32-Bit ODBC application.

What about 64-Bit ODBC Compliant Applications?

Simply repeat the steps outlined in this post, with regards to ODBC DSN setup, en route to connecting any ODBC compliant application on Windows with an Athena instance via JDBC.

Client Application Troubleshooting

32- and 64-Bit Application Editions

Your ODBC Application’s bitness can be determined by checking which location the applications files are stored in. 64-bit application folders are located in C:\Program Files\, and the 32-bit folders arelocated in C:\Program Files (x86)\

• 64-Bit Applications need to use a 64-bit ODBC Driver
• 32-Bit Applications need to use a 32-bit ODBC Driver

License Manager

License File Location Issues

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 will encounter the following error dialog:

[OpenLink][ODBC] License not found

This problem is resolved by copying your license (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.

Server Componenet Troubleshooting

32- and 64-Bit Edition JVMs

Your Java Virtual Machine (JVM) bitness can be determined by examining your user and system PATH variables. Open the System Environment Variable Manager and examine your PATH.

• 64 Bit Ja64 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.

Note that the System %PATH% variable takes precedence over User %PATH% if both collide.

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
• JDBC 1.8 Database Agent binary (required for making ODBC connections to Amazon Athena)
• JDBC-to-ODBC Bridge options — for the Latest Releases of Java, and Beyond
• What are Multi-Tier ODBC-JDBC Bridge Drivers?
• 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