Hologres:CREATE FOREIGN TABLE

Limits

Only Hologres V1.3 and later support the three-layer model of MaxCompute. 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 Hologres DingTalk group, see Obtain online support for Hologres.

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.

failed to import foreign schema:Table not found - table_xxx

Examples

• Directly query MaxCompute data

  You can create a foreign table in Hologres to query the data mapped from the MaxCompute table. For example, you can execute the following SQL statements:

  -- MaxCompute two-layer model
  CREATE FOREIGN TABLE src_pt(
    id text, 
    pt text) 
  SERVER odps_server 
  OPTIONS(project_name '<odps_project>', table_name '<odps_table>');
  
  -- MaxCompute three-layer model
  CREATE FOREIGN TABLE src_pt(
    id text,
    pt text)
  SERVER odps_server
  OPTIONS(project_name '<odps_project>#<odps_schema>', 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