使用DuckDB 加载和清洗数据

DuckDB CLI是允许用户直接从命令行与DuckDB交互的工具。前文你看到了如何使用Python与DuckDB交互。但是，有时你只是想直接使用数据库—例如在创建新表、从不同数据源导入数据以及执行与数据库相关的任务时。在这种情况下，直接使用DuckDB CLI要有效得多。本文介绍Duck cli，并使用命令行工具加载数据、清洗数据。
在这里插入图片描述

安装duck cli

DuckDB命令行已经针对Windows、macOS和Linux三种平台进行了预编译。有关为你的平台安装DuckDB CLI的说明，请参阅官网安装页面。

对于Windows，您可以在命令提示符下使用Windows包管理器下载DuckDB CLI:

winget install DuckDB.cli

一旦下载了DuckDB命令行，你可以用下面的语法来使用它：

$ duckdb [OPTIONS] [FILENAME]

你可以从DuckDB网站获得命令行参数选项的完整列表。或者直接使用-help选项来显示选项列表：

 duckdb -help
Usage: D:\software\duckdb\duckdb.exe [OPTIONS] FILENAME [SQL]
FILENAME is the name of an DuckDB database. A new database is created
if the file does not previously exist.
OPTIONS include:-append              append the database to the end of the file-ascii               set output mode to 'ascii'-bail                stop after hitting an error-batch               force batch I/O-box                 set output mode to 'box'-column              set output mode to 'column'-cmd COMMAND         run "COMMAND" before reading stdin-c COMMAND           run "COMMAND" and exit-csv                 set output mode to 'csv'-echo                print commands before execution-init FILENAME       read/process named file-[no]header          turn headers on or off-help                show this message-html                set output mode to HTML-interactive         force interactive I/O-json                set output mode to 'json'-line                set output mode to 'line'-list                set output mode to 'list'-markdown            set output mode to 'markdown'-newline SEP         set output row separator. Default: '\n'-nofollow            refuse to open symbolic links to database files-no-stdin            exit after processing options instead of reading stdin-nullvalue TEXT      set text string for NULL values. Default ''-quote               set output mode to 'quote'-readonly            open the database read-only-s COMMAND           run "COMMAND" and exit-separator SEP       set output column separator. Default: '|'-stats               print memory stats before each finalize-table               set output mode to 'table'-unredacted          allow printing unredacted secrets-unsigned            allow loading of unsigned extensions-version             show DuckDB version

如果不提供FILENAME参数，DuckDB命令行将打开一个临时内存数据库，并显示版本号、连接信息和以D开头的提示符：

 duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D

在创建内存数据库时，在退出DuckDB CLI时将丢失所有内容。因此，这个选项只有在您想要尝试DuckDB的工作方式时才有用，在按“Ctrl+C”可以退出DuckDB命令行窗户。

DuckDB CLI更常见的用法是用于持久数据库，从而保证跨会话能保存数据，允许长期使用和重用，而无需每次重新加载或重新处理数据。

下面的示例展示了如何将DuckDB命令行与持久数据库（名为mydb.duckdb）一起使用：

duckdb mydb.duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D

现在已经创建了数据库，你可以学习如何将数据导入到数据库中。

从本地文件加载数据

首先我们创建目标表，.tables命令可以查看所有表，desc 命令可以查看表字段信息：

D create table orders(  order_id int4, product varchar, quantity int4,  price float, order_date date);
D .tables
orders
D desc orders;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ order_id    │ INTEGER     │ YES     │         │         │         │
│ product     │ VARCHAR     │ YES     │         │         │         │
│ quantity    │ INTEGER     │ YES     │         │         │         │
│ price       │ FLOAT       │ YES     │         │         │         │
│ order_date  │ DATE        │ YES     │         │         │         │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

要从本地文件加载数据，我们使用COPY命令，就像下面的代码示例一样，它加载一个CSV文件：

D COPY orders FROM 'data.csv' with (HEADER, DELIMITER ',');
D select * from orders;
┌──────────┬────────────────────────────┬──────────┬────────┬────────────┐
│ order_id │          product           │ quantity │ price  │ order_date │
│  int32   │          varchar           │  int32   │ float  │    date    │
├──────────┼────────────────────────────┼──────────┼────────┼────────────┤
│   176558 │ USB-C Charging Cable       │        2 │  11.95 │ 2019-04-19 │
│   176559 │ Bose SoundSport Headphones │        1 │  99.99 │ 2019-04-07 │
│   176560 │ Google Phone               │        1 │  600.0 │ 2019-04-12 │
│   176560 │ Wired Headphones           │        1 │  11.99 │ 2019-04-12 │
│   176561 │ Wired Headphones           │        1 │  11.99 │ 2019-04-30 │
│   176562 │ USB-C Charging Cable       │        1 │  11.95 │ 2019-04-29 │
│   176563 │ Bose SoundSport Headphones │        1 │  99.99 │ 2019-04-02 │
│   176564 │ USB-C Charging Cable       │        1 │  11.95 │ 2019-04-12 │
│   176565 │ Macbook Pro Laptop         │        1 │ 1700.0 │ 2019-04-24 │
└──────────┴────────────────────────────┴──────────┴────────┴────────────┘

从远程加载数据

等等，如果你的数据不能在本地下载怎么办？不用担心，我们也可以从远程数据源加载数据，DuckDB为一堆数据库和数据源提供了连接器。下面是从远程PostgreSQL数据库加载数据的例子：

CREATE SERVER my_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'my_host', dbname 'my_db');CREATE USER MAPPING FOR current_user SERVER my_server
OPTIONS (user 'my_user', password 'my_password');IMPORT FOREIGN SCHEMA public FROM SERVER my_server INTO my_schema;SELECT *
INTO my_table
FROM my_schema.my_remote_table;

上面我们使用CREATE server语句创建了一个到PostgreSQL数据库的服务器连接。然后，我们使用CREATE user mapping语句建立具有必要凭据的用户映射。最后，我们导入外部模式，并使用IMPORT foreign schema和SELECT into语句将所需的数据加载到本地表。

现在你可能会想，我的数据分散在Hive分区Parquet文件中。DuckDB还能导入它吗？令人震惊的是，答案是肯定的。让我们尝试用一个Hive分区的目录结构来处理事件：

SELECT * FROM parquet_scan('events/*/*/*.parquet', hive_partitioning=1);

这将从events/目录下的Hive分区数据集中读取数据。

分区在数据集很大且查询模式涉及基于特定属性过滤或聚合数据的情况下特别有用。例如，对时间序列、地理数据、分类数据和增量更新的查询可以通过不加载整个数据集来基于分区列进行查询而获益。

数据转换

DuckDB提供了广泛的SQL函数和表达式来帮助进行数据转换和清理。下面是一些例子：

使用COALESCE清理缺失值：

select coalesce(product,'a') as product from orders;
┌────────────────────────────┐
│          product           │
│          varchar           │
├────────────────────────────┤
│ USB-C Charging Cable       │
│ Bose SoundSport Headphones │
│ Google Phone               │
│ Wired Headphones           │
│ Wired Headphones           │
│ USB-C Charging Cable       │
│ Bose SoundSport Headphones │
│ USB-C Charging Cable       │
│ Macbook Pro Laptop         │
└────────────────────────────┘

使用distinct删除重复数据

D select distinct product from orders;
┌────────────────────────────┐
│          product           │
│          varchar           │
├────────────────────────────┤
│ Google Phone               │
│ Macbook Pro Laptop         │
│ USB-C Charging Cable       │
│ Bose SoundSport Headphones │
│ Wired Headphones           │
└────────────────────────────┘

转换字符串日期

D SELECT strptime('02/03/1992', '%d/%m/%Y');
┌────────────────────────────────────┐
│ strptime('02/03/1992', '%d/%m/%Y') │
│             timestamp              │
├────────────────────────────────────┤
│ 1992-03-02 00:00:00                │
└────────────────────────────────────┘