Accelerate queries on MaxCompute data by using foreign tables - Hologres

Hologres lets you create foreign tables to accelerate queries on MaxCompute data. You can use this method to directly access and analyze data stored in MaxCompute from your Hologres environment. This improves query efficiency and simplifies your data processing workflow.

Permissions

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

Data type mapping

A one-to-one mapping exists between MaxCompute and Hologres data types. When you create a table, see Data type mapping between MaxCompute and Hologres.

Method comparison and selection

Method	Scenarios	Technical features
Accelerate queries using CREATE FOREIGN TABLE	Accelerate a small number of tables, query specific columns, and work with stable table schemas.	Manually create tables with flexible definitions for columns and comments.
Accelerate queries using IMPORT FOREIGN SCHEMA	Map all tables in batches at the schema or database level.	Automatically synchronizes the table schemas for an entire schema.
Accelerate queries using Auto Load	Manage many tables or tables with frequent schema changes, such as adding, deleting, or modifying columns.	Automatically detects changes in source tables. Supports on-demand and full data loading.

Accelerate queries using `CREATE FOREIGN TABLE`

You can use the CREATE FOREIGN TABLE statement to flexibly create MaxCompute foreign tables. You can customize table names, select specific columns, and define comments. This section describes how to query non-partitioned and partitioned MaxCompute tables using the CREATE FOREIGN TABLE statement.

You can also create foreign tables in 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. This example uses the customer table from the BIGDATA_PUBLIC_DATASET.tpcds_10t public dataset in MaxCompute.

Click to view the DDL statement for the table.

-- DDL statement for 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 sample data in the table.

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

The following is a sample result:

Create a foreign table in Hologres to map the MaxCompute data. The following is a sample statement.

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.

Parameter	Description
SERVER	The foreign table server. You can directly call the foreign table server named odps_server that is created at the underlying layer of Hologres. For more information about the principles, see Postgres FDW.
project_name	If your MaxCompute project uses the three-layer model: project_name is the MaxCompute project name and schema name, in the format `odps_project_name.odps_schema_name`. If your MaxCompute project uses the two-layer model: project_name is the MaxCompute project name. For more information about the three-layer model, see Schema operations.
table_name	The name of the MaxCompute table to query.

After the foreign table is created, you can query the foreign table in Hologres to access the data in the MaxCompute table. The following is a sample statement.
```
SELECT * FROM customer LIMIT 10;
```
Important
If a query error occurs, ensure that the account that you use to run the query has the required permissions, such as the Select permission, on the MaxCompute table. For more information, see Permissions.

Example 2: Query data from a MaxCompute partitioned table

Prepare a partitioned table in MaxCompute and import data. This example uses the ods_enterprise_share_trade_h table from the BIGDATA_PUBLIC_DATASET.finance public dataset in MaxCompute.

Click to view the DDL statement for the table.

-- DDL for 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 sample 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';

The following is a sample result:

Create a foreign table in Hologres to map the MaxCompute data. The following is a 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 'Area';
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 shares';
comment on column public.foreign_ods_enterprise_share_trade_h."totals" is 'Total shares (10,000)';
comment on column public.foreign_ods_enterprise_share_trade_h."totalassets" is 'Total assets (10,000)';
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 'Reserve fund';
comment on column public.foreign_ods_enterprise_share_trade_h."reservedpershare" is 'Reserve 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 margin (%)';
comment on column public.foreign_ods_enterprise_share_trade_h."holders_num" is 'Number of shareholders';

Query data from the MaxCompute partitioned table in Hologres.
- Query the first 10 data records. The SQL statement is as follows:
```
SELECT * FROM foreign_ods_enterprise_share_trade_h limit 10;
```
- Query data from a partition. The following is a sample SQL statement:
```
SELECT * FROM foreign_ods_enterprise_share_trade_h 
WHERE ds = '20170113';
```
Important
If a query error occurs, ensure that the account that you use to run the query has the required permissions, such as the Select permission, on the MaxCompute table. For more information, see Permissions.

Accelerate queries using `IMPORT FOREIGN SCHEMA`

To create multiple MaxCompute foreign tables in batches, you can use the IMPORT FOREIGN SCHEMA statement. For more information, see IMPORT FOREIGN SCHEMA.

Accelerate queries using `Auto Load`

To accelerate queries on many foreign tables or if the schemas of the foreign tables change frequently, you can use the Auto Load feature. For example, columns may be deleted, the column order may be changed, or column data types may be changed in MaxCompute. The Auto Load feature lets you automatically load MaxCompute data on demand or in full. You do not need to manually modify the schemas of foreign tables, which improves query efficiency. For more information, see Auto Load for foreign tables.