Application scenarios
- Typical scenarios
- Real-time reporting: Combine real-time transaction data from OceanBase with offline wide tables.
- Cross-system joint analysis: User behavior logs (Hive/Iceberg) + user profile data (OceanBase).
- Core benefits
- No data migration required, avoiding redundant storage and synchronization latency.
- Unified SQL engine, reducing development complexity.
- On-demand reading, saving computing and I/O costs.
Overview
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.
The core principle is as follows:
Catalog mechanism
- A Catalog (data catalog) is a logical container for metadata. Each Catalog corresponds to an external data source (such as a MaxCompute project or Hive Metastore).
- OceanBase Database has a built-in internal Catalog to manage local tables.
Federated query
- The query engine recognizes cross-Catalog table references (e.g.,
hms_prod.project.table). - Metadata retrieval: Obtains the table schema from HMS via the Catalog.
- Data reading: Uses the corresponding access protocol and authentication method based on the underlying storage type indicated by the table's Location.
- For HDFS, Kerberos authentication is supported.
- For OSS or S3-compatible object storage, access is implemented through compatible file systems (such as S3A or OSS-HDFS), using AccessKey / SecretKey (AK/SK) for signature authentication.
Objectives of this tutorial
- Master the core capability of OceanBase Database to achieve ETL-free federated queries through Catalog in AP scenarios.
- Proficiently use HMS Catalog to access Hive / Iceberg data.
Environment requirements
- OceanBase Database version ≥ V4.4.1 (supports HMS Catalog)
- Network connectivity:
- HMS: The OceanBase node can access Hive Metastore and HDFS/S3/OSS.
- Permission preparation:
- Hive: HMS read permission + HDFS file read permission (configured via Location). For details, see Load Hive tables via Catalog.
Procedure to access Hive / Iceberg tables via HMS Catalog
Prerequisites
Before you begin, ensure the following environment is ready:
Condition |
Description |
|---|---|
| Hive Metastore (HMS) is running normally. | Provides metadata services. The URI format is as follows:thrift://host:port |
| Underlying storage accessible | Storage systems such as HDFS, OSS, and S3 are configured, and the OBServer node has read permissions. |
| Network Connectivity | The OBServer can access the HMS (default port 9083 or custom port) and underlying storage. |
| Java support is deployed and enabled in OceanBase Database (Optional) | If you need to access HDFS, you must deploy the OceanBase Database Java SDK environment in advance. |
Note
If Kerberos authentication is enabled for HMS, you must also provide the KERBEROS_PRINCIPAL and KEYTAB parameters.
Assume the data directory name in the HMS Catalog is hive_meta. Inside the hive_meta catalog, there is a database named test_database, which contains a table named test_table. The table data is as follows:
+------------+--------+---------+--------+------------+
| product_id | amount | country | region | event_date |
+------------+--------+---------+--------+------------+
| 1001 | 2500 | CN | East | 2023-10-01 |
| 1003 | 1800 | JP | Kanto | 2023-10-03 |
| 1002 | 3500 | US | West | 2023-10-02 |
+------------+--------+---------+--------+------------+
The table structure shown here in the HMS Catalog is for clearer verification results in subsequent steps. If you are unsure about the table structure in your actual application scenario, run SHOW CREATE TABLE ${catalog_name}.${hive_db_name}.${table_name}; to view the table creation statement.
Step 1: Create an HMS Catalog in OceanBase
-- Create External Catalog
CREATE EXTERNAL CATALOG hive_meta
PROPERTIES (
TYPE = 'HMS',
URI = 'thrift://xx.xx.xx.xx:9083' -- Please fill in the URI according to your actual configuration.
);
-- Verification of creation success
SHOW CATALOGS;
If Kerberos authentication is available for HMS, configure KERBEROS_PRINCIPAL and KEYTAB.
The display result is as follows:
+-----------+
| Catalog |
+-----------+
| hive_meta |
| internal |
+-----------+
Step 2: Prepare the real-time order table in the OceanBase internal catalog
This step is performed in the OceanBase internal Catalog.
Create a database and a table
-- Switch back to internal catalog (default)
SET CATALOG internal;
-- Create a new database (optional. You can also use an existing one).
CREATE DATABASE IF NOT EXISTS federated_demo;
USE federated_demo;
-- Create an internal table
CREATE TABLE test_table_internal (
product_id INT,
amount DOUBLE,
country VARCHAR(10),
region VARCHAR(20),
event_date DATE
);
Insert test data
INSERT INTO test_table_internal VALUES
(1004, 4200, 'DE', 'Berlin', '2023-10-04'),
(1005, 1900, 'FR', 'Paris', '2023-10-05'),
(1001, 3000, 'CN', 'South', '2023-10-06'); -- Has duplicate product_id with the Hive table
-- Verify
SELECT * FROM test_table_internal;
Execute the following SQL in OceanBase to verify the data:
-- Query an internal table
SELECT * FROM sales_data_internal;
-- Output:
+------------+--------+---------+--------+------------+
| product_id | amount | country | region | event_date |
+------------+--------+---------+--------+------------+
| 1004 | 4200 | DE | Berlin | 2023-10-04 |
| 1005 | 1900 | FR | Paris | 2023-10-05 |
| 1001 | 3000 | CN | South | 2023-10-06 |
+------------+--------+---------+--------+------------+
3 rows in set
-- Execute the following statement in OceanBase Database to query the tables under the current configured HMS Catalog (ensure normal access):
SELECT * FROM hive_meta.test_database.test_table;
-- Output:
+------------+--------+---------+--------+------------+
| product_id | amount | country | region | event_date |
+------------+--------+---------+--------+------------+
| 1001 | 2500 | CN | East | 2023-10-01 |
| 1003 | 1800 | JP | Kanto | 2023-10-03 |
| 1002 | 3500 | US | West | 2023-10-02 |
+------------+--------+---------+--------+------------+
3 rows in set
Step 3: Perform a federated query (joint internal + Hive)
Scenario 1: Consolidate all sales data
SELECT
product_id,
amount,
country,
region,
event_date,
'internal' AS data_source
FROM federated_demo.test_table_internal
UNION ALL
SELECT
product_id,
amount,
country,
region,
STR_TO_DATE(event_date, '%Y-%m-%d') AS event_date, -- The event_date in Hive is a string; convert it to DATE.
'hive' AS data_source
FROM hive_meta.test_database.test_table
ORDER BY event_date;
Note
The event_date in a Hive table is of the string type. Use STR_TO_DATE() to convert it to DATE to ensure type consistency.
+------------+--------+---------+--------+------------+-------------+
| product_id | amount | country | region | event_date | data_source |
+------------+--------+---------+--------+------------+-------------+
| 1001 | 2500 | CN | East | 2023-10-01 | hive |
| 1002 | 3500 | US | West | 2023-10-02 | hive |
| 1003 | 1800 | JP | Kanto | 2023-10-03 | hive |
| 1004 | 4200 | DE | Berlin | 2023-10-04 | internal |
| 1005 | 1900 | FR | Paris | 2023-10-05 | internal |
| 1001 | 3000 | CN | South | 2023-10-06 | internal |
+------------+--------+---------+--------+------------+-------------+
6 rows in set
Scenario 2: Calculate total sales by country (cross-source aggregation)
SELECT
country,
SUM(amount) AS total_sales,
COUNT(*) AS record_count
FROM (
SELECT product_id, amount, country, region, event_date
FROM federated_demo.test_table_internal
UNION ALL
SELECT product_id, amount, country, region, event_date
FROM hive_meta.test_database.test_table
) AS all_sales
GROUP BY country
ORDER BY total_sales DESC;
Expected output:
+---------+-------------+--------------+
| country | total_sales | record_count |
+---------+-------------+--------------+
| US | 3500 | 1 |
| CN | 5500 | 2 | -- 2500 (Hive) + 3000 (Internal)
| DE | 4200 | 1 |
| FR | 1900 | 1 |
| JP | 1800 | 1 |
+---------+-------------+--------------+
Scenario 3: Identify products that exist only in internal
SELECT i.product_id, i.country
FROM federated_demo.test_table_internal i
LEFT JOIN hive_meta.test_database.test_table h
ON i.product_id = h.product_id
WHERE h.product_id IS NULL;
Output:
+------------+---------+
| product_id | country |
+------------+---------+
| 1004 | DE |
| 1005 | FR |
+------------+---------+
Federated aggregation
-- Federated Aggregation
SELECT country, SUM(amount) AS total
FROM (
SELECT product_id, amount, country FROM federated_demo.test_table_internal
UNION ALL
SELECT product_id, amount, country FROM hive_meta.test_database.test_table
) t
GROUP BY country;
The expected output is:
+---------+-------+
| country | total |
+---------+-------+
| DE | 4200 |
| FR | 1900 |
| CN | 5500 |
| JP | 1800 |
| US | 3500 |
+---------+-------+
5 rows in set