GBase 8a

其他

文章

南大通用GBase 8a 加载示例介绍

发表于2025-02-15 14:00:04362次浏览0个评论

数据加载语法格式

GBase 8a MPP Cluster支持通过SQL接口进行数据加载。

语法

LOAD DATA INFILE 'file_list' INTO TABLE tbl_name [options]
tbl_name:
 [database_name.]table_name
options:
 [DATA_FORMAT number [HAVING LINES SEPARATOR]]
[NULL_VALUE 'string']
 [FIELDS
   [TERMINATED BY 'string']
   [ENCLOSED BY 'string']
[PRESERVE BLANKS]
[AUTOFILL]
[DEFINER '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]

参数说明

FILE_LIST：待加载文件列表。支持URL的方式指定数据文件路径，以英文逗号(',')作为多个文件的分隔符，其中FTP文件名部分支持使用通配符’*’。
scheme://[user:password@]host[:port]/path,
scheme://[user:password@]host[:port]/path
OPTIONS：
DATA_FORMAT：用来指定使用哪种方式解析数据文件并加载。指定为3，表示使用文本方式加载。指定为4，表示使用定长方式加载。如果某列数据可能包含了行分隔符，则需要在SQL中输入'HAVING LINES SEPARATOR'子句。
NULL_VALUE：用于指定空值字符，支持不超过15个任意字符的组合，参数值以引号包围，指定方式与字段包围符一样。
FIELDS子段：
TERMINATED BY：用于指定字段分隔符，支持不超过15个任意字符的组合，支持任意字符，参数值以单引号包围，仅当使用文本方式加载时有效。可使用字符本身(仅限可见字符，如："|")、C风格转义字符(如："\a")、\xhh十六进制(如："\xFF")或x''十六进制(如："x'09'")四种方式指定。例如：'|'，表示用|作为分隔字符。
ENCLOSED BY：用于指定字段包围符，支持任意单字符，参数值以单引号包围，仅当使用文本方式加载时有效。可使用字符本身(仅限可见字符，如："|")、C风格转义字符(如："\a")、\xhh十六进制(如："\xFF")或x''十六进制(如："x'09'")四种方式指定。
PRESERVE BLANKS：用于设定是否保留字段内容两端的空格，默认不保留空格。
AUTOFILL：用于设定是否启用缺失列自动补齐功能，启用该参数后，对缺失分割符的字段数据按照default值或者NULL值进行加载，默认不自动补齐。
DEFINER：在使用定长模式加载时，用于设定字段长度的参数。定长格式数据导入时，设置每个字段的长度，有多个字段时，用逗号分隔。
TABLE_FIELDS：用于指定列加载，对于日期时间类型可以设置每一列的格式。
LINES子段：
TERMINATED BY：行分隔符，支持任意单字符，参数值以引号包围。指定方式与包围符一样。默认行分隔符为'\n'。
MAX_BAD_RECORDS：在每次加载的任务中，设定错误数据行数的上限。当本次加载任务产生的错误数据行数大于max_bad_records设定的值时，加载任务回滚，加载工具报错退出。此参数取值范围为：[0, 4294967295]。此参数为可选参数，默认不限制错误条数。0表示只要有错误数据就报错退出。
DATE FORMAT：用来指定date列类型的默认格式，默认为'%Y-%m-%d'。
DATETIME FORMAT：用来指定datetime列的默认格式，默认为'%Y-%m-%d %H:%i:%s'。
TIMESTAMP FORMAT：用来指定timestamp列的默认格式，默认为'%Y-%m-%d %H:%i:%s'。
TIME FORMAT：用来指定time列的默认格式，默认为'%H:%i:%s'。
TRACE：用来指示本次加载是否保存错误数据溯源。如果指定为0，则不溯源。如果指定为1，则进行溯源。默认值为1。
溯源信息包括：错误数据所在的文件，所在行号。T
TRACE_PATH：用来指定本次加载过程中产生的错误数据和日志存放路径。缺省情况下，错误数据和溯源日志记录在加载机节点的/opt/gnode/log/gbase/loader_logs下。

数据加载使用示例介绍

特定文件加载

以文本方式加载位于ftp服务器上的文件lineorder1.tbl，默认行分隔符，指定|为列分隔符

LOAD DATA INFILE 
'ftp://gbase:Gbase2016@ZGYD@10.17.5.225//opt/SSB/data/lineorder1.tbl' INTO TABLE ssbm.lineorder1 
data_format 3 
fields terminated by '|';

通配符加载

以文本方式加载位于ftp服务器上的文件*.tbl，默认行分隔符，指定|为列分隔符

LOAD DATA INFILE 
'ftp://gbase:Gbase2016@ZGYD@10.17.5.225//opt/SSB/data/*.tbl' 
INTO TABLE ssbm.lineorder1 
data_format 3 
fields terminated by '|';

日期格式字段加载

文本文件加载，默认行分隔符，指定逗号为列分隔符，指定null value,指定date、time、datetime日期格式，指定最大错误行数

load data infile 'ftp://gbase:gbase@172.16.128.11//home/gbase/1.txt '
into table test.tmp_lxj_0621_test 
data_format 3 
fields terminated by ',' 
null_value '' 
date format '%Y-%m-%d' 
time format '%H:%i:%s' 
datetime format '%Y-%m-%d %H:%i:%s' 
max_bad_records 0;

多个不同类型协议数据文件加载

LOAD DATA INFILE 
'http://gbase:gbase@172.16.128.13/1.txt,
ftp://gbase:gbase@172.16.128.13/1.txt' 
INTO TABLE test.tmp_lxj_0513_test 
DATA_FORMAT 3;
Query OK, 5 rows affected (Elapsed: 00:00:00.25)
Task 288 finished, Loaded 5 records, Skipped 0 records

定长文本数据加载

定长文本加载，指定fields definer,table_fields

load data infile 
'ftp://gbase:Gbase2016@ZGYD@10.17.6.1//home/gbase/1.txt' 
into table provag.stg_d_evnt_4g_gprs_voice_12300_20161123
data_format 4 
FIELDS definer 
'8,15,6,3,40,14,8,14,14,9,1,17,10,32,10,40,10,10' 
TABLE_FIELDS 'RECORD_NUM,MSISDN,VISIT_POSITION,ROAM_TYPE_CODE,APN_NET_ID,CALL_START_TIME,CALL_DUR,LAST_LINE_DATE_FLOW,NEXT_LINE_DATE_FLOW,TOTAL_FEE,GPRS_NET_TYPE,IMEI,BUSI_CODE,ITEM_IP_ADDRESS,GPRS_BILL_ID,IMS_BILL_ID,TAC_ID,CELL_ID';

定长文本日期格式加载

load data infile 
'ftp://gbase:Gbase2016@ZGYD@10.17.6.1//home/gbase/load_date.txt' 
into table test.tmp_lxj_1124_datetime_test
data_format 4 
FIELDS length '19,10,8' 
TABLE_FIELDS 'create_time,v_date,v_time';

数据加载默认去除两边空格

load data infile 
'ftp://gbase:Gbase2016@ZGYD@10.17.6.1//home/gbase/load_blank.txt' into table test.tmp_lxj_1124_load_blank_test
data_format 4 
FIELDS length '7' 
TABLE_FIELDS 'id';

数据加载保留两边空格

load data infile 
'ftp://gbase:Gbase2016@ZGYD@10.17.6.1//home/gbase/load_blank.txt' into table test.tmp_lxj_1124_load_blank_test
data_format 4 
FIELDS length '7' 
TABLE_FIELDS 'id' 
PRESERVE BLANKS;

加载固定值

加载时支持某列加入固定值，使用set 列名=’值’的方式，使用方法和限制如下，具体语句参见示例。
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 中不能重复指定，否则报错。

示例：

表结构：

CREATE TABLE "test" (
 "a" int(11) DEFAULT NULL,
 "b" varchar(10) DEFAULT NULL,
 "c" varchar(10) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'

数据格式：

1,aaa
2,bbb
3,ccc

测试用例：

load data infile 'ftp://gbase:gbase@192.168.20.31//home/gbase/test.txt' into table test.test data_format 3 fields terminated by ',' set c='test.txt';

gbase> select * from test.test;
+------+------+----------+
| a    | b    | c        |
+------+------+----------+
|    3 | ccc  | test.txt |
|    2 | bbb  | test.txt |
|    1 | aaa  | test.txt |
+------+------+----------+
3 rows in set (Elapsed: 00:00:00.02)

skip_bad_file跳过错误文件加载

skip_bad_file：
0：遇到错误文件中断加载
1：跳过错误文件继续加载
默认值是0，表示遇到错误文件中断加载

不设置skip_bad_file,10.17.5.225//opt/SSB/data/part1.tbl不存在，加载报错

gbase> LOAD DATA INFILE 'ftp://gbase:Gbase2016@ZGYD@10.17.5.225//opt/SSB/data/part.tbl,ftp://gbase:Gbase2016@ZGYD@10.17.5.225//opt/SSB/data/part1.tbl' INTO TABLE ssbm.part data_format 3 fields terminated by '|';
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: I/O operation on ftp://gbase:**************@10.17.5.225//opt/SSB/data/part1.tbl failed with error - Remote file not found, 550 Could not get file size., File name ftp://gbase:**************@10.17.5.225//opt/SSB/data/part1.tbl

设置skip_bad_file=1,10.17.5.225//opt/SSB/data/part1.tbl不存在，加载成功

gbase> LOAD DATA INFILE 'ftp://gbase:Gbase2016@ZGYD@10.17.5.225//opt/SSB/data/part.tbl,ftp://gbase:Gbase2016@ZGYD@10.17.5.225//opt/SSB/data/part1.tbl' INTO TABLE ssbm.part data_format 3 fields terminated by '|' skip_bad_file 1;
Query OK, 0 rows affected (Elapsed: 00:00:00.98)
Task 262148 finished, Loaded 2000000 records, Skipped 0 records

登录后才可以发表评论