Hologres:CREATE EXTERNAL DATABASE

Instance configuration

Enable data lake acceleration for the instance before running CREATE EXTERNAL DATABASE:

1. Open the Hologres console and go to the Instance List or Instance Details page.

2. Find the target instance and click Data Lake Acceleration in the Actions column.

3. Click Confirm in the dialog box.

Service activation

• DLF: Activate the DLF service. See Quick start and Available regions and endpoints.

• DLF V1.0: Also activate Object Storage Service (OSS) and complete OSS authorization. Without the required OSS permissions, queries fail even if the foreign table is created successfully. See Bucket policy.

• OSS-HDFS (optional): Activate the OSS-HDFS service if you plan to use OSS-HDFS storage. See Activate the OSS-HDFS service.

Permissions

• The account running CREATE EXTERNAL DATABASE must have Superuser permissions in Hologres.

• Permissions on the external data (tables, schemas) are controlled by MaxCompute or DLF, not Hologres. Grant the service-linked role (SLR) AliyunServiceRoleForHologresIdentityMgmt the required permissions before accessing external data. See Hologres service-linked role.

• To use a RAM role instead of the SLR, see RAM role overview.

• For non-Alibaba Cloud accounts and RAM users (for example, Hologres BASIC accounts), use CREATE USER MAPPING to bind and authorize the account. See CREATE USER MAPPING.

MaxCompute data source

CREATE EXTERNAL DATABASE <ext_database_name> WITH
  metastore_type 'maxcompute'
  mc_project '<project_name>'
  [comment '<description>'];

Parameters

DLF data source

DLF currently supports only the managed storage mode and the Paimon catalog type. Set metastore_type to dlf-rest and catalog_type to paimon. Authentication defaults to the SLR (AliyunServiceRoleForHologresIdentityMgmt). To use a RAM role instead, configure it via CREATE USER MAPPING.

CREATE EXTERNAL DATABASE <ext_database_name> WITH
  catalog_type 'paimon'
  metastore_type 'dlf-rest'
  dlf_catalog '<dlf_catalog_name>'
  [comment '<description>'];

Parameters

DLF V1.0 data source

DLF V1.0 supports custom OSS storage and additional formats: Paimon, Apache Hudi, Delta Lake, ORC, Parquet, CSV, and SequenceFile. See Data lake acceleration. DLF V1.0 does not support SLR or RAM role authentication. Use CREATE USER MAPPING to provide credentials per user.

CREATE EXTERNAL DATABASE <ext_database_name> WITH
  metastore_type 'dlf'
  dlf_region '<region>'
  dlf_endpoint '<dlf_endpoint>'
  dlf_catalog '<catalog_name>'
  oss_endpoint '<oss_endpoint>'
  [comment '<description>'];

Parameters

MaxCompute data source

When you map a MaxCompute project that uses a two-layer model, Hologres displays it as a three-layer structure: ext_db.schema.mc_table. The schema name defaults to default. For example: SELECT * FROM ext_db.default.mc_table.

-- Create an external database for a MaxCompute project.
CREATE EXTERNAL DATABASE ext_database_mc WITH
  metastore_type 'maxcompute'
  mc_project 'mc_3_layer_project'
  comment 'mc three layer project';

-- Query a table in the external database.
SELECT * FROM ext_database_mc.mc_schema.mc_table;

DLF data source

-- Create an external database for a DLF Paimon catalog.
CREATE EXTERNAL DATABASE ext_database_dlf WITH
  metastore_type 'dlf-rest'
  catalog_type 'paimon'
  dlf_catalog 'dlf_paimon_catalog'
  comment 'DLF paimon catalog';

-- Query a table in the external database.
SELECT * FROM ext_database_dlf.dlf_db.paimon_table;

DLF V1.0 data source

DLF V1.0 requires an AccessKey pair for both DLF and OSS. Provide credentials using CREATE USER MAPPING rather than embedding them in the CREATE EXTERNAL DATABASE statement.

-- Create an external database.
CREATE EXTERNAL DATABASE ext_database_dlf1 WITH
  metastore_type 'dlf'
  dlf_region 'cn-beijing'
  dlf_endpoint 'dlf-share.cn-beijing.aliyuncs.com'
  dlf_catalog 'hive_catalog'
  oss_endpoint 'oss-cn-beijing-internal.aliyuncs.com'
  comment 'DLF1.0 hive catalog';

-- Bind credentials for the current user.
CREATE USER MAPPING FOR current_user
EXTERNAL DATABASE ext_database_dlf1
OPTIONS
  (
      dlf_access_id 'LTxxxxxxxxxx',
      dlf_access_key 'y8xxxxxxxxxxxxx',
      oss_access_id 'LTxxxxxxxxxx',
      oss_access_key 'y8xxxxxxxxxxxxx'
  );

-- Query a table in the external database.
SELECT * FROM ext_database_dlf1.dlf_db.hive_table;

View all external databases

SELECT database_name, options FROM hologres.hg_external_databases();

Refresh external metadata

Force an immediate metadata refresh instead of waiting for the cache to expire:

REFRESH CACHE FOR EXTERNAL DATABASE <EXT_DB_NAME> WITH (cache_level = 'metadata');

Modify the metadata refresh interval

The metadata_refresh_interval_sec parameter controls the full schema refresh interval per frontend (FE) node. The default is 7,200 seconds. Adjust it with:

ALTER EXTERNAL DATABASE <EXT_DB_NAME> WITH metadata_refresh_interval_sec 1800;