gs_loader导入数据
工具介绍
gs_loader工具用于进行数据导入。gs_loader将控制文件支持的语法转换为\COPY语法,然后利用已有的\COPY功能,做主要数据导入工作,同时gs_loader将\COPY结果记录到日志中。
- gs_loader工具当前不支持M-Compatibility数据库。
- gs_loader工具当前支持PDB。
- gs_loader命令执行后可通过echo $?查看返回码判断执行结果:0表示执行成功且所有数据均已导入,1表示执行失败,2表示执行完成但部分数据未成功导入。
前提条件
- 使用gs_loader前请确保gs_loader版本与gsql版本、数据库版本保持一致。
- 用户在安装部署gs_loader服务以后,将工具路径添加到PATH中。gs_loader支持SSL加密通信,使用方式同gsql方式相同。
- 设置日志级别,可以供开发者查看。设置后会在控制台打印工具运行的相应信息。
export gs_loader_log_level=debug export gs_loader_log_level=info export gs_loader_log_level=warning export gs_loader_log_level=error
权限配置
gs_loader工具的使用场景分为三权分立场景下及非三权分立场景下的使用。使用者可以选择将GUC参数enableSeparationOfDuty设置为on或者off来控制三权分立功能的开启或关闭。
GUC参数enable_copy_error_log是控制是否使用错误表pg_catalog.gs_copy_error_log和日志表pg_catalog.gs_copy_summary_log的参数,默认为off,即不使用错误表以及不使用日志表,错误记录直接记录到gs_loader的bad文件中,日志记录直接记录到gs_loader的log文件中。如果该参数设置为on,则会使用错误表pg_catalog.gs_copy_error_log以及使用日志表pg_catalog.gs_copy_summary_log,将错误记录插入错误表,将日志信息插入日志表,错误表请参见表1,日志表请参见表2。
--开启三权分立功能。
gs_guc set -Z datanode -N all -I all -c "enableSeparationOfDuty = on"
--关闭三权分立功能。
gs_guc set -Z datanode -N all -I all -c "enableSeparationOfDuty = off"
--设置enable_copy_error_log = on时,会使用错误表gs_copy_error_log和日志表gs_copy_summary_log。
gs_guc reload -Z datanode -N all -I all -c "enable_copy_error_log = on" | 列名称 | 类型 | 描述 |
|---|---|---|
| relname | text | 表名称。以模式名.表名形式显示。 |
| begintime | timestamp with time zone | 出现数据格式错误的时间。 |
| filename | text | 出现数据格式错误的数据源文件名。 |
| lineno | bigint | 在数据源文件中,出现数据格式错误的行号。 |
| rawrecord | text | 在数据源文件中,出现数据格式错误的原始记录。 |
| detail | text | 详细错误信息。 |
| custom_id | text | copy中copy_custom_id输入的取值。 |
| 列名称 | 类型 | 描述 |
|---|---|---|
| relname | text | 本次导入的目标表名。 |
| begintime | timestamp with time zone | 导入任务开始时间。 |
| endtime | timestamp with time zone | 导入任务结束时间。 |
| id | bigint | 本次导入的事务id。 |
| pid | bigint | 本次导入工作线程id。 |
| readrows | bigint | 本次导入任务共计读取的数据行数。 |
| skiprows | bigint | 本次导入任务共计跳过的数据行数。 |
| loadrows | bigint | 本次导入任务成功导入的数据行数。 |
| errorrows | bigint | 本次导入任务的错误数据行数。 |
| whenrows | bigint | 本次导入任务违反when过滤条件的数据行数。 |
| allnullrows | bigint | 全部字段都为空的数据行数。 |
| detail | text | 本次导入任务的导入情况总结。主要包括成功导入行数、错误数据行数、违反when条件行数和字段全空行数等。 |
| custom_id | text | copy中copy_custom_id输入的取值。 |
默认场景,不开启三权分立(即enableSeparationOfDuty=off)时,用户可以是数据库普通用户或管理员用户。当用户为普通用户时,需要管理员用户对数据库普通用户赋权。管理员用户可以直接使用。注意:在赋权操作时,需要保证GUC参数allow_create_sysobject打开,否则会禁止管理员对普通用户赋权系统表权限。
- 使用管理员用户创建新的用户:
gaussdb=# CREATE USER load_user WITH PASSWORD '********'; - (可选)给新用户授权pg_catalog.gs_copy_summary_log表:
- gs_loader使用的public.gs_copy_summary变更为pg_catalog.gs_copy_summary_log,原public.gs_copy_summary表停止数据写入。同时为了查询兼容,新增了系统视图pg_catalog.gs_copy_summary去查询pg_catalog.gs_copy_summary_log中的数据。由于public.gs_copy_summary可能与用户表同名,因此需要用户进行识别,确认不是用户表后,将数据迁移到pg_catalog.gs_copy_summary_log表里之后,删除public.gs_copy_summary。由于存在pg_catalog.gs_copy_summary,gsql元命令\d(+)优先找到pg_catalog.gs_copy_summary,而因为是系统视图的缘故不显示,因此列表中找不到gs_copy_summary,但是实际上public.gs_copy_summary是存在的。可以直接通过public.gs_copy_summary引用。
- pg_catalog.gs_copy_summary_log只兼容SELECT查询操作,不兼容DELETE和TRUNCATE清理操作。
- 内核从低版本滚动升级到该版本时,在所有节点升级完成之前禁止使用gs_loader工具依赖系统日志表和系统错误表的能力。
- 如果GUC参数enable_copy_error_log未设置(默认为off)或者设置为off,可跳过此步骤。
gaussdb=# GRANT SELECT,DELETE ON pg_catalog.gs_copy_summary_log To load_user; - (可选)给新用户授权错误表gs_copy_error_log:
- gs_loader使用的public.pgxc_copy_error_log变更为pg_catalog.gs_copy_error_log,原public.pgxc_copy_error_log表停止数据写入。同时为了查询兼容,新增了系统视图pg_catalog.pgxc_copy_error_log去查询pg_catalog.gs_copy_error_log中的数据。由于public.pgxc_copy_error_log可能与用户表同名,因此需要用户进行识别,确认不是用户表后,将数据迁移到pg_catalog.gs_copy_error_log表里之后,删除public.pgxc_copy_error_log。由于存在pg_catalog.pgxc_copy_error_log,gsql元命令\d(+)优先找到pg_catalog.pgxc_copy_error_log,而因为是系统视图的缘故不显示,因此列表中找不到pgxc_copy_error_log,但是实际上public.pgxc_copy_error_log是存在的。可以直接通过public.pgxc_copy_error_log引用。
- pg_catalog.gs_copy_error_log只兼容SELECT查询操作,不兼容DELETE和TRUNCATE清理操作。
- 内核从低版本滚动升级到该版本时,在所有节点升级完成之前禁止使用gs_loader工具依赖系统错误表的能力。
- 如果GUC参数enable_copy_error_log未设置(默认为off)或者设置为off,可跳过此步骤。
gaussdb=# GRANT SELECT,DELETE ON pg_catalog.gs_copy_error_log To load_user;
开启三权分立(即enableSeparationOfDuty=on)时,使用者可以是数据库普通用户或管理员用户。使用前需要到各自的同名schema下创建pgxc_copy_error_log表以及gs_copy_summary并添加索引,不需要再对这两张表进行授权。
因为pg_catalog模式下始终存在pgxc_copy_error_log以及gs_copy_summary两张表,而pg_catalog模式在search_path中优先级高于用户模式,因此在三权分立场景下无法直接通过gsql元命令\d(+)查询到,使用这两张表需要显式指定用户模式。
pgxc_copy_error_log的创建详见GS_COPY_ERROR_LOG。
gs_copy_summary的创建详见GS_COPY_SUMMARY_LOG。
- 使用初始用户创建新用户:
gaussdb=# CREATE USER load_user WITH PASSWORD '********'; - 从初始用户切换为新用户:
gaussdb=# \c - load_user - (可选)创建gs_copy_summary表并添加索引:
如果GUC参数enable_copy_error_log未设置(默认为off)或者设置为off,则无需使用日志表,无需创建。否则需要创建该日志表。
gaussdb=# 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); gaussdb=# CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);
- (可选)创建pgxc_copy_error_log表并添加索引:
如果GUC参数enable_copy_error_log未设置(默认为off)或者设置为off,则无需使用错误表,无需创建。否则需要创建该错误表。
--创建pgxc_copy_error_log表。 gaussdb=# CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text); --创建索引。 gaussdb=# CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
命令格式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [OPTIONS(name=value)] LOAD [ DATA ] [CHARACTERSET char_set_name] [INFILE [directory_path] [filename ] ] [BADFILE [directory_path] [filename ] ] ["STR {'end_of_line' | x'end_of_line'}"] [OPTIONS(name=value)] [{ INSERT | APPEND | REPLACE | TRUNCATE }] INTO TABLE table_name [{ INSERT | APPEND | REPLACE | TRUNCATE }] [FIELDS CSV] [FIELDS 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)] [, ...] )] |
- 其中支持的OPTIONS(name=value)为:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
BAD_PATH = 'string' BINARY = {TRUE | FALSE} BINDSIZE = n COLUMNARRAYROWS = n COMPATIBLE_ILLEGAL_CHARS = {TRUE | FALSE} COUNT = n DATA = 'string' DATE_CACHE = n DEGREE_OF_PARALLELISM= {degree-num|DEFAULT|AUTO|NONE} DIRECT = {TRUE | FALSE} DISCARD_PATH = 'string' EMPTY_LOBS_ARE_NULL = {TRUE | FALSE} ERRORS = n EXTERNAL_TABLE = {NOT_USED | GENERATE_ONLY | EXECUTE} FILE = 'string' LIMIT = n LOAD = n MULTITHREADING = {TRUE | FALSE} PARALLEL = {TRUE | FALSE} POSITION_KEEP_TRAILING_SPACE = {TRUE | FALSE} READSIZE = n RESUMABLE = {TRUE | FALSE} RESUMABLE_NAME = 'string' RESUMABLE_TIMEOUT = n ROWS = n SDF_PREFIX = 'string' SILENT = ({HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL}, ...) SKIP = n SKIP_INDEX_MAINTENANCE = {TRUE | FALSE} SKIP_UNUSABLE_INDEXES = {TRUE | FALSE} SPLIT = n STREAMSIZE = n TRIM= {LRTRIM|NOTRIM|LTRIM|RTRIM|LDRTRIM}
- OPTIONS参数中,当指定skip=n时,导入时跳过前n条数据;当指定rows=n时,导入n行数据就进行一次提交。
- OPTIONS参数中,除了skip和rows选项,其余参数功能不生效,仅做语法兼容。
- 命令行和控制文件同时指定skip或rows参数时,命令行优先级更高。
参数说明
| 参数 | 参数说明 | 参数类型 : 取值范围 |
|---|---|---|
| help | 查看帮助信息。 | - |
| user | 数据库链接用户(与-U等价)。 | 字符串 |
| -U | 数据库链接用户(与user等价)。 | 字符串 |
| passwd | 用户密码(与-W等价)。 | 字符串 |
| -W | 用户密码(与passwd等价)。 | 字符串 |
| db | 数据库名称(必选,与-d等价)。 | 字符串 |
| -d | 数据库名称(必选,与db等价)。 | 字符串 |
| host | 指定正在运行服务器的主机名、Unix域套接字的路径、或者域名。接受以“,”分隔的字符串来指定多个主机地址,支持指定多个主机地址(与-h等价)。 当指定多个主机地址时,默认选择连接到主节点。 | 参考表9 |
| -h | 指定正在运行服务器的主机名、Unix域套接字的路径、或者域名。接受以“,”分隔的字符串来指定多个主机地址,支持指定多个主机地址(与host等价)。 当指定多个主机地址时,默认选择连接到主节点。 | 参考表9 |
| port | 指定数据库服务器的端口号。可以配置一个或多个,当配置一个时,所有的IP都使用同一个端口连接;当配置多个时,顺序与IP顺序相同,个数必须与IP数相等,当不相等时会报错(与-p等价)。 | 参考表9 |
| -p | 指定数据库服务器的端口号。可以配置一个或多个,当配置一个时,所有的IP都使用同一个端口连接;当配置多个时,顺序与IP顺序相同,个数必须与IP数相等,当不相等时会报错(与port等价)。 | 参考表9 |
| create | 是否创建pgxc_copy_error_log和gs_copy_summary表。由于当前版本会默认创建这两张表,因此该参数失去意义。保留仅为了兼容性。 | [true, false],默认true。 |
| clean | 是否清除本次错误记录。 | [true, false],默认false。 |
| data | 数据文件,可以指定多个,或者通配符多字符通配(*)以及单字符通配(?)(必选)。 | 字符串 |
| control | 控制文件名称(必选)。 | 字符串 |
| log | 日志文件名称。 | 字符串 |
| bad | 出错行以及详细情况记录文件名称,也可以指定目录,不指定时根据数据文件名生成。 | 字符串 |
| discard | WHEN匹配失败行记录文件名称,也可以指定目录,根据数据文件名生成。 | 字符串 |
| errors | 允许数据文件中出现多少出错行。与\copy语法中log errors data功能一致。 | 整数,取值范围[1, 2147483647],默认0。 |
| skip | 允许跳过数据文件的前多少行。 | 整数,取值范围[0, 2147483647],默认0。 |
| limit | 指定最多导入的行数。 | 整数,取值范围[1, 2147483647],默认无限大。 |
| bindsize | 仅做语法兼容不实现功能。 | - |
| rows | 多行提交参数,指定导入多少行数据后进行一次提交。 | 整数,取值范围[1, 2147483647]。 |
| compatible_nul | 是否开启数据中nul字符(0x00)兼容,开启后当数据文件中存在nul字符时,会先将nul字符转换成为空格字符' '(0x20),再进行判断,加工和导入。 | [true, false],默认true。 |
| compatible_illegal_chars | 是否开启非法字符容错功能,与COPY语法中的COMPATIBLE_ILLEGAL_CHARS容错规则和限制相同,具体请参见COMPATIBLE_ILLEGAL_CHARS。 | [true, false],默认false。 |
| parallel | 指定并行导入的并发度。当并发度大于1时,表示开启并行导入模式。当并发度等于1时,转化为串行导入。并发度最大值限定不超过客户端CPU核数的两倍。当客户端运行在容器中时,由于获取到的是主机的cpu数量,可能比容器实际能使用的多,并发度建议由用户自行控制在实际能使用的客户端cpu数量的两倍范围内。另外该能力通过多线程并发多个事务实现,实际并发度会受限于服务端的线程池模型,也同时会给服务端增加压力,请按实际情况合理设置并发度。 | 整数,取值范围[1, CPU核数两倍],默认1。 |
| binary | 数据文件是否为通过copy的binary模式导出的二进制文件。 | [true, false],默认false。 |
| position_keep_trailing_space | 在定长格式导入时,是否会trim数据尾部空格之后再导入。 对于定长格式下,明确指定列类型为raw类型,且开启a_format_copy_version='s1'的情况,将不受该参数控制,总是保留尾部空格。 该参数只对字符类型生效,对于数值、blob等类型不生效。 在导入数据的过程中,系统会先从数据文件中读取原始数据,随后依次进行列表达式计算和数据类型转换等处理。 trim尾部空格的行为仅发生在数据读取阶段,该参数仅在此阶段生效。一旦数据读取完成,后续的列表达式处理、类型转换等操作若涉及尾部空格修剪,则不受该参数控制。 例如,当控制文件中将某列定义为char类型,而表中对应列实际为varchar类型时,从char到varchar的转换会自动修剪尾部空格,这一行为不受上述读取参数影响。若希望在此类类型转换场景下保留尾部空格,可通过设置behavior_compat_options参数包含char_coerce_compat选项来实现。 | [true, false],默认false。 |
| check | 在不连接数据库的情况下,进行客户端参数和控制文件语法的校验,不进行实际的数据导入。校验通过后,命令在实际执行时能确保指定的参数通过客户端的校验,不保证一定执行成功。 | [true, false],默认false。 |
- 参数均为小写,不支持大写,同时兼容gsql登录方式:-p端口号,-h主机,-d数据库,-U用户名,-W密码。
- 不支持OPTIONS、INFILE、BADFILE,仅在特定场景下不报语法错误。
- gs_loader在设置guc参数a_format_load_with_constraints_violation='s1'开启支持约束冲突不回滚场景时,如果表带有BEFORE/AFTER ROW INSERT触发器,则每次提交行数不能超过1000万行。
- gs_loader在设置guc参数a_format_load_with_constraints_violation='s1'开启支持约束冲突不回滚场景时,不支持语句级触发器。
- 使用rows参数时,提交次数不要超过1000次,否则会对性能产生影响。提交次数约等于数据文件中数据行数除以rows参数取值。不指定rows参数时,rows无默认取值,表现为只进行一次提交,即所有数据都导入表中后进行一次事务提交。
- 小数据量频繁的提交会影响导入数据的性能,推荐合理配置rows参数的取值,保证每次提交的数据量大于5MB。对于常用的16U128G规格机器,一主两备部署场景下,向5个字段的表内导入13GB数据,排除网络影响,多次提交和单次提交(每次提交5MB数据)的速率基本持平,为10MB/s左右。
- compatible_nul参数实际控制guc参数loader_support_nul_character值的设置:
- 当前gs_loader仅支持数据文件中存在nul字符时的兼容,不支持ctl控制文件中存在nul字符。ctl文件中存在nul字符会存在不可预期的问题。
- gs_loader导入过程中,不需要转码场景下,单行数据最大不超过1GB-1;转码场景下单行数据最大不超过256MB-1。当单行数据过大而max_process_memory设置过小时会报错内存不足,需要调整max_process_memory的大小后进行重试。
- 建议单个导入文件的大小不超过1GB。gs_loader对单个导入文件的大小没有限制,但是对大文件数据的导入通常会比较耗时,因此建议在使用的过程中,对较大的数据文件进行切分,启动多个gs_loader进程以append的形式向表中写入数据(如果有truncate的需要则需要单独执行truncate,而不是将truncate写入控制文件中)。在cpu资源足够时,这种做法可以有效地提升导入速率。
- 如果是基本的数据迁移场景,建议先删除表上的索引,禁用表上的触发器,待数据迁移完成后再重建索引,恢复表上的触发器。对导入性能会有所提升。
- 指定binary参数为true后,有以下要求:
- 数据文件必须为通过\COPY中BINARY模式导出的二进制格式数据文件,但是该模式导出的数据文件通常兼容性及可移植性较差,建议直接使用\COPY语句进行导入。
- gs_loader会将控制文件中语法转换为\COPY中BINARY模式下最简单的语法,即\COPY table_name FROM 'binary_file_path' BINARY; 语句。只解析控制文件中导入模式,表名信息和命令行中的control、data、binary、guc_param及数据库连接参数信息,不对其他参数语法进行解析和生效。
- 对于gs_loader的命令行及控制文件中以下参数会被忽略,其功能不生效:
- 不支持字符集配置。
- 不支持WHEN条件过滤及DISCARD生成。
- 不支持enable_copy_error_log = off下将错误数据直接写入bad文件,将日志数据直接写入log文件,enable_copy_error_log=on下将错误数据直接写入错误表,日志数据直接写入日志表。
- 不支持配置CSV模式,不支持指定分隔符及包裹符,不支持TRAILING NULLCOLS语法。
- 不支持数据类型配置、POSITION配置及列表达式使用。
- 不支持FILLER、CONSTANT、SEQUENCE、NULLIF参数。
- 不支持skip、rows、compatible_nul、compatible_illegal_chars、parallel参数。
- 指定parallel大于1时:
- 将启动多个线程导入数据,每个线程都是单独的事务,可能出现数据部分导入的结果。请在充分理解并行可能造成的结果下,谨慎使用并行导入数据。
- 当同时设置binary参数为true时,parallel参数失效,按串行导入。
- 不支持在控制文件中设置OPTIONALLY ENCLOSED BY或者FIELDS CSV。
- 不支持在控制文件中设置SEQUENCE列。
- 无法保证数据按数据文件中的顺序导入。如果表中存在自增列,导入后自增列值的顺序无法保证与数据文件中顺序一致。
- 同时使用errors参数时,errors参数的意义为每个子任务允许出现的最大错误行数。若在指定多文件导入时,errors参数的意义为每个数据文件的每个子任务允许出现的最大错误行数。
- 同时使用skip参数时,skip参数的意义是在整个数据文件开头跳过的行数。
- 同时使用rows参数时,分批提交的各批次子任务独立计算。
- 在客户端CPU、内存和服务端CPU、内存、空闲线程以及网络带宽不存在瓶颈,并且bad/discard总数不超过1%时,相比于串行导入,并发度为2/4/8时的性能提升不低于1.5/3/5倍。
- 并发度每增加1,大约增加客户端10MB内存,服务端大约35MB内存。
- 在数据文件中数据少于100行时,不会触发真正的并行导入。
- 当同时设置rows参数时,rows参数失效。
- 当设置GUC参数support_zero_character为on时,表示数据库支持0x00字符的写入和读取,gs_loader导入数据时,会将0x00按照原始样式导入,而不是受其他兼容性参数影响转换成0x20。对于0字符处理场景,如下参数在开启的时候优先级为support_zero_character > copy_special_character_version > compatible_illegal_chars > loader_support_nul_character。
- 当设置了copy_special_character_version='no_error'时,优先级高于compatible_illegal_chars,非法编码的数据会原样导入,而不是发生转换。
- 参数值建议使用数字、字母、汉字以及文件路径分隔符'/'等,其他字符可能会导致shell命令解析异常。
- 在对带触发器的表执行并行导入时,若触发器内部存在向带约束的表(记为t1)插入数据的操作,则可能因并发导入导致死锁问题。具体表现为:多个并行导入进程在操作过程中,t1表因约束条件(如唯一键、外键等)导致不同事务间产生锁等待状态,若形成循环等待则触发数据库死锁检测机制,最终导致导入失败并抛出deadlock错误。因此当目标表包含触发器,且触发器逻辑中存在向带约束的表插入数据的操作时,不建议使用并行导入。
- 不支持在控制文件中的列表达式中使用返回集合的函数(Set-Returning Functions)。
- 导入过程trim尾部空格的行为中,空格的范围包含(括号内为对应ASCLL的十六进制值):普通空格(0x20),\t(0x09),\n(0x0A),\v(0x0B),\f(0x0C),\r(0x0D)。
- CHARACTERSET
取值范围:字符串。如果指定为'al32utf8'/'zhs16gbk'/'zhs32gb18030',实际会转换成UTF8/GBK/GB18030。
注意:控制文件中CHARACTERSET指定的字符集,应该和文件的编码格式保持一致,否则会报错或者导入数据乱码。
- INFILE
当前关键字无效,并在控制文件中需要单独占一行,运行时会忽略该关键字。需要用户在gs_loader命令行参数中指定对应的数据文件。
- BADFILE
当前关键字无效,运行时会忽略该关键字,如果gs_loader命令行参数没有指定badfile,则会根据对应控制文件名称生成对应的badfile文件。
- STR
指定导入数据文件行结束符(换行符)样式,需要在控制文件中单独占一行。
取值范围:常见的行结束符,如\r、\n、\r\n,其他字符或字符串,如$、#。
默认值:与数据文件中第一行的换行符保持一致。
- 支持多字符行结束符,但不能超过10个字节。
- 行结束符只能用于TEXT格式的导入,不支持在数据文件中对数据进行转义。对于特殊数据类型,如INTEGER、SMALLINT等,会无视分隔符进入FIXED格式,导致报错。
- 行结束符不能被分隔符、包裹符、转义符、NULL字符串包含。
- 行结束符不能包含.,()abcdefghijklmnopqrstuvwxyz0123456789中的字符。使用时禁止使用中文字符做分隔符,仅支持ASCII码范围内的单字节字符,拼接组合成为多字节分隔符使用。
- 对于包含单引号或双引号的换行符,需要对其转义,两个双引号连用即换行符内有一个双引号,两个单引号连用即换行符内有一个单引号。例如,"STR ''''"指定了换行符为一个单引号。
- 不支持在指定自定义换行符的场景下进行并行导入。
- 二进制格式下,不支持配置自定义行结束符;配置后换行符不生效,仅会解析为BINARY模式下最简单的语法,即\COPY table_name FROM 'binary_file_path' BINARY; 语句。
- STR参数的end_of_line支持两种写法,文本字符串写法以及十六进制字符串写法。以'|'字符作为行结束符为例,文本写法为"STR '|'",十六进制字符串写法为"STR x'7c'"。
- ctl文件中STR参数指定的字符串end_of_line按照ctl文件中characterset所指定的客户端编码进行识别。
- 当自定义行结束符end_of_line中,包含不可见字符,需要用十六进制的方式进行表示。一部分不可见字符可以通过'\'字符转义,可转义字符范围:'\n', '\t', '\f', '\v', '\r'。
- 自定义的行结束符同实际字段内容重复,TEXT格式无法对数据内的换行符进行转义,建议更换换行符为数据中没有的不可见字符。若不做区分则认为数据中冲突的换行符为真正的换行符处理,导入时可能会发生报错/窜行/缺列。
- 不指定自定义换行符时,UNIX-base的操作系统下, 默认使用'\n'进行换行。
- 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_character时,如果使用了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字符转换而来的空格字符。
- 使用多字节分隔符进行导出和导入时,不推荐用户将中文字符或其他包含多个字节的字符用作分隔符;这种场景下,当字段内存在与分隔符相同的数据时,可能会出现导出后再导入报错的问题。
- 使用多字节分隔符时,建议使用ASCII码范围内的单字节字符拼接组合成为多字节分隔符使用。
- OPTIONALLY ENCLOSED BY { 'string' }
仅在使用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 })
- 使用POSITION用法表示在转换后的\COPY语句中使用FIXED FORMATTER的定长模式,会无法正确处理字段数据中的换行符。
- 如果只有部分字段使用了POSITION用法, 建议优先把所有POSITION字段,按照POSITION位置先后,在控制文件中升序填写;其他没有使用POSITION的字段放到后面。
- 多个POSITION字段的起止范围不建议重叠。
- "sql_string"
对列进行处理,列表达式,根据表达式计算列的取值。详见列表达式。
取值范围:字符串。
- FILLER
- 当前不支持FILLER与POSITION ({ start:end })同时使用。
- 使用FILLER导入时,FILLER修饰的列上不能有非空约束。
- 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)语法。具体使用详见示例二和示例三。
- 列表达式
({ column_name [ data_type ] [ AS transform_expr ] } [, ...])其中data_type指定该列在表达式参数中的数据类型;transform_expr为目标表达式,返回与表中目标列数据类型一致的结果值,具体请参见示例四和示例五。
- 数据类型
对应控制文件中的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)]:
- FLOAT external [(length)]:
- DECIMAL external (length):
- TIMESTAMP:
- DATE:
- DATEA:
- DATE external:
- SYSDATE:
- CHAR [(length)]:
- 特殊数据类型
- SMALLINT:
无视字段分隔符读取两个字节长度的字符,按小端存储逻辑保存,然后将每个字符解析成十六进制ASCII码值,最后将整体转换为十进制数来保存值,具体请参见示例六。
- RAW:
会把每个字符解析成ASCII码值保存,转义字符“\”不执行转义操作,具体请参见示例七。
限制:RAW不能使用分隔符。
- 在多列导入场景中,不指定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大于文本文件 (.csv/.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';”。
- 当不设置guc参数a_format_copy_version='s1'时,若列表达式中对列字段进行使用,例如列字段名为col,列表达式为"trim(:col)",会消耗数据文件中对应列的数据;若列表达式中没有对列字段进行使用,例如列表达式为"trim('abc')",则不会消耗数据文件中对应列的数据。当设置guc参数a_format_copy_version='s1'时,无论是否在列表达式中对列字段进行使用,都会消耗数据文件中对应列的数据。
- 当指定SYSDATE时,不会消耗数据文件中对应列的数据。
- 并发导入时,若多个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
- SMALLINT:
- 普通数据类型
示例
示例一:导入基本场景
- (非三权分立)仅对于普通用户。
- (在管理员用户下)创建用户。
gaussdb=# CREATE USER load_user WITH PASSWORD '********'; - (可选,在管理员用户下)给用户授权pg_catalog.gs_copy_summary_log表。
如果guc参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用日志表,无需创建。否则需要创建该日志表。
gaussdb=# GRANT SELECT,DELETE ON pg_catalog.gs_copy_summary_log To load_user; - (可选,在管理员用户下)给用户授权错误表gs_copy_error_log。
如果guc参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用错误表,无需创建。否则需要创建该错误表。
gaussdb=# GRANT SELECT,DELETE ON pg_catalog.gs_copy_error_log To load_user; - 切换用户。
gaussdb=# \c - load_user
- (在管理员用户下)创建用户。
- (三权分立)对于普通用户和管理员用户。
- (在初始用户下)创建用户。
gaussdb=# CREATE USER load_user WITH PASSWORD '********'; - (在初始用户下)切换为load_user用户。
gaussdb=# \c - load_user - (可选)创建gs_copy_summary表并添加索引。
如果guc参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用日志表,无需创建。否则需要创建该日志表。
gaussdb=# 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); gaussdb=# CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);
- (可选)创建pgxc_copy_error_log表并添加索引。
如果guc参数enable_copy_error_log未设置(默认为off),或者设置为off,则无需使用错误表,无需创建。否则需要创建该错误表。
gaussdb=# CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text); gaussdb=# CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
- (在初始用户下)创建用户。
- 创建表和控制文件,准备数据文件。
创建表loader_tbl。
1 2 3 4 5 6 7
gaussdb=# 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。
1set 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工具有可执行权限。确保当前路径有文件写入权限(gs_loader在处理过程中会生成一些临时文件,导入完成后自动删除)。
1gs_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语法及其详细情况,[badfile]_bad.log文件用于记录错误数据及其详细情况。为防止上一次导入时记录的错误数据以及详细情况被覆盖,建议每次执行导入时使用不同的bad参数。如果使用错误表记录错误数据以及详细情况,请开启GUC参数enable_copy_error_log。如需删除表中的数据,可以对错误表和日志表执行truncate或者delete操作。
示例二:bad文件对应数据为空时,需要对应错误表的内容参考源文件和行号(不识别某种编码序列,不写bad文件内容,只记录空行)。
--对于loader对应的文件,查找数据文本第一行找出源数据。
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) --创建表gsloader_test_nullif。
gaussdb=# 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运算的列执行导入操作后导入字段正常,其余未指定运算的列表现为导入字段为空。
示例四:ctl文件中不指定列类型,源数据不满足表中列限制(数据类型限制、数据长度限制)。
--建表。 gaussdb=# 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设置a_format_copy_version参数。 cat test_guc.txt 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 --导入结果,显示导入成功。 gaussdb=# select * from t_test; id | text ----+------- 5 | aaaaa (1 row)
示例五:ctl文件中不指定列类型,隐式类型转换(涉及隐式类型转换,建议加上兼容性参数)。
--建表。 gaussdb=# 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设置a_format_copy_version参数。 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 --查看导入结果,显示导入成功。 gaussdb=# 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
--建表。 gaussdb=# 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设置a_format_copy_version参数。 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 --查看导入结果,显示导入成功。 gaussdb=# select * from t_spec; col1 | col2 -----------+------- 741618482 | 13612 (1 row)
--建表。 gaussdb=# 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设置a_format_copy_version参数。 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 --导入结果,查看导入成功。 gaussdb=# select * from t_raw; col -------------------------------------------------------- 31325C6E5C783738213C7E3F276B5E282573293E2F635B2435305D (1 row)