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
- Working JDBC connection to an AWS Athena Instance using the current version of the Athena JDBC driver
- Installation of OpenLink Software’s Lite Edition ODBC-JDBC Bridge (Mac OS X Guide)
- Read+Write access to an Athena Service Instance and an associated S3 Bucket that contains a target database document
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’)
[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;
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.
[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.
[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 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.)
[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.)
[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 (Excel)
[9] Click on Data -> New Database Query -> From Database to open the ODBC DSN selection interface.
[10] Select your newly created ODBC DSN for accessing your Athena-hosted SQL Table and click OK
[11] Use the same credentials as you tested when creating the DSN, and click connect.
[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.
- You can repeat the same exercise with any other ODBC compliant application, including the iODBC Demo applications bundled with our ODBC Drivers (found in
/Applications/iODBC/).
Troubleshooting
[OpenLink][ODBC][JDBC Server]java.lang.NoClassDefFoundError
If you receive this error, you need to add the Athena JDBC driver jar file (e.g., AthenaJDBC41–1.1.0.jar) to the /Library/Java/Extensions/ directory on your Mac. If the jar file is in your Downloads folder, executing these two commands (the second of which must include the trailing ".") in Terminal.app should do the job:
cd /Library/Java/Extensionssudo cp ~/Downloads/AthenaJDBC41–1.1.0.jar .
Related
- OpenLink ODBC-JDBC Bridge Driver Home Page
- Free OpenLink ODBC-JDBC Bridge Evaluation License Generation & Installer Archive Download Page
- Installing & Using the OpenLink Lite Edition ODBC-JDBC Bridge Driver for Mac OS X
- OpenLink JDBC-to-ODBC Bridge Drivers Are More Than Transitional Solutions
- iODBC SDK & Runtime for Mac OS X — for ODBC experience that matches what’s offered on Windows
- Amazon Athena — Interactive Query Service for S3 hosted Databases using SQL
- Amazon Athena JDBC Driver
