https://duckdb.org/2024/06/20/cli-data-processing-using-duckdb-as-a-unix-tool.html

DuckDB Logo for Download
Documentation

  * Installation
  * How-to guides
  * Data import
  * Client APIs
  * SQL
  * Why DuckDB
  * Media
  * FAQ

Blog
GitHub 19.9k
Support
Support

Command Line Data Processing: Using DuckDB as a Unix Tool

Author Avatar
Gabor Szarnyas2024-06-20

TL;DR: DuckDB's CLI client is portable to many platforms and
architectures. It handles CSV files conveniently and offers users the
same rich SQL syntax everywhere. These characteristics make DuckDB an
ideal tool to complement traditional Unix tools for data processing
in the command line.

In this blog post, we dive into the terminal to compare DuckDB with
traditional tools used in Unix shells (Bash, Zsh, etc.). We solve
several problems requiring operations such as projection and
filtering to demonstrate the differences between using SQL queries in
DuckDB versus specialized command line tools. In the process, we will
show off some cool features such as DuckDB's powerful CSV reader and
the positional join operator. Let's get started!

Table of Contents

  * Table of Contents
  * The Unix Philosophy
  * Portability and Usability
  * Data Processing with Unix Tools and DuckDB
      + Datasets
      + Projecting Columns
      + Sorting Files
      + Intersecting Columns
      + Pasting Rows Together
      + Filtering
      + Joining Files
      + Replacing Strings
      + Reading JSON
  * Performance
  * Summary

The Unix Philosophy

To set the stage, let's recall the Unix philosophy. This states that
programs should:

  * do one thing and do it well,
  * work together, and
  * handle text streams.

Unix-like systems such as macOS, Linux and WSL in Windows have
embraced this philosophy. Tools such as grep, sed, and sort are
ubiquitious and widely used in shell scripts.

As a purpose-built data processing tool, DuckDB fits the Unix
philosophy quite well. First, it was designed to be a fast in-process
analytical SQL database system (do one thing and do it well). Second,
it has a standalone command line client, which can consume and
produce CSV files (work together), and also supports reading and
writing text streams (handle text streams). Thanks to these, DuckDB
works well in the ecosystem of Unix CLI tools, as shown in several
posts.

Portability and Usability

While Unix CLI tools are fast, robust, and available on all major
platforms, they often have cumbersome syntax that's difficult to
remember. To make matters worse, these tools often come with slight
differences between systems - think of the differences between GNU
sed and macOS's sed or the differences between regex syntax among
programs, which is aptly captured by Donald Knuth's quip "I define
Unix as 30 definitions of regular expressions living under one roof."

While there are shells specialized specifically for dataframe
processing, such as the Nushell project, older Unix shells (e.g., the
Bourne shell sh and Bash) are still the most wide-spread, especially
on servers.

At the same time, we have DuckDB, an extremely portable database
system which uses the same SQL syntax on all platforms. With version
1.0.0 released recently, DuckDB's syntax - based on the proven and
widely used PostgeSQL dialect - is now in a stable state. Another
attractive feature of DuckDB is that it offers an interactive shell,
which aids quick debugging. Moreover, DuckDB is available in several
host languages as well as in the browser via WebAssembly, so if you
ever decide to use your SQL scripts outside of the shell, DuckDB SQL
scripts can be ported to a wide variety of environments without any
changes.

---------------------------------------------------------------------

Data Processing with Unix Tools and DuckDB

In the following, we give examples for implementing simple data
processing tasks using the CLI tools provided in most Unix shells and
using DuckDB SQL queries. We use DuckDB v1.0.0 and run it in
in-memory mode. This mode makes sense for the problems we are
tackling, as we do not create any tables and the operations are not
memory-intensive, so there is no data to persist or to spill on disk.

---------------------------------------------------------------------

Datasets

We use the four input files capturing information on cities and
airports in the Netherlands.

pop.csv, the population of each of the top-10 most populous cities.

city,province,population
Amsterdam,North Holland,905234
Rotterdam,South Holland,656050
The Hague,South Holland,552995
Utrecht,Utrecht,361924
Eindhoven,North Brabant,238478
Groningen,Groningen,234649
Tilburg,North Brabant,224702
Almere,Flevoland,218096
Breda,North Brabant,184716
Nijmegen,Gelderland,179073

area.csv, the area of each of the top-10 most populous cities.

city,area
Amsterdam,219.32
Rotterdam,324.14
The Hague,98.13
Utrecht,99.21
Eindhoven,88.92
Groningen,197.96
Tilburg,118.13
Almere,248.77
Breda,128.68
Nijmegen,57.63

cities-airports.csv, the IATA codes of civilian airports serving
given cities.

city,IATA
Amsterdam,AMS
Haarlemmermeer,AMS
Eindhoven,EIN
Groningen,GRQ
Eelde,GRQ
Maastricht,MST
Beek,MST
Rotterdam,RTM
The Hague,RTM

airport-names.csv, the airport names belonging to given IATA codes.

IATA,airport name
AMS,Amsterdam Airport Schiphol
EIN,Eindhoven Airport
GRQ,Groningen Airport Eelde
MST,Maastricht Aachen Airport
RTM,Rotterdam The Hague Airport

You can download all input files as a single zip file.

---------------------------------------------------------------------

Projecting Columns

Projecting columns is a very common data processing step. Let's take
the pop.csv file and project the first and last columns, city and
population.

Unix Shell: cut

In the Unix shell, we use the cut command and specify the file's
delimiter (-d) and the columns to be projected (-f).

cut -d , -f 1,3 pop.csv

This produces the following output:

city,population
Amsterdam,905234
Rotterdam,656050
The Hague,552995
Utrecht,361924
Eindhoven,238478
Groningen,234649
Tilburg,224702
Almere,218096
Breda,184716
Nijmegen,179073

DuckDB: SELECT

In DuckDB, we can use the CSV reader to load the data, then use the
SELECT clause with column indexes (#i) to designate the columns to be
projected:

SELECT #1, #3 FROM 'pop.csv';

Note that we did not have to define any schema or load the data to a
table. Instead, we simply used 'pop.csv' in the FROM clause as we
would do with a regular table. DuckDB detects that this is a CSV file
and invokes the read_csv function, which automatically infers the CSV
file's dialect (delimiter, presence of quotes, etc.) as well as the
schema of the table. This allows us to simply project columns using
SELECT #1, #3. We could also use the more readable syntax SELECT
city, population.

To make the output of the solutions using Unix tools and DuckDB
equivalent, we wrap the query into a COPY ... TO statement:

COPY (
    SELECT #1, #3 FROM 'pop.csv'
  ) TO '/dev/stdout/';

This query produces the same result as the Unix command's output
shown above.

To turn this into a standalone CLI command, we can invoke the DuckDB
command line client with the -c <query>  argument, which runs the SQL
query and exits once it's finished. Using this technique, the query
above can be turned into the following one-liner:

duckdb -c "COPY (SELECT #1, #3 FROM 'pop.csv') TO '/dev/stdout/'"

In the following, we'll omit the code blocks using the standalone
duckdb command: all solutions can be executed in the duckdb -c
<query>  template and yield the same result as the solutions using
Unix tools.

---------------------------------------------------------------------

Sorting Files

Another common task is to sort files based on given columns. Let's
rank the cities within provinces based on their populations. To do
so, we need to sort the pop.csv file first based on the name of the
province using an ascending order, then on the population using a
descending order. We then return the province column first, followed
by the city and the population columns.

Unix Shell: sort

In the Unix shell, we rely on the sort tool. We specify the CSV
file's separator with the -t argument and set the keys to sort on
using -k arguments. We first sort on the second column (province)
with -k 2,2. Then, we sort on the third column (population), setting
the ordering to be reversed (r) and numeric (n) with -k 3rn. Note
that we need to handle the header of the file separately: we take the
first row with head -n 1 and the rest of the rows with tail -n +2,
sort the latter, and glue them back together with the header.
Finally, we perform a projection to reorder the columns.
Unfortunately, the cut command cannot reorder the columns, so we use
awk instead:

(head -n 1 pop.csv; tail -n +2 pop.csv \
    | sort -t , -k 2,2 -k 3rn) \
    | awk -F , '{ print $2 "," $1 "," $3}'

The result is the following:

province,city,population
Flevoland,Almere,218096
Gelderland,Nijmegen,179073
Groningen,Groningen,234649
North Brabant,Eindhoven,238478
North Brabant,Tilburg,224702
North Brabant,Breda,184716
North Holland,Amsterdam,905234
South Holland,Rotterdam,656050
South Holland,The Hague,552995
Utrecht,Utrecht,361924

DuckDB: ORDER BY

In DuckDB, we simply load the CSV and specify the column ordering via
SELECT province, city, population, then set the sorting criteria on
the selected columns (province ASC and population DESC). The CSV
reader automatically detects types, so the sorting is numeric by
default. Finally, we surround the query with a COPY statement to
print the results to the standard output.

COPY (
    SELECT province, city, population
    FROM 'pop.csv'
    ORDER BY province ASC, population DESC
  ) TO '/dev/stdout/';

---------------------------------------------------------------------

Intersecting Columns

A common task is to calculate the intersection of two columns, i.e.,
to find entities that are present in both. Let's find the cities that
are both in the top-10 most populous cities and have their own
airports.

Unix Shell: comm

The Unix solution for intersection uses the comm tool, intended to
compare two sorted files line-by-line. We first cut the relevant
colum from both files. Due to the sorting requirement, we apply sort
on both inputs before performing the intersection. The intersection
is performed using comm -12 where the argument -12 means that we only
want to keep lines that are in both files. We again rely on head and
tail to treat the headers and the rest of the files separately during
processing and glue them together at the end.

head -n 1 pop.csv | cut -d , -f 1; \
  comm -12 \
    <(tail -n +2 pop.csv | cut -d , -f 1 | sort) \
    <(tail -n +2 cities-airports.csv | cut -d , -f 1 | sort)

The script produces the following output:

city
Amsterdam
Eindhoven
Groningen
Rotterdam
The Hague

DuckDB: INTERSECT ALL

The DuckDB solution reads the CSV files, projects the city fields and
applies the INTERSECT ALL clause to calculate the intersection:

COPY (
    SELECT city FROM 'pop.csv'
    INTERSECT ALL
    SELECT city FROM 'cities-airports.csv'
  ) TO '/dev/stdout/';

---------------------------------------------------------------------

Pasting Rows Together

Pasting rows together line-by-line is a recurring task. In our
example, we know that the pop.csv and the area.csv files have an
equal number of rows, so we can produce a single file that contains
both the population and the area of every city in the dataset.

Unix Shell: paste

In the Unix shell, we use the paste command and remove the duplicate
city field using cut:

paste -d , pop.csv area.csv | cut -d , -f 1,2,3,5

The output is the following:

city,province,population,area
Amsterdam,North Holland,905234,219.32
Rotterdam,South Holland,656050,324.14
The Hague,South Holland,552995,98.13
Utrecht,Utrecht,361924,99.21
Eindhoven,North Brabant,238478,88.92
Groningen,Groningen,234649,197.96
Tilburg,North Brabant,224702,118.13
Almere,Flevoland,218096,248.77
Breda,North Brabant,184716,128.68
Nijmegen,Gelderland,179073,57.63

DuckDB: POSITIONAL JOIN

In DuckDB, we can use a POSITIONAL JOIN. This join type is one of
DuckDB's SQL extensions and it provides a concise syntax to combine
tables row-by-row based on each row's position in the table. Joining
the two tables together using POSITIONAL JOIN results in two city
columns - we use the EXCLUDE clause to remove the duplicate column:

COPY (
    SELECT pop.*, area.* EXCLUDE city
    FROM 'pop.csv'
    POSITIONAL JOIN 'area.csv'
  ) TO '/dev/stdout/';

---------------------------------------------------------------------

Filtering

Filtering is another very common operation. For this, we'll use
cities-airports.csv file. For each airport, this file contains its
IATA code and the main cities that it serves:

city,IATA
Amsterdam,AMS
Haarlemmermeer,AMS
Eindhoven,EIN
...

Let's try to formulate two queries:

 1. Find all cities whose name ends in dam.

 2. Find all airports whose IATA code is equivalent to the first
    three letters of a served city's name, but the city's name does
    not end in dam.

Unix Shell: grep

To answer the first question in the Unix shell, we use grep and the
regular expression ^[^,]*dam,:

grep "^[^,]*dam," cities-airports.csv

In this expression, ^ denotes the start of the line, [^,]* searches
for a string that does not contain the comma character (the
separator). The expression dam, ensures that the end of the string in
the first field is dam. The output is:

Amsterdam,AMS
Rotterdam,RTM

Let's try to answer the second question. For this, we need to match
the first three characters in the city field to the IATA field but we
need to do so in a case-insensitive manner. We also need to use a
negative condition to exclude the lines where the city's name ends in
dam. Both of these requirements are difficult to achieve with a
single grep or egrep command as they lack support for two features.
First, they do not support case-insensitive matching using a
backreference (grep -i alone is not sufficient to ensure this).
Second, they do not support negative lookbehinds. Therefore, we use
pcregrep, and formulate our question as follows:

pcregrep -i '^([a-z]{3}).*?(?<!dam),\1$' cities-airports.csv

Here, we call pcregrep with the case-insensitive flag (-i), which in
pcregrep also affects backreferences such as \1. We capture the first
three letters with ([a-z]{3}) (e.g., Ams) and match it to the second
field with the backreference: ,\1$. We use a non-greedy .*? to seek
to the end of the first field, then apply a negative lookbehind with
the (?<!dam) expression to ensure that the field does not end in dam.
The result is a single line:

Eindhoven,EIN

DuckDB: WHERE ... LIKE

Let's answer the questions now in DuckDB. To answer the first
question, we can use LIKE for pattern matching. The header should not
be part of the output, so we disable it with HEADER false. The
complete query looks like follows:

COPY (
    FROM 'cities-airports.csv'
    WHERE city LIKE '%dam'
  ) TO '/dev/stdout/' (HEADER false);

For the second question, we use string slicing to extract the first
three characters, upper to ensure case-insensitivity, and NOT LIKE
for the negative condition:

COPY (
    FROM 'cities-airports.csv'
    WHERE upper(city[1:3]) = IATA
      AND city NOT LIKE '%dam'
  ) TO '/dev/stdout/' (HEADER false);

These queries return exactly the same results as the solutions using
grep and pcregrep.

In both of these queries, we used the FROM-first syntax. If the
SELECT clause is omitted, the query is executed as if SELECT * was
used, i.e., it returns all columns.

---------------------------------------------------------------------

Joining Files

Joining tables is an essential task in data processing. Our next
example is going to use a join to return city name-airport name
combinations. This is achieved by joining the cities-airports.csv and
the airport-names.csv files on their IATA code fields.

Unix Shell: join

Unix tools support joining files via the join command, which joins
lines of two sorted inputs on a common field. To make this work, we
sort the files based on their IATA fields, then perform the join on
the first file's 2nd column (-1 2) and the second file's 1st column
(-2 1). We have to omit the header for the join command to work, so
we do just that and construct a new header with an echo command:

echo "IATA,city,airport name"; \
  join -t , -1 2 -2 1 \
    <(tail -n +2 cities-airports.csv | sort -t , -k 2,2) \
    <(tail -n +2 airport-names.csv   | sort -t , -k 1,1)

The result is the following:

IATA,city,airport name
AMS,Amsterdam,Amsterdam Airport Schiphol
AMS,Haarlemmermeer,Amsterdam Airport Schiphol
EIN,Eindhoven,Eindhoven Airport
GRQ,Eelde,Groningen Airport Eelde
GRQ,Groningen,Groningen Airport Eelde
MST,Beek,Maastricht Aachen Airport
MST,Maastricht,Maastricht Aachen Airport
RTM,Rotterdam,Rotterdam The Hague Airport
RTM,The Hague,Rotterdam The Hague Airport

DuckDB

In DuckDB, we load the CSV files and connect them using the NATURAL
JOIN clause, which joins on column(s) with the same name. To ensure
that the result matches with that of the Unix solution, we use the
ORDER BY ALL clause, which sorts the result on all columns, starting
from the first one, and stepping through them for tie-breaking to the
last column.

COPY (
    SELECT "IATA", "city", "airport name"
    FROM 'cities-airports.csv'
    NATURAL JOIN 'airport-names.csv'
    ORDER BY ALL
  ) TO '/dev/stdout/';

---------------------------------------------------------------------

Replacing Strings

You may have noticed that we are using very clean datasets. This is
of course very unrealistic, so in an evil twist, let's reduce the
data quality a bit:

  * Replace the space in the province's name with an underscore,
    e.g., turning North Holland to North_Holland.
  * Add thousand separating commas, e.g., turning 905234 to 905,234.
  * Change the CSV's separator to the semicolon character (;).

And while we're at it, also fetch the data set via HTTPS this time,
using the URL https://duckdb.org/data/cli/pop.csv.

Unix Shell: curl and sed

In Unix, remote data sets are typically fetched via curl. The output
of curl is piped into the subsequent processing steps, in this case,
a bunch of sed commands.

curl -s https://duckdb.org/data/cli/pop.csv \
    | sed 's/\([^,]*,.*\) \(.*,[^,]*\)/\1_\2/g' \
    | sed 's/,/;/g' \
    | sed 's/\([0-9][0-9][0-9]\)$/,\1/'

This results in the following output:

city;province;population
Amsterdam;North_Holland;905,234
Rotterdam;South_Holland;656,050
The Hague;South_Holland;552,995
Utrecht;Utrecht;361,924
Eindhoven;North_Brabant;238,478
Groningen;Groningen;234,649
Tilburg;North_Brabant;224,702
Almere;Flevoland;218,096
Breda;North_Brabant;184,716
Nijmegen;Gelderland;179,073

DuckDB: httpfs and regexp_replace

In DuckDB, we use the following query:

COPY (
    SELECT
        city,
        replace(province, ' ', '_') AS province,
        regexp_replace(population::VARCHAR, '([0-9][0-9][0-9])$', ',\1')
            AS population
    FROM 'https://duckdb.org/data/cli/pop.csv'
  ) TO '/dev/stdout/' (DELIMITER ';');

Note that the FROM clause now has an HTTPS URL instead of a simple
CSV file. The presence of the https:// prefix triggers DuckDB to load
the httpfs extension and use it to fetch the JSON document. We use
the replace function to substitute the spaces with underscores, and
the regexp_replace function for the replacement using a regular
expression. (We could have also used string formatting functions such
as format and printf). To change the separator to a semicolon, we
serialize the file using the COPY statement with the DELIMITER ';'
option.

---------------------------------------------------------------------

Reading JSON

As a final exercise, let's query the number of stars given to the
duckdb/duckdb repository on GitHub.

Unix Shell: curl and jq

In Unix tools, we can use curl to get the JSON file from https://
api.github.com and pipe its output to jq to query the JSON object.

curl -s https://api.github.com/repos/duckdb/duckdb \
    | jq ".stargazers_count"

DuckDB: read_json

In DuckDB, we use the read_json function, invoking it with the remote
HTTPS endpoint's URL. The schema of the JSON file is detected
automatically, so we can simply use SELECT to return the required
field.

SELECT stargazers_count
  FROM read_json('https://api.github.com/repos/duckdb/duckdb');

Output

Both of these commands return the current number of stars of the
repository.

---------------------------------------------------------------------

Performance

At this point, you might be wondering about the performance of the
DuckDB solutions. After all, all of our prior examples have only
consisted of a few lines, so benchmarking them against each other
will not result in any measurable performance differences. So, let's
switch to the Dutch railway services dataset that we used in a
previous blog post and formulate a different problem.

We'll use the 2023 railway services file (services-2023.csv.gz) and
count the number of Intercity services that operated in that year.

In Unix, we can use the gzcat command to decompress the csv.gz file
into a pipeline. Then, we can use grep or pcregrep (which is more
performant), and top it off with the wc command to count the number
of lines (-l). In DuckDB, the built-in CSV reader also supports
compressed CSV files, so we can use that without any extra
configuration.

gzcat services-2023.csv.gz | grep '^[^,]*,[^,]*,Intercity,' | wc -l
gzcat services-2023.csv.gz | pcregrep '^[^,]*,[^,]*,Intercity,' | wc -l
duckdb -c "SELECT count(*) FROM 'services-2023.csv.gz' WHERE \"Service:Type\" = 'Intercity';"

We also test the tools on uncompressed input:

gunzip -k services-2023.csv.gz
grep '^[^,]*,[^,]*,Intercity,' services-2023.csv | wc -l
pcregrep '^[^,]*,[^,]*,Intercity,' services-2023.csv | wc -l
duckdb -c "SELECT count(*) FROM 'services-2023.csv' WHERE \"Service:Type\" = 'Intercity';"

To reduce the noise in the measurements, we used the hyperfine
benchmarking tool and took the mean execution time of 10 runs. The
experiments were carried out on a MacBook Pro with a 12-core M2 Pro
CPU and 32 GB RAM, running macOS Sonoma 14.5. To reproduce them, run
the grep-vs-duckdb-microbenchmark.sh script. The following table
shows the runtimes of the solutions on both compressed and
uncompressed inputs:

       Tool        Runtime (compressed) Runtime (uncompressed)
grep 2.6.0-FreeBSD 20.9 s               20.5 s
pcregrep 8.45      3.1 s                2.9 s
DuckDB 1.0.0       4.2 s                1.2 s

The results show that on compressed input, grep was the slowest,
while DuckDB is slightly edged out by gzcat+pcregrep, which ran in
3.1 seconds compared to DuckDB's 4.2 seconds. On uncompressed input,
DuckDB can utilize all CPU cores from the get-go (instead of starting
with a single-threaded decompression step), allowing it to outperform
both grep and pcregrep by a significant margin: 2.5x faster than
pcregrep and more than 15x faster than grep.

While this example is quite simple, as queries get more complex,
there are more opportunities for optimization and larger intermediate
dataset may be produced. While both of these can be tackled within a
shell script (by manually implementing optimizations and writing the
intermediate datasets to disk), these will likely be less efficient
than what a DBMS can come up with. Shell scripts implementing complex
pipelines can also be very brittle and need to be rethought even for
small changes, making the performance advantage of using a database
even more significant for more complex problems.

---------------------------------------------------------------------

Summary

In this post, we used DuckDB as a standalone CLI application, and
explored its abilities to complement or substitute existing command
line tools (sort, grep, comm, join, etc.). While we obviously like
DuckDB a lot and prefer to use it in many cases, we also believe Unix
tools have their place: on most systems, they are already
pre-installed and a well-chosen toolchain of Unix commands can be
fast, efficient, and portable (thanks to POSIX-compliance).
Additionally, they can be very concise for certain problems. However,
to reap their benefits, you will need to learn the syntax and quirks
of each tool such as grep variants, awk as well as advanced ones such
as xargs and parallel. In the meantime, DuckDB's SQL is easy-to-learn
(you likely know quite a bit of it already) and DuckDB handles most
of the optimization for you.

If you have a favorite CLI use case for DuckDB, let us know on social
media or submit it to DuckDB snippets. Happy hacking!

Recent posts

 
2024-08-15

DuckCon #5 in Seattle

 
2024-06-20

Command Line Data Processing: Using DuckDB as a Unix Tool

 
2024-06-10

Native Delta Lake Support in DuckDB

All blog posts

Documentation

Installation
SQL Introduction
Why DuckDB

Internals

FAQs
Code of Conduct
Foundation
Trademark Use

Commercial Support

DuckDB Labs
Support Options

Community

X (Twitter)
LinkedIn
Discord
GitHub
Stack Overflow
DuckDB Logo
(c) 2024 DuckDB Foundation, Amsterdam NL