Getting Started with DuckDB

2.2.1 macOS

For macOS users, the official recommendation is to use the Homebrew package manager, which handles updates automatically.

Copy code
brew install duckdb

2.2.2 Linux and Windows

For Linux and Windows, the CLI is distributed as a zip file via GitHub Releases. The process involves:

1. Visiting the DuckDB GitHub releases page.
2. Downloading the package appropriate for your architecture (e.g., duckdb_cli-linux-amd64.zip).
3. Unzipping the file.
4. Running the binary directly.

Example for Linux (AMD64):

Copy code
wget https://github.com/duckdb/duckdb/releases/download/v1.4.3/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
./duckdb -version

2.3.1 SQL statements

The CLI efficiently handles standard SQL statements. To execute a command, you must end it with a semicolon (;) and a newline. The interface supports multi-line input, allowing you to paste complex queries comfortably.

2.3.2 Dot commands

In addition to SQL, the CLI supports "Dot Commands"—special internal instructions identified by a leading period (.). These do not require a semicolon.

• .help: Displays a list of available commands.
• .tables: Lists all available tables and views.
• .mode: Changes how results are displayed (e.g., duckbox, line, json).
• .timer on/off: Toggles the display of execution time for queries.
• .read: Reads and executes SQL from an external file.
• .open: Closes the current database and opens a new file.

Pro Tip: The .duckdbrc Config File You can configure the CLI to run specific commands every time it launches by creating a .duckdbrc file in your home directory. This is perfect for setting a custom prompt or enabling timers by default.

2.3.3 CLI arguments

The CLI is also a powerful tool for automation. You can pass flags to control behavior without entering interactive mode:

• -json: Outputs results as JSON strings.
• -readonly: Opens the database in read-only mode.
• -c or -s: Runs a specific command and exits immediately.

For example, to output a query directly to JSON:

Copy code
duckdb --json -c 'SELECT * FROM my_table'

Installing and Loading

The lifecycle of an extension involves two steps:

1. INSTALL extension_name; – Downloads the extension to your local environment.
2. LOAD extension_name; – Activates the extension for the current session.

Note: Since version 0.8, DuckDB often autoloads known extensions (like httpfs or parquet) if it detects they are needed by your query.

Direct Remote Querying

Using the httpfs extension, you can query a remote CSV file simply by providing its URL. If the URL ends in a known extension like .csv, DuckDB infers the format automatically. For URLs without extensions (like shortened links), use read_csv_auto:

Copy code
SELECT count(*) 
FROM read_csv_auto('https://bit.ly/3KoiZR0');

2.5.1 Result modes

When exploring datasets with many columns, the default table view (duckbox) might truncate data. This chapter recommends switching to Line Mode for initial exploration. In this mode, every column is printed on its own line.

Copy code
.mode line
SELECT * FROM read_csv_auto('...') LIMIT 1;

Other useful modes include json, csv, list, and noheader.

Building Data Pipelines

The CLI can function as a robust step in a data pipeline. You can read data from a remote source, apply SQL transformations, and write the result to a local file in a single command.

Writing to CSV:

Copy code
duckdb -csv -s "SELECT * FROM remote_file WHERE region='Europe'" > western_europe.csv

Writing to Parquet: DuckDB can also export directly to the compressed Parquet format using the COPY ... TO syntax:

Copy code
duckdb -s "COPY (SELECT * FROM remote_file) TO 'output.parquet' (FORMAT PARQUET)"