duckdb导入CSV数据常用方法

黑麋鹿

2024-02-26 2024-02-26 约 1756 字预计阅读 4 分钟

官方参数

以下是可以传递给 CSV 读取器的参数。 COPY语句和 READ_CSV函数都接受这些参数。

区别是COPY语句参数与参数值之间使用空格，而READ_CSV函数参数与参数值之间使用=。

官方参数含义对照表--------(点击展开)

姓名	描述	类型	默认
all_varchar	跳过 CSV 解析的类型检测并假设所有列的类型为的选项`VARCHAR`。	BOOL	FALSE
allow_quoted_nulls	允许将引用值转换为`NULL`值的选项	BOOL	TRUE
auto_detect	启用CSV 参数的自动检测。	BOOL	TRUE
auto_type_candidates	此选项允许您指定嗅探器在检测 CSV 列类型时将使用的类型，例如`SELECT * FROM READ_CSV('csv_file.csv', auto_type_candidates=['BIGINT', 'DATE'])`. 该`VARCHAR`类型始终包含在检测到的类型中（作为后备选项）。	TYPE[]	[‘SQLNULL’, ‘BOOLEAN’, ‘BIGINT’, ‘DOUBLE’, ‘TIME’, ‘DATE’, ‘TIMESTAMP’, ‘VARCHAR’]
columns	指定 CSV 文件中包含的列名称和列类型的结构（例如`{'col1': 'INTEGER', 'col2': 'VARCHAR'}`）。使用此选项意味着不使用自动检测。	STRUCT	（空的）
compression	文件的压缩类型。默认情况下，这将自动从文件扩展名中检测到（例如，`t.csv.gz`将使用 gzip、`t.csv`将使用`none`）。选项有`none`, `gzip`, `zstd`。	VARCHAR	auto
dateformat	指定解析日期时使用的日期格式。请参阅日期格式。	VARCHAR	（空的）
decimal_separator	数字的小数点分隔符。	VARCHAR	.
delim 或者 sep	指定分隔文件每行（行）内的列的字符串。	VARCHAR	,
escape	指定应出现在与值匹配的数据字符序列之前的字符串`quote`。	VARCHAR	"
filename	`filename`结果中是否应包含额外的列。	BOOL	FALSE
force_not_null	不要将指定列的值与 NULL 字符串进行匹配。在字符串为空的默认情况下`NULL`，这意味着空值将被读取为零长度字符串而不是`NULL`s。	VARCHAR[]	[]
header	指定文件包含标题行，其中包含文件中每列的名称。	BOOL	FALSE
hive_partitioning	是否将路径解释为Hive 分区路径。	BOOL	FALSE
ignore_errors	忽略遇到的任何解析错误的选项 - 而是忽略有错误的行。	BOOL	FALSE
max_line_size	单行最大的大小（以字节为单位）。	BIGINT	2097152
names	列名称作为列表，请参阅示例。	VARCHAR[]	（空的）
new_line	在文件中设置新行字符。选项有`'\r'`、`'\n'`、或`'\r\n'`。	VARCHAR	（空的）
normalize_names	布尔值，指定是否应规范列名，并从中删除任何非字母数字字符。	BOOL	FALSE
null_padding	如果启用此选项，当行缺少列时，它将用空值填充右侧剩余的列。	BOOL	FALSE
nullstr	指定表示 NULL 值的字符串。	VARCHAR	（空的）
parallel	是否使用并行 CSV 读取器。	BOOL	TRUE
quote	指定引用数据值时要使用的引用字符串。	VARCHAR	"
sample_size	自动检测参数的样本行数。	BIGINT	20480
skip	文件顶部要跳过的行数。	BIGINT	0
timestampformat	指定解析时间戳时使用的日期格式。请参阅日期格式	VARCHAR	（空的）
types 或者 dtypes	列类型为列表（按位置）或结构（按名称）。例子在这里。	VARCHAR[] 或者 STRUCT	（空的）
union_by_name	多个模式的列是否应按名称统一，而不是按位置统一。	BOOL	FALSE

编码问题

CSV 阅读器仅支持使用 UTF-8 字符编码的输入文件。对于使用不同编码的 CSV 文件需要先进行转换。

导入无标题行csv文件的指定列

数据样本如下

1
2
3
4
5


姓名: 5dcjh 工号: 188982
姓名: pi2ag 工号: 156311
姓名: okut4 工号: 156163
姓名: m3jli 工号: 139333
姓名: u2uat 工号: 180964

看似有标题行，但是实质是无标题行的。姓名后面紧跟着空格，工号前后都有一个空格，也就是说，每行都被空格分割成了4列，而我们只需要导入第二和第四列。以下是导入语句：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


create table emps as 
    select name,code from READ_CSV(
        'E:\datas.txt',
        delim = ' ',
        header = false,
        columns = {
            'headername': 'VARCHAR',
            'name': 'VARCHAR',
            'headername1': 'VARCHAR',
            'code': 'BIGINT'
        });

忽略错误

如果个别行数据与样本不一致，也就是脏数据，会导致导入失败，此时可加入ingore_errors=true来忽略错误。

保存错误行

同时，使用rejects_table = 'temp_table_name'来将产生错误的行放入临时表temp_table_name（可自行修改，且该表不支持不同导入语句中复用）中。

导入目下所有csv文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


-- 在文件夹 "dir" 中读取所有文件名以 ".csv" 结尾的文件
SELECT * FROM 'dir/*.csv';

-- 读取两级目录深度中所有文件名以 ".csv" 结尾的文件
SELECT * FROM '*/*/*.csv';

-- 在文件夹 "dir" 中读取任意深度的所有文件名以 ".csv" 结尾的文件
SELECT * FROM 'dir/**/*.csv';

-- 读取 CSV 文件 'flights1.csv' 和 'flights2.csv'
SELECT * FROM READ_CSV(['flights1.csv', 'flights2.csv']);

-- 读取 CSV 文件 'flights1.csv' 和 'flights2.csv'，通过名称合并模式并输出一个 `filename` 列
SELECT * FROM READ_CSV(['flights1.csv', 'flights2.csv'], union_by_name = true, filename = true);

连接多个数据库文件进行查询

使用ATTACH 命令，可以将另一个数据库文件加载到当前数据库中，以便跨数据库进行操作。比如：在A数据库文件(first.duckdb)中，使用ATTACH 'second.duckdb'可以将first.duckdb同目录的second.duckdb加载进来。断开关联使用DETACH second（注意：此时second不需要用单引号包裹起来，同时不需要.duckdb后缀）