In versions earlier than OceanBase Database V4.3.5 BP2, accessing external data sources required manually creating mapping tables by using CREATE EXTERNAL TABLE. When an external data source contains a large number of tables, users had to create mappings one by one, which lacked flexibility.
To simplify querying data stored in various external data sources, OceanBase Database introduced the Catalog feature, which adds a new data hierarchy to enable unified management of external data sources.
Catalog types
Catalogs are divided into the following two types:
Internal catalog
An internal catalog is used to manage data stored within OceanBase Database. Each tenant has exactly one internal catalog named
internal. All databases and objects within databases (such as tables) created before V4.3.5 BP2 can be considered as stored in theinternalcatalog. Internal catalogs cannot be created, dropped, or modified.External catalog
An external catalog is used to connect to external data sources and obtain metadata of external data. You can directly query external data through external catalogs without the need to import or migrate data.
Catalog advantages
Multi-level data management architecture
A Catalog layer is added on top of the original Database-Table hierarchy to form a three-level Catalog-Database-Table structure, which supports direct access to the directory tree of external data sources.
Automatic mapping and on-demand querying
You only need to use
CREATE EXTERNAL CATALOGto declare a connection to an external data source, and then you can pass through queries to external tables without manually creating mapping tables.Consistent query experience
The syntax for querying external data sources is the same as that for querying local tables, and cross-source join analysis is supported.
Catalog features
Support for multiple data sources
External data sources such as ODPS (MaxCompute) and HMS (Hive and Iceberg) are supported.
Permission isolation
Access permissions on external data sources are controlled at the catalog layer to ensure data security.
Performance optimization
Catalog-based query plan optimization reduces intermediate data transmission and improves cross-source query efficiency.
Scenarios
Joint analysis
This scenario applies when you need to perform joint analysis on data in OceanBase Database and external data sources.
Data lake and data warehouse integration
You can build a unified data lake architecture to directly query massive amounts of data stored in external systems such as ODPS and Hive.
ETL pipeline optimization
You can reduce data migration steps and access source system data directly through catalogs, which lowers the complexity of ETL development.
Limitations
In the current version, data in catalogs is read-only and cannot be modified. All modification statements on objects under catalogs are prohibited, such as DROP TABLE and INSERT INTO.