Hologres:IMPORT FOREIGN SCHEMA

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 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 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

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

Parameters

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 that you want to create 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 that you want to create 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 that you want to create 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 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');

Create multiple foreign tables at a time in HoloWeb

You can use HoloWeb to create multiple foreign tables at a time in a visualized manner, without the need to write SQL statements. The following procedure shows the steps to create multiple foreign tables.

1. Log on to the Hologres console. In the left-side navigation pane, click Go to HoloWeb to go to the HoloWeb page. For more information, see Connect to HoloWeb and perform queries.

2. In the top navigation bar of the HoloWeb page, choose Metadata Management > MaxCompute Acceleration and select 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.

   

3. On the Create Multiple Foreign Tables tab, set the parameters as required.

   Section	Parameter	Description
   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 the 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 prevent 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. Ignore and Skip Table of 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.