Shredding Deeply Nested JSON, One Vector at a Time

TL;DR: We’ve recently improved DuckDB’s JSON extension so JSON files can be directly queried as if they were tables.

DuckDB has a JSON extension that can be installed and loaded through SQL:

INSTALL 'json';
LOAD 'json';

The JSON extension supports various functions to create, read, and manipulate JSON strings. These functions are similar to the JSON functionality provided by other databases such as PostgreSQL and MySQL. DuckDB uses yyjson internally to parse JSON, a high-performance JSON library written in ANSI C. Many thanks to the yyjson authors and contributors!

Besides these functions, DuckDB is now able to read JSON directly! This is done by automatically detecting the types and column names, then converting the values within the JSON to DuckDB’s vectors. The automated schema detection dramatically simplifies working with JSON data and subsequent queries on DuckDB’s vectors are significantly faster!

Reading JSON Automatically with DuckDB

Since the 0.7.0 update, DuckDB has added JSON table functions. To demonstrate these, we will read todos.json, a fake TODO list containing 200 fake TODO items (only the first two items are shown):

[
  {
    "userId": 1,
    "id": 1,
    "title": "delectus aut autem",
    "completed": false
  },
  {
    "userId": 1,
    "id": 2,
    "title": "quis ut nam facilis et officia qui",
    "completed": false
  },
]

Each TODO item is an entry in the JSON array, but in DuckDB, we’d like a table where each entry is a row. This is now (since DuckDB’s 0.7.0 release in February 2023) as easy as:

SELECT * FROM 'todos.json';

(Note: Only 5 rows shown)

Now, finding out which user completed the most TODO items is as simple as:

SELECT userId, sum(completed::int) total_completed
FROM 'todos.json'
GROUP BY userId
ORDER BY total_completed DESC
LIMIT 1;

Under the hood, DuckDB recognizes the .json file extension in 'todos.json', and calls read_json_auto('todos.json') instead. This function is similar to our read_csv_auto function, which automatically infers column names and types for CSV files.

Like our other table functions, read_json_auto supports reading multiple files by passing a list, e.g., read_json_auto(['file1.json', 'file2.json']), but also globbing, e.g., read_json_auto('file*.json'). DuckDB will read multiple files in parallel.

Newline Delimited JSON

Not all JSON adheres to the format used in todos.json, which is an array of ‘records’. Newline-delimited JSON, or NDJSON, stores each row on a new line. DuckDB also supports reading (and writing!) this format. First, let’s write our TODO list as NDJSON:

COPY (SELECT * FROM 'todos.json') to 'todos2.json';

Again, DuckDB recognizes the .json suffix in the output file and automatically infers that we mean to use (FORMAT JSON). The created file looks like this (only the first two records are shown):

{"userId":1,"id":1,"title":"delectus aut autem","completed":false}
{"userId":1,"id":2,"title":"quis ut nam facilis et officia qui","completed":false}

DuckDB can read this file in precisely the same way as the original one:

SELECT * FROM 'todos2.json';

If your JSON file is newline-delimited, DuckDB can parallelize reading. This is specified with nd or the lines parameter:

SELECT * FROM read_ndjson_auto('todos2.json');
SELECT * FROM read_json_auto('todos2.json', lines='true');

You can also set lines='auto' to auto-detect whether the JSON file is newline-delimited.

Other JSON Formats

If using the read_json function directly, the format of the JSON can be specified using the json_format parameter. This parameter defaults to 'auto', which tells DuckDB to infer what kind of JSON we are dealing with. The first json_format is 'array_of_records', while the second is 'records'. This can be specified like so:

SELECT * FROM read_json('todos.json', auto_detect=true, json_format='array_of_records');
SELECT * FROM read_json('todos2.json', auto_detect=true, json_format='records');

Other supported formats are 'values' and 'array_of_values', which are similar to 'records' and 'array_of_records'. However, with these formats, each ‘record’ is not required to be a JSON object but can also be a JSON array, string, or anything supported in JSON.

Manual Schemas

What you may also have noticed is the auto_detect parameter. This parameter tells DuckDB to infer the schema, i.e., determine the names and types of the returned columns. These can manually be specified like so:

SELECT * FROM read_json('todos.json',
                        columns={userId: 'INT', id: 'INT', title: 'VARCHAR', completed: 'BOOLEAN'},
                        json_format='array_of_records');

You don’t have to specify all fields, just the ones you’re interested in:

SELECT * FROM read_json('todos.json',
                        columns={userId: 'INT', completed: 'BOOLEAN'},
                        json_format='array_of_records');

Now that we know how to use the new DuckDB JSON table functions let’s dive into some analytics!

GitHub Archive Examples

GH Archive is a project to record the public GitHub timeline, archive it, and make it easily accessible for further analysis. Every hour, a GZIP compressed, newline-delimited JSON file containing all public events on GitHub is uploaded. I’ve downloaded a whole day (2023-02-08) of activity using wget and stored the 24 files in a directory called gharchive_gz.

wget https://data.gharchive.org/2023-02-08-0.json.gz
wget https://data.gharchive.org/2023-02-08-1.json.gz
...
wget https://data.gharchive.org/2023-02-08-23.json.gz

Keep in mind that the data is compressed:

$ du -sh gharchive_gz
2.3G  gharchive_gz
$ gunzip -dc gharchive_gz/* | wc -c
 18396198934

One day of GitHub activity amounts to more than 18GB of JSON, which compresses to 2.3GB with GZIP.

To get a feel of what the data looks like, we run the following query:

SELECT json_group_structure(json)
FROM (
  SELECT *
  FROM read_ndjson_objects('gharchive_gz/*.json.gz')
  LIMIT 2048
);

Here, we use our read_ndjson_objects function, which reads the JSON objects in the file as raw JSON, i.e., as strings. The query reads the first 2048 records of JSON from the JSON files gharchive_gz directory and describes the structure. You can also directly query the JSON files from GH Archive using DuckDB’s httpfs extension, but we will be querying the files multiple times, so it is better to download them in this case.

I’ve formatted the result using an online JSON formatter & validator:

{
   "id":"VARCHAR",
   "type":"VARCHAR",
   "actor":{
      "id":"UBIGINT",
      "login":"VARCHAR",
      "display_login":"VARCHAR",
      "gravatar_id":"VARCHAR",
      "url":"VARCHAR",
      "avatar_url":"VARCHAR"
   },
   "repo":{
      "id":"UBIGINT",
      "name":"VARCHAR",
      "url":"VARCHAR"
   },
   "payload":{"..."},
   "public":"BOOLEAN",
   "created_at":"VARCHAR",
   "org":{
      "id":"UBIGINT",
      "login":"VARCHAR",
      "gravatar_id":"VARCHAR",
      "url":"VARCHAR",
      "avatar_url":"VARCHAR"
   }
}

I’ve left "payload" out because it consists of deeply nested JSON, and its formatted structure takes up more than 1000 lines!

So, how many records are we dealing with exactly? Let’s count it using DuckDB:

SELECT count(*) count FROM 'gharchive_gz/*.json.gz';

That’s around 4.4M daily events, which amounts to almost 200K events per hour. This query takes around 7.3s seconds on my laptop, a 2020 MacBook Pro with an M1 chip and 16GB of memory. This is the time it takes to decompress the GZIP compression and parse every JSON record.

To see how much time is spent decompressing GZIP in the query, I’ve also created a gharchive directory containing the same data but uncompressed. Running the same query on the uncompressed data takes around 5.4s, almost 2 seconds faster. So we got faster, but we also read more than 18GB of data from storage, as opposed to 2.3GB when it was compressed. So, this comparison really depends on the speed of your storage. I prefer to keep the data compressed.

As a side note, the speed of this query really shows how fast yyjson is!

So, what kind of events are in the GitHub data?

SELECT type, count(*) count
FROM 'gharchive_gz/*.json.gz'
GROUP BY type
ORDER BY count DESC;

This query takes around 7.4s, not much more than the count(*) query. So as we can see, data analysis is very fast once everything has been decompressed and parsed.

The most common event type is the PushEvent, taking up more than half of all events, unsurprisingly, which is people pushing their committed code to GitHub. The least common event type is the GollumEvent, taking up less than 1% of all events, which is a creation or update of a wiki page.

If we want to analyze the same data multiple times, decompressing and parsing every time is redundant. Instead, we can create a DuckDB table like so:

CREATE TABLE events AS
SELECT * EXCLUDE (payload)
FROM 'gharchive_gz/*.json.gz';

Which takes around 9s if you’re using an in-memory database. If you’re using an on-disk database, this takes around 13s and results in a database size of 444MB. When using an on-disk database, DuckDB ensures the table is persistent and performs all kinds of compression. Note that we have temporarily ignored the payload field using the convenient EXCLUDE clause.

To get a feel of what we read, we can ask DuckDB to describe the table:

DESCRIBE SELECT * FROM events;

This gives us the following:

As we can see, the "actor", "repo" and "org" fields, which are JSON objects, have been converted to DuckDB structs. The "id" column was a string in the original JSON but has been converted to a BIGINT by DuckDB’s automatic type detection. DuckDB can also detect a few different DATE/TIMESTAMP formats within JSON strings, as well as TIME and UUID.

Now that we’ve created the table, we can analyze it like any other DuckDB table! Let’s see how much activity there was in the duckdb/duckdb GitHub repository on this specific day:

SELECT type, count(*) count
FROM events
WHERE repo.name = 'duckdb/duckdb'
GROUP BY type
ORDER BY count DESC;

That’s a lot of pull request activity! Note that this doesn’t mean that 35 pull requests were opened on this day, activity within a pull request is also counted. If we search through the pull requests for that day, we see that there are only 15. This is more activity than normal because most of the DuckDB developers were busy fixing bugs for the 0.7.0 release.

Now, let’s see who was the most active:

SELECT actor.login, count(*) count
FROM events
WHERE repo.name = 'duckdb/duckdb'
  AND type = 'PullRequestEvent'
GROUP BY actor.login
ORDER BY count desc
LIMIT 5;

As expected, Mark (Mytherin, co-founder of DuckDB Labs) was the most active! My activity (lnkuiper, software engineer at DuckDB Labs) also shows up.

Handling inconsistent JSON schemas

So far, we have ignored the "payload" of the events. We’ve ignored it because the contents of this field are different based on the type of event. We can see how they differ with the following query:

SELECT json_group_structure(payload) structure
FROM (SELECT *
  FROM read_json(
    'gharchive_gz/*.json.gz',
    columns={
      id: 'BIGINT',
      type: 'VARCHAR',
      actor: 'STRUCT(id UBIGINT,
                     login VARCHAR,
                     display_login VARCHAR,
                     gravatar_id VARCHAR,
                     url VARCHAR,
                     avatar_url VARCHAR)',
      repo: 'STRUCT(id UBIGINT, name VARCHAR, url VARCHAR)',
      payload: 'JSON',
      public: 'BOOLEAN',
      created_at: 'TIMESTAMP',
      org: 'STRUCT(id UBIGINT, login VARCHAR, gravatar_id VARCHAR, url VARCHAR, avatar_url VARCHAR)'
    },
    lines='true'
  )
  WHERE type = 'WatchEvent'
  LIMIT 2048
);

The "payload" field is simple for events of type WatchEvent. However, if we change the type to PullRequestEvent, we get a JSON structure of more than 500 lines when formatted with a JSON formatter. We don’t want to look through all those fields, so we cannot use our automatic schema detection, which will try to get them all. Instead, we can manually supply the structure of the fields we’re interested in. DuckDB will skip reading the other fields. Another approach is to store the "payload" field as DuckDB’s JSON data type and parse it at query time (see the example later in this post!).

I’ve stripped down the JSON structure for the "payload" of events with the type PullRequestEvent to the things I’m actually interested in:

{
   "action":"VARCHAR",
   "number":"UBIGINT",
   "pull_request":{
      "url":"VARCHAR",
      "id":"UBIGINT",
      "title":"VARCHAR",
      "user":{
         "login":"VARCHAR",
         "id":"UBIGINT",
      },
      "body":"VARCHAR",
      "created_at":"TIMESTAMP",
      "updated_at":"TIMESTAMP",
      "assignee":{
         "login":"VARCHAR",
         "id":"UBIGINT",
      },
      "assignees":[
         {
            "login":"VARCHAR",
            "id":"UBIGINT",
         }
      ],
  }
}

This is technically not valid JSON because there are trailing commas. However, we try to allow trailing commas wherever possible in DuckDB, including JSON!

We can now plug this into the columns parameter of read_json, but we need to convert it to a DuckDB type first. I’m lazy, so I prefer to let DuckDB do this for me:

SELECT typeof(json_transform('{}', '{
   "action":"VARCHAR",
   "number":"UBIGINT",
   "pull_request":{
      "url":"VARCHAR",
      "id":"UBIGINT",
      "title":"VARCHAR",
      "user":{
         "login":"VARCHAR",
         "id":"UBIGINT",
      },
      "body":"VARCHAR",
      "created_at":"TIMESTAMP",
      "updated_at":"TIMESTAMP",
      "assignee":{
         "login":"VARCHAR",
         "id":"UBIGINT",
      },
      "assignees":[
         {
            "login":"VARCHAR",
            "id":"UBIGINT",
         }
      ],
  }
}'));

This gives us back a DuckDB type that we can plug the type into our function! Note that because we are not auto-detecting the schema, we have to supply timestampformat to be able to parse the timestamps correctly. The key "user" must be surrounded by quotes because it is a reserved keyword in SQL:

CREATE TABLE pr_events as
SELECT *
FROM read_json(
  'gharchive_gz/*.json.gz',
  columns={
    id: 'BIGINT',
    type: 'VARCHAR',
    actor: 'STRUCT(id UBIGINT,
                   login VARCHAR,
                   display_login VARCHAR,
                   gravatar_id VARCHAR,
                   url VARCHAR,
                   avatar_url VARCHAR)',
    repo: 'STRUCT(id UBIGINT, name VARCHAR, url VARCHAR)',
    payload: 'STRUCT(
                action VARCHAR,
                number UBIGINT,
                pull_request STRUCT(
                  url VARCHAR,
                  id UBIGINT,
                  title VARCHAR,
                  "user" STRUCT(
                    login VARCHAR,
                    id UBIGINT
                  ),
                  body VARCHAR,
                  created_at TIMESTAMP,
                  updated_at TIMESTAMP,
                  assignee STRUCT(login VARCHAR, id UBIGINT),
                  assignees STRUCT(login VARCHAR, id UBIGINT)[]
                )
              )',
    public: 'BOOLEAN',
    created_at: 'TIMESTAMP',
    org: 'STRUCT(id UBIGINT, login VARCHAR, gravatar_id VARCHAR, url VARCHAR, avatar_url VARCHAR)'
  },
  json_format='records',
  lines='true',
  timestampformat='%Y-%m-%dT%H:%M:%SZ'
)
WHERE type = 'PullRequestEvent';

This query completes in around 36s with an on-disk database (resulting size is 478MB) and 9s with an in-memory database. If you don’t care about preserving insertion order, you can speed the query up with this setting:

SET preserve_insertion_order=false;

With this setting, the query completes in around 27s with an on-disk database and 8.5s with an in-memory database. The difference between the on-disk and in-memory case is quite substantial here because DuckDB has to compress and persist much more data.

Now we can analyze pull request events! Let’s see what the maximum number of assignees is:

SELECT max(length(payload.pull_request.assignees)) max_assignees
FROM pr_events;

That’s a lot of people reviewing a single pull request!

We can check who was assigned the most:

WITH assignees AS (
  SELECT payload.pull_request.assignee.login assignee
  FROM pr_events
  UNION ALL
  SELECT unnest(payload.pull_request.assignees).login assignee
  FROM pr_events
)
SELECT assignee, count(*) count
FROM assignees
WHERE assignee NOT NULL
GROUP BY assignee
ORDER BY count DESC
LIMIT 5;

That’s a lot of assignments! Although I suspect there are duplicates in here.

Storing as JSON to parse at query time

Specifying the JSON schema of the "payload" field was helpful because it allowed us to directly analyze what is there, and subsequent queries are much faster. Still, it can also be quite cumbersome if the schema is complex. If you don’t want to specify the schema of a field, you can set the type as 'JSON':

CREATE TABLE pr_events AS
SELECT *
FROM read_json(
  'gharchive_gz/*.json.gz',
  columns={
    id: 'BIGINT',
    type: 'VARCHAR',
    actor: 'STRUCT(id UBIGINT,
                   login VARCHAR,
                   display_login VARCHAR,
                   gravatar_id VARCHAR,
                   url VARCHAR,
                   avatar_url VARCHAR)',
    repo: 'STRUCT(id UBIGINT, name VARCHAR, url VARCHAR)',
    payload: 'JSON',
    public: 'BOOLEAN',
    created_at: 'TIMESTAMP',
    org: 'STRUCT(id UBIGINT, login VARCHAR, gravatar_id VARCHAR, url VARCHAR, avatar_url VARCHAR)'
  },
  json_format='records',
  lines='true',
  timestampformat='%Y-%m-%dT%H:%M:%SZ'
)
WHERE type = 'PullRequestEvent';

This will load the "payload" field as a JSON string, and we can use DuckDB’s JSON functions to analyze it when querying. For example:

SELECT DISTINCT payload->>'action' AS action, count(*) count
FROM pr_events
GROUP BY action
ORDER BY count DESC;

The ->> arrow is short-hand for our json_extract_string function. Creating the entire "payload" field as a column with type JSON is not the most efficient way to get just the "action" field, but this example is just to show the flexibility of read_json. The query results in the following table:

As we can see, only a few pull requests have been reopened.

Conclusion

DuckDB tries to be an easy-to-use tool that can read all kinds of data formats. In the 0.7.0 release, we have added support for reading JSON. JSON comes in many formats and all kinds of schemas. Duckdb’s rich support for nested types (LIST, STRUCT) allows it to fully ‘shred’ the JSON to a columnar format for more efficient analysis.

We are excited to hear what you think about our new JSON functionality. If you have any questions or suggestions, please reach out to us on Discord or GitHub!

JupySQL Plotting with DuckDB

TLDR

JupySQL provides a seamless SQL experience in Jupyter and uses DuckDB to visualize larger than memory datasets in matplotlib.

Announcing DuckDB 0.7.0

The DuckDB team is happy to announce the latest DuckDB version (0.7.0) has been released. This release of DuckDB is named “Labradorius” after the Labrador Duck (Camptorhynchus labradorius) that was native to North America.

To install the new version, please visit the installation guide. The full release notes can be found here.

DuckCon 2023 - 2nd edition

The DuckDB team is excited to invite you all for our second DuckCon user group meeting. It will take place the day before FOSDEM in Brussels on Feb 3rd, 2023, at the Hilton Hotel.

In this edition, we will have the DuckDB creators Hannes Mühleisen, and Mark Raasveldt, talking about the current state of DuckDB and future plans. We will also have one talk regarding building out-of-tree extensions from DuckDB contributors Pedro Holanda, and Sam Ansmink and one invited talk from MotherDuck’s Founding Engineers, Boaz Leskes and Yves Le Maout. In addition, we will have a session with lightning talks from our users. We will close the event with a networking session with food and drinks where users are invited to interact directly with the DuckDB team.

Announcing DuckDB 0.6.0

The DuckDB team is happy to announce the latest DuckDB version (0.6.0) has been released. This release of DuckDB is named “Oxyura” after the White-headed duck (Oxyura leucocephala) which is an endangered species native to Eurasia.

To install the new version, please visit the installation guide. Note that the release is still being rolled out, so not all artifacts may be published yet. The full release notes can be found here.

Lightweight Compression in DuckDB

TLDR: DuckDB supports efficient lightweight compression that is automatically used to keep data size down without incurring high costs for compression and decompression.

When working with large amounts of data, compression is critical for reducing storage size and egress costs. Compression algorithms typically reduce data set size by 75-95%, depending on how compressible the data is. Compression not only reduces the storage footprint of a data set, but also often improves performance as less data has to be read from disk or over a network connection.

Modern Data Stack in a Box with DuckDB

TLDR: A fast, free, and open-source Modern Data Stack (MDS) can now be fully deployed on your laptop or to a single machine using the combination of DuckDB, Meltano, dbt, and Apache Superset.

This post is a collaboration with Jacob Matson and cross-posted on dataduel.co.

Summary

There is a large volume of literature (1, 2, 3) about scaling data pipelines. “Use Kafka! Build a lake house! Don’t build a lake house, use Snowflake! Don’t use Snowflake, use XYZ!” However, with advances in hardware and the rapid maturation of data software, there is a simpler approach. This article will light up the path to highly performant single node analytics with an MDS-in-a-box open source stack: Meltano, DuckDB, dbt, & Apache Superset on Windows using Windows Subsystem for Linux (WSL). There are many options within the MDS, so if you are using another stack to build an MDS-in-a-box, please share it with the community on the DuckDB Twitter, GitHub, or Discord, or the dbt slack! Or just stop by for a friendly debate about our choice of tools!

Querying Postgres Tables Directly From DuckDB

TLDR: DuckDB can now directly query queries stored in PostgreSQL and speed up complex analytical queries without duplicating data.

Persistent Storage of Adaptive Radix Trees in DuckDB

TLDR: DuckDB uses Adaptive Radix Tree (ART) Indexes to enforce constraints and to speed up query filters. Up to this point, indexes were not persisted, causing issues like loss of indexing information and high reload times for tables with data constraints. We now persist ART Indexes to disk, drastically diminishing database loading times (up to orders of magnitude), and we no longer lose track of existing indexes. This blog post contains a deep dive into the implementation of ART storage, benchmarks, and future work. Finally, to better understand how our indexes are used, I’m asking you to answer the following survey. It will guide us when defining our future roadmap.

Range Joins in DuckDB

TL;DR: DuckDB has fully parallelised range joins that can efficiently join millions of range predicates.

Range intersection joins are an important operation in areas such as temporal analytics, and occur when two inequality conditions are present in a join predicate. Database implementations often rely on slow O(N^2) algorithms that compare every pair of rows for these operations. Instead, DuckDB leverages its fast sorting logic to implement two highly optimized parallel join operators for these kinds of range predicates, resulting in 20-30x faster queries. With these operators, DuckDB can be used effectively in more time-series-oriented use cases.

Friendlier SQL with DuckDB

An elegant user experience is a key design goal of DuckDB. This goal guides much of DuckDB’s architecture: it is simple to install, seamless to integrate with other data structures like Pandas, Arrow, and R Dataframes, and requires no dependencies. Parallelization occurs automatically, and if a computation exceeds available memory, data is gracefully buffered out to disk. And of course, DuckDB’s processing speed makes it easier to get more work accomplished.

However, SQL is not famous for being user-friendly. DuckDB aims to change that! DuckDB includes both a Relational API for dataframe-style computation, and a highly Postgres-compatible version of SQL. If you prefer dataframe-style computation, we would love your feedback on our roadmap. If you are a SQL fan, read on to see how DuckDB is bringing together both innovation and pragmatism to make it easier to write SQL in DuckDB than anywhere else. Please reach out on GitHub or Discord and let us know what other features would simplify your SQL workflows. Join us as we teach an old dog new tricks!

Parallel Grouped Aggregation in DuckDB

TL;DR: DuckDB has a fully parallelized aggregate hash table that can efficiently aggregate over millions of groups.

Grouped aggregations are a core data analysis command. It is particularly important for large-scale data analysis (“OLAP”) because it is useful for computing statistical summaries of huge tables. DuckDB contains a highly optimized parallel aggregation capability for fast and scalable summarization.

Jump straight to the benchmarks?

DuckDB Time Zones: Supporting Calendar Extensions

TLDR: The DuckDB ICU extension now provides time zone support.

Time zone support is a common request for temporal analytics, but the rules are complex and somewhat arbitrary. The most well supported library for locale-specific operations is the International Components for Unicode (ICU). DuckDB already provided collated string comparisons using ICU via an extension (to avoid dependencies), and we have now connected the existing ICU calendar and time zone functions to the main code via the new TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ for short) data type. The ICU extension is pre-bundled in DuckDB’s Python and R clients and can be optionally installed in the remaining clients.

In this post, we will describe how time works in DuckDB and what time zone functionality has been added.

DuckDB quacks Arrow: A zero-copy data integration between Apache Arrow and DuckDB

TLDR: The zero-copy integration between DuckDB and Apache Arrow allows for rapid analysis of larger than memory datasets in Python and R using either SQL or relational APIs.

This post is a collaboration with and cross-posted on the Arrow blog.

DuckDB - The Lord of Enums:
The Fellowship of the Categorical and Factors.

String types are one of the most commonly used types. However, often string columns have a limited number of distinct values. For example, a country column will never have more than a few hundred unique entries. Storing a data type as a plain string causes a waste of storage and compromises query performance. A better solution is to dictionary encode these columns. In dictionary encoding, the data is split into two parts: the category and the values. The category stores the actual strings, and the values stores a reference to the strings. This encoding is depicted below.

Fast Moving Holistic Aggregates

TLDR: DuckDB, a free and Open-Source analytical data management system, has a windowing API that can compute complex moving aggregates like interquartile ranges and median absolute deviation much faster than the conventional approaches.

In a previous post, we described the DuckDB windowing architecture and mentioned the support for some advanced moving aggregates. In this post, we will compare the performance various possible moving implementations of these functions and explain how DuckDB’s performant implementations work.

DuckDB-Wasm: Efficient Analytical SQL in the Browser

TLDR: DuckDB-Wasm is an in-process analytical SQL database for the browser. It is powered by WebAssembly, speaks Arrow fluently, reads Parquet, CSV and JSON files backed by Filesystem APIs or HTTP requests and has been tested with Chrome, Firefox, Safari and Node.js. You can try it in your browser at shell.duckdb.org or on Observable.

Windowing in DuckDB

TLDR: DuckDB, a free and Open-Source analytical data management system, has a state-of-the-art windowing engine that can compute complex moving aggregates like inter-quartile ranges as well as simpler moving averages.

Window functions (those using the OVER clause) are important tools for analysing data series, but they can be slow if not implemented carefully. In this post, we will take a look at how DuckDB implements windowing. We will also see how DuckDB can leverage its aggregate function architecture to compute useful moving aggregates such as moving inter-quartile ranges (IQRs).

Fastest table sort in the West - Redesigning DuckDB’s sort

TLDR: DuckDB, a free and Open-Source analytical data management system, has a new highly efficient parallel sorting implementation that can sort much more data than fits in main memory.

Database systems use sorting for many purposes, the most obvious purpose being when a user adds an ORDER BY clause to their query. Sorting is also used within operators, such as window functions. DuckDB recently improved its sorting implementation, which is now able to sort data in parallel and sort more data than fits in memory. In this post, we will take a look at how DuckDB sorts, and how this compares to other data management systems.

Querying Parquet with Precision using DuckDB

TLDR: DuckDB, a free and open source analytical data management system, can run SQL queries directly on Parquet files and automatically take advantage of the advanced features of the Parquet format.

Apache Parquet is the most common “Big Data” storage format for analytics. In Parquet files, data is stored in a columnar-compressed binary format. Each Parquet file stores a single table. The table is partitioned into row groups, which each contain a subset of the rows of the table. Within a row group, the table data is stored in a columnar fashion.

Efficient SQL on Pandas with DuckDB

TLDR: DuckDB, a free and open source analytical data management system, can efficiently run SQL queries directly on Pandas DataFrames.

Recently, an article was published advocating for using SQL for Data Analysis. Here at team DuckDB, we are huge fans of SQL. It is a versatile and flexible language that allows the user to efficiently perform a wide variety of data transformations, without having to care about how the data is physically represented or how to do these data transformations in the most optimal way.

Testing out DuckDB's Full Text Search Extension

TLDR: DuckDB now has full-text search functionality, similar to the FTS5 extension in SQLite. The main difference is that our FTS extension is fully formulated in SQL. We tested it out on TREC disks 4 and 5.

Searching through textual data stored in a database can be cumbersome, as SQL does not provide a good way of formulating questions such as “Give me all the documents about Mallard Ducks”: string patterns with LIKE will only get you so far. Despite SQL’s shortcomings here, storing textual data in a database is commonplace. Consider the table products (id INT, name VARCHAR, description VARCHAR) - it would be useful to search through the name and description columns for a website that sells these products.