You can execute the SYNC CATALOG statement to manually synchronize the database and table metadata in the internal catalog of OceanBase AI Database to Hive Metastore (HMS) when catalog synchronization is enabled. The synchronization supports three granularities: full synchronization, synchronization by database, and synchronization by table.
Note
The SYNC CATALOG statement is supported in OceanBase AI Database starting from V4.6.3. This statement is an extended syntax for OceanBase AI Database.
Overview
In the Unified Catalog system, automatic catalog data synchronization only takes effect on DDL operations performed after synchronization is enabled. Existing tables created before synchronization is enabled will not be automatically synchronized to HMS. Additionally, automatic synchronization may fail due to reasons such as network jitter. The SYNC CATALOG statement serves as a supplementary means to automatic synchronization, providing manual synchronization capabilities.
SYNC CATALOG only synchronizes metadata and the partition definitions and values of placeholder tables; it does not synchronize the actual data in the tables. For more information about automatic catalog data synchronization, see Automatic catalog data synchronization.
Scenarios
- Synchronize existing tables for compensation: Manually synchronize internally created tables that were created before synchronization was enabled to HMS without the need to drop and recreate them.
- Retry on failure: After automatic synchronization fails due to reasons such as network jitter, manually execute synchronization as a backup.
- Synchronize on demand: Supports synchronizing all databases and tables, all tables under a specified database, or only specified tables.
Syntax
SYNC CATALOG { *.* | db_name.* | db_name.table_name };
Parameters
Parameters |
Note |
|---|---|
*.* |
Synchronize all databases and tables that support synchronization under the internal catalog. |
db_name |
The name of the database. db_name.* indicates to synchronize all internal tables that support synchronization in the specified database. |
table_name |
Table name. db_name.table_name indicates that only the specified internal table is synchronized. |
Synchronization scope
SYNC CATALOG only synchronizes user databases and user tables. System databases, hidden databases, index tables, auxiliary tables, and other objects are not involved in synchronization. The behavior of different objects in explicit specification and batch synchronization is as follows:
Object |
Explicitly specify ( db_name.table_name) |
Batch Synchronization ( *.*ordb_name.*) |
|---|---|---|
| User Database | Supported | synchronize |
| Normal User Table | Supported | synchronize |
| system database | Unsupported | Skip |
| Hide Database | Unsupported | Skip |
| index table | Unsupported | Skip |
| Auxiliary table | Unsupported | Skip |
| Views and other non-normal tables | Unsupported | Skip |
| Non-existent database or table | Object not found | N/A |
Processing rules on HMS
Processing rules for databases
Remote status |
Handling behavior |
|---|---|
| The database with the same name does not exist. | Automatically Created |
| A database with the same name already exists. | Reuse the existing database. |
| Other remote tables exist in the database. | Retain, do not delete |
| The database is inaccessible. | Synchronization failed |
| Concurrent conflicts occurred during database creation. | Confirm again and proceed if present |
Processing rules for tables
Status of tables in HMS with the same name |
Handling behavior |
|---|---|
| None | Create an OceanBase AI Database placeholder table |
| The placeholder table is corresponding to the current local table. | Update with the Latest Local Schema |
| A regular Hive/Spark table | Object with the same name already exists. Object will not be overwritten. |
| It is a placeholder table for other tenants. | Ownership conflict reported, will not overwrite |
| It is a placeholder table for other local tables in this tenant. | Ownership conflict reported, will not overwrite |
| Incomplete placeholder table tags | Illegal placeholder table, will not overwrite |
| Undetermined Ownership | Handle conflicts and do not overwrite. |
The ownership of a placeholder table is determined based on the following attributes:
Attribute |
Verification requirements |
|---|---|
ob.type |
Must be placeholder. |
ob.tenant_id |
Must match the current tenant. |
ob.table_id |
Must be consistent with the current local table. |
Note
In the current version, the ownership verification of placeholder tables is primarily based on ob.type. In later versions, this verification will be further strengthened.
Processing rules for partitions
Partitions are only processed after confirming that the remote table belongs to the current local partitioned table. The processing rules are as follows:
Partitioning scenarios |
Handling behavior |
|---|---|
| Local but absent on HMS | Add partition |
| Both local and HMS copies exist and are consistent | Not processed |
| Both local and HMS copies exist but have different metadata | Update partition |
| Additional partitions exist on HMS that are not managed by OceanBase AI Database | Retain |
| The partition belongs to another tenant or another table | Conflict reported, no modification |
Deletion rules
SYNC CATALOG does not delete additional objects on HMS. The specific rules are as follows:
Synchronization scope |
Delete extra objects on HMS |
|---|---|
SYNC CATALOG *.* |
Do not delete additional databases and tables on HMS. |
SYNC CATALOG db_name.* |
Do not drop additional tables on HMS. |
SYNC CATALOG db_name.table_name |
Do not delete objects on HMS that are not managed by this table. |
| Reconciliation of placeholder table partitions | Delete a local placeholder table partition that no longer exists and belongs to the current table. |
Notice
SYNC CATALOG *.* and SYNC CATALOG db_name.* are non-atomic best-effort operations. If execution fails, some objects may have been synchronized successfully. You can re-execute after troubleshooting.
Failure and retry
Failure type |
Solution |
|---|---|
| Network connection failed | Internal retry limit |
| An exception occurred during Thrift communication. | Internal limited retries and switching of HMS URIs. |
| HMS is temporarily unavailable. | Return failure after retries are exhausted. |
| Insufficient permissions | No retry, fail directly. |
| Conflict on Objects with the Same Name | No retry, fail directly. |
| The SQL statement failed to execute. | Manually retry after troubleshooting the issue. |
Limitations and considerations
- Only synchronization from an internal catalog to an HMS catalog is supported.
- Before execution,
catalog_sync_targetmust be configured and point to a valid HMS catalog. - Renaming databases or tables is not supported during synchronization.
- Only user databases and user tables are synchronized. System databases, hidden databases, index tables, and auxiliary tables are not involved in synchronization. Errors are reported for explicitly specified unsupported objects, and they are automatically skipped during batch synchronization.
SYNC CATALOGonly synchronizes metadata, as well as the partition definitions and partition values of placeholder tables, not the actual data in the tables.
Prerequisites
An HMS-type external catalog has been created using
CREATE EXTERNAL CATALOG, and the tenant parametercatalog_sync_targethas been set to point to this catalog, enabling catalog synchronization. For detailed operations, see Automatic catalog data synchronization.The HMS catalog pointed to by
catalog_sync_targetis valid and accessible.
Notice
When executing SYNC CATALOG, the target HMS catalog must match the catalog specified by the catalog_sync_target parameter.
Usage examples
Create a synchronization target catalog.
For more syntax details, see CREATE EXTERNAL CATALOG.
CREATE EXTERNAL CATALOG hms_sync_catalog PROPERTIES ( TYPE = 'hms', URI = 'thrift://<hms-host>:<port>' );Enable synchronization.
Set the synchronization target Catalog. After enabling, DDL operations on the Internal catalog will be automatically synchronized to HMS.
ALTER SYSTEM SET catalog_sync_target = 'hms_sync_catalog';Synchronize all supported databases and tables under the Internal catalog.
SYNC CATALOG *.*;Synchronize all internal tables under the specified database
db1.SYNC CATALOG db1.*;Synchronize the specified table
t1under thedb1database.SYNC CATALOG db1.t1;
FAQ
Q: Does
SYNC CATALOGsynchronize the actual data in tables?No.
SYNC CATALOGonly synchronizes metadata, including partition definitions and partition values of placeholder tables, not the actual data in tables.Q: If
SYNC CATALOG *.*fails and is re-executed, will objects that have already been successfully synchronized be recreated?No. When re-synchronizing objects that have already been successfully synchronized, the remote conflict resolution rules are applied: the placeholder table corresponding to the current local table is updated with the latest local schema and is not recreated.
Q: Why does an error occur when explicitly specifying a table for synchronization?
Possible reasons include specifying unsupported objects such as system databases, hidden databases, index tables, auxiliary tables, or views; or the specified database or table does not exist. You can use batch synchronization to automatically skip unsupported objects.