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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37


    ;; 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.

1
2
3
4
5
6


  (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/")