using DuckDB
using DataFrames
using Dates
using Chain
con = DBInterface.connect(DuckDB.DB)DuckDB.DB(":memory:")DuckDB in Julia
Use the same queries from Julia
Load libraries
We have a julia package as well for duckdb. 1
Create a database connection
We proceed as before with the other languages:
Let’s run the first simple query again to get the number of rows, as before with R:
nrowsdb = @chain con begin
DBInterface.execute(
"""
FROM 'nyc-taxi/**/*.parquet'
SELECT COUNT(passenger_count) as nrows
"""
)
end
ncolsdb = DBInterface.execute(con,
"""
FROM 'nyc-taxi/**/*.parquet'
SELECT * LIMIT 1
"""
) |> DataFrame |> names |> length
(rows = nrowsdb, cols = ncolsdb)(rows = (nrows = [570009660],), cols = 19)… and we know that this is a lot of data:
run(`du -h -d 0 nyc-taxi`) 17G nyc-taxiProcess(`du -h -d 0 nyc-taxi`, ProcessExited(0))
First example
Same examples as previously:
SELECT
passenger_count,
AVG(tip_amount) AS mean_tip
FROM 'nyc-taxi/**/*.parquet'
GROUP BY passenger_count
ORDER BY passenger_counttime1 = @elapsed dat1 = DBInterface.execute(
con,
"""
FROM 'nyc-taxi/**/*.parquet'
SELECT
passenger_count,
AVG(tip_amount) AS mean_tip
GROUP BY ALL
ORDER BY ALL
"""
) |> DataFrame0.778765916That took 0.78 seconds. Here is the result:
first(dat1,5)5×2 DataFrame
| Row | passenger_count | mean_tip |
|---|---|---|
| Int8 | Float64 | |
| 1 | -127 | 0.5 |
| 2 | -123 | 0.0 |
| 3 | -122 | 5.0 |
| 4 | -119 | 9.0 |
| 5 | -115 | 2.0 |
More Aggregation
Let’s try out some more aggregation queries. How about a slightly variation on a our first example query, where we (a) add “month” as a second grouping variable, and (b) subset to only the first three months of the year.
time2 = @elapsed dat1 = DBInterface.execute(
con,
"""
FROM 'nyc-taxi/**/*.parquet'
SELECT
passenger_count,
AVG(tip_amount) AS mean_tip
WHERE month::int <= 3
GROUP BY ALL
"""
)0.210989083This time we clocked up 0.21 seconds - keep in mind that this is a lot of data to go through each time.
Julia Specifics: register_data_frame
One cool thing about the julia package is that you can register a local dataframe into the database, which means that there is no copy oepration performed. Duckdb will directly read that julia dataframe instead:
DuckDB.register_data_frame(con, dat1, "dat1_on_db")
# let's read the first row back out just to check
results = DBInterface.execute(con,
"""
SELECT * FROM dat1_on_db
LIMIT 1
"""
)
DataFrame(results)1×2 DataFrame
| Row | passenger_count | mean_tip |
|---|---|---|
| Int8 | Float64 | |
| 1 | -127 | 0.5 |
Julia Specifics: Appender
The appender is an advanced duckdb feature which is optimized for fast row insertion into a database table. This feature is not available in neither R nor python packages. Here is an example. Let’s first create a new dataframe in your julia session and then read it row by row into an empty database table.
ndf = 100_000
df = DataFrame(
id = collect(1:ndf),
value = rand(Float32,ndf),
timestamp = Dates.now() + Dates.Second.(1:ndf),
date = Dates.today() + Dates.Day.(1:ndf)
)
first(df,5)5×4 DataFrame
| Row | id | value | timestamp | date |
|---|---|---|---|---|
| Int64 | Float32 | DateTime | Date | |
| 1 | 1 | 0.885621 | 2025-12-01T22:29:56.677 | 2025-12-02 |
| 2 | 2 | 0.773069 | 2025-12-01T22:29:57.677 | 2025-12-03 |
| 3 | 3 | 0.807024 | 2025-12-01T22:29:58.677 | 2025-12-04 |
| 4 | 4 | 0.76593 | 2025-12-01T22:29:59.677 | 2025-12-05 |
| 5 | 5 | 0.588932 | 2025-12-01T22:30:00.677 | 2025-12-06 |
create a new database with an empty table called test_table1:
db = DuckDB.DB()
DBInterface.execute(db,
"""
CREATE OR REPLACE TABLE
test_table1(id INTEGER PRIMARY KEY, value FLOAT, timestamp TIMESTAMP, date DATE)
""")(Count = Int64[],)Now let us prepare a SQL statement, into which we will repeatedly insert the row for each data. This makes use of the positional parameters ?:
stmt = DBInterface.prepare(db,
"""
INSERT INTO test_table1 VALUES(?,?,?,?)
"""
)DuckDB.Stmt(DuckDB.Connection(":memory:"), Ptr{Nothing}(0x000000011d74f620), "INSERT INTO test_table1 VALUES(?,?,?,?)\n", DuckDB.MaterializedResult)We are ready to time this now.
e = @elapsed res =
begin
for r in eachrow(df)
DBInterface.execute(stmt,
(r.id, r.value, r.timestamp, r.date)
)
end
DBInterface.close!(stmt) # close statement when done
end39.3169185On my machine this took 39.32 seconds. This is probably not the most efficient way to get data into your database, but it could happen (if you create the data yourself from some modeling task, for example).
Now, let’s try with the Appender API. We create an identical table, and attach an Appender object to it:
DBInterface.execute(db,
"CREATE OR REPLACE TABLE
test_table2(id INTEGER PRIMARY KEY, value FLOAT, timestamp TIMESTAMP, date DATE)")
# create an appender on the second table
appender = DuckDB.Appender(db, "test_table2")DuckDB.Appender(Ptr{Nothing}(0x0000000336f056d0))let’s see how fast that insert operation is now:
e2 = @elapsed begin
for i in eachrow(df)
for j in i
DuckDB.append(appender, j)
end
DuckDB.end_row(appender)
end
DuckDB.close(appender) # done now
end0.449401458Now only 0.45 seconds only! Holy smokes.
TipMeta Info
For the purpose of working with this notebook, we can currently only have a single quarto engine per notebook. While it’s perfectly possible to call both R and python code from a running julia session, one needs to annotate the code with some additional markup, which is not great for a learner. So I prefered to separate the julia version from the others. It’s most useful to create a local environment. To achieve this, you can open julia in the root of this document and do
] activate . # activate current dir in Pkg mode
add DuckDB
add DataFrames
add Dates
add Chainand quarto will pick up this env when it sees engine: julia in the frontmatter.