数据迁移分类

  • 数据格式
  • 数据范围
  • 数据用途



数据格式

按数据格式分类,可分为:

  • 文本格式
  • 二进制格式。



数据范围

按数据范围分类,可分为:

  • 整库迁移
  • 单表迁移



数据用途

按数据用途分类,可分为:

  • 产品自己使用
  • 提供给第三方数据



数据迁移问题

  • 行分隔符
  • 列分隔符
  • 日期时间格式
  • 汉字乱码



数据包含行分隔符

  • 默认的行分隔符为\n(Linux)或\r\n(Windows)。对于从Windows系统生成的数据需要在Linux系统导入数据时,可以使用dos2unix工具删除\r。
  • 对于需要导入数据的系统,如果导入程序只支持逐行解析数据并导入,则需要在生成数据时,对数据中的换行符进行转义或删除。对字符进行转义会使数据产生膨胀,在导入数据时,可根据实际情况对目标字段进行加长处理。
  • 对于一些数据库系统,通过定义一些规则来解决数据中包含换行符问题,如GBase 8s使用在换行符前增加一个\表示这行数据未完成,下一行仍然是本行数据的延续,来解决数据中包含换行符的问题。基于特定规则的换行符处理方式,只能在本系统内使用,无法提供数据给第三方系统。
  • 部分厂商采用自定义换行符方法,解决数据中包含\n问题。用户可以指定不可见的二进制字符作为数据的换行符。



数据包含列分隔符

  • 用户常指定一个字符,做为列数据的分隔符,如逗号或管道符。对于数据中全部为数值字段的数据,这种方式非常有效和高效。
  • 对于迁移的数据包含文本数据时,数据中可能包含任意的单个可见字符,指定单个字符作为列分隔符时,容易出现部分数据无法导入现象。通常迁移工具需要支持转义,将数据中的列分隔符进行转义,以区别数据中的列分隔符和实际的列分隔符。
  • 一些工具支持不可见字符或多个字符一起作为列分隔符。这种方案通常不需要对数据中的字符进行转义处理,适用于不同数据库系统间的数据迁移。



数据中的日期时间格式不匹配

格式化符 说明
%a 星期的短格式表示
%A 星期的完整格式表示
%b 月份的短格式表示
%B 月份的完整格式表示
%C 世纪的表示,通常是年值除以100后的整数部分
%d 天(01-31)
%D 相当于格式:%m/%d/%y
%e 天(1-31)
%h 和%b相同
%H 小时(00-24)
%iy 两位年(00-99)
%iY 四位年(0000-9999)
%m 月(01-12)
%M 分钟(00-59)
%n 换行符
%S 秒(00-59)
%t 制表符
%w 星期的数字表示(0-6)
%y 两位年(00-99)
%Y 四位年(0000-9999)



汉字乱码

  • 不同的汉字编码格式,是产生乱码的主要原因。
  • 数据截断可能导致部分数据产生乱码。



数据迁移方法

数据迁移工具

  1. unload/load
  2. dbload
  3. dbexport/dbimport
  4. gunload/gload
  5. external table

数据迁移工具对比

数据迁移方法 运行方式 数据格式 数据范围 数据用途
unload/load SQL 文本数据 单表 自用/第三方
dbexport/dbimport 命令行 文本数据 自用
dbload 命令行 文本数据 多表 自用
gunload/gload 命令行 二进制数据 库/表 自用
external table SQL 文本数据 单表 自用/第三方



unload/load

unload语法

unload to 'file_name' [delimiter 'delimiter_string'] 
select <* | columns> from <table_name | synonym_name | view_name>;

file_name:保存数据的文件名,可以为全路径或者相对路径;
delimiter:定义数据列分隔符,为一个或多个字符,默认为'|';
columns:查询的字段列表;

导出单表数据,列分隔符使用【,】。

unload to '../data/unload_01.unl' delimiter ','
select * from t_dept;

导出多表关联数据,列分隔符使用【|】。

unload to '../data/unload_02.unl' 
select a.f_employeeid, a.f_employeename, a.f_deptid, b.f_deptname, a.f_birthdate
from t_employee a, t_dept b
where a.f_deptid = b.f_deptid;

导出单表数据,日期类型数据使用类似

18.06.2021

的日期格式,列分隔符使用一个英文句号【

.

】。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

unload to '../data/unload_03.unl' delimiter '.'
select * from t_employee;

导出单表数据,日期类型数据使用类似

18.06.2021

的日期格式,列分隔符使用四个英文句号【

....

】。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

unload to '../data/unload_04.unl' delimiter '....'
select * from t_employee;

导出单表数据,日期类型数据使用类似

18.06.2021

的日期格式,列分隔符使用一个#号和三个英文句号【

#...

】。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

unload to '../data/unload_05.unl' delimiter '#...'
select * from t_employee;

导出单表数据,日期类型数据使用类似

18.06.2021

的日期格式,列分隔符使用三个英文句号和一个#号【

...#

】。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

unload to '../data/unload_06.unl' delimiter '...#'
select * from t_employee;

导出单表数据,日期类型数据使用类似

Wed Dec 25 1985

的格式,列分隔符使用【

.*.

】。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%a %b %e %iY"

unload to '../data/unload_07.unl' delimiter '.*.'
select * from t_employee;

导出单表数据,日期类型数据使用类似

星期三 十二月 25 1985

的格式,列分隔符使用【

...

】。

-- export CLIENT_LOCALE=zh_CN.UTF8
-- export GL_DATE="%A %B %e %iY"

unload to '../data/unload_08.unl' delimiter '...'
select * from t_employee;

GBase 8s最多支持4个字符做为列分隔符,超过4个时分报错。

unload to '../data/unload_09.unl' delimiter '#...#'
select * from t_employee;

load语法

load from 'file_name' [delimiter 'delimiter_string']
insert into <table_name | synonym_name| view_name [(columns)]>;

file_name:导入的数据文件,可以为全路径或者相对路径文件名;
delimiter:定义数据列分隔符,为一个或多个字符,默认为'|';
columns:导入表的字段列表,默认为数据库表的字段的顺序;

加载单表数据,列分隔符为【

,

】。

load from '../data/unload_01.unl' delimiter ','
insert into t_dept_01;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_dept_01;

尝试加载通过多表关联导出的数据。

对于数据文件和表定义不同的文件,通常无法使用load方式导出。需要采用其它技术,实现数据入库。

-- export CLIENT_LOCALE=zh_CN.UTF8
-- export GL_DATE="%iY %m月 %d日"

load from '../data/unload_02.unl' delimiter '|' 
insert into t_employee_02(f_employeeid, f_deptid, f_birthdate) values(f01, f02, f03, f05);

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_02;

加载单表数据,观察列分隔符与数据中字符冲突时的转义处理。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_03.unl' delimiter '.'
insert into t_employee_03;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_03;

加载单表数据,观察列分隔符与数据中字符冲突时的转义处理。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_04.unl' delimiter '....'
insert into t_employee_04;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_04;

加载单表数据,观察列分隔符与数据中字符冲突时的转义处理。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_05.unl' delimiter '#...'
insert into t_employee_05;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_05;

加载单表数据,观察列分隔符与数据中字符冲突时的转义处理。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%d.%m.%iY"

load from '../data/unload_06.unl' delimiter '...#'
insert into t_employee_06;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_06;

加载单表数据,熟悉日期字段数据的格式控制。

-- export CLIENT_LOCALE=en_US.UTF8
-- export GL_DATE="%a %b %e %iY"

load from '../data/unload_07.unl' delimiter '.*.'
insert into t_employee_07;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_07;

加载单表数据,熟悉日期字段数据的格式控制。

-- export CLIENT_LOCALE=zh_CN.UTF8
-- export GL_DATE="%A %B %e %iY"

load from '../data/unload_08.unl' delimiter '...'
insert into t_employee_08;

!echo "******************************************"
!echo "# Query the table to validate load result."
!echo "******************************************"
select * from t_employee_08;



dbload

dbload语法

dbload [-d db_name] [-c cmd_file] [-l log_file] [-e errors] [-n num_rows]
	[-i i_skip] [-s] [-p] [-r | -k] [-X]

db_name:指定要加载数据的数据库名称
cmd_file:指定包含加载命令的文件路径
log_file:指定日志文件,其中记录不能正确加载的数据
errors:指定最多可以有多少行数据错误,超过指定的行数后,加载任务失败
num_rows:指定多少条记录执行一次提交
s:指定只做语法检查,不进行实际的数据加载
i_skip:指定跳过多少行数据,再进行加载,可用于跳过数据中的标题部分
r:加载数据时,不对表加锁
k:加载数据时,对表加排它锁

command file语法

file 'file_name' delimiter 'delimiter_string' nfields
insert into table_name [(col1,col2,...) values (f01,f02,…)]

file_name:指定要导入的文件名称,可以为绝对路径和相对路径。
delimiter_string:数据文件使用的列分隔符。
nfields:数据文件中,每行的列数。
table_name:要导入的表的名称。
col1,col2...:表的列名称,多个列用逗号分隔。
f01,f02...:数据文件中的列数据编号,从01开始编号,多个编号用逗号分隔。

使用dbload一次导入多个表数据的方法。

[gbasedbt@train dbload]$ cat dept_employee.ctl 
file '/home/gbasedbt/train/data/unload_01.unl' delimiter ',' 2;
insert into t_dept_00;

file '/home/gbasedbt/train/data/unload_03.unl' delimiter '.' 4;
insert into t_employee_00;

使用dbload导入数据文件与表定义格式不一致的数据。

[gbasedbt@train dbload]$ cat employee.ctl 
file '/home/gbasedbt/train/tmp/unload_02.unl' delimiter '|' 5;
insert into t_employee_02(f_employeeid, f_employeename, f_deptid, f_birthdate) values(f01, f02, f03, f05);
[gbasedbt@train dbload]$ 

说明:

当导入的数据中包含有错误数据时,需要使用dbload进行数据导入,在导入时指定参数-e max_errors,即允许数据文件中包含的最大错误行数。



dbexport/dbimport

dbexport语法

dbexport <database> [-X] [-c] [-q] [-d] [-ss [-si]] [-ext]
  [{ -o <dir> | -t <tapedev> -b <blksz> -s <tapesz> [-f <sql-command-file>] }] [-nw]
  [-no-data-tables[=table name{,table name}]]
  [-no-data-tables-accessmethods[=access method name{,access method name}]]

database:指定要导出全部表数据的数据库名称
no-data-tables:指定哪些表不导出数据
o:指定导出的数据保存在哪个操作系统的目录中
ss:测试是否对分片表有特殊作用

导出数据库

dbexport mydb -o .

dbimport语法

dbimport <database> [-X] [-c] [-q] [-ext] [-d <dbspace>]
  [-l [{ buffered }] [-ansi]] [-ci] [-nv] [-D]
  [{ -i <dir> | -t <tapedev> [ -b <blksz> -s <tapesz> ] [-f <script-file>] }]

database:指定要导入全部表数据的数据库名称
d:指定数据库导入到哪个数据库空间中
nv:不对引用约束进行校验
i:指定要导入数据所在的目录

导入数据库。

dbimport mydb -d datadbs1 -i .



gunload/gload

gunload语法

gunload [-l] [-t <tape_device>] [-b <block size>] [-s <tape size>]
		<db_name>[:[<owner>.]<table_name>]

tape_device:保存数据的磁带设备。当使用磁盘时,需创建一个空文件并指定该文件。
db_name:要导出数据的数据库名称。如果不指定表名称,则导出全库数据。
table_name:当需要导出表数据时,指定表名称。

说明:
参数b,参数s和参数l主要用于使用磁带设备,当使用磁盘设备时,可以忽略。
导出数据为二进制格式,只能用于兼容硬件上的兼容数据库版本的数据导入。
该方式使用限制较严格,真实场景中使用较少。

卸载数据库

touch mydb.bin
gunload -t mydb.bin mydb

卸载表

touch mytable.bin
gunload -t mytable.bin mydb:t_user

gload语法

gload [-l] [-t <tape_device>] [-b <block size>] [-s <tape size>]
  [-d <data_dbs>] <db_name>[:[<owner>.]<table_name>]
  [{-i <old index name> <new index name>}]
  [{-fd old-DBspace-name new-DBspace-name}]
  [{-fi index-name old-DBspace-name new-DBspace-name}]
  [{-c <old constraint name> <new constraint name>}]

tape_device:保存数据的磁带设备或操作系统文件目录。当为操作系统的文件时,需要指定该文件。
data_dbs:指定数据导入时,使用的数据库空间。
db_name:指定导入的数据库名称。
table_name:指定导入的表名称。

加载数据库

gload -t mydb.bin -d datadbs1 mydb

加载表

gload -t mytable.bin -d datadbs1 mydb:t_user



external table

external table语法

create external table table_name([column definition | sameas table_template])
using (
datafiles ('disk:/textfile'),
format 'delimited',
delimiter 'delimiter_string'
);

table_name:要创建的外部表名称。
table_template:可以指定一个模板,以该表的列定义创建一个外部表。
datafiles:指定外部表使用的数据文件。
format:
delimiter:

创建外部表

[gbasedbt@train external]$ cat create.sql 
-- export GL_DATE="%d.%m.%iY"

create external table t_ext_data_01(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date)
using(
datafiles ('disk:/home/gbasedbt/train/data/ext_data.unl'),
format 'delimited',
delimiter '.'
);

select * from t_ext_data_01;
[gbasedbt@train external]$ 



附录



数据准备

[gbasedbt@train train]$ cat table_prepare.sql 
-- database mydb;
create table t_dept_00(f_deptid int, f_deptname varchar(20));
create table t_dept_01(f_deptid int, f_deptname varchar(20));
create table t_employee_00(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_01(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_02(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_03(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_04(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_05(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_06(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_07(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_08(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
create table t_employee_09(f_employeeid int, f_deptid int, f_employeename varchar(20), f_birthdate date);
[gbasedbt@train train]$ 
[gbasedbt@train train]$ cat data_prepare.sql 
-- export GL_DATE="%iY-%m-%d"

create table t_dept(f_deptid int, f_deptname varchar(50));

insert into t_dept values(1,'Dev');
insert into t_dept values(2,'Test');
insert into t_dept values(3,'Market');

create table t_employee(f_employeeid int, f_deptid int, f_employeename varchar(50), f_birthdate date);

insert into t_employee values(1,1,'Bill','1983-06-01');
insert into t_employee values(2,1,'John','1985-12-25');
insert into t_employee values(3,2,'Mary','1987-10-10');
insert into t_employee values(4,3,'Kate','1989-11-11');
insert into t_employee values(5,1,'Will
Smith','1981-02-28');
[gbasedbt@train train]$



版权声明:本文为jihui8848原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/jihui8848/article/details/121125421