Connecting to Amazon Athena via ODBC (Windows)

Published in

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

6 min readAug 26, 2017

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 Windows operating systems.

Prerequisites

Working JDBC connection to an AWS Athena Instance
Installation of OpenLink Software’s Lite Edition ODBC-JDBC Bridge
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 everything is verifiably functional in Athena, you can now proceed to accessing the same data via ODBC, using our ODBC-JDBC Bridge Driver.

Pre ODBC-JDBC Driver Data Source Name setup checks

Basic JDBC Connectivity Confirmation

To successfully use an ODBC-JDBC Bridge driver, it is important that your target JDBC Driver is properly installed and functioning. You can use our JDBC demo apps (installed with our Drivers) to simplify this process.

Locate the folder named: \Program Files\OpenLink Software\UDA\Samples, and then go into the folder that matches you current Java Runtime installation (JDK 1.8 is the latest for JVM 8.0+) .

Pre ODBC DSN Creation JDBC Connectivity Check

Assuming JDK 1.8, go to the folder named: \Program Files\OpenLink Software\UDA\Samples\JDBC\jdk1.8\JDBCDemo, and then open JDBCDemo.bat .

At this point you have a simple JDBC application instantiated for testing with your JDBC Driver. Here are some screenshots that guide you through the process of establishing a connection using our JDBC Drivers for Virtuoso.

Change the default JDBC connection URL to what’s required by your JDBC Driver.

Insert the Driver Name for your specified JDBC Driver (Athena for this example) and the required JDBC connection URL.

Enter a sample SQL Query, in this case targeting a table within a default AWS Athena instance database.

Scroll through your query results (which is also a simple test of Scrollable Cursor functionality)

Your instance is ready to be used with the OpenLink ODBC-JDBC bridge upon a successful connection and population of values.

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

[3] Open The 64 Bit ODBC Administrator. Click the Add button and select OpenLink JDBC Lite for JDK 1.5 [7.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/

[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 Next 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.)

[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 and may vary for each ODBC-compliant application.)

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

Accessing Athena-hosted SQL Table via an ODBC Compliant Application (C++ Demo)

[9] Click on Environment -> Open Connection to open the ODBC DSN selection interface.

Initializing ODBC Data Source Selector UI (part of ODBC Administrator)

[10] Select your newly created ODBC DSN for accessing your Athena-hosted SQL Table and click OK. Use the same credentials as you tested when creating the DSN, and click connect.

[11] The selection interface will disappear upon a successful ODBC connection. Navigate to SQL -> Execute SQL, and enter a query just as you would in Athena’s interface, or in a JDBC application such as SQL Workbench J, and Click OK.