Description

You can execute the CREATE FOREIGN TABLE statement to create a foreign table.

Note In the current version, you can only create foreign tables sourced from MaxCompute.

Synopsis

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type }
  [, ... ]
  ] )
SERVER odps_server
[ OPTIONS ( option 'value' [, ... ] ) ]

Parameters

  • SERVER: the MaxCompute server. Hologres has encapsulated the server named odps_server at the underlying layer. You can directly use the server. For more information, see PostgreSQL FDW.
  • OPTIONS: the options to be specified. You must use project_name to specify the MaxCompute project name and use table_name to specify the MaxCompute table name.
  • Each data type in the foreign table must match the corresponding data type in the mapping MaxCompute table.

Examples

  1. Directly query MaxCompute data.

    You can create a foreign table and then directly query the foreign table for data in the mapping MaxCompute table. Sample SQL statements are as follows:

    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 to accelerate queries of MaxCompute data.

  2. Import MaxCompute data to Hologres for queries.

    You can also import MaxCompute data to Hologres for queries. Sample SQL statements are as follows:

    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 Use SQL statements to import MaxCompute to Hologres.

Data types mapping between MaxCompute and Hologres when you create a foreign table

The following table lists the mappings between MaxCompute and Hologres data types supported when you execute the CREATE FOREIGN TABLE statement to create a foreign table.

MaxCompute data type Hologres data type Supported
STRING and VARCHAR TEXT Yes
BIGINT INT8 Yes
INT INT4 and INT Yes
SMALLINT INT8, which is manually defined by executing the set hg_enable_convert_type_for_foreign_table = true statement Yes
TINYINT Not supported No
FLOAT FLOAT4 and REAL Yes
DOUBLE FLOAT and FLOAT8 Yes
DATETIME TIMESTAMPTZ Yes
DECIMAL NUMERIC Yes
CHAR Not supported No
BINARY Not supported No

For more information about the data types supported by the current Hologres version, see Data types.

Note :
  1. In MaxCompute, DATETIME is of date and time types and uses UTC+8 as the standard system time zone. The time ranges from 0000-01-01 to 9999-12-31, which is accurate to milliseconds.
  2. 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.
  3. If the MaxCompute table contains certain fields whose data types are not supported by Hologres, you can still query other fields.

Data types mapping between MaxCompute and Hologres data types supported when you execute the IMPORT FOREIGN SCHEMA statement to create foreign tables

The following table lists the mappings between MaxCompute and Hologres data types supported when you execute the IMPORT FOREIGN SCHEMA statement to create foreign tables.

MaxCompute data type Hologres data type Supported
STRING and VARCHAR TEXT Yes
BIGINT INT8 Yes
INT INT4 and INT Yes
SMALLINT INT2 No
TINYINT Not supported No
FLOAT FLOAT4 and REAL Yes
DOUBLE FLOAT and FLOAT8 Yes
DATETIME TIMESTAMPTZ Yes
DECIMAL NUMERIC Yes
CHAR Not supported No
BINARY Not supported No