Syntax and limits of the IMPORT FOREIGN SCHEMA statement - Hologres

You can execute the IMPORT FOREIGN SCHEMA statement to create multiple foreign tables at a time in Hologres. This topic describes the syntax and limits of the IMPORT FOREIGN SCHEMA statement.

Limits

We recommend that you use the LIMIT TO clause to specify the source tables and enclose the table names in parentheses "( )" in the IMPORT FOREIGN SCHEMA statement. Otherwise, foreign tables are created in Hologres to map all the tables in the specified MaxCompute project.
Only Hologres V1.1.26 and later allow you to add prefixes or suffixes to the names of foreign tables created by using the IMPORT FOREIGN SCHEMA statement. If the version of your Hologres instance is earlier than V1.1.26, manually upgrade your Hologres instance in the Hologres console or join a DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a DingTalk group, see Obtain online support for Hologres.
Only Hologres V1.3 and later support MaxCompute projects that use the three-layer model. This model allows you to create schemas in MaxCompute projects and use these schemas to classify objects such as tables. For more information, see Schema-related operations. If you want to create a foreign table in a Hologres instance to map a table in a MaxCompute project that uses the three-layer model and the version of your Hologres instance is earlier than V1.3, manually upgrade your Hologres instance in the Hologres console or join a DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a DingTalk group, see Obtain online support for Hologres.

Syntax

The following sample code provides an example on how to use the IMPORT FOREIGN SCHEMA statement to create multiple foreign tables at a time in Hologres:

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

Parameters

The following table describes the parameters in the preceding syntax.

Parameter	Description
remote_schema	MaxCompute two-layer model: the name of the MaxCompute project to which the source tables belong. MaxCompute three-layer model: the name of the MaxCompute project and the name of the schema to which the source tables belong, in the format of `odps_project_name#odps_schema_name`. If your MaxCompute project uses the three-layer model but you specify only the name of the MaxCompute project, an error message is returned. Example: `failed to import foreign schema:Table not found - table_xxx`
table_name	The names of the MaxCompute source tables.
server_name	The name of the server on which the MaxCompute source tables are stored. Default value: odps_server. You can use the odps_server server that is created at the underlying layer of Hologres. For more information, see postgres_fdw.
local_schema	The name of the schema to which the Hologres foreign tables belong, such as public.
options	Hologres supports the following options: if_table_exist: specifies the operation to perform if a foreign table that you want to create already exists. Valid values: error: returns an error message and does not recreate the foreign table. This is the default value. ignore: skips the creation of the foreign table and creates other foreign tables. update: updates the existing foreign table. if_unsupported_type: specifies the operation to perform if a foreign table that you want to create contains data types that are not supported by Hologres. Valid values: error: returns an error message indicating that specific foreign tables fail to be created and lists the tables that contain unsupported data types. skip: skips the creation of the foreign tables that contain unsupported data types and lists the skipped tables. This is the default value. prefix: the prefix of the foreign tables that you want to create. You can use this option only in Hologres V1.1.26 and later. suffix: the suffix of the foreign tables that you want to create. You can use this option only in Hologres V1.1.26 and later.

Note

Hologres supports only foreign tables that are sourced from MaxCompute. The created foreign tables are named the same as the MaxCompute source tables.

Examples

MaxCompute projects that use the two-layer model

In the examples, foreign tables are created in Hologres to map the tables in the public dataset public_data in MaxCompute.

Example 1: Create a foreign table in the schema named public. If the foreign table that you want to create already exists in Hologres, update the existing table.
```
IMPORT FOREIGN SCHEMA public_data LIMIT TO
(customer) 
  FROM server odps_server INTO PUBLIC options(if_table_exist 'update');
```

Example 2: Create multiple foreign tables at a time in the schema named public.

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

Example 3: Create a schema named testdemo and create multiple foreign tables at a time in the 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;

Example 4: Create multiple foreign tables at a time in the schema named public. If a foreign table that you want to create already exists, an error is returned.
```
IMPORT FOREIGN SCHEMA public_data LIMIT to
(customer,
  customer_address) 
  FROM server odps_server INTO PUBLIC options(if_table_exist 'error');
```
Example 5: Create multiple foreign tables at a time in the schema named public. If a foreign table that you want to create already exists, skip the creation of this foreign table.
```
IMPORT FOREIGN SCHEMA public_data LIMIT to
(customer,
  customer_address) 
  FROM server odps_server INTO PUBLIC options(if_table_exist 'ignore');
```

MaxCompute projects that use the three-layer model
In this example, a foreign table is created in Hologres to map the odps_region_10g table in the tpch_10g schema of 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');
```