ODPS Foreign Table（ODPS FDW）是云原生数据仓库 AnalyticDB PostgreSQL 版基于PostgreSQL Foreign Data Wrapper（PG FDW）框架开发的用于访问MaxCompute外部数据的方案，允许您在云原生数据仓库 AnalyticDB PostgreSQL 版中创建外表，然后将MaxCompute中的数据通过外表引入云原生数据仓库 AnalyticDB PostgreSQL 版中进行查询和分析。关于ODPS Endpoint，请参见配置Endpoint。2. 创建 ODPS User Mapping2.1 语法示例CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC } SERVER odps_serv -- ODPS Server 名称 OPTIONS ( id '<odps access id>', -- ODPS Account ID key '<odps access key>' -- ODPS Account Key );2.2 参数选项在AnalyticDB PostgreSQL版中定义访问ODPS Server的账户，需要指定账户类型 TYPE，ID和KEY。选项是否必选备注id必选指定账户ID。key必选指定账户KEY。3. 创建ODPS Foreign Table3.1 语法示例CREATE FOREIGN TABLE IF NOT EXISTS table_name ( -- ODPS 外表名称 column_name data_type [, ... ] ) SERVER odps_serv -- ODPS Server 名称 OPTIONS ( project '<odps project>', -- ODPS 项目空间 table '<odps table>' -- ODPS 表名称 );3.2 参数选项定义了ODPS Server和ODPS User Mapping 后，就可以创建ODPS Foreign Table。参数选项包括：选项是否必选备注project必选项目空间。项目空间（Project）是 MaxCompute 的基本组织单元，它类似于传统数据库的 Database 或 Schema 的概念，是进行多用户隔离和访问控制的主要边界。详情请参见项目。table必选MaxCompute表名称。表是MaxCompute的数据存储单元，详情请参见表。partition可选用于定义MaxCompute的末级分区表。分区partition是指在一张表中，根据分区字段（一个或多个字段的组合）对数据存储进行划分。也就是说，如果表没有分区，数据是直接放在表所在的目录下。如果表有分区，每个分区对应表下的一个目录，数据是分别存储在不同的分区目录下。关于分区的更多介绍请参见分区。3.3 外表分类根据MaxCompute的表分类，ODPS FDW支持定义以下三种类型的ODPS外表。非分区外表非分区ODPS外表映射的是MaxCompute的非分区表。用户创建外表时，只需要指定有效的project和table属性即可，无需指定partition属性或者指定partition属性为“空”。例如：CREATE FOREIGN TABLE odps_lineitem ( -- ODPS 外表名称 l_orderkey bigint, l_partkey bigint, l_suppkey bigint, l_linenumber bigint, l_quantity double precision, l_extendedprice double precision, l_discount double precision, l_tax double precision, l_returnflag char(1), l_linestatus char(1), l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(25), l_shipmode char(10), l_comment varchar(44) ) SERVER odps_serv -- ODPS Server 名称 OPTIONS ( project 'odps_fdw', -- ODPS 项目空间 table 'lineitem_big' -- ODPS 表名称 );末级分区外表相对于非分区外表，末级分区外表，映射的是MaxCompute的末级分区表，需要设置正确的partition属性，多级分区时，末级分区外表只支持末级分区表，即partition属性需要包含多级分区完整路径。举例说明：在MaxCompute上创建一个二级分区表，如下：--创建一个二级分区表，以日期为一级分区，地域为二级分区 CREATE TABLE src (key string, value bigint) PARTITIONED BY (pt string,region string);当您需要在AnalyticDB PostgreSQL版中定义一个末级分区外表，映射 MaxCompute上一级分区为（20170601），二级分区为（hangzhou）的末级分区表时，需要设置 partition为'pt=20170601,region=hangzhou'。CREATE FOREIGN TABLE odps_src_20170601_hangzhou ( -- ODPS 外表 key string, value bigint ) SERVER odps_serv -- ODPS Server 名称 OPTIONS ( project 'odps_fdw', -- ODPS 项目空间 table 'src', -- ODPS 表名称 partition 'pt=20170601,region=hangzhou' -- 末级分区完整路径 );分区外表MaxCompute分区外表，映射的是MaxCompute的分区表。同样，以上述MaxCompute二级分区表src 为例，您可以按照如下方法创建对应的分区外表。更多表分区定义。CREATE FOREIGN TABLE odps_src( -- ODPS 外表名称 key text, value bigint, pt text, -- ODPS 一级分区键 region text -- ODPS 二级分区键 ) SERVER odps_serv OPTIONS ( project 'odps_fdw', -- ODPS 项目空间 table 'src' -- ODPS 表名称 ) PARTITION BY LIST (pt) -- 一级分区以"pt"字段为分区键 SUBPARTITION BY LIST (region) -- 二级分区以"region"字段为分区键 SUBPARTITION TEMPLATE ( -- 二级分区模板 SUBPARTITION hangzhou VALUES ('hangzhou'), SUBPARTITION shanghai VALUES ('shanghai') ) ( PARTITION "20170601" VALUES ('20170601'), PARTITION "20170602" VALUES ('20170602'));3.4 如何添加子分区外表以上述odps_src分区外表为例。添加一级子分区，效果如下图。-- 添加一级子分区（自动创建二级子分区） alter table odps_src add partition "20170603" values(20170603);添加二级子分区，效果如下图。-- 添加二级子分区 alter table odps_src alter partition "20170603" add partition "nanjing" values('nanjing');3.5 如何删除子分区外表以上述odps_src分区外表为例。删除一级子分区，效果如下图。-- 删除一级子分区（级联删除二级子分区） alter table odps_src drop partition "20170602";删除二级子分区-- 删除二级子分区 alter table odps_src alter partition "20170601" drop partition "hangzhou";MaxCompute外表数据类型目前MaxCompute数据类型与AnalyticDB PostgreSQL版数据类型的对应关系如下，建议按照此类型对照表来定义AnalyticDB PostgreSQL版外表的字段类型。MaxCompute数据类型AnalyticDB PostgreSQL版数据类型BOOLEANboolTINYINTint2SMALLINTint2INTEGERint4BIGINTint8FLOATfloat4DOUBLEfloat8DECIMALnumericBINARYbyteaVARCHAR(n)varchar(n)CHAR(n)char(n)STRINGtextDATEdateDATETIMEtimestampTIMESTAMPtimestampMaxCompute外表使用场景MaxCompute外表扫描，实现了AnalyticDB PostgreSQL版的Foreign Scan算子。因此，表查询的使用方法上，对MaxCompute外表的查询与对普通表的查询基本一致。本文以TPC-H Query为例，举例说明常见的使用场景。MaxCompute外表查询分析TPC-H Query Q1是典型的单表聚集过滤场景，定义odps_lineitem为MaxCompute外表，对其执行Q1查询。-- 定义MaxCompute外表odps_lineitem CREATE FOREIGN TABLE odps_lineitem ( l_orderkey bigint, l_partkey bigint, l_suppkey bigint, l_linenumber bigint, l_quantity double precision, l_extendedprice double precision, l_discount double precision, l_tax double precision, l_returnflag CHAR(1), l_linestatus CHAR(1), l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, l_shipinstruct CHAR(25), l_shipmode CHAR(10), l_comment VARCHAR(44) ) server odps_serv options ( project 'odps_fdw', table 'lineitem' ); -- TPC-H Q1 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from odps_lineitem where l_shipdate <= date '1998-12-01' - interval '88' day --(3) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;MaxCompute数据导入本地表导入数据时，请执行如下步骤：在AnalyticDB PostgreSQL版中，创建MaxCompute外表。执行如下操作，并行导入数据。-- INSERT方式 INSERT INTO <本地目标表> SELECT * FROM <ODPS 外表>; -- CREATE TABLE AS 方式 CREATE TABLE <本地目标表> AS SELECT * FROM <ODPS 外表>;示例1：INSERT方式将odps_lineitem数据导入到本地AOCS表。-- 创建本地AOCS表 CREATE TABLE aocs_lineitem ( l_orderkey bigint, l_partkey bigint, l_suppkey bigint, l_linenumber bigint, l_quantity double precision, l_extendedprice double precision, l_discount double precision, l_tax double precision, l_returnflag CHAR(1), l_linestatus CHAR(1), l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, l_shipinstruct CHAR(25), l_shipmode CHAR(10), l_comment VARCHAR(44) ) WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5) DISTRIBUTED BY (l_orderkey); -- 将 odps_lineitem 数据导入到 AOCS 本地表 INSERT INTO aocs_lineitem SELECT * FROM odps_lineitem;示例2：CREATE TABLE AS方式将odps_lineitem导入到本地heap表。create table heap_lineitem as select * from odps_lineitem distributed by (l_orderkey);MaxCompute外表与本地表关联以TPC-H Query Q19为例，使用本地列存表aocs_lineitem与MaxCompute外表odps_part关联查询。-- TPC-H Q19 select sum(l_extendedprice* (1 - l_discount)) as revenue from aocs_lineitem, -- 本地 AOCS 列存表 odps_part -- ODPS 外表 where ( p_partkey = l_partkey and p_brand = 'Brand#32' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 8 and l_quantity <= 8 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#41' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 15 and l_quantity <= 15 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#44' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 22 and l_quantity <= 22 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );MaxCompute外表使用常见错误Tunnel常见错误MaxCompute外表使用建议MaxCompute外表通过网络访问MaxCompute，使用瓶颈除了机器自身资源外，还受限于MaxCompute Tunnel对外吞吐的网络带宽 。因此，建议您：纯外表使用的并发数不超过5个。多张ODPS外表关联使用时，将大表导入本地后再和小的外表关联，性能更佳。