Connect Hologres to OSS Data Lake via Hive Metastore - Hologres - Alibaba Cloud - Hologres

Starting from V2.2, Hologres supports Hive Metastore (HMS) as a metadata source for data lakes built on Object Storage Service (OSS). If your data lake runs on an E-MapReduce (EMR) cluster with OSS or OSS-HDFS as the storage layer, connect Hologres to HMS to query OSS data directly using SQL — without migrating or copying data.

Prerequisites

Before you begin, ensure that you have:

An activated OSS service. See Console quick start.
An EMR data lake cluster with test data loaded. See Access OSS-HDFS from EMR Hive or Spark and Create a cluster. The cluster must meet all of the following conditions:
- Hive version 3.1.3 or later
- Kerberos authentication disabled
- Metadata set to Self-managed RDS or Built-in MySQL
A Hologres instance with data lake acceleration enabled and a database created. See Purchase a Hologres instance and Create a database.

To enable data lake acceleration, go to the Hologres console. In the Actions column of the target instance, click Lake Acceleration and confirm.

A network connection between Hologres and the EMR cluster. Because Hologres is deployed in a classic network and EMR runs in a virtual private cloud (VPC), a reverse endpoint is required for the two services to communicate. Submit a network connection request. The Hologres support team will then guide you through the following steps:

Log on to the VPC console and create a reverse endpoint. See Access Alibaba Cloud services.

For the Type parameter, select Other Endpoint Services and enter the endpoint service name for the region where your EMR cluster is located:

Region	Endpoint service name
China (Beijing)	`com.aliyuncs.privatelink.cn-beijing.epsrv-2zeokrydzjd6kx3cbwmb`
China (Shanghai)	`com.aliyuncs.privatelink.cn-shanghai.epsrv-uf61fvlfwta7f7dv9n3x`
China (Zhangjiakou)	`com.aliyuncs.privatelink.cn-zhangjiakou.epsrv-8vbno4k4wwvys0eg2swp`

If your region is not listed, the Hologres team will create an endpoint service and provide the name after you submit the request. The connection uses an IP address. If the EMR cluster's IP address changes, reconfigure the connection.

Limitations

Read-only secondary Hologres instances do not support data lake acceleration.
UPDATE, DELETE, and TRUNCATE are not supported on foreign tables.
Auto Load (batch foreign table mapping from HMS) is not supported.
EMR clusters with Kerberos authentication enabled are not supported.

Connect Hologres to HMS

Hologres offers two ways to connect to HMS. Pick one based on your instance version and how much control you need over column mapping.

Method 1: external database (recommended). Requires Hologres V3.0 or later. An external database maps every HMS database and table to an external database, schema, and foreign table in Hologres at once, so you query data with a three-part name instead of creating one foreign table at a time.
Method 2: hive_fdw. Use this on Hologres V2.2 or later but earlier than V3.0, or when you need to map a subset of columns or rename tables.

Method 1: Use an external database (recommended)

Connect to your Hologres instance and create the external database. This step requires superuser permissions.

For the full syntax, see CREATE EXTERNAL DATABASE.

CREATE EXTERNAL DATABASE <EXTERNAL_DATABASE_NAME>
  owner '<ACCOUNT_NAME>'
  metastore_type 'hms'
  catalog_type 'hive'
  hive_metastore_uris 'thrift://<HIVE_METASTORE_IP>:<PORT>'
  metadata_cache_ttl_sec '600'
  metadata_cache_update_interval_sec '60'
  metadata_refresh_interval_sec '7200'
  oss_endpoint 'oss-<REGION_ID>-internal.aliyuncs.com'
  table_count_limitation_per_schema '2000'
  comment '<EXTERNAL_DATABASE_COMMENT>';

Parameter	Description	Example
`external_database_name`	Name of the external database.	`catalog_hive`
`owner`	Hologres account that owns the external database.	`p4_<ACCOUNT_ID>`
`metastore_type`	Type of metadata service. Set to hms for Hive Metastore.	`hms`
`catalog_type`	Type of catalog. Set to hive for a Hive catalog.	`hive`
`hive_metastore_uris`	URI of the Hive Metastore. Format: thrift://<HIVE_METASTORE_IP>:<PORT>. Default port is 9083.	`thrift://10.0.0.1:9083`
`metadata_cache_ttl_sec`	Optional. How long cached metadata stays valid, in seconds.	`600 (default)`
`metadata_cache_update_interval_sec`	Optional. Interval between metadata cache updates, in seconds.	`60 (default)`
`metadata_refresh_interval_sec`	Optional. Interval between full metadata refreshes, in seconds.	`7200 (default)`
`oss_endpoint`	OSS endpoint. For native OSS, use the internal endpoint for better performance.	`oss-cn-beijing-internal.aliyuncs.com`
`table_count_limitation_per_schema`	Optional. Maximum number of tables loaded per schema.	`2000 (default)`
`comment`	Optional. Description of the external database.	`holo-emr-hive`

The cache intervals and table limit above are sample values. Tune them to match your metadata update frequency and the number of tables in HMS.

(Optional) Create a user mapping.

A user mapping supplies the credentials that a given Hologres account uses to read from OSS. For details, see CREATE USER MAPPING.

CREATE USER MAPPING FOR <ACCOUNT_NAME>
EXTERNAL DATABASE <EXTERNAL_DATABASE_NAME>
OPTIONS (
  oss_access_id  '<ACCESS_KEY_ID>',
  oss_access_key '<ACCESS_KEY_SECRET>'
);

Query the foreign tables.

Once the external database exists, reference any table with a three-part name: external database, Hive database, Hive table.

Non-partitioned table:
```
SELECT *
FROM <EXTERNAL_DATABASE_NAME>.<HIVE_DATABASE_NAME>.<HIVE_TABLE_NAME>;
```
Partitioned table: filter on the partition key in the WHERE clause.
```
SELECT *
FROM <EXTERNAL_DATABASE_NAME>.<HIVE_DATABASE_NAME>.<HIVE_PARTITION_TABLE_NAME>
WHERE <PARTITION_KEY> = '<PARTITION_VALUE>';
```

Method 2: Use hive_fdw

Step 1: Create the extension

Run the following SQL command to install the hive_fdw foreign data wrapper (FDW). This operation requires superuser permissions and only needs to run once per database.

CREATE EXTENSION IF NOT EXISTS hive_fdw;

Step 2: Create a foreign server

Create a foreign server that points to your HMS instance and OSS storage.

Before running the command, collect the following values:

HMS IP address: In the E-MapReduce console, click Node Management for your cluster. On the Node Management tab, find the Internal IP of the master node.
OSS endpoint: In the OSS console, open the bucket overview page and check the Access Ports area.

CREATE SERVER IF NOT EXISTS <SERVER_NAME> FOREIGN DATA WRAPPER hive_fdw
OPTIONS (
  hive_metastore_uris 'thrift://<HIVE_METASTORE_IP>:<PORT>',
  oss_endpoint '<OSS_ENDPOINT>'
);

Parameter	Required	Description	Example
`server_name`	Yes	A custom name for the foreign server.	`hive_server`
`hive_metastore_uris`	Yes	The URI of the Hive Metastore. Format: `thrift://<HIVE_METASTORE_IP>:<PORT>`. Default port is 9083.	`thrift://172.16.0.250:9083`
`oss_endpoint`	Yes	The OSS endpoint. For native OSS, use the internal endpoint for better performance. For OSS-HDFS, only the internal endpoint is supported.	See examples below

For oss_endpoint, choose based on your storage type:

Native OSS: Use the internal endpoint.
```
oss-cn-shanghai-internal.aliyuncs.com
```
OSS-HDFS: Only internal network access is supported.
```
<BUCKET_NAME>.cn-beijing.oss-dls.aliyuncs.com
```

Step 3: (Optional) Create a user mapping

A user mapping controls which Hologres accounts can access external data through a foreign server. For example, a foreign server owner can grant a Resource Access Management (RAM) user access to OSS data.

For details on the CREATE USER MAPPING syntax, see the PostgreSQL documentation.

-- Grant the current user access to the foreign server
CREATE USER MAPPING FOR current_user SERVER <SERVER_NAME> OPTIONS (
  dlf_access_id  '<ACCESS_KEY_ID>',
  dlf_access_key '<ACCESS_KEY_SECRET>',
  oss_access_id  '<ACCESS_KEY_ID>',
  oss_access_key '<ACCESS_KEY_SECRET>'
);

-- Grant a RAM user (123xxx) access to the foreign server
CREATE USER MAPPING FOR "p4_123xxx" SERVER <SERVER_NAME> OPTIONS (
  dlf_access_id  '<ACCESS_KEY_ID>',
  dlf_access_key '<ACCESS_KEY_SECRET>',
  oss_access_id  '<ACCESS_KEY_ID>',
  oss_access_key '<ACCESS_KEY_SECRET>'
);

-- Remove user mappings
DROP USER MAPPING FOR CURRENT_USER SERVER <SERVER_NAME>;
DROP USER MAPPING FOR "p4_123xxx" SERVER <SERVER_NAME>;

Step 4: Create a foreign table

Hologres provides two commands for creating foreign tables:

Command	Best for
CREATE FOREIGN TABLE	A small number of tables, or when you need to map a subset of columns or assign a custom table name.
IMPORT FOREIGN SCHEMA	Batch mapping of multiple tables from an external schema.

Hologres supports partitioned tables in OSS. Supported partition key types are TEXT, VARCHAR, and INT.

With CREATE FOREIGN TABLE: define partition fields as regular columns, since this command maps the schema without storing data.

With IMPORT FOREIGN SCHEMA: the system handles field mapping automatically.

If an external table name conflicts with an existing Hologres internal table, IMPORT FOREIGN SCHEMA skips that table. Use CREATE FOREIGN TABLE to map it with a different name.

-- Create a single foreign table
CREATE FOREIGN TABLE <HOLO_SCHEMA_NAME>.<TABLE_NAME>
(
  column_name data_type
  [, ...]
)
SERVER <SERVER_NAME>
OPTIONS (
  schema_name '<HIVE_DATABASE_NAME>',
  table_name  '<HIVE_TABLE_NAME>'
);

-- Import multiple foreign tables in batch
IMPORT FOREIGN SCHEMA <HIVE_DATABASE_NAME>
[
  { LIMIT TO | EXCEPT }
  ( table_name [, ...] )
]
FROM SERVER <SERVER_NAME>
INTO <HOLO_SCHEMA_NAME>
OPTIONS (
  if_table_exist        'update',
  if_unsupported_type   'error'
);

Step 5: Query the foreign table

After creating the foreign table, query it directly to read data from OSS.

Non-partitioned table:

SELECT * FROM <HOLO_SCHEMA_NAME>.<TABLE_NAME>;

Partitioned table:

SELECT * FROM <HOLO_SCHEMA_NAME>.<PARTITION_TABLE_NAME>
WHERE <PARTITION_KEY> = '<PARTITION_VALUE>';