Automate Foreign Table Creation with IMPORT FOREIGN SCHEMA - Hologres

IMPORT FOREIGN SCHEMA creates foreign tables in batches by automatically mapping table schemas from a remote data source to Hologres. Use this statement instead of creating each foreign table manually.

Two data sources are supported:

MaxCompute tables: Import foreign tables from MaxCompute two-layer or three-layer models.
Paimon tables in Data Lake Formation (DLF): Import foreign tables from Paimon tables in DLF.

Syntax

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parameters

Parameter	Description
`remote_schema`	The name of the remote schema to import from. For the MaxCompute two-layer model: the project name. For the MaxCompute three-layer model: `odps_project_name#odps_schema_name`. For DLF: the metadatabase name.
`table_name`	The names of the tables to import. Used with `LIMIT TO` to specify a subset, or with `EXCEPT` to exclude specific tables.
`server_name`	The name of the foreign server. For MaxCompute: use `odps_server`, which is pre-created in Hologres. For DLF: use the name of the foreign server for your Paimon catalog.
`local_schema`	The name of the Hologres schema where the foreign tables are created—for example, `public`.
`options`	Controls behavior during import. See Options below.

Options

Option	Values	Description
`if_table_exist`	`error` (default), `ignore`, `update`	Action when a foreign table with the same name already exists. `error` stops creation of that table. `ignore` skips the table and continues importing others. `update` reimports and overwrites the existing table.
`if_unsupported_type`	`skip` (default), `error`	Action when a table contains data types that Hologres does not support. `skip` skips the affected tables and logs which ones were skipped. `error` stops the import and reports which tables contain unsupported types.
`prefix`	Any string	Prefix added to the names of the created Hologres foreign tables. Requires Hologres V1.1.26 or later.
`suffix`	Any string	Suffix added to the names of the created Hologres foreign tables. Requires Hologres V1.1.26 or later.

Usage notes

Always include the LIMIT TO clause to specify which tables to import. If omitted, Hologres creates foreign tables for all tables in the target MaxCompute project.
Hologres supports creating only MaxCompute foreign tables. Foreign table names must match the corresponding MaxCompute table names.
The prefix and suffix options require Hologres V1.1.26 or later. For earlier versions, see Common errors when preparing for an upgrade or contact support via online support.
The MaxCompute three-layer model requires Hologres V1.3 or later. Using two-layer syntax against a three-layer model returns an error like:
```
failed to import foreign schema:Table not found - table_xxx
```
For earlier versions, see Common errors when preparing for an upgrade.
For background on the MaxCompute three-layer model, see Schema operations.

Examples

MaxCompute two-layer model

The following examples import foreign tables from the public_data MaxCompute project into Hologres.

Import one table, update if it exists:

IMPORT FOREIGN SCHEMA public_data LIMIT TO
  (customer)
  FROM SERVER odps_server INTO public
  OPTIONS (if_table_exist 'update');

Import multiple tables in batch:

IMPORT FOREIGN SCHEMA public_data LIMIT TO (
  customer,
  customer_address,
  customer_demographics,
  inventory,
  item,
  date_dim,
  warehouse
)
FROM SERVER odps_server INTO public
OPTIONS (if_table_exist 'update');

Import into a custom schema:

CREATE SCHEMA testdemo;

IMPORT FOREIGN SCHEMA public_data LIMIT TO (
  customer,
  customer_address,
  customer_demographics,
  inventory,
  item,
  date_dim,
  warehouse
)
FROM SERVER odps_server INTO testdemo
OPTIONS (if_table_exist 'update');

SET search_path TO testdemo;

Error if a table already exists:

IMPORT FOREIGN SCHEMA public_data LIMIT TO (
  customer,
  customer_address
)
FROM SERVER odps_server INTO public
OPTIONS (if_table_exist 'error');

Skip tables that already exist:

IMPORT FOREIGN SCHEMA public_data LIMIT TO (
  customer,
  customer_address
)
FROM SERVER odps_server INTO public
OPTIONS (if_table_exist 'ignore');

MaxCompute three-layer model

The three-layer model adds a schema layer between the project and the table. Specify remote_schema as odps_project_name#odps_schema_name.

The following example imports the odps_region_10g table from the tpch_10g schema in the odps_hologres MaxCompute project:

IMPORT FOREIGN SCHEMA "odps_hologres#tpch_10g" LIMIT TO (
  odps_region_10g
)
FROM SERVER odps_server INTO public
OPTIONS (if_table_exist 'error', if_unsupported_type 'error');

DLF data source

For a DLF data source, specify the metadatabase name as remote_schema and the foreign server for your Paimon catalog as server_name.

The following example imports the customer table from the github_events DLF metadatabase. For setup steps, see Access a Paimon catalog based on DLF.

IMPORT FOREIGN SCHEMA github_events LIMIT TO (
  customer
)
FROM SERVER paimon_server INTO public
OPTIONS (if_table_exist 'update');

What's next

Postgres FDW — understand the foreign-data wrapper principles used by Hologres
Access a Paimon catalog based on DLF — set up the DLF foreign server before importing DLF tables
Schema operations — learn how the MaxCompute three-layer model works