Hologres:Access OSS data with Hive Metastore (beta)

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://<hms_ip>:<port>',
  oss_endpoint '<oss_endpoint>'
);

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  '<AccessKey_ID>',
  dlf_access_key '<AccessKey_Secret>',
  oss_access_id  '<AccessKey_ID>',
  oss_access_key '<AccessKey_Secret>'
);

-- Grant a RAM user (123xxx) access to the foreign server
CREATE USER MAPPING FOR "p4_123xxx" SERVER <server_name> OPTIONS (
  dlf_access_id  '<AccessKey_ID>',
  dlf_access_key '<AccessKey_Secret>',
  oss_access_id  '<AccessKey_ID>',
  oss_access_key '<AccessKey_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:

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 <hive_server_name>
OPTIONS (
  schema_name '<ext_db_name>',
  table_name  '<ext_table_name>'
);

-- Import multiple foreign tables in batch
IMPORT FOREIGN SCHEMA <ext_db_name>
[
  { LIMIT TO | EXCEPT }
  ( table_name [, ...] )
]
FROM SERVER <hive_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>.<hive_table>;

Partitioned table:

SELECT * FROM <holo_schema>.<hive_partition_table>
WHERE <partition_key> = '<partition_value>';