CREATE FOREIGN TABLE (OBS导入导出)
功能描述
在当前数据库创建一个OBS外表,用于OBS数据并行导入导出。该方式不需要用户再额外创建外部服务器server,使用数据库默认创建的gsmpp_server即可。
本语法功能仅支持OBS桶上的TEXT和CSV格式数据,如果需要处理ORC、CARBONDATA、PARQUET等格式,请参见CREATE FOREIGN TABLE (SQL on OBS or Hadoop),且需要额外创建外部server。
外表(Foreign Table)是数据库或大数据平台中一种虚拟表结构,它不实际存储数据,而是通过元数据信息(如表结构、存储位置、文件格式等)将外部存储系统(如HDFS、OBS等)中的数据映射到数据库内部,使用户能够以标准SQL操作直接访问或操作外部数据。外表的本质是一种“数据代理”,将外部文件系统的数据逻辑映射成数据库可识别的表结构。
注意事项
- 这种方式仅支持TEXT和CSV格式,并且需要额外指定OBS连接信息。
- 使用OBS外表访问OBS桶数据时,应确保DWS与OBS桶在同一个区域(Region)下,例如都在“中国-香港”下。
- 创建的外表分为只读外表(READ ONLY)和只写外表(WRITE ONLY),缺省为只读外表。导入OBS数据时,请将外表设为READ ONLY;将数据导出到OBS时,请设为WRITE ONLY。
- 仅系统管理员dbadmin角色或已授权了USEFT权限的普通用户可执行外表操作。
1
ALTER USER user_name USEFT;
- OBS外表不需要显式指定分布方式,默认支持ROUNDROBIN分布方式。
- 所创建外表只对信息约束(Informational Constraint)约束生效。
- 不支持OBS桶中带中文路径的场景。
数据类型 |
DIST_FDW |
|
---|---|---|
- |
READ ONLY |
WRITE ONLY |
ORC |
× |
× |
PARQUET |
× |
× |
CARBONDATA |
× |
× |
TEXT |
√ |
√ |
CSV |
√ |
√ |
JSON |
× |
× |
语法格式
1 2 3 4 5 6 7 8 |
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( { column_name type_name [column_constraint ] | LIKE source_table | table_constraint [, ...]} [, ...] ) SERVER server_name OPTIONS ( { option_name ' value ' } [, ...] ) [ { WRITE ONLY | READ ONLY }] [ WITH error_table_name | LOG INTO error_table_name] [PER NODE REJECT LIMIT 'value'] ; |
- 其中column_constraint为:
1 2 3
[CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]
- 其中table_constraint为:
1 2 3
[CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} (column_name) [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]
参数说明
参数 |
描述 |
取值范围或示例 |
||
---|---|---|---|---|
IF NOT EXISTS |
如果已经存在相同名称的表,系统不会报错,而会发出一个通知,告知表关系已存在。 |
- |
||
table_name |
外表的表名。 |
字符串,要符合标识符的命名规范。参见标识符命名规范。 |
||
column_name |
外表中的字段名。 |
字符串,要符合标识符的命名规范。参见标识符命名规范。 |
||
type_name |
字段的数据类型。 |
- |
||
SERVER server_name |
外表的server名字。对于导入导出的OBS外表,可以使用初始数据库默认创建的gsmpp_server,也可以使用自定义的server。 |
|
||
OPTIONS |
用于指定外表数据的各类参数。 |
|||
READ ONLY |
外表只读,该参数只供数据导入使用。 |
- |
||
WRITE ONLY |
外表只写。该参数只供数据导出使用。 |
- |
||
WITH error_table_name |
数据导入过程中出现的数据格式错误信息将被写入error_table_name指定的错误信息表中,可以在并行导入结束后查询此错误信息表,获取详细的错误信息。此参数只在设置了reject_limit参数时有效。 |
字符串,需符合标识符的命名规范。
注意:
如果为了兼容postgres开源接口,此语法建议用LOG INTO代替。该参数指定时错误表自动创建。 |
||
LOG INTO error_table_name |
数据导入过程中出现的数据格式错误信息将被写入error_table_name指定的错误信息表中,可以在并行导入结束后查询此错误信息表,获取详细的错误信息。 |
字符串,需符合标识符的命名规范。
|
||
PER NODE REJECT LIMIT 'value' |
指定本次数据导入过程中每个DN实例上允许出现的数据格式错误的数量,如果有一个DN实例上的错误数量大于设定值,本次导入失败,报错退出。 |
整型,unlimited(无限),不指定该参数时,有错误信息立即返回。
注意:
此语法指定的是单个节点的错误容忍度。 数据格式错误是指缺少或者多出字段值,数据类型错误或者编码错误。对于非数据格式错误,一旦发生就将导致整个数据扫描失败。 |
||
NOT ENFORCED |
指定所建立的约束为信息约束,即数据库不会强制验证该约束,这个选项与后续的ENABLE QUERY OPTIMIZATION搭配使用,主要有以下作用:
|
例如声明primary key,但是不强制验证。
|
||
ENFORCED |
数据库强制验证约束,预留参数,目前DWS不支持设置为ENFORCED。 |
- |
||
PRIMARY KEY (column_name) |
指定所建立的信息约束位于column_name列上。 |
字符串,需符合标识符的命名规范,并且这个column_name必须存在。 |
||
ENABLE QUERY OPTIMIZATION |
允许查询优化器利用信息约束生成更高效的执行计划,与前面的NOT ENFORCED搭配使用。 |
创建外表,为字段名p_partkey int增加唯一约束,但是不强制验证该约束,且允许优化器利用该信息约束生成更优执行计划:
|
||
DISABLE QUERY OPTIMIZATION |
禁止查询优化器利用信息约束生成高效的执行计划。 |
- |
参数 |
描述 |
取值范围或示例 |
||
---|---|---|---|---|
encrypt |
数据传输过程中使用HTTPS,否则使用HTTP,默认off。 |
- |
||
access_key |
OBS访问协议对应的AK值(由用户从服务界面上用户信息里获取),创建外表时AK值不会加密保存到数据库的元数据表中。创建外表时不校验该正确性。 AK可参见访问密钥获取。 |
- |
||
secret_access_key |
OBS访问协议对应的SK值(由用户从服务界面上用户信息里获取),创建外表时SK值会加密保存到数据库的元数据表中。创建外表时不校验该正确性。 SK可参见访问密钥获取。 |
- |
||
security_token |
对应统一身份认证服务IAM中临时安全凭证的SecurityToken值,与临时AK、SK共同组成临时安全凭证,临时安全凭证有效期不超过24小时。该参数仅8.2.0及以上集群版本支持。
注意:
|
- |
||
chunksize |
在DN中每个OBS读取线程的缓存大小,可指定范围8~512,默认大小为64,单位为MB。 |
- |
||
location |
OBS外表的数据源位置,即OBS数据的存放目录,目前支持URL描述,格式为'obs://OBS桶名/文件夹名',多个URL使用‘|’分割。应确保OBS桶与DWS集群在同一个区域,不支持跨区访问OBS桶数据。 使用该参数请参见location参数说明。 |
举例如下:
|
||
region |
可选参数,region参数指定regionCode,regionCode为云上的region信息。 若显式指定此参数,域名信息将会读取指定的region参数;若此参数不指定,则读取defaultRegion的值,即安装集群时指定的region。 |
TEXT、CSV格式的OBS导入导出外表格式参数使用说明如下:
|
||
format |
外表中数据源文件的格式。 |
CSV或TEXT。缺省值为TEXT。GaussDB(DWS)只支持CSV和TEXT格式。 |
||
header |
指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。 |
OBS导出数据时不支持该参数为true,使用缺省值false,不需要设置,表示导出的数据文件第一行不是标题行(即表头)。 在导入数据时,如果header选项为on,则数据文件中第一行会被识别为标题行,会忽略此行。如果header为off,而数据文件中第一行会被识别为数据。 取值范围:true/on,false/off。缺省值为false/off。 |
||
delimiter |
指定数据文件行数据的字段分隔符,不指定则使用默认分隔符,TEXT格式的默认分隔符是水平制表符(tab),CSV格式的默认分隔符为“,”。 |
支持多字符分隔符,但分隔符不能超过10个字节。
|
||
quote |
CSV格式文件下的引号字符,缺省值为双引号。 |
|
||
escape |
CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。 |
缺省值为双引号。当与quote值相同时,会被替换为'\0'。 |
||
null |
用来指定数据文件中空值的表示方式。 |
|
||
noescaping |
TEXT格式下,开启后不对'\'和后面的字符进行转义。 |
noescaping参数只在TEXT格式下有效。 true/on,false/off。缺省值为false/off。 |
||
encoding |
指定数据文件的编码格式名称,即需要以何编码格式对数据文件进行解析和校验/输出文件为何种编码格式。缺省值为当前数据库的默认客户端编码格式,即client_encoding。 |
导入外表此处强烈建议指定为文件的编码格式,或根据文件的字符集在导入前对client_encoding进行设置。否则可能会导致不必要的解析、校验错误以及其导致的导入报错回滚,甚至非法数据入库。导出外表同样希望指定此选项,以避免导出采用默认字符集设置时与预期不符。 在创建外表时此选项未指定,会在客户端给出对应Warning信息。
注意:
|
||
eol |
指定导入导出数据文件换行符样式。 |
支持多字符换行符,但换行符不能超过10个字节。常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。
|
||
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格式。 |
||
bom |
标识CSV文件是否包含utf8 BOM字段。 |
仅在只读外表且文件编码为utf8时生效。 取值范围:true/on,false/off。 缺省值:false |
参数 |
描述 |
取值范围 |
---|---|---|
fill_missing_fields |
当数据导入时,若数据源文件中一行的最后一个字段缺失的处理方式。 |
true/on,false/off。缺省值为false/off。
|
ignore_extra_data |
数据源文件中的字段比外表定义列数多时,是否忽略多出的列。该参数只在数据导入过程中使用。 |
true/on,false/off。缺省值为false/off。
注意:
如果行尾换行符丢失,使两行变成一行时,设置此参数为true将导致后一行数据被忽略掉。 |
reject_limit |
指定本次数据导入允许出现的数据格式错误个数,当导入过程中出现的数据格式错误未达到限定值时,本次数据导入可以成功。 |
整型值、unlimited(无限制)。 不指定该参数时,有错误信息立即返回。
注意:
此语法建议用PER NODE REJECT LIMIT 'value'替代。 数据格式错误是指缺少或者多出字段值,数据类型错误或者编码错误。对于非数据格式错误,一旦发生就将导致整个数据导入失败。 |
force_save_err |
表示是否在导入报错退出后,保存错误信息至错误表中。 |
true/on,false/off。缺省值为false/off。 与reject_limit配合使用,若该参数开启:
|
obs_null_file |
用于dws和obs之间空文件导入导出的处理。
注意:
|
true/on,false/off。缺省值为false/off。 当obs_null_file为true/on时:
|
compatible_illegal_chars |
导入非法字符容错参数。此语法仅对READ ONLY的外表有效。 |
true/on,false/off。缺省值为false/off。
注意:
Windows平台下OBS若按照文本格式读取数据文件,遇到0x1A会作为EOF符号结束数据读入造成解析错误,这是Windows平台的实现约束。由于OBS不支持BINARY形式读取,可将相应数据文件交由Linux平台下的OBS读取。 |
location参数说明
- 对于只读外表(默认为只读)的URL末尾可以指定到对象路径的前缀或直接指定到对象全路径。指定方式为obs://bucket/prefix。(其中,prefix是指对象路径的前缀。)例如:obs://mybucket/tpch/nation/
- 对于obs://bucket/prefix格式,若显式指定region参数时,域名信息将会读取指定的region参数;若region参数不指定,则读取defaultRegion的值,即安装集群时指定的region。
- 对于可写外表,URL不需要指定文件名。外部表数据源位置只可指定一个,并且要预先创建好对应目录。
- 对于只读外表不能使用多个相同的URL地址。
- 向外表中插入数据需要指定location。
- location参数中前缀gsobs、obs均支持,都识别为OBS的信息,若为gsobs时,其中包含obs url、bucket、prefix,若为obs时则表示bucket、prefix。
在实际导入导出数据时,location参数使用建议如下:
- 导入时“location”建议指定到具体文件名。如果仅指定到OBS桶或目录,则会导入其中的所有文本文件。当数据格式不正确时,则会报错。如果设置了容错,则容错表可能导入大量数据。
- 支持OBS单桶多文件导入,根据文件名前缀进行匹配,匹配到的文件都会被导入。
- 导入时如果指定到文件名,例如“1.csv”,那么在此文件的桶或目录存在此名称为前缀的其他文件,也会被导入。即“1.csv1”、“1.csv22”等,都会被自动导入。
- 导入时,“location”中如果使用obs方式,支持多个url,并且用‘|’分隔;如果使用gsobs方式,则不支持多个路径。
- 导出时“location”默认按目录处理。如果仅指定到自定义名称的文件,则导出时会以该文件为名称创建目录,然后再生成导出文件。文件名由GaussDB(DWS)自动生成。
- 导出时“location”只支持一个路径。
示例

认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险,建议在配置文件或者环境变量中密文存放,使用时解密,确保安全。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DROP FOREIGN TABLE IF EXISTS OBS_ft; CREATE FOREIGN TABLE OBS_ft ( a int, b int) SERVER gsmpp_server OPTIONS (location 'obs://gaussdbcheck/obs_ddl/test_case_data/txt_obs_informatonal_test001', format 'text', encoding 'utf8', chunksize '32', encrypt 'on', ACCESS_KEY 'access_key_value_to_be_replaced', SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', delimiter E'\x08') read only; DROP TABLE row_tbl; CREATE TABLE row_tbl( a int, b int); INSERT INTO row_tbl select * from OBS_ft; |