DuckDb Performance: min, max, and median vs quantile

I was playing with some classical statistics in DuckDB and I ran into something I'd like to share. It's about the measures minimum, maximum and median, and quantiles in general.

The short of it is:

1. Descriptive statistical measures minimum, maximum, and median occur rarely on their own: they are typically collected together, along with a number of other core descriptive statistics.
2. These measures are all special cases of quantiles: minimum is the 0th-quantile, maximum the 1st-quantile and median the 0.5th-quantile.
3. DuckDB provides aggregate functions for each of these measures. It was found that queries using min( expr ), max( expr ), and median( expr ) together are quicker than when using equivalent separate calls to quantile( expr, 0), quantile( expr, 1), and quantile( expr, 0.5) respectively.
4. Interestingly, quantile()'s second argument also accepts an array of quantile specifications, requesting multiple quantiles at once to be returned; also as an array. Calling quantile( expr, [0, 1, 0.5] ) was found to be considerably faster and less memory-intensive than multiple single quantile( expr, quantile ) calls for the 0th-, 1st- and 0.5th-quantile. This particular array-version call to quantile() was found to be only slightly slower than calling the min(), max(), and median() trio.
5. Some applications require a more extensive set of quantiles besides just the minimum, maximum and median, such as deciles or even percentiles. In those cases, collecting all quantiles of interest in one quantile()-call should be preferred over multiple calls each retrieving a single quantile.

You can skip directly to the benchmarks if you like; or you can read on from the top for some background info, and to learn about the considerations and ideas that went into this benchmark.

min, max, median, and quantiles

Just a bit of background on quantiles, and their relation to the classic measures min, max, and median.

Imagine a list of values, and sorting them in ascending order. (We'll assume it's already clear how the values can be compared to each other to determine their position in the sorted list.) Values may or may not be unique: after sorting, duplicate values simply appear adjacent to each other. Once this is done:

• The minimum is the first value of the sorted list.
• The maximum would be the last value of the sorted list.
• The median is the value found halfway the sorted list.

To get from here to the quantiles: imagine taking all list positions, and dividing each by the length of the list. (Here it's assumed list positions start counting from 1.) This yields the fraction of the number of values up to that position in the list.

Now, in essence, a percentile is one such position in the list, so that one can say:

the <fraction>th-quantile is <value>

Be careful when turning the wording around though:

<value> is the <fraction>th-quantile

Duplicate values span multiple quantiles. If that's the case, you should probably express that fact by specifying the quantile range:

<value> spans the <x>th- to the <y>th-quantiles

Stating some value is the xth-quantile implies there is exactly one quantile for that value, which in turns implies that value is unique. If that's the case, you should probably state that explicitly to remove any doubt.

Note: this is just a practical explanation: a quick refresher of what quantiles are, and how they relate to the classic statistical measures. This is not necessarily how an algorithm might calculate these measures; And, as we are about to see, there are some loose ends too.

Discrete vs Continuous Quantiles, and value-extrapolation

The method illustrated above features quantiles that correspond to concrete positions in the list; each has an actual fractional value. In the jargon, these are known as discrete quantiles.

But in practice, quantiles are often treated as a continuum: one could name any number from 0 to 1, and ask for its quantile value. If the required quantile doesn't match an actual position in the list, then the value corresponding to the smallest actual fraction including the required quantile is selected.

Example:

If there are 3 values with positions 1, 2, 3, and respective fractions 1/3, 2/3, 3/3, then the 0.5thquantile is the value at position 2. Why? No actual quantile corresponds to 0.5: 1/3 is too small and 2/3 is too large. But 2/3 is selected since it's the smallest actual quantile that covers the requested 0.5 proportion of the values.

There are more sophisticated implementations that try to return the "true" quantile value. This requires extrapolation between the value of the actual quantiles that occur in the list (i.e., the discrete quantiles) right before and after the required quantile.

Extrapolation applies in particular to values of numerical types, or of types that can be treated as if they are numerical, such as DATE, TIME, and TIMESTAMP. In these cases, the geometrical mean is used to extrapolate.

While value extrapolation may appear intuitive and harmless, I feel it's actually less so:

• By definition, extrapolation creates values that are not actually observed in the dataset. This in itself does not have to be a problem as long as you're aware of it, but it does add complexity and may lead to paradoxical results.
• Geometrical mean extrapolation may result in values that have a different type than that of the input domain, while the minimum and maximum will always retain the type of the argument. Again, one may work around this but it does introduce some untidiness which may be undesirable.
• Using the geometrical mean to extrapolate between the quantiles right before and after the requested quantile may lead to misleading results. Suppose you have 100 values. Let's say the 50th value is 50, and the 51st value is 51. With a naive geometrical mean the 0.5th quantile would be 50.5. But what if all prior values are all 50 while values 51 through 100 are more evenly distributed? It seems clear to me that if the purpose of extrapolation is to find a more true value, it should take the distribution of values into account, and the 0.5th-percentile should be a lot closer to 50 than 50.5.

However, this need not concern us right now: for several reasons, extrapolation doesn't matter much for how quantiles are used. (Some of the reasons are that for realisticly sized data sets, the difference between the extrapolated value and the discrete value becomes smaller and smaller; another reason is that the descriptive statistics can still provide a good or good-enough description of the dataset as a whole, even if the actual measure values are somewhat incaccurate. There are probably counter-arguments too, but that's not the topic of this article. )

Generalization: min, max, and median as Quantiles

With the rule in place to select the actual quantile value, the definitions of minimum, maximum, and median given earlier can now be generalized to quantiles:

• The 0th-quantile corresponds to the minimum. Of course, there is no actual fraction that corresponds to 0. But the smallest fraction that includes it, corresponds to the first value in the list, i.e. our original definition of the minimum.
• The 1th-quantile corresponds to the maximum, i.e. the last value in the list.
• The median corresponds to the 0.5th-quantile, as 0.5 is halfway between 0 and 1. For median, value extrapolation is often mentioned, probably because the simple definition as "halfway the list" poses an easy-to-spot problem when the list has an even number of values.

Quantiles in DuckDB

All these measures have an implementation in SQL as an aggregate function. And of course, DuckDB provides them too:

• min( expression ) implements minimum.
• max( expression ) implements maximum
• median( expression ) implements median.
• quantile( expression, quantile(s) ) implements quantile.

A few remarks concerning the implementation of these functions in DuckDB:

• While min() and max() are straightforward, median() applies value extrapolation for numeric types and temporal types. This means that the return type of median() may not match the type of its argument.
• The quantile()-function is actually an alias for quantile_disc(): the postfix _disc indicates it implements discrete quantiles. DuckDB also provides quantile_cont(), which implements continuous quantiles.
• While quantile_disc() and quantile_cont() examine the entire set of values, DuckDB also offers a family of approximate aggregate functions, including a few to estimate quantiles. These are approx_quantile() and reservoir_quantile(), which work by examining a sample of the values rather than all values. The idea here is that these functions may be faster to compute, but at the expense of returning an estimate of the quantile, rather than a precisely determined one.

What to use, under what circumstances, and how

Considering there are quite a number of options to potentially achieve the same or similar results, the question naturally arises, which should one use, and under what circumstances?

For now I just want to decide a simple dilemma:

The functions min(), max() and median() are well-known and have intuitive names, which is why I would prefer them. But quantile() is more general, and can achieve similar or even equivalent results, as well as handle other, more fine-grained quantiles. Is there a good reason to prefer one over the other?

So in the remainder of this article, I won't be looking into approximate quantile functions: I won't be measuring whether they are faster, and how much; nor will I be exploring how accurate their estimates are as compared to exact methods.

Also, I'm focusing only on quantile(), that is to say: quantile_disc(). That's because for my particular use case, I'm treating the descriptive statistics as summary of my dataset. For that purpose I prefer the quantile values to be drawn from my dataset rather then extrapolated.

With that said, this article will answer the question: when to use min(), max(), and median() vs quantile().

Calling quantile()

As the function signature suggests, quantile( expression, quantile(s) ) takes two parameters: the expression which provides the values as first argument, and a second argument. The second argument can be a single number between 0 and 1, specifying what quantile to return.

The interesting thing is that the second argument can also accept a list of numbers, each specifying a particular quantile. In this case, all specified quantiles are calculated in one go and the function returns the list of corresponding quantile values.

For statistical applications, this makes a lot of sense! Surely with regard to minimum and maximum: these measures almost always occur as a pair to indicate the entire value range. The median is a natural complement, adding basic but valueable information about the distribution of the data along the range. But many applications (for example in demographics) go even further, and are interested in deciles (quantile increments of 10%) or even percentiles (increments of 1%).

Quantiles must be specified as constants

Regardless of whether you're using quantile() to collect a single quantile, or multiple quantiles at once, the argument that specifies the quantiles must be a constant value. It can not be an expression that depends on a column. This makes sense: considering that quantile() is an aggregate function, the percentile definition should be invariant for the entire statement.

However, the requirement for a constant percentile specification may be inconvenient when its value is also to be used elsewhere. In those cases, one can sometimes store the percentile definition in a DuckDB-variable. As argument, the variable may be referenced using a call to getvariable( 'variablename' ), which is considered a constant value.

Benchmarking quantile()

After all this background, it's time to do some measurements to try and find some answers to my questions. To benchmark the functions, I used one of the NYC taxi trip parquet data files, in this case the "High Volume For-Hire Vehicle Trip Records" for January, 2023. The file is called fhvhv_tripdata_2023-01.parquet and has 24 columns and 18,479,031 (~18 million) rows.

All tests are performed using the DuckDB command line, version 1.5.5 with standard settings, running on my laptop (HP EliteBook 850 G8 with an Intel i7-1185G7 and 32GB RAM, on Windows 11).

There's two different benchmarks:

1. Comparing 'classic' measures min(), max() and median() against quantile() - checking both the single- and multi-quantile mode.
2. Comparing the single quantile vs the multi-quantile version of the quantile() function. This tests the impact of adding more quantiles.

Each query provides real, user and sys timings. For this purpose, the real timings matter most, but the split allows for some interesting observations.

Comparing classic measures against the quantile()

As I explained, my first instinct would be to use min(), max() and median(). Because these are all just specific cases of quantiles, I'm trying to figure out whether there's any reason (not?) to switch to quantile() instead. Because these classical statistics are often collected together, and considering that quantile() can collect multiple quantiles in one go, both single- or multi-quantile versions of quantile() should be compared.

This benchmark tests one column at a time; the idea behind that is that the data type and the number of NULL-values in a column may reveal different aspects of the different aggregation functions.

A graph of the results is shown below:


The column name is on the vertical axis, and the query runtimes are on the horizontal axis. For each column, there are 3 series: green, blue and orange; for each color there are three shades: darkest (user), lighter (real) and lightest (sys).

1. The green groups are the classic aggregates min(), max() and median(). For each column, there is one such group. So in this case the query looks like:

   SELECT min( <column> )
   , max( <column> )
   , median( <column> )
   FROM read_parquet( 'fhvhv_tripdata_2023-01.parquet' );
   

2. The blue groups are the multi-quantile() calls, using [0, 1, 0.5] as quantiles specification, equivalent to the classic aggregates min(), max() and median(). So in this case the query looks like:

   SELECT quantile( <column>, [ 0, 1, 0.50 ] )
   FROM read_parquet( 'fhvhv_tripdata_2023-01.parquet' );
   

3. The orange groups are the single-quantile() calls, using 3 individual quantile() calls passing 0, 1 and 0.5 for the quantile argument. So in this case, the query looks like:

   SELECT quantile( <column>, 0 )
   ,      quantile( <column>, 1 )
   ,      quantile( <column>, 0.5 )
   FROM read_parquet( 'fhvhv_tripdata_2023-01.parquet' );
       

Observation #1

Classic statistics are fastest, but only slighly so as compared to a multi-quantile()

• For each column, the green group, calling the classical statistics min(), max() and median() is the fastest. (Avg: 0.75seconds; Stdev: 0.16)
• The blue group, calling the multi-quantile variant of quantile() is slightly slower than the green group. (Avg: 0.78seconds; Stdev: 0.20) The difference is consistent, but really quite small - on average, about 5% slower compared to the green group.
• The orange group, with multiple calls to the single-quantile variant of quantile(), is substantially slower than the blue and green groups (Avg: 1.52; Stdev: 0.43). On average they last twice as long as the classic statistics (50% slower).

In general we can conclude that if you only need min(), max() and median(), you're probably best off sticking to those functions. However, if you for some reason would want to use quantile(), performance will probably be acceptably close, provided you use the multi-quantile variant. It's also clear that the single-quantile variant of quantile() really incurs significant negative impact on performance, and this should probably be avoided.

Observation #2

The column data type and distinct value-count really appears to have a discernible effect, which is a little different for the classical statistics as compared to quantile():

• For the VARCHAR columns (hvfhs_license_num , originating_base_nu, dispatching_base_num, shared_match_flag, wav_request_flag, shared_request_flag, access_a_ride_flag, and wav_match_flag), the user-timing for the classical statistics appears to be a bit lower or the same as the real-timing. Both types of quantile() calls tend to have a user timing that is often a bit higher than the real-timing.
• For the TIMESTAMP-columns (request_datetime, on_scene_datetime, pickup_datetime and dropoff_datetime), as well as for the BIGINT columns (PULocationID and DOLocationID), the user timing is typically somewhat lower than the real timing. This applies to both the classical statistics as well as the quantile() calls. The BIGINT column trip_time is a bit of a mystery in this regard, as its user-timing is higher than the real timing.
• For the DOUBLE-columns (trip_miles, base_passenger_fare, tolls, bcf, sales_tax, congestion_surcharge, airport_fee, tips, and driver_pay), user time consistently exceeds the real time somewhat.

Even though the column type appears to have some effect on the performance, it is not clear whether and how this could be used to an advantage. Also, the differences are quite slight, and do not appear to affect the main observation. Perhaps more significant difference can be detected with either larger data sets or with other data types or column value distributions.

Observation #3

For the classic statistics and the multi-quantile version of quantile(), sys measurements appear to be inconsequential: they are consistently low compared to the real and user measurments (green: 0.12; blue: 0.11), and their averages are virtually the same. I suspect that for these groups, the sys time measurement mostly has to do with time spent reading the parquet data which needs to happen anyway, regardless of what functions are tested. But for the single quantile calls to quantile() the sys measurements are consistently higher (Avg: 0.26), and not only in absolute sense: the share of the sys measurement in proportion to the real measurements is also noticeably higher: 0.17 (green: 0.16, blue: 0.14). To be sure, this may be a small increase which does not have an immediate practical consequence. But it does show that more quantile calls require more system calls, which I suspect come down to memory allocation.

More info on that in the next benchmark.

Comparing single- vs multi- quantile() variants

The idea behind this benchmark is to compare the single- and multi- variants of the quantile() function to see which one you should prefer. The results are shown in the chart below:


The query runtimes are on the vertical axis, and the number of quantiles on the horizontal axis.

For the quantiles, a set of 11 values 0.00, 0.01, 0.05, 0.10, 0.25, 0.50, 0.75, 0.90, 0.95, 0.99 and 1.00 is tested. For convenience these are stored in a DuckDB variable:

SET VARIABLE quantiles = [0.00, 0.01, 0.05, 0.10, 0.25, 0.50, 0.75, 0.90, 0.95, 0.99, 1.00];

There are 2 series: blue and orange, again in three shades (user: darkest; real: lighter; sys: lightest).

1. Again, the blue groups are the multi-quantile quantile() calls. The function is applied to all columns using a COLUMNS( * ) expression. So in this case the query looks like:

   SELECT quantile( COLUMNS( * ), getvariable('quantiles')[1:<iteration>] )
   FROM read_parquet( 'fhvhv_tripdata_2023-01.parquet' );
   

   The <iteration> is varied, starting with 1, and incremented to 2, 3, and so on, all the way up to 11, requesting a new additional quantile for each iteration.
2. The orange groups again are the single-quantile quantile() calls. Like for the blue series, the call is applied on all columns using a COLUMNS( * ) expression. The difference is that here, for each iteration, an additional call, including a COLUMNS( * ) expression is required.
   
   So, for the 1st iteration the query looks like:

   SELECT quantile( COLUMNS( * ), getvariable('quantiles')[1] )
   FROM read_parquet( 'fhvhv_tripdata_2023-01.parquet' );
   

   For the 2nd, it becomes

   SELECT quantile( COLUMNS( * ), getvariable('quantiles')[1] )
   ,      quantile( COLUMNS( * ), getvariable('quantiles')[2] )
   FROM read_parquet( 'fhvhv_tripdata_2023-01.parquet' );
   

   ...And so on.
   
   One might guess this should go up all the way up to 11 too, but alas: the series stops at 7. Further iterations resulted in an out of memory error, so the benchmark could not be completed. That said, the iterations that did run already provide ample information!

Observation #1

The blue group - the multi-quantile calls - demonstrate moderate and predictable runtime increases as more quantiles are requested. Each iteration is a few percents slower than the previous one, but considering the fact that each iteration also collects another quantile, that seems perfectly acceptable.

The orange group with the single-quantile calls fares quite differently! Here, each next iteration takes about twice the time of the previous iteration. As a result, it becomes unacceptably slow very quickly, and even then runs into an out of memory error after iteration 7.


Perhaps the slowdown for each iteration is all down to time required for memory allocation, or perhaps there is some other reason - this cannot be concluded from this test. But we can definitely conclude that the multi-quantile call should be preferred whenever collecting more than quantile at once.

In conclusion

I hope you enjoyed this post! I'm happy I could just resolve some questions from my end. I think the takeaways are clear:

• If you only need min(), max() and median(), you're fine! You don't need to switch to quantile() for performance reasons. That said, it also doesn't hurt, provided you use the multi-quantile() version of quantile().
• If you need to collect multiple quantiles beyond min(), max() and median(), you should probably be using quantile( expr, [...quantiles...] ).
• If you're using single quantile quantile() calls, you're either doing something very special, or you're doing something wrong. At any rate, beware of out of memory errors, and investigate whether you can bunch up several single quantile calls into one multi-quantile call - it will save memory and probably increase performance, potentially a lot!

If you found this interesting, you might want to investigate some of the topics that were mentioned but not explored any further, such as:

• Approximate aggregate functions, like approx_quantile() and reservoir_quantile().
• Using quantile_cont() instead of quantile_disc(), especially if you require value extrapolation.
• The quantile_cont() function also supports array syntax for specifying multiple quantiles. It's definiteley worth comparing that to both its single quantile version, as well as to quantile_disc(). They appear to have quite different implementations, going by their type signatures. Therefore, there may be interesting and perhaps unexpected differences in performance.

Feel free to leave some feedback - I read and respond to your comments.

DataZen winter meetup 2025

The DataZen winter meetup 2025 is nigh!


Join us 18 - 20 February 2025 for 3 days of expert-led sessions on AI, LLM, ChatGPT, Big Data, MLOps, and more. This FREE online event is open to data enthusiasts of all levels!

Checkout the program here: https://wearecommunity.io/events/winter-data-meetup2025/talks/84304

I'm doing a talk on DuckDB and Huey - an open source browser app for pivoting hundreds of millions of rows directly in your browser:



My talk is on february 18 2025, 10:00- 11:00 CET Huey: Blazing-Fast Browser Pivot Tables with DuckDB/WASM.

In this session, I’ll cover:

• ✅ Emerging trends in data and analytics, including the rise of DuckDB and Small Data
• ✅ How to build high-performance analytical browser apps using DuckDB/WASM
• ✅ The workings of Huey - a tool for pivoting hundreds of millions of rows directly in your browser
• ✅ Plus, a live demo to demonstrate Huey's speed and user-friendliness

If you're a data engineer, developer, or anyone excited about cutting-edge analytics tools, join me to learn about the future of browser-based analytics and maybe start building your own DuckDB/WASM apps! 📅 Register now - it's time to redefine data pivoting!

I'm looking forward to seeing you there!

DuckDB Bag of Tricks: Reading JSON, Data Type Detection, and Query Performance

DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB.

This post is about a particular challenge posted on the DuckDB Discord server which happened to interest me. So, here goes!!

Yesterday on the DuckDB discord server user @col_montium asked:

I've got a bit of a head scratcher. My json from https://api.census.gov/data/2000/dec/sf1/variables.json is a single struct with dynamic keys. I want to extract a table with columns field_code, label, concept, and group. I completed this with a small sample of the data but with the entire dataset the query uses up 24 gigabytes of RAM and then crashes. Here's my query:

WITH source AS (
  SELECT *
  FROM 'https://api.census.gov/data/2000/dec/sf1/variables.json'
),
all_keys AS (
  SELECT unnest( json_keys( variables ) ) AS "key",
         variables
  FROM   source
),
extracted_fields AS (
  SELECT "key" AS field_code
  ,      variables->key->>'$.label' AS label
  ,      variables->key->>'$.concept' AS concept
  ,      variables->key->>'$.predicateType' AS predicate_type
  ,      variables->key->>'$.group' AS "group"
  FROM all_keys
  WHERE "key" NOT IN ('for', 'in', 'ucgid')
)
SELECT *
FROM extracted_fields
ORDER BY field_code

I tried to run this myself on my laptop using the DuckDB 1.1.3 (GA) command line interface, and it failed with a similar error as reported by @col_montium:

Run Time (s): real 252.335 user 197.031250 sys 9.500000
Out of Memory Error: failed to allocate data of size 16.0 MiB (24.9 GiB/25.0 GiB used)

I was able to run it successfully using a 1.1.4 nightly build, but it took about 3 minutes to complete:

Run Time (s): real 188.681 user 148.500000 sys 8.078125

Dataset: Size and Structure

Surprisingly, the source data set is less than 2MB (2,039,748 bytes)! The query also doesn't appear to be terribly complicated, and if it finishes successfully, it yields only 8141 rows. So, clearly something interesting must be the matter with the structure of the dataset.

As it is so modest in size, we can easily inspect it with a text editor:

{
  "variables": {
    "for": {
      "label": "Census API FIPS 'for' clause",
      "concept": "Census API Geography Specification",
      "predicateType": "fips-for",
      "group": "N/A",
      "limit": 0,
      "predicateOnly": true
    },
    "in" {
      "label": "Census API FIPS 'in' clause",
      "concept": "Census API Geography Specification",
      "predicateType": "fips-in",
      "group": "N/A",
      "limit": 0,
      "predicateOnly": true
    },
    "ucgid" {
      "label": "Uniform Census Geography Identifier clause",
      "concept": "Census API Geography Specification",
      "predicateType": "ucgid",
      "group": "N/A",
      "limit": 0,
      "predicateOnly": true,
      "hasGeoCollectionSupport": true
    },
    "P029009" {
      "label": "Total!!In households!!Related child!!Own child!!6 to 11 years",
      "concept": "RELATIONSHIP BY AGE FOR THE POPULATION UNDER 18 YEARS [46]",
      "predicateType": "int",
      "group": "P029",
      "limit": 0
    },
    
    ...many more variables...
    
    "PCT012H185" {
      "label": "Total!!Female!!78 years",
      "concept": "SEX BY AGE (HISPANIC OR LATINO) [209]",
      "predicateType": "int",
      "group": "PCT012H",
      "limit": 0
    }
  }
}    

The structure of the dataset is quite simple: a single object with a single variables property, which has an object value with many object-typed properties. The object-type values of these properties contain a handful of recurring scalar properties like label, concept, predicateType, group and limit.

Now that we analyzed the structure, it should be clear what the query attempts to achieve. It wants to make a row for each property of the outermost variables-object. The property name becomes the field_code column, and forms its natural primary key. The recurring properties of the innermost objects become its non-key columns label, concept, predicate_type and group.

Using read_text() and a DuckDB table column with the JSON-datatype

In an attempt to get the query working without running out of memory, I decided to store the dataset in a DuckDB table like so:

CREATE TABLE t_json
AS
SELECT  filename
,       content::JSON AS data
FROM    read_text('https://api.census.gov/data/2000/dec/sf1/variables.json');

With this in place, a modified version of the initial query might be:

WITH variables AS (
  SELECT  data->variables                         AS variables
  ,       unnest( json_keys( variables ) )        AS field_code
  FROM    t_json
)
SELECT    field_code
,         variables->field_code->>'label'         AS label
,         variables->field_code->>'concept'       AS concept
,         variables->field_code->>'predicateType' AS predicate_type
,         variables->field_code->>'group'         AS group
FROM      variables
WHERE     field_code NOT IN ('for', 'in', 'ucgid')
ORDER BY  field_code

The results look like this:

┌────────────┬──────────────────────────────────┬─────────┬────────────────┬─────────┐
│ field_code │              label               │ concept │ predicate_type │  group  │
│  varchar   │             varchar              │ varchar │    varchar     │ varchar │
├────────────┼──────────────────────────────────┼─────────┼────────────────┼─────────┤
│ AIANHH     │ American Indian Area/Alaska Na…  │ NULL    │ NULL           │ N/A     │
│ AIHHTLI    │ American Indian Area (Off-Rese…  │ NULL    │ NULL           │ N/A     │
│ AITSCE     │ American Indian Tribal Subdivi…  │ NULL    │ NULL           │ N/A     │
│ ANRC       │ Alaska Native Regional Corpora…  │ NULL    │ NULL           │ N/A     │
│ BLKGRP     │ Census Block Group               │ NULL    │ NULL           │ N/A     │
│ BLOCK      │ Census Block                     │ NULL    │ NULL           │ N/A     │
│ CD106      │ Congressional District (106th)   │ NULL    │ string         │ N/A     │
│ CONCIT     │ Consolidated City                │ NULL    │ NULL           │ N/A     │
│   ·        │         ·                        │  ·      │  ·             │  ·      │
│   ·        │         ·                        │  ·      │  ·             │  ·      │
│   ·        │         ·                        │  ·      │  ·             │  ·      │
│ SUBMCD     │ Sub-Minor Civil Division (FIPS)  │ NULL    │ NULL           │ N/A     │
│ SUMLEVEL   │ Summary Level code               │ NULL    │ string         │ N/A     │
│ TRACT      │ Census Tract                     │ NULL    │ NULL           │ N/A     │
│ UA         │ Urban Area                       │ NULL    │ NULL           │ N/A     │
│ US         │ United States                    │ NULL    │ NULL           │ N/A     │
│ ZCTA3      │ ZIP Code Tabulation Area (Thre…  │ NULL    │ NULL           │ N/A     │
│ ZCTA5      │ Zip Code Tabulation Area (Five…  │ NULL    │ NULL           │ N/A     │
├────────────┴──────────────────────────────────┴─────────┴────────────────┴─────────┤
│ 8141 rows (15 shown)                                                     5 columns │
└────────────────────────────────────────────────────────────────────────────────────┘
Run Time (s): real 11.682 user 8.015625 sys 2.406250

Less than 12 seconds. Not super-fast, but still: a pretty substantial improvement as compared to the initial query. What could explain this difference? Two things stand out as compared to the initial query:

• Using the read_text()-function to ingest the data. In the original query, the data was ingested by the first common table expression called source, which did a SELECT directly FROM the dataset's URL 'https://api.census.gov/data/2000/dec/sf1/variables.json'. Clearly, with this syntax, DuckDB will do some magic to perform the appropriate action, which in this case will be to invoke read_json_auto('https://api.census.gov/data/2000/dec/sf1/variables.json').
• Explicitly casting the content of the data file to DuckDB's JSON-datatype. I expected the issue with the original query had something todo with the transformation of JSON data to a tabular result. Having the data in a JSON-datatype seemed like a good start to play with different methods to extract data. The read_text()-function returns the contents of the file as VARCHAR. It cannot and does not attempt to parse or process the data. That's why we need to cast its output explictly to the JSON-type ourselves.

You might object to a comparison as this query does not include the time to fetch the dataset from the internet, nor the time to store it in the DuckDB table. For me that takes about 6 - 10 seconds. However, we can easily rewrite the query to include read_text() directly:

WITH variables AS (
  SELECT  json( content )->variables              AS variables
  ,       unnest( json_keys( variables ) )        AS field_code
  FROM    read_text( 'https://api.census.gov/data/2000/dec/sf1/variables.json' )
)
SELECT    field_code
,         variables->field_code->>'label'         AS label
,         variables->field_code->>'concept'       AS concept
,         variables->field_code->>'predicateType' AS predicate_type
,         variables->field_code->>'group'         AS group
FROM      varibles
WHERE     field_code NOT IN ('for', 'in', 'ucgid')
ORDER BY  field_code

This query takes about 20 seconds, in other words: about the same time as it takes to run the previous query plus the time to fetch the dataset and load it in the table. For a serious comparison of query performance we should probably eliminate the download and use only local files, but we'll get to that later. For now it's enough to notice that this setup is a significant improvement, both in terms of query execution time as well as in stability/memory consumption.

You might also have noticed this alternative query uses slightly different syntax to the extract label, concept, predicateType and group. Also, the final data presentation query is combined with the field extraction logic. However, these changes are really a matter of style. They do not have any bearing on query performance - not for this data set anyway.

Understanding the difference: the JSON-reader

With these results in mind, we have to conclude that the actual operations on the JSON data cannot really explain the difference. So, it must have something todo with the JSON-reader that was implicitly invoked by the initial query. Let's zoom in a bit on the difference between data extracted from a JSON-typed value and data extracted by the JSON-reader.

We know that in the second example, the data is of the JSON-type, because we explicitly cast it to that type. However, that only tells us that the data is essentially text, conforming to the JSON-syntax. It is of course very useful to know the encoding scheme used to represent values and structures, that still does not reveal anything about the content and structure of the data itself.

What data type does the JSON reader think the data has? Let's find out using this DESCRIBE-statement:

DESCRIBE
SELECT *
FROM 'https://api.census.gov/data/2000/dec/sf1/variables.json'

Its result is:

┌─────────────┬─────────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │         column_type         │  null   │   key   │ default │  extra  │
│   varchar   │           varchar           │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ variables   │ STRUCT("for" STRUCT("labe…  │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────────────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 9.541 user 1.968750 sys 0.031250

As you can see, the variables property of the main object that makes up our dataset was detected and projected as a column. DuckDB has also inferred a pretty detailed data type for the column, expressed using DuckDB native SQL data types.

In the example above it's truncated, but if we run it again using .mode line, we can see the entire type descriptor. It goes like this:

STRUCT(
  "for" STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  , predicateOnly BOOLEAN
  )
, "in" STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  , predicateOnly BOOLEAN
  )
, ucgid STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  , predicateOnly BOOLEAN
  , hasGeoCollectionSupport BOOLEAN
  )
, P029009 STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  )
  
  ...many more ...
  
, PCT012H185 STRUCT(
    "label" VARCHAR
  , concept VARCHAR
  , predicateType VARCHAR
  , "group" VARCHAR
  , "limit" BIGINT
  )
)

Looks familiar? It should, as it corresponds closely to the structure we witnessed when analyzing the raw JSON data using a text editor.

So, this really is quite different as compared to the JSON-type column, as the JSON-reader must have done some work to explore the structure of the data. Not only did it detect the data type, it also uses the type to represent the data, which in turn determines what operators and functions will be available by the remainder of the query to work with the data.

Whether this is related to the difference in performance and to the out of memory errors, is yet to be determined, but it does not seem unlikely. For starters, the data type closely resembles the data. The textual description of the data type is about half the size of the data itself, which does not seem like a good thing!

We will investigate the JSON-readers' capabilities for data type detection and the possibilities to control that in the next couple of sections. But before we examine that in more detail, let's take a step back and consider what we just learned.

One lesson learned

So far, we've learned at least one important lesson: the JSON reader does in fact do exactly what its name implies - read JSON-encoded data. But while doing so, it tends to output data using DuckDB native data types, and typically not the JSON data type.

In hindsight this certainly sounds perfectly sensible. Yet, both me and @col_montium apparently did not realize that fully, as the original query takes the data coming out of the JSON reader, and processes it using functions like json_keys(), and the extraction operators -> and ->>. These are designed for working with values in the JSON data type, and not for the STRUCT values that the JSON reader hands back to us.

You may wonder: how it is possible that these functions and operators work at all on the STRUCT value returned by the JSON reader? The answer is quite simple: when we apply these functions and operators, they first implicitly cast their arguments and operands to the JSON-type. So, just as the JSON reader spent all its effort to read raw JSON-encoded data and convert it into neat native DuckDB STRUCTs, we immediately undid that effort only to convert them back to a JSON-type. And initially we - or at least I - didn't even realize it.

Actually - it is a little bit different still, as the JSON-reader deals with reading JSON-encoded text whereas the JSON-data type is still a database type. But the point here is that the JSON-reader did work to provide a precise description of the data as STRUCTs, and we didn't bother treating the data as such. Instead, we implicitly cast it to the JSON-type, which is much looser. So it feels there might be an unused opportunity to benefit from the detected data type. We will examine this possibility in the next section.

Avoiding the implicit JSON-type cast?

Now that we have a better understanding of what we've done, could we rewrite the original query so we can avoid the implicit JSON-type cast, and benefit from the DuckDB native types returned by the JSON reader? Somewhat surprisingly, this is not quite as simple as one might think it would (or should) be.

First of all, we cannot simply use whatever are the STRUCT equivalents for the JSON-type functions and operators.

For example, it is currently not possible to extract the keys from a STRUCT type. So there's no straightforward equivalent to how the original query uses json_keys() and then unnest() to spread out the keys from the original variables object into separate rows. (In the DuckDB github repository, @pkoppstein opened a discussion about creating a feature to extract the keys from STRUCTs in case you want to chime in.)

Even if we somehow managed to extract the keys from the STRUCT, there is currently no way to use it to extract the corresponding value. That is, STRUCTs of course support extraction methods, but all of them require the key to be a constant value. This prohibits using a column value, such as what we would get if we'd use an unnested set of keys:

WITH field_codes AS (
  SELECT  unnest( json_keys( variables ) ) AS field_code
  ,       variables
  FROM    'https://api.census.gov/data/2000/dec/sf1/variables.json'
)
SELECT  field_code
,       variables[field_code] AS "variable"
FROM    field_codes
WHERE   field_codes NOT IN ('for', 'in', 'ucgid')

In the example above, variables is the STRUCT-typed value we receive from the JSON-reader. The expression variables[field_code] attempts to extract a value from it using the column value field_code. The statement fails because field_code is not a constant:

Run Time (s): real 8.345 user 1.531250 sys 0.031250
Binder Error: Key name for struct_extract needs to be a constant string

As the error message suggests, the square bracket extraction syntax is just syntactic sugar for the struct_extract() function. Using a function call instead yields the exact same error.

A Solution based on STRUCTs

Eventually I came up with an approach that at least allows me to envision what the solution based on STRUCTs, and without relying on the JSON-type might look like. Here's that attempt:

WITH variables AS (
  UNPIVOT (
    WITH variables AS (
      SELECT  COLUMNS( variables.* EXCLUDE( "for", "in", "ucgid" ) )
      FROM    read_json( 'https://api.census.gov/data/2000/dec/sf1/variables.json' )
    )
    SELECT  struct_pack(
              label := struct_extract( 
                COLUMNS( * )
              , 'label' 
              )
            , concept := struct_extract( 
                COLUMNS( * )
              , 'concept' 
              )
            , predicateType := struct_extract( 
                COLUMNS( * )
              , 'predicateType' 
              )
            , "group" := struct_extract( 
                COLUMNS( * )
              , 'group' 
              )
            )
    FROM    variables
  )
  ON( * )
  INTO NAME field_code VALUE "variable"
)
SELECT field_code, "variable".*
FROM variables
ORDER BY field_code

The main idea here is to use variables.* to turn the keys of the STRUCT given to us by the JSON-reader into separate columns. The UNPIVOT-statement can then turn those columns into rows. In the original query this was achieved by combining json_keys() and unnest().

Since we don't need the metadata-like objects ucgid, for and in, we also don't need to unpack them from the variables object. So, we wrapped variables.* into a COLUMNS-"star expression" so we could add an EXCLUDE-clause to remove them. In the original query, those where removed in the WHERE-clause.

The UNPIVOT-statement not only creates rows from columns, but also lets us transform the column names into values. We use the NAME-clause to collect them into a new field_code column. The VALUE-clause can be used to name the column that receives the corresponding column values. In the example above, that value column is assigned the name variable.

Now, the values extracted from the variables-object are themselves STRUCTs, and they do not all have the same set of keys. From the DuckDB persective, they therefore have distinct types, and thus cannot all simply be lumped together directly into a single column.

However, we're only interested in a particular set of properties. They are 'label', 'concept', 'predicateType', and 'group'. If the type of their value is the same across all objects, then we can extract them and create new STRUCTs having only those keys. This is achieved using struct_extract() and struct_pack().

Again, the COLUMNS(*)-"star expression" proves to be a very useful tool! We use it here as argument for struct_extract(). This way, we need to write the STRUCT extraction-and-assembly business only once. The star expression then applies it to all columns without having to name them explicitly. The STRUCT-value assembled by struct_pack() gets the name of the original column.

The final touch is to unwrap the STRUCTs we assembled into separate columns. This is done in the terminal SELECT, again using variable.* syntax.

Nice! Pity it works only in theory

When we run it, we get:

Run Time (s): real 14.183 user 12.484375 sys 0.062500
Binder Error: Could not find key "concept" in struct

Apparently, not only do the objects in our data set sometimes have more keys than we're interested in, some also don't have all the keys we're attempting to extract. There does not seem to be any way to detect whether some arbitrary STRUCT-value has a specific key, so we can't simply work around it.

If we comment out the extraction for the the concept key, we get the same error but now for the predicateType key. If you comment out that extraction too, the query executes succesfully. On my laptop, it takes about as long as the original query, that is to say, much worse than the alternative using the JSON-type.

So even if we would somehow be able to overcome the issues with extracting the properties and creating the STRUCT, we still can't seem to really benefit from the more strict typing. I guess the main take-away here is, we can STRUCTggle all we want, but our data simply appears not to be given to us in a way that allows it to work for us.

JSON reader Parameters

We mentioned earlier that SELECT-ing directly from the url, as the original query did, causes DuckDB to invoke read_json() or read_json_auto(), which are synonyms of each other. If you like, you can convince yourself by running an equivalent DESCRIBE-statement that explicitly invokes the reader:

DESCRIBE
SELECT *
FROM read_json( 'https://api.census.gov/data/2000/dec/sf1/variables.json' )

If you execute it, you'll notice that the inferred column name and data type are identical to that returned by the prior DESCRIBE-statement. But invoking the reader explicitly has a benefit in that it offers us the possibility to pass parameters to control its behavior. Some relevant to the topic at hand are:

BOOLEAN auto_detect

Whether to auto detect the schema at all. Contrary to what the current documentation states, the default value is TRUE rather than FALSE.

STRUCT(name VARCHAR, type VARCHAR) columns

If you choose auto_detect to be FALSE, you are required to explicitly specify the columns and column types.

INTEGER maximum_depth

The number of nesting levels that are considered when detecting the datatype. The default is -1, which means there is no restriction on the depth of type detection. When set to a positive integer, and there are object-type values at the maximum level of nesting, then those will get 'detected' as being of the JSON-type.

Controlling the depth of the JSON-reader's type detection

Now that we learned about the maximum_depth parameter, let's apply it and experience its effect:

DESCRIBE
SELECT  *
FROM    read_json( 
          'https://api.census.gov/data/2000/dec/sf1/variables.json' 
        , maximum_depth = 0
        )

At maximum_depth = 0, there is no detection at all, and the entire data set is just a JSON-typed value:

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ json        │ JSON        │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 7.583 user 0.375000 sys 0.015625

This is on par from what we achieved with the read_text() and explicitly casting its content column to the JSON-type. Let's allow for one level:

DESCRIBE
SELECT  *
FROM    read_json( 
          'https://api.census.gov/data/2000/dec/sf1/variables.json' 
        , maximum_depth = 1
        )

At maximum_depth = 1 we get at least extraction into separate columns. However, the column data type will still be the generic JSON-type:

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ variables   │ JSON        │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 8.521 user 0.343750 sys 0.000000

We could allow another level:

DESCRIBE
SELECT  *
FROM    read_json( 
          'https://api.census.gov/data/2000/dec/sf1/variables.json' 
        , maximum_depth = 2
        )

At maximum_depth = 2, the type detected for the variables column is a bag of properties of the JSON-type. In the DuckDB type system, this is represented as MAP( VARCHAR, JSON ):

┌─────────────┬────────────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │    column_type     │  null   │   key   │ default │  extra  │
│   varchar   │      varchar       │ varchar │ varchar │ varchar │ varchar │
├─────────────┼────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ variables   │ MAP(VARCHAR, JSON) │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴────────────────────┴─────────┴─────────┴─────────┴─────────┘
Run Time (s): real 8.229 user 0.343750 sys 0.000000

This is interesting! So far we've seen STRUCTs, but not MAPs before.

JSON-objects as DuckDB MAPs

Let's attempt to rewrite the original query using the MAP( VARCHAR, JSON )-typed value we get from the JSON-reader when we pass maximum_depth = 2:

WITH variables AS (
  SELECT  unnest( map_keys( variables ) )   AS field_code
  ,       unnest( map_values( variables ) ) AS "variable"
  FROM    read_json( 
            'https://api.census.gov/data/2000/dec/sf1/variables.json' 
          , maximum_depth = 2
          )
)
SELECT    field_code
,         "variable"->>'label'              AS label
,         "variable"->>'concept'            AS concept
,         "variable"->>'predicateType'      AS predicate_type
,         "variable"->>'group'              AS group
FROM      variables
WHERE     field_code NOT IN ('for', 'in', 'ucgid')
ORDER BY  field_code

The main difference as compared to using only the JSON-type, is that the MAP-type lets us extract both the keys as well as the corresponding values using map_keys() and map_values() respectively. This means the final SELECT does not have to extract the variable object explicitly using the key: instead, we can immediately extract properties of interest from the variable objects.

In addition, the main difference with the original query is that the JSON-reader now hands the variable objects to us as JSON-typed values. The original query relied on implicit typecasting, which does not happen here.

If we download the JSON file and store it on the local disc, and modify our queries to use that, the results are quite remarkable: the one using read_text() and the explicit cast to the JSON-type takes about 12 seconds. However, that result is smashed completely by the last query, which now takes just half a second! A really nice improvement!

Conclusion

• DuckDB's feature to SELECT directly from a file or URL is convenient, but it pays off to examine the underlying reader invocation and its parameters.
• When using the JSON-reader, it's a good idea to examine whether the detected datatype is suitable for the extraction you're attempting to perform. By default, the JSON-reader will exhaustively detect the type. If you find the detected type is large as compared to your dataset, and/or more precise or detailed than required for your purpose, try using the maximum_depth parameter to curtail the type detection.
• If you're using the JSON-reader and find that your extraction logic relies on JSON-type functions and operators, then beware of implicit casting of your extracted data to the JSON-type. Implicit casting to the JSON-type may point to an opportunity to limit the level of data type detection.
• When reading JSON-data, you can try extracting from a JSON-typed value - either by loading your data to a table with a JSON-typed column, or using a combination of read_text() and an explicit cast to the JSON-type. Even if your goal is use the JSON-reader, the JSON-typed value is a useful baseline: if you find that your JSON-reader based query is slower than the one on the JSON-typed value, it means you have an opportunity to improve.
• If you're using json_keys() with the purpose of extracting objects or values from the JSON-data, then consider using the JSON-reader and configuring it so that it returns a MAP-type. The MAP functions map_keys() and map_values() are really fast, and may help you avoid an an extra step to use the key to extract an object.
• It would be nice if STRUCTs would be a little bit more flexible. For example, I'd like to be able to extract field values non-constant expressions. It would also be useful to have a function to obtain their keys, and it would help to check if it has a particular key. Another feature that would also have helped is if it would be possible to define STRUCT-fields to be optional, or to have a default value.

DuckDB bag of tricks: Processing PGN chess games with DuckDB - Rolling up each game's lines into a single game row (6/6)

DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB.

This post is the sixth installment of a series in which I share tips and tricks on how to use DuckDB for raw text processing. As a model, we will use Portable Game Notation (PGN), a popular format for digitized recording Chess games.

The installments are:

1. Chess and Portable Game Notation (PGN)
2. Ingesting raw text with the CSV Reader
3. Distinguishing the Line Type
4. Keeping game lines together: window functions
5. Extracting Tagpairs with Regular Expressions
6. Rolling up each game's lines into a single game row

Rolling up each game's lines into a single game row

All essential elements are in place for transforming the PGN lines into a tabular result. The actual transformation entails two things:

• Grouping all lines with the same game_id into one single row.
• Create columns for each unique value of tag_name from tag_pair, and place the corresponding tag_value into those columns.

We can do that using DuckDB's PIVOT-statement.

Typical PIVOT-statements

The PIVOT-statement is typically used for OLAP use cases to create analytical crosstabs. In this context, we can think of PIVOT as an extension of a standard SELECT-statement with a GROUP BY-clause: each row is actually an aggregate row that represents the group of rows from the underlying dataset that have a unique combination of values for all expressions appearing in the GROUP BY-clause.

In addition, PIVOT also has an ON-clause, and each unique combination of values coming from the ON-clause expressions generates an aggregate column.

At the intersection of the aggregate rows and aggregate columns of the crosstab are the cell values. These are specified by the USING-clause, which specifies aggregate function expressions that are to be applied on those rows from the underlying resultset that belong to both the aggregate row and the aggregate column.

In typical OLAP use cases, the cell values are typically SUM()s or AVG()s over monetary amounts or quantities, sometimes COUNT()s.

PIVOT as a row-to-column transposer

The description of the PIVOT-statement above provides some hints on how we can use it to transform the game lines to a single game row, and how to turn the tags into separate columns:

• We want to roll up the game lines of one game into a single game row, so the game_id expression should be placed in the GROUP BY-clause.
• The tag names should be used to generate columns, so the tag_name-member of the tag_pair STRUCT-value returned by regexp_extract() should be placed in the ON-clause.
• The tag_values should appear as cell values, so the tag_value-member of tag_pair should be placed in the USING-clause.

This makes it all a bit different from the typical analytical crosstabs:

• The tag_values that are to appear as cell values are primarily of a text type: player names, event locations, and sometimes date- or time-like values. There are some numerical values too, like Elo scores, but these are non-additive, and quite unlike the amounts and quantities we find in the typical OLAP-case.
• As the tagspairs are just attributes of the game, we expect set of tag_name-values for in one game to be unique. That means that for each game, we will find at most one tag_value in each generated column.

So for the PGN use case, it is more natural to think of the PIVOT-statement as a device to transpose rows to column, rather than an analytical crosstab.

Even though we expect only a single text value for the tag_value, the PIVOT-statement's USING-clause still requires some kind of aggregate function. To aggregate tag_value we can settle for anything that preserves the text: MIN(), MAX(), or ANY_VALUE(), as well as the text aggregate STRING_AGG() would all do.

When we put it all together, this is what our inital PIVOT-statement looks like:

PIVOT(
SELECT  line
,       line LIKE '[%'                               AS is_header
,       COUNT(CASE line LIKE '1.%' THEN 1 END) OVER (
         ROWS BETWEEN UNBOUNDED PRECEDING
              AND     CURRENT ROW
        ) + 
        CASE
          WHEN is_header THEN 1
          ELSE 0
        END                                          AS game_id
,       CASE
          WHEN is_header THEN
            regexp_extract(
              line
            , '^\[([^\s]+)\s+"((\\["\\]|[^"])*)"\]*$'
            , [ 'tag_name', 'tag_value' ]
            )
        END                                          AS tag_pair
FROM    read_csv(
          'C:\Users\Roland_Bouman\Downloads\DutchClassical\DutchClassical.pgn'
        , columns = {'line': 'VARCHAR'}
        )
WHERE   line IS NOT NULL
)
ON tag_pair['tag_name']
USING ANY_VALUE( tag_pair['tag_value'] )
GROUP BY game_id

This is what its result looke like:

┌─────────┬──────────────────────┬──────────┬────────────┬───┬─────────┬─────────────────────┬──────────────────────┬──────────┐
│ game_id │        Black         │ BlackElo │    Date    │ . │  Round  │        Site         │        White         │ WhiteElo │
│  int64  │       varchar        │ varchar  │  varchar   │   │ varchar │       varchar       │       varchar        │ varchar  │
├─────────┼──────────────────────┼──────────┼────────────┼───┼─────────┼─────────────────────┼──────────────────────┼──────────┤
│       1 │ Pollock, William H.  │          │ 1895.??.?? │ . │ ?       │ Hastings            │ Tinsley, Samuel      │          │
│       2 │ Lasker, Edward       │          │ 1913.??.?? │ . │ ?       │ Scheveningen        │ Loman, Rudolf        │          │
│       3 │ Tartakower, Saviely  │          │ 1921.??.?? │ . │ 5       │ The Hague           │ Alekhine, Alexander  │          │
│       4 │ Wegemund, Otto       │          │ 1922.??.?? │ . │ 7       │ Bad Oeynhausen      │ Antze, O.            │          │
│       5 │ Tarrasch, Siegbert   │          │ 1922.??.?? │ . │ 19      │ Bad Pistyan         │ Johner, Paul F       │          │
│       6 │ Alekhine, Alexander  │          │ 1922.??.?? │ . │ ?       │ Hastings            │ Bogoljubow, Efim     │          │
│       7 │ Kmoch, Hans          │          │ 1922.??.?? │ . │ ?       │ Vienna              │ Rubinstein, Akiba    │          │
│       8 │ Mieses, Jacques      │          │ 1923.??.?? │ . │ 9       │ Hastings            │ Norman, George Mar.  │          │
│       9 │ Orlando, Placido     │          │ 1923.??.?? │ . │ ?       │ Trieste             │ Szabados, Eugenio    │          │
│      10 │ Tarrasch, Siegbert   │          │ 1923.??.?? │ . │ 1       │ Trieste             │ Seitz, Jakob Adolf   │          │
│      11 │ Wolf, Siegfried Re.  │          │ 1923.??.?? │ . │ 5       │ Vienna              │ Von Patay, J.        │          │
│      12 │ Tartakower, Saviely  │          │ 1924.??.?? │ . │ ?       │ New York            │ Bogoljubow, Efim     │          │
│      13 │ Pokorny, Amos        │          │ 1926.??.?? │ . │ 3       │ Trencianske Teplice │ Kostic, Boris        │          │
│      14 │ Tartakower, Saviely  │          │ 1927.??.?? │ . │ 2       │ Kecskemet           │ Vukovic, Vladimir    │          │
│      15 │ Botvinnik, Mikhail   │          │ 1927.??.?? │ . │ 2       │ Moscow              │ Rabinovich, Ilya L.  │          │
│       · │      ·               │  ·       │     ·      │ · │ ·       │   ·                 │    ·                 │  ·       │
│       · │      ·               │  ·       │     ·      │ · │ ·       │   ·                 │    ·                 │  ·       │
│       · │      ·               │  ·       │     ·      │ · │ ·       │   ·                 │    ·                 │  ·       │
│    7229 │ Kovacevic,Bl         │ 2400     │ 2023.12.09 │ . │ 7.3     │ Zagreb CRO          │ Kozul,Z              │ 2532     │
│    7230 │ Iskos,A              │ 2153     │ 2023.12.10 │ . │ 6.46    │ Skopje MKD          │ Zhezhovska,Monika    │ 1826     │
│    7231 │ Spichkin,A           │ 2035     │ 2023.12.12 │ . │ 2       │ chess.com INT       │ Rodriguez Santiago,J │ 2043     │
│    7232 │ Rogov,Matfey         │ 2213     │ 2023.12.12 │ . │ 3       │ chess.com INT       │ Clarke,Matthew       │ 2127     │
│    7233 │ Osmonbekov,T         │ 2137     │ 2023.12.12 │ . │ 3       │ chess.com INT       │ Sroczynski,M         │ 2266     │
│    7234 │ Novikova,Galina      │ 2073     │ 2023.12.12 │ . │ 8       │ chess.com INT       │ Marcziter,D          │ 2192     │
│    7235 │ Tomazini,A           │ 2336     │ 2023.12.14 │ . │ 5.24    │ Zagreb CRO          │ Pultinevicius,Paul.  │ 2584     │
│    7236 │ Spichkin,A           │ 2035     │ 2023.12.19 │ . │ 2       │ chess.com INT       │ Levine,D             │ 2040     │
│    7237 │ Kanyamarala,Tarun    │ 2305     │ 2023.12.19 │ . │ 4       │ chess.com INT       │ Nechitaylo,Nikita    │ 2203     │
│    7238 │ Ronka,E              │ 2291     │ 2023.12.19 │ . │ 4       │ chess.com INT       │ Gruzman,Ilya         │ 2151     │
│    7239 │ Kurbonboeva,Sarvinoz │ 2154     │ 2023.12.26 │ . │ 1.37    │ Samarkand UZB       │ Mammadzada,G         │ 2449     │
│    7240 │ Koneru,H             │ 2554     │ 2023.12.26 │ . │ 3.24    │ Samarkand UZB       │ Peycheva,Gergana     │ 2271     │
│    7241 │ Carlsson,Andreas     │ 1902     │ 2023.12.28 │ . │ 4.9     │ Karlstad SWE        │ Kreken,Eivind Grunt  │ 2271     │
│    7242 │ Kazarjan,Gachatur    │ 2078     │ 2023.12.30 │ . │ 9.31    │ Groningen NED       │ Schuricht,Emil Fre.  │ 2095     │
│    7243 │ Kurbonboeva,Sarvinoz │ 2154     │ 2023.12.30 │ . │ 14.47   │ Samarkand UZB       │ Zhu Chen             │ 2423     │
├─────────┴──────────────────────┴──────────┴────────────┴───┴─────────┴─────────────────────┴──────────────────────┴──────────┤
│ 7243 rows (30 shown)                                                                                    11 columns (8 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

This certainly is starting to look a lot like the result we were after.

Folding in the movetext

The only thing stil missing is the movetext, but at this point, it's almost trivial to add that as well. We can simply amend the tag_pair-expression and let it return a new STRUCT-value with the literal text 'moves' as name member and the line itself as value in case the is_header expression is not TRUE:

CASE
  WHEN is_header THEN
    regexp_extract(
      line
    , '^\[([^\s]+)\s+"((\\["\\]|[^"])*)"\]*$'
    , [ 'column_name', 'column_value' ]
    )
  ELSE {
    'column_name': 'moves'
  , 'column_value': line
  }
END AS column_name_value

For consistency, we also changed the tag_pair alias to column_name_value and its member names from column_name and column_value to column_name and column_value respectively. Therefore we must also update the references elsewhere in the PIVOT statement accordingly.

Also, because one game could have multiple lines of movetext, we must also change the aggregate function in the USING-clause from ANY_VALUE() to STRING_AGG(). After these changes we get the final statement:

PIVOT(
SELECT  line
,       line LIKE '[%'                               AS is_header
,       COUNT(CASE line LIKE '1.%' THEN 1 END) OVER (
         ROWS BETWEEN UNBOUNDED PRECEDING
              AND     CURRENT ROW
        ) + 
        CASE
          WHEN is_header THEN 1
          ELSE 0
        END                                          AS game_id
,       CASE
          WHEN is_header THEN
            regexp_extract(
              line
            , '^\[([^\s]+)\s+"((\\["\\]|[^"])*)"\]*$'
            , [ 'column_name', 'column_value' ]
            )
          ELSE {
            'column_name': 'moves'
          , 'column_value': line
          }
        END                                          AS column_name_value
FROM    read_csv(
          'C:\Users\Roland_Bouman\Downloads\DutchClassical\DutchClassical.pgn'
        , columns = {'line': 'VARCHAR'}
        )
WHERE   line IS NOT NULL
)
ON column_name_value['column_name']
USING STRING_AGG( column_name_value['column_value'], ' ' )
GROUP BY game_id

And its result:

┌─────────┬──────────────────────┬──────────┬────────────┬───┬──────────────────────┬──────────┬──────────────────────┐
│ game_id │        Black         │ BlackElo │    Date    │ . │        White         │ WhiteElo │        moves         │
│  int64  │       varchar        │ varchar  │  varchar   │   │       varchar        │ varchar  │       varchar        │
├─────────┼──────────────────────┼──────────┼────────────┼───┼──────────────────────┼──────────┼──────────────────────┤
│       1 │ Pollock, William H.  │          │ 1895.??.?? │ . │ Tinsley, Samuel      │          │ 1.d4 f5 2.c4 e6 3..  │
│       2 │ Lasker, Edward       │          │ 1913.??.?? │ . │ Loman, Rudolf        │          │ 1.d4 e6 2.c4 f5 3..  │
│       3 │ Tartakower, Saviely  │          │ 1921.??.?? │ . │ Alekhine, Alexander  │          │ 1.d4 f5 2.c4 e6 3..  │
│       4 │ Wegemund, Otto       │          │ 1922.??.?? │ . │ Antze, O.            │          │ 1.c4 f5 2.d4 Nf6 3.  │
│       5 │ Tarrasch, Siegbert   │          │ 1922.??.?? │ . │ Johner, Paul F       │          │ 1.d4 e6 2.c4 f5 3..  │
│       6 │ Alekhine, Alexander  │          │ 1922.??.?? │ . │ Bogoljubow, Efim     │          │ 1.d4 f5 2.c4 Nf6 3.  │
│       7 │ Kmoch, Hans          │          │ 1922.??.?? │ . │ Rubinstein, Akiba    │          │ 1.d4 e6 2.c4 f5 3..  │
│       8 │ Mieses, Jacques      │          │ 1923.??.?? │ . │ Norman, George Mar.  │          │ 1.d4 f5 2.g3 Nf6 3.  │
│       9 │ Orlando, Placido     │          │ 1923.??.?? │ . │ Szabados, Eugenio    │          │ 1.d4 e6 2.c4 f5 3..  │
│      10 │ Tarrasch, Siegbert   │          │ 1923.??.?? │ . │ Seitz, Jakob Adolf   │          │ 1.c4 e6 2.d4 f5 3..  │
│      11 │ Wolf, Siegfried Re.  │          │ 1923.??.?? │ . │ Von Patay, J.        │          │ 1.d4 e6 2.c4 f5 3..  │
│      12 │ Tartakower, Saviely  │          │ 1924.??.?? │ . │ Bogoljubow, Efim     │          │ 1.d4 f5 2.g3 e6 3..  │
│      13 │ Pokorny, Amos        │          │ 1926.??.?? │ . │ Kostic, Boris        │          │ 1.c4 f5 2.d4 Nf6 3.  │
│      14 │ Tartakower, Saviely  │          │ 1927.??.?? │ . │ Vukovic, Vladimir    │          │ 1.d4 f5 2.c4 e6 3..  │
│      15 │ Botvinnik, Mikhail   │          │ 1927.??.?? │ . │ Rabinovich, Ilya L.  │          │ 1.d4 e6 2.c4 f5 3..  │
│       · │      ·               │  ·       │     ·      │ · │    ·                 │  ·       │          ·           │
│       · │      ·               │  ·       │     ·      │ · │    ·                 │  ·       │          ·           │
│       · │      ·               │  ·       │     ·      │ · │    ·                 │  ·       │          ·           │
│    7229 │ Kovacevic,Bl         │ 2400     │ 2023.12.09 │ . │ Kozul,Z              │ 2532     │ 1.d4 e6 2.c4 f5 3..  │
│    7230 │ Iskos,A              │ 2153     │ 2023.12.10 │ . │ Zhezhovska,Monika    │ 1826     │ 1.d4 e6 2.c4 f5 3..  │
│    7231 │ Spichkin,A           │ 2035     │ 2023.12.12 │ . │ Rodriguez Santiago,J │ 2043     │ 1.d4 e6 2.c4 f5 3..  │
│    7232 │ Rogov,Matfey         │ 2213     │ 2023.12.12 │ . │ Clarke,Matthew       │ 2127     │ 1.d4 e6 2.c4 f5 3..  │
│    7233 │ Osmonbekov,T         │ 2137     │ 2023.12.12 │ . │ Sroczynski,M         │ 2266     │ 1.d4 e6 2.c4 f5 3..  │
│    7234 │ Novikova,Galina      │ 2073     │ 2023.12.12 │ . │ Marcziter,D          │ 2192     │ 1.d4 e6 2.c4 f5 3..  │
│    7235 │ Tomazini,A           │ 2336     │ 2023.12.14 │ . │ Pultinevicius,Paul.  │ 2584     │ 1.d4 e6 2.c4 f5 3..  │
│    7236 │ Spichkin,A           │ 2035     │ 2023.12.19 │ . │ Levine,D             │ 2040     │ 1.d4 e6 2.c4 f5 3..  │
│    7237 │ Kanyamarala,Tarun    │ 2305     │ 2023.12.19 │ . │ Nechitaylo,Nikita    │ 2203     │ 1.d4 e6 2.c4 f5 3..  │
│    7238 │ Ronka,E              │ 2291     │ 2023.12.19 │ . │ Gruzman,Ilya         │ 2151     │ 1.d4 f5 2.g3 e6 3..  │
│    7239 │ Kurbonboeva,Sarvinoz │ 2154     │ 2023.12.26 │ . │ Mammadzada,G         │ 2449     │ 1.d4 f5 2.c4 Nf6 3.  │
│    7240 │ Koneru,H             │ 2554     │ 2023.12.26 │ . │ Peycheva,Gergana     │ 2271     │ 1.d4 e6 2.c4 f5 3..  │
│    7241 │ Carlsson,Andreas     │ 1902     │ 2023.12.28 │ . │ Kreken,Eivind Grunt  │ 2271     │ 1.d4 e6 2.c4 f5 3..  │
│    7242 │ Kazarjan,Gachatur    │ 2078     │ 2023.12.30 │ . │ Schuricht,Emil Fre.  │ 2095     │ 1.d4 f5 2.g3 e6 3..  │
│    7243 │ Kurbonboeva,Sarvinoz │ 2154     │ 2023.12.30 │ . │ Zhu Chen             │ 2423     │ 1.d4 f5 2.g3 Nf6 3.  │
├─────────┴──────────────────────┴──────────┴────────────┴───┴──────────────────────┴──────────┴──────────────────────┤
│ 7243 rows (30 shown)                                                                           12 columns (7 shown) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Next steps

From this point on there's many things that could be done to improve the solution, for example:

• Column values that originate from headers tags should un-escape escaped characters.
• The PGN-syntax itself does not dictate this but there are established conventions for what tag names to use and what kind of values are appropriate. For example, see the seven tag roster and optional tag pairs in the wikipedia entry for Portable Game Notation. It would make sense to further cleanse and conform the corresponding columns and give them a more suitable data type, or to actively validate their value.
• Further data normalization could be attempted by creating separate tables for player, event, opening etc.
• The moves could be further processed and analyzed to derive a table of board positions, something which would greatly increase the opportunities to analyze games.

We could also improve the statement and make it more robust:

• Better detection of the first game line. Our assumption has been that the first movetext always starts with '1.'. But what if a game does not have any moves at all? This may sound like that shouldn't be possible, but especially on an online chess site, a player's connection might break after a game was started, but before a move was made.
  
  Whatever the reason may be, and whether we're interested in such games or not, our current solution is not capable to detect these cases. Instead, games simply aren't identified as intended, and our final game would likely be a mixture of 2 or maybe even more games. Bad bad bad!
  
  (If you're interested in looking into such a scenario, the Lichess chess database for October 2013 contains 411,039 games, but 140 do not have any moves.)
• A more robust regular expression to deal with games that may not adhere fully to the PGN syntax (for example, more foregiving handling of whitespace)
• Better handling of errors when reading CSV.

For now we leave these considerations as an excercise for the reader.

The goal of these posts was to show how DuckDB's features and SQL-dialect allow these kind of raw-text processing tasks to be solved quickly and elegantly. I hope I have succeeded in demonstrating that - it sure was a lot of fun to try!