CREATE FOREIGN TABLE (SQL on OBS or Hadoop)_数据仓库服务 GaussDB(DWS)

功能描述

在当前数据库创建一个HDFS或OBS外表，用来访问存储在HDFS或者OBS分布式集群文件系统上的结构化数据。也可以导出ORC和PARQUET格式数据到HDFS或者OBS上。

数据存储在OBS：数据存储和计算分离，集群存储成本低，存储量不受限制，并且集群可以随时删除，但计算性能取决于OBS访问性能，相对HDFS有所下降，建议在数据计算不频繁场景下使用。

数据存储在HDFS：数据存储和计算不分离，集群成本较高，计算性能高，但存储量受磁盘空间限制，删除集群前需将数据导出保存，建议在数据计算频繁场景下使用。

说明：

单节点集群（单机部署）暂不支持HDFS外表。
单节点集群（单机部署）8.2.0.100及以上集群版本支持OBS外表，但需要指定Server的foreign data wrapper为DFS_FDW。
以下描述的读写外表，仅存算分离3.0版本支持。

注意事项

HDFS外表与OBS外表分为只读外表、只写外表和读写外表，只读外表用于查询操作，只写外表可以将GaussDB(DWS)中的数据导出到分布式文件系统中，读写外表可以查询和数据导出，其中读写外表仅9.1.0.100及以上版本支持。
此方式支持ORC、TEXT、CSV、CARBONDATA、PARQUET和JSON格式的导入查询，OBS外表支持ORC、PARQUET（仅存算分离3.0版本支持）格式的导出。HDFS外表支持ORC、PARQUET格式的导出。
该方式需要用户手动创建外部服务器，具体请参见CREATE SERVER。
若手动创建Server时指定foreign data wrapper为HDFS_FDW或者DFS_FDW，创建只读外表时需DISTRIBUTE BY子句指定分布方式。

表1 OBS外表支持读写格式说明
数据类型	DFS_FDW/HDFS_FDW
-	READ ONLY	WRITE ONLY	READ WRITE
ORC	√	√	√
PARQUET	√	√	√
CARBONDATA	√	×	×
TEXT	√	×	×
CSV	√	×	×
JSON	√	×	×

语法格式

创建外表。

    
       CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name 
( [ { column_name type_name 
    [ { [CONSTRAINT constraint_name] NULL |
    [CONSTRAINT constraint_name] NOT NULL |
      column_constraint [...]} ] |
      table_constraint [, ...]} [, ...] ] ) 
    SERVER server_name 
    OPTIONS ( { option_name ' value ' } [, ...] ) 
    [ {WRITE ONLY | READ ONLY | READ WRITE} ]
    DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
   
    [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ;

其中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]

参数说明

表2 CREATE FOREIGN TABLE (SQL on OBS or Hadoop)参数说明
参数	描述	取值范围
IF NOT EXISTS	如果已经存在相同名称的表，不会抛出一个错误，而会发出一个通知，告知表关系已存在。	-
table_name	外表的表名。	字符串，要符合标识符的命名规范。参见标识符命名规范。
column_name	外表中的字段名。可以选择多个字段名，中间用“,”隔开。	字符串，要符合标识符的命名规范。参见标识符命名规范。须知： JSON对象由嵌套或并列的name-value对组成，具有顺序无关性，当导入JSON格式数据时，需要通过字段名与name的自动对应来确定字段与value的对应关系。用户需要定义恰当的字段名，否则可能导致导入结果不符合预期。字段名与name的自动对应规则如下：无嵌套无数组的情况下，字段名应当与name一致，不区分大小写。字段名使用‘_’字符拼接两个name，标识嵌套关系。字段名使用‘#’字符加十进制非负整数‘n’标识数组的第n个元素（从0开始）。例如，要导入JSON对象{"A" : "simple", "B" : {"C" : "nesting"}, "D" : ["array", 2, {"E" : "complicated"}]}中的每个元素，外表字段名应当分别定义为a、b、b_c、d、d#0、d#1、d#2、d#2_e，字段的定义顺序不会影响导入结果的正确性。
type_name	字段的数据类型。	-
constraint_name	外表的表约束名。	-
{ NULL \| NOT NULL }	标识此列是否允许NULL值。在创建表时，对于列的约束NULL/NOT NULL，并不能保证该表在HDFS系统中的数据为NULL或者NOT NULL，数据的一致性由用户保证。所以需要由用户判断该列是否一定不为空或者一定为空，在建表的时候选用NULL或NOT NULL。(优化器对列为NULL/NOT NULL做了优化处理，会产生更优的计划。)	-
SERVER server_name	外表的server名字。允许用户自定义名字。	字符串，要符合标识符的命名规范，并且这个server必须存在。
OPTIONS	用于指定外表数据的各类参数。	不同场景下，需要指定的OPTIONS参数有所不同，说明如下。数据格式参数：参见表3。容错性参数：参见表4。仅Server类型为DLI时需要指定的参数：参见表5。仅format为hudi时需要指定的参数：参见表6。 OBS外表，各数据源格式支持的OPTIONS参数有所不同，具体参见表7。 HDFS外表，各数据源格式支持的OPTIONS参数有所不同，具体参见表8。
WRITE ONLY \| READ ONLY \| READ WRITE	指定创建的外表类型。	WRITE ONLY指定创建HDFS/OBS的只写外表。 READ ONLY指定创建HDFS/OBS的只读外表。 READ WRITE指定创建HDFS/OBS的读写外表。如果不指定创建的外表的类型，默认为只读外表。
DISTRIBUTE BY ROUNDROBIN	指定HDFS/OBS外表为ROUNDROBIN分布方式。	-
DISTRIBUTE BY REPLICATION	指定HDFS外表为REPLICATION分布方式。	-
PARTITION BY ( column_name ) AUTOMAPPED	column_name指定分区列。对于分区表，AUTOMAPPED表示HDFS分区外表指定的分区列会和HDFS数据中的分区目录信息自动对应，前提是必须保证HDFS分区外表指定分区列的顺序和HDFS数据中分区目录定义的顺序一致，该功能只适用于只读外表，只写外表不支持。	只写分区外表最多支持四级分区（即最多指定四个列为分区列）。且至少需要保留一列为非分区列。 HDFS只读外表支持text，csv，carbondata，orc，parquet格式分区表。 HDFS只写外表支持orc，parquet格式分区表。 HDFS读写外表支持orc，parquet格式分区表。 OBS只读外表/只写外表/读写外表均支持orc，parquet格式分区表。不支持浮点类型和布尔类型的列作为分区列。分区字段长度限制可通过guc参数 "dfs_partition_directory_length" 调整。分区目录名称由 "分区列名=分区列值" 组成。此名称内如果包含特殊字符还会经过转义，所以转义前推荐长度不要超过 (dfs_partition_directory_length + 1) / 3 ，以此保证其转义后总长度不会因为超过dfs_partition_directory_length而出现报错。不推荐包含过长中文字符的列作为分区列。因为一个中文字符和一个英文字符所占的空间大小并不一致，用户不易计算最终的分区目录名称长度，更容易触发超过dfs_partition_directory_length长度限制的报错。
CONSTRAINT constraint_name	用于指定外表所建立的信息约束（Informational Constraint）的名字。	字符串，要符合标识符的命名规范。
PRIMARY KEY	主键约束，表示表里的一个或者一些字段只能包含唯一（不重复）的非NULL值。一个表只能声明一个主键。	-
UNIQUE	唯一约束，表示表里的一个或者多个字段的组合必须在全表范围内唯一。对于唯一约束，NULL被认为是互相不等的。	-
NOT ENFORCED	指定所建立的约束为信息约束，该约束不由数据库来保证，而由用户来保证。	-
ENFORCED	ENFORCED为默认值。预留参数，目前对于ENFORCED不支持。	-
PRIMARY KEY (column_name)	指定所建立的信息约束位于column_name列上。	字符串，要符合标识符的命名规范，并且这个column_name必须存在。
ENABLE QUERY OPTIMIZATION	利用信息约束对执行计划进行优化。	-
DISABLE QUERY OPTIMIZATION	禁止利用信息约束对执行计划优化。	-

表3 OPTIONS数据格式参数说明
参数	描述	取值范围
header	指定导出数据文件是否包含标题行，标题行一般用来描述表中每个字段的信息。header只能用于CSV格式的文件中。如果header选项为on，则数据文件第一行会被识别为标题行，导出时会忽略此行。如果header为off，而数据文件中第一行会被识别为数据。	true/on，false/off。缺省值为false/off。
quote	CSV格式文件下的引号字符，缺省值为双引号。	quote参数不能和分隔符、null参数相同。 quote参数只能是单字节的字符。推荐不可见字符作为quote，例如0x07，0x08，0x1b等。
escape	CSV格式下，用来指定逃逸字符，逃逸字符只能指定为单字节字符。缺省值为双引号。当与quote值相同时，会被替换为'\0'。	-
location	OBS外表参数，指定存储在OBS上的文件路径，多个桶的数据源数据之间使用分隔符‘\|’进行分割，例如：LOCATION 'obs://bucket1/folder/ \| obs://bucket2/'，数据库将会扫描指定路径文件夹下面的所有对象。当访问DLI多版本表时，无需指定location参数。	-
format	外表中数据源文件的格式。	HDFS外表READ ONLY外表支持ORC、TEXT、JSON、CSV、PARQUET文件格式，WRITE ONLY/READ WRITE外表支持ORC和PARQUET文件格式。 OBS外表READ ONLY外表支持ORC、TEXT、JSON、CSV、CARBONDATA、PARQUET、HUDI文件格式，WRITE ONLY外表支持ORC和PARQUET文件格式。须知：对于JSON格式数据，仅支持JSON对象（object，最外层由{}构造）导入，不支持JSON数组（array，最外层由[]构造）导入，但支持JSON对象内部数组的导入。 Hudi即Apache Hudi，是一个事务性数据湖平台。当前版本支持将OBS外表的format参数指定为Hudi来访问相应的Hudi表数据集，并提供访问Hudi表增量/实时视图的方法。
foldername	外表中数据源文件目录，即表数据目录在HDFS文件系统和OBS上对应的文件目录。此选项对WRITE ONLY和READ WRITE可写外表为必选项，对READ ONLY外表为可选项。当访问DLI多版本表时，无需指定foldername参数。	-
encoding	外表中数据源文件的编码格式名称，缺省为utf8。此选项为可选参数。	-
totalrows	可选参数，估计表的行数，仅OBS外表使用。由于OBS上文件可能很多，做analyze可能会很慢，通过此参数让用户设置一个预估的值，使优化器能通过这个值做大小表的估计。一般预估值和实际值相近时，查询效率较高。	-
filenames	外表中数据源文件，以","间隔。	推荐通过使用foldername参数指定数据源的位置，对于只读外表filenames参数与foldername参数两者必有其一，而只写外表和读写外表只能通过foldername指定。 foldername为绝对目录时，前后必须有'/', 多个路径用', '分隔。查询分区表时，会先根据分区信息进行剪枝，然后查询满足条件的数据文件。由于剪枝操作会涉及多次扫描HDFS分区目录内容，不建议使用重复度非常小的列作为分区列，因为这可能导致分区目录非常的多，增加对HDFS的查询压力。 OBS只读外表和读写外表不支持。
delimiter	指定数据文件行数据的字段分隔符，不指定则使用默认分隔符，TEXT格式的默认分隔符是水平制表符（tab）。	支持多字符分隔符，但分隔符不能超过10个字节。分隔符不能是\r和\n。分隔符不能和null参数相同。分隔符不能包含“\”、“.”、数字和字母。数据文件中单行数据长度需<1GB，如果分隔符较长且数据列较多的情况下，会影响导出有效数据的长度。分隔符推荐使用多字符（例如'$^&'）和不可见字符（例如0x07、0x08、0x1b等）。 delimiter参数只在TEXT和CSV格式下有效。
eol	指定导入数据文件换行符样式。	支持多字符换行符，但换行符不能超过10个字节。常见的换行符，如\r、\n、\r\n（设成0x0D、0x0A、0x0D0A效果是相同的），其他字符或字符串，如$、#。 eol参数只能用于TEXT格式的导入。 eol参数不能和分隔符、null参数相同。 eol参数不能包含：数字，字母和符号“.”。
null	用来指定数据文件中空值的表示。	在TEXT格式下缺省值是\N。 null值不能是\r和\n，最大为100个字符。 null值不能是分隔符。 null参数只在TEXT和CSV格式下有效。
noescaping	TEXT格式下，不对'\'和后面的字符进行转义。	该参数只在TEXT格式下有效。true/on，false/off。缺省值为false/off。
date_format	导入对于DATE类型指定格式。此语法仅对READ ONLY的外表有效。	合法DATE格式。可参考时间、日期处理函数和操作符。对于指定为ORACLE兼容类型的数据库，则DATE类型内建为TIMESTAMP类型。在导入的时候，若需指定格式，可以参考下面的timestamp_format参数。 date_format参数只在TEXT和CSV格式下有效。
time_format	导入对于TIME类型指定格式。此语法仅对READ ONLY的外表有效。	该参数只在TEXT和CSV格式下有效。取值范围：合法TIME格式，不支持时区。可参考时间、日期处理函数和操作符。
timestamp_format	导入对于TIMESTAMP类型指定格式。此语法仅对READ ONLY的外表有效。	该参数只在TEXT和CSV格式下有效。取值范围：合法TIMESTAMP格式，不支持时区。可参考时间、日期处理函数和操作符。
smalldatetime_format	导入对于SMALLDATETIME类型指定格式。此语法仅对READ ONLY的外表有效。	该参数只在TEXT和CSV格式下有效。取值范围：合法SMALLDATETIME格式。可参考时间、日期处理函数和操作符。
dataencoding	在数据库编码与数据表的数据编码不一致时，该参数用于指定导出数据表的数据编码。比如数据库编码为Latin-1，而导出的数据表中的数据为UTF-8编码。此选项为可选项，如果不指定该选项，默认采用数据库编码。此语法仅对HDFS的WRITE ONLY/READ WRITE外表有效。	该参数只对ORC格式的WRITE ONLY/READ WRITE的HDFS外表有效。取值范围：该数据库编码支持转换的数据编码。
filesize	指定WRITE ONLY外表的文件大小。此选项为可选项，不指定该选项默认分布式文件系统配置中文件大小的配置值。此语法仅对WRITE ONLY/READ WRITE可写外表有效。	该参数只对ORC格式的WRITE ONLY/READ WRITE的HDFS外表有效。取值范围：[1, 1024]的整数。
compression	指定文件的压缩方式，此选项为可选项，该参数仅8.2.0及以上集群版本支持。对WRITE ONLY/READ WRITE的外表：指定ORC格式文件的压缩方式。对READ ONLY的外表：指定TEXT、CSV或JSON格式文件的压缩方式。	当format为ORC时：zlib，snappy，lz4，缺省值为zlib。当format为PARQUET时：zlib，snappy，lz4，lz4_hadoop，缺省值为snappy。当format为TEXT、CSV或JSON时：gzip。须知： 8.2.0以下版本不支持READ ONLY外表指定compression参数。 lz4_hadoop压缩选项，该选项适配hive的lz4，当parquet外表设置该压缩格式时，可以与hive端相互读取。
version	指定ORC格式的版本号，此选项为可选项。此语法仅对WRITE ONLY/READ WRITE的外表有效。	取值范围：目前仅支持0.12。缺省值为0.12。
julian_adjust	指定是否矫正数据中的儒略日，此选项为可选项，该参数仅8.3.0及以上集群版本支持。 spark2.X版本使用了julian日，3.0版本后使用Proleptic Gregorian公历，导致了parquet 1582年前的部分日期有差别，根据实际情况选择是否需要矫正。	取值范围：true，false。缺省值为true。
cache_policy	指定外表磁盘缓存(disk cache)策略。该参数仅存算分离3.0版本支持。	取值范围：外表只支持ALL和NONE两种缓存策略，ALL表示使用disk cache中的热缓存，NONE则使用冷缓存。热缓存相比冷缓存占用的空间更大，技术上使用更加复杂的替换算法。

表4 OPTIONS容错性参数说明
参数	描述	取值范围
fill_missing_fields	当数据加载时，若数据源文件中一行的最后一个字段缺失时的处理方式。	true/on，false/off。缺省值为false/off。参数为true/on，当数据加载时，若数据源文件中一行数据的最后一个字段缺失，则把最后一个字段的值设置为NULL，不报错。参数为false/off，如果最后一个字段缺失会显示如下错误信息。 missing data for column "tt" 须知： TEXT格式下执行SELECT COUNT(*) 不会去解析具体字段，因此不会对字段缺失情况报错。 fill_missing_fields参数只在TEXT和CSV格式下有效。
ignore_extra_data	若数据源文件比外表定义列数多，是否会忽略多出的列。该参数只在数据导入过程中使用。	true/on，false/off。缺省值为false/off。参数为true/on，若数据源文件比外表定义列数多，则忽略行尾多出来的列。参数为false/off，若数据源文件比外表定义列数多，会显示如下错误信息。 extra data after last expected column 须知：如果行尾换行符丢失，使两行变成一行时，设置此参数为true将导致后一行数据被忽略掉。 TEXT格式下执行SELECT COUNT(*) 不会去解析具体字段，因此不会对多余的情况报错。 ignore_extra_data参数只在TEXT和CSV格式下有效。
checkencoding	是否检查字符编码。	取值范围：no、low、high 。缺省值为low。 TEXT格式下，导入非法字符容错规则如下：对于'\0'，容错后转换为空格；对于其他非法字符，容错后转换为问号；若checkencoding为low标识，导入时对于非法字符进行容错处理，则若NULL、DELIMITER设置为空格或问号则会通过如"illegal chars conversion may confuse null 0x20"等报错信息提示用户修改可能引起混淆的参数以避免导入错误。 ORC格式下，导入非法字符容错规则如下： checkencoding为no标识，导入时不检查非法字符。在用户确认无非法字符的情况下，此选项可以缩短导入时间。 checkencoding为low标识，若导入时检查到某个字段中包含非法字符，则自动将当前列当前行的字段整体替换为同样长度的‘？’字符； checkencoding为high标识，若导入时检查到某个字段中包含非法字符，则报错退出。
force_mapping	JSON格式下，外表列无法匹配到正确的name-value键值对时的处理方式。	true，false。缺省值为true。 force_mapping为true，相应的列填null，该null与JSON定义中的null含义相同。 force_mapping为false，查询报错，提示不存在这样的列。须知：由于对JSON对象没有限制，但外表字段定义需要符合GaussDB(DWS)的标识符规范（例如长度、字符等限制），因此这种导入方式可能导致异常：例如，字段无法正确标识JSON name、字段需重复定义等。建议使用容错性选项force_mapping或json操作符（可参考JSON/JSONB函数和操作符）来规避。 JSON格式下执行SELECT COUNT(*) 不会去解析具体字段，因此不会对字段缺失、格式错误等情况报错。

表5 OPTIONS参数说明（仅Server类型为DLI时设置）
参数	描述	取值范围
dli_project_id	DLI服务对应的项目编号，可在管理控制台上获取项目ID，该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上集群版本支持。	-
dli_database_name	待访问的DLI多版本表所在的数据库名称，该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上集群版本支持。	-
dli_table_name	待访问的DLI多版本表的名称，该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上集群版本支持。	-

表6 OPTIONS参数说明（仅format为hudi时设置）
参数	描述	取值范围
auth_server	指定一个server，用于支持hudi外表数据使用KMS加密时的用户身份认证，仅支持format为hudi时指定。该参数仅8.3.0及以上集群版本支持。	取值范围：可用的hdfs server名。 MRS服务提供了hudi数据列级加密的能力，通过RangerKMS对用户以及keyId进行管理以及加解密权限控制。用户通过在创建外表时指定auth_server参数，绑定RangerKMS用户信息，进一步进行安全认证、获取密钥，从而实现对读取加密数据的支持。
kms_url	指定KMS服务的ip和端口。仅支持format为hudi、且指定了auth_server时指定。该参数仅8.3.0及以上集群版本支持。	取值范围：合法的ip:port风格字符串。一般情况下，仅指定auth_server即可自动解析KMS服务的ip和端口。如果指定了该参数，则会强制使用地址访问KMS服务。
cow_improve	针对COPY_ON_WRITE表的优化参数，该参数打开时，DN会替代dws-bigdata组件直接从OBS上读取数据。仅支持format为hudi时指定，且不能与auth_server同时指定。该参数仅8.3.0及以上集群版本支持。	取值范围：true/on，false/off。缺省值为false/off。须知： MERGE_ON_READ表指定该参数无效。

表7 OBS外表支持的OPTIONS说明
参数名称	OBS
-	TEXT	CSV	JSON	ORC			CARBONDATA	PARQUET			HUDI
-	READ ONLY	READ ONLY	READ ONLY	READ ONLY	WRITE ONLY	READ WRITE	READ ONLY	READ ONLY	WRITE ONLY	READ WRITE	READ ONLY
location	√	√	√	√	×	×	√	√	×	×	×
format	√	√	√	√	√	√	√	√	√	√	√
header	×	√	×	×	×	×	×	×	×	×	×
delimiter	√	√	×	×	×	×	×	×	×	×	×
quote	×	√	×	×	×	×	×	×	×	×	×
escape	×	√	×	×	×	×	×	×	×	×	×
null	√	√	×	×	×	×	×	×	×	×	×
noescaping	√	×	×	×	×	×	×	×	×	×	×
encoding	√	√	√	√	√	√	√	√	√	√	√
fill_missing_fields	√	√	×	×	×	×	×	×	×	×	×
ignore_extra_data	√	√	×	×	×	×	×	×	×	×	×
date_format	√	√	√	×	×	×	×	×	×	×	×
time_format	√	√	√	×	×	×	×	×	×	×	×
timestamp_format	√	√	√	×	×	×	×	×	×	×	×
smalldatetime_format	√	√	√	×	×	×	×	×	×	×	×
chunksize	√	√	√	×	×	×	×	×	×	×	×
filenames	×	×	×	×	×	×	×	×	×	×	×
foldername	√	√	√	√	√	√	√	√	√	√	√
dataencoding	×	×	×	×	×	×	×	×	×	×	×
filesize	×	×	×	×	×	×	×	×	×	×	×
compression	√	√	√	×	√	√	×	×	√	√	×
version	×	×	×	×	√	√	×	×	×	×	×
checkencoding	√	√	√	√	×	√	√	√	√	√	√
totalrows	√	√	√	√	×	√	×	×	×	×	×
force_mapping	×	×	√	×	×	×	×	×	×	×	×
auth_server	×	×	×	×	×	×	×	×	×	×	√
kms_url	×	×	×	×	×	×	×	×	×	×	√
cow_improve	×	×	×	×	×	×	×	×	×	×	√
julian_adjust	×	×	×	×	×	×	×	√	×	×	√

表8 HDFS外表支持的OPTIONS说明
参数名称	HDFS
-	TEXT	CSV	JSON	ORC			PARQUET
-	READ ONLY	READ ONLY	READ ONLY	READ ONLY	WRITE ONLY	READ WRITE	READ ONLY	WRITE ONLY	READ WRITE
location	×	×	×	×	×	×	×	×	×
format	√	√	√	√	√	√	√	√	√
header	×	√	×	×	×	×	×	×	×
delimiter	√	√	×	×	×	×	×	×	×
quote	×	√	×	×	×	×	×	×	×
escape	×	√	×	×	×	×	×	×	×
null	√	√	×	×	×	×	×	×	×
noescaping	√	×	×	×	×	×	×	×	×
encoding	√	√	√	√	√	√	√	√	√
fill_missing_fields	√	√	×	×	×	×	×	×	×
ignore_extra_data	√	√	×	×	×	×	×	×	×
date_format	√	√	√	×	×	×	×	×	×
time_format	√	√	√	×	×	×	×	×	×
timestamp_format	√	√	√	×	×	×	×	×	×
smalldatetime_format	√	√	√	×	×	×	×	×	×
chunksize	√	√	√	×	×	×	×	×	×
filenames	√	√	√	√	×	×	√	×	×
foldername	√	√	√	√	√	√	√	√	√
dataencoding	×	×	×	×	√	√	×	×	×
filesize	×	×	×	×	√	√	×	×	×
compression	√	√	√	×	√	√	×	√	√
version	×	×	×	×	√	√	×	×	×
checkencoding	√	√	√	√	√	√	√	√	√
totalrows	×	×	×	×	×	×	×	×	×
force_mapping	×	×	√	×	×	×	×	×	×
julian_adjust	×	×	×	×	×	×	√	×	×

说明：

chunksize表示在DN中每个HDFS读取线程的缓存大小，支持text，csv，json格式，默认大小为4MB。

信息约束（Informational Constraint）

在GaussDB(DWS)中，数据的约束完全由使用者保证，数据源数据能够严格遵守某种信息约束条件，能够加速对已经具有这种约束特征数据的查询。目前外表不支持索引，所以采取使用Informational Constraint信息优化Plan，提高查询性能。

建立外表信息约束的约束条件：

只有用户保证表中的其中一列的非空值具有唯一性时才可以建立Informational Constraint，否则查询结果将与期望值不同。
GaussDB(DWS)的Informational Constraint只支持PRIMARY KEY和UNIQUE两种约束。
GaussDB(DWS)的Informational Constraint支持NOT ENFORCED属性，不支持ENFORCED属性。
一个表上的多列可以分别建立UNIQUE类型的Informational Constraint，但是PRIMARY KEY一个表中只能建立一个。
一个表的一列上可以建立多个Informational Constraint（由于一个列上有多个约束和一个的作用一致，所以不建议一个列上建立多个Informational Constraint），但是Primary Key类型只能建立一个。
不支持表级COMMENT。
不支持多列组合约束。
ORC格式只写外表不支持同一个集群不同CN向同一外表并发导出。
ORC格式只写外表的目录，只能用于GaussDB(DWS)的单个外表的导出目录，不能用于多个外表，并且其他组件不能向此目录写入其他文件。

示例1

在HDFS通过HIVE导入TPC-H benchmark测试数据表part表及region表。part表的文件路径为/user/hive/warehouse/partition.db/part_4，region表的文件路径为/user/hive/warehouse/mppdb.db/region_orc11_64stripe/。

创建HDFS_Server，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW。

      
         CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');

说明：

在可选项options里面写入了HDFS集群对应的NameNode的IP地址及端口号。具体端口号请在MRS-HDFS服务配置中搜索参数“dfs.namenode.rpc.port”查看。本示例假设端口号为25000。
‘10.10.0.100:25000,10.10.0.101:25000’中列出了两组NameNode的地址及端口号，分别表示HDFS的主NameNode及备NameNode，这里推荐使用该种主备方式填写。两组参量中间使用“,”进行分割。

创建HDFS外表。表关联的HDFS server为hdfs_server，表ft_region对应的HDFS服务器上的文件格式为‘orc’，在HDFS文件系统上对应的文件目录为'/user/hive/warehouse/mppdb.db/region_orc11_64stripe/'。

创建不包含分区列的HDFS外表：

        
         
           
           CREATE FOREIGN TABLE ft_region
(
    R_REGIONKEY INT4,
    R_NAME TEXT,
    R_COMMENT TEXT
)
SERVER
    hdfs_server
OPTIONS
(
    FORMAT 'orc',
    encoding 'utf8',
    FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc11_64stripe/'
)
DISTRIBUTE BY 
     roundrobin;

          

        
       

创建包含分区列的HDFS外表：

        
         
           
           CREATE FOREIGN TABLE ft_part 
(
     p_partkey int, 
     p_name text, 
     p_mfgr text, 
     p_brand text, 
     p_type text, 
     p_size int, 
     p_container text, 
     p_retailprice float8, 
     p_comment text
)
SERVER
     hdfs_server
OPTIONS
(
     FORMAT 'orc',
     encoding 'utf8',
     FOLDERNAME '/user/hive/warehouse/partition.db/part_4'
)
DISTRIBUTE BY 
     roundrobin
PARTITION BY 
     (p_mfgr) AUTOMAPPED;

          

        
       

说明：

GaussDB(DWS)支持2种文件指定方式：通过关键字filenames指定和通过foldername指定。推荐通过使用foldername进行指定。关键字distribute指定了表ft_region的存储分布方式。

查看创建的外表：

      
         SELECT * FROM pg_foreign_table WHERE ftrelid='ft_region'::regclass;
 ftrelid | ftserver | ftwriteonly |                                  ftoptions
---------+----------+-------------+------------------------------------------------------------------------------
   16510 |    16509 | f           | {format=orc,foldername=/user/hive/warehouse/mppdb.db/region_orc11_64stripe/}
(1 row)

select * from pg_foreign_table where ftrelid='ft_part'::regclass;
 ftrelid | ftserver | ftwriteonly |                            ftoptions
---------+----------+-------------+------------------------------------------------------------------
   16513 |    16509 | f           | {format=orc,foldername=/user/hive/warehouse/partition.db/part_4}
(1 row)

示例2

通过HDFS只写外表，将TPC-H benchmark测试数据表region中的数据导出至HDFS文件系统的/user/hive/warehouse/mppdb.db/regin_orc/目录下。

创建HDFS SERVER，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例一。

创建HDFS只写外表。

      
       
         
         CREATE FOREIGN TABLE ft_wo_region
(
    R_REGIONKEY INT4,
    R_NAME TEXT,
    R_COMMENT TEXT
)
SERVER
    hdfs_server
OPTIONS
(
    FORMAT 'orc',
    encoding 'utf8',
    FOLDERNAME '/user/hive/warehouse/mppdb.db/regin_orc/'
)
WRITE ONLY;

        

      
     

通过只写外表向HDFS文件系统写入数据。

      
         INSERT INTO ft_wo_region SELECT * FROM region;

示例3（仅存算分离3.0版本支持）

通过HDFS多级分区只写外表将TPC-H benchmark测试数据表region表中的数据导出HDFS文件系统的/user/hive/warehouse/mppdb.db/region_orc/目录下。

创建HDFS外表，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例1。

创建HDFS多级分区只写外表。

      
       
         
         CREATE FOREIGN TABLE ft_wo_region_partition
(
    R_REGIONKEY INT4,
    R_NAME TEXT,
    R_COMMENT TEXT
)
SERVER
    hdfs_server
OPTIONS
(
    FORMAT 'orc',
    encoding 'utf8',
    FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc/'
)
WRITE ONLY
PARTITION BY (R_REGIONKEY, R_NAME);

        

      
     

通过只写外表向HDFS文件系统写入数据。

      
         INSERT INTO ft_wo_region_partition SELECT * FROM region;

示例4（仅存算分离3.0版本支持）

通过partsupp外表往obs文件系统读取数据。

创建obs server。

须知：

认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。
了解更多创建Server语法请参见CREATE SERVER。

      
         CREATE SERVER obs_srv FOREIGN DATA WRAPPER dfs_fdw OPTIONS (
    address 'obs.cn-north-1.myhuaweicloud.com',
    type 'obs',
    access_key 'xxx',
    secret_access_key 'xxx'
);

创建obs外表partsupp，使用disk cache中的冷缓存。

      
       
         
         CREATE FOREIGN TABLE PARTSUPP
(
   PS_PARTKEY     BIGINT NOT NULL,
   PS_SUPPKEY     BIGINT NOT NULL,
   PS_AVAILQTY    BIGINT NOT NULL,
   PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
   PS_COMMENT     VARCHAR(199) NOT NULL
) SERVER obs_srv options (
    encoding     'utf-8',
    format       'parquet',
    foldername   'xxx',
    cache_policy 'NONE'
) READ ONLY DISTRIBUTE BY ROUNDROBIN;

        

      
     

通过外表partsupp往obs上读取数据。
1

SELECT * FROM PARTSUPP;

示例5

关于包含信息约束（Informational Constraint）HDFS外表的相关操作。

创建含有信息约束（Informational Constraint）的HDFS外表。

      
         CREATE FOREIGN TABLE ft_region  (
 R_REGIONKEY  int,
 R_NAME TEXT,
 R_COMMENT TEXT
  , primary key (R_REGIONKEY) not enforced)
SERVER hdfs_server
OPTIONS(format 'orc',
    encoding 'utf8',
 foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe')
DISTRIBUTE BY roundrobin;

查看region表是否有信息约束索引：

      
         SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass;
        relname         | relhasindex 
------------------------+-------------
        ft_region       | f
(1 row)

SELECT conname, contype, consoft, conopt, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey';
   conname      | contype | consoft | conopt | conindid | conkey
----------------+---------+---------+--------+----------+--------
 ft_region_pkey | p       | t       | t      |        0 | {1}
(1 row)

删除信息约束：

      
         ALTER FOREIGN TABLE ft_region DROP CONSTRAINT ft_region_pkey RESTRICT;

SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey';
 conname | contype | consoft | conindid | conkey 
---------+---------+---------+----------+--------
(0 rows)

添加一个唯一信息约束：

      
         ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED;

删除唯一信息约束：

       
          ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique RESTRICT;

SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='constr_unique';
 conname | contype | consoft | conindid | conkey 
---------+---------+---------+----------+--------
(0 rows)

添加一个唯一信息约束：

      
         ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED disable query optimization;

SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass;
        relname         | relhasindex 
------------------------+-------------
        ft_region       | f
(1 row)

删除唯一信息约束：

       
          ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;

示例6

通过外表读取OBS上的json数据。

OBS上有如下json文件，json对象中存在嵌套、数组，部分对象的某些字段缺失，部分对象name重复。

{"A" : "simple1", "B" : {"C" : "nesting1"}, "D" : ["array", 2, {"E" : "complicated"}]}
{"A" : "simple2", "D" : ["array", 2, {"E" : "complicated"}]}
{"A" : "simple3", "B" : {"C" : "nesting3"}, "D" : ["array", 2, {"E" : "complicated3"}]}
{"B" : {"C" : "nesting4"},"A" : "simple4",  "D" : ["array", 2, {"E" : "complicated4"}]}
{"A" : "simple5", "B" : {"C" : "nesting5"}, "D" : ["array", 2, {"E" : "complicated5"}]}

创建obs_server，对应的foreign data wrapper为DFS_FDW。

      
         CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( 
  ADDRESS 'obs.cn-north-1.myhuaweicloud.com', 
  ACCESS_KEY 'xxxxxxxxx', 
  SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', 
  TYPE 'OBS'
);

说明：

ADDRESS是OBS的终端节点（Endpoint），请根据实际替换。也是使用region参数，通过指定regionCode在region_map文件中查找对应的域名。
ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问密钥。请根据实际替换。
TYPE表示创建的Server为OBS Server。请保持OBS取值不变。

创建OBS外表json_f ，定义字段名，以d#2_e为例，从命名可以看出该字段是数组d的第二个元素里嵌套的e对象。表关联的OBS服务器为obs_server。foldername为外表中数据源文件目录，即表数据目录在OBS上对应的文件目录。

须知：

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

      
         CREATE FOREIGN TABLE json_f (
  a VARCHAR(10),
  b_c TEXT,
  d#1 INTEGER,
  d#2_e VARCHAR(30)
)SERVER obs_server OPTIONS (
    foldername '/xxx/xxx/',
    format 'json',
    encoding 'utf8',
    force_mapping 'true'
)distribute by roundrobin;

查询外表json_f。由于容错性参数force_mapping默认打开，json对象缺失的字段会填NULL；json对象name重复的以最后一次出现的name为准。

      
         SELECT * FROM json_f;
    a    |   b_c    | d#1 |    d#2_e
---------+----------+-----+--------------
 simple1 | nesting1 |   2 | complicated1
 simple2 |          |   2 | complicated2
 simple3 | nesting3 |   2 | complicated3
 simple4 | nesting4 |   2 | complicated4
 repeat  | nesting5 |   2 | complicated5
(5 rows)

示例7

通过外表读取DLI多版本外表。DLI多版本外表示例仅8.1.1及以上版本支持。

创建dli_server，对应的foreign data wrapper为DFS_FDW。

      
         CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( 
  ADDRESS 'obs.cn-north-1.myhuaweicloud.com', 
  ACCESS_KEY 'xxxxxxxxx', 
  SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', 
  TYPE 'DLI',
  DLI_ADDRESS 'dli.example.com',
  DLI_ACCESS_KEY 'xxxxxxxxx',
  DLI_SECRET_ACCESS_KEY 'yyyyyyyyyyyyy'
);

说明：

ADDRESS是OBS的终端节点（Endpoint）。DLI_ADDRESS是DLI的终端节点（Endpoint），请根据实际替换。
ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问OBS服务的密钥。请根据实际替换。
DLI_ACCESS_KEY和DLI_SECRET_ACCESS_KEY是云账号体系访问DLI服务的密钥。请根据实际替换。
TYPE表示创建的Server为DLI Server。请保持DLI取值不变。

创建访问DLI多版本的OBS外表customer_address，不包含分区列，表关联的DLI服务器为dli_server。其中project_id为xxxxxxxxxxxxxxx，dli上的database_name为database123，需要访问的table_name为table456，根据实际替换。

须知：

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

      
       
         
         CREATE FOREIGN TABLE customer_address
(
    ca_address_sk             integer               not null,
    ca_address_id             char(16)              not null,
    ca_street_number          char(10)                      ,   
    ca_street_name            varchar(60)                   ,   
    ca_street_type            char(15)                      ,   
    ca_suite_number           char(10)                      ,   
    ca_city                   varchar(60)                   ,   
    ca_county                 varchar(30)                   ,   
    ca_state                  char(2)                       ,   
    ca_zip                    char(10)                      ,   
    ca_country                varchar(20)                   ,   
    ca_gmt_offset             decimal(36,33)                  ,   
    ca_location_type          char(20)    
) 
SERVER dli_server OPTIONS (
    FORMAT 'ORC',
    ENCODING 'utf8',
    DLI_PROJECT_ID 'xxxxxxxxxxxxxxx',
    DLI_DATABASE_NAME 'database123'，
    DLI_TABLE_NAME 'table456'
)
DISTRIBUTE BY roundrobin;

        

      
     

通过外表查询DLI多版本表的数据。

      
         SELECT COUNT(*) FROM customer_address;
 count 
-------
    20
(1 row)

CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

功能描述

注意事项

语法格式

参数说明

信息约束（Informational Constraint）

示例1

示例2

示例3（仅存算分离3.0版本支持）

示例4（仅存算分离3.0版本支持）

示例5

示例6

示例7

相关链接

相关文档

意见反馈

文档内容是否对您有帮助？

7*24

备案

专业服务

退订

建议反馈

售前咨询热线

文档反馈