AnalyticDB:Access MaxCompute data using ODPS Foreign Tables

Step 1: Create a MaxCompute server

The server object tells AnalyticDB for PostgreSQL how to reach MaxCompute. Specify at least one endpoint option.

If you specify both options, tunnel_endpoint takes precedence. If you specify only odps_endpoint, AnalyticDB for PostgreSQL routes requests to the corresponding Tunnel endpoint automatically.

We recommend that you use the MaxCompute Tunnel endpoint in the Alibaba Cloud classic network or a virtual private cloud (VPC). If you use a VPC endpoint, make sure that the AnalyticDB for PostgreSQL instance and the MaxCompute project reside in the same zone. Accessing MaxCompute data over the Internet via the Tunnel endpoint costs USD 0.1166 per GB.

For the full list of MaxCompute endpoints, see Endpoints.

Step 2: Create a user mapping

The user mapping binds a database user to credentials for accessing the MaxCompute server.

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
  SERVER odps_serv
  OPTIONS (
    id  '<odps-access-key-id>',
    key '<odps-access-key-secret>'
  );

Specify who the mapping applies to:

• username — a specific existing database account

• CURRENT_USER or USER — the currently connected user

• PUBLIC — all database accounts

Step 3: Create a MaxCompute foreign table

Foreign tables map directly to MaxCompute tables. The project and table options are always required. The partition option is used only for lowest-level partitioned foreign tables.

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

Lowest-level partitioned foreign tables

A lowest-level partitioned foreign table maps to one specific partition of a partitioned MaxCompute table. Set the partition option to the full path of the target partition in key=value format, with no spaces.

The following example maps to the hangzhou subpartition under the 20170601 partition of a two-level partitioned MaxCompute table:

-- The MaxCompute table src is partitioned by pt (level 1) and region (level 2).
CREATE FOREIGN TABLE odps_src_20170601_hangzhou (
    key   text,
    value bigint
) SERVER odps_serv
  OPTIONS (
    project   'odps_fdw',
    table     'src',
    partition 'pt=20170601,region=hangzhou'  -- Full path of the lowest-level partition
  );

Rules for the partition option:

• Use key=value format with no spaces.

• For multi-level partitions, separate pairs with commas: pt=20170601,region=hangzhou.

• The path must be the full path to the lowest-level partition. Partial paths (e.g., pt=20170601 or region=hangzhou alone) are not valid.

Partitioned foreign tables

A partitioned foreign table maps to an entire partitioned MaxCompute table and mirrors its partition structure inside AnalyticDB for PostgreSQL. Use PARTITION BY LIST to define partitions.

The following example creates a partitioned foreign table for the src MaxCompute table, which has two partition levels (pt and region):

CREATE FOREIGN TABLE odps_src (
    key    text,
    value  bigint,
    pt     text,    -- Level-1 partition key (must be appended after data columns)
    region text     -- Level-2 partition key (must be appended after data columns)
) SERVER odps_serv
  OPTIONS (
    project 'odps_fdw',
    table   'src'
  )
  PARTITION BY LIST (pt)
  SUBPARTITION BY LIST (region)
      SUBPARTITION TEMPLATE (
          SUBPARTITION hangzhou VALUES ('hangzhou'),
          SUBPARTITION shanghai VALUES ('shanghai')
      )
  ( PARTITION "20170601" VALUES ('20170601'),
    PARTITION "20170602" VALUES ('20170602') );

Requirements that differ from partitioned tables in MaxCompute:

• Append partition key columns after all data columns. For multi-level partitions, the order must match the MaxCompute partition hierarchy.

• Use the LIST partitioning method.

• Do not set the partition option. If set, the foreign table maps to a specific lowest-level partition instead of the whole table.

If a partition or subpartition in the foreign table has no matching partition in MaxCompute, a warning appears when you query the table. Delete the unmatched partition or subpartition from the foreign table to clear the warning. See Delete partitions or subpartitions from a partitioned foreign table.

Manage partitions in a partitioned foreign table

The following examples use the odps_src partitioned foreign table created above.

Add partitions or subpartitions

Add a partition — subpartitions are created automatically based on the subpartition template:

alter table odps_src add partition "20170603" values(20170603);

Add a subpartition to an existing partition:

alter table odps_src alter partition "20170603" add partition "nanjing" values('nanjing');

Delete partitions or subpartitions from a partitioned foreign table

Delete a partition — all cascaded subpartitions are also deleted:

alter table odps_src drop partition "20170602";

Delete a single subpartition:

alter table odps_src alter partition "20170601" drop partition "hangzhou";

Query a MaxCompute foreign table

Run foreign scans directly on a foreign table just as you would on a local table. The following example runs a TPC-H Q1 aggregation query on odps_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;

Import MaxCompute data into a local table

For large datasets or repeated queries, import data from a foreign table into a local table to avoid repeatedly scanning MaxCompute over the network.

Use INSERT INTO to import into an existing table:

INSERT INTO <destination-table> SELECT * FROM <foreign-table>;

Or use CREATE TABLE AS to create and populate a new table in one step:

CREATE TABLE <destination-table> AS SELECT * FROM <foreign-table>;

Example 1: Import odps_lineitem into an append-optimized column store (AOCS) table:

-- Create a local AOCS 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 the foreign table
INSERT INTO aocs_lineitem SELECT * FROM odps_lineitem;

Example 2: Import odps_lineitem into a heap table using CREATE TABLE AS:

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

Join a foreign table with a local table

Query a foreign table and a local table together in a single SQL statement. The following example runs a TPC-H Q19 join query between the odps_part foreign table and the local aocs_lineitem table:

-- TPC-H Q19
select
    sum(l_extendedprice * (1 - l_discount)) as revenue
from
    aocs_lineitem,   -- local AOCS table
    odps_part        -- 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'
    );

Write data from AnalyticDB for PostgreSQL to MaxCompute

Use INSERT INTO to write data from a local table back to a MaxCompute foreign table:

INSERT INTO odps_lineitem SELECT * FROM heap_lineitem;

ARRAY column types are not supported when writing data to MaxCompute foreign tables.