Load a Hive table through Catalog

Starting from V4.4.1, OceanBase Database supports connecting through HMS (Hive Metastore) to create an HMS Catalog, thereby providing unified access to tables managed by Hive Metastore—including traditional Hive tables and tables stored in Iceberg format but with metadata registered in Hive Metastore.

• Unified metadata management: Automatically synchronize table structures via Hive Metastore.
• Federated queries: JOIN and analyze with internal OceanBase tables.
• Read-only security: Prevent accidental modification of source data.

OceanBase Database officially supports the HMS Catalog feature starting from V4.4.1. To help users understand and plan for the AP capabilities of OceanBase Database in advance, relevant usage instructions were provided in the V4.3.5 documentation set.

Supported capabilities

• Read-only access: All objects under the HMS Catalog are read-only. Currently, DML/DDL operations such as INSERT, UPDATE, and DROP TABLE are not supported.

• Supported table types: Hive tables, which are the native table types of HMS Catalog.

• Hive version compatibility

  • Supported versions: Hive 1.2.x, Hive 2.3.x, Hive 3.1.x, and Hive 4.x.

  • HMS Catalog name: Starting from Hive Metastore 3.x, it is supported to maintain multiple Catalogs within HMS for resource management. When connecting to such an HMS, you can specify the Catalog to access via HMS_CATALOG_NAME. If this parameter is not specified, no Catalog is explicitly chosen, and HMS will handle it according to its default behavior. In Hive Metastore 3.x and later, the default Catalog name is typically hive.

  • Compatibility notes for Iceberg tables:

    • Hive 4.x and later: The Hive engine natively supports Iceberg tables. You can perform operations such as table creation and metadata updates directly through Hive, and OceanBase can access them directly via the HMS Catalog.
    • Hive 1.2.x / 2.3.x / 3.1.x: Hive itself does not support Iceberg. In such environments, Iceberg tables are usually created and written by computing engines like Spark or Flink, while the Iceberg metadata is registered with Hive Metastore. OceanBase relies on this metadata in HMS to identify and read Iceberg tables.

Prerequisites

1. Privilege requirements

   • The current user must have Catalog-related privileges such as CREATE CATALOG and USE CATALOG (MySQL-compatible mode). Catalog capabilities are currently only supported in MySQL-compatible mode.

2. Environmental dependencies: If the underlying storage is HDFS, the Java SDK environment must be deployed in advance. For more information, see Deploy the Java SDK environment for OceanBase Database.

3. External access

   • HMS service: The OceanBase cluster must be able to access Hive Metastore (using the Thrift protocol). The address and port of the HMS service should be provided by the customer's operations team. Open-source Hive uses port 9083 by default, but this may vary in actual deployments. Please refer to the on-site configuration.
   • Storage system: All OBServer nodes have read permissions for HDFS/S3/OSS. If path permissions are restricted or Kerberos is enabled, you need to configure the corresponding authentication credentials via the Location, as described in the Authentication mechanism between HMS Catalog and HDFS storage section below.

Syntax for creating an HMS Catalog

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 = '...']
);

Parameter description

Create an HMS Catalog with SIMPLE authentication

This is the normal authentication mode, where Location authentication is not required.

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

For more information about the Hadoop authentication mode, see the Authentication mechanism between HMS Catalog and HDFS storage section below.

Create an HMS Catalog with Kerberos authentication

For more information about the Hadoop authentication mode, see the Authentication mechanism between HMS Catalog and HDFS storage section below.

Step 1: Create an HMS Catalog with Kerberos authentication (Metadata layer)

CREATE EXTERNAL CATALOG hms_kerberos
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 an HDFS Location with Kerberos authentication (Data layer)

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  = '...'  -- HA configuration as above
);

The HMS Catalog can now discover table structures normally, and the Location can securely read HDFS data files.

Switch Catalogs

SET CATALOG hms_catalog;

Query external data sources through Catalog

-- Hive table
SELECT city, COUNT(*) FROM hive_db.customer WHERE dt >= '2025-04-01' GROUP BY city;

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

-- Federated Query
SELECT o.order_id, h.city
FROM internal.test_db.orders o
JOIN hms_catalog.hive_db.customer h ON o.user_id = h.id;

Authentication mechanisms for HMS Catalog and HDFS storage

In OceanBase Database, accessing Hive Metastore (HMS) involves two independent authentication layers:

• Catalog layer: Used to connect to Hive Metastore (to retrieve metadata such as table structures and partitions).

• Location layer: Used to read data files stored in file systems like HDFS.

The authentication methods for these two layers must be configured separately and must align with the security policies of the Hadoop cluster.

The Hadoop authentication mode is determined by the server

The Hadoop cluster defines its security mode using the hadoop.security.authentication parameter in the hdfs-site.xml configuration file:

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

• kerberos: Kerberos authentication is enabled. All clients (including HMS Client and HDFS Client) must pass Kerberos authentication.
• simple: The Hadoop server trusts the operating system username declared by the client, without performing password or credential verification. Permission control is based solely on the owner, group, and POSIX permissions of HDFS files/directories (for example, drwxr-xr-x(755)), with no Kerberos authentication.

Authentication configuration method for OceanBase Database

Important configuration rule: If Kerberos is enabled for either HMS or HDFS, please check whether the deployment is correct.

Detailed explanation of the two authentication modes

SIMPLE mode (common in development/test environments)

Application scenarios:

• Kerberos is not enabled for HDFS, relying only on Linux file permission controls.

• Tables are written by specific users (for example, Impala writes to an HDFS path whose owner is impala; Hive on Tez writes to a path whose owner is the job submitting user, such as hive).

Configuration requirements:

• Anonymous readable paths: ● The HDFS directory permissions are open (e.g., drwxr-xr-x (755)), allowing any user to read without creating a Location.

  • Meaning: The owner has read, write, and execute permissions (rwx = 4+2+1 = 7); The group and others have only read and execute permissions (r-x = 4+0+1 = 5);
  • For such paths, OceanBase does not need to create a Location and can access them directly.

• Restricted paths: If the OceanBase Observer process user (e.g., admin) is not in the directory authorized users/groups, then you must:

If the OceanBase Observer process user (e.g., admin) does not belong to the user or user group allowed access by the HDFS path permissions, you need to create a Location and specify a user with corresponding access permissions on the HDFS side.

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

Scenario 1: No Kerberos required, and no HDFS user needs to be specified (default anonymous)

• Application: Suitable for development or test environments where Kerberos authentication is not enabled in the HDFS cluster (i.e., hadoop.security.authentication=simple).

• Procedure: No Location needs to be created.

Scenario 2: No Kerberos required, but an HDFS user needs to be specified

• Application: Suitable for development or test environments where Kerberos authentication is not enabled in the HDFS cluster (i.e., hadoop.security.authentication=simple), but the data files of the target Hive table are stored on a restricted HDFS path (for example, written by Impala, Hive, or another engine, the path owner is a specific user, and global read permission is not granted).

• Typical cases:

  • A table is written to HDFS by Impala, with the path owner being impala.
  • A table is written by a user submitting a Hive job → The owner may be hive, etl_user, etc.
  • OceanBase Observer accesses HDFS by default using the operating system user (e.g., admin). If this user does not have read permission, the query fails.

• Example: Assume a Hive table is written to HDFS by Impala, and its HDFS path is hdfs://namenode:8020/warehouse/sales.db/click_log.

CREATE LOCATION hdfs_impala_data
URL = 'hdfs://namenode:8020/'    -- URL, see description below
CREDENTIAL (
  USERNAME = 'username'
);

How to determine the correct URL:

1. Execute the following in Hive CLI or Beeline:

   SHOW CREATE TABLE your_db.your_table;
   

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

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

3. Extract the protocol + service name/host name + service port as the URL. That is, fill hdfs://namenode:8020/ in the CREATE LOCATION statement.

Scenario 3: Kerberos is not enabled, and HDFS is high-availability (HA)

Action: No need to set the PRINCIPAL, KEYTAB, or KRB5CONF parameters.

CREATE LOCATION hdfs_location_ha
    URL = 'hdfs://${nameservice_id}'  -- Logical service name is recommended.
    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 (standard for production environments)

Application scenarios:

• Enterprise-level Hadoop clusters where HMS or HDFS has Kerberos security authentication enabled.

Configuration requirements:

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

• HDFS Location: Refer to Step 2: Create a Kerberos-authenticated HDFS Location above and Scenarios 4 and 5 below; the CREATE LOCATION syntax follows the SQL reference.

Scenario 4: Enable Kerberos, and HDFS has a single NameNode (non-HA mode)

-- Create LOCATION: Kerberos authentication + single-node HDFS
CREATE LOCATION hdfs_kerberos_single
URL = 'hdfs://namenode.example.com:8020/'   -- Obtain the complete HDFS path of the table by executing the SHOW CREATE TABLE statement, and extract the root URL.
CREDENTIAL (
  PRINCIPAL = "hdfs/TEST@EXAMPLE.COM",
  KEYTAB   = "/data/hdfs.keytab",
  KRB5CONF = "/data/krb5.conf",
  CONFIGS  = 'dfs.data.transfer.protection=integrity'
);

Scenario 5: Enable Kerberos, and HDFS is in high availability (HA) mode

CREATE LOCATION hdfs_kerberos_ha
URL = 'hdfs://${nameservice id}'  -- Logical service name is recommended.
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 information about Location parameter configuration, see the LOCATION section in CREATE EXTERNAL TABLE and Catalogs and external tables.

Configuration instructions and considerations

• Separation of authentication: Catalog (metadata) and Location (data) authentication are configured independently.
• Mode alignment: The authentication method in OceanBase must match the hadoop.security.authentication setting of the Hadoop cluster.
• Kerberos consistency: If Kerberos is enabled for either HMS or HDFS, Kerberos credentials must be configured for both.
• Permissions in SIMPLE mode: Ensure the Observer user or the specified USER has read permission on the HDFS path.
• HMS Catalog name: If multiple catalogs exist in HMS 3.1.3 or later, when creating an HMS Catalog, confirm that the HMS_CATALOG_NAME matches the actual catalog name on the HMS side.

References

• Catalog and external tables
• Load an Iceberg table through Catalog
• Hive tables (HMS)
• Overview of Catalog
• Deploy the Java SDK environment for OceanBase Database
• CREATE EXTERNAL CATALOG