Access MaxCompute data with foreign tables

The following figure shows an overview.

The MaxCompute FDW enables data synchronization between AnalyticDB for PostgreSQL and MaxCompute. You can use the MaxCompute FDW to create three types of MaxCompute foreign tables.

• Non-partitioned foreign tables: Map to non-partitioned MaxCompute tables.

• Lowest-level partitioned foreign tables: Map to the lowest-level partitions of partitioned MaxCompute tables.

• Partitioned foreign tables: Map to entire partitioned MaxCompute tables.

• For new instances, the MaxCompute FDW extension is created by default. You do not need to perform these steps.

• For existing instances, connect to the target database with a Initial Account and run the preceding commands to create the MaxCompute FDW extension.

To use a MaxCompute foreign table, you must create the following three components in order:

• Server: Specifies the connection endpoint for MaxCompute.

• User Mapping: Specifies the credentials for accessing MaxCompute.

• Foreign Table: Defines the specific table to access in MaxCompute.

1.1 Syntax

CREATE SERVER odps_serv                        -- Name of the server
  FOREIGN DATA WRAPPER odps_fdw
  OPTIONS (
    tunnel_endpoint '<odps_tunnel_endpoint>'   -- The MaxCompute Tunnel endpoint
  );

1.2 Parameters

When you create a server in AnalyticDB for PostgreSQL, you need to specify either the tunnel_endpoint or odps_endpoint option.

For more information about MaxCompute endpoints, see Configure Endpoint.

2.1 Syntax

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
  SERVER odps_serv                                  -- Name of the server
  OPTIONS (
    id '<access_key_id>',                                -- The AccessKey ID
    key '<access_key_secret>'                        -- The AccessKey secret
  );

2.2 Parameters

To define an account in AnalyticDB for PostgreSQL to access an ODPS Server, you must specify its TYPE, ID, and KEY.

3.1 Syntax

CREATE FOREIGN TABLE IF NOT EXISTS table_name ( -- Name of the foreign table
    column_name data_type [, ... ]
)
  SERVER odps_serv                              -- Name of the server
  OPTIONS (
    project '<mc_project_name>',                   -- Name of the MaxCompute project
    table '<mc_table_name>'                        -- Name of the MaxCompute table
);

3.2 Parameters

After creating a server and a user mapping, you can create a MaxCompute foreign table with the following options:

3.3 Types of foreign tables

Based on the types of MaxCompute tables, you can define the following three types of MaxCompute foreign tables.

• Non-partitioned foreign tables

  A non-partitioned foreign table maps to a non-partitioned MaxCompute table. When you create the foreign table, you only need to specify the project and table options. You do not need to specify the partition option, or you can set the partition option to an empty string. Example:

  CREATE FOREIGN TABLE odps_lineitem (              -- Name of the foreign table
      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                              -- Name of the server
  OPTIONS (
    project 'odps_fdw',                           -- Name of the MaxCompute project
    table 'lineitem_big'                          -- Name of the MaxCompute table
  );

• Lowest-level partitioned foreign tables

  A lowest-level partitioned foreign table maps to the lowest-level partition of a partitioned MaxCompute table. You must correctly set the partition option. For tables with multi-level partitions, this type of foreign table maps only to the lowest-level partition. Therefore, the partition option must specify the complete path of the target partition.

  For example, create a two-level partitioned table in MaxCompute:

  -- Create a two-level partitioned table.
  -- 'pt' is the level-1 partition key (by date).
  -- 'region' is the level-2 partition key (by region).
  CREATE TABLE src (key string, value bigint) PARTITIONED BY (pt string, region string);

  

  To create a foreign table in AnalyticDB for PostgreSQL that maps to the subpartition where the level-1 partition is 20170601 and the level-2 partition is hangzhou, set the partition option to 'pt=20170601,region=hangzhou'.

  CREATE FOREIGN TABLE odps_src_20170601_hangzhou (   -- Name of the foreign table
    key string,
    value bigint
  ) SERVER odps_serv                                  -- Name of the server
  OPTIONS (
    project 'odps_fdw',                               -- Name of the MaxCompute project 
    table 'src',                                      -- Name of the MaxCompute table
    partition 'pt=20170601,region=hangzhou'           -- Full path of the lowest-level partition
  );

  Note

  • Specify the partition in key=value format. For multi-level partitions, separate key-value pairs with commas (,) and do not include extra spaces.

  • You cannot map a foreign table to a non-lowest-level partition. For example, you cannot set the option to only the level-1 partition path: partition 'pt=20170601'.

  • You must specify the full multi-level partition path. For example, you cannot set the option to only the lowest-level partition path: partition '

    region=hangzhou'.

• Partitioned foreign tables

  A partitioned foreign table maps to an entire partitioned MaxCompute table. Using the same two-level partitioned MaxCompute table src from the previous example, you can create a corresponding partitioned foreign table. For more information, see Defining table partitions.

  CREATE FOREIGN TABLE odps_src(                    -- Name of the foreign table
    key text,
    value bigint,
    pt text,                                        -- Level-1 partition key
    region text                                     -- Level-2 partition key
  ) SERVER odps_serv
  OPTIONS (
    project 'odps_fdw',                             -- Name of the MaxCompute project
    table 'src'                                     -- Name of the MaxCompute table
  )
  PARTITION BY LIST (pt)                            -- Partition by the 'pt' field
  SUBPARTITION BY LIST (region)                     -- Subpartition by the 'region' field
      SUBPARTITION TEMPLATE (                       -- Define a subpartition template
         SUBPARTITION hangzhou VALUES ('hangzhou'),
         SUBPARTITION shanghai VALUES ('shanghai')
      )
  ( PARTITION "20170601" VALUES ('20170601'), 
    PARTITION "20170602" VALUES ('20170602'));

  Note

  The definition for a partitioned foreign table in AnalyticDB for PostgreSQL differs from a partitioned table definition in MaxCompute:

  • The partition key columns must be defined after all other columns. For multi-level partitions, the definition order of the partition key columns must match the partition level order in the MaxCompute table.

  • You must specify partition key values. Use LIST partitioning.

  • Do not specify the partition option. This option is used only for lowest-level partitioned foreign tables and logically conflicts with partitioned foreign table definitions.

  • If a partition defined in the foreign table does not exist in MaxCompute, the system reports a warning when you query the table. You can then delete the non-existent subpartition as described in the "Deleting a subpartition" section.

    postgres=# select count(1) from odps_tt_err;
    WARNING:  RequestId=202006101759108531f60b0b108613, ErrorCode=NoSuchPartition, ErrorMessage=Error: The specified partition does not exist. from:http://dt.cn-hangzhou.maxcompute.aliyun.com
    CONTEXT:  table=odps_tt_err_1_prt_20200621_2_prt_hangzhou, partition="pt=20200621,region=hangzhou"
    WARNING:  RequestId=20200610175910c231f60b0b107670, ErrorCode=NoSuchPartition, ErrorMessage=Error: The specified partition does not exist. from:http://dt.cn-hangzhou.maxcompute.aliyun.com
    CONTEXT:  table=odps_tt_err_1_prt_20200621_2_prt_shanghai, partition="pt=20200621,region=shanghai"
     count
    -------
      4000
    (1 row)
    Time: 4587.069 ms
    postgres=# alter table odps_tt_err drop partition "20200621";
    NOTICE:  dropped partition "20200621" for relation "odps_tt_err" and its children
    ALTER TABLE
    Time: 59.416 ms
    postgres=# select count(1) from odps_tt_err;
     count
    -------
      4000
    (1 row)
    Time: 3429.979 ms
    postgres=#

3.4 Adding a subpartition

This example uses the odps_src partitioned foreign table.

-- Add a level-1 subpartition. 
-- Level-2 subpartitions are created automatically based on the template.
ALTER TABLE odps_src ADD PARTITION "20170603" VALUES ('20170603');

-- Add a level-2 subpartition.
ALTER TABLE odps_src ALTER PARTITION "20170603" ADD PARTITION "nanjing" VALUES ('nanjing');

3.5 Deleting a subpartition

This example uses the odps_src partitioned foreign table.

-- Delete a level-1 subpartition.
-- This also deletes its cascaded level-2 subpartitions.
ALTER TABLE odps_src DROP PARTITION "20170602";

• Delete a level-2 subpartition

-- Delete a level-2 subpartition.
ALTER TABLE odps_src ALTER PARTITION "20170601" DROP PARTITION "hangzhou";

Queries on MaxCompute foreign tables use the Foreign Scan operator in AnalyticDB for PostgreSQL. Therefore, the process is similar to querying a regular table. The following sections use TPC-H queries to illustrate common use cases.

TPC-H Query Q1 is a typical example of single-table aggregation and filtering. This example runs a Q1 query on the odps_lineitem MaxCompute foreign table.

-- Define the MaxCompute foreign table 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
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus;

To import data, perform the following steps:

1. Create a MaxCompute foreign table in AnalyticDB for PostgreSQL.

2. Run one of the following statements to import data in parallel.

-- Use INSERT INTO...SELECT
INSERT INTO <local_destination_table> SELECT * FROM <maxcompute_foreign_table>;
-- Use CREATE TABLE AS SELECT
CREATE TABLE <local_destination_table> AS SELECT * FROM <maxcompute_foreign_table>;

• Example 1: Use an INSERT statement to import data from odps_lineitem to a local columnar table.

-- Create a local columnar table.
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);
-- Import data from odps_lineitem to the local columnar table.
INSERT INTO aocs_lineitem SELECT * FROM odps_lineitem;

• Example 2: Use a CREATE TABLE AS statement to import data from odps_lineitem to a local heap table.

CREATE TABLE heap_lineitem AS SELECT * FROM odps_lineitem DISTRIBUTED BY (l_orderkey);

This example uses TPC-H Query Q19 to join a local columnar table, aocs_lineitem, with a MaxCompute foreign table, odps_part.

-- TPC-H Q19
SELECT
    sum(l_extendedprice * (1 - l_discount)) as revenue
FROM
    aocs_lineitem,          -- A local columnar table
    odps_part               -- A MaxCompute foreign table
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'
    );

INSERT INTO odps_lineitem SELECT * FROM heap_lineitem;

Data type mappings

The following table describes the mappings between MaxCompute and AnalyticDB for PostgreSQL data types. Define column types in your foreign table according to these mappings.