文档首页> 云数据库 GaussDB> 主备版> 客户端工具> gs_loader
更新时间：2023-10-20 GMT+08:00
查看PDF

gs_loader

概述

gs_loader工具用于进行数据导入。gs_loader将控制文件支持的语法转换为\COPY语法，然后利用已有的\COPY功能，做主要数据导入工作，同时gs_loader将\COPY结果记录到日志中。

日志等级配置

设置日志级别，可以供开发者查看。设置后会在控制台打印工具运行的相应信息。

export gs_loader_log_level=debug
export gs_loader_log_level=info
export gs_loader_log_level=warning
export gs_loader_log_level=error

使用权限

使用者可以是数据库普通用户或管理员用户。当使用者为普通用的时候，需要管理员用户对普通用户赋权。管理员账户可以直接使用。

创建用户。

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 To load_user;
GRANT ALL PRIVILEGES ON  public.gs_copy_summary To load_user;

使用环境

由用户自己将工具路径添加到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.
            |

使用指导

  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 To load_user;
GRANT ALL PRIVILEGES ON  public.gs_copy_summary To load_user;

  2. 创建表和控制文件，准备数据文件。

    创建表loader_tbl。

    1
2
3
4
5
6
7
    		 
    CREATE TABLE  loader_tbl
(
    ID   NUMBER,
    NAME VARCHAR2(20),
    CON  VARCHAR2(20),
    DT   DATE
);

    创建控制文件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
)

    创建guc参数文件guc.txt。

    1
    		 
    set a_format_copy_version='s1';

    创建数据文件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

  3. 进行导入。

    执行导入前，先确认gs_loader工具有可执行权限。确保当前路径有文件写入权限（gs_loader在处理过程中会生成一些临时文件，导入完成后自动删除）。

    1
    		 
    gs_loader control=loader.ctl data=data.csv db=testdb bad=loader.bad guc_param=guc.txt 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语法以及详细情况，pgxc_copy_error_log记录错误数据以及详细情况。如需删除表中的数据，可以对上述表执行truncate或者delete操作。

参数说明

表1 gs_loader参数说明

参数

参数说明

参数类型 : 取值范围

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

仅做语法兼容不实现功能。

-

参数均为小写，不支持大写，同时兼容gsql登录方式：-p端口号，-h主机，-d数据库，-U用户名，-W密码方式。

控制文件

  • 语法说明： 
     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

      字符集。

      取值范围：字符串。

    • INFILE

      当前关键字无效，并在控制文件中需要单独占一行，运行时候会忽略该关键字。需要用户在gs_loader命令行参数中指定对应的数据文件。

    • BADFILE

      当前关键字无效，运行时候会忽略该关键字，如果gs_laoder 命令行参数没有指定badfile,则会根据对应控制文件名称生成对应的badfile文件。

    • OPTIONS

      其中只有skip功能生效，skip=n为导入时跳过前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语句后面指定导入模式会覆盖在前面指定的导入模式。

    • FIELDS CSV

      标识使用copy的CSV模式。在CSV模式下分隔符缺省值为逗号，引号字符的缺省值为双引号。

    • table_name

      表的名称（可以有模式修饰）。

      取值范围：已存在的表名。

    • TERMINATED [BY] { 'string' }

      在文件中分隔各个字段的字符串，分隔符最大长度不超过10个字节。

      取值范围：不允许包含\.abcdefghijklmnopqrstuvwxyz0123456789中的任何一个字符。

      缺省值：在文本模式下，缺省是水平制表符，在CSV模式下是一个逗号。

    • OPTIONALLY ENCLOSED BY { 'string' }

      CSV格式文件下的引号字符。

      仅在使用FIELDS CSV参数明确说明的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文件中遇到此情况时记录空行。
  • 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运算的列执行导入操作后导入字段正常，其余未指定运算的列表现为导入字段为空。

  • 列表达式

    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的默认长度为1。
        • 如果实际数据长度超过了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与分隔符不能同时使用。
      • 在多列导入场景中，指定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的值。
      • 并发导入时，若discard文件名指向同一目录同名文件，则会后一个执行的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
父主题： 客户端工具