Set a loading scope for an external database
If an external database maps to a data source with many schemas and tables, refreshing all metadata at once can cause performance issues. Hologres V4.1.5 and later lets you define an allowlist (include_pattern) and a denylist (exclude_pattern) to control exactly which schemas and tables are refreshed, improving metadata refresh efficiency.
Prerequisites
-
Your Hologres instance is V4.1.5 or later. To view your instance version, see Instance Configuration Overview.
-
You have created an External Database. For more information, see CREATE EXTERNAL DATABASE.
-
You must have superuser permissions to execute the commands.
Features
After you set a loading scope, the REFRESH CACHE FOR EXTERNAL DATABASE command loads only metadata for schemas and tables that match the scope rules.
The loading scope only affects indirect metadata loading, such as REFRESH CACHE FOR EXTERNAL DATABASE. Direct queries that specify a target table, such as SELECT * FROM ext_db.ext_schema.ext_table, are not restricted by the loading scope.
Parameters
|
Parameter |
Description |
Default |
|
|
An allowlist rule. Specify a JSON-formatted string that defines which schemas and tables to load. If not set, Hologres loads all schemas and tables. |
None (no restrictions) |
|
|
A denylist rule. Specify a JSON-formatted string that defines which schemas and tables to exclude from loading. If not set, no schemas or tables are excluded. |
None (no restrictions) |
|
|
The maximum number of tables to load from a single external schema during metadata refresh. A value of 0 means no limit. |
2000 |
-
The default value of 2000 for
table_count_limitation_per_schemaapplies only to new External Databases created in V4.1.5 or later. For existing External Databases created in earlier versions, this value is treated as 0 (unlimited). -
When the number of tables in a single schema exceeds the value set for
table_count_limitation_per_schema, the system sorts the tables by name, selects the first N tables, and refreshes their metadata.
Pattern matching syntax
Both include_pattern and exclude_pattern accept JSON-formatted strings with the following structure:
{"schema_pattern": ["table_pattern_1", "table_pattern_2"], "schema_pattern_2": ["table_pattern_3"]}
-
Key: The schema pattern (
schema_pattern), which matches schema names. -
Value: A list of table patterns (
table_pattern_list), which matches table names within the schema.
Patterns use fnmatch-style wildcards, similar to shell wildcards. The following wildcards are supported:
|
Wildcard |
Description |
Example |
|
|
Matches any number of any characters. |
|
|
|
Matches any single character. |
|
|
|
Matches any single character in |
|
|
|
Matches any single character not in |
|
Matching rules
include_pattern (allowlist) rules:
-
If
include_patternis not set, all schemas and tables are considered to pass the allowlist check. -
After you set
include_pattern, a table is included if it matches any pattern in the table pattern list.
exclude_pattern (denylist) rule:
-
If
exclude_patternis not set, all schemas and tables are considered to pass the denylist check. -
After
exclude_patternis set, a table passes only if it does not match any pattern in the table pattern list.
Final result: A schema or table is included in the loading scope only if it passes both the include_pattern and exclude_pattern checks.
If a schema name matches multiple keys (schema_pattern), the behavior is undefined. Make sure each schema name matches only one key when defining rules.
Syntax
Set a loading scope during creation
CREATE EXTERNAL DATABASE <ext_database_name> WITH
metastore_type '<type>'
-- Other required parameters...
include_pattern '<pattern_json>'
exclude_pattern '<pattern_json>'
table_count_limitation_per_schema '<number>';
Modify the loading scope of an existing database
-- Set or update the loading scope
ALTER EXTERNAL DATABASE <ext_database_name> WITH
include_pattern '<pattern_json>'
exclude_pattern '<pattern_json>';
-- Set the table count limit for a single schema
ALTER EXTERNAL DATABASE <ext_database_name> WITH
table_count_limitation_per_schema '<number>';
-- Remove the table count limit for a single schema
ALTER EXTERNAL DATABASE <ext_database_name> WITH
table_count_limitation_per_schema default;
-- Equivalent syntax
ALTER EXTERNAL DATABASE <ext_database_name> WITH
table_count_limitation_per_schema '0';
Check for scope inclusion
-- Check whether a schema is within the loading scope
SELECT hologres.hg_external_database_includes('<database_name>', '<schema_name>');
-- Check whether a table is within the loading scope
SELECT hologres.hg_external_database_includes('<database_name>', '<schema_name>', '<table_name>');
Examples
Example 1: Load items by prefix
CREATE EXTERNAL DATABASE ext_mc_db WITH
metastore_type 'maxcompute'
mc_project 'my_mc_project'
include_pattern '{"prod_*": ["fact_*", "dim_*"]}';
ALTER EXTERNAL DATABASE ext_mc_db WITH
include_pattern '{"prod_*": ["fact_*", "dim_*"]}';
REFRESH CACHE FOR EXTERNAL DATABASE ext_mc_db WITH(cache_level = 'metadata');
Example 2: Exclude specific schemas
ALTER EXTERNAL DATABASE ext_mc_db WITH
exclude_pattern '{"tmp": ["*"], "test_*": ["*"]}';
Example 3: Use allowlist and denylist
ALTER EXTERNAL DATABASE ext_mc_db WITH
include_pattern '{"dev_*": ["2025*"]}'
exclude_pattern '{"dev_*": ["2025_tmp*"]}';
Example 4: Limit tables per schema
CREATE EXTERNAL DATABASE ext_mc_db WITH
metastore_type 'maxcompute'
mc_project 'my_mc_project'
table_count_limitation_per_schema '1000';
ALTER EXTERNAL DATABASE ext_mc_db WITH
table_count_limitation_per_schema '1000';
ALTER EXTERNAL DATABASE ext_mc_db WITH
table_count_limitation_per_schema '0';
Example 5: Verify the loading scope
SELECT hologres.hg_external_database_includes('ext_mc_db', 'prod_order');
-- Returns: true
SELECT hologres.hg_external_database_includes('ext_mc_db', 'prod_order', 'fact_sales');
-- Returns: true
SELECT hologres.hg_external_database_includes('ext_mc_db', 'test_dev');
-- Returns: false
Best practices
-
Load only frequently accessed schemas and tables based on your business needs to avoid unnecessary metadata refresh overhead.
-
Start with an allowlist, then refine with a denylist: Use
include_patternto define a broad scope first, then useexclude_patternto exclude specific cases. -
Set a reasonable table limit: For schemas with a very large number of tables (such as tens of thousands), set
table_count_limitation_per_schemato a reasonable value to prevent slow metadata refreshes. -
Verify with the detection function: After setting a pattern, use
hologres.hg_external_database_includesto confirm that target schemas and tables fall within the loading scope. -
Avoid schema pattern conflicts: Make sure the schema patterns in
include_patternandexclude_patterndo not match multiple keys for the same schema name. Otherwise, the behavior is undefined.
FAQ
Q: After setting a loading scope, can I still query tables that are not in the scope?
A: Yes. The loading scope only affects indirect metadata loading via REFRESH CACHE FOR EXTERNAL DATABASE. Direct queries using the full path ext_db.ext_schema.ext_table are not restricted.
Q: Do I need to manually refresh the metadata after I change the loading scope?
A: The new rules take effect after the next metadata refresh. Run REFRESH CACHE FOR EXTERNAL DATABASE to apply the changes immediately.
Q: How does table_count_limitation_per_schema affect an existing External Database?
A: For existing external databases created before V4.1.5, this value defaults to 0 (unlimited). To set a limit, explicitly configure it by using the ALTER EXTERNAL DATABASE command.