Catalog and External Tables

Feature overview

OceanBase supports accessing external data through Catalogs and External Tables. Both are external data access capabilities, but they differ in metadata management methods and application scenarios:

• Catalog: Connects to an external metadata service (such as Hive Metastore or MaxCompute Project) to automatically synchronize database and table structures. It is suitable for structured data sources with existing metadata centers.
• External Table: Manually defines the table structure and access path within OceanBase. It is suitable for scenarios such as file paths, temporary data, or single-table mapping.

OceanBase has supported ODPS Catalog since V4.3.5 BP2 and HMS Catalog since V4.4.1. Through Catalogs, you can query Hive tables, Iceberg tables managed by HMS, and tables in MaxCompute (ODPS). During queries, you can perform federated analysis with internal tables in the internal Catalog.

Deployment and environment dependencies

Relationship between Catalogs and External Tables

When OceanBase accesses external data, the following hierarchy is involved. Do not confuse them:

OceanBase provides two external data access modes:

Selection guide:

• Catalogs are suitable for scenarios where the data source is already registered in HMS or MaxCompute. OceanBase reads metadata through Catalogs and then accesses underlying storage or remote tables.
• Explicit External Tables are suitable for accessing CSV/Parquet/ORC files by path, or mapping a single ODPS table via the MaxCompute API.
• Iceberg tables are accessed through External Catalogs (such as HMS Catalog or REST Catalog) and do not belong to the CREATE EXTERNAL TABLE access method. For details, see Load an Iceberg table through Catalog.

Catalog types

Catalogs are the top-level namespace for database objects, used to organize and isolate metadata from different data sources. OceanBase supports internal Catalogs and external Catalogs:

Internal Catalog

Each tenant has one and only one internal Catalog named internal, which contains objects created within that tenant using DDL statements such as CREATE DATABASE and CREATE TABLE. The internal Catalog is logically isolated from external Catalogs; creating, deleting, or renaming the internal Catalog is not supported.

• By default, you can directly use USE database; to access it without explicitly specifying the Catalog.
• Supports complete DDL/DML; data is stored in the OceanBase distributed storage engine.

USE ap_db;
SELECT COUNT(*) FROM user_behavior;

-- Explicitly specify the internal catalog (optional).
SELECT COUNT(*) FROM internal.ap_db.user_behavior;

External Catalog

An external Catalog is created using CREATE EXTERNAL CATALOG. The SQL reference currently uses ODPS Catalog as the official syntax; for HMS Catalog (V4.4.1+) and Iceberg-related REST/FILESYSTEM Catalog parameters, see the following integration documents. Before use, check whether the SQL reference has been updated based on your current cluster version:

• Load a Hive table through Catalog
• ODPS Catalog
• Load an Iceberg table through Catalog

Create and access Catalogs

Create an External Catalog

The internal Catalog does not need to be manually created. For the syntax to create an external Catalog, see CREATE EXTERNAL CATALOG; descriptions of various parameters are provided in the aforementioned integration documents.

View Catalogs

SHOW CATALOGS;

Switch Catalogs and perform cross-Catalog queries

When referencing tables, follow these rules:

• If the session context has not been switched, use the three-part identifier: catalog_name.database_name.table_name
• After executing SET CATALOG and USE, you can use the abbreviated table name.

Method 1: Three-part identifier (no context switching required)

SELECT CURRENT_CATALOG();
SELECT DATABASE();

SELECT * FROM hive_catalog.hive_db.hive_table;

SELECT h.*, o.*
FROM hive_catalog.hive_db.hive_table h
JOIN internal.ap_db.ap_table o ON h.id = o.id;

Method 2: Use the abbreviated table name after switching contexts

SET CATALOG hive_catalog;
USE hive_db;

SELECT h.*, o.*
FROM hive_table h
JOIN internal.ap_db.ap_table o ON h.id = o.id;

You can also execute SET CATALOG catalog_name; separately, or specify it explicitly in the query:

SELECT * FROM odps_catalog.database_name.table_name;

For syntax details, see SET CATALOG and Query catalogs.

Catalog support scope

This section describes the capability boundaries of each external catalog. For detailed information on creation, authentication, and location configuration, see the corresponding integration documentation.

HMS Catalog

For more information about the ARRAY data type, see Overview of array element types. For information about the authentication mechanism between HMS and HDFS, see Load Hive tables by using Catalogs.

ODPS Catalog

For detailed parameter and API mode descriptions, see ODPS Catalog.

Catalog privilege management

Catalog privileges are divided into two levels:

Privileges in MySQL-compatible mode

Grant global privileges:

GRANT CREATE CATALOG ON *.* TO 'user1';
GRANT USE CATALOG ON *.* TO 'user1' WITH GRANT OPTION;

Grant specific catalog privileges:

GRANT SELECT, USE CATALOG ON CATALOG odps_prod TO 'user1' WITH GRANT OPTION;
REVOKE USE CATALOG ON CATALOG odps_prod FROM 'user1';

Additional information:

1. After successfully executing CREATE EXTERNAL CATALOG, the creator automatically obtains the USE CATALOG and SELECT privileges on that catalog.
2. Catalog privileges are independent of data access privileges. Taking HMS as an example, the catalog layer connects to the Hive Metastore to retrieve metadata, while the location layer accesses data files stored in HDFS. Authentication for both layers must be configured separately.

Privilege verification example:

GRANT SELECT, USE CATALOG ON CATALOG ca1 TO zhangsan WITH GRANT OPTION;
SHOW GRANTS FOR zhangsan;

Privilege query views:

• information_schema.USER_PRIVILEGES
• oceanbase.DBA_OB_USERS / CDB_OB_USERS

External tables

An external table stores its definition and access path in OceanBase Database, while the data remains in external storage systems such as object storage, HDFS, or MaxCompute. External tables are typically read-only and do not support constraints or indexes.

Compared with the Catalog mode, explicit external tables are suitable for the following scenarios:

• Accessing data in formats such as CSV, Parquet, or ORC by file path when the data is not registered with HMS.
• Temporarily exploring external files without needing to pre-build a Catalog.
• Mapping a single ODPS table (not an entire project) through the MaxCompute API.

OceanBase supports the following types of external tables:

Iceberg tables cannot be accessed using the CREATE EXTERNAL TABLE statement; instead, you should use an External Catalog. For more information, see Load an Iceberg table through a Catalog.

References

Access documents

• Load a Hive table through a Catalog
• Hive tables (HMS)
• Load an Iceberg table through a Catalog
• ODPS Catalog
• ODPS external tables
• File external tables
• URL external tables
• Overview of data lakes

Reference documents

• Overview of Catalogs (Reference)
• Create a Catalog
• View a Catalog
• Delete a Catalog
• CREATE EXTERNAL CATALOG
• SET CATALOG
• DROP CATALOG