Connect to AWS Athena Database in a Mule Application With Generic Database

This would be a short tutorial on connecting from MuleSoft to AWS Athena Database. We will use Generic Database Driver.

 Please read through the following resources for further information before getting started, just in case. 

We will get started and go through each process step by step. Let's assume that we have the following information before getting started:

Get Started

We will start by creating a Project in Anypoint MuleSoft Platform. If you want to understand how to do this, please check my blog on Introduction to Building API Using MuleSoft. 

new mule project

 

Once we have created the application, the first step is to update our POM with the necessary JAR. I was not able to find a MAVEN Repository having the necessary JAR Files available to be used. However, I was able to get the JAR files.

The JAR files can be available for download here in my GitHub space.

Alternatively, you can download the JAR file from the AWS Documentation Connect Amazon Athena with JDBC.

In my case, I am using version 2.1.0.1000, the JDBC driver with AWS SDK.

Once the corresponding JAR is downloaded, we need to upload the same to the artifactory that we use. In my case, I will simply upload this to my Maven Repository using the following command.

Shell
 
mvn install:install-file \
   -Dfile=<path-to-file> \
   -DgroupId=<group-id> \
   -DartifactId=<artifact-id> \
   -Dversion=<version> \
   -Dpackaging=<packaging> \
   -DgeneratePom=true

 

In my case:

mvn install:install-file   -Dfile=<path-to-file> -DgroupId=aws.athena -DartifactId=AthenaJDBC42 -Dversion=2.1.0.1000 File Upload

I would overwrite the pom file that I have downloaded from the GitLab.

Set Up My Application POM File

Once completed, we must update my POM File at 2 places:

XML
 
<dependency>
	<groupId>aws.athena</groupId>
    <artifactId>AthenaJDBC42</artifactId>
    <version>2.1.0.1000</version>
</dependency>

 

XML
 
<plugin>
	<groupId>org.mule.tools.maven</groupId>
    <artifactId>mule-maven-plugin</artifactId>
    <version>${mule.maven.plugin.version}</version>
    <extensions>true</extensions>
    <configuration>
		<sharedLibraries>
			<sharedLibrary>
				<groupId>aws.athena</groupId>
				<artifactId>AthenaJDBC42</artifactId>
			</sharedLibrary>                              
		</sharedLibraries>
		<classifier>mule-application</classifier>
	</configuration>
</plugin>


Starting the Code

Here is an example of the code used. We will head back to the actual XML and start by creating a flow consisting of the following, in order:

flow

Here is the code that I have in my XML:

XML
 
<?xml version="1.0"encoding="UTF-8"?>

<mule xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"xmlns:db="http://www.mulesoft.org/schema/mule/db"
     xmlns:http="http://www.mulesoft.org/schema/mule/http"
     xmlns="http://www.mulesoft.org/schema/mule/core"xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd">
     
     <http:listener-config name="HTTP_Listener_config"doc:name="HTTP Listener config">
          <http:listener-connection host="0.0.0.0"port="8081" />
     </http:listener-config>
     
     <db:config name="Athena_Database_Config">
          <db:generic-connection 
               url="jdbc:awsathena://User=${athena.User};Password=${athena.Password};AwsRegion=${athena.AwsRegion};S3OutputLocation=${athena.S3OutputLocation}"
               driverClassName="com.simba.athena.jdbc.Driver"/>
     </db:config>
     
     <flow name="test-athena-dbFlow"doc:id="cc20b26a-4cc0-4a81-b66a-bf97e310858a">
          <http:listener doc:name="Listener"doc:id="d6463d2c-4895-43e7-a5f8-ae4e261cf187"config-ref="HTTP_Listener_config" path="/testAthena"/>
          <logger level="INFO"doc:name="Logger" doc:id="21cb94b1-a3cc-436a-a97b-e362c2ab8989"message="Request Received"/>
          <db:select doc:name="Select"doc:id="6210b6c3-bc8e-4e7d-87f1-dafa888fdf45"config-ref="Athena_Database_Config">
               <db:sql ><![CDATA[${athena.sqlQuery}]]></db:sql>
          </db:select>
          <logger level="INFO"doc:name="Logger" doc:id="c17e9502-e118-4a06-a33f-ccc816fcd7b5"message="Database Connection Complete"/>
          <ee:transform doc:name="Transform Message" doc:id="7de4dceb-e124-41f6-a51a-6718ae2bc6b5">
               <ee:message >
                     <ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
payload]]></ee:set-payload>
               </ee:message>
          </ee:transform>
     </flow>
</mule>

 

Testing My Application

Finally, I will run my application in MuleSoft. 

 run my application in MuleSoft

Once the application is deployed, up and kicking, we can call the API from Postman:

call the API from Postman

Conclusion

This blog explains the steps required to configure the Mule application to use generic database configuration to connect to Amazon Athena using the AWS Athena JDBC Driver. Please let me know your thoughts. The idea was to connect to the database. Of course, we should have proper error handling, HTTPs security, etc., in the application.

 

 

 

 

Top