时间戳

首页 > 技术文章 > Sys_Bulkload 工具使用

Sys_Bulkload 工具使用

kingbase 2021-11-20 14:19 原文

一、介绍

sys_bulkload是KingbaseES提供的快速加载数据的命令行工具。用户使用sys_bulkload工具能够把一定格式的文本数据简单、快速的加载到KingbaseES数据库中，或将KingbaseES数据库中的数据快速导出到CSV文件中。

使用前需要用户手动创建sys_bulkload插件，需要说明的是sys_bulkload工具是随数据库版本一同发布的，使用时请使用对应发布的数据库版本，其他数据版本则不保证能正常工作。

create extension sys_bulkload;

二、架构图

sys_bulkload主要包括两个模块：reader和writer。reader负责读取文件、解析tuple，writer负责把解析出的tuple写入输出源中。

三、sys_bulkload参数

sys_bulkload可以通过短选项、长选项、配置文件三种方式来指定配置加载选项。由于加载的选项比较多，并且一些加载选项的信息比较固定和反复使用，可以把这些加载选项配置在配置文件中，命令行直接指定配置文件的路径。配置加载选项规则如下:

1． 启动参数的短选项和长选项是等价的。

2． 在启动参数选项中仍然可以指定配置文件中配置的加载选项，其作用主要是临时替换配置文件的选项，以避免去修改配置文件中的选项，配置文件和启动参数同时指定则以启动参数指定为准。

3． 如果使用启动参数则需要注意input，output，logfile，parse_badfile，duplicate_badfile不能多次指定，否则报错：specified only once。其他选项可以多次指定，并且以后指定的选项为准。

 

四、参数选项说明

1、必填参数:

1.TYPE = CSV | TEXT | BINARY | DB  加载的数据源的类型

CSV    从CSV格式的文本文件加载数据

TEXT       从TEXT格式的文本文件加载数据

BINARY 从二进制格式的文件加载数据

DB     从数据库的表中导出数据

 

2.INPUT = PATH | [schema_name.] table_name 需要导入的数据文件路径或者导出数据的源表

PATH 需要导入的数据源文件路径。如果是相对路径，在控制文件中指定时，它将与控制文件相对；当在命令行参数中指定时，相对于当前工作目录。KingbaseES服务器的用户必须具有该文件的读取权限。在“TYPE = CSV | TEXT | BINARY”时可用。

[schema_name.] table_name 需要导出数据的表名。仅在“TYPE = DB”时可用。

 

3.OUTPUT = [schema_name.] table_name | PATH 指定将数据导入的目标表或者目标文件。

[schema_name.] table_name 导入数据的表名。在“ TYPE = CSV | TEXT | BINARY ”时可用。

PATH 导出数据的文件路径。如果是相对路径，在控制文件中指定时，它将与控制文件相对；当在命令行参数中指定时，相对于当前工作目录。KingbaseES服务器的用户必须具有该文件的读取权限。仅在“TYPE = DB”时可用。

 

4.LOGFILE = PATH

指定一个文件记录日志。如果指定为相对路径，则指定规则与INPUT相同。默认值为$KINGBASE_DATA/sys_bulkload/<timestamp>_<dbname>_<schema>_<table>.log

 

2、非必填参数

1.WRITER = BUFFERED | CSV_FILE 指定数据的加载方式，默认值为 BUFFERED。

BUFFERED 通过共享缓冲区将数据加载到表中。使用共享缓冲区编写WAL日志，并可使用KingbaseES的WAL日志进行恢复。

CSV_FILE 当数据的加载方式指定为CSV_FILE时，表示将数据库中的数据导出到CSV格式的文本文件中，该参数一般与“TYPE = DB”配合使用。

2.LIMIT = n

LIMIT只在导入数据文件时有效，加载n行即停止加载。默认值为最大的64位整数（即(2^64)/2-1 = 9223372036854775807），当n为0或默认值时表示不限制加载行数。

3.ENCODING = encoding

指定输入数据的编码格式，检查指定的编码格式是否合法。默认不检查。若有需要转化输入文件的格式为数据库的编码格式。如果可以确保输入文件格式与数据库格式一致，不指定该选项，会有助于加载速度的提高，因为会忽略字符集的检查和转化。配置文件中ENCODING选项与数据库编码选项的转化关系详见 表 2-1 。

4.CHECK_CONSTRAINTS = YES | NO

指定加载时是否进行约束检查（只检查check约束），默认为NO。 注： 当前不支持分区约束检查，所以直接插入分区时需要保证数据正确。

5.PARSE_ERRORS = n

允许出现的错误次数。若错误次数超过该设置值，则快速加载退出运行。-1表示不限制错误个数，0为默认值，表示不允许错误，其他表示允许的错误次数。

6.FILTER = [schema_name.] function_name[(arg_value，...)]

只在导入数据文件时有效，指定过滤函数用来转换输入文件的每行，如果函数名在数据库中唯一，可以忽略函数的参数类型定义。如果该选项未指定，输入数据将直接被解析到目标表中。

7.DUPLICATE_ERRORS = n

允许违反唯一约束的忽略的元组个数。冲突的元组将从表中删除并被记录在重复失败的文件中。如果大于等于重复记录数，记录会被回滚。默认值为0，表示不允许重复记录数，-1表示忽略所有错误。BUFFERED模式不支持该参数，指定时会被忽略。

8.ON_DUPLICATE_KEEP = NEW | OLD

执行元组如何处理违反唯一约束。被删除的元组会被记录在出错文件中。设置了该选项，同样需要设置DUPLOCATE_ERRORS大于0。默认值为NEW。

NEW 采用输入文件中的最新一条记录的数据替换表中原有的数据。

OLD 保持表中原有数据，删除输入文件中的元组。

9.PARSE_BADFILE = PATH

指定一个文件路径（若指定路径的文件不存在则自动创建一个新文件），默认值与bulkload工具同目录，文件名为导入数据文件的文件名+”.bad”后缀的文件。保存数据文件中解析失败的数据行。若在数据文件解析的过程中解析失败，则该数据行追加记录到该文件。 

10.DUPLICATE_BADFILE = PATH

指定一个文件路径（若指定路径的文件不存在则自动创建一个新文件），默认值与bulkload工具同目录，文件名为导入数据的文件名+”.dupbad”后缀的文件。若存在不能被写入到数据库的元组，则该元组对应的数据文件中的行追加记录到该文件。例如在数据导入过程中元组违背了约束（唯一，主键，非空，check）原则，则该元组不能写入+”.badf”后缀的文件路径。若在数据文件解析的过程中解析失败，则该数据行追加记录到该文件。

11.TRUNCATE = YES | NO

是否删除所有目标表中的数据，默认值为NO。多进程并行和TYPE为DB时不支持该选项。

12.VERBOSE = YES | NO

出错的元组是否写入到服务器日志中，默认值为NO。

13.DELIMITER = delimiter_character

间隔符，数据文件中列与列的间隔符，为单个字符，可以为任何可视化字符。默认值为逗号( , )。当需要一个TAB字符作为间隔符时，用双引号包裹TAB字符，如 DELIMITER = "" 。

14.QUOTE = quote_character

QUOTE在文件格式为CSV时有效，详情参考COPY语句。默认值为双引号( " )。

15.ESCAPE = escape_character

ESCAPE在文件格式为CSV时有效，详情参考COPY语句。默认值为反斜杠( \ )。

16.REINDEX = YES | NO 导入数据后是否重建索引。默认值为NO。

17.SKIP_LAST_EMPTY_VALUE = YES | NO

只对CSV格式有用，最后一列数据为空，是否把它当成一列数据，还是只是当分隔符。默认值为NO。用TPCH测试时生成的CSV文件在行末尾会加一个分隔符，指定该选项为YES，忽略最后一个分隔符。

18.SKIP = n

SKIP只在导入TEXT和CSV格式的数据文件时有效，TEXT和CSV格式的数据文件以行为单位进行导入，该选项可以设置跳过多少行数据，这些数据不导入数据库。默认值为0。

19.NULL = null_string 指定表示一个空值的字符串。默认值是一个没有引号的空字符串。

20.FORCE_NOT_NULL = column_name

该选项强制要求指定的列不为NULL值，默认情况下将空字符串按照NULL值处理，如果指定了该值，则空字符串不再按照NULL值处理，而是按照零长字符串处理。该选项不能与FILTER一起使用。

21.TRACKING_INTERVAL = n

指定导入过程中的时间间隔（单位为：秒），客户端反馈导入状况，防止在导入过程中因异常卡死，但用户不知道。默认为0，表示不反馈导入信息，其它非0整数为反馈时间间隔。

22.PROCESSOR_COUNT = n

指定服务器并行处理的进程数，具体参数值可根据用户服务器的CPU个数指定。默认值为1。TYPE为BIANRY方式不支持该参数，TYPE为DB方式时该参数恒为1。

processor_count > 1 only support in csv or db mode

23.ASYNC_WRITE = YES | NO

指定服务器的写文件是否独立的进程，默认值为NO。当WRITE为CSV_FILE时，如果PROCESSOR_COUNT大于1，则无论是否指定ASYNC_WRITE，其值始终为YES。

24.DUMP_PARAMS = YES | NO 是否将配置参数信息导入到日志文件中。默认值为YES。

25.SPECIFY_COLUMN = COLOUMNAME [FILTER | LLS],...

将数据加载到指定的列上, COLUMNAME为具体的列名; 列名之前使用“,”分隔, 如果列名包含逗号则需要使用单引号将列名括起来, 如果列名包含单引号则需要使用'\'进行转义, 例如列名为i,'d则写为'i,\'d'。FILTER表示跳过对应的列, LLS表示对应字段加载方式使用LOB location specify方式加载。

 

LLS方式，加载的内容不是文本本身所指的内容而是字段指定文件中的内容，对应的加载内容格式如下：

filename:offset:length

filename：为待加载的数据文件名，支持zip文件

offset：文件的偏移量

length：加载的数据量（字节数）

 

注意：

如果加载的数据文件为zip等压缩文件，length长度一般为整个文件大小，否则会导致加载数据不完整，导出后无法正常解压缩。另外如果加载的对象为CLOB，zip等压缩文件是含有\0可能加载过程中会造成数据截断，所以CLOB不建议采用zip压缩文件进行加载。

 

26.LOB_DIR = PATH LLS方式加载时，对应filename所对应文件所在的路径需要通过LOB_DIR指定。

27.CLIENT_LOB = TRUE | FALSE 远端LLS方式加载时，需要设置CLIENT_LOB = TRUE 默认值为FALSE。

28.SCHEMA_SAMEAS_USER = TRUE | FALSE

导入时INPUT参数可以指定[schema_name.] table_name， 如果对应用户只有一个与用户名相同的SCHEMA则可以设置SCHEMA_SAMEAS_USER=TRUE,这样可以不填写schema_name，bulkload会自动补全为username.table_name 这样做是为了兼容oracle 

29.REMOTE_FILE = PATH 远端加载导入时，INPUT填写为stdin，待加载的数据文件可以通过REMOTE_FILE指定。

30.LIMIT 在导入CSV文件并且已知文件行数的情况下，设置LIMIT参数为导入文件的行数可提升导入速度。

31.REINDEX 若导入数据不需要建立索引，则将REINDEX参数设置为NO(默认值)，可提升导入速度

32.PROCESSOR_COUNT 使用BUFFERED方式导入，可以根据当前物理环境合理配置PROCESSOR_COUNT参数优化导入速度。

配置文件中的选项不区分大小写，每个选项占用一行，选项和选项值之间通过等号进行连接，选项的值可以有引号也可以没有引号。如果字符串中有空格、等号等特殊值，则必须加引号。“#”表示注释该行后面的配置。具体格式如下：

PROCESSOR_COUNT =4
DELIMITER = ","                       # Delimiter
QUOTE = "\""                          # Quoting character
LOGFILE =/home/bulkload.log
DUPLICATE_BADFIL=/home/bad.dat
TYPE=CSV
INPUT=/home/data.csv
OUTPUT=test_table

 

五、实例

1、导入数据示例

1、创建导入数据表

create table test(id int primary key, info text, crt_time timestamp);

数据文件示例：将下列数据以 test.csv 为文件名保存到KingbaseES服务器所在目录

1,29b35ff06c949e7e442c929e1df86396,2017-10-08 10:52:47.746062
2,06fde814525395de5ab85f6d92b04e87,2017-10-08 10:52:47.746573
3,c93f02e8677c9cd7c906c6ad5dbd450e,2017-10-08 10:52:47.746627
4,6541700070ae3d051f965fcef43baf45,2017-10-08 10:52:47.746835
5,3d7e7246016acaa842526b6614d0edf5,2017-10-08 10:52:47.746869
6,1d1ae5a03ef0bad3bc14cd5449ba0985,2017-10-08 10:52:47.746894
7,7745c57c54b97656bec80a502ec13ec7,2017-10-08 10:52:47.746918
8,3c377131f6ef82c3284dc77a3b4ffdf7,2017-10-08 10:52:47.746942
9,5ef98d40aeeadf65eb1f0d7fd86ed585,2017-10-08 10:52:47.746968
10,312c0a0188da9e34fe45aa19d0d07427,2017-10-08 10:52:47.746993　

2、导入数据

以 BUFFERED 方式导入 TEXT 文件

配置文件示例（以test.ctl为名保存到服务器所在目录，也可自行指定其他目录。)

output = test
INPUT = /home/kingbase/test.csv
TYPE = CSV
SKIP = 2
LIMIT = 5
WRITER = BUFFERED
PROCESSOR_COUNT = 3

　　

3、使用配置文件导入命令示例

sys_bulkload -h localhost -d TEST /home/kingbase/test.ctl -U SYSTEM -W 123

使用配置文件导入结果

 

导入成功提示信息：

   NOTICE: BULK LOAD START
    NOTICE: BULK LOAD END
        2 Rows skipped.
        5 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.
        log path: /opt/Kingbase/ES/V8/data/sys_bulkload/20211111161310_test_public_test.log
        parse error path: /opt/Kingbase/ES/V8/data/sys_bulkload/20211111161310_test_public_test.prs.csv
        duplicate error path: /opt/Kingbase/ES/V8/data/sys_bulkload/20211111161310_test_public_test.dup.csv

　　

2、导出数据命令示例

 

sys_bulkload -d TEST -i TEST -O test_out.csv -o "TYPE=DB" -o

"WRITER=CSV_FILE" -o "DELIMITER=|" -h localhost -U SYSTEM -W 123

导出数据结果

 

导出成功提示信息：

    NOTICE: BULK LOAD START
    NOTICE: BULK LOAD END
        0 Rows skipped.
        10 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

　　

导出结果查询：

cat test_out.csv
    1|29b35ff06c949e7e442c929e1df86396|2017-10-08 10:52:47.746062
    2|06fde814525395de5ab85f6d92b04e87|2017-10-08 10:52:47.746573
    3|c93f02e8677c9cd7c906c6ad5dbd450e|2017-10-08 10:52:47.746627
    4|6541700070ae3d051f965fcef43baf45|2017-10-08 10:52:47.746835
    5|3d7e7246016acaa842526b6614d0edf5|2017-10-08 10:52:47.746869
    6|1d1ae5a03ef0bad3bc14cd5449ba0985|2017-10-08 10:52:47.746894
    7|7745c57c54b97656bec80a502ec13ec7|2017-10-08 10:52:47.746918
    8|3c377131f6ef82c3284dc77a3b4ffdf7|2017-10-08 10:52:47.746942
    9|5ef98d40aeeadf65eb1f0d7fd86ed585|2017-10-08 10:52:47.746968
   10|312c0a0188da9e34fe45aa19d0d07427|2017-10-08 10:52:47.746993

　　

 

推荐阅读