Connecting to Amazon Athena via ODBC

with OpenLink’s Lite Edition ODBC-JDBC Bridge for macOS

Here is a usage guide for our latest Lite Edition ODBC-JDBC Bridge update that includes additional innovations to increase the pool of JDBC-accessible data sources that work flawlessly with existing ODBC-compliant applications on macOS (a/k/a Mac OS X).

The official release date for this macOS driver update is set for June 2017, and it will also be available for Windows, Linux, and other Unix-like OS in the near future.

Prerequisites

  1. Working JDBC connection to an AWS Athena Instance
  2. Installation of OpenLink Software’s Lite Edition ODBC-JDBC Bridge (Mac OS X Guide)
  3. Read+Write access to an Athena Service Instance and an associated S3 Bucket that contains a target database document
Setting up Athena Service Access Policies/Controls for a specific User

4. An Athena accessible Database

Creating an Athena-accessible SQL Table that’s attached to an S3 Bucket hosted CSV document

Here’s a breakdown of steps that enable creation of an Athena-accessible SQL Table using a CSV document published to an Amazon S3 Bucket.

These steps are based on a CSV document identified by the following URI: <https://s3.amazonaws.com/my-athena-db/GlobalInternetUsage2014.csv>.

[1] Go to the Athena Query Editor and execute the following query to place your table in the “default” database that Athena creates on your behalf. (Advanced users may adjust the query to place the table in a different database.)

CREATE EXTERNAL TABLE IF NOT EXISTS net_usage_2014 
(`Country` string,
`Users` int,
`Year` string
)
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH
SERDEPROPERTIES
(‘serialization.format’ = ‘,’,
‘field.delim’ = ‘,’
)
LOCATION ‘s3://my-athena-db/’
TBLPROPERTIES (‘has_encrypted_data’=’false’)
Execution of the query above, using Athena’s query builder UI.

[2] Now that you have a SQL Table (basically, a CSV document attached to an Athena Virtual Table), you can perform a simple SQL Query to ensure the process completed as desired:

SELECT * FROM net_usage_2014 
LIMIT 50;
Query execution via the Athena Query Builder.

Now that everyhing is verifably functional in Athena, yo can now proceed to accessing the same data via ODBC, using our ODBC-JDBC Bridge Driver.

ODBC Driver for JDBC Data Source Name (DSN) Setup & Configuration

[3] Click the Add button and select OpenLink JDBC Lite Driver v7.0 to create a new Data Source Name (DSN), or select an existing DSN to adjust its configuration.

Data Source Name Config: ODBC Driver Selection

[4] Provide a Data Source Name that identifies it distinctly from any other ODBC Data Source Names in your environment. Optionally, include a Description to help you remember what this DSN is for.

Data Source Name Config: Labeling and Description

[5] Provide the JDBC Driver classname, and a URL String. This should include the AWS Athena URL (with location), and the S3 directory to which your your query output will be written (s3_staging_dir). For example:

  • JDBC Driver = com.amazonaws.athena.jdbc.AthenaDriver
  • URL String = jdbc:awsathena://athena.us-east-1.amazonaws.com:443?s3_staging_dir=s3://my-athena-db/
Data Source Name Config: Database Connection Attributes (in this case for a 3rd Party JDBC Driver)

[6] Tick the box to Connect now, and input your IAM account’s API Key and Secret (note: the Secret [a/k/a Password] will not be saved in your DSN). Click the Continue button to make a simple test connection to your Athena DBMS server, and verify the current settings are correct. (You can leave the Connect now box un-ticked to configure all options without making a test connection.)

Data Source Name Config: Connection Attributes Verification & Testing

[7] You will now be on the Options panel, where you must tick the Emulate Prepared Statements box for use with Athena. Some applications may require specific other options. Continue through the Setup panels (Preferences, Compatibility) to adjust any other settings specific to how this DSN will interact with Athena and/or your ODBC client application. (Details of these settings may be found in the ODBC-JDBC Bridge product documentation.)

Data Source Name Config: Additional Settings for 3rd part JDBC Driver

[8] Review the list of active settings, and click Test Datasource… to verify your connection.

Data Source Name Config: Usage Verification & Testing

Accessing Athena-hosted SQL Table via an ODBC Compliant Application (Excel)

[9] Click on Data -> New Database Query -> From Database to open the ODBC DSN selection interface.

Initializing ODBC Data Source Selector UI (part of ODBC Administrator for Mac OS X)

[10] Select your newly created ODBC DSN for accessing your Athena-hosted SQL Table and click OK

Data Source Name Selection

[11] Use the same credentials as you tested when creating the DSN, and click connect.

ODBC Data Source Authentication Challenge

[12] Microsoft Query will open upon a successful ODBC connection. Enter a query just as you would in Athena’s interface, or in a JDBC application such as SQL Workbench J, and Click Run.

[13] If the query returns the desired data, click Return Data to bring it into Excel.

Microsoft Excel for Mac OS X SQL Query Builder
  • You can repeat the same exercise with any other ODBC compliant application, including the iODBC Demo applications bundled with our ODBC Drivers.
iODBC C++ Demo for Mac OS X SQL Query Builder and Query Results

Related