CREATE FOREIGN TABLE (GDS导入导出)
功能描述
在当前数据库创建一个GDS外表,用于数据并行导入导出。GDS外表分为只读外表和只写外表,分别用于数据并行导入和并行导出,缺省为只读外表。
GDS是一款运行在Linux操作系统的命令行工具,通过和外表机制的配合,实现数据的高速导入导出。GDS工具包需要安装在数据源文件所在的服务器上,数据源文件所在的服务器称为数据服务器,也叫GDS服务器。
安装和配置GDS,请参见安装配置和启动GDS。
注意事项
- 仅系统管理员dbadmin角色或已授权了USEFT权限的普通用户可执行外表操作。
1
ALTER USER user_name USEFT;
- GDS外表不需要显式指定分布方式,默认支持ROUNDROBIN分布方式。
- 对于GDS外表指定任何约束(列约束、表约束等)均不生效。
- GDS导入导出支持的文件格式:TEXT、CSV和FIXED。
语法格式
1 2 3 4 5 6 7 8 9 |
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ { column_name type_name POSITION(offset,length) | LIKE source_table } [, ...] ] ) SERVER gsmpp_server OPTIONS ( { option_name ' value ' } [, ...] ) [ { WRITE ONLY | READ ONLY }] [ WITH error_table_name | LOG INTO error_table_name] [REMOTE LOG 'name'] [PER NODE REJECT LIMIT 'value'] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]; |
参数说明
参数 |
描述 |
取值范围 |
---|---|---|
IF NOT EXISTS |
如果已经存在相同名称的表,系统不会报错,而会发出一个通知,告知表关系已存在。 |
- |
table_name |
外表的表名。 |
字符串,要符合标识符的命名规范。参见标识符命名规范。 |
column_name |
外表中的字段名。 |
|
type_name |
字段的数据类型。 |
- |
POSITION(offset,length) |
在固定长度模式中,定义每一个字段在数据文件中的位置。 offset为该字段在数据源文件中的起始位置,length为该字段的长度。 |
offset取值不能小于0字节,单位为字节。 每条记录的长度不能大于1GB,文件中没有出现的列默认以空值代替。 |
SERVER gsmpp_server |
外表的server名字。对于GDS外表,其server是初始数据库默认创建的,即gsmpp_server。 |
- |
OPTIONS |
用于指定外表数据的各类参数。 |
|
READ ONLY |
只读外表,该参数只供数据导入使用。 |
- |
参数 |
描述 |
取值范围 |
---|---|---|
location |
外表的数据源位置,目前支持URL方式的描述。多个URL使用‘|’分隔。 gds目前可以支持导出的时候自动创建外表定义的目录。 如外表location定义“gsfs:// 192.168.0.91:5000/2019/09”执行导出任务的时候,如果gds数据目录下的子目录“2019/09”不存在则会自动的创建该子目录,不需要用户手动创建外表中指定的目录。 |
|
format |
外表中数据源文件的格式。 |
取值范围:CSV、TEXT、FIXED,缺省值为TEXT。
说明:
|
header |
指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。header只能用于CSV,FIXED格式的文件中。 在导入数据时,如果header选项为on,则数据文件中第一行会被识别为标题行,会忽略此行。如果header为off,而数据文件中第一行会被识别为数据。 在导出数据时,如果header选项为on,则需要指定fileheader。fileheader用来指定导出头文件的格式。如果header为off,则导出数据文件不包含标题行。 |
true/on,false/off。缺省值为false/off。 |
fileheader |
指定导出数据要包含的标题行定义的文件,文件一般只包含一行用来描述每一列数据信息的字符串。 例如:在包含商品信息的数据前加标题行,定义文件如下 The information of products.\n |
|
out_filename_prefix |
指定write only外表导出时,GDS端生成导出数据文件的文件名前缀。 file_type设置为pipe时,会生成“dbName_schemaName_foreignTableName.pipe”的管道文件。 如果out_filename_prefix和location中都指定了管道名,则以location中指定的管道文件名为准。 |
|
delimiter |
指定数据文件行数据的字段分隔符,不指定则使用默认分隔符,TEXT格式的默认分隔符是水平制表符(tab),CSV格式的默认分隔符为“,”,FIXED格式没有分隔符。 |
支持多字符分隔符,但分隔符不能超过10个字节。
|
quote |
用于设置将CSV格式数据源文件中的什么字符识别为引号字符。缺省值为双引号。 |
|
escape |
用来指定CSV格式的数据源文件中,什么字符为逃逸字符。逃逸字符只能指定为单字节字符。 缺省值和quote相同。 |
- |
null |
用来指定数据文件中空值的表示。 |
|
blank_number_str_to_nul |
用来指定数据文件中数值类型(INT1OID、INT2OID、INT4OID、INT8OID、FLOAT4OID、FLOAT8OID)不定数量的空格转换为NULL值。该参数仅8.1.3、8.2.1.236、9.1.0及以上集群版本支持。 例如,设置该选项为true时,定义null '',null ' '(一个空格)、null ' '(二个空格)时都返回NULL值。 |
true/false,默认值为false,表示不做转换。 |
noescaping |
TEXT格式下,不对'\'和后面的字符进行转义。 |
noescaping参数只在TEXT格式下有效。true/on,false/off。缺省值为false/off。 |
encoding |
指定数据文件的编码格式名称,即需要以何编码格式对数据文件进行解析和校验/输出文件为何种编码格式。缺省值为当前数据库的默认客户端编码格式,即client_encoding。 导入外表此处强烈建议指定为文件的编码格式,或根据文件的字符集在导入前对client_encoding进行设置。否则可能会导致不必要的解析、校验错误以及其导致的导入报错回滚,甚至非法数据入库。导出外表同样希望指定此选项,以避免导出采用默认字符集设置时与预期不符。 在创建外表时此选项未指定,会在客户端给出对应Warning信息。 |
|
dataencoding |
用于指定数据在latin1数据库表中的实际编码,实现GDS导入导出时数据从dataencoding与encoding之间的转换。该参数仅8.2.0及以上集群版本支持。 |
GBK、GB18030、GB18030_2022、UTF8。 典型场景:
注意:
|
mode |
指定数据导入过程中,数据导入策略。GaussDB(DWS)只支持Normal策略。 |
取值范围:
|
eol |
指定导入导出数据文件换行符样式。 |
取值范围:支持多字符换行符,但换行符不能超过10个字节。常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。
|
conflict_delimiter |
此参数一般配合compatible_illegal_chars参数一起使用,当用户的数据文件中包含半个汉字字符的时候,并且这半个字符和分隔符会由于外表的编码和数据库编码不一致被编码成一个其他的汉字,导致分隔符被掩盖从而报错缺少字段。 如果用户不希望让这半个字符和分隔符编码成一个其他字符则可以使用此参数。 |
取值范围:true/on,false/off。缺省值为false/off。
此参数默认关闭。由于场景较少出现,不建议打开。如果未能识别场景而打开此参数则会有入表信息错乱的风险。 例如:假设有一行GBK数据"3|+|膢+|20191212"要被导入到UTF8的数据库,用户自定义的字段分隔符为"|+|"。 这行GBK数据和十六进制对应关系如下 33 7C 2B 7C C4 7C 2B 7C 323031393132313 ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ 3 | + | ** | + | 20191212 当导入UTF8数据库时,数据库会发生如下转换。 GBK -> UTF8 33->33 (3) 7C->7C (|) 2B->2B (+) 7C->7C(|) C47C-> E886A2(膢) 2B->2B(+) 7C->7C(|) 323031393132313-> 20191212 由于用户定义的字段分隔符号为"|+|",因此对该行数据分隔后获取的数据是3,膢+|20191212, 实际上用户可能要获得的是3,?,20191212。 "膢" 这个字符其实是一个用户不期望的半个字符,和用户定义的分隔符“|+|”中的“|”产生了冲突,导致数据错乱或者导入失败。 如果用户想要忽略这种字符可以使用conflict_delimiter配合compatible_illegal_chars参数将“C4”这个半个GBK字符准换成“?”导入UTF8数据库。 |
file_type |
指定导入或者导出的文件类型。 |
取值范围:normal,pipe,interconn。缺省为normal。
|
auto_create_pipe |
用于设置GDS进程是否自动创建命名管道文件。 设置auto_create_pipe参数时,file_type必须设置为pipe或者interconn,否则不能成功创建外表。 |
取值范围:true/on,false/off。缺省值为true/on。
|
del_pipe |
用于设置不落地导入/导出业务结束后是否自动删除管道文件。 设置del_pipe时,file_type必须设置为pipe或者interconn,否则不能成功创建外表。 |
取值范围:true/on,false/off。缺省值为true/on。
|
gds_compress |
该参数用于降低用户使用GDS管道文件时集群之间互联互通的网络带宽,当前仅支持snappy算法。该参数仅8.2.0及以上集群版本支持。 |
取值范围:snappy。 当前仅支持snappy算法,表示GDS管道导入导出的数据是以snappy算法压缩和解压缩的。
|
preserve_blanks |
用于定长导入时,控制每列数据拖尾的空白字符(包括空格,\t,\v,\f)是否保留。该参数仅8.2.0.100及以上集群版本支持。 |
取值范围:true/on,false/off。缺省值为false/off。
|
fix |
指定每一行定长格式数据的长度。按字节计算。此语法仅对READ ONLY的外表有效。 |
取值范围:N >= POSITION指定的总长度(总长度即为表定义最后一个字段的offset与length的和)AND N < 1GB |
out_fix_alignment |
定长导出中,指定BYTEAOID、CHAROID、NAMEOID、TEXTOID、BPCHAROID、VARCHAROID、NVARCHAR2OID、CSTRINGOID对应类型所在列的对齐方式。 |
取值范围:align_left、align_right 默认值:align_right
注意:
由于bytea数据类型要求十六进制格式(如"\XXXX")或八进制格式(如"\XXX\XXX\XXX"),导入时需要左对齐(即列数据以两种格式开头,而非空格)。因此若导出文件需要重新以GDS外表入库且数据长度小于外表formatter指定长度,导出时需要指定左对齐,否则会在入库的过程中报错。 |
out_fix_num_alignment |
定长导出中,指定数值类型TINYINT、SMALLINT、INTEGER、BINARY_INTEGER、BIGINT、NUMERIC[(p[,s])]、DECIMAL[(p[,s])]、NUMBER[(p[,s])]、REAL、FLOAT4、DOUBLE PRECISION、FLOAT8、FLOAT[(p)]、BINARY_DOUBLE、DEC[(p[,s])]、INTEGER[(p[,s])]、SMALLSERIAL、SERIAL、BIGSERIAL对应类型所在列的对齐方式。该选项仅9.1.0及以上集群版本支持。 |
取值范围:align_left、align_right 默认值:align_left |
date_format |
导入对于DATE类型指定格式。此语法仅对READ ONLY的外表有效。 |
取值范围:合法DATE格式。可参考时间、日期处理函数和操作符。
注意:
对于指定为ORACLE兼容类型的数据库,则DATE类型内建为TIMESTAMP类型。在导入的时候,若需指定格式,可以参考下面的timestamp_format参数。 |
time_format |
导入对于TIME类型指定格式。此语法仅对READ ONLY的外表有效。 |
取值范围:合法TIME格式,不支持时区。可参考时间、日期处理函数和操作符。 |
timestamp_format |
导入对于TIMESTAMP类型指定格式。此语法仅对READ ONLY的外表有效。 |
取值范围:合法TIMESTAMP格式,不支持时区。可参考时间、日期处理函数和操作符。 |
smalldatetime_format |
导入对于SMALLDATETIME类型指定格式。此语法仅对READ ONLY的外表有效。 |
取值范围:合法SMALLDATETIME格式。可参考时间、日期处理函数和操作符。 |
参数 |
描述 |
取值范围 |
---|---|---|
fill_missing_fields |
当数据导入时,若数据源文件中一行的最后一个字段缺失的处理方式。 |
取值范围:true/on,false/off。缺省值为false/off。
|
ignore_extra_data |
若数据源文件比外表定义列数多,是否会忽略对多出的列。该参数只在数据导入过程中使用。 |
取值范围:true/on,false/off。缺省值为false/off。
注意:
如果行尾换行符丢失,使两行变成一行时,设置此参数为true将导致后一行数据被忽略掉。 |
reject_limit |
指定本次数据导入允许出现的数据格式错误个数,当导入过程中出现的数据格式错误未达到限定值时,本次数据导入可以成功。
说明:
此语法建议用PER NODE REJECT LIMIT 'value'替代。 数据格式错误是指缺少或者多出字段值,数据类型错误或者编码错误。对于非数据格式错误,一旦发生就将导致整个数据导入失败。 |
取值范围:正整型值、unlimited(无限制)。指定正整型参数时需要添加单引号。 不指定该参数时,有错误信息立即返回。 |
compatible_illegal_chars |
导入导出非法字符容错参数。此语法对READ ONLY和WRITE ONLY的外表有效。 导出容错仅8.1.3.331及以上版本集群支持。 |
取值范围:true/on,false/off。缺省值为false/off。
|
replace_illegal_chars |
导入非法字符容错参数,用于将'\0'替换为指定字符串。此语法仅对READ ONLY的外表有效。 |
指定用于替换'\0'的字符串有如下约束:
|
WITH error_table_name |
数据导入过程中出现的数据格式错误信息将被写入error_table_name指定的错误信息表中,可以在并行导入结束后查询此错误信息表,获取详细的错误信息。此参数只在设置了reject_limit参数时有效。
说明:
如果为了兼容postgres开源接口,此语法建议用LOG INTO代替。 |
字符串,要符合标识符的命名规范。 |
LOG INTO error_table_name |
数据导入过程中出现的数据格式错误信息将被写入error_table_name指定的错误信息表中,可以在并行导入结束后查询此错误信息表,获取详细的错误信息。
说明:
若没有指定PER NODE REJECT LIMIT参数,则此参数不起作用。 |
字符串,要符合标识符的命名规范。 |
REMOTE LOG 'name' |
数据导入过程中出现的数据格式错误信息将被写到GDS端以文件方式保存。name为错误数据文件的文件名前缀。 |
- |
PER NODE REJECT LIMIT 'value' |
指定本次数据导入过程中每个DN实例上允许出现的数据格式错误的数量,如果有一个DN实例上的错误数量大于设定值,本次导入失败,报错退出。
注意:
此语法指定的是单个节点的错误容忍度。 数据格式错误是指缺少或者多出字段值,数据类型错误或者编码错误。对于非数据格式错误,一旦发生就将导致整个数据扫描失败。 |
整型值,unlimited(无限),不指定该参数时,有错误信息立即返回。 |
参数 |
描述 |
取值范围 |
---|---|---|
file_sequence |
用于多任务GDS外表并行导入,提升单个文件的导入性能。该参数仅供数据导入使用。 格式为file_sequence '文件被拆分的总数-当前分片' 。例如: file_sequence '3-1' 表示导入的文件在逻辑上被拆分成3份,当前外表导入的数据为第一个分片上的数据。 file_sequence '3-2' 表示导入的文件在逻辑上被拆分成3份,当前外表导入的数据为第二个分片上的数据。 file_sequence '3-3' 表示导入的文件在逻辑上被拆分成3份,当前外表导入的数据为第三个分片上的数据。 |
使用该参数需遵循以下约束:
使用CSV格式进行并行导入时,在某些场景中会因为CSV本身的规则和GDS拆分逻辑冲突而导致其中的某些分片导入失败,详情参见使用file_sequence参数异常场景说明。 |
示例
创建外表customer_ft,用来以TEXT格式导入GDS服务器10.10.123.234上的数据:
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 |
CREATE FOREIGN TABLE customer_ft ( c_customer_sk integer , c_customer_id char(16) , c_current_cdemo_sk integer , c_current_hdemo_sk integer , c_current_addr_sk integer , c_first_shipto_date_sk integer , c_first_sales_date_sk integer , c_salutation char(10) , c_first_name char(20) , c_last_name char(30) , c_preferred_cust_flag char(1) , c_birth_day integer , c_birth_month integer , c_birth_year integer , c_birth_country varchar(20) , c_login char(13) , c_email_address char(50) , c_last_review_date char(10) ) SERVER gsmpp_server OPTIONS ( location 'gsfs://10.10.123.234:5000/customer1*.dat', FORMAT 'TEXT' , DELIMITER '|', encoding 'utf8', mode 'Normal') READ ONLY; |
创建外表foreign_HR_staffS_ft,用来以TEXT格式导入GDS服务器192.168.0.90和192.168.0.91上的数据,导入过程错误信息将记录到err_HR_staffS中。本次数据导入允许出现的数据格式错误个数为2。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE FOREIGN TABLE foreign_HR_staffS_ft ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', format 'TEXT', delimiter E'\x08', null '',reject_limit '2') WITH err_HR_staffS_ft; |
建立外表,用来以CSV格式导入input_data目录下存放在各个节点名文件下的所有文件。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE FOREIGN TABLE foreign_HR_staffS_ft1 ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'file:///input_data/*', format 'csv', quote E'\x08', mode 'private', delimiter ',') WITH err_HR_staffS_ft1; |
建立外表,用来以CSV格式导出数据到output_data目录下。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE FOREIGN TABLE foreign_HR_staffS_ft2 ( staff_ID NUMBER(6) , FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4) ) SERVER gsmpp_server OPTIONS (location 'file:///output_data/', format 'csv', quote E'\x08', delimiter '|', header 'on') WRITE ONLY; |
使用file_sequence参数异常场景说明
使用CSV格式进行并行导入时,在如下示例场景中会因为CSV本身的规则和GDS拆分逻辑冲突而导致其中的某些分片导入失败。
场景:csv文件中包含未转义的换行符,且该换行符被quote指定的字符所包含,并且该行数据处于逻辑分片的第一行。
示例:并行导入一个文件big.csv ,正确导入显示内容如下:
--id, username, address 10001,"customer1 name","Rose District" 10002,"customer2 name"," 23 Road Rose District NewCity" 10003,"customer3 name","NewCity"
文件被拆分成两份后,第一个分片显示内容如下:
10001,"customer1 name","Rose District" 10002,"customer2 name"," 23
第二个分片显示内容如下:
Road Rose District NewCity" 10003,"customer3 name","NewCity"
因为第二个分片第一行后面的换行符包含在一个双引号之间,导致GDS无法分辨该换行符是字段中的换行符还是行中的分隔符,因此第一个分片会成功导入两条数据,第二个分片导入失败。