Query MaxCompute Data via Hologres Foreign Tables - Hologres

Prerequisites

Before you begin, make sure you have:

A Hologres instance with access to a MaxCompute project
The permissions to access MaxCompute projects and tables. This includes the Select permission on the MaxCompute tables you want to query. For details, see Manage user permissions using commands

Data type mapping

MaxCompute and Hologres data types map one-to-one. Before creating a foreign table, see Data type mapping between MaxCompute and Hologres.

Choose a method

Select the method based on how many tables you have and how often their schemas change:

Method	Use when	How it works
CREATE FOREIGN TABLE	You have a small number of tables, need specific columns only, or want to customize column names and comments	Manually define each foreign table — full control over columns, names, and comments
IMPORT FOREIGN SCHEMA	You want to map all tables in a schema or DB at once	Automatically synchronizes the entire schema's table structure
Auto Load	You have many tables, or MaxCompute schemas change frequently (columns added, removed, reordered, or data types changed)	Detects source schema changes and creates or updates foreign tables on demand or in bulk

CREATE FOREIGN TABLE

Use CREATE FOREIGN TABLE when you need to select specific columns, rename tables, or add custom comments.

To create foreign tables without SQL, use HoloWeb instead. For details, see Create a MaxCompute foreign table using HoloWeb.

How it works

Identify the MaxCompute table you want to query.
Run CREATE FOREIGN TABLE in Hologres, specifying SERVER odps_server and an OPTIONS block with project_name and table_name.
Query the foreign table directly — Hologres reads data from MaxCompute at query time.

Example 1: Query a non-partitioned table

This example maps the customer table from the MaxCompute public dataset BIGDATA_PUBLIC_DATASET.tpcds_10t.

Step 1: Verify the source data in MaxCompute.

Run the following statement in MaxCompute to confirm the data exists:

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

The following sample data is returned:

Click to view the DDL statement for this table.

-- DDL 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);

Step 2: Create a foreign table in Hologres.

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 key parameters in the OPTIONS block:

Parameter	Description
`SERVER`	The foreign data server. Use the built-in `odps_server`, which Hologres pre-creates based on Postgres FDW.
`project_name`	The MaxCompute project identifier. For a three-layer model project, use the format `odps_project_name.odps_schema_name`. For a two-layer model project, use the project name only. For details, see Schema operations.
`table_name`	The name of the MaxCompute table to query.

Step 3: Query the foreign table.

SELECT * FROM customer LIMIT 10;

Important

If the query fails with a permission error, make sure the account has the Select permission and other required permissions on the MaxCompute table. For details, see Prerequisites.

Example 2: Query a partitioned table

This example maps the ods_enterprise_share_trade_h table from the MaxCompute public dataset BIGDATA_PUBLIC_DATASET.finance. The table is partitioned by the date string column ds.

For three-layer model MaxCompute projects, the project_name value in CREATE FOREIGN TABLE uses a # separator between the project name and schema name (for example, BIGDATA_PUBLIC_DATASET#finance). This differs from the . separator used in standard MaxCompute queries.

Step 1: Verify the source data in MaxCompute.

-- Query data in 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 sample data is returned:

Click to view the DDL statement for this 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 (in 10,000s)',
  totalassets STRING COMMENT 'Total assets (in 10,000s)',
  liquidassets STRING COMMENT 'Liquid assets',
  fixedassets STRING COMMENT 'Fixed assets',
  reserved STRING COMMENT 'Reserve fund',
  reservedpershare STRING COMMENT 'Reserve per share',
  eps STRING COMMENT 'Earnings per share',
  bvps STRING COMMENT 'Book value per share',
  pb STRING COMMENT 'PB ratio',
  timetomarket STRING COMMENT 'Time to market',
  undp STRING COMMENT 'Undistributed profit',
  perundp STRING COMMENT 'Undistributed per share',
  rev STRING COMMENT 'Revenue YoY (%)',
  profit STRING COMMENT 'Profit YoY (%)',
  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');

Step 2: Create a foreign table in Hologres.

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 'Historical stock trading information';
comment on column public.foreign_ods_enterprise_share_trade_h."code" is 'Code';
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 'PE 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 (in 10,000s)';
comment on column public.foreign_ods_enterprise_share_trade_h."totalassets" is 'Total assets (in 10,000s)';
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 'PB 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 profit';
comment on column public.foreign_ods_enterprise_share_trade_h."perundp" is 'Undistributed per share';
comment on column public.foreign_ods_enterprise_share_trade_h."rev" is 'Revenue YoY (%)';
comment on column public.foreign_ods_enterprise_share_trade_h."profit" is 'Profit YoY (%)';
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';

Step 3: Query the foreign table.

To return the first 10 rows:

SELECT * FROM foreign_ods_enterprise_share_trade_h LIMIT 10;

To filter by partition:

SELECT * FROM foreign_ods_enterprise_share_trade_h
WHERE ds = '20170113';

Important

If the query fails with a permission error, make sure the account has the Select permission and other required permissions on the MaxCompute table. For details, see Prerequisites.

IMPORT FOREIGN SCHEMA

Use IMPORT FOREIGN SCHEMA to map all tables in a MaxCompute schema to Hologres foreign tables in a single statement. This is more efficient than running CREATE FOREIGN TABLE for each table individually.

For syntax and usage, see IMPORT FOREIGN SCHEMA.

Auto Load

Use Auto Load when you have a large number of tables or when MaxCompute table schemas change frequently — for example, when columns are added, removed, reordered, or their data types are changed. Auto Load creates and updates foreign tables automatically, either for tables you query on demand or for all tables in a MaxCompute project. No manual schema updates on the Hologres side are required.

For setup and usage, see Auto Load for foreign tables.

Hologres:MaxCompute foreign tables