Skip to main content

Connect databses in Julia

Examples to connect db in Julia
julia
database
aws
gcp
bigquery
Author

Masaya Kameyama

Published

July 29, 2021

Code
versioninfo()
Julia Version 1.6.1
Commit 6aaedecc44 (2021-04-23 05:59 UTC)
Platform Info:
  OS: macOS (x86_64-apple-darwin18.7.0)
  CPU: Intel(R) Core(TM) i7-8557U CPU @ 1.70GHz
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-11.0.1 (ORCJIT, skylake)
Environment:
  JULIA_NUM_THREADS = 8
Code
using GBQ
Code
# use .env
using DotEnv
DotEnv.config(path = "~/.env")
aurora="dbname='$(ENV["WRITE_RDB_DATABASE"])' host='$(ENV["WRITE_RDB_HOST"])' user='$(ENV["WRITE_RDB_USERNAME"])' password='$(ENV["WRITE_RDB_PASSWORD"])' port=$(ENV["WRITE_RDB_PORT"])"
redshift="dbname='$(ENV["DWH_DATABASE"])' host='$(ENV["DWH_HOST"])' user='$(ENV["DWH_USERNAME"])' password='$(ENV["DWH_PASSWORD"])' port=$(ENV["DWH_PORT"])"
project_name=ENV["BQ_PROJECT_NAME"]
dataset_name=ENV["BQ_DATASET_NAME"]
;
Code
using LibPQ
using DataFrames
# next two packages conflict with each other at the macro @select
using DataFramesMeta
# using Queryverse #packages for dataframes 
Code
using Format
Code
function sql(query,conn_str)
    conn = LibPQ.Connection(conn_str; options=Dict{String, String}())
    result =execute(conn,query)
    df = DataFrame(result)
    close(conn)
    return df
end 
sql (generic function with 1 method)
Code
function sql_time(query1,query2)
    print("Aurora: ")
    @time sql(query1,aurora)
    print("Redshift: ")
    @time sql(query1,redshift)
    print("Bigquery: ")
    @time gbq_query(query2)
end
sql_time (generic function with 1 method)
Code
query="select * from {}companies"
query1=format(query,"")
query2=format(query,project_name*"."*dataset_name*".")
"select * from prod-tameike-219208.smart.companies"
Code
sql_time(query1,query2);
Aurora:   0.485374 seconds (202.23 k allocations: 7.152 MiB)
Redshift:   0.265902 seconds (211.10 k allocations: 7.288 MiB)
Bigquery:   2.751780 seconds (142.51 k allocations: 14.153 MiB)
Back to top