After you create a catalog, you can view the information about the catalog by using SQL statements.
View the statement used to create a catalog
You can run the SHOW CREATE CATALOG external_catalog_name; statement to view the statement used to create the catalog.
Here is an example:
obclient> SHOW CREATE CATALOG test_odps_catalog;
View the catalogs in the current tenant
You can run the SHOW CATALOGS; statement to view the catalogs in the current tenant.
Here is an example:
obclient> SHOW CATALOGS;
The query result is as follows:
+-------------------+
| Catalog |
+-------------------+
| internal |
| test_odps_catalog |
+-------------------+
2 rows in set
View the current catalog environment
You can use the CATALOG() function to view the current catalog environment.
Here is an example:
obclient> SELECT CATALOG();
The query result is as follows:
+-----------+
| CATALOG() |
+-----------+
| internal |
+-----------+
1 row in set
View data in a catalog
In a catalog, you can execute any query operation on tables in the database, and you can also perform cross-catalog queries.
Here is an example:
obclient> SELECT * FROM internal.test.t1, odps_catalog.default.t1;
Query historical data of an Iceberg table
OceanBase AI Database supports querying historical data of an Iceberg table (Time Travel), which means you can query the state of a table at a specific snapshot in the past. Even if this data is modified or deleted later, you can still access its content from that time.
The syntax for the Time Travel statement is as follows:
SELECT select_expr_list
FROM iceberg_table_name {VERSION AS OF 'version_identifier'
| TIMESTAMP AS OF 'timestamp'};
version_identifier:
branch_name
| tag_name
| snapshot_id
The parameters are described as follows:
select_expr_list: Specifies the list of columns or expressions to display in the query results. For more information about theSELECTstatement, see SELECT.iceberg_table_name: Specifies the name of the Iceberg table.branch_name: Specifies the branch name (string type). The query will Time Travel to the head snapshot of that branch.tag_name: Specifies the tag name (string type). The query will Time Travel to the snapshot corresponding to the tag.snapshot_id: Specifies the snapshot ID (integer type). The query will Time Travel to the snapshot corresponding to the snapshot ID.timestamp: Specifies the timestamp (timestamp type). The query will return the latest snapshot that is earlier than or equal to this point in time.Note
If the table has not been created by the specified point in time (
timestamp), the query will report an error with the message: Table snapshot does not exist.
Examples:
Time Travel to the head snapshot of the branch named
audit-branch.obclient> SELECT * FROM prod.db.table VERSION AS OF 'audit-branch';Time Travel to the snapshot corresponding to the tag named
historical-snapshot.obclient> SELECT * FROM prod.db.table VERSION AS OF 'historical-snapshot';Time Travel to the snapshot with ID 10963874102873.
obclient> SELECT * FROM prod.db.table VERSION AS OF 10963874102873;Time Travel to the latest snapshot on October 26, 1986, at 01:21:00.
obclient> SELECT * FROM prod.db.table TIMESTAMP AS OF '1986-10-26 01:21:00';
Switch catalogs
You can use the SET CATALOG catalog_name; statement to switch catalogs.
You can also use USE internal.test; or USE odps_catalog.default to switch both the catalog and the database in one operation.
Examples:
Switch to the system internal catalog.
obclient> SET CATALOG internal;Switch to an external catalog.
obclient> SET CATALOG external_catalog_name;