Add the following to ./configs/default.yml.
Don't forget to add your own credentials and database settings!
database:
name: "SimbaAthenaODBCConnector"
path: "/Library/simba/athenaodbc/lib/libathenaodbc_sb64.dylib"
driver: "SimbaAthenaODBCConnector"
region: "eu-west-1"
s3_location: ""
authentication_type: "IAM Credentials"
uid: ""
pwd: ""Make sure you install the ODBC driver for your OS which you can find here.
Find out more about connection strings for Athena here.
import Pkg
Pkg.activate(".")
Pkg.add(["ODBC", "DataFrames", "Configs"])
using ODBC, DataFrames, Configs
database = getconfig("database")
name = database.name
path = database.path
driver = database.driver
region = database.region
s3_location = database.s3_location
authentication_type = database.authentication_type
uid = database.uid
pwd = database.pwd
sql = open("src/sql/query.sql" ) do file
read(file, String)
end
# locate existing ODBC driver shared libraries or download new, then configure
ODBC.adddriver(name, path)
# build connection string
connection_string = """
Driver=$driver;
AwsRegion=$region;
S3OutputLocation=$s3_location;
AuthenticationType=$authentication_type;
UID=$uid;
PWD=$pwd;
"""
conn = DBInterface.connect(ODBC.Connection, connection_string)
# execute sql statement directly, then materialize results in a DataFrame
df = DBInterface.execute(
conn,
sql
) |> DataFrame
297×3 DataFrame
Row │ dt table_name n_rows
│ DateTime…? String? Int64?
─────┼────────────────────────────────────────────────
1 │ 2021-05-08T06:46:24.183 Table_A 196040
2 │ 2021-05-08T06:46:24.183 Table_B 28172242
3 │ 2021-05-08T06:46:24.183 Table_C 27111764
4 │ 2021-05-06T06:46:29.916 Table_A 196041
5 │ 2021-05-06T06:46:29.916 Table_C 27080936
6 │ 2021-05-23T06:46:26.201 Table_A 196034
⋮ │ ⋮ ⋮
293 │ 2021-03-03T14:47:56.910 Table_B 27421193
294 │ 2021-03-03T14:47:56.910 Table_C 26379105
295 │ 2021-04-27T06:46:34.887 Table_A 196046
296 │ 2021-04-27T06:46:34.887 Table_B 28016354
297 │ 2021-04-27T06:46:34.887 Table_C 26960853
286 rows omitted