CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

功能描述

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

外表（Foreign Table）是数据库或大数据平台中一种虚拟表结构，它不实际存储数据，而是通过元数据信息（如表结构、存储位置、文件格式等）将外部存储系统（如HDFS、OBS等）中的数据映射到数据库内部，使用户能够以标准SQL操作直接访问或操作外部数据。外表的本质是一种“数据代理”，将外部文件系统的数据逻辑映射成数据库可识别的表结构。

外表的核心价值是 “打破数据孤岛”： 通过元数据映射，让数据库用户以熟悉的方式访问外部系统数据，同时支持数据向外部系统的导出，实现跨系统数据的高效协同、分析与共享，尤其在大数据场景中能显著降低数据移动成本、提升协作效率。

DWS通过外表访问外部数据源时，对于不同的数据源，访问性能有所不同：

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

以下描述的读写外表，仅存算分离3.0版本支持。

注意事项

• 使用OBS外表访问OBS桶数据时，应确保DWS与OBS桶在同一个区域(Region)下，例如都在“中国-香港”下。
• 仅系统管理员dbadmin角色或已授权了USEFT权限的普通用户可执行外表操作。

  1	ALTER USER user_name USEFT;

• HDFS外表与OBS外表分为只读外表、只写外表和读写外表，只读外表用于查询操作，只写外表可以将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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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为：

  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]

信息约束（Informational Constraint）

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

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

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

示例：创建HDFS外表

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

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

   1 2 3 4

   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，这里推荐使用该种主备方式填写。两组参量中间使用“,”进行分割。
2. 创建HDFS外表。表关联的HDFS server为hdfs_server，表ft_region对应的HDFS服务器上的文件格式为‘orc’，在HDFS文件系统上对应的文件目录为'/user/hive/warehouse/mppdb.db/region_orc11_64stripe/'。
   • 创建不包含分区列的HDFS外表：

     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

     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外表：

     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

     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;

     

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

3. 查看创建的外表：

   1 2 3 4 5 6 7 8 9 10 11

   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)

示例：创建HDFS只写外表

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

1. 创建HDFS SERVER，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例一。
2. 创建HDFS只写外表。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   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/region_orc/' ) WRITE ONLY;

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

   1	INSERT INTO ft_wo_region SELECT * FROM region;

示例：创建HDFS只写分区外表（仅存算分离3.0版本支持）

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

1. 创建HDFS外表，对应的foreign data wrapper为HDFS_FDW或者DFS_FDW，同示例：创建HDFS外表。
2. 创建HDFS多级分区只写外表。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   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);

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

   1	INSERT INTO ft_wo_region_partition SELECT * FROM region;

示例：创建OBS只读外表（仅存算分离3.0版本支持）

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

1. 创建obs server。
   

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

   1 2 3 4 5 6

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

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

   1 2 3 4 5 6 7 8 9 10 11 12 13

   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;

3. 通过外表partsupp从obs上读取数据。

   1	SELECT * FROM PARTSUPP;

示例：创建包含信息约束（指定not enforced）的HDFS外表

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

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

  1 2 3 4 5 6 7 8 9 10

  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表是否有信息约束索引：

  1 2 3 4 5 6 7 8 9 10 11

  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)

• 删除信息约束：

  1 2 3 4 5 6

  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)

• 添加一个唯一信息约束：

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

  删除唯一信息约束：

  1 2 3 4 5 6

  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)

• 添加一个唯一信息约束：

  1 2 3 4 5 6 7

  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)

  删除唯一信息约束：

  1	ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;

示例：通过OBS外表读取OBS的json数据

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

1. 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"}]}

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

   1 2 3 4 5 6

   CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.ap-southeast-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取值不变。
3. 创建OBS外表json_f ，定义字段名，以d#2_e为例，从命名可以看出该字段是数组d的第二个元素里嵌套的e对象。表关联的OBS服务器为obs_server。foldername为外表中数据源文件目录，即表数据目录在OBS上对应的文件目录。
   

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

   1 2 3 4 5 6 7 8 9 10 11

   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;

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

   1 2 3 4 5 6 7 8 9

   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)

示例：通过DLI外表访问DLI上的数据

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

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

   1 2 3 4 5 6 7 8 9

   CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.ap-southeast-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取值不变。
2. 创建访问DLI多版本的OBS外表customer_address，不包含分区列，表关联的DLI服务器为dli_server。其中project_id为xxxxxxxxxxxxxxx，dli上的database_name为database123，需要访问的table_name为table456，根据实际替换。
   

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

   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;

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

   1 2 3 4 5

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

相关链接

• 修改和删除外表语法请参见ALTER FOREIGN TABLE (For HDFS or OBS)和DROP FOREIGN TABLE。
• 更多最佳实践：
  • 使用HDFS外表访问MRS远端数据，请参见从MRS Hive导入表数据到DWS集群。
  • 使用OBS外表，请参见导入OBS桶数据到DWS集群及从DWS导出数据到OBS桶。
  • 使用DLI外表，请参见从DLI导入表数据到DWS集群。