文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(8.1.3.x)/ DDL语法/ CREATE FOREIGN TABLE (OBS导入导出)
更新时间：2024-10-25 GMT+08:00
查看PDF
分享

CREATE FOREIGN TABLE (OBS导入导出)

功能描述

在当前数据库创建一个外表，用于OBS数据并行导入导出。该方式使用的SERVER为数据库默认创建的gsmpp_server。

实时数仓（单机部署）暂不支持OBS外表导入导出功能。

注意事项

  • 这种方式仅支持TEXT和CSV格式，并且需要额外指定OBS连接信息。对于OBS上的ORC、Carbondata等格式数据，不适用这种方式，请参考CREATE FOREIGN TABLE (SQL on OBS or Hadoop)。
  • 创建的外表分为只读外表（READ ONLY）和只写外表（WRITE ONLY），缺省为只读外表。数据导入集群时，请将外表设为READ ONLY；导出时，请设为WRITE ONLY。
  • 外表由命令执行者所有；
  • OBS外表不需要显式指定分布方式，默认支持ROUNDROBIN分布方式；
  • 所创建外表只对信息约束（Informational Constraint）约束生效。
  • OBS导入导出数据时，不支持中文路径。
表1 OBS外表支持读写格式说明

数据类型

自建Server

gsmpp_server

-

READ ONLY

WRITE ONLY

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 gsmpp_server 
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 gsmpp_server

    外表的server名字。对于导入导出的OBS外表，其server是初始数据库默认创建的，即gsmpp_server。

  • OPTIONS ( { option_name ' value ' } [, ...] )

    用于指定外表数据的各类参数。

    • encrypt

      数据传输过程中使用HTTPS，否则使用HTTP，默认off。

    • access_key

      OBS访问协议对应的AK值（由用户从服务界面上用户信息里获取），创建外表时AK值会加密保存到数据库的元数据表中。

    • secret_access_key：

      OBS访问协议对应的SK值（由用户从服务界面上用户信息里获取），创建外表时SK值会加密保存到数据库的元数据表中。

    • chunksize

      在DN中每个OBS读取线程的缓存大小，可指定范围8~512，默认大小为64，单位为MB。

    • location

      外表的数据源位置，目前支持URL描述。多个URL使用‘|’分割。

      • 对于只读外表（默认为只读）的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单桶多文件导入，根据文件名前缀进行匹配，匹配到的文件都会被导入。
        例如，有以下两个数据文件，只要在“location”中指定前缀mybucket/input_data/product_info就能识别并导入这两个文件。 
        mybucket/input_data/product_info.0
mybucket/input_data/product_info.1
      • 导入时如果指定到文件名，例如“1.csv”，那么在此文件的桶或目录存在此名称为前缀的其他文件，也会被导入。即“1.csv1”、“1.csv22”等等，都会被自动导入。
      • 导入时，“location”中如果使用obs方式，支持多个url，并且用‘|’分隔；如果使用gsobs方式，则不支持多个路径。
      • 导出时“location”默认按目录处理。如果仅指定到自定义名称的文件，则导出时会以该文件为名称创建目录，然后再生成导出文件。文件名由GaussDB(DWS)自动生成。
      • 导出时“location”只支持一个路径。
    • region

      可选参数，region参数指定regionCode，regionCode为云上的region信息。

      若显式指定此参数，域名信息将会读取指定的region参数；若此参数不指定，则读取defaultRegion的值，即安装集群时指定的region。

      TEXT、CSV格式的OBS导入导出外表格式参数使用说明如下：

      • location参数必选，其中前缀gsobs、obs均支持，都识别为OBS的信息，若为gsobs时，其中包含obs url、bucket、prefix，若为obs时则表示bucket、prefix。
      • 多个桶的数据源数据之间使用分隔符‘|’进行分割，LOCATION 'obs://bucket1/folder/ | obs://bucket2/'，数据库将会扫描指定路径文件夹下面的所有对象。
    • format

      外表中数据源文件的格式。

      取值范围：CSV、TEXT，缺省值为TEXT。GaussDB(DWS)只支持CSV和TEXT格式。

      • CSV（逗号分隔文件格式）：
        • 格式的文件，可以有效处理数据列中的换行符，但对一些特殊字符处理有欠缺。
        • 由记录组成，每条记录被分隔符分隔为字段，且每条记录都有同样的字段序列。
      • TEXT（文本格式）：
        • 由换行符区分每条记录，由分隔符区分每个字段。可以有效处理一些特殊字符，但无法正确处理数据列中的换行符。
    • header

      指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息。

      OBS导出数据时不支持该参数为true，使用缺省值false，不需要设置，表示导出的数据文件第一行不是标题行（即表头）。

      在导入数据时，如果header选项为on，则数据文件中第一行会被识别为标题行，会忽略此行。如果header为off，而数据文件中第一行会被识别为数据。

      取值范围：true/on，false/off。缺省值为false/off。

    • delimiter

      指定数据文件行数据的字段分隔符，不指定则使用默认分隔符，TEXT格式的默认分隔符是水平制表符（tab），CSV格式的默认分隔符为“,”。

      • TEXT格式，分隔符不能是\r和\n。
      • 分隔符不能和null参数相同，CSV格式数据的分隔符不能和quote参数相同。
      • TEXT格式数据的分隔符不能包含：字母、数字以及特殊字符“\”和“.”。
      • 数据文件中单行数据长度需<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。
      • 分隔符推荐使用多字符（例如'$^&'）和不可见字符（例如0x07、0x08、0x1b等）。

      取值范围：

      支持多字符分隔符，但分隔符不能超过10个字节。

    • quote

      CSV格式文件下的引号字符，缺省值为双引号。

      • quote参数不能和分隔符、null参数相同。
      • quote参数只能是单字节的字符。
      • 推荐不可见字符作为quote，例如0x07，0x08，0x1b等。
    • escape

      CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。

      缺省值为双引号。当与quote值相同时，会被替换为'\0'。

    • null
      用来指定数据文件中空值的表示方式。
      • null的值不能是\r和\n，最大为100个字符。
      • null值不能和分隔符、quote参数相同。

      取值范围：

      • 在TEXT格式下缺省值是\N。
      • CSV格式下缺省值是一个没有引号的空字符串。
    • noescaping

      TEXT格式下，开启后不对'\'和后面的字符进行转义。

      noescaping参数只在TEXT格式下有效。

      取值范围：true/on，false/off。缺省值为false/off。

    • encoding

      指定数据文件的编码格式名称，即需要以何编码格式对数据文件进行解析和校验/输出文件为何种编码格式。缺省值为当前数据库的默认客户端编码格式，即client_encoding。

      导入外表此处强烈建议指定为文件的编码格式，或根据文件的字符集在导入前对client_encoding进行设置。否则可能会导致不必要的解析、校验错误以及其导致的导入报错回滚，甚至非法数据入库。导出外表同样希望指定此选项，以避免导出采用默认字符集设置时与预期不符。

      在创建外表时此选项未指定，会在客户端给出对应Warning信息。

      • 目前OBS导入外表不支持解析带有多种字符集编码格式混合的文件。
      • 目前OBS导出外表不支持写出带有多种字符集编码格式混合的文件。
    • fill_missing_fields

      当数据导入时，若数据源文件中一行的最后一个字段缺失的处理方式。

      取值范围：true/on，false/off。缺省值为false/off。

      • 参数为true/on，当数据导入时，若数据源文件中一行数据的最后一个字段缺失，则把最后一个字段的值设置为NULL，不报错。
      • 参数为false/off，如果最后一个字段缺失会显示如下错误信息。 
        missing data for column "tt"
    • ignore_extra_data

      数据源文件中的字段比外表定义列数多时，是否忽略多出的列。该参数只在数据导入过程中使用。

      取值范围：true/on，false/off。缺省值为false/off。

      • 参数为true/on，若数据源文件比外表定义列数多，则忽略行尾多出来的列。
      • 参数为false/off，若数据源文件比外表定义列数多，会显示如下错误信息。 
        extra data after last expected column

      如果行尾换行符丢失，使两行变成一行时，设置此参数为true将导致后一行数据被忽略掉。

    • reject_limit

      指定本次数据导入允许出现的数据格式错误个数，当导入过程中出现的数据格式错误未达到限定值时，本次数据导入可以成功。

      此语法建议用PER NODE REJECT LIMIT 'value'替代。

      数据格式错误是指缺少或者多出字段值，数据类型错误或者编码错误。对于非数据格式错误，一旦发生就将导致整个数据导入失败。

      取值范围：整型值、unlimited（无限制）。

      不指定该参数时，有错误信息立即返回。

    • eol

      指定导入导出数据文件换行符样式。

      取值范围：支持多字符换行符，但换行符不能超过10个字节。常见的换行符，如\r、\n、\r\n（设成0x0D、0x0A、0x0D0A效果是相同的），其他字符或字符串，如$、#。

      • eol参数只能用于TEXT格式的导入导出，不支持CSV格式。
      • eol参数不能和分隔符、null参数相同。
      • eol参数不能包含：数字，字母和符号“.”。
    • 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格式。

    • compatible_illegal_chars

      导入非法字符容错参数。此语法仅对READ ONLY的外表有效。

      取值范围：true/on，false/off。缺省值为false/off。

      • 参数为true/on，则导入时遇到非法字符进行容错处理，非法字符转换后入库，不报错，不中断导入。
      • 参数为false/off，导入时遇到非法字符进行报错，中断导入。

      Windows平台下OBS若按照文本格式读取数据文件，遇到0x1A会作为EOF符号结束数据读入造成解析错误，这是Windows平台的实现约束。由于OBS不支持BINARY形式读取，可将相应数据文件交由Linux平台下的OBS读取。

      导入非法字符容错规则如下：

      1. 对于'\0'，容错后转换为空格；
      2. 对于其他非法字符，容错后转换为问号；
      3. 若compatible_illegal_chars为true/on标识导入时对于非法字符进行容错处理，则若NULL、DELIMITER、QUOTE、ESCAPE设置为空格或问号则会通过如“illegal chars conversion may confuse COPY escape 0x20”等报错信息提示用户修改可能引起混淆的参数以避免导入错误。
    • bom

      标识CSV文件是否包含utf8 BOM字段。

      取值范围：true/on，false/off。

      缺省值：false

      仅在只读外表且文件编码为utf8时生效。

  • 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参数，则此参数不起作用。
    • 该参数指定时，错误表自动创建。

    取值范围：字符串，要符合标识符的命名规范。

  • PER NODE REJECT LIMIT 'value'

    指定本次数据导入过程中每个DN实例上允许出现的数据格式错误的数量，如果有一个DN实例上的错误数量大于设定值，本次导入失败，报错退出。

    此语法指定的是单个节点的错误容忍度。

    数据格式错误是指缺少或者多出字段值，数据类型错误或者编码错误。对于非数据格式错误，一旦发生就将导致整个数据扫描失败。

    取值范围：整型值，unlimited（无限），不指定该参数时，有错误信息立即返回。

  • NOT ENFORCED

    指定所建立的约束为信息约束，该约束不由数据库来保证，而由用户来保证。

  • ENFORCED

    ENFORCED为默认值。预留参数，目前对于ENFORCED不支持。

  • PRIMARY KEY (column_name)

    指定所建立的信息约束位于column_name列上。

    取值范围：字符串，要符合标识符的命名规范，并且这个column_name必须存在。

  • ENABLE QUERY OPTIMIZATION

    利用信息约束对查询计划进行优化。

  • DISABLE QUERY OPTIMIZATION

    禁止利用信息约束对查询计划优化。

示例

创建外表OBS_ft，用来以txt格式导入OBS上指定的对象数据到row_tbl表中：

认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。

1
2
3
4
5
6
7
8
9
 
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;

相关链接

ALTER FOREIGN TABLE (For HDFS or OBS)，DROP FOREIGN TABLE

父主题： DDL语法