Hive tables (HMS)

Starting from OceanBase Database V4.4.1 (MySQL-compatible mode), you can access Hive tables registered in Hive Metastore (HMS) through HMS. HMS is responsible for providing metadata such as databases, tables, and partitions. Table data is still stored in external storage such as HDFS, OSS, or S3, and OceanBase does not hold these data replicas.

This topic describes how to access and query Hive tables through HMS. For details on the creation syntax of the HMS Catalog, Kerberos authentication, and Location configuration, see Load Hive tables by using a catalog.

Overview

When accessing Hive tables through HMS, the execution process in OceanBase Database consists of two steps:

1. Read metadata: Connect to Hive Metastore through the HMS Catalog to obtain information such as Hive databases, tables, partitions, and storage paths.
2. Read data files: Read data files in formats such as ORC and Parquet from external storage based on information such as the LOCATION returned by HMS.

This method is suitable for tables already managed by Hive in the Hadoop ecosystem. Users do not need to manually define column structures and file paths for each table in OceanBase Database.

Currently supported operations:

Prerequisites

1. Version and mode: OceanBase Database V4.4.1 or later, in MySQL-compatible mode.
2. HMS service: The OceanBase cluster can access Hive Metastore (using the Thrift protocol). The HMS address and port are provided by the operations team. The default port for open-source Hive is 9083, but this may vary in actual deployments.
3. Storage access: All OBServer nodes have read permission for the underlying storage of Hive tables (such as HDFS, OSS, or S3). When the underlying storage is HDFS, you must deploy the Java SDK environment. For more information, see Deploy the Java SDK environment for OceanBase Database.
4. Permissions: The current user must have Catalog-related permissions such as CREATE CATALOG and USE CATALOG. If Kerberos or restricted HDFS paths are used, you must also configure the relevant authentication credentials in the Location.

HMS external table access model

The access chain involves three layers; do not confuse them:

Key points:

• HMS is a metadata service, not a data storage location.
• Data files for Hive tables are stored under the LOCATION path recorded in the HMS metadata.
• Catalog permissions and storage read permissions are independent: having Catalog permissions does not guarantee the ability to read underlying files.

If Hive table data is located in a restricted HDFS path, or if Kerberos is enabled in the cluster, you must configure both the HMS Catalog and Location. For configuration methods, refer to the authentication instructions in Load Hive tables by using a catalog.

Procedure

Step 1: Create an HMS Catalog

CREATE EXTERNAL CATALOG hive_prod
PROPERTIES (
    TYPE = 'HMS',
    URI = 'thrift://hms.example.com:9083'
);

If Kerberos is enabled for HMS or HDFS, you need to add parameters such as PRINCIPAL, KEYTAB, and KRB5CONF in the PROPERTIES file. For the complete syntax and parameter descriptions, see Load a Hive table by using Catalog.

Step 2: (Optional) Create a Location

Create a Location when the Hive table data is located in an HDFS path that requires explicitly specifying a user or Kerberos credentials:

CREATE LOCATION hdfs_sales
URL = 'hdfs://namenode:8020/'
CREDENTIAL (
    USERNAME = 'hive'
);

How to determine the URL: Execute SHOW CREATE TABLE db.table in Hive and extract the root storage path from the LOCATION field.

Step 3: Switch Catalogs and Query

Method 1: Switch the session context

SET CATALOG hive_prod;
USE sales_db;

SELECT * FROM customer_log
WHERE dt = '20250401'
LIMIT 100;

Method 2: Use a three-part identifier

SELECT city, COUNT(*)
FROM hive_prod.sales_db.customer_log
WHERE dt >= '2025-04-01'
GROUP BY city;

Step 4: Federated query (Optional)

SELECT o.order_id, h.city
FROM internal.trade_db.orders o
JOIN hive_prod.sales_db.customer h ON o.user_id = h.id;

Sample queries

Query a partitioned table

The partition information of a Hive partitioned table is provided by HMS. When querying, specify the partitioning column in the WHERE clause. OceanBase can perform partition pruning to reduce the scan range.

SET CATALOG hive_prod;
USE sales_db;

SELECT product_id, SUM(amount)
FROM sales_detail
WHERE dt = '20250401' AND region = 'cn-east'
GROUP BY product_id;

View the table structure

DESC hive_prod.sales_db.sales_detail;

SHOW CREATE TABLE hive_prod.sales_db.sales_detail;

Supported operations

Hive tables and file formats

Query optimization

Assuming HMS metadata and storage access are normal, OceanBase supports the following Hive table queries:

• Partition pruning: When specifying partition columns in the WHERE clause, you can narrow down the scan range.
• Column pruning: Only read the columns involved in the SELECT statement (depending on the file format).
• Predicate pushdown: Some filter conditions are pushed down to the file read layer (subject to the execution plan).

Hive versions

Hive 1.2.x, 2.3.x, 3.1.x, and 4.x are supported. The column definitions and storage path formats may vary among different Hive versions. The actual metadata in HMS shall prevail.

Considerations

1. Read-only access: Hive tables accessed through HMS are read-only. Writing to a Hive table or modifying the table structure on the HMS side is not supported.
2. Tiered configuration of Catalog and Location: HMS connection failures and insufficient HDFS read permissions are two different issues that require separate troubleshooting.
3. Network deployment: When HMS and OBServer are deployed across data centers, metadata fetch latency may increase query execution time. It is recommended to deploy them within the same low-latency network.
4. Files not registered with HMS: If a data file is not registered as a Hive table in HMS, it cannot be accessed using the methods described in this topic. Instead, use an external file table or an external URL table.
5. Division of labor with HMS Catalog documentation: This topic focuses on the access process and query usage. For detailed configurations such as Kerberos and HDFS HA, see Load Hive tables by using Catalog.

References

• Load Hive tables by using Catalog
• Catalog and external tables
• CREATE EXTERNAL CATALOG
• SET CATALOG

Document description

• Key points: Describes the role of HMS in the Hive table access chain, and the operation process and support scope from creating a Catalog to querying.
• Boundaries with other topics: Does not cover Iceberg table loading and capability descriptions (see Load an Iceberg table through Catalog). Does not cover ODPS API external table mapping (see ODPS external tables. For detailed parameters and authentication configurations of HMS Catalog, see Load a Hive tables through Catalog, which is not repeated here.