# CREATE FOREIGN TABLE (OBS导入导出) #### 功能描述 在当前数据库创建一个OBS外表，用于OBS数据并行导入导出。该方式不需要用户再额外创建外部服务器server，使用数据库默认创建的gsmpp_server即可。 本语法功能仅支持OBS桶上的**TEXT和CSV格式** 数据，如果需要处理**ORC、CARBONDATA、PARQUET** 等格式，请参见[CREATE FOREIGN TABLE (SQL on OBS or Hadoop)](https://support.huaweicloud.com/sqlreference-dws/dws_06_0161.html)，且需要额外创建外部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权限的普通用户可执行外表操作。 ``` ALTER USER user_name USEFT; ``` - OBS外表不需要显式指定分布方式，默认支持ROUNDROBIN分布方式。 - 所创建外表只对[信息约束（Informational Constraint）](https://support.huaweicloud.com/sqlreference-dws/dws_06_0161.html#ZH-CN_TOPIC_0000001811634745__s0b7a85d0acff48e79ada2f91d1e79a0f)约束生效。 - 不支持OBS桶中带中文路径的场景。 表1OBS外表支持读写格式说明 | 数据类型 | DIST_FDW || |:---|:---|---| | - | READ ONLY | WRITE ONLY | | ORC | × | × | | PARQUET | × | × | | CARBONDATA | × | × | | TEXT | √ | √ | | CSV | √ | √ | | JSON | × | × | #### 语法格式 ``` 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为： ``` [CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED] ``` - 其中table_constraint为： ``` [CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} (column_name) [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED] ``` #### 参数说明 表2CREATE FOREIGN TABLE (OBS导入导出)参数说明 | 参数 | 描述 | 取值范围或示例 | |:---|:---|:---| | IF NOT EXISTS | 如果已经存在相同名称的表，系统不会报错，而会发出一个通知，告知表关系已存在。 | - | | table_name | 外表的表名。 | 字符串，要符合标识符的命名规范。参见[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | column_name | 外表中的字段名。 | 字符串，要符合标识符的命名规范。参见[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | type_name | 字段的数据类型。 | - | | SERVER server_name | 外表的server名字。对于导入导出的OBS外表，可以使用初始数据库默认创建的gsmpp_server，也可以使用自定义的server。 | - 如果使用自定义server，FOREIGN DATA WRAPPER应该为dist_fdw。 - 8.2.0及以上集群版本支持在自定义dist_fdw server中指定OBS访问相关参数：access_key、secret_access_key和security_token。当server中指定以上参数时，外表中不需要再重复指定。 | | OPTIONS | 用于指定外表数据的各类参数。 | - 数据格式参数，参见[表3]。 - 容错性参数，参见[表4]。 - 性能参数，参见[表5]。 | | 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 | 指定所建立的约束为信息约束，即数据库不会强制验证该约束，这个选项与后续的ENABLE QUERY OPTIMIZATION搭配使用，主要有以下作用： - **仅为信息约束**：通过 PRIMARY KEY 或 UNIQUE 声明列的唯一性约束，帮助优化查询执行计划。 - **不强制验证**：数据库不会检查外部数据源中的数据是否实际满足约束条件（如是否存在重复值）。 - **性能优化**：允许查询优化器利用约束信息生成更高效的执行计划，但需用户自行确保数据符合约束，否则可能导致查询结果不准确。 - **风险**：若实际数据违反约束（如存在重复值），可能导致查询结果错误（如聚合结果异常、连接产生多余记录）。 更多信息参见[信息约束（Informational Constraint）](https://support.huaweicloud.com/sqlreference-dws/dws_06_0161.html#ZH-CN_TOPIC_0000001811634745__s0b7a85d0acff48e79ada2f91d1e79a0f)。 | 例如声明primary key，但是不强制验证。 ``` CREATE FOREIGN TABLE hdfs_users ( user_id INT PRIMARY KEY NOT ENFORCED, ... ``` | | ENFORCED | 数据库强制验证约束，预留参数，目前DWS不支持设置为ENFORCED。 | - | | PRIMARY KEY (column_name) | 指定所建立的信息约束位于column_name列上。 | 字符串，需符合标识符的命名规范，并且这个column_name必须存在。 | | ENABLE QUERY OPTIMIZATION | 允许查询优化器利用信息约束生成更高效的执行计划，与前面的NOT ENFORCED搭配使用。 | 创建外表，为字段名p_partkey int增加唯一约束，但是不强制验证该约束，且允许优化器利用该信息约束生成更优执行计划： ``` CREATE FOREIGN TABLE ft_part ( p_partkey int UNIQUE NOT ENFORCED ENABLE QUERY OPTIMIZATION, ... ``` | | DISABLE QUERY OPTIMIZATION | 禁止查询优化器利用信息约束生成高效的执行计划。 | - | 表3OPTIONS数据格式参数说明 | 参数 | 描述 | 取值范围或示例 | |:---|:---|:---| | encrypt | 数据传输过程中使用HTTPS，否则使用HTTP，默认off。 | - | | access_key | OBS访问协议对应的AK值（由用户从服务界面上用户信息里获取），创建外表时AK值不会加密保存到数据库的元数据表中。创建外表时不校验该正确性。 AK可参见[访问密钥](https://support.huaweicloud.com/usermanual-ca/ca_01_0003.html)获取。 | - | | secret_access_key | OBS访问协议对应的SK值（由用户从服务界面上用户信息里获取），创建外表时SK值会加密保存到数据库的元数据表中。创建外表时不校验该正确性。 SK可参见[访问密钥](https://support.huaweicloud.com/usermanual-ca/ca_01_0003.html)获取。 | - | | security_token | 对应统一身份认证服务IAM中临时安全凭证的SecurityToken值，与临时AK、SK共同组成临时安全凭证，临时安全凭证有效期不超过24小时。该参数仅8.2.0及以上集群版本支持。 注意： - 该参数仅8.2.0及以上集群版本支持。 - 使用该参数时，access_key和secret_access_key参数分别对应临时AK、SK。 | - | | chunksize | 在DN中每个OBS读取线程的缓存大小，可指定范围8\~512，默认大小为64，单位为MB。 | - | | location | OBS外表的数据源位置，即OBS数据的存放目录，目前支持URL描述，格式为'obs://OBS桶名/文件夹名'，多个URL使用'\|'分割。应确保OBS桶与DWS集群在同一个区域，不支持跨区访问OBS桶数据。 使用该参数请参见[location参数说明]。 | 举例如下： ``` OPTIONS ( encoding 'utf8', location 'obs:///traffic-data/gcxx', format 'text', ... ``` | | 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。DWS只支持CSV和TEXT格式。 - CSV（逗号分隔文件格式）： - 格式的文件，可以有效处理数据列中的换行符，但对一些特殊字符处理有欠缺。 - 由记录组成，每条记录被分隔符分隔为字段，且每条记录都有同样的字段序列。 - TEXT（文本格式）： 由换行符区分每条记录，由分隔符区分每个字段。可以有效处理一些特殊字符，但无法正确处理数据列中的换行符。 | | header | 指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息。 | OBS导出数据时不支持该参数为true，使用缺省值false，不需要设置，表示导出的数据文件第一行不是标题行（即表头）。 在导入数据时，如果header选项为on，则数据文件中第一行会被识别为标题行，会忽略此行。如果header为off，而数据文件中第一行会被识别为数据。 取值范围：true/on，false/off。缺省值为false/off。 | | delimiter | 指定数据文件行数据的字段分隔符，不指定则使用默认分隔符，TEXT格式的默认分隔符是水平制表符（tab），CSV格式的默认分隔符为","。 | 支持多字符分隔符，但分隔符不能超过10个字节。 - TEXT格式，分隔符不能是\\r和\\n。 - 分隔符不能和null参数相同，CSV格式数据的分隔符不能和quote参数相同。 - TEXT格式数据的分隔符不能包含：字母、数字以及特殊字符"\\"和"."。 - 数据文件中单行数据长度需\<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。 - 分隔符推荐使用多字符（例如'$\^\&'）和不可见字符（例如0x07、0x08、0x1b等）。 | | quote | CSV格式文件下的引号字符，缺省值为双引号。 | - quote参数不能和分隔符、null参数相同。 - quote参数只能是单字节的字符。 - 推荐不可见字符作为quote，例如0x07，0x08，0x1b等。 | | escape | CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。 | 缺省值为双引号。当与quote值相同时，会被替换为'\\0'。 | | null | 用来指定数据文件中空值的表示方式。 | - 在TEXT格式下缺省值是\\N。 - CSV格式下缺省值是一个没有引号的空字符串。 - null的值不能是\\r和\\n，最大为100个字符。 - null值不能和分隔符、quote参数相同。 | | noescaping | TEXT格式下，开启后不对'\\'和后面的字符进行转义。 | noescaping参数只在TEXT格式下有效。 true/on，false/off。缺省值为false/off。 | | encoding | 指定数据文件的编码格式名称，即需要以何编码格式对数据文件进行解析和校验/输出文件为何种编码格式。缺省值为当前数据库的默认客户端编码格式，即client_encoding。 | 导入外表此处强烈建议指定为文件的编码格式，或根据文件的字符集在导入前对client_encoding进行设置。否则可能会导致不必要的解析、校验错误以及其导致的导入报错回滚，甚至非法数据入库。导出外表同样希望指定此选项，以避免导出采用默认字符集设置时与预期不符。 在创建外表时此选项未指定，会在客户端给出对应Warning信息。 注意： - 目前OBS导入外表不支持解析带有多种字符集编码格式混合的文件。 - 目前OBS导出外表不支持写出带有多种字符集编码格式混合的文件。 | | eol | 指定导入导出数据文件换行符样式。 | 支持多字符换行符，但换行符不能超过10个字节。常见的换行符，如\\r、\\n、\\r\\n（设成0x0D、0x0A、0x0D0A效果是相同的），其他字符或字符串，如$、#。 - eol参数只能用于TEXT格式的导入导出，不支持CSV格式。 - eol参数不能和分隔符、null参数相同。 - eol参数不能包含：数字，字母和符号"."。 | | date_format | 导入对于DATE类型指定格式。此语法仅对READ ONLY的外表有效。 | 合法DATE格式。可参考[时间日期函数和操作符](https://support.huaweicloud.com/sqlreference-dws/dws_06_0035.html)。 说明： 对于指定为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 | 表4OPTIONS容错性参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | 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 | 指定本次数据导入允许出现的数据格式错误个数，当导入过程中出现的数据格式错误未达到限定值时，本次数据导入可以成功。 | 整型值、unlimited（无限制）。 不指定该参数时，有错误信息立即返回。 注意： 此语法建议用PER NODE REJECT LIMIT 'value'替代。 数据格式错误是指缺少或者多出字段值，数据类型错误或者编码错误。对于非数据格式错误，一旦发生就将导致整个数据导入失败。 | | force_save_err | 表示是否在导入报错退出后，保存错误信息至错误表中。 | true/on，false/off。缺省值为false/off。 与reject_limit配合使用，若该参数开启： - reject_limit不指定时，保留一条错误记录至错误表中。 - reject_limit设置为N，保留N+1条错误记录至错误表中。 | | obs_null_file | 用于DWS和OBS之间空文件导入导出的处理。 注意： - 该参数仅8.2.1及以上版本支持。 - obs_null_file为true/on时，导出时若导出目录只有"_SUCCESS"空文件时，可以重复导出，obs_null_file为false/off时不能。 - obs_null_file为true/on时，多桶导入，对于第一个文件不存在的路径进行报错。 | true/on，false/off。缺省值为false/off。 当obs_null_file为true/on时： - DWS导出空表时，生成名字为"_SUCCESS"的空文件，表示导出成功；导出非空表时，生成本身的文件+"_SUCCESS"空文件。 - DWS导入文件时，如果文件不存在或者路径错误，会显示如下错误信息。 ``` No such file or directory: 'XXX' ``` | | compatible_illegal_chars | 导入非法字符容错参数。此语法仅对READ ONLY的外表有效。 | true/on，false/off。缺省值为false/off。 - 参数为true/on，则导入时遇到非法字符进行容错处理，非法字符转换后入库，不报错，不中断导入。 - 参数为false/off，导入时遇到非法字符进行报错，中断导入。 - 导入非法字符容错规则如下： 1. 对于'\\0'，容错后转换为空格； 2. 对于其他非法字符，容错后转换为问号； 3. 若compatible_illegal_chars为true/on，表示导入时对于非法字符进行容错处理，若NULL、DELIMITER、QUOTE、ESCAPE设置为空格或问号则会通过如"illegal chars conversion may confuse COPY escape 0x20"等报错信息提示用户修改可能引起混淆的参数，以避免导入错误。 注意： Windows平台下OBS若按照文本格式读取数据文件，遇到0x1A会作为EOF符号结束数据读入造成解析错误，这是Windows平台的实现约束。由于OBS不支持BINARY形式读取，可将相应数据文件交由Linux平台下的OBS读取。 | 表5OPTIONS性能参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | file_split_threshold | TEXT格式导入性能优化参数，含义是指定文件逻辑分块的大小下限。当指定该参数时，会根据实际文件和DN状况，通过对大文件进行拆分来提高导入并发度，最终目的是让各个DN的任务均衡分布。因此适合文件数少于DN数或文件大小不均衡的导入场景。 | 0\~2147483647，单位MB。默认为0，不会拆分文件。 - 该参数仅8.2.0及以上版本支持。 - 该参数仅支持TEXT格式READ ONLY外表。 - 该参数的含义是指定文件逻辑分块的大小下限，因此文件不一定按照该参数切块，但切块大小一定不会小于file_split_threshold。 例如：当前文件大小为1024MB，DN数为4。当指定file_split_threshold小于256时，文件会被均匀的分成4块，每个DN分配一个256MB的文件导入任务；当指定file_split_threshold为500时，由于用户明确告知不希望分块小于500MB，因此最终文件被拆分成500MB和524MB，分配给2个DN。多文件时该参数仍适用。 - 除非有明确的分块大小需求，一般建议设置较小的值（例如10），否则可能影响并发效果。 | #### 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单桶多文件导入，根据文件名前缀进行匹配，匹配到的文件都会被导入。 例如，有以下两个数据文件，只要在"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"默认按目录处理。如果仅指定到自定义名称的文件，则导出时会以该文件为名称创建目录，然后再生成导出文件。文件名由DWS自动生成。 - 导出时"location"只支持一个路径。 #### 示例 创建外表OBS_ft，用来以txt格式导入OBS上指定的对象数据到row_tbl表中： ![](https://support.huaweicloud.com/sqlreference-dws/public_sys-resources/caution_3.0-zh-cn.png) 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。 ``` 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_informational_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 IF EXISTS 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)](https://support.huaweicloud.com/sqlreference-dws/dws_06_0124.html)，[DROP FOREIGN TABLE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0192.html) **最佳实践** ：[使用DWS秒级查询交通卡口通行车辆行驶路线](https://support.huaweicloud.com/bestpractice-dws/dws_05_0043.html)