[Julia, DB] DuckDB

DuckDB는 inprocess OLAP db 인데 별도의 서버 설치 없이 바로 사용 가능한 in app db 이다.

DuckDB URL : https://duckdb.org/

Analytical DBMS 비교

https://clickhouse.com/benchmark/dbms/

DuckDB Julia Package : https://github.com/kimmolinna/DuckDB.jl

DuckDB install

pkg>add DuckDB#master

julia> using DuckDB

julia> db = DuckDB.open(":memory:")
DuckDB.DB(":memory:")

julia> con = DuckDB.connect(db)
DuckDB.Connection(":memory:")

julia> res = DuckDB.execute(con,"CREATE TABLE integers(date DATE, jcol INTEGER);")
0×1 DataFrame

julia> res = DuckDB.execute(con,"INSERT INTO integers VALUES ('2021-09-27', 4), ('2021-09-28', 6), ('2021-09-29', 8);")
1×1 DataFrame
 Row │ Count
     │ Int64
─────┼───────
   1 │     3

julia> res = DuckDB.execute(con, "SELECT * FROM integers;")
3×2 DataFrame
 Row │ date        jcol
     │ Date        Int32
─────┼───────────────────
   1 │ 2021-09-27      4
   2 │ 2021-09-28      6
   3 │ 2021-09-29      8

julia> df = DuckDB.toDataFrame(res)
3×2 DataFrame
 Row │ date        jcol
     │ Date        Int32
─────┼───────────────────
   1 │ 2021-09-27      4
   2 │ 2021-09-28      6
   3 │ 2021-09-29      8

julia> df = DuckDB.toDataFrame(con, "SELECT * FROM integers;")
3×2 DataFrame
 Row │ date        jcol
     │ Date        Int32
─────┼───────────────────
   1 │ 2021-09-27      4
   2 │ 2021-09-28      6
   3 │ 2021-09-29      8

julia> res = DuckDB.execute(con, "COPY (SELECT * FROM integers) TO 'test.parquet' (FORMAT 'parquet');")
1×1 DataFrame
 Row │ Count
     │ Int64
─────┼───────
   1 │     3

julia> res = DuckDB.execute(con, "SELECT * FROM 'test.parquet';")
3×2 DataFrame
 Row │ date        jcol
     │ Date        Int32
─────┼───────────────────
   1 │ 2021-09-27      4
   2 │ 2021-09-28      6
   3 │ 2021-09-29      8

julia> DuckDB.appendDataFrame(df, con, "integers")

julia> DuckDB.disconnect(con)

julia> DuckDB.close(db)