Query AWS Athena with Emacs using jdbc

If you haven't checked out ejc-sql, you should now.

Official JDBC connection setup using jdbc drivers is available here - https://github.com/kostafey/ejc-sql#create-connections-manualy

It has support for

MySQL
MariaDB
MS SQL Server
Oracle
H2
SQLite
PostgreSQL
Informix
Presto

And also allows you to add custom jdbc drivers using ejc-jdbc-drivers

You can customize artifacts and their versions used as JDBC drivers for each database type in Leiningen format in ejc-jdbc-drivers custom variable.

Here is how I setup it up

;; setup with use-package
(use-package ejc-sql
  :commands
  (ejc-create-connection ejc-connect ejc-set-column-width-limit ejc-set-max-rows)
  :init
  (setq ejc-set-rows-limit 1000
        ejc-result-table-impl 'orgtbl-mode ;; 'ejc-result-mode
        ejc-use-flx t
        ejc-flx-threshold 3
        nrepl-sync-request-timeout 30)
  ;; enable auto complete
  (add-hook 'ejc-sql-minor-mode-hook
            (lambda ()
              (auto-complete-mode t)
              (ejc-ac-setup))))

;; custome interactive function to create connection
(defun my/setup-ejc-connections ()
  (interactive)
  (ejc-set-column-width-limit 72)
  ;; add athena jdbc driver to the `ejc-jdbc-drivers`
  (plist-put ejc-jdbc-drivers
            "awsathena"
            [com.simba.athena/athena-jdbc "2.0.9"])
  (ejc-create-connection "athena-prod"
                        :dbtype "awsathena"
                        :classpath "~/.m2/repository/com/simba/athena/athena-jdbc/2.0.9/athena-jdbc-2.0.9.jar"
                        :classname "com.simba.athena.jdbc.Driver"
                        :subprotocol "awsathena"
                        :subname "//athena.eu-west-1.amazonaws.com:443/my_database;S3OutputLocation=s3://my-athena-bucket/"
                        :user (getenv "AWS_ACCESS_KEY_ID")
                        :password (getenv "AWS_SECRET_ACCESS_KEY")))
  ;; You can also use connectin-uri like below
  ;; :connection-uri (concat "jdbc:awsathena://athena.eu-west-1.amazonaws.com:443/my_database;"
  ;;                         "User=" (getenv "AWS_ACCESS_KEY_ID") ";"
  ;;                         "Password=" (getenv "AWS_SECRET_ACCESS_KEY") ";"
  ;;                         "S3OutputLocation=" "s3://my-athena-bucket/"))

I mostly use this in org-mode buffer so that I can analyse data and make notes about it.

EDIT 08/04/2021

You may also simplify connection configuration using Profile attribute in :connection-uri with the latest Athena JDBC driver.

This also helps if you are using AWS STS temporary tokens for authenticating with AWS.

(ejc-create-connection
 "athena-prod"
 :dbtype "awsathena"
 :classpath "~/.m2/repository/com/simba/athena/athena-jdbc/2.0.16.1000/athena-jdbc-2.0.16.1000.jar"
 :classname "com.simba.athena.jdbc.Driver"
 :connection-uri "jdbc:awsathena://AwsRegion=eu-west-1;Profile=MyProfile;S3OutputLocation=s3://aws-athena-query-results-xxxxxx-eu-west-1/")