Connect to Amazon Athena with a SQL client

AWS Athena lets you run SQL on S3 data, which is very powerful. I wanted to connect to Athena using a SQL client like SQL Workbench, on my Mac so that I could execute those queries to get the insight I wanted into my data. Let us get started 🚀

Prerequisites

• Ensure you have Java installed from here
  • ⚠️ If you are using Windows/Linux, make sure you install the appropriate Java distro
• Install SQL Workbench from here
• Download the Athena Driver from here

Setup the Athena Driver

• File -> Manage Drivers

• Select the Athena driver if available in the list. If it does not exist, don't worry. You can select any of the options and make it an Athena driver by following the next steps
• Lets say you select the Adabas driver ( assuming thats one on the list )
  • Change the Name to Athena-DEV
  • In the Library section, upload the athena driver you installed earlier
  • Change the classname to com.simba.athena.jdbc.Driver
  • You can leave the Sample URL field empty
• Your final Driver setup should look like

• Hit OK to save configuration

Setup the Athena Connection

• File -> Connect Window

• Click the icon on the top left to Create a new connection profile

• You should have this as the view now

• Connection Name ( uppermost text box ) = Athena-DEV
• Driver = Athena-DEV in the dropdown ( since you already created the driver in the steps above )
• URL = <your-athena-instance-based-on-region> eg. jdbc:awsathena://athena.us-east-1.amazonaws.com:443
• Username = <AWS_ACCESS_KEY_ID>
• Password = <AWS_SECRET_ACCESS_KEY>
• Click the Extended Properties box and add a property S3OutputLocation as the key. For the value, use the Amazon S3 location where you would like to store the results of the queries, prefixed by s3://. Eg. If you have a bucket named my-personal-bucket and you want to store the results in a folder called test-run, then the value would be s3://my-personal-bucket/test-run
• Click OK
• Hit the Test button and you should be successfully connected to Athena in your region!

You can now go ahead and query your S3 data out of Athena 🔥

To know more about me, visit my website, or follow me on Twitter. If you want to share your own experience or have thoughts about what you just read, feel free to comment below! 👏👏👏