Query MaxCompute, OSS & DLF Data via Hologres Foreign Tables - Hologres

CREATE FOREIGN TABLE maps an external table in Hologres to a table in a connected data source — such as MaxCompute, Object Storage Service (OSS), Data Lake Formation (DLF), or another Hologres instance — so you can query that data using standard SQL without importing it first.

The odps_server foreign server is pre-configured in Hologres. You do not need to create it.

Syntax

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name (
  { column_name data_type }
  [, ... ]
)
SERVER odps_server
OPTIONS (
  project_name '<odps_project_name>[#<odps_schema_name>]',
  table_name '<odps_table_name>'
)

Parameters

Required parameters

Parameter	Description
`table_name`	Name of the foreign table to create in Hologres.
`SERVER odps_server`	The foreign server that connects to MaxCompute. `odps_server` is pre-configured in Hologres — do not replace this value. For the underlying implementation, see Postgres FDW.
`OPTIONS`	Key-value pairs that identify the MaxCompute source table. See the OPTIONS section below for details.

OPTIONS

The format of project_name depends on whether your MaxCompute project uses the two-layer or three-layer model.

Two-layer model (project → table):

Key	Value
`project_name`	Name of the MaxCompute project. Example: `'my_project'`
`table_name`	Name of the MaxCompute table to query. Example: `'my_table'`

Three-layer model (project → schema → table):

Key	Value
`project_name`	MaxCompute project name and schema name, joined with `#`. Example: `'my_project#my_schema'`
`table_name`	Name of the MaxCompute table to query. Example: `'my_table'`

Note

The data types of all columns in the Hologres foreign table must match the corresponding column data types in the MaxCompute table.

Usage notes

The three-layer model requires Hologres V1.3 or later. If your instance version is earlier than V1.3, upgrade it before creating foreign tables for three-layer model projects. If the upgrade fails, see Common errors when you prepare for an upgrade or contact support through the Hologres DingTalk group.
Using two-layer model syntax against a three-layer model project returns the following error: failed to import foreign schema:Table not found - table_xxx
If the MaxCompute table contains a column of an unsupported data type, you can still query the remaining supported columns as long as you do not select the unsupported column.
DATETIME uses the UTC+8 time zone. The value range is January 1, 0000 to December 31, 9999, with millisecond precision.
TIMESTAMPTZ includes a time zone offset. The value range is 4713 BC to 294276 AD, with microsecond precision.

Examples

Query data directly from a foreign table

Create a foreign table and query MaxCompute data without importing it.

Two-layer model:

CREATE FOREIGN TABLE src_pt (
  id text,
  pt text
)
SERVER odps_server
OPTIONS (project_name '<odps_project>', table_name '<odps_table>');

SELECT * FROM src_pt;

Three-layer model:

CREATE FOREIGN TABLE src_pt (
  id text,
  pt text
)
SERVER odps_server
OPTIONS (project_name '<odps_project>#<odps_schema>', table_name '<odps_table>');

SELECT * FROM src_pt;

For more information, see Accelerate queries on MaxCompute data based on foreign tables.

Import data and then query

Import MaxCompute data into a Hologres internal table first, then query from there.

-- Step 1: Create a foreign table pointing to the MaxCompute source
CREATE FOREIGN TABLE src_pt_odps (
  id text,
  pt text
)
SERVER odps_server
OPTIONS (project_name '<odps_project>', table_name '<odps_table>');

-- Step 2: Create an internal Hologres table to hold the imported data
BEGIN;
CREATE TABLE src_pt (
  id text,
  pt text
);
COMMIT;

-- Step 3: Import data from MaxCompute into the internal table
INSERT INTO src_pt SELECT * FROM src_pt_odps;

For more information, see Import data from MaxCompute using SQL.

Data type mapping

For the full mapping between MaxCompute and Hologres data types, see Data type mapping between MaxCompute and Hologres.

Hologres:CREATE FOREIGN TABLE