Use foreign tables to accelerate queries on MaxCompute data - Hologres

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.

Permissions

To accelerate queries on MaxCompute data, you must grant users permission to access the MaxCompute project and tables. For more information, see Manage user permissions using commands.

Data type mappings

Data types in MaxCompute and Hologres have a one-to-one mapping. For more information, see data type mappings between MaxCompute and Hologres.

Solution introduction and selection

Solution	Scenarios	Technical features
Accelerate queries on MaxCompute data using CREATE FOREIGN TABLE	Accelerating queries on a small number of tables, querying a subset of columns, and using stable table schemas.	Allows you to manually create tables and flexibly define columns and comments.
Accelerate queries on MaxCompute data using IMPORT FOREIGN SCHEMA	Batch mapping of all tables at the schema or database level.	Automatically synchronizes the schemas of all tables in a specified schema.
Accelerate queries on MaxCompute data using Auto Load	Working with many tables or tables with frequent schema evolution, such as adding, deleting, or modifying columns.	Automatically detects source table changes and supports on-demand or full data loading.

Accelerate queries using `CREATE FOREIGN TABLE`

You can use the CREATE FOREIGN TABLE statement 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 query data from non-partitioned and partitioned tables in MaxCompute.

You can also use the HoloWeb console to create a foreign table visually. 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.

Click to view the DDL statement of the 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);

Run the following command to view the data in the source table.

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

The following figure shows a sample of the returned data:

Create a foreign table in Hologres to map the table in MaxCompute. 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. You can use the odps_server server that is created at the underlying layer of Hologres. For more information, see postgres_fdw.
project_name	If your MaxCompute project uses the Layer 3 model, set project_name to the MaxCompute project name and schema name in the `odps_project_name#odps_schema_name` format. If the MaxCompute project uses the two-layer model, set the project_name parameter to the MaxCompute project name. For more information about the three-layer model, see Schema-related operations.
table_name	The name of the MaxCompute table that you want to query.

After you create a foreign table, you can directly query data from the MaxCompute table using the foreign table. Sample statement:
```
SELECT * FROM customer LIMIT 10;
```
Important
If a query error occurs, ensure that the query account has the Select permission on the MaxCompute table. For more information, see Permissions.

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.

Click to view the DDL statement of the 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');

Run the following command to view the data in the source 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';

The following figure shows a sample of the returned data:

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.
- To query the first 10 data records, execute the following statement:
```
SELECT * FROM foreign_ods_enterprise_share_trade_h limit 10;
```
- To query data from a specific partition, execute the following statement:
```
SELECT * FROM foreign_ods_enterprise_share_trade_h 
WHERE ds = '20170113';
```
Important
If a query error occurs, ensure that the query account has the Select permission on the MaxCompute table. For more information, see Permissions.

Accelerate queries using `IMPORT FOREIGN SCHEMA`

You can use the IMPORT FOREIGN SCHEMA statement to create multiple MaxCompute foreign tables in a batch operation. For more information, see IMPORT FOREIGN SCHEMA.

Accelerate queries using `Auto Load`

If the number of foreign tables to be created is large or schemas of MaxCompute tables are frequently changed, you can enable the Auto Load feature to automatically create foreign tables for queried MaxCompute tables as needed or for all MaxCompute tables. Schema changes on MaxCompute tables include operations such as deleting columns, changing the order of columns, and changing the data types of columns. You do not need to manually change the schemas of foreign tables. This improves the query efficient. For more information, see Auto Load.