控制文件
-
语法说明:
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模式下,被双引号包含的换行符被视为字段数据的一部分。
- CSV模式下,设置了GUC参数a_format_copy_version为's1'时,会跳过字段开头空格。并且当某个字段第一个非空格字符不是enclosed字符时,忽略enclosed设置。当未匹配到关闭enclosed字符,先匹配到行末时,会进行报错。
- CSV模式下,在不打开0字符GUC开关support_zero_characters时,如果使用了compatible_nul或者compatible_illegal_chars参数对0x00字符进行兼容,由于0x00转换0x20的行为发生在跳过开头空格的行为之前,因此字段开头的0x00字符会被当作0x20处理被删除掉。
-
table_name
表的名称(可以有模式修饰)。
取值范围:已存在的表名。
-
TERMINATED [BY] { 'string' }
在文件中分隔各个字段的字符串,分隔符最大长度不超过10个字节。
取值范围:不允许包含\.abcdefghijklmnopqrstuvwxyz0123456789中的任何一个字符。不支持将nul字符设置为分隔符。
缺省值:在文本模式下,缺省是水平制表符,在CSV模式下是一个逗号。
注意: 开启nul字符兼容,即compatible_nul=true,如果指定分隔符为' '空格字符(0x20)时需要注意,所判断的分隔符为数据文件中已存在的空格字符,并非nul字符转换而来的空格字符。
-
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之间的字符串,或者根据列名进行行过滤。
取值范围:字符串。
说明:
- 当GUC参数enable_copy_when_filler=on(默认)时,支持根据FILLER类型列进行过滤。当GUC参数enable_copy_when_filler=off时,则不支持。
- WHEN条件后的常量字符串中不支持'\0'、'\r'等特殊字符。
-
POSITION ({ start:end })
对列进行处理,根据start到end范围获取对应字符串。
-
“sql_string”
对列进行处理,列表达式,根据表达式计算列的取值。详见•列表达式。
取值范围:字符串。
-
FILLER
对列进行处理,如果出现FILLER,则这个字段跳过。
说明: 当前不支持FILLER与POSITION **({ start:end })**同时使用。
-
column_type [external]
在导入数据时,根据不同的数据类型对数据进行处理。详见•数据类型。
-
CONSTANT
对列进行处理,将插入的对应字段设置为常量。
取值范围:字符串。
-
SEQUENCE ( { COUNT | MAX | integer } [, incr] )
对列进行处理,生成对应的序列值。
- COUNT:表示根据表中数据的行数开始计算。
- MAX:表示根据表中这一列的最大值开始计算。
- integer:表示从用户指定的值开始计算。
- incr:表示每次递增多少。
-
NULLIF
在设置a_format_copy_version等于's1'时,当指定列的数据只包含空白字符时返回NULL,否则返回trim(COL),等价于列表达式 “nullif(trim(COL), '')"。
在设置a_format_copy_version不等于's1'时,对列进行处理,在多行导入场景中,若列名后未指定sysdate、constant、position、列表达式等运算时,执行导入操作,表现为未指定NULLIF关键字的列字段设置为空。
当前只支持COL POSITION() CHAR NULLIF (COL=BLANKS)语法。具体使用详见•NULLIF使用用例。
注意:
- 不支持OPTIONS、INFILE、BADFILE,仅在特定场景下不报语法错误。
- gs_loader使用bad文件来记录出错数据,如果设置guc参数enable_copy_error_log开启错误表,该数据来自错误表的rawrecord字段,由于错误表对于以某种编码无法读起的错误不记录rawrecord,因此bad文件中遇到此情况时记录空行。
- gs_loader在设置guc参数a_format_load_with_constraints_violation开启支持约束冲突不回滚场景时,如果表带有BEFORE/AFTER ROW INSERT触发器,则每次提交行数不能超过1000万行。
- gs_loader在设置guc参数a_format_load_with_constraints_violation开启支持约束冲突不回滚场景时,不支持语句级触发器。
-
-
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对应的文件,查找数据文本第一行找出源数据
-
// 建表 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运算的列执行导入操作后导入字段正常,其余未指定运算的列表现为导入字段为空。
-
gs_loader支持对指定列进行表达式转换和场景扩展:
({ 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
-
-
对应控制文件中的column_type [external],在加载数据时,根据不同的数据类型对数据进行处理。gs_loader中可以将数据类型分为普通数据类型和特殊数据类型。
-
普通数据类型
-
CHAR [(length)]:
按照字段分隔符读取数据,并转换使用CHAR类型来保存值。length表示单条数据的最大长度,以字节为单位,通常一个字符占用一个字节,并且可以缺省,分为以下几种场景:
- 缺省对length长度的声明时,length的值会根据POSITION的声明来继承最大长度值。
- 声明了length的长度,则它会覆盖POSITION中对于最大长度的声明。
- 缺省了length的声明,同时也缺省了POSITION的声明,length的长度会根据分隔符间长度进行设置。
- 对于长度声明的优先级:length > POSITION > 分隔符。
- 缺省length,POSITION,分隔符的声明时,会从当前位置读到行结束符为止。
- 如果实际数据长度超过了length声明的最大长度,会报错。
-
INTEGER external [(length)]:
按照字段分隔符读取数据,并转换使用INTEGER类型来保存值。length的使用规则与CHAR类型中相同。
-
FLOAT external [(length)]:
按照字段分隔符读取数据,并转换使用FLOAT类型来保存值。length的使用规则与CHAR类型中相同。
-
DECIMAL external (length):
按照字段分隔符读取数据,并转换使用DECIMAL类型来保存值。length的使用规则与CHAR类型中相同
-
TIMESTAMP:
按照字段分隔符读取数据,并转换使用TIMESTAMP类型来保存值。
-
DATE:
按照字段分隔符读取数据,并转换使用DATE类型来保存值。
-
DATE external:
按照字段分隔符读取数据,并转换使用DATE类型来保存值。
-
SYSDATE:
在数据库执行对应的插入时,取系统时间。该字段对应对应的值无法被引用使用,被引用使用的内容为SYSDATE字符串。
-
-
特殊数据类型
-
INTEGER:
无视字段分隔符读取四个字节长度的字符,按小端存储逻辑保存,然后将每个字符解析成十六进制ASCII码值,最后将整体转换为十进制数来保存值。
-
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与分隔符不能同时使用。
- 在多列导入场景中,SYSDATE和CONSTANT运算不能和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的值。
- 指定POSITION时,在处理字段内容时不会省略尾部的空格;不指定POSITION时,处理字段内容时会省略尾部的空格,如果需要保留空格,需要在guc_param所指定的文件中,已设置好a_format_version的前提下,添加 set behavior_compat_options='char_coerce_compat'; 详细内容请参考管理员指南中behavior_compat_options的设置。
- 并发导入时,若多个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,22你好,33,
导入结果为:
loader=# select * from gsloader; id | text | gmt_create | create_str ----+--------+---------------------+------------ 11 | 2023-02-08 16:00:54 | 22你好 | 33
-
-