Access MaxCompute foreign tables - Hologres - Alibaba Cloud Documentation Center

Hologres lets you create foreign tables to accelerate queries on MaxCompute data. This way, you can directly access and analyze data stored in MaxCompute using foreign tables in Hologres. This improves query efficiency and simplifies data processing.

Permission requirement

This feature requires granting users permission to access the MaxCompute project and tables. See Manage user permissions by using commands.

Data type mappings between MaxCompute and Hologres

See Data type mappings between MaxCompute and Hologres.

Method comparison

Solution	Scenarios	Highlights
CREATE FOREIGN TABLE	Accessing a small number of tables, querying a subset of columns, and working with stable table schemas.	Manual table creation, with flexible column definitions.
IMPORT FOREIGN SCHEMA	Batch mapping of all tables under a schema or database.	Automatic re of all tables in a schema.
Auto Load	Accessing many tables, with frequent structure changes.	Automatically detects source table changes and supports on-demand or full load.

CREATE FOREIGN TABLE

Use CREATE FOREIGN TABLE to create MaxCompute foreign tables. This statement lets you customize table names, select specific columns, and add comments. This section provides examples of how to use the CREATE FOREIGN TABLE statement to map non-partitioned and partitioned tables from MaxCompute.

To create a foreign table visually, use the HoloWeb console. For more information, see Create a MaxCompute foreign table in the HoloWeb console.

Example 1: Query data from a MaxCompute non-partitioned table

Create a non-partitioned table in MaxCompute and import data into the table. This example uses the customer table from the MaxCompute public dataset BIGDATA_PUBLIC_DATASET.tpcds_10t as the source table.

Create a MaxCompute table

-- The DDL statement of the table in the MaxCompute public dataset
CREATE TABLE IF NOT EXISTS public_data.customer(
  c_customer_sk BIGINT,
  c_customer_id STRING,
  c_current_cdemo_sk BIGINT,
  c_current_hdemo_sk BIGINT,
  c_current_addr_sk BIGINT,
  c_first_shipto_date_sk BIGINT,
  c_first_sales_date_sk BIGINT,
  c_salutation STRING,
  c_first_name STRING,
  c_last_name STRING,
  c_preferred_cust_flag STRING,
  c_birth_day BIGINT,
  c_birth_month BIGINT,
  c_birth_year BIGINT,
  c_birth_country STRING,
  c_login STRING,
  c_email_address STRING,
  c_last_review_date_sk STRING);

View data in the table.

-- Query data from the table
SET odps.namespace.schema=true;
SELECT * FROM BIGDATA_PUBLIC_DATASET.tpcds_10t.customer;

Results:

Create a foreign table in Hologres to map the MaxCompute table. Sample statements:

SET hg_enable_convert_type_for_foreign_table = true;
CREATE FOREIGN TABLE customer (
    "c_customer_sk" int8,
    "c_customer_id" text,
    "c_current_cdemo_sk" int8,
    "c_current_hdemo_sk" int8,
    "c_current_addr_sk" int8,
    "c_first_shipto_date_sk" int8,
    "c_first_sales_date_sk" int8,
    "c_salutation" text,
    "c_first_name" text,
    "c_last_name" text,
    "c_preferred_cust_flag" text,
    "c_birth_day" int8,
    "c_birth_month" int8,
    "c_birth_year" int8,
    "c_birth_country" text,
    "c_login" text,
    "c_email_address" text,
    "c_last_review_date_sk" text)
SERVER odps_server
OPTIONS (project_name 'BIGDATA_PUBLIC_DATASET.tpcds_10t', table_name 'customer');

The following table describes the parameters in the preceding statements.

Parameter	Description
`SERVER`	The server on which you want to create the foreign table. Set it to `odps_server` that is created at the underlying layer of Hologres. For more information, see postgres_fdw.
`project_name`	If your MaxCompute project has enabled the schema, set `project_name` to the MaxCompute project and schema name in the format: `odps_project_name#odps_schema_name`. If your MaxCompute project hasn't enabled the schema, set `project_name` to your MaxCompute project name. For more information about the three-layer model, see Schema operations.
`table_name`	The MaxCompute table to map.

Directly query data from the MaxCompute table using the foreign table. Sample statement:
```
SELECT * FROM customer LIMIT 10;
```
Important
To prevent query errors, ensure that your account has the Select permission on the MaxCompute table. For more information, see Permission requirement.

Example 2: Query data from a MaxCompute partitioned table

Create a partitioned table in MaxCompute and import data into it. This example uses the ods_enterprise_share_trade_h table from the MaxCompute public dataset BIGDATA_PUBLIC_DATASET.finance as the source table.

Create a partitioned table

-- The DDL statement of the table in the public dataset
CREATE TABLE IF NOT EXISTS public_data.ods_enterprise_share_trade_h(
  code STRING COMMENT 'Code',
  name STRING COMMENT 'Name',
  industry STRING COMMENT 'Industry',
  area STRING COMMENT 'Area',
  pe STRING COMMENT 'PE ratio',
  outstanding STRING COMMENT 'Outstanding shares',
  totals STRING COMMENT 'Total shares (10,000)',
  totalassets STRING COMMENT 'Total assets (10,000)',
  liquidassets STRING COMMENT 'Liquid assets',
  fixedassets STRING COMMENT 'Fixed assets',
  reserved STRING COMMENT 'Reserve fund',
  reservedpershare STRING COMMENT 'Reserve per share',
  eps STRING COMMENT 'EPS',
  bvps STRING COMMENT 'BVPS',
  pb STRING COMMENT 'PB ratio',
  timetomarket STRING COMMENT 'Time to market',
  undp STRING COMMENT 'Undistributed profit',
  perundp STRING COMMENT 'Undistributed profit per share',
  rev STRING COMMENT 'YoY revenue (%)',
  profit STRING COMMENT 'YoY profit (%)',
  gpr STRING COMMENT 'Gross profit margin (%)',
  npr STRING COMMENT 'Net profit margin (%)',
  holders_num STRING COMMENT 'Number of shareholders') 
  PARTITIONED BY (ds STRING) STORED AS ALIORC TBLPROPERTIES ('comment'='Data import date');

View data in the table.


-- Query data from a specific partition in MaxCompute
SET odps.namespace.schema=true;
SELECT * FROM BIGDATA_PUBLIC_DATASET.finance.ods_enterprise_share_trade_h WHERE ds = '20170113';

Sample results:

Create a foreign table in Hologres to map the table in MaxCompute. Sample statement:

CREATE FOREIGN TABLE public.foreign_ods_enterprise_share_trade_h (
    "code" text,
    "name" text,
    "industry" text,
    "area" text,
    "pe" text,
    "outstanding" text,
    "totals" text,
    "totalassets" text,
    "liquidassets" text,
    "fixedassets" text,
    "reserved" text,
    "reservedpershare" text,
    "eps" text,
    "bvps" text,
    "pb" text,
    "timetomarket" text,
    "undp" text,
    "perundp" text,
    "rev" text,
    "profit" text,
    "gpr" text,
    "npr" text,
    "holders_num" text,
    "ds" text
)
SERVER odps_server
OPTIONS (project_name 'BIGDATA_PUBLIC_DATASET#finance', table_name 'ods_enterprise_share_trade_h');
comment on foreign table public.foreign_ods_enterprise_share_trade_h is 'Stock transaction history';
comment on column public.foreign_ods_enterprise_share_trade_h."code" is 'Stock symbol';
comment on column public.foreign_ods_enterprise_share_trade_h."name" is 'Name';
comment on column public.foreign_ods_enterprise_share_trade_h."industry" is 'Industry';
comment on column public.foreign_ods_enterprise_share_trade_h."area" is 'Country/region';
comment on column public.foreign_ods_enterprise_share_trade_h."pe" is 'Price-earnings ratio';
comment on column public.foreign_ods_enterprise_share_trade_h."outstanding" is 'Outstanding';
comment on column public.foreign_ods_enterprise_share_trade_h."totals" is 'Total shares outstanding (ten thousand)';
comment on column public.foreign_ods_enterprise_share_trade_h."totalassets" is 'Total assets (ten thousand)';
comment on column public.foreign_ods_enterprise_share_trade_h."liquidassets" is 'Liquid assets';
comment on column public.foreign_ods_enterprise_share_trade_h."fixedassets" is 'Fixed assets';
comment on column public.foreign_ods_enterprise_share_trade_h."reserved" is 'Reserved';
comment on column public.foreign_ods_enterprise_share_trade_h."reservedpershare" is 'Reserved per share';
comment on column public.foreign_ods_enterprise_share_trade_h."eps" is 'Earnings per share';
comment on column public.foreign_ods_enterprise_share_trade_h."bvps" is 'Book value per share';
comment on column public.foreign_ods_enterprise_share_trade_h."pb" is 'Price-to-book ratio';
comment on column public.foreign_ods_enterprise_share_trade_h."timetomarket" is 'Time to market';
comment on column public.foreign_ods_enterprise_share_trade_h."undp" is 'Undistributed profits';
comment on column public.foreign_ods_enterprise_share_trade_h."perundp" is 'Undistributed profits per share';
comment on column public.foreign_ods_enterprise_share_trade_h."rev" is 'Year-over-year revenue (%)';
comment on column public.foreign_ods_enterprise_share_trade_h."profit" is 'Year-over-year profit (%)';
comment on column public.foreign_ods_enterprise_share_trade_h."gpr" is 'Gross profit margin (%)';
comment on column public.foreign_ods_enterprise_share_trade_h."npr" is 'Net profit ratio (%)';
comment on column public.foreign_ods_enterprise_share_trade_h."holders_num" is 'Holders';

Query data from the MaxCompute partitioned table.
- Query the first 10 records:
```
SELECT * FROM foreign_ods_enterprise_share_trade_h limit 10;
```
- Query data from a specific partition:
```
SELECT * FROM foreign_ods_enterprise_share_trade_h 
WHERE ds = '20170113';
```
Important
To prevent query errors, ensure that your account has the Select permission on the MaxCompute table. For more information, see Permission requirement.

IMPORT FOREIGN SCHEMA

The IMPORT FOREIGN SCHEMA statement allows you to batch create multiple MaxCompute foreign tables. For more information, see IMPORT FOREIGN SCHEMA.

Auto Load

To handle scenarios with numerous source tables or frequent MaxCompute schema modifications (like column deletions, reordering, or data type changes), employ the Auto Load feature. It automatically synchronizes foreign table schemas with your MaxCompute tables, negating the need for manual updates and enhancing query efficiency. For more information, see Auto Load.