本节课程一起来学习一下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.loggs_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 -t22.创建外表
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
