Automatic loading of foreign tables (Auto Load)

Prerequisites

Before you begin, make sure you have:

• A Hologres instance running V1.1.43 or later

• db_admin permissions on the Hologres database

• (For DLF/OSS sources) A foreign server created with DLF_FDW and a user mapping configured — see Accelerate queries on data in an OSS-based data lake using DLF

Step 1: Enable automatic loading

ALTER DATABASE <database_name> SET hg_experimental_enable_auto_load_foreign_table = on;

Replace <database_name> with the name of your Hologres database.

Step 2: Set the loading mode

This step applies to Hologres V2.2.1 and later. Skip it for earlier versions — on-demand loading (query) is used by default.

The mode must be set at the database level.

-- On-demand loading (default)
ALTER DATABASE <database_name> SET hg_experimental_load_foreign_table_mode = 'query';

-- Full loading
ALTER DATABASE <database_name> SET hg_experimental_load_foreign_table_mode = 'period';

Step 3: Configure schema mappings

Map each Hologres schema to the external data source it should mirror.

MaxCompute mappings (V2.2.1+)

-- Three-layer model project
ALTER DATABASE <database_name> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<odps_foreign_server>.<mc_project>.<mc_schema>, [...]';

-- Two-layer model project
ALTER DATABASE <database_name> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<odps_foreign_server>.<mc_project>, [...]';

MaxCompute mappings (earlier than V2.2.1)

ALTER DATABASE <database_name> SET hg_experimental_default_odps_project_list='<odps_project_name_1>,<odps_project_name_2>...';

DLF mappings (V2.2.1+)

-- Default catalog
ALTER DATABASE <database_name> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<dlf_foreign_server>.<dlf_database> [...]';

-- Custom catalog
ALTER DATABASE <database_name> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<dlf_foreign_server>.<dlf_catalog>.<dlf_database>, [...]';

Parameters

Step 4: Query data and verify the setup

Run a query against the mapped schema. Auto Load creates the foreign table on first access (on-demand mode) or confirms that tables are already loaded (full mode).

-- Query a MaxCompute table
SELECT * FROM <hologres_schema>.<mc_table>;

-- Query a DLF table
SELECT * FROM <hologres_schema>.<dlf_table>;

To confirm which data sources are configured, run:

-- V2.2.1 and later
SHOW hg_experimental_auto_load_foreign_schema_mapping;

-- Earlier than V2.2.1
SHOW hg_experimental_default_odps_project_list;

Disable automatic loading

ALTER DATABASE <database_name> SET hg_experimental_enable_auto_load_foreign_table = off;

After disabling, Hologres stops syncing metadata from external data sources and creating new foreign tables. Existing foreign tables remain queryable. To add more foreign tables manually, use IMPORT FOREIGN SCHEMA.

Set the check interval for full loading

For full loading mode, configure how often Hologres checks for new tables in the data source. When a new table is detected, it is auto-loaded as a foreign table on the next query after the interval elapses.

-- Set the check interval to 600 seconds (10 minutes)
ALTER DATABASE <database_name> SET hg_experimental_load_all_foreign_table_interval_time = 600;

Default: 1800 seconds (30 minutes). Minimum: 600 seconds (10 minutes).

Enable schema evolution for MaxCompute foreign tables

When the schema of a MaxCompute data source changes — columns added, deleted, or renamed — enable this Grand Unified Configuration (GUC) parameter at the session level to update the foreign table metadata automatically at query time.

SET hg_experimental_enable_auto_load_check_schema_evolution = on;

Delete a foreign table

To stop accelerating queries on a foreign table, drop it with the DROP statement. See DROP FOREIGN TABLE.

Example 1: On-demand loading from a MaxCompute project

This example shows on-demand loading for both two-layer and three-layer MaxCompute model projects.

Two-layer model

-- Step 1: Enable automatic loading
ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

-- Step 2: Configure the mapping
-- V2.2.1 and later
ALTER DATABASE holo_demo SET hg_experimental_auto_load_foreign_schema_mapping = 'hologres_schema:odps_server.mc_project';
-- Earlier than V2.2.1
ALTER DATABASE holo_demo SET hg_experimental_default_odps_project_list='mc_project';

-- Step 3: Query the table (the foreign table is created on first query)
SELECT * FROM hologres_schema.mc_table;

Three-layer model

-- Step 1: Enable automatic loading
ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

-- Step 2: Configure the mapping
ALTER DATABASE holo_demo SET hg_experimental_auto_load_foreign_schema_mapping = 'holo_schema_3layer:odps_server.mc_3_layer_project.mc_schema';

-- Step 3: Query the table
SELECT * FROM holo_schema_3layer.mc_table;

Example 2: Full loading from a DLF data source

This example shows full loading for a DLF database — both from the default catalog and a custom catalog.

DLF default catalog

All tables in dlf_db are loaded as Hologres foreign tables. The system checks for new tables periodically based on the configured interval.

-- Step 1: Create the DLF foreign server
CREATE SERVER IF NOT EXISTS dlf_server FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    dlf_endpoint 'dlf-share.cn-beijing.aliyuncs.com',
    oss_endpoint 'oss-cn-beijing-internal.aliyuncs.com'
);

-- Step 2: Create a user mapping with credentials for DLF and OSS
CREATE USER MAPPING FOR <account_uid> SERVER <server_name> OPTIONS (
    dlf_access_id 'accessid',
    dlf_access_key 'accesskey',
    oss_access_id 'accessid',
    oss_access_key 'accesskey'
);

-- Step 3: Enable automatic loading
ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

-- Step 4: Configure the mapping and set full loading mode
SET hg_experimental_auto_load_foreign_schema_mapping = 'holo_schema:dlf_server.dlf_db';
SET hg_experimental_load_foreign_table_mode = 'period';
SET hg_experimental_load_all_foreign_table_interval_time = 600;

-- Step 5: Query a table
SELECT * FROM holo_schema.dlf_table;

DLF custom catalog

-- Step 1: Create the DLF foreign server
CREATE SERVER IF NOT EXISTS DLF_server FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    dlf_endpoint 'dlf-share.cn-beijing.aliyuncs.com',
    oss_endpoint 'oss-cn-beijing-internal.aliyuncs.com'
);

-- Step 2: Create a user mapping
CREATE USER MAPPING FOR <account_uid> SERVER <server_name> OPTIONS (
    dlf_access_id 'accessid',
    dlf_access_key 'accesskey',
    oss_access_id 'accessid',
    oss_access_key 'accesskey'
);

-- Step 3: Enable automatic loading
ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

-- Step 4: Configure the mapping, loading mode, and check interval
SET hg_experimental_auto_load_foreign_schema_mapping = 'holo_schema:DLF_server.dlf_catalog.dlf_db';
SET hg_experimental_load_foreign_table_mode = 'period';
SET hg_experimental_load_all_foreign_table_interval_time = 600;

-- Step 5: Query a table
SELECT * FROM holo_schema.dlf_table;