腾讯云数据仓库 TCHouse-P copy 的使用

一、实验表结构及数据

“`sql\\d+ t

数据测试过程可以行再录入修改

select * from t;

三、导出所有列

copy public.t to '/data/pgxz/t.txt';

cat /data/pgxz/t.txt

默认生成的文件内容为表的所有列，列与列之间使用 tab 分隔开来。NULL 值生成的值为\\N。

四、导出部分列

copy public.t(f1,f2) to '/data/pgxz/t.txt';

cat /data/pgxz/t.txt

只导出 f1 和 f2 列

五、导出查询结果

copy (select f2,f3 from public.t order by f3) to '/data/pgxz/t.txt';

cat /data/pgxz/t.txt

查询可以是任何复杂查询

六、指定生成文件格式

生成 csv 格式

“`sqlcopy public.t to '/data/pgxz/t.txt' with csv;

cat /data/pgxz/t.txt

生成二进制格式

copy public.t to '/data/pgxz/t.txt' with binary;

默认为 TEXT 格式

七、使用 delimiter 指定列与列之间的分隔符

copy public.t to '/data/pgxz/t.txt' with delimiter '@';

cat /data/pgxz/t.txt

指定分隔文件各列的字符。文本格式中默认是一个制表符，而 CSV 格式中默认是一个逗号。分隔符必须是一个单一的单字节字符，即汉字是不支持的。使用 binary 格式时不允许这个选项。

八、NULL 值的处理

copy public.t to '/data/pgxz/t.txt' with NULL 'NULL';

cat /data/pgxz/t.txt

记录值为 NULL 时导出为 NULL 字符。使用 binary 格式时不允许这个选项。

九、生成列标题名

使用 CSV 格式和 HEADER 选项导出表：“`sqlcopy public.t to '/data/pgxz/t.txt' with csv HEADER;

导出结果查看：

cat /data/pgxz/t.txt

f1,f2,f3,f41,Tbase,,72,pgxc,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,

尝试在非 CSV 模式下使用 HEADER 选项会导致错误：

copy public.t to '/data/pgxz/t.txt' with HEADER;

ERROR: COPY HEADER available only in CSV mode

十、导出 oids 系统列

创建一个带有 oids 的新表：

drop table t;CREATE TABLE t ( f1 integer NOT NULL, f2 text NOT NULL, f3 timestamp without time zone, f4 integer) with oids DISTRIBUTE BY SHARD (f1);

导入数据并包含 oids：

copy t from '/data/pgxz/t.txt' with csv;

查询确认导入结果：

select * from t;

使用 oids 选项导出数据：

copy t to '/data/pgxz/t.txt' with oids;

导出结果查看：

cat /data/pgxz/t.txt

35055 1 Tbase \\N 735056 2 pg'", xc 2017-10-28 18:24:05.643102 335177 3 pgxz 2017-10-28 18:24:05.645691 \\N

十一、使用 quote 自定义引用字符

默认 CSV 导出引用字符为双引号：

copy t to '/data/pgxz/t.txt' with csv;

导出结果查看：

cat /data/pgxz/t.txt

1,Tbase,,72,"pg'"", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,

自定义引用字符为百分号：

copy t to '/data/pgxz/t.txt' with quote '%' csv;

导出结果查看：

cat /data/pgxz/t.txt

1,Tbase,,72,%pg'", xc%%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,

十二、使用 escape 自定义逃逸符

不指定 escape 时，默认与 QUOTE 值相同：

copy t to '/data/pgxz/t.txt' with quote '%' csv;

自定义 escape 为 '@'：

copy t to '/data/pgxz/t.txt' with quote '%' escape '@' csv;

导出结果查看：

cat /data/pgxz/t.txt

1,Tbase,,72,%pg'", xc@%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,

尝试使用多字节字符作为 escape 会导致错误：

copy t to '/data/pgxz/t.txt' with quote '%' escape '@@' csv;

ERROR: COPY escape must be a single one-byte character

十三、强制给某个列添加引用字符

使用 CSV 格式和 `force_quote` 选项导出指定列：“`sqlcopy t to '/data/pgxz/t.txt' (format 'csv', force_quote (f1,f2));

导出结果查看：

cat /data/pgxz/t.txt

"1","Tbase",,7"2","pg'"", xc%",2017-10-28 18:24:05.643102,3"3","pgxz",2017-10-28 18:24:05.645691,

指定多列强制添加引用字符，列顺序可以任意排列：

copy t to '/data/pgxz/t.txt' (format 'csv', force_quote (f1,f4,f3,f2));

导出结果查看：

cat /data/pgxz/t.txt

"1","Tbase",,"7""2","pg'"", xc%","2017-10-28 18:24:05.643102","3""3","pgxz","2017-10-28 18:24:05.645691",

尝试在非 CSV 模式下使用 force_quote 选项会导致错误：

copy t to '/data/pgxz/t.txt' (format 'text', force_quote (f1,f2,f3,f4));

ERROR: COPY force quote available only in CSV mode

十四、使用 encoding 指定导出文件内容编码

使用不同编码导出文件：

copy t to '/data/pgxz/t.csv' (encoding utf8);copy t to '/data/pgxz/t.csv' (encoding gbk);

使用 set_client_encoding 设置客户端编码并导出 CSV 文件：

set client_encoding to gbk;copy t to '/data/pgxz/t.csv' with csv;

十五、使用多字节做分隔符

腾讯云数据仓库 TCHouse-P 5.06.2.1 以上版本支持多字节分隔符。

创建新表并插入数据：

create table t_position_copy(f1 int, f2 varchar(10), f3 int);insert into t_position_copy values(123, '阿弟', 2021);

使用多字节分隔符导出数据：

copy t_position_copy to '/data/tbase/t_position_copy_mul_delimiter.text' with delimiter '@\\u0026';

导出结果查看：

cat /data/tbase/t_position_copy_mul_delimiter.text

123@\\u0026阿弟@\\u00262021

十七、导入所有列

cat /data/pgxz/t.txt

truncate table t;copy t from '/data/pgxz/t.txt';

select * from t;

十八、导入部分指定列

将指定列的数据复制到文件：“`sqlcopy t(f1,f2) to '/data/pgxz/t.txt';

查看文件内容：

cat /data/pgxz/t.txt

1 Tbase2 pg'", xc%3 pgxz

清空表 t 并从文件导入指定列：

truncate table t;copy t(f1,f2) from '/data/pgxz/t.txt';

查询确认导入结果：

select * from t;

十九、指定导入文件格式

导入文这篇件：

copy t from '/data/pgxz/t.txt' (format 'text');

导入 CSV 文件：

copy t from '/data/pgxz/t.csv' (format 'csv');

导入二进制文件：

copy t from '/data/pgxz/t.bin' (format 'binary');

查询确认导入结果：

select * from t;

20、使用 delimiter 指定列与列之间的分隔符

使用自定义分隔符导入文这篇件：

copy t from '/data/pgxz/t.txt' (format 'text', delimiter '%');

使用自定义分隔符导入 CSV 文件：

copy t from '/data/pgxz/t.csv' (format 'csv', delimiter '%');

查询确认导入结果：

select * from t;

21、NULL 值处理

导入文件中包含 NULL 字符串：

copy t from '/data/pgxz/t.txt' (null 'NULL');

查询确认 NULL 值处理结果：

select * from t;

注意：字符串 NULL 在不同情境下可能不被识别，导致导入错误。

copy t from '/data/pgxz/t.txt' (null 'null');

ERROR: invalid input syntax for type timestamp: "NULL"CONTEXT: COPY t, line 1, column f3: "NULL"

22、自定义 quote 字符

查看 CSV 文件内容：“`shellcat /data/pgxz/t.csv

1,Tbase,,72,%pg'", xc%%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,

导入 CSV 文件时配置 quote 字符：

copy t from '/data/pgxz/t.csv' (format 'csv', quote '%');

23、自定义 escape 字符

查看 CSV 文件内容：

cat /data/pgxz/t.csv

1,Tbase,,72,"pg'@", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,

导入 CSV 文件时配置 escape 字符：

copy t from '/data/pgxz/t.csv' (format 'csv', escape '@');

查询确认导入结果：

select * from t;

24、CSV Header 忽略首行

查看 CSV 文件内容（含头部）：

cat /data/pgxz/t.csv

f1,f2,f3,f41,Tbase,,72,"pg'"", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,

导入 CSV 文件时忽略 header：

copy t from '/data/pgxz/t.csv' (format 'csv', header true);

查询确认导入结果：

select * from t;

25、导入 oid 列值

查看文这篇件内容（含 oid）：

cat /data/pgxz/t.txt

35242 1 Tbase \\N 735243 2 pg'", xc% 2017-10-28 18:24:05.643102 335340 3 pgxz 2017-10-28 18:24:05.645691 \\N

导入文这篇件并保留 oid：

truncate table t;copy t from '/data/pgxz/t.txt' (oids true);

查询确认导入结果：

select oid, * from t;

26、使用 FORCE_NOT_NULL 把某列中空值变成长度为 0 的字符串

查看 CSV 文件内容（含空值）：

cat /data/pgxz/t.csv

1,Tbase,,72,"pg'"", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,4,,2017-10-30 16:14:14.954213,4

导入 CSV 文件使用 FORCE_NOT_NULL：

truncate table t;copy t from '/data/pgxz/t.csv' (format 'csv', FORCE_NOT_NULL (f2));

查询确认某列空值处理结果：

select * from t where f2 = '';

27、Encoding 指定导入文件的编码

使用 `enca` 命令确定文件编码：“`shellenca -L zh_CN /data/pgxz/t.txt

Simplified Chinese National Standard; GB2312

导入文件到数据库：

copy t from '/data/pgxz/t.txt';

查询确认导入结果：

select * from t;

不指定导入文件的编码格式，则无法正确导入中文字符。使用 GBK 编码重新导入：

truncate table t;copy t from '/data/pgxz/t.txt' (encoding gbk);

再次查询确认导入结果：

select * from t;

可以使用 enconv 命令转换文件编码后再导入数据：

truncate table t;enconv -L zh_CN -x UTF-8 /data/pgxz/t.txtcopy t from '/data/pgxz/t.txt';

查询确认转换编码后的导入结果：

select * from t;

28、Position 指定字段长度导入

查看待导入的文这篇件内容：

cat /data/tbase/t_position_copy.txt

123 阿弟 2021

使用 position 指定字段长度进行导入：

copy t_position_copy (f1 position(1:3), f2 position(4:9), f3 position(10:13)) from '/data/tbase/t_position_copy.txt';

29、position 指定字段长度和函数处理导入

“`shellcat /data/tbase/t_position_copy.txt

123 阿弟 2020010120200101121212

copy t_position_copy (f1 position(1:3), f2 position(4:9), f3 position(10:17) to_date($3,'yyyymmdd'), f4 position(18:31) to_timestamp($4,'yyyymmddhh24miss')) from '/data/tbase/t_position_copy.txt';

30、position 指定使用的编码

腾讯云数据仓库 TCHouse-P 5.06.2.1 以上版本支持，这个功能是用于要导入的数据文件的字符编码与数据库的编码不一致时能正常导入数据。

file /data/tbase/t_position_copy.txt

/data/tbase/t_position_copy.txt: ISO-8859 text

不指定编码导入数据时会出错：

copy t_position_copy (f1 position(1:3), f2 position(4:7), f3 position(8:11)) from '/data/tbase/t_position_copy.txt';

ERROR: multibyte character "Ü2" is truncated for column f2CONTEXT: COPY t_position_copy, line 1: "123°¢µÜ2021", nodetype:1(1:cn,0:dn)

指定要导入的文件编码为 gbk：

copy t_position_copy (f1 position(1:3), f2 position(4:7), f3 position(8:11)) from '/data/tbase/t_position_copy.txt' encoding 'gbk';

select * from t_position_copy;

注意，gbk 编码中一个汉字的长度是 2。

如果你的 gbk 文件是从 oracle 导出来的，则导入到 tbase 的 utf 数据库这样处理才能正常导入：

copy t4(f1 position(1:1), f2 position(2:3)) from '/data/tbase/t4.txt' with csv encoding 'gbk';

如果不指定，则导入遇到如“猏”，“玕”字会出错：

copy t4(f1 position(1:1), f2 position(2:3)) from '/data/tbase/t4.txt' encoding 'gbk';

ERROR: character with byte sequence 0xab 0x0a in encoding "GBK" has no equivalent in encoding "UTF8"CONTEXT: COPY t4, line 1: "1«1ÌÚ", nodetype:1(1:cn,0:dn)

31、使用多字节分隔符

腾讯云数据仓库 TCHouse-P5.06.2.1 以上版本支持。

create table t_position_copy(f1 int, f2 varchar(10), f3 int);

cat /data/tbase/t_position_copy_mul_delimiter.text

123@\\u0026阿弟@\\u00262021

copy t_position_copy from '/data/tbase/t_position_copy_mul_delimiter.text' with delimiter '@\\u0026';

select * from t_position_copy;