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