Gems of DuckDB 1.2

We published the DuckDB 1.2.1 bugfix release yesterday. As usual, please consult the release notes for the full list of changes and the installation page for instructions on installing or upgrading. In this post, we'll highlight a few features that were recently added to DuckDB and improvements that have been made in its ecosystem.

New Clients Page

DuckDB's client APIs (or “drivers”) are distributed through several centralized repositories, such as CRAN for R and Maven for Java. To help users keep track of the rollout of a new DuckDB release, we reworked our “Clients” page to show the latest version for each client. The page also clarifies the support tiers that apply to clients.

Simpler Installation

In line with DuckDB's “low friction” principle, we made sure that you can install the DuckDB command line client more easily.

Installation Script on Linux and macOS

DuckDB can now be installed on UNIX-like systems with an installation script:

curl https://install.duckdb.org | sh

The script determines your operating system and architecture, fetches the tag of latest release, and if not present downloads the latest available DuckDB binary to ~/.duckdb/cli (the ~/.duckdb folder is already used to store extensions).

Running the script does not require root (sudo) privileges, and it only uses the curl and zcat tools, which are widely available. You can inspect the shell script by visiting install.duckdb.org in your browser.

Signed Binaries on Windows

Starting with v1.2.1, we ship digitally signed binaries for the DuckDB Windows command line client. This means that you can run DuckDB in environments where signed binaries are a requirement.

Unsung DuckDB 1.2 Features

Further to the “What's New in 1.2.0” section of the announcement blog post, we collected five new features that were introduced in v1.2.0.

OR / IN Filter Pushdown

Starting with version 1.2.0, DuckDB supports OR and IN expressions for filter pushdown. This optimization comes especially handy when querying remote Parquet files or DuckDB databases.

-f Command Line Flag

The DuckDB CLI client now supports the -f flag to execute SQL script files:

duckdb -f script.sql

This is equivalent to:

duckdb -c ".read script.sql"

This feature is documented in the DuckDB tldr page. If you have tldr installed, you can get this page in the CLI via tldr duckdb.

allowed_directories / allowed_paths Options

We continue to improve support for operating DuckDB in secure environments. The allowed_directories and allowed_paths options allow restricting DuckDB's access to certain directories or files (resp.). These options allows fine-grained access control for the file system. For example, you can set DuckDB to only use the /tmp directory.

SET allowed_directories = ['/tmp'];  
SET enable_external_access = false;  
FROM read_csv('test.csv');  

With the setting applied, DuckDB will refuse to read files in the current working directory:

Permission Error:
Cannot access file "test.csv" - file system operations are disabled by configuration  

sum(BOOLEAN)

You can now directly compute the sum of BOOLEAN expressions without wrapping them into a CASE expression:

SELECT sum(l_extendedprice > 500) FROM lineitem;  

This is equivalent to:

SELECT sum(CASE WHEN l_extendedprice > 500 THEN 1 END) FROM lineitem;  

Excel Extension

Prior to DuckDB 1.2, Excel files were only supported by the spatial extension, which is a heavyweight extension with several dependencies. Starting with 1.2, the excel extension – which was previously limited to computing a few formulas – can read and write Excel sheets. For example:

FROM read_xlsx('test.xlsx', header = true);  

If you would like to work with Google Sheets sheets, take a look at the gsheets community extension.

On the Interweb

There are several repositories and pieces of information on DuckDB on the internet. We highlight two important ones:

Awesome DuckDB

The ecosystem around DuckDB keeps growing: many projects are built both with DuckDB and within DuckDB. The community-driven awesome-duckdb repository, maintained by David Gasquez, lists these and has recently surpassed 200 entries.

DuckDB File Signature

DuckDB's file signature, DUCK (hex: 44 55 43 4B), is now listed on Wikipedia.

Parquet Information Sheet

We added DuckDB to the “Implementation status” page of the Parquet documentation. In the process, we also improved DuckDB's Parquet support, e.g., by adding the FLOAT16 logical type.