gs_loader
概述
gs_loader工具用于进行数据导入。gs_loader将控制文件支持的语法转换为\COPY语法，然后利用已有的\COPY功能，做主要数据导入工作，同时gs_loader将\COPY结果记录到日志中。
使用gs_loader前请确保gs_loader版本与gsql版本、数据库版本保持一致。
安装部署
在存放数据源文件的服务器上，安装并配置gs_loader客户端工具，以便后续使用gs_loader工具进行数据的导入。
- 创建存放gs_loader工具包的目录。
mkdir -p /opt/bin
- 将gsql工具包上传至上一步所创建的目录中。
以上传EULER Linux版本的工具包为例，将软件安装包中的gsql工具包"GaussDB-Kernel_数据库版本号_操作系统版本号_64bit_gsql.tar.gz"上传至上一步所创建的目录中。
- 在工具包所在目录下，解压工具包。
cd /opt/bin tar -zxvf GaussDB-Kernel_数据库版本号_操作系统版本号_64bit_gsql.tar.gz source gsql_env.sh
- 验证工具位置及版本信息。
which gs_loader
- 验证客户端版本信息。
gs_loader工具版本与gsql工具版本相对应，直接查询gsql客户端版本即可。
gsql -V
- 验证数据库版本信息，确保与客户端工具版本保持一致。
使用gsql工具连接到数据库后输入：
select version();
日志等级配置
设置日志级别，可以供开发者查看。设置后会在控制台打印工具运行的相应信息。
export gs_loader_log_level=debug export gs_loader_log_level=info export gs_loader_log_level=warning export gs_loader_log_level=error
使用权限
使用场景分为三权分立场景下及非三权分立场景下的使用。你可以通过将GUC参数enableSeparationOfDuty设置为on或者off，控制三权分立功能的开启或关闭。
GUC参数enable_copy_error_log是控制是否使用错误表pgxc_copy_error_log的参数，默认为off，即不使用错误表，错误数据直接记录到gs_loader的bad文件中。如果该参数设置为on，则会使用错误表pgxc_copy_error_log，将错误数据同时写入bad文件和错误表中。
- 默认场景，关闭三权分立（即enableSeparationOfDuty=off）时，使用者可以是数据库普通用户或管理员用户。当使用者为普通用户的时候，需要管理员用户对普通用户赋权。管理员账户可以直接使用。
- （在管理员用户下）创建用户。
CREATE USER load_user WITH PASSWORD '************';
- （在管理员用户下）将public schema权限给用户。
GRANT ALL ON SCHEMA public TO load_user;
- （在管理员用户下）创建并给用户授权gs_copy_summary表。
SELECT copy_summary_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='gs_copy_summary'); GRANT ALL PRIVILEGES ON public.gs_copy_summary To load_user;
- （可选，在管理员用户下）创建并给用户授权错误表pgxc_copy_error_log。
如果guc参数enable_copy_error_log未设置（默认为off），或者设置为off，则无需使用错误表，无需创建。否则需要创建该错误表。
SELECT copy_error_log_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='pgxc_copy_error_log'); GRANT ALL PRIVILEGES ON public.pgxc_copy_error_log To load_user;
- （在管理员用户下）创建用户。
- 开启三权分立（即enableSeparationOfDuty=on）时，使用者可以是数据库普通用户或管理员用户。使用前需要到各自的Schema下创建pgxc_copy_error_log表以及gs_copy_summary这两张表并添加索引，不需要再进行授权。
- （在初始用户下）创建用户。
CREATE USER load_user WITH PASSWORD '********';
- （在初始用户下）切换为load_user用户
\c - load_user
- （在自建用户下）创建gs_copy_summary表并添加索引。
CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text); CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);
- （可选，在自建用户下）创建pgxc_copy_error_log表并添加索引。
如果guc参数enable_copy_error_log未设置（默认为off），或者设置为off，则无需使用错误表，无需创建。否则需要创建该错误表。
CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text); CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
- （在初始用户下）创建用户。
使用环境
由用户自己将工具路径添加到PATH中。gs_loader支持SSL加密通信，使用方式同gsql方式。
新增系统表
新增gs_copy_summary表，记录COPY执行结果汇总，包括成功行数，出错行数，忽略行数，空行数。
新增函数：copy_summary_create用于创建gs_copy_summary表。
gs_copy_summary表格式如下：
relname | public.sqlldr_tbl
begintime | 2021-09-03 16:00:11.7129-04
endtime | 2021-09-03 16:00:15.259908-04
id | 21870
pid | 47582725060352
readrows | 100000
skiprows | 0
loadrows | 111
errorrows | 0
whenrows | 99889
allnullrows | 0
detail | 111 Rows successfully loaded.
| 0 Rows not loaded due to data errors.
| 99889 Rows not loaded because all WHEN clauses were failed.
| 0 Rows not loaded because all fields were null.
|
系统表gs_copy_summary字段说明
|
字段名称 |
描述 |
|---|---|
|
relname |
本次导入的目标表名。 |
|
begintime |
导入任务开始时间。 |
|
endtime |
导入任务结束时间。 |
|
id |
本次导入的事务id。 |
|
pid |
本次导入工作线程id。 |
|
readrows |
本次导入任务共计读取的数据行数。 |
|
skiprows |
本次导入任务共计跳过的数据行数。 |
|
loadrows |
本次导入任务成功导入的数据行数。 |
|
errorrows |
本次导入任务的错误数据行数。 |
|
whenrows |
本次导入任务违反when过滤条件的数据行数。 |
|
allnullrows |
全部字段都为空的数据行数。 |
|
detail |
本次导入任务的导入情况总结。主要包括成功导入行数、错误数据行数、违反when条件行数和字段全空行数等。 |
使用指导
- （非三权分立）仅对于普通用户
- （在管理员用户下）创建用户。
CREATE USER load_user WITH PASSWORD '************';
- （在管理员用户下）将public schema权限赋给普通用户。
GRANT ALL ON SCHEMA public TO load_user;
- （在管理员用户下）创建并给用户授权gs_copy_summary表。
SELECT copy_summary_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='gs_copy_summary'); GRANT ALL PRIVILEGES ON public.gs_copy_summary To load_user;
- （可选，在管理员用户下）创建并给用户授权错误表pgxc_copy_error_log。
如果GUC参数enable_copy_error_log未设置（默认为off），或者设置为off，则无需使用错误表，无需创建。否则需要创建该错误表。
SELECT copy_error_log_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='pgxc_copy_error_log'); GRANT ALL PRIVILEGES ON public.pgxc_copy_error_log To load_user;
- （在管理员用户下）切换用户
\c - load_user
- （在管理员用户下）创建用户。
- （三权分立）对于普通用户和管理员用户。
- （在初始用户下）创建用户。
CREATE USER load_user WITH PASSWORD '************';
- （在初始用户下）切换为load_user用户。
\c - load_user
- （在自建用户下）创建gs_copy_summary表并添加索引。
CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text); CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);
- （在自建用户下，可选）创建pgxc_copy_error_log表并添加索引。
如果GUC参数enable_copy_error_log未设置（默认为off），或者设置为off，则无需使用错误表，无需创建。否则需要创建该错误表。
CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text); CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
- （在初始用户下）创建用户。
- 创建表和控制文件，准备数据文件。
创建表loader_tbl。
1 2 3 4 5 6 7
CREATE TABLE loader_tbl ( ID NUMBER, NAME VARCHAR2(20), CON VARCHAR2(20), DT DATE );
（在gs_loader客户端）创建控制文件loader.ctl。
1 2 3 4 5 6 7 8 9 10 11
LOAD DATA truncate into table loader_tbl WHEN (2:2) = ',' fields terminated by ',' trailing nullcols ( id integer external, name char(32), con ":id || '-' || :name", dt date )
（在gs_loader客户端）创建guc参数guc.txt
set a_format_copy_version='s1';
（在gs_loader客户端）创建数据文件data.csv。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
1,OK,,2007-07-8 2,OK,,2008-07-8 3,OK,,2009-07-8 4,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
- 进行导入。
执行导入前，先确认gs_loader工具有可执行权限。确保当前路径有文件写入权限（gs_loader在处理过程中会生成一些临时文件，导入完成后自动删除）。
1gs_loader control=loader.ctl data=data.csv db=testdb bad=loader.bad errors=5 port=8000 passwd=************ user=load_user
执行结果：
1 2 3 4 5 6
gs_loader: version 0.1 5 Rows successfully loaded. log file is: loader.log
gs_copy_summary记录调用的copy语法以及详细情况，[badfile]_bad.log文件记录错误数据以及详细情况，为防止上一次导入时记录的错误数据以及详细情况被覆盖，建议每次执行导入时使用不同的bad参数。pgxc_copy_error_log默认关闭，如果使用错误表pgxc_copy_error_log记录错误数据以及详细情况，请开启guc参数enable_copy_error_log。如需删除表中的数据，可以对上述表执行truncate或者delete操作。
参数说明
|
参数 |
参数说明 |
参数类型 : 取值范围 |
|---|---|---|
|
help |
查看帮助信息。 |
- |
|
user |
数据库链接用户（与-U等价）。 |
字符串 |
|
-U |
数据库链接用户（与user等价）。 |
字符串 |
|
passwd |
用户密码（与-W等价）。 |
字符串 |
|
-W |
用户密码（与passwd等价）。 |
字符串 |
|
db |
数据库名称（必选，与-d等价）。 |
字符串 |
|
-d |
数据库名称（必选与db等价）。 |
字符串 |
|
host |
指定正在运行服务器的主机名、Unix域套接字的路径、或者域名。接受以“,”分隔的字符串来指定多个主机地址，支持指定多个主机地址（与-h等价）。 当指定多个主机地址时，默认选择连接到主节点。 |
参考gsql --host参数 |
|
-h |
指定正在运行服务器的主机名、Unix域套接字的路径、或者域名。接受以“,”分隔的字符串来指定多个主机地址，支持指定多个主机地址（与host等价）。 当指定多个主机地址时，默认选择连接到主节点。 |
参考gsql --host参数 |
|
port |
指定数据库服务器的端口号。可以配置一个或多个，当配置一个时，所有的IP都使用同一个端口连接；当配置多个时，顺序与IP顺序相同，个数必须与IP数相等，当不相等时会报错（与-p等价）。 |
参考gsql --port参考 |
|
-p |
指定数据库服务器的端口号。可以配置一个或多个，当配置一个时，所有的IP都使用同一个端口连接；当配置多个时，顺序与IP顺序相同，个数必须与IP数相等，当不相等时会报错（与port等价）。 |
参考gsql --port参考 |
|
create |
是否创建pgxc_copy_error_log和gs_copy_summary表。 |
[true, false]，默认true |
|
clean |
是否清除本次错误记录。 |
[true, false]，默认false |
|
data |
数据文件，可以指定多个，或者通配符多字符通配(*)以及单字符通配(?)（必选）。 |
字符串 |
|
control |
控制文件名称（必选）。 |
字符串 |
|
log |
日志文件名称。 |
字符串 |
|
bad |
出错行以及详细情况记录文件名称，也可以指定目录，不指定时根据数据文件名生成。 |
字符串 |
|
discard |
WHEN匹配失败行记录文件名称，也可以指定目录，根据数据文件名生成。 |
字符串 |
|
errors |
允许数据文件中出现多少出错行。 |
整数，默认0 |
|
skip |
允许跳过数据文件的前多少行。 |
整数，默认0 |
|
bindsize |
仅做语法兼容不实现功能。 |
- |
|
rows |
多行提交参数，指定导入多少行数据后进行一次提交。 |
整数，取值范围[1, 2147483647] |
- 参数均为小写，不支持大写，同时兼容gsql登录方式：-p端口号，-h主机，-d数据库，-U用户名，-W密码方式。
- 使用rows参数时，提交次数不能超过1000次，否则会对性能产生影响。提交次数约等于数据文件中数据行数除以rows参数取值。不指定rows参数时，rows无默认取值，表现为只进行一次提交，即所有数据都导入表中后进行一次事务提交。
- gs_loader在设置guc参数a_format_load_with_constraints_violation开启支持约束冲突不回滚场景时，如果表带有BEFORE/AFTER ROW INSERT触发器，则每次提交行数不能超过1000万行。
- gs_loader在设置guc参数a_format_load_with_constraints_violation开启支持约束冲突不回滚场景时，不支持语句级触发器。
控制文件
- 语法说明：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
LOAD [ DATA ] [CHARACTERSET char_set_name] [INFILE [directory_path] [filename ] ] [BADFILE [directory_path] [filename ] ] [OPTIONS(name=value)] [{ INSERT | APPEND | REPLACE | TRUNCATE }] INTO TABLE table_name [{ INSERT | APPEND | REPLACE | TRUNCATE }] [FIELDS CSV] [TERMINATED [BY] { 'string' }] [OPTIONALLY ENCLOSED BY { 'string' }] [TRAILING NULLCOLS] [ WHEN { (start:end) | column_name } {= | !=} ‘string’ ] [( col_name [ [ POSITION ({ start:end }) ] ["sql_string"] ] | [ FILLER [column_type [external] ] ] | [ CONSTANT "string" ] | [ SEQUENCE ( { COUNT | MAX | integer } [, incr] ) ]|[NULLIF (COL=BLANKS)] [, ...] )]
- 参数说明：
- CHARACTERSET
字符集。
取值范围：字符串，目前可指定为'AL32UTF8'、'zhs16gbk'或'zhs32gb18030'。
控制文件中CHARACTERSET指定的字符集，应该和文件的编码格式保持一致，否则会报错或者导入数据乱码。
- INFILE
当前关键字无效，并在控制文件中需要单独占一行，运行时候会忽略该关键字。需要用户在gs_loader命令行参数中指定对应的数据文件。
- BADFILE
当前关键字无效，运行时候会忽略该关键字，如果gs_loader 命令行参数没有指定badfile,则会根据对应控制文件名称生成对应的badfile文件。
- OPTIONS
其中只有skip和rows功能生效，skip=n为导入时跳过前n条数据，rows=n为导入多少行数据后进行一次提交。命令行和控制文件同时指定时，命令行优先级更高。
- INSERT | APPEND | REPLACE | TRUNCATE
导入模式。
INSERT：如果表中有数据，则报错。
APPEND：直接插入数据。
REPLACE：如果表中有数据，则全部删除，然后再插入。
TRUNCATE：如果表中有数据，则全部删除，然后再插入。
- 在写控制文件(.ctl)文件时，在INTO TABLE table_name语句前后都可以指定（导入模式，INSERT | APPEND | REPLACE | TRUNCATE），使用优先级为：在INTO TABLE table_name语句后面指定导入模式优先级高于在INTO TABLE table_name语句前面指定导入模式，在INTO TABLE table_name语句后面指定导入模式会覆盖在前面指定的导入模式。
- 当开启多个gs_loader会话，并发地向同一张表中导入数据时，推荐以APPEND的方式进行导入，以INSERT|REPLACE|TRUNCATE的方式会出现导入报错或数据导入不全的问题。
- FIELDS CSV
标识使用copy的CSV模式。在CSV模式下分隔符缺省值为逗号，引号字符的缺省值为双引号。
当前CSV模式下，被引号包裹的换行符被视为字段数据的一部分。
- table_name
表的名称（可以有模式修饰）。
取值范围：已存在的表名。
- TERMINATED [BY] { 'string' }
在文件中分隔各个字段的字符串，分隔符最大长度不超过10个字节。
取值范围：不允许包含\.abcdefghijklmnopqrstuvwxyz0123456789中的任何一个字符。
缺省值：在文本模式下，缺省是水平制表符，在CSV模式下是一个逗号。
- OPTIONALLY ENCLOSED BY { 'string' }
CSV格式文件下的引号字符。
仅在使用FIELDS CSV参数明确说明的CSV模式下缺省值：双引号。
其余模式下无缺省值。
- 设置OPTIONALLY ENCLOSED BY { 'string' }时，要么数据左边不带引号字符，如果有引号字符，左右都必须为奇数个，但不必相等。
- 当前仅CSV模式支持OPTIONALLY ENCLOSED BY { 'string' }。当指定OPTIONALLY ENCLOSED BY { 'string' }时，默认进入CSV模式。
- TRAILING NULLCOLS
当数据加载时，若数据源文件中一行的多个字段缺失的处理方式。
当一行数据的最后存在一个或多个字段为空时，按照空值将其导入到表中。不设置则会报错字段为空，将这行数据当作错误数据处理。
- WHEN { (start:end) | column_name } {= | !=}
对行中的start到end之间的字符串，或者根据列名进行行过滤。
取值范围：字符串。
- POSITION ({ start:end })
对列进行处理，根据start到end范围获取对应字符串。
- "sql_string"
对列进行处理，列表达式，根据表达式计算列的取值。详见•列表达式。
取值范围：字符串。
- FILLER
对列进行处理，如果出现FILLER，则这个字段跳过。
- column_type [external]
在导入数据时，根据不同的数据类型对数据进行处理。详见•数据类型。
- CONSTANT
对列进行处理，将插入的对应字段设置为常量。
取值范围：字符串。
- SEQUENCE ( { COUNT | MAX | integer } [, incr] )
对列进行处理，生成对应的序列值。
- COUNT：表示根据表中数据的行数开始计算。
- MAX：表示根据表中这一列的最大值开始计算。
- integer：表示从用户指定的值开始计算。
- incr：表示每次递增多少。
- NULLIF
对列进行处理，在多行导入场景中，若列名后未指定sysdate、constant、position、列表达式等运算时，执行导入操作，表现为未指定NULLIF关键字的列字段设置为空。
当前只支持 COL POSITION() CHAR NULLIF (COL=BLANKS)语法。具体使用详见•NULLIF使用用例。
- 不支持OPTIONS、INFILE、BADFILE，仅在特定场景下不报语法错误。
- gs_loader使用bad文件来记录出错数据，该数据来自错误表的rawrecord字段，由于错误表对于以某种编码无法读起的错误不记录rawrecord，因此bad文件中遇到此情况时记录空行。
- 单次导入的数据文件约束冲突的行数较多时，如数据库服务端机器内存较小（如32G），约束冲突数据量预估超200万时；或者内存128G以上，约束冲突超过1000万行时，可能会占用较大缓存，报"ERROR: memory is temporarily unavailable"而导入失败，所以此时不建议使用约束冲突不回滚特性。
- CHARACTERSET
- bad文件对应数据为空的需要对应错误表的内容参考源文件和行号（不识别某种编码序列，不写bad文件内容，只记录空行）。
loader=# select * from pgxc_copy_error_log; relname | begintime | filename | lineno | rawrecord | detail ----------------------+-------------------------------+----------+--------+-----------+------------------------------------------------- public.test_gsloader | 2023-02-09 09:20:33.646843-05 | STDIN | 1 | | invalid byte sequence for encoding "UTF8": 0xb4 (1 row) //如上例子对于loader对应的文件，查找数据文本第一行找出源数据 - NULLIF使用用例
// 建表 CREATE TABLE gsloader_test_nullif( col1 varchar2(100) not null enable, col2 number(5,0) not null enable, col3 varchar2(200) not null enable, col4 varchar2(34) not null enable, col5 varchar2(750), col6 number(20,0), col7 varchar2(4000), col8 varchar2(200) ); // 数据文件 test.csv 6007 17060072021-09-0360070001102010000000230 1 600700010000218 0 1 1 229465 3 6007 17060072021-09-0360070001102010000000299 1 600700010000282 0 1 1 230467 3 6007 17060072021-09-0360070001102010000000242 1 600700010000255 0 1 1 226400 3 6007 17060072021-09-0360070001102010000000202 1 600700010000288 0 1 1 219107 3 6007 17060072021-09-0360070001102010000000294 1 600700010000243 0 1 1 204404 3 6007 17060072021-09-0360070001102010000000217 1 600700010000270 0 1 1 226644 3 // 控制文件 test.ctl LOAD DATA CHARACTERSET UTF8 TRUNCATE INTO TABLE gsloader_test_nullif TRAILING NULLCOLS (COL1 POSITION(1:10) CHAR NULLIF (COL1 = BLANKS), COL2 POSITION(11:14) CHAR NULLIF (COL2 = BLANKS), COL3 POSITION(21:30) CHAR NULLIF (COL3 = BLANKS), COL4 POSITION(31:40) CHAR NULLIF (COL4 = BLANKS), COL5 sysdate, COL6, COL7, COL8 POSITION(71:80) CHAR NULLIF (COL8 = BLANKS)) // 执行导入 GS_LOADER -p xxx host=xxx control=test.ctl data=test.csv -d testdb -W xxx // 导入结果：导入成功 loader=# SELECT * FROM gsloader_test_nullif; col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 ------------+------+------------+------------+---------------------+------+------+----------- 6007 17060 | 720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 | | | 010000218 6007 17060 | 720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 | | | 010000282 6007 17060 | 720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 | | | 010000255 6007 17060 | 720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 | | | 010000288 6007 17060 | 720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 | | | 010000243 6007 17060 | 720 | 0360070001 | 1020100000 | 2023-05-17 20:32:03 | | | 010000270 (6 rows)从导入表中的数据可以看出在使用NULLIF关键字后，除指定NULLIF运算和sysdate运算的列执行导入操作后导入字段正常，其余未指定运算的列表现为导入字段为空。
- 列表达式
({ column_name [ data_type ] [ AS transform_expr ] } [, ...])其中data_type指定该列在表达式参数中的数据类型；transform_expr为目标表达式，返回与表中目标列数据类型一致的结果值。
示例：
- ctl文件中不指定列类型，源数据不满足表中列限制（数据类型限制、数据长度限制）。
// 建表 create table t_test(id int, text varchar(5)); // 数据文件 test.csv addf2,bbbbaaa,20220907, // 控制文件 test.ctl Load Data TRUNCATE INTO TABLE t_test fields terminated by ',' TRAILING NULLCOLS( id "length(trim(:id))", text "replace(trim(:text),'bbbb','aa')" ) // guc_param file set a_format_copy_version='s1'; // 执行导入 gs_loader -p xxx host=xxx control=test.ctl data=test.csv -d testdb -W xxx guc_param=test_guc.txt // 导入结果：导入成功 select * from t_test; id | text ----+------- 5 | aaaaa (1 row)
- ctl文件中不指定列类型，隐式类型转换（涉及隐式类型转换，建议加上兼容性参数）。
// 建表 create table test(mes int, mes1 text, mes2 float8, mes3 timestamp with time zone, mes4 INTEGER); // 数据文件 cat load_support_transform.data 1,mmoo,12.6789,Thu Jan 01 15:04:28 1970 PST,32767 2,yyds,180.883,Thu Jun 21 19:00:00 2012 PDT,32768 // 控制文件 cat load_support_transform.ctl Load Data TRUNCATE INTO TABLE test fields terminated by ',' TRAILING NULLCOLS( mes, mes1 "mes1 || mes2", mes2 "mes2 + 1", mes3 "date_trunc('year', mes3)", mes4 ) // guc_param file cat test_guc.txt set a_format_copy_version='s1'; set a_format_dev_version='s2'; set a_format_version='10c'; //执行导入 gs_loader -p xxx host=xxx control=load_support_transform.ctl data=load_support_transform.data -d testdb -W xxx guc_param=test_guc.txt // 导入结果：导入成功 select * from test; mes | mes1 | mes2 | mes3 | mes4 -----+-------------+---------+------------------------+------- 1 | mmoo12.6789 | 13.6789 | 1970-01-01 00:00:00+08 | 32767 2 | yyds180.883 | 181.883 | 2012-01-01 00:00:00+08 | 32768
- ctl文件中不指定列类型，源数据不满足表中列限制（数据类型限制、数据长度限制）。
- 数据类型
对应控制文件中的column_type [external]，在加载数据时，根据不同的数据类型对数据进行处理。gs_loader中可以将数据类型分为普通数据类型和特殊数据类型。
- 普通数据类型
- CHAR [(length)]：
按照字段分隔符读取数据，并转换使用CHAR类型来保存值。length表示单条数据的最大长度，以字节为单位，通常一个字符占用一个字节，并且可以缺省，分为以下几种场景：
- 缺省对length长度的声明时，length的值会根据POSITION的声明来继承最大长度值。
- 声明了length的长度，则它会覆盖POSITION中对于最大长度的声明。
- 缺省了length的声明，同时也缺省了POSITION的声明，length的长度会根据分隔符间长度进行设置。
- 对于长度声明的优先级：length > POSITION > 分隔符。
- 缺省length，POSITION，分隔符的声明时，length的默认长度为1。
- 如果实际数据长度超过了length声明的最大长度，会报错。
- INTEGER external [(length)]：
- FLOAT external [(length)]：
- DECIMAL external (length)：
- TIMESTAMP：
- DATE：
- DATE external：
- SYSDATE：
- CHAR [(length)]：
- 特殊数据类型
- SMALLINT：
无视字段分隔符读取两个字节长度的字符，按小端存储逻辑保存，然后将每个字符解析成十六进制ASCII码值，最后将整体转换为十进制数来保存值。
示例：
// 建表 create table t_spec(col1 varchar(10), col2 varchar(10)); // 数据文件 cat t_spec.txt 1234,5678, // 控制文件 cat t_spec.ctl Load Data TRUNCATE INTO TABLE t_spec fields terminated by ',' TRAILING NULLCOLS( col1 position(2:6) integer, col2 position(5:8) smallint ) // guc_param file cat test_guc.txt set a_format_copy_version='s1'; set a_format_dev_version='s2'; set a_format_version='10c'; // 执行导入 gs_loader -p xxx host=xxx control=t_spec.ctl data=t_spec.txt -d testdb -W xxx guc_param=test_guc.txt // 导入结果：导入成功 select * from t_spec; col1 | col2 -----------+------- 741618482 | 13612 (1 row)
- RAW：
会把每个字符解析成ASCII码值保存，转义字符“\”不执行转义操作。
限制：RAW不能使用分隔符。
示例：
// 建表 create table t_raw(col raw(50)); // 数据文件 cat t_raw.txt 12\n\x78!<~?'k^(%s)>/c[$50] // 控制文件 cat t_raw.ctl Load Data TRUNCATE INTO TABLE t_raw TRAILING NULLCOLS( col position(1:50) raw ) // guc_param file cat test_guc.txt set a_format_copy_version='s1'; set a_format_dev_version='s2'; set a_format_version='10c'; // 执行导入 gs_loader -p xxx host=xxx control=t_raw.ctl data=t_raw.txt -d testdb -W xxx guc_param=test_guc.txt // 导入结果：导入成功 select * from t_raw; col -------------------------------------------------------- 31325C6E5C783738213C7E3F276B5E282573293E2F635B2435305D (1 row)
- 在多列导入场景中，不指定GUC参数时，部分position与分隔符不能同时使用。
- 在多列导入场景中，指定GUC参数时，不支持部分列使用POSITION运算。
- 在多列导入场景中，普通数据类型与特殊数据类型混用时，需全部指定POSITION。
- 指定数据类型导入时，包含普通数据类型需要通过guc_param设置a_format_copy_version参数，包含特殊数据类型则需要通过guc_param设置a_format_copy_version, a_format_dev_version及a_format_version参数。
- 列表达式涉及到系统函数时，需要根据对应功能通过guc_param设置合适的a_format_dev_version及a_format_version参数。
- 带length数据类型的使用，length需指定为大于0的整数；RAW数据类型作为特殊类型，RAW(length)的使用区别于普通类型的使用，如INTEGER EXTERNAL(length)的使用，当不指定position时，INTEGER EXTERNAL(length)表现为，当length小于文本文件 (.csv/.txt等)中对应列数据长度时报错；当length大于文本文件 (.txt)中对应列数据长度时，输出INTEGER EXTERNAL类型的结果。RAW(length)当不指定position时表现为读取length个字符。
- POSITION使用时，POSITION(start:end)，start需设置为大于0的整数，且end值应大于等于start的值。
- 并发导入时，若多个gs_loader的discard文件名或bad文件名指向同一目录同名文件，则后一个执行的gs_loader会中止报错。若前一个已经导入完成，则文件被覆盖
ERROR: An error occurred. Please check logfile.
log文件中
…lock failed: Resource temporarily unavailable…
- 控制文件中对于字段值的部分若不为空且不使用本字段内容，则不占用数据文件的位置。
Load Data TRUNCATE INTO TABLE gsloader fields terminated by ',' TRAILING NULLCOLS( id "trim(:id)", text "to_char(SYSDATE,'yyyymmdd')", gmt_create "trim(:gmt_create)", create_str "trim(:create_str)" )
数据文件如下：
11,HELLO,33,
导入结果为：
loader=# select * from gsloader; id | text | gmt_create | create_str ----+--------+---------------------+------------ 11 | 2023-02-08 16:00:54 | HELLO | 33
- SMALLINT：
- 普通数据类型
