Set a loading scope for an external database

Updated at:

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.

Important

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

include_pattern

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)

exclude_pattern

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)

table_count_limitation_per_schema

The maximum number of tables to load from a single external schema during metadata refresh. A value of 0 means no limit.

2000

Note
  • The default value of 2000 for table_count_limitation_per_schema applies 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.

test_* matches test_abc and test_123

?

Matches any single character.

test_? matches test_a and test_1

[seq]

Matches any single character in seq.

test_[abc] matches test_a, test_b, and test_c.

[!seq]

Matches any single character not in seq.

test_[!abc] matches test_d and test_1

Matching rules

include_pattern (allowlist) rules:

  • If include_pattern is 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_pattern is not set, all schemas and tables are considered to pass the denylist check.

  • After exclude_pattern is 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.

Note

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_pattern to define a broad scope first, then use exclude_pattern to 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_schema to a reasonable value to prevent slow metadata refreshes.

  • Verify with the detection function: After setting a pattern, use hologres.hg_external_database_includes to confirm that target schemas and tables fall within the loading scope.

  • Avoid schema pattern conflicts: Make sure the schema patterns in include_pattern and exclude_pattern do 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.