You can execute the CREATE FOREIGN TABLE statement to create foreign tables. Hologres allows you to create foreign tables to read data from MaxCompute, Object Storage Service (OSS), and Data Lake Formation (DLF), and across Hologres databases. This topic describes how to use the CREATE FOREIGN TABLE statement to create a foreign table.

Syntax

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type }
  [, ... ]
  ] )
SERVER odps_server
[ OPTIONS ( option 'value' [, ... ] ) ]
The following table describes the parameters in the syntax.
Note The following table describes the parameters that you must set when you create a MaxCompute foreign table. For information about how to create a foreign table to read data from OSS or DLF, or across Hologres databases, see Use DLF to read OSS data, or Query data across databases.
Parameter Description
SERVER The server where the foreign table resides.

You can use the odps_server server that is created at the underlying layer of Hologres. For more information, see postgres_fdw.

OPTIONS The options to be specified. You must set the following fields:
  • project_name: the name of the project where the MaxCompute table to be queried resides.
  • table_name: the name of the MaxCompute table to be queried.
Note The data types in the foreign table must match those in the source MaxCompute table.

Examples

  • Directly query MaxCompute data
    You can create a foreign table to query the data mapped from the source MaxCompute table. For example, you can execute the following SQL statements:
    CREATE FOREIGN TABLE src_pt(
      id text, 
      pt text) 
    SERVER odps_server 
    OPTIONS(project_name '<odps_project>', table_name '<odps_table>');
    
    SELECT * FROM src_pt;

    For more information, see Create a foreign table in Hologres to accelerate queries of MaxCompute data.

  • Import MaxCompute data to Hologres and query the data
    You can also import MaxCompute data to Hologres and query the data. For example, you can execute the following statements:
    CREATE FOREIGN TABLE src_pt_odps(
      id text,
      pt text) 
    SERVER odps_server 
    OPTIONS (project_name'<odps_project>', table_name'<odps_table>');
    
    BEGIN;
    CREATE TABLE src_pt(
     id text, 
      pt text);
    COMMIT;
    
    INSERT INTO src_pt SELECT * FROM src_pt_odps;

    For more information, see Import data from MaxCompute to Hologres by executing SQL statements.

Data type mappings between MaxCompute and Hologres when you create a foreign table

The following table describes the data type mappings between MaxCompute and Hologres when you create a foreign table.
MaxCompute data type Hologres data type Mapping
  • STRING
  • VARCHAR
TEXT Supported
BIGINT INT8 Supported
INT
  • INT4
  • INT
Supported
SMALLINT
  • INT2
  • INT8
Note You must specify the setting set hg_enable_convert_type_for_foreign_table = true to set the data type to INT8.
Not supported
TINYINT N/A Not supported
FLOAT
  • FLOAT4
  • REAL
Supported
DOUBLE
  • FLOAT
  • FLOAT8
Supported
BOOLEAN BOOL Supported
DATETIME TIMESTAMPTZ Supported
DECIMAL NUMERIC Supported
CHAR N/A Not supported
BINARY N/A Not supported
Note
  • In MaxCompute, DATETIME uses UTC+8 as the standard system time zone. The time ranges from 0000-01-01 to 9999-12-31 and is accurate to milliseconds.
  • Hologres supports the TIMESTAMPTZ type of PostgreSQL, including the time zone. The time ranges from 4713 BC to 294276 AD and is accurate to microseconds.
  • If the MaxCompute table contains fields whose data types are not supported by Hologres, you can query other fields.