GaussDB数据导出和导入

GottdesKrieges

已于 2024-12-17 19:45:39 修改

阅读量1.2k

点赞数 26

CC 4.0 BY-SA版权

分类专栏： GaussDB基础篇文章标签： gaussdb 数据库 SQL postgresql

于 2024-12-11 20:49:34 首次发布

本文链接：https://blog.youkuaiyun.com/Sebastien23/article/details/144409763

GaussDB基础篇专栏收录该内容

18 篇文章

订阅专栏

GaussDB数据导出和导入

gsql客户端工具
gs_dump和gs_restore
gs_loader批量导数
- gs_loader特性
- gs_loader使用方法
GDS分布式导数

GaussDB数据库中数据导出和导入的几种工具和使用场景：

工具	适用场景	Oracle对应工具
gsql	sql文本导出和导入，适用于集中式部署和分布式部署	SQLPlus
copy to/copy from	少量表数据在线导出和导入，适用于集中式部署和分布式部署	无
gs_dump/gs_restore	批量数据在线导出和导入，以及数据库对象定义的导出和导入。适用于集中式部署和分布式部署	expdp/impdp
gs_loader	Oracle的sqlldr兼容场景在线导入。目前仅适用于集中式部署场景	sqlldr
GDS	分布式大批量数据离线导出和导入。仅适用于分布式部署场景	无

gsql客户端工具

导入SQL脚本并执行：

gsql -d DBNAME -p PORT -U USERNAME -W PASSWORD -f script.sql

通过gsql元命令进行导出和导入：

--执行SQL脚本
\i script.sql

--将SQL查询结果写入文件
\o data.txt

gs_dump和gs_restore

gs_dump

使用gs_dump导出数据或对象定义：

--导出单表的定义
gs_dump DBNAME -U USERNAME -W PASSWORD -p PORT -s -t TABLE_NAME -f /home/omm/t1.sql -F c

--导出整个数据库对象
gs_dump DBNAME -p PORT -s -f /data/all_metadata.sql

--导出数据库所有对象和数据
gs_dump DBNAME -p PORT -f /data/all.sql

gs_dump命令的参数：

-U：数据库用户名，-W：用户口令，-p：数据库端口。
-a：只转储数据，不转储模式（数据定义）。
-s：只导出对象定义，不导出数据。
-t：只导出指定的该表。
-T：不转储的表（或视图、或序列、或外表）对象列表，可以使用多个-T选项来选择多个表，也可以使用通配符指定多个表对象。当同时输入-t和-T时，会转储在-t列表中，而不在-T列表中的表对象。
-n：只转储与模式名称匹配的模式，此选项包括模式本身和所有它包含的对象。写入多个-n选项来选择多个模式。
-N：不转储任何与模式pattern匹配的模式。可以通过输入多次-N，不转储与任何pattern匹配的模式。当同时输入-n和-N时，会转储与至少一个-n选项匹配、与-N选项不匹配的模式。
-f：导出到指定的文件。
-F：导出的格式，支持四种格式：p 纯文本、c 自定义归档、d 目录归档、t tar归档。默认格式为p。
-v：verbose模式，向转储文件输出详细的对象注解和启动/停止次数，向标准错误流输出处理信息。
-Z：指定使用的压缩比级别。取值范围0到9，0表示不压缩，9压缩比例最大、速度最慢。

gs_dump通过环境变量PGDATABASE来识别导出的数据库：

export PGDATABASE=tpcc

gs_dumpall

可以使用gs_dumpall一次导出集群的所有数据库。gs_dumpall在导出整个集群所有数据库时分为两部分：

gs_dumpall自身对所有数据库公共的全局对象进行导出，包括有关数据库用户和组，表空间以及属性（例如，适用于数据库整体的访问权限）信息。
gs_dumpall通过调用gs_dump来完成集群中各数据库的SQL脚本文件导出，该脚本文件包含将数据库恢复为其保存时的状态所需要的全部SQL语句。

gs_dumpall仅支持纯文本格式导出。所以只能使用gsql恢复gs_dumpall导出的转储内容。

gs_dumpall通用的命令参数：

-f：导出到指定的文件。
-a：只转储数据，不转储模式（数据定义）。
-s：只转储对象定义（模式），而非数据。
-l：指定所连接的转储全局对象的数据库名称，并去寻找还有其他哪些数据库需要被转储。如果没有指定，会使用postgres数据库，如果postgres数据库不存在，会使用template1。
--parallel-jobs：指定备份进程并发数，取值范围为1~1000。

gs_restore

gs_restore是针对gs_dump导出数据的导入工具。gs_restore工具支持的格式包括自定义归档、目录归档和tar归档格式。

gs_restore命令的参数：

-U：数据库用户名，-W：用户口令，-h：数据库主机名，-p：数据库端口。
-d：导入数据的目标数据库名。
-f：将数据导入到指定的文本文件。实际作用类似gs_dump。与-d不能同时使用。
-a：只导入数据，不导入数据定义（模式）。
-s：只导入模式（数据定义），不导入数据。
-c：在导入数据库对象前，删除目标库中已存在的数据库对象。
-j：指定导入的并行度。只支持自定义归档格式。
-n：只导入指定模式中的数据库对象。
-t：只导入指定的表。该选项与-n选项同时使用时，用来指定某个模式下的表对象。
-e：导入报错时退出。默认情况下导入出错时会继续，且在导入结束后输出错误信息。

使用gs_restore导入数据或数据定义：

--导入自定义归档格式
gs_restore -W xxxxx /gaussdata/backup/MPPDB_backup.dmp -p 8000 -d gaussdb

--导入目录归档格式
gs_restore -W xxxxx /gaussdata/backup/MPPDB_backup -p 8000 -d mppdb

--导入tar归档格式
gs_restore -W xxxxx /gaussdata/backup/MPPDB_backup.tar -p 8000 -d tpcc

--导入PUBLIC模式下所有对象的定义和数据，导入前清除已经存在数据库对象
gs_restore -W xxxxx /gaussdata/backup/MPPDB_backup.dmp -p 8000 -d gaussdb -e -c -n PUBLIC

--只导入PUBLIC模式下bmsql_orders表的数据
gs_restore -W xxxxx /gaussdata/backup/MPPDB_backup.dmp -p 8000 -d gaussdb -e -a -n PUBLIC -t bmsql_orders

gs_loader批量导数

gs_loader是一款语法基本兼容Oracle sqlldr的导入工具。目前仅支持集中式部署模式下的数据导入。

gs_loader的原理是将控制文件支持的语法转换为\copy语法，实现数据的导入。

gs_loader特性

支持position：通过字段起始和结束位置来获取对应字段。

# 控制文件示例：将每一行的第1到第4个字符导入到id字段，第5到第6个字符导入到name字段
LOAD DATA
truncate into table sqlldr_tbl
trailing nullcols
(
  id position(1:4) integer external,
  name position(5:6) char(32)
)

# 数据文件示例
1001aa
1002bb
1003cc

支持列表达式：使用表达式对数据进行变换后再插入到目标表中。

# 控制文件示例：name字段去掉前后的空格，address字段把冒号替换为短横连接符，money字段做判断和简单计算
LOAD DATA
truncate into table sqlldr_tbl
fields terminated by ','
tailing nullcols
(
  id integer external,
  name char(32) "trim(:name)",
  address char(8) "replace(:address,':','-')",
  money integer external "case when :money<1000 then 0 else :money-1000 end"
)

# 数据文件示例
1001,aa,GD:SZ,2000
1002, bb,GD:DG,999
1003,cc ,HB:WH,1200

支持按行提交：默认所有数据都导入后进行一次事务提交。通过rows参数控制每导入多少行就提交一次，可以避免导入大量数据时因失败造成的时间浪费。

# 每导入10000行就提交一次
gs_loader control=loader.ctl data=data.csv db=testdb discard=loader.dis bad=loader.bad errors=5 \
rows=10000 port=8000 passwd=****** user=loaduser

⭐️ 最佳实践：单次导入的提交次数不建议超过1000次，否则可能会对性能产生影响。提交次数约等于数据总行数除以rows参数的取值。

gs_loader使用方法

创建用户和系统表，并授予使用权限。

create user loaduser with password 'xxxxxx';
grant all on function copy_error_log_create() to loaduser;
grant all on schema public to loaduser;

select copy_error_log_create();
select copy_summary_create();
grant all privileges on public.pgxc_copy_error_log to loaduser;
grant all privileges on public.gs_copy_summary to loaduser;

pgxc_copy_error_log为错误表，gs_copy_summary为记录导入成功行数、错误行数、忽略行数、空行数的执行结果汇总表。

创建存放导入数据的目标表（如果不存在）。

create table loader_tbl
(
  id number,
  name varchar2(20),
  con varchar2(20),
  dt date
);

创建控制文件。

LOAD DATA
truncate into table loader_tbl
WHEN name='Tom'
fields terminated by ','
trailing nullcols
(
  id integer external,
  name char(32),
  con "length(name)",
  dt date
)

其中：

truncate into table后面是要导入的目标表名。truncate表示如果表中已经有数据，则全部删除后再导入。可选值包括：truncate、append、replace、truncate。
WHEN name='Tom'表示根据name字段对导入的数据进行过滤。
fields terminated by ','表示每一行中以逗号作为字段分隔符。

准备数据文件。

1,OK,,2023-07-8
2,OK,,2024-07-8
a,OK,,2021-07-8
34,DISCARD,,2013-07-8
...
28,DISCARD,,2015-07-8
a,ERROR int,,2008-07-8
8,ERROR date,,2007-37-8
...
8,ERROR fields,,2007-37-8
5,OK,,2019-12-30

使用gs_loader导入，并检查结果。

gs_loader control=loader.ctl data=data.csv db=testdb discard=loader.dis bad=loader.bad errors=5 \
rows=10000 port=8000 passwd=****** user=loaduser

其中各参数的含义如下：

data=：指定要导入的数据文件；
db=：指定要导入的表所在的数据库名；
discard=：指定文件名，用于记录WHEN匹配失败的行。
bad=：指定文件名，用于记录导入出错的行。
errors=：允许数据文件中导入出错的行数。

GDS分布式导数

GDS仅支持分布式场景下的大规模数据导入。

📖 官方文档：https://support.huaweicloud.com/tg-dws/dws_gds_index.html

References
【1】https://support.huaweicloud.com/tg-dws/dws_07_0103.html
【2】https://support.huaweicloud.com/tg-dws/dws_gds_index.html