表级导入导出
对于表级的导入与导出,能够使用的工具很多,可基于以下场景切入选择合适的工具。
- 需要导出单个表的定义和表内数据到同一个文件中时,推荐使用gs_dump工具的纯文本归档,并配合-t参数使用,可以连续使用-t备份多张表。gs_dump工具请参考《工具参考》中“数据导入导出工具 > gs_dump导出数据库信息”。
当导出的表依赖其他未导出的对象时,可能会导致导入该表时报缺少依赖对象的错误,因此恢复时请确保依赖的其他对象已经创建好后再做导入。
备份时推荐以初始用户或具备sysadmin权限的用户执行如下命令,源库为my_database,目标表为my_schema下的my_table。nohup gs_dump my_database -U root -W ******** -p 8000 -F p -f /data/backup/my_table_backup.sql -t my_schema.my_table > /data/backup/my_table_backup.log &
恢复时需要先创建好与源库属性相同的目标库,且库内已有目标模式,没有目标表,再以初始用户或具备sysadmin权限的用户执行如下命令进行恢复nohup gsql -d my_database2 -p 8000 -U root -W ******** -f /data/backup/my_table_backup.sql -a > /data/backup/my_table_restore.log &
- 仅需要导出单个表的定义,不需要表内数据。
当导出的表依赖其他未导出的对象时,可能会导致导入该表时报缺少依赖对象的错误,因此恢复时请确保依赖其他的对象已经创建好后再做导入。
推荐使用gs_dump工具的纯文本归档,配合-s参数使用,命令如下。nohup gs_dump my_database -U root -W ******** -p 8000 -F p -f /data/backup/my_table_backup.sql -t my_schema.my_table -s > /data/backup/my_table_backup.log &
恢复时需要先创建好与源库属性相同的目标库,且库内已有目标模式,没有目标表,再以初始用户或具备sysadmin权限的用户执行如下命令进行恢复。nohup gsql -d my_database2 -p 8000 -U root -W ******** -f /data/backup/my_table_backup.sql -a > /data/backup/my_table_restore.log &
- 若存在oracle的sqlldr使用习惯,或者需要将数据导入的相关日志信息(导入结果、废弃数据、出错数据)保存到客户端的场景下,可以使用gs_loader。
gs_loader支持导入CSV、TEXT、FIXED三种格式的数据文件,推荐使用CSV格式数据文件进行导入。gs_loader工具请参考《工具参考》中“数据导入导出工具 > gs_loader导入数据”。
- 三种格式的数据文件都必须以'\n'或'\r\n'为行结束符,且整个文件的行结束符保持一致(要么都是'\n',要么都是'\r\n')。
- 在TEXT格式上,gs_loader与COPY相比,无法识别转义后的特殊字符(如'\n',导入后还是'\n',不会转义为0x0A)。COPY导出的TEXT格式文件默认情况下会进行转义,如果使用gs_loader导入,被转义的字符无法转回去,会导致不一致。因此gs_loader只能导入COPY导出的不转义(使用WITHOUT ESCAPING)的TEXT格式文件。
典型场景如下所示:- 上游数据文件格式可以自行决定或者确定为CSV格式
CSV格式具备跨平台兼容性和行业普适性,很多数据源都支持导出CSV格式的数据文件。除非上游数据文件格式因为某些原因已确定不是CSV,需要根据实际的文件格式(TEXT或FIXED格式)来选择导入方法,此外我们都推荐选择CSV格式文件来导入。尤其是,如果字段数据中包含特殊字符(逗号、换行符等),只能使用CSV格式的数据文件承载(因为CSV格式会使用封闭符包裹含特殊字符的数据,防止字段数据中的特殊字符与分隔符和行结束符冲突)。
导入标准CSV格式文件时,控制文件中增加 FIELDS CSV 字句,示例如下:
--建表。 gaussdb=# create table test_loader(id int, name name); --查看控制文件test.ctl。 LOAD DATA TRUNCATE INTO TABLE test_loader FIELDS CSV FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( id integer external, name char ) --查看数据文件test.csv。 1,"aa a" 2,"bb b" 3,"cc,c" 4,ddd --执行导入。 gs_loader -p xxx host=xxx control=test.ctl data=test.csv -d testdb -W xxx --导入成功,查看导入结果。 gaussdb=# select * from t1; id | name ----+----------- 1 | aa + | a 2 | bb b 3 | cc,c 4 | ddd (4 rows)
- 如果字段封闭符不是标准的双引号,可以通过 OPTIONALLY ENCLOSED BY 字句设置。
- 如果字段分隔符不是标准的逗号,可以通过 FIELDS TERMINATED BY 字句设置。
- 上游数据文件确定为TEXT格式
在TEXT格式上,gs_loader与COPY相比,无法识别转义后的特殊字符(如'\n',导入后还是'\n',不会转义为0x0A)。因此包含分隔符、行结束符等特殊字符的数据无法用TEXT格式承载,否则会导致结构错乱。
对于合法的TEXT格式数据文件,导入示例如下:--建表。 gaussdb=# create table test_loader(id int, name name); --查看控制文件test.ctl。 LOAD DATA TRUNCATE INTO TABLE test_loader ( id integer external, name char ) --查看数据文件test.dat。 1 aaa 2 bbb 3 ccc --执行导入。 gs_loader -p xxx host=xxx control=test.ctl data=test.dat -d testdb -W xxx --导入成功,查看导入结果。 gaussdb=# select * from t1; id | name ----+------ 1 | aaa 2 | bbb 3 | ccc (3 rows)
如果字段分隔符不是标准的水平制表符,可以通过 FIELDS TERMINATED BY 字句设置。
- 上游数据文件确定为FIXED格式
FIXED格式每列的长度固定,不使用分隔符来划分字段,因此不存在字段数据跟分割符冲突的情况。但是如果字段数据中存在换行符,仍然无法处理。
导入实例如下:
--建表。 gaussdb=# create table test_loader(id int, name name); --查看控制文件test.ctl。 LOAD DATA TRUNCATE INTO TABLE test_loader ( id POSITION(1:1) integer external, name POSITION(2:5) char ) --查看数据文件test.fixed。 1aaaa 2bb b 3cc,c --执行导入。 gs_loader -p xxx host=xxx control=test.ctl data=test.fixed -d testdb -W xxx --导入成功,查看导入结果。 gaussdb=# select * from t1; id | name ----+----------- 1 | aaaa 2 | bb b 3 | cc,c (3 rows)