GaussDB 数据导入导出工具介绍

本节课程一起来学习一下GaussDB数据库的导入导出。

目录

一、数据导入导出场景划分 

1. gsql工具适用场景和使用方法

2. copy使用场景和使用方法

3.gs_dump工具使用方法

4. gs_restore工具使用方法

二、gs_loader介绍

1. 工具介绍

2.创建系统表和数据表

3.创建控制文件ctl

4.数据文件data.csv示例

5.进行导入

6.执行结果

三、GDS使用方法-导入示例

1.启动GDS服务

 2.创建外表

3.执行导入

四、GDS使用方法-导入错误表分析

1.错误表

​编辑2.常见导入错误

五、GDS使用方法-导出

1.启动GDS服务

2.创建外表

3.执行导出

---

一、数据导入导出场景划分 

1. gsql工具适用场景和使用方法

适用场景
文本格式对象定义的创建

使用方法

gsql -d db1 -p 16000 -U u1 -W Huawei@123 -f/data/table.sql

示例：

ALTER SESSION SET CURRENT_SCHEMA = PUBLIC; ALTER TABLE "PUBLC".GA_COM_COLLECTION_ERROR_TABLEMODIFY MESSAGE VARCHAR(4000); ALTER TABLE "PUBLIC".GA_COM_COLLECTION_ERROR_TABLE ADDCOLUMN FRRORCOUNTINT NUL DEEAULT O; ALTER TABLE "PUBLIC".GA_COM_COLLECTION_ERROR_TABLE ADDCOLUMN PLUGINID VARCHAR (50)NULL; ALTER TABLE "PUBLIC".GA_COM_COLLECTION_ERROR_TABLE ADDCOLUMN IS ALARM RAISED INT NULL DEFAULT O; CREATE INDEX GA_COM_COLLECTION_ERROR_TABLE_IDX ON "PUBLIC".GA_COM_COLLECTION_ERROR_TABLE(CLUSTERID,PLUGINID); CREATE OR REPLACE FUNCTION GenerateSnapshotID()    RETURN BIGINT IS    SnapID BIGINT; BEGIN    SELECT"PUBLIC".GA_COM_COLLECTION_SNAPSHOT_SEQ.nextval into    SnaplD from SYS_DUMMY;    RETURN SnapID; END;

2. copy使用场景和使用方法

copy是数据库服务端的命令，只能在服务端执行数据导入，\copy则可以在客户端执行命令。
适用场景

小数据量表以文本数据作为来源导入;

小数量表的导出，查询结果集导出;

使用方法

文本数据导入：

copy t1 from '/data/input/t1.txt'delimiter ',';

表数据导出：

copy t1 to '/data/input/t1 output.txt' delimiter ',';

查询结果集导出：

copy (select * from t1 where a2=1) to '/data/input/t1 output.txt'delimiter ',';

注:分布式场景中，copy from/to是从CN端进行数据的导入导出，与GDS方式相比性能较低。

数据文件示例(CSV)

1,AAAA,842334595,LKDHSGWIE04OKJNGOERI20ONG 2,BBBB,842339356,KBJASAKDGNIASKDGNOKRMKNGA 3,CCCC,840282591,JBIASKDG10JT3409JNLG0123L 4,DDDD,842871486,LKSD8498LJFG71LKLJASNDGDS 5,EEEE,8429897130,SD81KNA0LAKJG0O4L23NL1KN 6,FFFE,842334595,LLA0AKJN88ASJDGN6LK3LKLNA 7,GGGG,840148810,LKDHSGWIE04OKJNI20ONGLSDK 8,HHHH,846768299,YUIHBRIASASD8BDKHDBFAIS2L 9,IIII,847990809,YNBBV2VXZUODJNFAKS3498YBS 10,JJJJ,842334595,SDUGBAKSHD874TI1BGPO14J 11,KKKK,827592226,T9134F90AKLSAJOIKAJSDFFYA 12,LLLL,842334595,LKDHSGWIE04OKJNGOERIGLSDK 13,MMMM,842849111,YJASDHA690HASJ013B01B102 14,NNNN,842334595,JBIASKDG10JTJNLASDKG0123L 15,OOOO,847465286,JZZXKCEBO02392NOFKS0120NS 16,PPPP,842334595,YNBBZUOWEISDJNFAKS3498YBS 17,QQQQ,842334595,LKDHSGWIE040KJNGOERI20ONG 18,RRRR,848347289,U98KHNASDY9OABSDGVSD9JBO0 19,SSSS,842334595,YNBBV2VXZUOWEISDJN3498YBS 20,TTTT,842334595,LKDHSGWIE04OKJRI20ONGLSDK 21,UUUU,847187929,LLA0AKJN88GN6LK3LK32NLLTA 22,VVVV,842334595,LKSD849G71LJASOKLJASNDGDS 23,WWWW,847592917,OKNA0LAKJG0Q4L23NL1KNL1N JDBC中使用copy

适用于copy的场景中，编程的方式导入数据;

使用方法

private void metadataToDB(){    try {    CopyManager cpManager = new CopyManager((BaseConnection) conn); //清理数据库中已有数据，可选     st.executeUpdate( "TRUNCATE TABLE metadata;");     String metaFile = this.dataDirectory + File.separator+this.metadataFile.     this.logger.log(Level.INFO, "Importing metadata from " + metaFile);     long n = cpManager.copyln( "COPY metadata FROM STDIN WITH CSV" , new FileReader(metaFile));     this.loggerlog(Level.INFO, n + " metadata imported");} catch(Exception e){     e.printStackTrace();     System.exit(1);     } }

gs_dump/gs_restore适用场景
适用场景

导出、恢复整个数据库对象定义:用户可以自定义导出一个数据库或其中的对象(模式、表、视图等)。

以Sql语句进行导入、导出:导入导出为.sql文件格式为纯本格式。

避开业务高峰期，避免操作失败:gs_dump工具在进行数居导出时，其他用户可以访问数据库数据库(读或写)。

3.gs_dump工具使用方法

导出单表定义

gs_dump mydb -U u1 -W Huawei@123 -p 16000 -s -t t1 -f /data/t1.sql -F c

导出整个数据库对象

gs_dump mydb -p 16000 -s -f /data/all.sql

导出数据库所有对象和数据

gs_dump mydb -p 16000 -f /data/all.sql

导出文件示例

SET statement timeout = 0; SET xmloption = content; SET client_encoding = 'SQL ASCII'; SET standard_conforming strings = on; SET check_function_bodies = false; SET client_ min_messages = warning; SET search_path = public; SET default_tablespace = "; SET default_with oids = false; -- --Name: t1; Type: TABLE; Schema: public; Owner: rdsAdmin; Tablespace: -- CREATE TABLE t1(     name text,     age integer ) WITH (orientation=row,compression=no) DISTRIBUTE BY HASH(name); ALTER TABLE public.t1 OWNER TO "rdsAdmin"; -- --Data for Name: t1; Type: TABLE DATA; Schema: public; Owner:rdsAdmin -- COPY t1 (name, age) FROM stdin; st                   27 \. ;

4. gs_restore工具使用方法

gs_restore恢复数据库对象:gs_restore工具支持的格式包括自定义归档、目录归档和tar归档格式。1、执行gsql程序，使用如下选项导入由gs_dump/gs_dumpall生成导出文件夹(纯文本格式)的

MPPDB_backup.sql文件到mydb数据库。 gsql -d mydb -p 8000 -f /home/omm/test/MPPDB_backup.sal Password: SET ALTER TABLE CREATE INDEX REVOKE GRANT total time: 30476 ms

示例中"-f"后的是导出的文件，"8000"表示数据库服务器端口;"mydb"表示要访问的数据库名。
2、执行gs_restore，将导出的MPPDB_backup文件(目录格式)导入到mydb数据库。

示例:执行成功

gs_restore backup/MPPDB_backup -p 8000 -d mydb Password: gs_restore[2017-07-21 19:16:26]: restore operation successful gs_restore[2017-07-21 19:16:26]: total time: 21003 ms

示例:执行失败

gs_restore backup/MPPDB_backup -p 8000 -d mydb Error while PROCESSING TOC: Error from Toc entry 6245:0 18719 TABLE DATA ga_an_an failure_report_detail apprim COPY failed for table "ga_an_an_failure_report_detail": ERROR: duplicate key value violates unique constraint "ga_an_an_failure_report_detail_pkey1" DETAlL: Key (faildetailid)=(1) already exists. CONTEXT: COPY ga_an_an_failure_report_detail, line 13: "" table ga_pl_ap_upgradelog complete data imported ! Finish reading 30 SQL statements! end restore operation ... WARNING: errors ignored on restore: 1 restore operation successful total time:150 ms

二、gs_loader介绍

1. 工具介绍

gs_loader工具是一款兼容Oracle的sqlldr的导入工具，语法基本兼容Oracle的sqlldr。
适用场景

Oracle的sqlldr兼容场景的导入

可以设置导入的容错性

当前只支持集中式

原理介绍

将控制文件支持的语法转换为\COPY语法，然后利用已有的\COPY功能，实现数据导入工作。

gs_loader使用方法-导入
1.创建用户并授予使用权限

CREATE USER load_user WITH PASSWORD '************'; GRANT ALL ON FUNCTION copy_error_log_create() TO load_user; GRANT ALL ON SCHEMA public TO load_user; SELECT copy_error_log_create(); SELECT copy_summary_create(); GRANT ALL PRIVILEGES ON public.pgxc_copy_error_log Toload_user; GRANT ALL PRIVILEGES ON public.gs_copy_summary To load_user;

2.创建系统表和数据表

select copy_summary_create();--gs_copy_summary记录执行结果汇总，包括成功行数，出错行数，忽略行数，空行数。

Select copy_error_log_create();--错误表pgxc_copy_error_log 。
503.1.SPC1300及以后版本之后无需再创建错误表

3.创建控制文件ctl

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

4.数据文件data.csv示例

1,OK,2007-07-8 2,OK,2008-07-8 3,OK,2009-07-8 a,OK,2007-07-8 43,DISCARD,,2007-07-8 "' 32,DISCARD,,2007-07-8 a,ERROR int,,2007-07-8 8,ERROR date,2007-37-8 "" ' 8,ERROR fields,,2007-37-8 '" 5,OK,,2021-07-30

5.进行导入

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

6.执行结果

gs_loader: version 0.1 10 Rows successfully loaded.log file is: Loader.log

gs_loader控制文件格式
控制文件示例

LOAD DATA truncate into table salldr_tbl fields terminated by ',' WHEN name ='Jack' trailing nullcols (     id integer external,     name char(32),     dt date )

a.sqlldr_tbl是目标表名，truncate表示如果表中有数据，则全部删除后再导入，可选值包括:insert、append、replace、truncate。
b.fields terminated by '，’表示数据文件中按 '，’分隔字段。
C.WHEN name =‘Jack’ 表示根据name过滤，只导入name= 'Jack’的数据。
d.小括号 中定义了导入的字段列表。id为字段名称，integer external为字段类型

1)普通数据类型: char. integer external. float external. decimal external. timestamp, date, date external. sysdate
2)特殊数据类型: integer、smallint、raw
e.以上为控制文件的简单示例，完整格式参考集中式《GaussDB Kernal 工具参考》中“客户端工具-gs_loader”章节。

gs_loader特性:支持position
1.使用场景:通过字段起始和结束位置来获取对应字段
2.使用方法:在控制文件字段列表中指定position，如下所示:

LOAD DATA truncate into table sqlldr_tbl trailing nullcols (     id position(1:4)integer external,     name position(5:6) char(32) )

gs_loader特性:支持列表达式
1.使用场景:使用表达式对数据进行变换后再插入目标表中
2.使用方法:在控制文件字段列表中指定列表达式，如下所示：

LOAD DATA truncate into table salldr_tbl fields terminated by ',' trailing 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' ) 作者：Gauss松鼠会 https://www.bilibili.com/read/cv33630164/?spm_id_from=333.999.0.0 出处：bilibili

gs_loader特性:按行提交
1.rows 命令行参数介绍
不指定rows参数时，rows无默认取值，表现为只进行一次提交，即所有数据都导入表中后进行一次事务提交。

参数指定每导入多少行数据后就进行一次提交,可以避免导入大量数据时因为失败,导致前功尽弃。 

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

 

这里的rows=10000表示每成功导入10000行提交一次

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

GDS介绍
工具介绍
GDS(Gauss Data Service)工具，用于解决分布式场景下大数据量数据导入导出慢的问题。通过DN并行导入导出，解决了CN在分布式常规导入导出的瓶颈问题，极大提升了导入导出的效率。

适用场景

分布式场景下大数据量表以文本数据作为来源导入。

大数据量表的导出。

可以设置导入的容错性。

离线导入。

GDS原理介绍
传统单节点导入性能低，通过GDS工具充分利用多节点并行导入，提高整体导入性能:

1、CN节点只负责任务的规划及下发，把数据导入的工作交给了DN节点，释放了CN节点的资源。
2、GDS进程负责数据文件的切分，然后分发给各个DN实例。
3、各DN实例接收到数据分片后解析数据，而后根据表的分布列计算hash值，确定该条数据属于哪个DN;如果属于自身则缓冲中到本地，如果属于别的DN则通过网络发送给相应的DN。

Foreian Table:
用于识别源数据文件的位置、文件格式、存放位置、编码格式、分隔符等信息;是关联数据文件与数据库实表(目标表)的对象。

三、GDS使用方法-导入示例

1.启动GDS服务

gds-d/input_data/-p10.186.251.85:8808-H0.0.0.0/0 -/log/gds_log.txt -D -t2

 2.创建外表

create foreian table t1_foreiqn(a1 varchar2(10),a2 int) SERVER qsmpp_server OPTlONS (location 'gsfs://10.186.251.85:8808/t1.txt' format 'text',encoding 'utf8’,delimiter '^',null ") per node reject limit 'value' with error_ftr_sbtest2;

3.执行导入

insert into t1 select * from t1_foreign;

四、GDS使用方法-导入错误表分析

1.错误表

2.常见导入错误

invalid byte sequence for encoding "“UTF8"”:0x00

文本数据中含有对UTF8来说非法字符的编码0x00，需要用命令进行清理tr-s“八000]“”“八040*]”<inputfile >outputfile，后续版本提供错误数据入库选项。

missing data for column “a2”

错误数据行缺失列。

value too long for type character varying(10)

错误数据行的字段值超过表定义字段长度。

五、GDS使用方法-导出

1.启动GDS服务

gds -d /input_data/-p 192.168.0.90:5000 -H10.10.0.1/24 -I/log/gds_log.txt -D -t 2

2.创建外表

create foreian table t1_foreian output(a1 yarchar2(10), a2 int)     SERVER gsmpp_server OPTlONS (location 'gsfs://10.185.240.41:8000/',     format 'text',encoding 'utf8', delimiter'^',nul ")     write only;

3.执行导出

insert into t1_foreign_output select * from t1;

导出的文本命名格式为t1_foreign_output.dat.0