Hologres:Automatic loading of foreign tables (Auto Load)

Enable automatic loading

• Syntax

  -- Enable automatic loading
  ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = on;

• Parameter description

  database name: The name of the Hologres database.

(Optional) Set the automatic loading mode

In Hologres V2.2.1 and later, you can set the automatic loading mode based on your business needs. The automatic loading mode must be enabled at the database level. If you do not set a mode, the on-demand loading mode is used by default. You do not need to perform this step for Hologres versions earlier than V2.2.1.

• Syntax

  ALTER DATABASE <database name> SET hg_experimental_load_foreign_table_mode = ['query' | 'period']

• The parameter values are described as follows:

  • query (default): On-demand loading of foreign tables.

    -- Set the loading mode to on-demand loading
    ALTER DATABASE <database name> SET hg_experimental_load_foreign_table_mode = 'query';

  • period: Full loading of foreign tables.

    -- Set the loading mode to full loading
    ALTER DATABASE <database name> SET hg_experimental_load_foreign_table_mode = 'period';

Configure mappings

• Syntax

  • Configure MaxCompute mappings

    • For Hologres V2.2.1 and later

      -- For a MaxCompute three-layer model project
      ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<odps_foreign_server>.<mc_project>.<mc_schema>, [...]';
      -- For a MaxCompute two-layer model project
      ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<odps_foreign_server>.<mc_project>, [...]';

    • For Hologres versions earlier than V2.2.1

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

  • Configure DLF mappings

    -- DLF Default Catalog
    ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<dlf_foreign_server>.<dlf_database> [...]';
    -- Custom Catalog
    ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<dlf_foreign_server>.<dlf_catalog>.<dlf_database>, [...]';

• Parameters

  Parameter name	Description
  database	The name of the Hologres database.
  hologres_schema	The name of the Hologres schema.
  mc_project	The name of the MaxCompute project to automatically load. Log on to the MaxCompute console and go to the Workspace > Project Management page to obtain the MaxCompute project name.
  odps_foreign_server	The server used to access the MaxCompute project. This is fixed to odps_server.
  odps_project_name	The name of the MaxCompute project. You can specify multiple projects. Separate multiple project names with commas (,). The default value is empty, which means that the metadata of tables in any MaxCompute project is not loaded periodically. Note This parameter is required only for Hologres versions earlier than V2.2.1.
  mc_schema	The name of the MaxCompute schema. Note This parameter is required only for projects where the three-layer model is enabled. For more information about schemas, see Schema operations.
  dlf_foreign_server	The foreign server created based on DLF_FDW. You can run the following command to get the foreign server name. For more information, see Accelerate queries on data in an OSS-based data lake using DLF. SELECT * FROM pg_foreign_server;
  dlf_catalog	The name of the data catalog where the DLF database to be automatically loaded resides. Log on to the Data Lake Formation console, choose Metadata > Metadata Management, and then obtain the data catalog name from the Data Catalog tab.
  dlf_database	The name of the DLF database to be automatically loaded. Log on to the Data Lake Formation console, choose Metadata > Metadata Management, and then obtain the database name from the Database tab.

Query data

You can directly query the data in the corresponding MaxCompute or DLF tables.

-- Query data in a MaxCompute project
SELECT * FROM <hologres_schema>.<mc_table>;
-- Query DLF data
SELECT * FROM <hologres_schema>.<dlf_table>;

Disable automatic loading

• Syntax

  ---- Disable automatic loading
  ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = off;

  Note

  After you disable automatic loading, the system stops synchronizing metadata from external data sources and creating new foreign tables. You can view only the foreign tables and data that are already loaded in Hologres. To add more foreign tables, you must manually run the IMPORT command. For more information, see IMPORT FOREIGN SCHEMA.

• Parameter description

  database name: The name of the Hologres database.

View data sources with automatic loading enabled

-- Before V2.2, view the configured MaxCompute data sources
SHOW hg_experimental_default_odps_project_list;

-- In V2.2 and later, view the configured MaxCompute and DLF data sources
SHOW hg_experimental_auto_load_foreign_schema_mapping;

Set the inspection period

After you configure a data source for full loading, you can use the following time parameter to set up periodic checks. If new tables are added to the data source, the system automatically loads them as Hologres foreign tables when a query is run after the specified check period. The default interval is 30 minutes. This means that if a new table is added, it is automatically loaded as a foreign table when it is queried 30 minutes later.

• Syntax

  -- Set the inspection period for the specified Hologres database to 600 s
  ALTER DATABASE <database name> SET hg_experimental_load_all_foreign_table_interval_time = 600;

• Parameter description

  database name: The name of the Hologres database.

Delete a loaded foreign table

If you no longer need to accelerate queries on a foreign table, you can run the DROP statement to delete it. For more information, see DROP FOREIGN TABLE.

Example 1: On-demand loading of foreign tables

• Two-layer model

  1. Enable automatic loading.

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  2. Configure the mapping.

     --- For V2.2.1 and later
     ALTER DATABASE holo_demo SET hg_experimental_auto_load_foreign_schema_mapping = 'hologres_schema:odps_server.mc_project';
     --- For versions earlier than V2.2.1
     ALTER DATABASE holo_demo SET hg_experimental_default_odps_project_list='mc_project';

  3. Query the foreign table.

     SELECT * FROM hologres_schema.mc_table;

• Three-layer model

  1. Enable automatic loading.

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  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';

  3. Query the foreign table.

     SELECT * FROM holo_schema_3layer.mc_table;

Example 2: Full automatic loading of foreign tables

This example uses a DLF data source. You can automatically load DLF foreign tables by binding to the DLF Default Catalog or a custom catalog.

• DLF Default Catalog

  Set up automatic loading for the dlf_db database in the DLF Default Catalog. After the setup is complete, all tables in this database are automatically created as Hologres foreign tables. The system also periodically loads new tables from this database based on the check period configured by hg_experimental_load_all_foreign_table_interval_time.

  1. Create the foreign service dlf_server and configure its Endpoint information. For more information, see Accelerate queries on data in an OSS-based data lake using DLF.

     -- Create a 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'
     );

  2. Create a user mapping.

     Auto Load requires you to run the CREATE USER MAPPING command to specify the AccessKey ID and AccessKey secret for accessing DLF and OSS. For more information, see Accelerate queries on data in an OSS-based data lake using DLF.

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

  3. Enable automatic loading.

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  4. Set the mapping and 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 = 10;

  5. Query the foreign table data.

     SELECT * FROM holo_schema.dlf_table;

• DLF custom catalog

  1. Create a foreign server and configure its Endpoint information. For more information, see Accelerate queries on data in an OSS-based data lake using DLF.

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

  2. Create a user mapping.

     Auto Load requires you to run the CREATE USER MAPPING command to specify the AccessKey ID and AccessKey secret for accessing DLF and OSS. For more information, see Accelerate queries on data in an OSS-based data lake using DLF.

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

  3. Enable automatic loading.

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  4. Configure the mapping, loading mode, and check period.

     -- Configure the mapping
     SET hg_experimental_auto_load_foreign_schema_mapping = 'holo_schema:DLF_server.dlf_catalog.dlf_db';
     -- Set the loading mode to full loading of foreign tables
     SET hg_experimental_load_foreign_table_mode = 'period'; 
     -- Set the inspection period to 600 s
     SET hg_experimental_load_all_foreign_table_interval_time = 600;

  5. Query the foreign table data.

     SELECT * FROM holo_schema.dlf_table;