I Love DuckDB

Business Garden RUG, 2024-05-16

Paulius Alaburda

Kokias Duomenų bazes buvo galima rinktis 2018 m.?

Jeigu nori sukti analitines užklausas, turėjai turėti dedikuotą serverį (ir komandą).

Progresas duomenų saugojime ir apdorojime

Nauji formatai: 2013 m. .parquet, 2016 m. Arrow projektas, 2017 m. Iceberg, Hudi
Nauji column-store varikliai: 2015 m. Microsoft VertiPaq, 2019 m. Tableau Hyper

Kas yra DuckDB?

In-process SQL OLAP duomenų bazė sukurta DuckDB Labs
Parašyta su C++, neturi priklausinių ir turi bibliotekas populiariose programavimo kalbose, įskaitant R.
Sukuria DB atmintyje arba sukuria vieną failą kaip SQLite
Nemokama!

Kaip įsijungti DuckDB?

R paketas yra DuckDB
DuckDB yra kaip visos DB - dbConnect priima duckdb::duckdb() kaip driver’į ir sukuria DuckDB sąsają

install.packages("duckdb")
library(duckdb)

#con <- dbConnect(duckdb(), ":memory:")
#con <- dbConnect(duckdb(), "database.db")

con <- dbConnect(duckdb())

Demonstraciją atliksime su TPC-H

TPC-H yra automatiškai sugeneruojamas duomenų rinkinys, skirtas standartizuoti duomenų bazių testavimą. Kartu su juo taip pat viešai prieinamos testinės SQL užklausos.

Naudosime TPC-H Query 1

tpch_01 <- function(lineitem) {
  lineitem |>
    select(l_shipdate, l_returnflag, l_linestatus, l_quantity, l_extendedprice, l_discount, l_tax) |>
    filter(l_shipdate <= !!as.Date("1998-09-02")) |>
    select(l_returnflag, l_linestatus, l_quantity, l_extendedprice, l_discount, l_tax) |>
    summarise(
      sum_qty = sum(l_quantity),
      sum_base_price = sum(l_extendedprice),
      sum_disc_price = sum(l_extendedprice * (1 - l_discount)),
      sum_charge = sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)),
      avg_qty = mean(l_quantity),
      avg_price = mean(l_extendedprice),
      avg_disc = mean(l_discount),
      count_order = n(),
      .by = c(l_returnflag, l_linestatus)
    ) |>
    arrange(l_returnflag, l_linestatus)
}

SELECT
  l_returnflag, 
  l_linestatus, 
  sum(l_quantity) as sum_qty, 
  sum(l_extendedprice) as sum_base_price,
  sum(l_extendedprice*(1-l_discount)) as sum_disc_price, 
  sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge, 
  avg(l_quantity) as avg_qty, 
  avg(l_extendedprice) as avg_price, 
  avg(l_discount) as avg_disc, 
  count(*) as count_order
FROM
  lineitem
WHERE 
  l_shipdate <= make_date(1998,12,1)
GROUP BY 
  l_returnflag,
  l_linestatus 
ORDER BY 
  l_returnflag,
  l_linestatus

DuckDB kverksi SQL kalba - kaip ir kitos DB

dbGetQuery(con, "SELECT * FROM lineitem LIMIT 5")

  l_orderkey l_partkey l_suppkey l_linenumber l_quantity l_extendedprice
1          1    155190      7706            1         17        21168.23
2          1     67310      7311            2         36        45983.16
3          1     63700      3701            3          8        13309.60
4          1      2132      4633            4         28        28955.64
5          1     24027      1534            5         24        22824.48
  l_discount l_tax l_returnflag l_linestatus l_shipdate l_commitdate
1       0.04  0.02            N            O 1996-03-13   1996-02-12
2       0.09  0.06            N            O 1996-04-12   1996-02-28
3       0.10  0.02            N            O 1996-01-29   1996-03-05
4       0.09  0.06            N            O 1996-04-21   1996-03-30
5       0.10  0.04            N            O 1996-03-30   1996-03-14
  l_receiptdate    l_shipinstruct l_shipmode                          l_comment
1    1996-03-22 DELIVER IN PERSON      TRUCK            to beans x-ray carefull
2    1996-04-20  TAKE BACK RETURN       MAIL  according to the final foxes. qui
3    1996-01-31  TAKE BACK RETURN    REG AIR      ourts cajole above the furiou
4    1996-05-16              NONE        AIR            s cajole busily above t
5    1996-04-01              NONE        FOB            the regular, regular pa

Failų skaitymas

DuckDB gali kverksėti su failais tiesiogiai

DuckDB kverksi su įvairiais failais

# Lentelės viduje DuckDB
dbGetQuery(con, "SELECT * FROM lineitem LIMIT 5")

# csv
dbGetQuery(con, "SELECT * FROM '../lineitem.csv' LIMIT 5")

# parquet
dbGetQuery(con, "SELECT * FROM '../lineitem.parquet' LIMIT 5")

# .json...
# SQLite...
# Postgres...
# .xlsx?!

# R objektai?!?!
duckdb_register(con, name = "lineitem", df = lineitem)

dbGetQuery(con, "SELECT * FROM lineitem LIMIT 5")

DuckDB suderinamas su dplyr

dbplyr paketas išverčia dplyr komandas į SQL komandas - užtenka į dplyr komandas perduoti lentelę su tbl() funkcija.

Ką reikia pakeisti savo kode?

Prieš

lineitem %>% 
  tpch_01()

Po

con <- dbConnect(duckdb())

duckdb_register(con, name = "lineitem", df = lineitem)

tbl(con, "lineitem") %>% 
  tpch_01()

dplyr + DuckDB greičio palyginimas

Kada apsimoka naudoti DuckDB?

duckplyr - nauja antis tvenkinyje

Posit ir DuckDB Labs kartu kuria biblioteką, su kuria reikės tik vieno eilutės pakeitimo.
duckplyr naudoja DuckDB relational API tiesiogiai - nesinaudoja DuckDB SQL sąsaja
Kai duckplyr negali išversti dplyr kodo į DuckDB, tiesiog naudoja R variklį

duckplyr sintaksė

as_duckplyr_df()

1 eilutė vienai lentelei

lineitem %>% 
  as_duckplyr_df() %>% 
  tpch_01()

duckplyr::methods_overwrite()

1 eilutė visoms lentelėms

duckplyr::methods_overwrite()

lineitem %>% 
  tpch_01()

DuckDB plėtiniai (extensions)

DuckDB galima išplėsti su plėtiniais. Pavyzdžiui, httpfs leidžia skaityti nuotolinius .parquet failus:

dataset <- "https://static.data.gouv.fr/resources/bureaux-de-vote-et-adresses-de-leurs-electeurs/20230626-135723/table-adresses-reu.parquet"

dbExecute(con, "INSTALL httpfs;")
dbExecute(con, "LOAD httpfs;")

dbSendQuery(con, glue::glue("
  CREATE VIEW bureaux AS
    SELECT * 
    FROM '{dataset}'"))

dbGetQuery(con, "
  DESCRIBE bureaux")

Kiti plėtiniai

.pbix failų skaitymas
Delta lentelės nuo v0.10.3