DuckDB SQL

Use the same queries from R, Python, Julia…

What is a Database?

You can think of a database as a collection of tables (or dataframes). In general, the benefit of databases is that they can handle very large amounts of data - data which would not fit in your computer’s RAM. As such, most databases are stored on a hard disk, from where data is read and written to.

There are many different forms of databases. We have several so-called database management systems (DBMS), which allow us to interact with each of them. Unhelpfully, each different flavor of database has a slightly different dialect of a what is called the structured query language, SQL for short. Helpfully, on the other hand, there are several packages out there which help us keeping the effort manageable by translating our queries. We will see a few of those here, and in different driving languages (i.e. R, python and julia).

One final important feature of different databases is how (and where) they actually run. The more traditional solutions exist as server-client models, where you would start on a machine (could be your own) a database server, which interacts with the persistent storage (e.g. with your hard disk) to read and write data, and a client process, where you formulate so-called queries, and get back the results from the server. In some sense you formulate a question for the database in the client, you submit it to the database, and you get back an answer. Of course those server processes can run on dedicated and powerful machines which you can reach via a network, or directly in the cloud, where they are used to work with arbitrarily huge amounts of data.

We will focus on a relatively recent addition to the database family called duckdb. The main benefit (beyond it’s excellent performance) is that it is very easy to run a database without the need to set up the client-server structure. We will come to that in due course.

What is `SQL`

As already mentioned, this is the main database language. Here is an example:

SELECT carat, cut, clarity, color, price 
  FROM diamonds 
  WHERE price > 15000

This reads:

From the table called diamonds take the columns carat, cut, clarity, color, price, but keep only rows where the column price is larger than the value 15000

Not too bad, right?

Let’s get this example running now in our computers. We need to create a duckdb database first, read some data into it, and the confront it with that SQL query. For simplicity we will do this only in R to get us started, then we will move on to a more elaborate example and big data, which will use both R, python and julia.

Simple Intro Example with `R`

library(DBI)
library(duckdb)

Warning: package 'duckdb' was built under R version 4.5.2

library(tibble)
con = DBI::dbConnect(duckdb::duckdb(), shutdown = TRUE)  # will get erased after shutdown
# con = DBI::dbConnect(duckdb::duckdb(), dbdir = "db.duck") # would save on disk

The con object is our live connection to the database. Think of it like a telephone receiver - you can talk into it, and it will respond results back to you. As such, we have to refer to con in everything we want to do with the database.

Let us first read some data from the ggplot2 package, and add it to our database. Remember, a database is a collection of tables, and now we are adding two of those:

dbWriteTable(con, "mpg", ggplot2::mpg)
dbWriteTable(con, "diamonds", ggplot2::diamonds)

Ok, let’s see what tables we have in the database now:

dbListTables(con)

[1] "diamonds" "mpg"

Great. Now how do get data out of it? Here goes the mpg dataset:

con |>
    dbReadTable("mpg") |>
    as_tibble()

# A tibble: 234 × 11
   manufacturer model      displ  year   cyl trans drv     cty   hwy fl    class
   <chr>        <chr>      <dbl> <int> <int> <chr> <chr> <int> <int> <chr> <chr>
 1 audi         a4           1.8  1999     4 auto… f        18    29 p     comp…
 2 audi         a4           1.8  1999     4 manu… f        21    29 p     comp…
 3 audi         a4           2    2008     4 manu… f        20    31 p     comp…
 4 audi         a4           2    2008     4 auto… f        21    30 p     comp…
 5 audi         a4           2.8  1999     6 auto… f        16    26 p     comp…
 6 audi         a4           2.8  1999     6 manu… f        18    26 p     comp…
 7 audi         a4           3.1  2008     6 auto… f        18    27 p     comp…
 8 audi         a4 quattro   1.8  1999     4 manu… 4        18    26 p     comp…
 9 audi         a4 quattro   1.8  1999     4 auto… 4        16    25 p     comp…
10 audi         a4 quattro   2    2008     4 manu… 4        20    28 p     comp…
# ℹ 224 more rows

Back to our SQL query from above. We can submit it to the database listening at con:

query1 = "SELECT carat, cut, clarity, color, price 
  FROM diamonds 
  WHERE price > 15000"

head(dbGetQuery(con, query1))

  carat       cut clarity color price
1  1.54   Premium     VS2     E 15002
2  1.19     Ideal    VVS1     F 15005
3  2.10   Premium     SI1     I 15007
4  1.69     Ideal     SI1     D 15011
5  1.50 Very Good    VVS2     G 15013
6  1.73 Very Good     VS1     G 15014

or of course as before with a pipeline such as

con |>
    dbGetQuery(query1) |>
    as_tibble()

# A tibble: 1,655 × 5
   carat cut       clarity color price
   <dbl> <fct>     <fct>   <fct> <int>
 1  1.54 Premium   VS2     E     15002
 2  1.19 Ideal     VVS1    F     15005
 3  2.1  Premium   SI1     I     15007
 4  1.69 Ideal     SI1     D     15011
 5  1.5  Very Good VVS2    G     15013
 6  1.73 Very Good VS1     G     15014
 7  2.02 Premium   SI2     G     15014
 8  2.05 Very Good SI2     F     15017
 9  1.5  Very Good VS1     F     15022
10  1.82 Very Good SI1     G     15025
# ℹ 1,645 more rows

That’s it for starters. There is now a whole world out there to learn in terms of SQL queries, database joins, pivots (i.e. reshape operations) etc. I leave you in the hands of Hadley Wickham and his dedicated chapter on this topic if you want to dive deeper. It will be almost unavoidable to learn some simple SQL, but hopefully it won’t be so bad. You will see in the associated pages of this tutorial that there are some very nice translation tools for R. Grant’s intro to databases is another valuable resource here. Now, however, let’s get cracking with the real show, i.e. our big data set on NYC taxi rides.

# setup python from R with reticulate package
library(reticulate)
Sys.setenv(RETICULATE_PYTHON="/Users/floswald/.pyenv/versions/3.13.5/bin/python")

Load libraries

library(duckdb)  # loaded already

Warning: package 'duckdb' was built under R version 4.5.2

Loading required package: DBI

import duckdb
import time # just for timing some queries

Create a database connection

The first thing we need to do is instantiate a connection with an in-memory database.¹

con = dbConnect(duckdb(), shutdown = TRUE)

con = duckdb.connect(database = ':memory:', read_only = False)

Digression: In-memory versus on-disk

The fact that our connection lives “in memory” is a killer feature of DuckDB (one that it inherits from SQLite). We don’t need to connect to some complicated, existing database infrastructure to harness all of DuckDB’s power. Instead we can just spin up an ephemeral database that interacts directly with our R (or Python, or Julia, etc.) client.

However, it’s worth noting that you can create a persistent, disk-backed database simply by providing a database file path argument as part of your connection, e.g.

## Uncomment and run the next line if you'd like to create a persistent,
## disk-backed database instead.

# con = dbConnect(duckdb(), dbdir = "nyc.duck")

## Uncomment and run the next line if you'd like to create a persistent,
## disk-backed database instead.

# con = duckdb.connect(database = 'nyc.duck', read_only = False)

(Note that the ".duck" file extension above is optional. You could also use ".db", ".dbb", or whatever you want really.)

Bigger than RAM data?

One really important benefit of creating a persistent disk-backed database is that it enables out-of-core computation for bigger than RAM data. See here for more details and performance considerations (which are still great).

Just How Big is This?

Before we get started with some analysis tasks, here’s a simple question to ask our database: how many rows are we dealing with here, actually?

nrows = dbGetQuery(
      con,
      "
      FROM 'nyc-taxi/**/*.parquet'
      SELECT
        COUNT(passenger_count) as nrows
      "
    )

# con.
#   query(
#   '''
#     FROM 'nyc-taxi/**/*.parquet'
#     SELECT
#     COUNT(passenger_count) as nrows
#   '''
# )

Ok, well that is 570,009,660. 😬

First example

We’ll start with a simple aggregation query to get situated. I’ll also use this example to highlight some general features of DuckDB SQL and the underlying query engine.

Okay, first query. Let’s say we want to know: What is the average tip per passenger count? A typical SQL job for this question might look as follows:

SELECT
  passenger_count,
  AVG(tip_amount) AS mean_tip
FROM 'nyc-taxi/**/*.parquet'
GROUP BY passenger_count
ORDER BY passenger_count

(Where the last ORDER BY statement is optional. Note that ordering (i.e., sorting) is a potentially expensive operation but we’ll get back to that later.)

This is perfectly valid DuckDB SQL too. However, we can rewrite it with slightly nicer syntax thanks DuckDB’s “friendly SQL”. The key changes for this simple query are going to be: (1) the FROM statement comes first, and (2) we can use the GROUP BY ALL and ORDER BY ALL statements to avoid writing out the “passenger_count” grouping column multiple times.²

FROM 'nyc-taxi/**/*.parquet'
SELECT
  passenger_count,
  AVG(tip_amount) AS mean_tip
GROUP BY ALL
ORDER BY ALL

DuckDB’s “friendly SQL”

One of the under-appreciated (IMHO) features of DuckDB is that it supports many syntax enhancements over tradional SQL dialects, which they collectively dub “friendly SQL”. Together these syntax enhancements allow you to write much more ergonomic SQL queries that cut down on duplication and logical inconsistencies.

To run this operation from our R or Python client, simply pass the SQL query as a string to our connection. Let’s use this as a chance to save the result and time our query too.

tic = Sys.time()
dat1 = dbGetQuery(
  con,
  "
  FROM 'nyc-taxi/**/*.parquet'
  SELECT
    passenger_count,
    AVG(tip_amount) AS mean_tip
  GROUP BY ALL
  ORDER BY ALL
  "
)
toc = Sys.time()

dat1

   passenger_count   mean_tip
1             -127  0.5000000
2             -123  0.0000000
3             -122  5.0000000
4             -119  9.0000000
5             -115  2.0000000
6             -101 46.5999985
7              -98  2.0000000
8              -96  2.0000000
9              -93  1.0000000
10             -92  8.0000000
11             -91  0.9000000
12             -63  0.0000000
13             -48  0.4331620
14             -45  2.0000000
15             -43  0.0000000
16             -33  1.5000000
17             -31  3.0000000
18              -6  0.3333333
19              -1  4.7800000
20               0  0.8086557
21               1  0.8380950
22               2  0.7600803
23               3  0.6722275
24               4  0.5602723
25               5  0.8207867
26               6  0.9231394
27               7  0.5307692
28               8  0.1500000
29               9  0.4835294
30              10  3.2156250
31              13  2.5000000
32              15  2.0000000
33              17  4.2600002
34              19  0.0000000
35              25  1.2000000
36              33  1.0000000
37              34  2.0000000
38              36 11.2500000
39              37  0.0000000
40              38  0.0000000
41              47  0.0000000
42              49  0.0000000
43              53  0.0000000
44              58  4.0000000
45              61  4.3400002
46              65  0.0000000
47              66  1.5000000
48              69  0.0000000
49              70  0.0000000
50              84  5.0000000
51              97  2.0000000
52             113  0.0000000
53             125  2.0000000

toc - tic

Time difference of 0.6785898 secs

tic = time.time()
dat1 = (
  con.
  query(
    '''
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      passenger_count,
      AVG(tip_amount) AS mean_tip
    GROUP BY ALL
    ORDER BY ALL
    '''
    )
)
toc = time.time()

dat1

┌─────────────────┬────────────────────┐
│ passenger_count │      mean_tip      │
│      int8       │       double       │
├─────────────────┼────────────────────┤
│            -127 │                0.5 │
│            -123 │                0.0 │
│            -122 │                5.0 │
│            -119 │                9.0 │
│            -115 │                2.0 │
│            -101 │ 46.599998474121094 │
│             -98 │                2.0 │
│             -96 │                2.0 │
│             -93 │                1.0 │
│             -92 │                8.0 │
│               · │                 ·  │
│               · │                 ·  │
│               · │                 ·  │
│              58 │                4.0 │
│              61 │  4.340000152587891 │
│              65 │                0.0 │
│              66 │                1.5 │
│              69 │                0.0 │
│              70 │                0.0 │
│              84 │                5.0 │
│              97 │                2.0 │
│             113 │                0.0 │
│             125 │                2.0 │
├─────────────────┴────────────────────┤
│ 53 rows (20 shown)         2 columns │
└──────────────────────────────────────┘

# print(f"Time difference of {toc - tic} seconds")
## Timing will be misleading for this rendered Quarto doc, since we're calling
## Python from R (via the reticulate package).

Note that we actually get a polars DataFrame as a return object. Click the callout box below to learn more.

Result conversion in Python (click to expand)

By default, the con.query method that we are using here will return a polars DataFrame object that Python understands “natively” (i.e., has a print method for and so on). Behind the scenes, this duckdb to polars integration relies on the pyarrow library being available to our Python environment, which have already installed for this workshop.

It’s also possible return other types of Python objects. For example, you can use the .df() method to coerce to a pandas DataFrame instead, among several other formats like numpy arrays. (Details here.) Given the focus of this workshop, it won’t surprise you to hear that I’m going to stick with the default polars conversion.

So that only took 0.68 seconds in this rendered Quarto doc (and will likely be even faster when you try in an interactive session). To underscore just how crazy impressive this is, recall that this includes the time that it takes to read the data from disk. I can almost guarantee that the read + serialization time alone for traditional data wrangling workflows would take several minutes, and probably crash my laptop RAM. Don’t forget that our full dataset is nearly 200 million rows deep and 30 columns wide.

Aside: We clearly have a few outlier typos in our dataset. 254 passengers in a single taxi trip? I don’t think so. We’d probably want to filter these out with a WHERE statement if we were doing serious analysis, but I’m just going to leave them in for this tutorial.

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.

tic = Sys.time()
dat2 = dbGetQuery(
  con,
  "
  FROM 'nyc-taxi/**/*.parquet'
  SELECT
    month,
    passenger_count,
    AVG(tip_amount) AS mean_tip
  WHERE month::int <= 3
  GROUP BY ALL
  "
    )
toc = Sys.time()

head(dat2)

  month passenger_count  mean_tip
1    01               4 0.5392428
2    03              37 0.0000000
3    02               0 0.8380251
4    01               7 0.3833333
5    03               3 0.7226773
6    03               6 0.9743453

toc - tic

Time difference of 0.2821069 secs

tic = time.time()
dat2 = (
  con.
  query(
    '''
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      month,
      passenger_count,
      AVG(tip_amount) AS mean_tip
    WHERE month::int <= 3
    GROUP BY ALL
    '''
  )
)
toc = time.time()

dat2

┌─────────┬─────────────────┬─────────────────────┐
│  month  │ passenger_count │      mean_tip       │
│ varchar │      int8       │       double        │
├─────────┼─────────────────┼─────────────────────┤
│ 03      │               2 │  0.7975787729058189 │
│ 03      │             -48 │  0.4964473613521509 │
│ 02      │              97 │                 2.0 │
│ 01      │               8 │                 0.0 │
│ 01      │               5 │  0.7658916932484046 │
│ 01      │               9 │                 0.0 │
│ 01      │             -45 │                 2.0 │
│ 01      │              65 │                 0.0 │
│ 02      │               0 │  0.8380250567980946 │
│ 01      │               7 │ 0.38333332538604736 │
│ ·       │               · │           ·         │
│ ·       │               · │           ·         │
│ ·       │               · │           ·         │
│ 01      │               2 │  0.7000857207479081 │
│ 02      │               3 │  0.7038398380914318 │
│ 02      │               6 │  0.9382620652590578 │
│ 01      │             -48 │                 0.0 │
│ 03      │              13 │                 2.5 │
│ 03      │              61 │   4.340000152587891 │
│ 01      │               3 │  0.6365031314435748 │
│ 01      │               6 │  0.8870541753823185 │
│ 02      │               2 │  0.7735807588639534 │
│ 02      │             -48 │                 0.0 │
├─────────┴─────────────────┴─────────────────────┤
│ 45 rows (20 shown)                    3 columns │
└─────────────────────────────────────────────────┘

# print(f"Time difference of {toc - tic} seconds")
## Timing will be misleading for this rendered Quarto doc, since we're calling
## Python from R (via the reticulate package).

Note that this query completed even faster than the first one, even though we added another grouping variable. Reason: Subsetting along our Hive-partitioned parquet dataset allows DuckDB to take shortcuts. We can see this directly by prepending an EXPLAIN statement to our query to reveal the optmized query plan.

dbGetQuery(
  con,
  "
  EXPLAIN
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      month,
      passenger_count,
      AVG(tip_amount) AS mean_tip
    WHERE month::int <= 3
    GROUP BY ALL
  "
)

physical_plan
┌───────────────────────────┐
│       HASH_GROUP_BY       │
│    ────────────────────   │
│          Groups:          │
│             #0            │
│             #1            │
│                           │
│    Aggregates: avg(#2)    │
│                           │
│     ~155,016,543 rows     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           month           │
│      passenger_count      │
│ CAST(tip_amount AS DOUBLE)│
│                           │
│     ~155,016,543 rows     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       PARQUET_SCAN        │
│    ────────────────────   │
│         Function:         │
│        PARQUET_SCAN       │
│                           │
│        Projections:       │
│           month           │
│      passenger_count      │
│         tip_amount        │
│                           │
│       File Filters:       │
│ (CAST(month AS INTEGER) <=│
│             3)            │
│                           │
│   Scanning Files: 11/40   │
│                           │
│     ~155,016,543 rows     │
└───────────────────────────┘

con.query(
  '''
  EXPLAIN
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      month,
      passenger_count,
      AVG(tip_amount) AS mean_tip
    WHERE month::int <= 3
    GROUP BY ALL
  '''
)

┌───────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  explain_key  │                                            explain_value                                             │
│    varchar    │                                               varchar                                                │
├───────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ physical_plan │ ┌───────────────────────────┐\n│       HASH_GROUP_BY       │\n│    ────────────────────   │\n│    …  │
└───────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────┘

tl;dr DuckDB is able to exploit the month partition of our dataset, so subsetting means that it can avoid unecessary data ingestion. Similarly, it only reads in a select group of columns; that’s what the “PROJECTION” part of the plan denotes. If nothing else, the take-home message is that DuckDB only does what it needs to. Laziness as a virtue!

Here’s a final aggregation example, this time including a high-dimensional grouping column (i.e., “trip_distance”) and some additional aggregations.

tic = Sys.time()
dat3 = dbGetQuery(
  con,
  "
  FROM 'nyc-taxi/**/*.parquet'
  SELECT
    passenger_count,
    trip_distance,
    AVG(tip_amount) AS mean_tip,
    AVG(fare_amount) AS mean_fare
  GROUP BY ALL
"
)
toc = Sys.time()

nrow(dat3)

[1] 34490

head(dat3)

  passenger_count trip_distance  mean_tip mean_fare
1               1           0.2 0.2710060  4.450034
2               1           0.5 0.2682509  4.243049
3               2           0.9 0.3301824  5.488188
4               2           0.6 0.2599188  4.600829
5               1           7.8 1.8862030 20.886028
6               4           2.2 0.3908674  9.056943

toc - tic

Time difference of 1.657583 secs

tic = time.time()
dat3 = (
  con.
  query(
    '''
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      passenger_count,
      trip_distance,
      AVG(tip_amount) AS mean_tip,
      AVG(fare_amount) AS mean_fare
    GROUP BY ALL
    '''
    )
)
toc = time.time()

len(dat3)

dat3

┌─────────────────┬───────────────┬────────────────────┬────────────────────┐
│ passenger_count │ trip_distance │      mean_tip      │     mean_fare      │
│      int8       │     float     │       double       │       double       │
├─────────────────┼───────────────┼────────────────────┼────────────────────┤
│               1 │           1.3 │  0.501041142944036 │  6.576000542796745 │
│               1 │           3.8 │ 1.0645854111633746 │ 12.744132897255941 │
│               2 │           0.8 │ 0.3072232402152276 │  5.187316269314565 │
│               1 │           2.9 │ 0.8733271546170125 │ 10.647348553159707 │
│               1 │          12.7 │ 3.0130180744416952 │  31.56132960153057 │
│               2 │           2.0 │ 0.5492390885637236 │  8.470511892639378 │
│               2 │           3.9 │ 0.9419637646988281 │ 13.102721804135836 │
│               4 │           4.6 │ 0.6916373442123728 │ 14.836173975949746 │
│               3 │          17.6 │ 2.0336999306138575 │  45.40287953209084 │
│               2 │          11.1 │ 2.2718801570738654 │ 28.136059642770356 │
│               · │            ·  │                 ·  │          ·         │
│               · │            ·  │                 ·  │          ·         │
│               · │            ·  │                 ·  │          ·         │
│               2 │         36.62 │               30.0 │ 146.10000610351562 │
│               4 │         36.64 │                0.0 │  94.30000305175781 │
│               2 │         42.36 │                8.0 │               40.0 │
│               2 │         47.41 │               15.0 │  179.3000030517578 │
│               0 │          2.55 │ 18.799999237060547 │               94.0 │
│               4 │         30.18 │                0.0 │               66.5 │
│               1 │         73.66 │               20.0 │  157.3000030517578 │
│               1 │         72.05 │               10.0 │              240.0 │
│               6 │         31.33 │                0.0 │  77.69999694824219 │
│               3 │          50.8 │                0.0 │               45.0 │
├─────────────────┴───────────────┴────────────────────┴────────────────────┤
│ ? rows (>9999 rows, 20 shown)                                   4 columns │
└───────────────────────────────────────────────────────────────────────────┘

# print(f"Time difference of {toc - tic} seconds")
## Timing will be misleading for this rendered Quarto doc, since we're calling
## Python from R (via the reticulate package).

Pivot (reshape)

Let’s explore some pivot (reshape) examples, by building off the previous query.

UNPIVOT: wide => long
PIVOT: long => wide

Here I’ll use a Common Table Expression (CTE) to define a temporary table tmp_table, before unpivoting—i.e., reshaping long—at the end.

dat_long = dbGetQuery(
  con,
  "
  WITH tmp_table AS (
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      passenger_count,
      trip_distance,
      AVG(tip_amount) AS mean_tip,
      AVG(fare_amount) AS mean_fare
    GROUP BY ALL
  )
  UNPIVOT tmp_table
  ON mean_tip, mean_fare
  INTO
    NAME variable
    VALUE amount
  "
)

head(dat_long)

  passenger_count trip_distance  variable     amount
1               1           4.2  mean_tip  1.1437746
2               1           4.2 mean_fare 13.6165938
3               1           6.7  mean_tip  1.5553541
4               1           6.7 mean_fare 18.7803887
5               3           3.3  mean_tip  0.6807862
6               3           3.3 mean_fare 11.8060703

dat_long = (
  con.
  query(
    '''
    WITH tmp_table AS (
      FROM 'nyc-taxi/**/*.parquet'
      SELECT
        passenger_count,
        trip_distance,
        AVG(tip_amount) AS mean_tip,
        AVG(fare_amount) AS mean_fare
      GROUP BY ALL
    )
    UNPIVOT tmp_table
    ON mean_tip, mean_fare
    INTO
      NAME variable
      VALUE amount
    '''
  )
)

dat_long

┌─────────────────┬───────────────┬───────────┬─────────────────────┐
│ passenger_count │ trip_distance │ variable  │       amount        │
│      int8       │     float     │  varchar  │       double        │
├─────────────────┼───────────────┼───────────┼─────────────────────┤
│               1 │           0.9 │ mean_tip  │  0.3955349657901968 │
│               1 │           0.9 │ mean_fare │  5.4215752687366905 │
│               1 │           0.6 │ mean_tip  │ 0.29999257364286563 │
│               1 │           0.6 │ mean_fare │   4.529382089639585 │
│               1 │           1.6 │ mean_tip  │  0.5693209149594437 │
│               1 │           1.6 │ mean_fare │    7.39461490980278 │
│               3 │           1.0 │ mean_tip  │ 0.30720687218061526 │
│               3 │           1.0 │ mean_fare │   5.825608045585165 │
│               3 │           3.5 │ mean_tip  │  0.7159305394649522 │
│               3 │           3.5 │ mean_fare │   12.28213095308538 │
│               · │            ·  │    ·      │           ·         │
│               · │            ·  │    ·      │           ·         │
│               · │            ·  │    ·      │           ·         │
│               3 │         18.84 │ mean_tip  │  3.5693908621211947 │
│               3 │         18.84 │ mean_fare │   45.54390862750523 │
│               6 │         10.74 │ mean_tip  │   2.933041354455507 │
│               6 │         10.74 │ mean_fare │  27.232116787393018 │
│               4 │         20.96 │ mean_tip  │  3.0614865019514754 │
│               4 │         20.96 │ mean_fare │   46.11216210030221 │
│               1 │         24.24 │ mean_tip  │  3.8683265306511703 │
│               1 │         24.24 │ mean_fare │  58.325714360451215 │
│               3 │         13.93 │ mean_tip  │   2.347375415884379 │
│               3 │         13.93 │ mean_fare │  34.861793923615615 │
├─────────────────┴───────────────┴───────────┴─────────────────────┤
│ ? rows (>9999 rows, 20 shown)                           4 columns │
└───────────────────────────────────────────────────────────────────┘

Another option would have been to create a new table in memory and then pivot over that, which segues nicely to…

Digression: Create new tables

CTEs are a very common, and often efficient, way to implement multi-table operations in SQL. But, for the record, we can create new tables in DuckDB’s memory cache pretty easily using the CREATE TABLE statement.

Instead of DBI::dbGetQuery, we must now use DBI::dbExecute.

dbExecute(
    con,
    "
    CREATE TABLE taxi2 AS
      FROM 'nyc-taxi/**/*.parquet'
      SELECT
        passenger_count,
        trip_distance,
        AVG(tip_amount) AS mean_tip,
        AVG(fare_amount) AS mean_fare
      GROUP BY ALL
    "
)

[1] 34490

dbListTables(con)

[1] "taxi2"

FWIW, you can always remove a table with dbRemoveTable().

Instead of con.query(), we must now use con.execute().

con.execute(
  '''
  CREATE TABLE taxi2 AS
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      passenger_count,
      trip_distance,
      AVG(tip_amount) AS mean_tip,
      AVG(fare_amount) AS mean_fare
    GROUP BY ALL
  '''
)

<_duckdb.DuckDBPyConnection object at 0x1265f5530>

# https://stackoverflow.com/q/75727685
con.query(
  '''
  SELECT table_name, estimated_size AS nrows, column_count AS ncols
  FROM duckdb_tables;
  '''
)

┌────────────┬───────┬───────┐
│ table_name │ nrows │ ncols │
│  varchar   │ int64 │ int64 │
├────────────┼───────┼───────┤
│ taxi2      │ 34490 │     4 │
└────────────┴───────┴───────┘

Back to reshaping

With our new taxi2 table in hand, let’s redo the previous unpivot query directly on this new table. This makes the actual (un)pivot statement a bit clearer… and also separates out the execution time.

dbGetQuery(
  con,
  "
  UNPIVOT taxi2
  ON mean_tip, mean_fare
  INTO
    NAME variable
    VALUE amount
  LIMIT 5
  "
)

  passenger_count trip_distance  variable     amount
1               2           5.0  mean_tip  1.1283594
2               2           5.0 mean_fare 15.4946218
3               1          10.0  mean_tip  2.7706395
4               1          10.0 mean_fare 25.3572088
5               2           1.1  mean_tip  0.3722562

con.query(
  '''
  UNPIVOT taxi2
  ON mean_tip, mean_fare
  INTO
    NAME variable
    VALUE amount
  LIMIT 5
  '''
)

┌─────────────────┬───────────────┬───────────┬─────────────────────┐
│ passenger_count │ trip_distance │ variable  │       amount        │
│      int8       │     float     │  varchar  │       double        │
├─────────────────┼───────────────┼───────────┼─────────────────────┤
│               4 │           1.5 │ mean_tip  │  0.2967576243217164 │
│               4 │           1.5 │ mean_fare │   7.278823156775435 │
│               2 │           1.1 │ mean_tip  │ 0.37225622853665635 │
│               2 │           1.1 │ mean_fare │  6.0700679524931225 │
│               2 │           3.6 │ mean_tip  │  0.8727633243594671 │
└─────────────────┴───────────────┴───────────┴─────────────────────┘

(Note how crazy fast pivoting in DuckDB actually is.)

Joins (merges)

It’s a bit hard to demonstrate a join with only a single main table. But here is a contrived example, where we calculate the mean monthly tips and the mean monthly fares as separate sub-queries (CTEs), before joining them together by month.

dbGetQuery(
  con,
  "
  WITH 
    mean_tips AS (
      FROM 'nyc-taxi/**/*.parquet'
      SELECT
        month,
        AVG(tip_amount) AS mean_tip
      GROUP BY month
    ),
    mean_fares AS (
      FROM 'nyc-taxi/**/*.parquet'
      SELECT
        month,
        AVG(fare_amount) AS mean_fare
      GROUP BY month 
    )
  FROM mean_tips
  LEFT JOIN mean_fares
  USING (month)
  SELECT *
  ORDER BY mean_tips.month
  "
)

   month  mean_tip mean_fare
1     01 0.7534124  9.621946
2     02 0.8285459  9.804122
3     03 0.8534463 10.045834
4     04 0.8145655 10.054076
5     05 0.8408745 10.272067
6     06 0.7861996 10.154768
7     07 0.7782784 10.076624
8     08 0.7838768 10.118335
9     09 0.8240095 10.337792
10    10 0.8210327 10.198155
11    11 0.8340241 10.209248
12    12 0.8193982 10.186188

con.query(
  '''
  WITH 
    mean_tips AS (
      FROM 'nyc-taxi/**/*.parquet'
      SELECT
        month,
        AVG(tip_amount) AS mean_tip
      GROUP BY month
    ),
    mean_fares AS (
      FROM 'nyc-taxi/**/*.parquet'
      SELECT
        month,
        AVG(fare_amount) AS mean_fare
      GROUP BY month 
    )
  FROM mean_tips
  LEFT JOIN mean_fares
  USING (month)
  SELECT *
  ORDER BY mean_tips.month
  '''
)

┌─────────┬────────────────────┬────────────────────┐
│  month  │      mean_tip      │     mean_fare      │
│ varchar │       double       │       double       │
├─────────┼────────────────────┼────────────────────┤
│ 01      │ 0.7534123946901562 │  9.621945830692315 │
│ 02      │ 0.8285459487206723 │  9.804121920681121 │
│ 03      │ 0.8534462602598598 │ 10.045834371392742 │
│ 04      │ 0.8145655439154411 │ 10.054076479147493 │
│ 05      │ 0.8408745010238741 │  10.27206657198953 │
│ 06      │ 0.7861995818044666 │ 10.154767617040491 │
│ 07      │ 0.7782784126595831 │ 10.076623524738146 │
│ 08      │ 0.7838767754310967 │  10.11833459367217 │
│ 09      │ 0.8240094649480889 │ 10.337792208162867 │
│ 10      │ 0.8210326814949942 │ 10.198154846599179 │
│ 11      │ 0.8340240727721798 │ 10.209247815883026 │
│ 12      │ 0.8193982180364637 │ 10.186187986703436 │
├─────────┴────────────────────┴────────────────────┤
│ 12 rows                                 3 columns │
└───────────────────────────────────────────────────┘

Challenge

Redo the above join but, rather than using CTEs, use tables that you first create in DuckDB’s memory bank. Again, this will simplify the actual join operation and also emphasise how crazy fast joins are in DuckDB.

Windowing

One last example: Binning “trip_distance” into deciles and then calculating the the mean “tip_amount” within each decile. This is an example of a window function and query pattern that I use all the time in my own work. I find it extremely useful for quickly pulling out descriptive patterns from large datasets, from which I can then develop a better intuition of my data. In turn, this shapes the hypotheses and modeling choices that I make in the subsequent analysis stage.

Sorting and sampling

I’m using a 1% random sample of my data here (see the USING SAMPLE 1% statement). Why? Because calculating deciles requires ranking your data and this is expensive! To rank data, we first have to sort it (ORDER) and this requires evaluating/comparing every single row in your dataset. In turn, this means that it’s very hard to take shortcuts. (This is one reason why DuckDB’s optimized query plan will always delay sorting until as late as possible; to only sort on a smaller subset/aggregation of the data if possible.) FWIW, DuckDB’s sorting algorithm is still crazy fast. But for data of this size, and where sorting on the full datset is unavoidable, I strongly recommend sampling first. Your general insights will almost certainly remain intact.

dbGetQuery(
  con,
  "
  WITH trip_deciles AS (
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      tip_amount,
      trip_distance,
      NTILE(10) OVER (ORDER BY trip_distance) AS decile
    USING SAMPLE 1%
  )
  FROM trip_deciles
  SELECT
    decile,
    AVG(trip_distance) AS mean_distance,
    AVG(tip_amount) AS mean_tip
  GROUP BY ALL
  ORDER BY ALL
  "
)

   decile mean_distance  mean_tip
1       1     0.4201671 0.4486563
2       2     0.7806239 0.3636336
3       3     1.0210033 0.4403919
4       4     1.2809783 0.4915636
5       5     1.5704339 0.5573184
6       6     1.9282043 0.6293665
7       7     2.4061288 0.7248113
8       8     3.1268114 0.8637410
9       9     4.5419002 1.1182093
10     10    10.5911832 2.3612101

con.query(
  '''
  WITH trip_deciles AS (
    FROM 'nyc-taxi/**/*.parquet'
    SELECT
      tip_amount,
      trip_distance,
      NTILE(10) OVER (ORDER BY trip_distance) AS decile
    USING SAMPLE 1%
  )
  FROM trip_deciles
  SELECT
    decile,
    AVG(trip_distance) AS mean_distance,
    AVG(tip_amount) AS mean_tip
  GROUP BY ALL
  ORDER BY ALL
  '''
)

┌────────┬─────────────────────┬─────────────────────┐
│ decile │    mean_distance    │      mean_tip       │
│ int64  │       double        │       double        │
├────────┼─────────────────────┼─────────────────────┤
│      1 │ 0.42110492231404545 │  0.4572925620259506 │
│      2 │  0.7819526986335842 │ 0.37162775141495785 │
│      3 │  1.0222376624077332 │  0.4509621888272826 │
│      4 │  1.2824326558411971 │  0.5048181378907102 │
│      5 │  1.5729979219735062 │  0.5678449837473593 │
│      6 │  1.9315289780368432 │  0.6415433057005221 │
│      7 │   2.410110105380252 │  0.7333555736009032 │
│      8 │  3.1318764035045104 │  0.8755400748629928 │
│      9 │   4.550381984630143 │  1.1328701635594765 │
│     10 │  10.610901862100459 │    2.39506401635539 │
├────────┴─────────────────────┴─────────────────────┤
│ 10 rows                                  3 columns │
└────────────────────────────────────────────────────┘

Close connection

dbDisconnect(con)

Again, this step isn’t strictly necessary since we instantiated our connection with the shutdown = TRUE argument. But it’s worth seeing in case you want to be explicit.

con.close()

Footnotes

Aside: The shutdown = TRUE argument is a convenience feature that ensures our connection is automatically terminated when our R session ends (i.e., even if we forget to do it manually.) I’m not aware of a similar convenience argument for Python; please let me know if I am missing something.↩︎
I’ll admit that the benefits don’t seem so great for this simple example. But trust me: they make a big difference once you start having lots of grouping columns and complex sub-queries.↩︎