Access Hive tables through HMS Catalog

Starting from V4.4.1 in MySQL-compatible mode, OceanBase Database supports creating an HMS Catalog through Hive Metastore (HMS) to provide unified access to Hive tables registered in HMS. HMS provides metadata for databases, tables, partitions, and other objects. The table data remains in external storage such as HDFS, OSS, or S3.

This topic describes how to create an HMS Catalog, configure storage-access authentication, query Hive tables, and perform federated analysis with internal tables.

Access model

OceanBase Database accesses a Hive table through HMS in two steps:

1. Read metadata: Connect to Hive Metastore through the HMS Catalog and retrieve information about Hive databases, tables, partitions, and storage paths.
2. Read data files: Use information returned by HMS, such as LOCATION, to read ORC, Parquet, or other data files from external storage.

The access path consists of three layers:

• HMS is a metadata service, not a data storage location.
• Data files for a Hive table are stored under the LOCATION path recorded in HMS metadata.
• Catalog privileges and storage read permissions are independent. Having Catalog privileges does not necessarily grant access to the underlying files.

If Hive table data is stored in an HDFS path with restricted permissions, or if the cluster uses Kerberos, configure the HMS Catalog and Location separately.

Supported capabilities

Supported operations

Table types and Hive versions

• Supported table type: Hive tables, the native table type of HMS Catalog.
• Hive versions: Hive 1.2.x, 2.3.x, 3.1.x, and 4.x are supported. Table definitions and storage-path formats can vary between versions. The actual metadata in HMS prevails.
• HMS Catalog name: Starting from Hive Metastore 3.x, HMS can maintain multiple Catalogs. When connecting to such an HMS instance, use HMS_CATALOG_NAME to specify the Catalog to access. If this parameter is omitted, HMS applies its default behavior. In HMS 3.x and later, the default Catalog is typically named hive.

File formats and query support

When HMS metadata and storage access are available, OceanBase Database supports the following optimizations for Hive table queries:

• Partition pruning: Specify partitioning columns in the WHERE clause to reduce the scan scope.
• Column pruning: Read only the columns referenced by SELECT, depending on the file format.
• Predicate pushdown: Push some filter conditions down to the file-reading layer, subject to the execution plan.

Relationship with Iceberg

HMS Catalog can also access Iceberg tables whose metadata is registered in Hive Metastore. For detailed Iceberg capabilities, differences between Catalog types, and write boundaries, see Load an Iceberg table through a Catalog. The following list provides a summary:

• Hive 4.x and later: The Hive engine natively supports Iceberg tables. OceanBase Database can access these tables directly through HMS Catalog.
• Hive 1.2.x, 2.3.x, and 3.1.x: Hive itself does not support Iceberg. In these environments, Iceberg tables are typically created by engines such as Spark or Flink, which register the metadata in HMS. OceanBase Database identifies and reads the tables based on the metadata in HMS.

Prerequisites

1. Version and mode: OceanBase Database V4.4.1 or later and a MySQL-compatible tenant.
2. Privileges: The current user has Catalog privileges such as CREATE CATALOG and USE CATALOG.
3. HMS service: The OceanBase cluster can access Hive Metastore through the Thrift protocol. Obtain the HMS address and port from your O&M team. The default port for open-source Hive is 9083, but the port can differ in your deployment.
4. Storage access: All OBServer nodes have read access to the underlying storage of the Hive tables, such as HDFS, OSS, or S3.
5. Environment dependencies: If the underlying storage is HDFS, deploy the Java SDK environment in advance. For more information, see Deploy the Java SDK environment for OceanBase Database.
6. Authentication configuration: If the HDFS path has restricted permissions or the cluster uses Kerberos, configure the HMS Catalog at the metadata layer and the Location at the data layer separately.

Procedure

The examples in this topic use hive_prod as the OceanBase Catalog name. This name is different from HMS_CATALOG_NAME in HMS. Replace the URI, database and table names, and credential paths based on your environment.

Before you start: Select an authentication configuration

Accessing Hive tables involves authentication at two layers: the Catalog layer, which connects to HMS, and the Location layer, which reads storage files. Configure the two layers separately. Before creating a Catalog or Location, confirm the cluster security mode with the Hadoop O&M team and select a configuration based on the following table:

Based on these results, select a Location configuration for the HDFS scenario. For OSS and S3 examples, see CREATE EXTERNAL CATALOG.

Sequence of operations:

1. Deploy the Java SDK.
2. Create an HMS Catalog in Step 1.
3. Create a Location as needed in Step 2. You can skip this step in some scenarios.
4. Verify metadata and storage connectivity in Step 3.
5. Run queries or federated analysis in Steps 4 and 5.

Step 1: Create an HMS Catalog

Syntax

CREATE EXTERNAL CATALOG [IF NOT EXISTS] catalog_name
PROPERTIES (
    TYPE = 'HMS',
    URI = 'thrift://host:port',
    [PRINCIPAL = '...'],
    [KEYTAB = '...'],
    [KRB5CONF = '...'],
    [MAX_CLIENT_POOL_SIZE = 20],
    [SOCKET_TIMEOUT = 10000000],
    [HMS_CATALOG_NAME = '...']
);

Parameters

SIMPLE authentication example

In SIMPLE authentication mode, the Catalog layer does not require Kerberos parameters. Whether you need a Location for HDFS read access depends on the scenario.

CREATE EXTERNAL CATALOG hive_prod
PROPERTIES (
    TYPE = 'HMS',
    URI = 'thrift://xxx.xxx.xxx.xxx:xxxx',
    HMS_CATALOG_NAME = 'hive'
);

Kerberos authentication example (metadata layer)

CREATE EXTERNAL CATALOG hive_prod
PROPERTIES (
    TYPE = 'HMS',
    URI = 'thrift://hms.example.com:9083',
    PRINCIPAL = 'hive/hms.example.com@EXAMPLE.COM',
    KEYTAB = '/etc/ob/hive.keytab',
    KRB5CONF = '/etc/krb5.conf',
    HMS_CATALOG_NAME = 'hive'
);

Step 2: Create a Location (optional)

Create a Location when Hive table data is stored in an HDFS path that requires an explicitly specified operating-system user or Kerberos credentials. The following example applies to the data layer in a Kerberos environment:

CREATE LOCATION hdfs_kerberos_ha
URL = 'hdfs://namenode:8020/'
CREDENTIAL (
    PRINCIPAL = 'ob_hdfs@EXAMPLE.COM',
    KEYTAB   = '/etc/ob/hdfs.keytab',
    KRB5CONF = '/etc/krb5.conf',
    CONFIGS  = '...'  -- For HA and other settings, see the authentication section below.
);

Example for a restricted path in SIMPLE mode:

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

Determine the Location URL:

1. Run SHOW CREATE TABLE your_db.your_table; in Hive CLI or Beeline.
2. Find the LOCATION field in the output, for example, LOCATION 'hdfs://namenode:8020/warehouse/your_db.db/your_table'.
3. Extract the protocol, service or host name, and service port as the URL: hdfs://namenode:8020/.

How a Location is associated with a Catalog:

You do not need to manually bind a Location when creating a Catalog. When querying a Hive table, OceanBase Database first obtains the table LOCATION from HMS and then automatically matches an existing Location by URL prefix. If a match is found, OceanBase Database uses the credentials in that Location to access the underlying files. Otherwise, it uses the default access method, which is equivalent to the Observer process user in SIMPLE mode.

The Location URL must therefore cover the prefix of the target table LOCATION. For example, if the table path is hdfs://namenode:8020/warehouse/sales.db/t1, you can set the Location URL to hdfs://namenode:8020/.

Step 3: Verify connectivity

After creating the Catalog and, if needed, the Location, verify metadata and storage connectivity before running a large scan.

-- Switch to the HMS Catalog.
SET CATALOG hive_prod;

-- Verify that HMS metadata is readable. A failure usually indicates a Catalog-layer or HMS connectivity issue.
DESC hive_prod.sales_db.customer_log;

-- Optional: View the table definition in HMS.
SHOW CREATE TABLE hive_prod.sales_db.customer_log;

-- Run a small query to verify storage-layer read permissions. A failure usually indicates a Location or HDFS permission issue.
SELECT * FROM sales_db.customer_log
WHERE dt = '20250401'
LIMIT 10;

Step 4: Query a Hive table

After verification succeeds, use either of the following methods. If you ran SET CATALOG in Step 3, you can omit the first line in Method 1.

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 5: Run a 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;

More query examples (optional)

Query a partitioned table

HMS provides the partition information for a partitioned Hive table. Specify partitioning columns in the WHERE clause so that OceanBase Database can push down partition pruning:

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 schema

DESC hive_prod.sales_db.sales_detail;

SHOW CREATE TABLE hive_prod.sales_db.sales_detail;

Query an Iceberg table registered in HMS

If an Iceberg table is registered in HMS, query it through the same HMS Catalog. For capability boundaries, see Load an Iceberg table through a Catalog.

SET CATALOG hive_prod;

SELECT product_id, SUM(sales)
FROM iceberg_db.sales_iceberg
WHERE event_time >= '2025-04-01'
GROUP BY product_id;

Authentication mechanisms for HMS Catalog and HDFS storage

Access to Hive Metastore involves two independent authentication layers in OceanBase Database:

• Catalog layer: Connects to Hive Metastore and retrieves metadata such as table schemas and partitions.
• Location layer: Reads the actual data files from HDFS or another file system.

Configure the two authentication layers separately and align them with the Hadoop cluster security policy.

The server determines the Hadoop authentication mode

The Hadoop cluster defines its security mode through hadoop.security.authentication in hdfs-site.xml:

<property>
  <name>hadoop.security.authentication</name>
  <value>kerberos</value> <!-- or simple -->
</property>

• kerberos: All clients, including the HMS Client and HDFS Client, must pass Kerberos authentication.
• simple: The server trusts the operating-system user name declared by the client. Permissions are controlled by the owner, group, and POSIX permission bits of HDFS files and directories, such as drwxr-xr-x. Kerberos authentication is not used.

Authentication configuration in OceanBase Database

Important configuration rule: If either HMS or HDFS uses Kerberos, verify that credentials are correctly deployed for both layers.

SIMPLE mode

Applicable scenarios:

• HDFS does not use Kerberos and relies on Linux file permissions.
• Tables are written by specific users. For example, an Impala path can be owned by impala, while a Hive on Tez path can be owned by hive.

Configuration requirements:

• Globally readable path: The HDFS directory has open permissions, such as drwxr-xr-x (755), and can be read by any user. You do not need to create a Location.

  • The owner has read, write, and execute permissions: rwx = 4 + 2 + 1 = 7.
  • The group and other users have read and execute permissions only: r-x = 4 + 0 + 1 = 5.
  • OceanBase Database can access a path with these permissions without creating a Location.

• Restricted path: If the OceanBase Observer process user, such as admin, does not belong to a user or group authorized to access the HDFS path, create a Location and specify a user that has the required HDFS permissions.

CREATE LOCATION my_loc
URL = 'hdfs://mycluster/'
CREDENTIAL (USERNAME = 'username');

Scenario 1: Kerberos is not required and an HDFS user does not need to be specified

• Applicable scenario: A development or test environment in which the HDFS cluster does not use Kerberos authentication, that is, hadoop.security.authentication=simple.

• Operation: You do not need to create a Location.

Scenario 2: Kerberos is not required but an HDFS user must be specified

• Applicable scenario: A development or test environment in which the HDFS cluster does not use Kerberos authentication, that is, hadoop.security.authentication=simple, but the target Hive table data is stored in an HDFS path with restricted permissions. For example, the data was written by Impala, Hive, or another engine, the path is owned by a specific user, and global read permission is not enabled.

• Typical cases:

  • Impala writes the table to HDFS, and the HDFS path is owned by impala.
  • A user submits the Hive job, and the path can be owned by hive, etl_user, or another user.
  • OceanBase Observer accesses HDFS as its operating-system user, such as admin, by default. If that user does not have read permission, the query fails.

• Example: Assume that an Impala job writes a Hive table to hdfs://namenode:8020/warehouse/sales.db/click_log.

CREATE LOCATION hdfs_impala_data
URL = 'hdfs://namenode:8020/'    -- For information about the URL, see the following instructions.
CREDENTIAL (
  USERNAME = 'username'
);

Determine the correct URL:

1. Run the following statement in Hive CLI or Beeline:

   SHOW CREATE TABLE your_db.your_table;
   

2. Find the LOCATION field in the output, for example:

   LOCATION 'hdfs://namenode:8020/warehouse/your_db.db/your_table'
   

3. Extract the protocol, service or host name, and service port. In this example, set the URL in CREATE LOCATION to hdfs://namenode:8020/.

Scenario 3: Kerberos is disabled and HDFS is in high-availability mode

Operation: You do not need to set PRINCIPAL, KEYTAB, or KRB5CONF.

CREATE LOCATION hdfs_location_ha
    URL = 'hdfs://${nameservice_id}'  -- Use the logical service name.
    CREDENTIAL (
        CONFIGS = 'dfs.nameservices=${nameservice id}#dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}#dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}#dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}#dfs.ha.automatic-failover.enabled.${nameservice id}=true#dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
        );

KERBEROS mode

Applicable scenario: An enterprise Hadoop cluster in which HMS or HDFS uses Kerberos.

Configuration requirements:

• HMS Catalog: Specify PRINCIPAL, KEYTAB, and KRB5CONF in the PROPERTIES clause of CREATE EXTERNAL CATALOG.

Scenario 4: Kerberos and a single HDFS NameNode (non-HA)

-- Create a Location for Kerberos authentication and a single HDFS NameNode.
CREATE LOCATION hdfs_kerberos_single
URL = 'hdfs://namenode.example.com:8020/'   -- Run SHOW CREATE TABLE to obtain the complete HDFS path and extract its root URL.
CREDENTIAL (
  PRINCIPAL = "hdfs/TEST@EXAMPLE.COM",
  KEYTAB   = "/data/hdfs.keytab",
  KRB5CONF = "/data/krb5.conf",
  CONFIGS  = 'dfs.data.transfer.protection=integrity'
);

Scenario 5: Kerberos and HDFS high availability

CREATE LOCATION hdfs_kerberos_ha
URL = 'hdfs://${nameservice_id}'
CREDENTIAL (
    PRINCIPAL = 'ob_hdfs@EXAMPLE.COM',
    KEYTAB   = '/etc/ob/hdfs.keytab',
    KRB5CONF = '/etc/krb5.conf',
    CONFIGS  = 'dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#dfs.namenode.rpc-address.mycluster.nn1=nn1:8020#dfs.namenode.rpc-address.mycluster.nn2=nn2:8020#dfs.client.failover.proxy.provider.mycluster=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

For more Location parameters, see the LOCATION section of CREATE EXTERNAL TABLE and Catalogs and external tables.

Considerations and limits

1. Read-only access: Hive tables under an HMS Catalog are read-only. They do not support DML or DDL operations such as INSERT, UPDATE, or DROP TABLE, and you cannot modify their schemas in HMS through OceanBase Database.
2. Separate authentication: Configure authentication for the Catalog metadata layer and Location data layer independently. An HMS connection failure and insufficient HDFS read permissions are different issues and must be troubleshot separately.
3. Mode alignment: The OceanBase authentication configuration must match hadoop.security.authentication in the Hadoop cluster. If either HMS or HDFS uses Kerberos, configure Kerberos credentials for both layers.
4. Permissions in SIMPLE mode: Make sure that the Observer process user or the USERNAME specified in the Location has read permission on the HDFS path.
5. HMS Catalog name: If HMS 3.1.3 or later contains multiple Catalogs, make sure that HMS_CATALOG_NAME matches the actual name in HMS when creating the Catalog.

References

• Catalogs and external tables
• Load an Iceberg table through a Catalog
• Catalog overview
• CREATE EXTERNAL CATALOG
• SET CATALOG
• Deploy the Java SDK environment for OceanBase Database