Hologres:CREATE FOREIGN TABLE

Limits

Only Hologres V1.3 and later supports the Layer 3 model of MaxCompute. The Layer 3 model adds a schema layer between the project and the table. For more information, see Schema operations. To create a foreign table for a MaxCompute project that uses the Layer 3 model, you must upgrade your Hologres instance if its version is earlier than V1.3. If the upgrade fails, see Common errors when you prepare for an upgrade. You can also join the Hologres DingTalk group for feedback. For more information, see How do I get more online support?.

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.

failed to import foreign schema:Table not found - table_xxx

Examples

• Query data directly from a foreign table.

  After you create a foreign table in Hologres, you can query data directly from the MaxCompute foreign table. The following SQL statements provide an example.

  -- Two-layer model
  CREATE FOREIGN TABLE src_pt(
    id text, 
    pt text) 
  SERVER odps_server 
  OPTIONS(project_name '<odps_project>', table_name '<odps_table>');
  
  -- Layer 3 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 Accelerate queries on MaxCompute data based on foreign tables.

• Import data from a foreign table and then query the data.

  You can import data from MaxCompute into Hologres before you query the data. The following statements provide an example.

  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 using SQL.

Data type mapping between MaxCompute and Hologres