DuckDB快速入门
既然我们已经了解了 DuckDB 的基本概念以及它在 2020 年代初期崛起的原因，现在是时候熟悉它的操作方法了。本章将重点介绍 DuckDB 命令行界面 (CLI)。我们将学习如何在不同环境中安装它，然后了解内置的命令。最后，我们将通过它查询一个远程的 CSV 文件。
DuckDB 支持的环境
DuckDB 涵盖了多种编程语言和操作系统（Linux、Windows、macOS），同时支持 Intel/AMD 和 ARM 架构。截至撰写之时，它支持命令行、Python、R、Java、Javascript、Go、Rust、Node.js、Julia、C/C++、ODBC、JDBC、WASM 和 Swift 等环境。本章我们将重点介绍 DuckDB 命令行界面 (CLI)，因为我们认为这是让您快速上手的最简单方法。
DuckDB 命令行界面不需要单独安装服务器，因为 DuckDB 是一个嵌入式数据库，在命令行界面中它也是嵌入式的。
DuckDB 命令行界面 (CLI) 发布在 GitHub 的版本库中，针对不同的操作系统和架构提供各种软件包。您可以在安装页面找到完整列表：duckdb.org/docs/instal…
安装步骤
DuckDB CLI 采用“复制粘贴”的方式安装，无需额外的安装程序或库。它由单个名为 duckdb 的可执行文件组成。下面介绍如何在不同系统上安装 DuckDB CLI：
macOS
官方推荐使用 Homebrew 安装 DuckDB：
# 这仅在您没有安装 Homebrew 的情况下需要运行
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
brew install duckdb
Linux 和 Windows
Linux 和 Windows 有适用于不同架构和版本的软件包。您可以在 GitHub 的版本发布页面找到完整列表。
例如，清单 2.2 展示了如何在具有 AMD64 架构的 Linux 上执行零过程安装：
清单 2.2：在 Linux 上进行零过程安装
# 1. 请勿忘记将链接更新到 GitHub releases 页面上最新版本.
wget https://github.com/duckdb/duckdb/releases/download/v0.8.1/duckdb_cli-linux-amd64.zip # 1
unzip duckdb_cli-linux-amd64.zip
./duckdb -version
duckdb
13
Windows 的安装步骤请参考官方指南：duckdb.org/docs/instal…
使用 DuckDB 命令行界面 (CLI)
启动 DuckDB CLI 非常简单，只需运行以下命令即可：
duckdb
这将启动 DuckDB 和 CLI。您应该会看到类似以下的输出：
v0.8.1 6536a77232
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
此时，数据库将处于暂态模式，所有数据都存储在内存中。一旦您退出 CLI，数据将消失。您可以通过键入 .quit 或 .exit 来退出 CLI。
DuckDB 命令行界面的点命令
除了 SQL 语句和命令之外，CLI 还有一些特殊的点命令，这些命令仅在 CLI 中可用。要使用这些命令之一，请在行首加上一个句点 (.)，然后紧跟您想要执行的命令名称。命令的其他参数用空格隔开，放在命令后面。点命令必须单行输入，句点之前不能有空格。与普通 SQL 语句或命令不同，行尾不需要分号。
下面列出了一些最常用的点命令：
.open关闭当前数据库文件并打开一个新文件。.read允许从 CLI 内部读取要执行的 SQL 文件。.tables列出当前可用的表和视图。.timer on/off开启/关闭 SQL 执行时间的输出。.mode控制输出格式。.maxrows控制默认显示的行数 (用于 duckbox 格式)。.excel将下一个命令的输出显示为电子表格。.quit或ctrl-d退出 CLI。
可以通过 .help 获取完整概述。
参数
DuckDB CLI 接受一些参数，可用于调整数据库模式、控制输出格式或决定 CLI 是否进入交互模式。语法为：
duckdb [选项] 文件名 [SQL 语句]
下面列出了一些最常用的 CLI 参数：
-readonly以只读模式打开数据库。-json将输出模式设置为 json 格式。-line将输出模式设置为行格式。-unsigned允许加载无符号扩展。-s 命令或-c 命令指定要运行的命令，然后退出 CLI。 这在与.read点命令结合使用时特别有用，.read命令可以从指定的文件读取 SQL 语句。
要获取可用 CLI 参数的列表，请运行以下命令：
duckdb -h
或
duckdb --help
DuckDB 的扩展系统
DuckDB 拥有一个扩展系统，用于存放一些非核心数据库功能。您可以将扩展视为可以与 DuckDB 一起安装的软件包。
DuckDB 预装了几个扩展，具体取决于您使用的发行版。您可以通过调用 duckdb_extensions 函数获取所有可用扩展的列表，无论是否已安装。
DESCRIBE
SELECT *
FROM duckdb_extensions();
输出的截断视图显示在清单2.3中：
┌────────────────┬─────────────┐
│ column_name │ column_type │
│ varchar │ varchar │
├────────────────┼─────────────┤
│ extension_name │ VARCHAR │
│ loaded │ BOOLEAN │
│ installed │ BOOLEAN │
│ install_path │ VARCHAR │
│ description │ VARCHAR │
│ aliases │ VARCHAR[] │
└────────────────┴─────────────┘
让我们检查一下我们的机器上安装了哪些扩展：
SELECT extension_name, loaded, installed
from duckdb_extensions()
ORDER BY installed DESC, loaded DESC;
运行查询的结果显示在清单2.4中：
┌──────────────────┬─────────┬───────────┐
│ extension_name │ loaded │ installed │
│ varchar │ boolean │ boolean │
├──────────────────┼─────────┼───────────┤
│ autocomplete │ true │ true │
│ fts │ true │ true │
│ icu │ true │ true │
│ json │ true │ true │
│ parquet │ true │ true │
│ tpch │ true │ true │
│ httpfs │ false │ false │
│ inet │ false │ false │
│ jemalloc │ false │ false │
│ motherduck │ false │ false │
│ postgres_scanner │ false │ false │
│ spatial │ false │ false │
│ sqlite_scanner │ false │ false │
│ tpcds │ false │ false │
│ excel │ true │ │
├──────────────────┴─────────┴───────────┤
│ 15 rows 3 columns │
└────────────────────────────────────────┘
您可以通过输入INSTALL命令，后跟扩展名，安装任何扩展。然后，扩展将安装在您的数据库中，但不会加载。要加载扩展，请输入LOAD，后跟相同的名称。扩展机制是幂等的，这意味着您可以多次发出这两个命令而不会遇到错误。
注意：自DuckDB版本0.8以来，如果可以确定需要，数据库将自动加载安装的扩展，因此您可能不需要LOAD命令。
默认情况下，DuckDB无法查询位于互联网上其他位置的文件，但是通过官方的httpfs扩展可以实现此功能。如果该扩展尚未包含在您的发行版中，您可以安装并加载httpfs扩展。该扩展允许我们直接查询托管在HTTP(S)服务器上的文件，而无需将文件下载到本地，它还支持S3和其他一些云存储提供商。
INSTALL httpfs;
LOAD httpfs;
然后，我们可以通过输入以下命令来检查它被安装在哪里：
FROM duckdb_extensions()
SELECT loaded, installed, install_path
WHERE extension_name = 'httpfs';
您应该看到以下输出：
┌─────────┬───────────┬────────────────────────────────────────────┐
│ loaded │ installed │ install_path │
│ boolean │ boolean │ varchar │
├─────────┼───────────┼────────────────────────────────────────────┤
│ true │ true │ /path/to/httpfs.duckdb_extension │
└─────────┴───────────┴────────────────────────────────────────────┘
我们可以看到这个扩展现在已经被加载和安装了，并且还可以看到它被安装的位置。
使用DuckDB CLI分析CSV文件
我们将从演示CLI开始，这是任何数据工程师的常见任务 —— 理解CSV文件中的数据！我们的数据存储在何处并不重要，无论是在远程HTTP服务器上还是云存储（S3、GCP、HDFS），DuckDB现在都可以直接处理它，而无需手动下载和导入过程。由于许多支持的文件格式（如CSV和Parquet）的摄取默认是并行的，因此将数据导入DuckDB应该非常快速。
我们在GitHub上搜寻了CSV文件，并找到了一个包含各国人口数量的数据集。我们可以编写以下查询来计算记录的数量：
SELECT count(*)
FROM 'https://github.com/bnokoro/Data-Science/raw/master/
➥countries%20of%20the%20world.csv';
如果我们运行这个查询，我们应该会看到以下输出，表明我们已经获得了200多个国家的人口数据：
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 227 │
└──────────────┘
在这种情况下，如果我们的URL或文件名以特定扩展名结尾（例如.csv），DuckDB将自动处理它。但如果我们尝试自动处理同一个CSV文件的短链接呢？
SELECT count(*)
FROM 'https://bit.ly/3KoiZR0';
运行此查询会导致以下错误：
Error: Catalog Error: Table with name https://bit.ly/3KoiZR0 does not exist!
Did you mean "Player"?
LINE 1: select count(*) from 'https://bit.ly/3KoiZR0';
虽然它是一个CSV文件，但DuckDB并不知道，因为它没有.csv后缀。我们可以通过使用read_csv_auto函数来解决这个问题，该函数会处理提供的URI，就像它是一个CSV文件一样，尽管它缺少.csv后缀。更新后的查询如清单2.5所示：
SELECT count(*)
FROM read_csv_auto("https://bit.ly/3KoiZR0");
这个查询将返回与使用可以推断格式的规范链接的查询相同的结果。
结果模式
要显示结果，您可以使用.mode <name>选择不同的模式。您可以通过输入.help mode来查看可用模式的列表。
在本章中，我们一直使用'duckbox'模式，它返回灵活的表结构。DuckDB提供了一系列不同的模式，它们大致分为几个类别：
-
基于表的模式，适用于少量列 — duckbox、box、csv、ascii、table、list、column.
-
基于行的模式，适用于更多列 — json、jsonline、line
然后还有一些其他的模式不属于这些类别，包括html、insert和trash（无输出）。
我们的第一个查询统计了CSV文件中的记录数量，但了解它有哪些列会更有趣。如果我们使用默认模式，会有很多列被截断，因此在运行查询之前，我们将切换到行模式：
.mode line -- #1
SELECT *
FROM read_csv_auto("https://bit.ly/3KoiZR0")
LIMIT 1;
运行此查询的结果显示在清单2.6中。
Country = Afghanistan
Region = ASIA (EX. NEAR EAST)
Population = 31056997
Area (sq. mi.) = 647500
Pop. Density (per sq. mi.) = 48,0
Coastline (coast/area ratio) = 0,00
Net migration = 23,06
Infant mortality (per 1000 births) = 163,07
GDP ($ per capita) = 700
Literacy (%) = 36,0
Phones (per 1000) = 3,2
Arable (%) = 12,13
Crops (%) = 0,22
Other (%) = 87,65
Climate = 1
Birthrate = 46,6
Deathrate = 20,34
Agriculture = 0,38
Industry = 0,24
Service = 0,38
如您从输出中所见，与duckbox模式相比，行模式占用了更多的空间，但我们发现它是探索具有大量列的数据集的最佳模式。一旦您决定了要使用的列子集，您可以随时切换回其他模式。
这个数据集包含许多关于各个国家的有趣信息。让我们编写一个查询来计算国家的数量，并找出所有国家中平均面积最大的人口。这个查询只返回了少量列，所以在运行查询之前，我们将切换回duckbox模式：
.mode duckbox
SELECT count(*) AS countries,
max(Population) AS max_population,
round(avg(cast("Area (sq. mi.)" AS decimal))) AS avgArea
FROM read_csv_auto("https://bit.ly/3KoiZR0");
┌───────────┬────────────────┬──────────┐
│ countries │ max_population │ avgArea │
│ int64 │ int64 │ double │
├───────────┼────────────────┼──────────┤
│ 227 │ 1313973713 │ 598227.0 │
└───────────┴────────────────┴──────────┘
到目前为止，在这个过程中没有创建任何表，我们只是浅尝了DuckDB的实际功能。虽然上面的示例都是在交互模式下运行的，但DuckDB CLI也可以以非交互方式运行。它可以从标准输入读取，并将结果写入标准输出。这使得可以构建各种各样的管道。
让我们以一个脚本来结束，该脚本提取西欧国家的人口、出生率和死亡率，并创建一个新的本地CSV文件来存储这些数据。在运行下面的命令之前，我们可以要么从DuckDB CLI中退出，要么在运行命令之前打开另一个标签页：
duckdb -csv \
-s "SELECT Country, Population, Birthrate, Deathrate
FROM read_csv_auto('https://bit.ly/3KoiZR0')
WHERE trim(region) = 'WESTERN EUROPE'" \
> western_europe.csv
可以使用命令行工具或文本编辑器查看western_europe.csv的前几行。如果我们使用head工具，可以像这样找到前5行：
head -n5 western_europe.csv
输出将看起来像表2.1：
| Country | Population | Birthrate | Deathrate |
|---|---|---|---|
| Andorra | 71201 | 8.71 | 6.25 |
| Austria | 8192880 | 8.74 | 9.76 |
| Belgium | 10379067 | 10.38 | 10.27 |
| Denmark | 5450661 | 11.13 | 10.36 |
我们也可以创建Parquet文件，但我们不能直接将输出导入具有Parquet扩展名的文件中。相反，我们可以使用COPY ... TO子句，并将stdout作为目标：
duckdb \
-s "COPY (
SELECT Country, Population, Birthrate, Deathrate
FROM read_csv_auto('https://bit.ly/3KoiZR0')
WHERE trim(region) = 'WESTERN EUROPE'
) TO '/dev/stdout' (FORMAT PARQUET)" \
> western_europe.parquet
您可以使用任何Parquet阅读器查看Parquet文件的内容，甚至可能使用DuckDB本身！
duckdb -s "FROM 'western_europe.parquet' LIMIT 5"
结果将与表2.1中所见相同。
提示：可以将重复的配置和使用存储在位于$HOME/.duckdbrc的配置文件中。此文件在启动期间被读取，并且其中的所有命令 - 包括点命令和SQL命令 - 都通过一个.read命令执行。这使您可以存储CLI的配置状态以及您可能想要使用SQL命令初始化的任何内容。
一个可能放在duckdbrc文件中的示例是，在启动DuckDB时自定义提示符和欢迎消息：
-- Duck head prompt
.prompt '⚫◗ '
-- Example SQL statement
select 'Begin quacking!' as "Ready, Set, ...";
总结
- DuckDB数据库带有命令行界面（CLI），可以在Windows、Linux和OSX上作为CLI安装。
- DuckDB可作为Python、R、Java、Javascript、Julia、C/C++、ODBC、WASM和Swift的库使用。
- CLI支持额外的点命令，用于控制输出、读取文件、内置帮助等等。
- 通过 .mode，您可以使用多种显示模式，包括 duckbox、line 和 ascii。
- 您可以通过安装https扩展，直接从HTTP服务器查询CSV文件。
- 您可以将CLI用作任何数据流水线中的一步，无需创建表，通过查询外部数据集并将结果写入标准输出或其他文件。
转载自:https://juejin.cn/post/7353459702929784895