This topic describes the syntax of the IMPORT FOREIGN SCHEMA statement for creating foreign tables in Hologres.

Description

You can execute the IMPORT FOREIGN SCHEMA statement to create one or more foreign tables at a time. Currently, the statement can only create foreign tables sourced from MaxCompute.

Note The created foreign tables are named the same as the mapping MaxCompute tables.

Synopsis

The IMPORT FOREIGN SCHEMA statement uses the following syntax:
IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema 
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parameters

  • remote_schema: the name of the MaxCompute project where the tables mapping the foreign tables to be created reside.
  • table_name: the names of the MaxCompute tables that the foreign tables to be created map.
  • server_name: the name of the MaxCompute server.
  • local_schema: the name of the Hologres schema in which the foreign tables will be created. By default, the foreign tables will be created in the public schema. You can create a schema and create foreign tables in the new schema.
  • options: the options to be used for creating the foreign tables. Currently, Hologres allows you to specify the following options in the IMPORT FOREIGN SCHEMA statement:
    1. 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:
      1. error: Throw an error indicating that the foreign table to be created is named the same as an existing foreign table in Hologres. No other foreign tables will be created by the current SQL statement. This is the default value.
      2. ignore: Skip the creation of the foreign table that is named the same as an existing foreign table in Hologres and create other foreign tables.
      3. update: Create the foreign table that is named the same as an existing foreign table in Hologres and use it to replace the existing foreign table in Hologres.
    2. if_unsupported_type: specifies the operation to perform when a foreign table to be created contains data types that Hologres does not support. Valid values:
      1. error: Throw an error indicating that the foreign tables fail to be created and list the tables that contain unsupported data types.
      2. skip: Skip the creation of the foreign tables that contain unsupported data types and list the skipped tables. This is the default value.

Examples

The following example shows you how to use the IMPORT FOREIGN SCHEMA statement in Hologres:

// Create a foreign table for the public schema.

import foreign schema <odpsproject_name> limit to (bank_data) from server odps_server into PUBLIC;

// Create a schema named testdemo and create foreign tables for it.

create schema testdemo;
import foreign schema odps_4_holoworkshop limit to (customer,lightning_cat_industry) from server odps_server into testdemo;
set search_path to testdemo;
\d

// Specify an option.
import foreign schema <odpsproject_name> limit to (customer) from server odps_server into testdemo options( if_table_exist 'error');


import foreign schema <odpsproject_name> limit to (customer) from server odps_server into testdemo options( if_table_exist 'ignore');


import foreign schema <odpsproject_name> limit to (customer) from server odps_server into testdemo options( if_table_exist 'update');
IMPORT FOREIGN SCHEMA

Limitation

We recommend that you use the limit to clause to specify table names and enclose the table names in parentheses (( )) in the IMPORT FOREIGN SCHEMA statement. Otherwise, foreign tables mapping all the tables in the specified MaxCompute project will be created in Hologres.