Are you looking for a guide to help you configure AWS Athena JDBC connection? Read on to learn more. Our AWS Support team is here to lend a hand with your queries and issues.

How to configure AWS Athena JDBC connection?

Configuring an AWS Athena JDBC connection is an easy task. Our experts have put together this article to help you with the process. First, let’s take a quick look at connecting Amazon Athena data from a connection pool in Tomcat.

1. First, we have to copy CData JAR and CData .lic files to $CATALINA_HOME/lib. We can find the CData JAR in the lib subfolder of the installation directory.
2. Then, we have to add a definition of the resource to the context and specify the JDBC URL as well.

In order to authenticate Amazon Athena requests, we have to offer the credentials for an IAM user with custom permissions or an administrator account. Our experts recommend using the former. We can do that by setting the AccessKey to the access key_id and SecretKey to the secret access key.

The Access key can be attained by signing into the IAM console and navigating to the Users section. Then, we have to select the user and then choose the Security Credentials tab to create or manage the access keys for the user.

How to authenticate from an EC2 Instance

We can use the IAM Role to authenticate if we are using the CData Data Provider from an EC2 Instance with an IAM Role assigned to the instance. This is done by setting UseEC2Roles to true and leaving AccessKey and SecretKey empty. Then, the CData Data Provider will obtain the IAM Role credentials and authenticate with them automatically.

How to authenticate as an AWS Role

Using an AWS role will push the CData Data Provider to retrieve credentials for the specified role. If we connect to AWS we have to specify the AccessKey as well as the SecretKey of an IAM user to assume the role.

How to authenticate with Multi-factor Authentication

We have to specify the MFASerialNumber and MFAToken connection properties for users and roles that need Multi-factor Authentication.

This causes the CData Data Provider to submit the MFA credentials in order to retrieve temporary authentication credentials.

How to connect to Amazon Athena

Besides mentioning AccessKey and SecretKey properties, we have to specify S3StagingDirectory, database, and Region.

In case the Database has not been set in the connection, the data provider will connect to the default database set in Amazon Athena.

Furthermore, we can use the connection string designed to construct the JDBC URL. This is done by executing the jar file from the command line or right-clicking the JAR file.

Once we fill in the connection properties and then copy the connection string to the clipboard we will see the JDBC URL in the resource as seen below:

We can allow a single application to access the Athena data by adding this code to the context.xml file in the META-INF directory of that specific application.

In case of a shared resource configuration, we have to add this code to the context.xml file in $CATALINA_BASE/conf. This offers connectivity to Amazon Athena for all applications.

Then add a reference to the resource to the web.xml and then initialize connections from the connection pool.

This is how we can easily connect to Amazon Athena data in a simple connection pooling scenario. Let us know in the comments if you need further help.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To conclude, our Support Engineers gave us a quick introduction to configuring the AWS Athena JDBC connection.