Overview
OceanBase Database supports access to external data through Catalogs and external tables. Both provide external-data access, but they differ in how metadata is managed and in their applicable scenarios:
- Catalog: Connects to an external metadata service, such as Hive Metastore or a MaxCompute project, and automatically synchronizes database and table schemas. It is suitable for structured data sources that already have a metadata service.
- External table: Defines a table schema and access path manually in OceanBase Database. It is suitable for file paths, temporary data exploration, and single-table mappings.
OceanBase Database supports ODPS Catalog starting from V4.3.5 BP2 and HMS Catalog starting from V4.4.1. Through Catalogs, you can query Hive and Iceberg tables managed by HMS and tables in MaxCompute (ODPS). You can also perform federated analysis by joining them with internal tables in the internal Catalog.
Note
Catalog capabilities are currently supported only in MySQL-compatible mode of OceanBase Database.
Deployment and environment dependencies
Item |
Description |
|---|---|
| Cross-data-center deployment | If HMS and OBServer are deployed in different data centers, metadata retrieval latency can reach seconds and increase query latency. We recommend that you deploy HMS and OBServer in the same low-latency network. |
| HDFS storage | If the underlying storage is HDFS, deploy a Java runtime environment (JDK 8 or 11) on each OBServer node and enable Java support by setting ob_enable_java_env = true. For more information, see Deploy the Java SDK environment for OceanBase Database. |
Relationship between Catalogs and external tables
When OceanBase Database accesses external data, the following layers are involved:
Layer |
Description |
|---|---|
| External storage | Physical location of data files, such as HDFS, OSS, or S3 |
| Metadata | Structural information such as databases, tables, and partitions, maintained by HMS, ODPS, or an OceanBase external-table definition |
| Access entry | A Catalog or external table that makes external data available to the SQL query system |
OceanBase Database provides two external-data access modes:
Mode |
Metadata source |
Applicable scenarios |
Typical usage |
|---|---|---|---|
| Catalog mode | Automatically synchronized from an external metadata service | Structured data sources that already have a metadata service, such as a Hive data warehouse or ODPS project | SET CATALOG my_hms;SELECT * FROM db1.sales; |
| Explicit external table | Manually defined in OceanBase Database | Non-Catalog scenarios such as file-path access, temporary data exploration, or a single-table API mapping | CREATE EXTERNAL TABLE t1 (...) LOCATION = 'oss://...'; |
Selection guidelines:
- Use a Catalog when the data source is already registered in HMS or MaxCompute. OceanBase Database reads metadata through the Catalog and then accesses the underlying storage or remote tables.
- Use an explicit external table to access CSV, Parquet, or ORC files by path, or to map one ODPS table through the MaxCompute API.
- Access Iceberg tables through an External Catalog, such as HMS Catalog or REST Catalog. Iceberg tables are not accessed by using
CREATE EXTERNAL TABLE. For more information, see Load an Iceberg table through a Catalog.
Note
- A Catalog is a metadata abstraction of the underlying data. It is not a storage location.
- HMS Catalog connects only to Hive Metastore and cannot point directly to an arbitrary HDFS path. To access files that are not registered in HMS, use
CREATE EXTERNAL TABLE ... LOCATION.
Catalog types
A Catalog is the top-level namespace for database objects and is used to organize and isolate metadata from different data sources. OceanBase Database supports internal and external Catalogs:
Catalog type |
Data source |
Supported table type |
Typical scenario |
|---|---|---|---|
Internal Catalog (internal) |
Local OceanBase storage | OceanBase tables | Core business data in a tenant |
| ODPS Catalog | Alibaba Cloud MaxCompute | ODPS tables | Offline data-warehouse analytics |
| HMS Catalog | Hive Metastore | Hive and Iceberg tables | Hadoop ecosystem integration |
Internal Catalog
Each tenant has exactly one internal Catalog named internal. It contains the objects created in the tenant by DDL statements such as CREATE DATABASE and CREATE TABLE. The internal Catalog is logically isolated from external Catalogs and cannot be created, dropped, or renamed.
- By default, you can access it directly by running
USE database;without explicitly specifying the Catalog. - It supports complete DDL and DML operations, and stores data 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;
Note
The internal Catalog is named internal. You can usually omit this name.
External Catalog
Create an external Catalog by using CREATE EXTERNAL CATALOG. The current SQL reference formally documents the syntax for ODPS Catalog. For the parameters of HMS Catalog (V4.4.1 and later) and the REST and FILESYSTEM Catalogs used with Iceberg, see the following integration topics. Before use, verify whether the SQL reference has been updated for your cluster version.
Create and access a Catalog
Create an External Catalog
You do not need to create the internal Catalog manually. For the syntax used to create an external Catalog, see CREATE EXTERNAL CATALOG. For parameters specific to each Catalog type, see the preceding integration topics.
View Catalogs
SHOW CATALOGS;
Switch Catalogs and run cross-Catalog queries
Follow these rules when referencing a table:
- Before switching the session context, use the three-part identifier
catalog_name.database_name.table_name. - After running
SET CATALOGandUSE, you can use an abbreviated table name.
Method 1: Use a three-part identifier without switching the context
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: Switch the context and use abbreviated table names
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 run SET CATALOG catalog_name; separately or explicitly specify the Catalog in a query:
SELECT * FROM odps_catalog.database_name.table_name;
Note
- If you run
SELECT * FROM hive_table;without first runningSET CATALOGandUSE, the system searches for the table in the current Catalog, such asinternal, and may return an error. - Switching the context affects only subsequent statements. It does not affect the connection or transaction state.
For syntax information, see SET CATALOG and View a Catalog.
Catalog support scope
This section describes the capability boundaries of each external Catalog. For information about creation, authentication, and Location configuration, see the corresponding integration topic.
HMS Catalog
Item |
Description |
|---|---|
| Access mode | Read-only. DML and DDL operations such as INSERT, UPDATE, and DROP TABLE are not supported. |
| Hive tables | Supports ORC, Parquet, TextFile, and CSV. For complex data types, only ARRAY data in Parquet format is currently supported. |
| Iceberg tables | Supports V1 and V2. Parquet is recommended. Features such as Schema Evolution and Partition Transform are supported. |
| Hive versions | Supports Hive 1.2.x, 2.3.x, 3.1.x, and 4.x. Iceberg tables can be accessed after their metadata is registered in HMS. |
For information about the ARRAY type, see Overview of array element types. For information about HMS and HDFS authentication, see Load Hive tables through a Catalog.
ODPS Catalog
Item |
Description |
|---|---|
| Supported operations | Analytical queries that use SELECT, JOIN, or GROUP BY |
| Unsupported operations | Writes or DDL operations such as INSERT, UPDATE, and DROP TABLE |
| Query optimization | Supports partition-pruning and column-pruning pushdown |
For detailed parameters and API modes, see ODPS Catalog.
Catalog privilege management
Catalog privileges are divided into two levels:
Level |
Scope |
|---|---|
| User Level (global) | All Catalogs, corresponding to *.* in MySQL-compatible mode |
| Catalog Level (object) | A specified Catalog |
Privileges in MySQL-compatible mode
Privilege |
Scope |
Purpose |
|---|---|---|
CREATE CATALOG |
User Level | Run CREATE or DROP EXTERNAL CATALOG |
USE CATALOG |
User Level or Catalog Level | Run SET CATALOG or SHOW CATALOGS, or query catalog.db.table |
Grant global privileges:
GRANT CREATE CATALOG ON *.* TO 'user1';
GRANT USE CATALOG ON *.* TO 'user1' WITH GRANT OPTION;
Grant privileges on a specific Catalog:
GRANT SELECT, USE CATALOG ON CATALOG odps_prod TO 'user1' WITH GRANT OPTION;
REVOKE USE CATALOG ON CATALOG odps_prod FROM 'user1';
Additional information:
- After
CREATE EXTERNAL CATALOGsucceeds, the creator automatically obtains theUSE CATALOGandSELECTprivileges on that Catalog. - Catalog privileges and data-access permissions are independent. For HMS, the Catalog layer connects to Hive Metastore to retrieve metadata, while the Location layer accesses data files in HDFS or other storage. Configure authentication separately for the two layers.
Privilege verification example:
GRANT SELECT, USE CATALOG ON CATALOG ca1 TO zhangsan WITH GRANT OPTION;
SHOW GRANTS FOR zhangsan;
Privilege views:
External tables
An external table stores its table definition and access path in OceanBase Database, while its data remains in external storage such as object storage, HDFS, or MaxCompute. External tables are generally read-only and do not support constraints or indexes.
Compared with Catalog mode, explicit external tables are suitable for the following scenarios:
- Access CSV, Parquet, or ORC data by file path when it is not registered in HMS.
- Explore external files temporarily without creating a Catalog first.
- Map one ODPS table, rather than an entire project, through the MaxCompute API.
OceanBase Database supports the following external-table types:
Type |
Description |
Documentation |
|---|---|---|
| File external table | Accesses CSV, Parquet, or ORC files at paths in OSS, S3, HDFS, or similar storage | File external tables |
| URL external table | Uses FILES() to read external files directly without creating an external table first |
URL external tables |
| ODPS external table | Maps one ODPS table through the MaxCompute API | ODPS external tables |
Iceberg tables are not accessed by using CREATE EXTERNAL TABLE. Use an External Catalog instead. For more information, see Load an Iceberg table through a Catalog.
References
Integration topics
- Access Hive tables through HMS Catalog
- Load an Iceberg table through a Catalog
- ODPS Catalog
- ODPS external tables
- File external tables
- URL external tables
- Data lake overview