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

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

Parameters

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

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. Procedure:

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.

2. In the top navigation bar of the HoloWeb page, choose Metadata Management > MaxCompute Query Acceleration and select Create Foreign Table.

   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, configure the parameters as required.

   Section	Parameter	Description
   MaxCompute Data Source	Project Name	The name of the MaxCompute project.
   	Schema Name	The name of the schema in MaxCompute. If your MaxCompute project uses the two-layer model, this parameter is not displayed by default. If your MaxCompute project uses the three-layer model, you can select an authorized schema from the drop-down list.
   Destination Table Position	Schema Name	The name of the schema. You can select the default schema named public or a custom schema.
   Advanced Settings	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.
   	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 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.