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, submit a ticket or join the Hologres DingTalk group for technical support.
  • Only Hologres V1.3 and later support the three-layer model of MaxCompute. This model allows you to create schemas in MaxCompute projects and use these schemas to classify objects such as tables. For more information, see MaxCompute schemas. 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, submit a ticket to update your instance.

Syntax

The following sample code provides an example on how to use the IMPORT FOREIGN SCHEMA statement to create foreign tables 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 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 names of the MaxCompute project and 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 tables that the foreign tables to be created map.
server_name The name of the server on which the MaxCompute 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 in which the Hologres foreign tables reside. Example: public.
options Hologres supports the following four options:
  • if_table_exist: specifies the operation to perform when a foreign table to be created is named the same as an existing foreign table in Hologres. Valid values:
    • error: returns an error and does not create the foreign table that is named the same as an existing foreign table in Hologres. This is the default value.
    • ignore: skips the creation of the foreign table that is named the same as an existing foreign table in Hologres and creates other foreign tables.
    • update: creates the foreign table that is named the same as an existing foreign table in Hologres and uses the created foreign table to replace the existing foreign table.
  • if_unsupported_type: specifies the operation to perform when a foreign table to be created involves 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 involve unsupported data types.
    • skip: skips the creation of the foreign tables that involve unsupported data types and lists the skipped tables. This is the default value.
  • prefix: the prefix of the foreign tables to be created. You can use this option only in Hologres V1.1.26 and later.
  • suffix: the suffix of the foreign tables to be created. 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 tables.

Examples

  • MaxCompute two-layer model

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

    • Example 1: Create a foreign table in the schema named public. If the foreign table to be created is named the same as an existing foreign table 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 to be created is named the same as an existing foreign table in Hologres, 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 to be created is named the same as an existing foreign table in Hologres, 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 three-layer model
    In this example, a foreign table is created in Hologres to map the odps_region_10g table in thetpch_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');

Create multiple foreign tables at a time in the HoloWeb console

You can use HoloWeb to create multiple foreign tables at a time in a visualized way, without the need to write SQL statements. To do so, perform the following steps:

  1. Log on to the HoloWeb console. For more information, see HoloWeb quick start.
  2. In the top navigation bar of the HoloWeb console, choose Metadata Management > MaxCompute Acceleration > Create Multiple Foreign Tables.

    You can also click Instances Connected in the left-side navigation pane of the Metadata Management tab. Click the instance and the database that you want to manage. Right-click the schema that you want to manage and select Create Multiple Foreign Tables.

    Create an internal table
  3. On the Create Multiple Foreign Tables tab, set the parameters as required. Create multiple foreign tables at a time
    Category Parameter Description
    Basic information Instance Name The name of the current instance.
    Database The name of the Hologres database in which the foreign tables are to be created.
    Destination Location Schema The name of the schema.

    You can select the default schema public or a custom schema.

    Source MaxCompute Tables Type The type of the foreign table. The default value is MaxCompute, which cannot be changed.
    Server The name of the server on which the MaxCompute tables are stored. You can use the odps_server server that is created at the underlying layer of Hologres. For more information, see postgres_fdw.
    Source Project The name of the MaxCompute project.
    Data Source
    • Entire Project: creates foreign tables for all the tables in the MaxCompute project at a time.
    • Selected Tables: creates foreign tables only for specified tables in the MaxCompute project. You can search for required tables based on table names or keywords.
      Note If you select Selected Tables and search for required tables, a maximum of 200 tables are displayed in the search results. The excess tables are not displayed, but foreign tables can be created for the excess tables.

      For example, the MaxCompute project has 203 tables whose names contain test. When you enter test to search for tables, only 200 tables are displayed in the search results. However, foreign tables can be created for all 203 tables.

    Advanced Settings Processing Rule for Table Name Conflicts
    • Ignore Conflicts and Continue Creating Tables: If a foreign table that you want to create is named the same as an existing foreign table in the database, the system skips the creation of this table and continues to create other tables.
    • Change Table Names: If a foreign table that you want to create is named the same as an existing foreign table in the database, the system updates data in the existing foreign table.
    • Report Error and Create No Table: If a foreign table that you want to create is named the same as an existing foreign table in the database, an error is reported to avoid repeated creation.
    Processing Rule for Unsupported Data Types
    • Report Error and Stop Import: If specific data types in the source tables are not supported by Hologres, an error is reported and data import fails.
    • Skip Unsupported Fields: If specific data types in the source tables are not supported by Hologres, the system imports data from all fields except for fields of the unsupported data types.
  4. Click Run. Multiple foreign tables are created at a time.

Data type mappings

For more information about data type mappings between MaxCompute and Hologres when you create multiple foreign tables at a time, see Data type mappings between MaxCompute and Hologres when you create multiple MaxCompute tables at a time.