All You Need to Know About Connecting to the Amazon Athena with an ODBC Driver
In this article, we will learn how to connect to the Amazon Athena with an ODBC Driver on a Windows machine. We are going to use Magnitude Simba Athena ODBC Driver to connect to Amazon Athena. In addition to that, we will also demonstrate how to query Athena using ODBC Client (RazorSQL) with the newly set up driver. In near future, we will be adding the ODBC Connectors for different OS.
Basic Requirements:
- Windows Machine
- Magnitude Simba Athena ODBC License. Get the License from the following link https://www.magnitude.com/drivers/athena-odbc-jdbc
- AWS CLI is installed on your machine. Install AWS CLI from the following link https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html
- IAM User with the right permission
- ODBC Client (RazorSQL)
Steps to be followed:
- Download the ODBC Driver from the following link https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html
- Depending on the bitness of your client application, double-click to run Simba Athena 1.1 32-bit.msi or Simba Athena 1.1 64-bit.msi.
- Click Next.
- Select the check box to accept the terms of the License Agreement if you agree, and then click Next.
- To change the installation location, click Change, then browse to the desired folder, and then click OK. To accept the installation location, click Next.
- Click Install.
- When the installation completes, click Finish.
- If you received a license file through email, then copy the license file into the \lib subfolder of the installation folder you selected above. You must have Administrator privileges when changing the contents of this folder.
Setting up Data Source Name:
- Go to ODBC Data Sources
- In the ODBC Data Source Administrator, click the Drivers tab, and then scroll down as needed to confirm that the Simba Athena ODBC Connector appears in the alphabetical list of ODBC connectors that are installed on your system.
- Choose one:
To create a DSN that only the user currently logged into Windows can use, click the User DSN tab.
Or, to create a DSN that all users who log in to Windows can use, click the System DSN tab.
- Click Add.
- In the Create New Data Source dialog box, select Simba Athena ODBC Driver.
- Click Finish. The Simba Athena ODBC Driver DSN Setup dialog box opens.
- In the Data Source Name field, type a name for your DSN.
- Optionally, in the Description field, type relevant details about the DSN.
- In the AWS Region field, type the AWS region of the Athena instance that you want to connect to. Refer http://docs.aws.amazon.com/general/latest/gr/rande.html#athena. to choose the correct region.
- Optionally, in the Catalog field, type the name of the Athena catalog you want the connector to query. By default, it will use AwsDataCatalog.
- In the Schema field, type the name of the database schema to use when a schema is not explicitly specified in a query. You can still issue queries on other schemas by explicitly specifying the schema in the query.
- Optionally, in the Workgroup field, type the name of the workgroup to use when signing in to Athena.
- Optionally, from the Metadata Retrieval Method drop-down list, select the option you want to use to retrieve metadata from Athena.
- In the S3 Output Location field, type the path of the Amazon S3 location where you want to store query results, prefixed by s3://.
For example, to store results in a folder named “test-folder-1” inside an S3 bucket named “query-results-bucket”, you would type s3://query-resultsbucket/test-folder-1 in this field. - To configure encryption for your query results, do the following:
- From the Encryption Options drop-down list, select the encryption protocol that you want to use.
- If you selected SSE_KMS or CSE_KMS in the previous step, then in the KMS Key field, type the KMS customer key to use for encrypting data. - To configure authentication, click Authentication Options. Choose Default Credentials and Click OK.
- Open Command Prompt, type aws configure and set the credentials.
- To test the connection, click Test. Review the results as needed, and then click OK.
Setting up ODBC Client:
- Download the ODBC Client from the following link https://razorsql.com/download.html. I’m using RazorSQL
- Double click on the .exe file that has been downloaded.
- Click on the Install for all users (recommended) option.
- Click on the I accept the agreement radio button and click Next.
- To change the installation location, click Change, then browse to the desired folder, and then click OK. To accept the installation location, click Next.
- To Display the RazorSQL on the Start Menu, just click on Next. If you don’t want the shortcut, then click on the Don’t create a Start Menu folder checkbox. Click Next.
- To make an additional shortcut on the Desktop, just click on Next. If you don’t want the shortcut, then uncheck the Create a desktop shortcut checkbox. Click Next.
- Click Install.
- If you want to start RazorSQL after the installation, click on the start RazorSQL after the installation checkbox. Click Finish. If you don’t want to start, just click Finish.
- Open RazorSQL, click on Click to Connect to a Database.
- Connection Wizard popup window will appear. Select Athena(Amazon) Database Type.
- Click Continue.
- Select ODBC Connection type. On the Connection Profile Name field, type a name for your Connection Profile Name.
- From the Datasource Name drop-down list, select the option you have created in the ODBC Driver.
- Click Connect.
Under the hood of Athena
AWS Athena is a serverless interactive analytics service offered by Amazon that can be readily used to gain insights on data residing in S3. Under to hood, Athena used a distributed SQL engine called Presto, which is used to run the SQL queries. Presto is based on the popular open-source technology Hive, to store structured, semi-structured, and unstructured data.
To know more about Amazon Athena, please refer to the following link
https://docs.aws.amazon.com/athena/latest/ug/what-is.html
https://aws.amazon.com/athena/pricing/
What is Simba ODBC Drvier?
The Simba Athena ODBC Driver enables organizations to connect their BI tools to the Amazon Athena query service, enabling Business Intelligence, analytics, and reporting on the data that Athena returns from Amazon S3 databases. The driver retrieves catalog metadata from the AthenaAPI.
The driver complies with the ODBC 3.80 data standard, including important functionality such as Unicode and 32- and 64-bit support for high-performance computing environments on all platforms.
To Know more about Driver, please refer to the following link
https://www.magnitude.com/blog/what-is-odbc
We’re a proud AWS partner. Read all about our AWS practice.
Download Brochure: Orchestrating the data on the Amazon Web Services
