Skip to content

CSV

DuckDB 提供了命令来从CSV中导入数据。

导入 CSV

由于 CSV 文件并没有严格的规范，并且对于数据类型的表现非常弱。因此导入 CSV 文件时一个非常棘手的任务。

DuckDB 提供了两种方式来导入 CSV 文件:

  • 通过 SQL 标准的 COPY ... FROM 语句来实现
  • 通过 duckdb 内置的read_csv()CSV 读取器函数来导入 CSV 数据
SQL
-- 读取 csv 文件创建了内存表
SELECT * FROM read_csv('flights.csv', sample_size=20000);
-- 可以导入表
-- CREATE TABLE AS 方式
CREATE TABLE ontime AS SELECT * FROM 'flights.csv';

-- COPY 方式， 前提是必须先创建表
CREATE TABLE ontime (
    FlightDate DATE,
    UniqueCarrier VARCHAR,
    OriginCityName VARCHAR,
    DestCityName VARCHAR
);
COPY ontime FROM 'flights.csv';

read_csv()

默认情况下 read_csv() 能够通过 CSV 嗅探器来自动检测 CSV 文件的格式以及数据类型，特殊情况下 read_csv() 还提供了各种选项来修改嗅探器的结果:

参数 说明
all_varchar:bool = false 跳过类型检测，假定所有的列是 VARCHAR 类型
allow_quoted_nulls:bool = true 允许将引号包裹的值(quoted value)转换为 null
auto_detect:bool = true 启动自动类型检测
auto_type_candidates:type[] = ['SQLNULL','BOOLEAN','BIGINT','DOUBLE','TIME','DATE','TIMESTAMP','VARCHAR'] 允许嗅探器在检测类型时使用的类型
columns:dict = {} 指定 csv 文件中包含的列名和对应的数据类型，这意味着不使用自动检测
compression:str = 'auto' 文件的压缩类型，默认情况下会更具扩展名自动检测
dateformat:str = '' 指定如何解析日期格式
decimal_separator:str = '.' 数字的小数分隔符
delim/sep:str = ',' 指定分隔符
filename:bool\|str = false 结果中是否应当包含额外的 filename 列，如果值是字符串将作为 filename 的列名
force_not_null:str[] = [] 不要将指定列的值设置为 null，这意味着空字符串会作为长度为零的字符串，而不是 null
header:bool = false 指定文件包含标题行
ignore_errors:bool = false 忽略有错误的行
max_line_size:int = 2097152 一行的最大字节数
names:str[] = [] 指定列名列表
new_line:str = '\n' 指定换行符
nullstr:str = '' 指定表示 null 值的字符串
parallel:bool = true csv 读取器是否并行
quote:str = '"' 指定在对数据值进行包裹时使用的引号
null_padding:bool = false 如果行缺少列时，是否使用 null 填充右侧列
sample_size:int = 20480 自动检测类型的样本行数
skip:int = 0 要跳过的文件行数
timestampformat:str = None 制定如何解析日期时间格式
types/dtypes:dict = None 类似 {header: type} 为对应字段指定类别
union_by_name:bool = false 读取多个 csv 时根据字段名合并而不是位置

Tips

这些参数在DuckDB CLI中是可以全部使用的，当时其他客户端例如DuckDB Python API中并不都可用。此时可以通过作为 SQL 语句来传入使用

auto_detect

默认情况下 auto_detect:bool = true 开启自动检测，他会读取 sample_size 参数指定的行来检测:

  • CSV 的方言，包括分隔符、引号规则、转义方式
  • 检测每列的类型
  • 检测文件是否具有标题行

DuckDB 还提供了 一个 sniff_csv(filename) 函数来单独运行 CSV 嗅探器，该函数会以一个单行表的形式返回，他的字段包括:

列名称 描述 示例
Delimiter 分隔符 ,
Quote 包裹引号 "
Escape 转义字符 |
NewLineDelimiter 换行符 \r\n
SkipRow 跳过的行数 1
HasHeader 是否有标头 true
Columns 列对应的类型 {'name': 'VARCHAR', 'age': 'BIGINT'}
DateFormat 日期格式 %Y-%m-%d
TimestampFormat 日期时间格式 如果没有检测到可为空
UserArguments 调用 sniff_csv 的参数 sample_size=1000
Prompt 最佳推荐 FROM read_csv('my_file.csv', auto_detect=false, delim=',', ...)

Tips

.mode 默认的情况无法展示表的所有内容，此时可以 .mode json 来输出结果

Tips

sniff_csv 方法只能在 CLI 中使用

错误 CSV 文件处理

如果默认情况下出现读取错误，通常可以使用三个选项来解决:

  • ignore_errors = true: 会忽略包含错误的行
  • max_line_size = xx: 一行的最大字节数，这个在保存一些非常长的文本时就必须指定
  • null_padding = true: 缺失值以 null 补充，这个比较粗暴通常不建议使用

上面的三种方式都比较粗暴，会导致数据的丢失。我们更希望的是确切知道哪些行出现了解析错误，对于这种场景，DuckDB 提供了 CSV 拒绝表(rejects_table)功能，有三个参数来配置该功能:

参数 描述
rejects_table:str = '' 要存储 CSV 文件错误行信息的表名称
rejects_limit:int = 0 记录拒绝表中错误行的上限，0 表示没有任何限制
rejects_recovery_columns:str[] = [] 用作 csv 文件的主键的列，他会存储在拒绝表中用于表示唯一行
SQL
FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    rejects_table = 'rejects_table',
    ignore_errors = true
);

Tips

需要注意，rejects_table 功能必须配合 ignore_errors=true 使用

CSV 拒绝表的返回结果的字段如下:

列名 描述
file: str 文件路径
line: int 发生错误的 csv 文件中的行数
column: int 发明错误的 csv 文件中的列号
column_name: str 发生错误的 csv 文件中的列名称
parsed_value: str 发生强制转换错误的值
recovery_columns: dict csv 文件的可选主键
error: str 解析器遇到的错误

读取多文件

duckdb 能够批量读取 CSV 文件来构造表:

SQL
-- 读取以 .csv 结尾的所有 dir 目录中的文件
SELECT * FROM 'dir/*.csv';
-- 能够进行深度定义，即两个目录下
SELECT * FROM '*/*/*.csv';
-- 任意深度
SELECT * FROM 'dir/**/*.csv';

Note

他使用了 Glob 语法，其中 * 匹配任意数量的任意字符，** 匹配任意数量的子目录，? 匹配任意单个字符，可以使用 [abc] 或者 [a-z] 来匹配指定范围的字符。

默认情况下假设 csv 中的字段以及顺序是完全一致的，如果想要实现根据字段名称来合并表需要指定 union_by_name = true，如果想要保存文件的名称可以指定 filename = ture:

SQL
SELECT * FROM read_csv(['flights1.csv', 'flights2.csv'], union_by_name = true, filename = true);

导出 CSV

要导出数据到 CSV 文件有两种方式:

  • 通过CLI Dot Command 的 .output path/filename.csv 配合 .mode csv 来导出
  • 通过来 COPY ... TO SQL 语句来实现

Tips

Dot Command 比较灵活方便，但是 COPY TO 提供了更加细节的控制，例如导出时压缩