南大通用GBase 8a 集群加载数据LOAD的方法

发表于 作者 laozizhu

86版本不再使用加载服务dispserver和控制文件，而是采用了SQL方式从数据源(ftp, http, hadoop) 直接加载的方式。本文提供SQL语法和样例。

作用

从数据源将数据加载到指定表中。

使用方法

LOAD DATA INFILE 'file_list'
INTO TABLE [dbname.]tbl_name
[options]
options:
 [CHARACTER SET charset_name]
 [DATA_FORMAT number [HAVING LINES SEPARATOR]]
 [NULL_VALUE 'string']
 [FIELDS
   [TERMINATED BY 'string']
   [ENCLOSED BY 'string']
   [PRESERVE BLANKS]
   [AUTOFILL]
   [LENGTH 'string']
   [TABLE_FIELDS 'string']
 ]
 [LINES
   [TERMINATED BY 'string']
 ]
 [MAX_BAD_RECORDS number]
 [DATETIME FORMAT format]
 [DATE FORMAT format]
 [TIMESTAMP FORMAT format]
 [TIME FORMAT format]
 [TRACE number]
 [TRACE_PATH 'string']
 [NOSPLIT]
 [PARALLEL number]
 [MAX_DATA_PROCESSORS number]
 [MIN_CHUNK_SIZE number]
 [SKIP_BAD_FILE number]
 [SET col_name = value[,...]]
 [IGNORE NUM LINES]
 [FILE_FORMAT format]

参数详解

一些默认的参数，请参考 GBase 8a集群86版本加载相关参数

FILE_LIST

待加载文件列表，或待加载数据文件所在的目录。支持 URL 的方式指定数据文件路径，以逗号(',')作为多个文件/目录的分隔符。

格式

scheme://host:port/path, scheme://host:port/path

支持的数据源类型

S3、本地、https、ftp、sftp、hdfs、gbfs。

参考：
GBase 8a 集群加载sftp 和ftp数据的样例
GBase 8a加载hadoop 测试2.6.10版本3节点集群安装

CHARACTER SET

用来指定待加载数据文件的编码格式，目前支持 GBK和 UTF8 两种格式。省略时，不需要转码。

参考：GBase 8a加载时数据字符集和数据库不匹配时的处理方法

DATA_FORMAT

用来指定使用哪种方式解析数据文件并加载。

  • 指定为1/2 ，在85版本使用，86之后已经弃用。
  • 指定为 3，表示使用文本方式加载。
  • 指定为 4，表示使用定长方式加载。
  • 指定为 5，表示使用文本文件宽松模式，即数据源文件为包围符中含有换行符和包围符文本文件，或多列少列文本文件

HAVING LINES SEPARATOR

如果某列数据可能包含了行分隔符，则需要在 SQL 中输入'HAVING LINES SEPARATOR'子句。

NULL_VALUE

用于指定空值字符，支持不超过 15 个任意字符的组合，参数值以引号包围，指定方式与字段包围符一样。

默认值是'\N'

FIELDS

字段指示，包括分隔符，包围符，空格处理等。

TERMINATED BY

用于指定字段分隔符，支持不超过 15 个任意字符的组合，支持任意字符，参数值以引号包围，仅当使用文本方式加载时有效。可
使用字符本身(仅限可见字符，如："|")、C 风格转义字符(如："\a")、\xhh 十六进制(如："\xFF")或 x''十六进制(如："x'09'")四种方式指定。例如：'|'，表示用|作为分隔字符。

ENCLOSED BY

用于指定字段包围符，支持任意单字符，参数值以单引号包围，仅当使用文本方式加载时有效。可使用字符本身(仅限可见字符，如：
"|")、C 风格转义字符(如："\a")、\xhh 十六进制(如："\xFF")或 x''十六进制(如："x'09'")四种方式指定。

PRESERVE BLANKS

用于设定是否保留字段内容两端的空格，可选参数可以选择保留左边空格或者右边空格，默认不保留空格。

  • 没有设置该参数：不保留空格。默认；
  • PRESERVE BLANKS ： 保留全部空格；
  • PRESERVE LEADING BLANKS ： 保留开头的空格；
  • PRESERVE TRAILING BLANKS ： 保留尾部的空格。

参考： GBase 8a对加载数据中的保留空格处理方案

AUTOFILL

用于设定是否启用缺失列自动补齐功能，启用该参数后，对缺失分割符的字段数据按照default值或者NULL值进行加载，默认不自动补齐。

参考：GBase 8a 加载LOAD通过SET或AUTOFILL设置未加载列的值

LENGTH

在使用定长模式加载时，用于设定字段长度的参数。定长格式数据导入时，设置每个字段的长度，有多个字段时，用逗号分隔。单位：字节。请注意原始数据1个UTF8汉字，对应3个字节。

参考：GBase 8a加载LOAD定长格式的数据方法

TABLE_FIELDS

用于指定列加载，对于日期时间类型可以设置每一列的格式。对于数据加载过程中，数据文件中不需要加载的字段，可以使用
table_fields 参数中的 filler 关键字将其忽略掉。

参考：GBase 8a 集群加载数据LOAD数据和字段不匹配的处理方案

LINES TERMINATED BY

行分隔符，支持任意单字符，参数值以引号包围。指定方式与包围符一样。默认行分隔符为'\n'。

参考：GBase 8a导出加载数据时，字段内容包含换行\n怎么处理

MAX_BAD_RECORDS

允许数据格式错误的最大行数。取值范围0, 4294967295。

  • 不设置此参数 ，无限制，即使全部数据都无法入库，也不会报错返回；
  • 0：只要有一行无法入库就报错返回；
  • 0, 4294967295]。0 表示只要有错误数据就报错返回；
  • 其它正整数，允许的错误行数，超过了就报错返回；

最大加载错误数的计算方式：

  • 所有集群节点独立计算，一旦有一个节点加载时错误数据达到本限制，则终止所有节点的加载任务。
  • 集群加载提交之前检查总错误条数是否超出限制，如果超出限制，放弃提交，报错退出。

参考：GBase 8a加载LOAD报错 Too many bad records 原因和解决方案

DATETIME FORMAT

用来指定datetime 列的格式 ， 默认'%Y-%m-%d %H:%i:%s'。 2021-02-03 12:13:14 这种。

带毫秒的，需要单独指定：比如'%Y-%m-%d %H:%i:%s.%f'。

参考：GBase 8a集群加载日期类型datetime自定义格式参数

DATE FORMAT

用来指定 date 列类型的格式，默认'%Y-%m-%d'。

TIMESTAMP FORMAT

用来指定timestamp 列的格式 ， 默认'%Y-%m-%d %H:%i:%s'

TIME FORMAT

用来指定 time 列的格式，默认'%H:%i:%s'。

TRACE

用来指示本次加载是否保存错误数据溯源。日志里将包含错误数据的原始文件，偏移量，长度等信息。

  • 为 0，则不溯源。
  • 为 1，则进行溯源。默认值为 1。

TRACE_PATH

用来指定本次加载过程中产生的错误数据和日志存放路径。在禁用日志汇总功能时，该参数才能起作用，默认值为加载节点的“$GBASE_BASE/log/gbase/loader_logs”中。

参考：GBase 8a集群加载的错误数据导出到指定ftp/sftp/服务器目录下

NOSPLIT

用来指定本次加载任务中是否禁用分块加载功能，指定该参数将禁用分块加载功能。不指定该参数，在集群加载时，将自动启动分块加载功能，按照数据量和参与运算的加载节点数对数据进行均匀分块，以均衡数据服务器和数据处理节点的负载，优化加载性能。

参考：GBase 8a 加载大量小文件时，通过NOSPLIT参数较少执行计划耗时

PARALLEL

用来控制集群加载并行度，取值范围[0,1024]。默认值为 0，表示并行度取值是线程池最大可用线程数。

对应参数：gbase_loader_parallel_degree

MAX_DATA_PROCESSORS

用来指定本次加载任务中参与数据解析的处理的最大节点数，取值范围[1, 4294967295]，默认值 16。

对应参数：gcluster_loader_max_data_processors

MIN_CHUNK_SIZE

用来指定本次加载任务中数据分块的最小粒度，取值范围[1, 4294967295]，默认值 64M。一个大文件，会被分成多个分块来传输和加载。如果有并行，有可能被多个加载节点处理。

对应参数：gcluster_loader_min_chunk_size

SKIP_BAD_FILE

用来指定本次加载任务中是否忽略不存在或没有读取权限的数据文件继续加载。

  • 为 0，则加载报错终止。默认值。
  • 为 1，则忽略异常文件继续加载。

SET

指定列值加载，加载系统将待加载文件和指定加载列值加载到集群系统的表中。输入的类型应为常量，包括字符串、整数值、浮点值和 NULL。

  • 1） 支持指定所有列类型加载值；
  • 2） 指定列值为常量值（包括 NULL），包括字符串（单引号包围）、十进制数值（10）、浮点值（10.9）、NULL、16 进制表示的字符串（0xbac3)、科学计数法（10e4)；
  • 3） 支持多列同时指定加载值。最多可 SET 表列数-1，如果设置的列数与表定义中的列数一致将报错：Specified all fields；
  • 4） 支持 format=3、format=4 以及 format=5；

使用限制说明

  • 1） 输入除常量值外的其他值，如列名、表达式等会报错，报错信息为 Column 'addr' should be const value；
  • 2） 指定的列不能存在于 TABLE_FIELDS 中，否则报错；
  • 3） 如果没有指定 AUTOFILL，指定值的列数+数据中列数之和必须等于表定义或者 TABLE_FIELDS (若指定了 TABLE_FIELDS)中的列数，否则会产生错误数据；如果指定了 AUTOFILL，则可以小于表定义的列数，缺少的列会自动补全。如果 TABLE_FIELDS 列数+SET 列数小于表定义的列数，能够正常加载，没有涉及的列按照 default 值补齐；
  • 4） 同一列在 SQL 中不能重复指定，否则报错

参考：GBase 8a 加载LOAD通过SET或AUTOFILL设置未加载列的值

IGNORE NUM LINES

配置该参数加载工具会将本次加载指定的所有数据文件的表头进行过滤，跳过每个文件的前 NUM 行（表头所占行数），NUM 取
值范围为[0，MAX_UINT]。

FILE_FORMAT

用来指定被加载文件的格式。枚举型参数。

  • UNDEFINED，不指定格式，按文件后缀自动判断文件格式；默认值。
  • UNCOMPRESSED，未压缩，按普通文本方式加载文件；
  • GZIP，按 GZIP 格式加载文件（默认.gz结尾)；
  • SNAPPY，按 SNAPPY格式加载文件(默认.snappy结尾)；
  • LZO，按 LZO 格式加载文件(默认.lzo结尾)

IPV6注意事项

IPV6地址前后要用中括号，因为数据源协议里的最后默认是端口号。

ftp://gbase:gbase@[2001::61]/

兼容加载时，在ctl文件要加上中括号，在dispcli_config.conf里也要加上中括号。

server=[2001::61]{/home/gbase/:ftp://gbase:gbase@[2001::61]/}

使用样例

LOAD DATA INFILE
'ftp://ftploaduser:ftploaduser@xxx.xx.xx.191/data/tbname_toload/Tbname20160730143000_20160730150000.ldr'
INTO TABLE `test86`.`tbname_86`
DATA_FORMAT 3
NULL_VALUE '\\N'
FIELDS TERMINATED BY '|’enclosed by '"'

其中最重要的是字段分隔符 TERMINATED BY，如果有自定义的时间，也需要指出

LOAD DATA
INFILE 'ftp://gbaseload:gbaseload@192.168.1.1/1.csv,ftp://gbaseload:gbaseload@192.168.1.1/2.csv'
INTO TABLE zxvmax.aggr_volte_call_ci_15min_20170613
DATA_FORMAT 3
FIELDS 
TERMINATED BY '^'
TABLE_FIELDS 'clttime,neid,hh,source_ne_type'
DATETIME FORMAT '%Y-%m-%d %H:%i:%s.%f'
TIMESTAMP FORMAT '%Y-%m-%d %H:%i:%s.%f'

一些协议的URI样例，请注意协议支持绝对路径的用//表示根，否则默认是协议的默认目录。

ftp://gbaseload:gbaseload@192.168.1.1/2.csv
sftp://gbase:gbase@127.0.0.1//home/gbase/data/a.tbl
sftp://gbase:gbase@127.0.0.1/data/a.tbl
hdp://hadoop@192.168.10.1:50070/data/test.tbl
http://IP/b.tbl.gz

某个IP本地加载
file://192.168.0.110/home/gbase/3.txt
所有计算节点的目录下都有这个文件，可以并行本地文件加载
file:///home/gbase/3.txt

一个加载hadoop数据源的样例

LOAD DATA INFILE 'hdp://mr:@csjwy-dsj-zdh01:50071/hive/csga_ads.db/ads_kkxx_kkfltgl///*' into table csga_ads.ads_kkxx_kkfltgl_bak fields terminated by '^|~' table_fields 'kkbh,kkmc,cpgs,cpys,sjl,rq,sjzx_rksj,sjzx_nbid' datetime format '%Y-%m-%d %H:%i:%s.%f'

加载的数据库参数请参考 GBase 8a集群86版本加载相关参数

Post Views: 7,069

相关文章:

  1. 南大通用GBase 8a集群查看加载进度的方法
  2. 南大通用GBase 8a集群加载日期类型datetime自定义格式参数
  3. 南大通用GBase 8a数据库加载时用户名、密码、路径等有特殊字符的处理
  4. 南大通用GBase 8a数据库加载LOAD报错信息分析和解决文章汇总
  5. 南大通用GBase 8a加载通过null_value参数指定null空值的参数
  6. 南大通用GBase 8a加载错误数据收集参数gbase_loader_logs_collect
  7. 南大通用GBase 8a针对宽表1000列的小文件加载不同版本配置性能测试
  8. 南大通用GBase 8a在V95版本不同列、不同行数文件加载性能测试对比
  9. 南大通用GBase 8a加载hadoop 测试2.6.10版本3节点集群安装
  10. 南大通用GBase 8a如何提高加载性能，影响性能的因素有哪些

《南大通用GBase 8a 集群加载数据LOAD的方法》有2条评论

  1. Pingback： GBase 8a集群86版本加载相关参数 - 老紫竹的家
  2. Pingback： GBase 8a 集群加载数据LOAD数据和字段不匹配的处理方案 - 老紫竹的家

评论已关闭。