Hologres:IMPORT FOREIGN SCHEMA

Feature details

IMPORT FOREIGN SCHEMA is a feature that lets you create multiple foreign tables at a time. It automatically maps the table schemas from a remote data source to Hologres foreign tables. This saves you from creating each table manually. This feature supports the following two types of data sources:

• MaxCompute tables: Import foreign tables in a batch from MaxCompute projects that use the two-layer or three-layer model.

• Paimon tables in DLF: Create Hologres foreign tables in a batch from Paimon tables in Alibaba Cloud Data Lake Formation (DLF).

Limits

• You must use the IMPORT FOREIGN SCHEMA statement with the LIMIT TO clause and enclose the table names in parentheses. If you do not include this clause, the system creates foreign tables in Hologres for all tables in the target MaxCompute workspace.

• Only Hologres V1.1.26 and later allow you to add prefixes or suffixes to the names of foreign tables created using the IMPORT FOREIGN SCHEMA statement. If your instance is a version earlier than V1.1.26, see Common errors that occur when you prepare for an upgrade or join the Hologres DingTalk group for feedback. For more information, see How can I get more online support?.

• Only Hologres V1.3 and later support MaxCompute projects that use the three-layer model. This model lets you create schemas in MaxCompute projects and use these schemas to classify objects such as tables. For more information, see Schema 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 your Hologres version is earlier than V1.3, see Common errors that occur when you prepare for an upgrade or join the Hologres DingTalk group for feedback. For more information, see How can I get more online support?.

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 this example, foreign tables are created in Hologres in a batch for tables in the MaxCompute public dataset public_data.

  • Example 1: Create a new foreign table for the public schema. If the table exists, it is updated.

    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 in a batch for the public schema.

     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 then create multiple foreign tables in a batch.

    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 in a batch in the public schema. If a foreign table already exists, an error is reported.

    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 in a batch in the public schema. If a foreign table already exists, it is skipped.

    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

  Create a Hologres foreign table for the odps_region_10g table in the tpch_10g schema of the MaxCompute odps_hologres 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, you can create Hologres foreign tables directly by specifying the data source name, such as github_events.

  • Example: Create a foreign table in the public schema. If the table already exists, it is updated. For more information, see Access Paimon tables using DLF.

    IMPORT FOREIGN SCHEMA github_events
    limit to (customer) 
    FROM SERVER paimon_server into public
    options (if_table_exist 'update');