View a catalog

After you create a catalog, you can view the information about the catalog by using SQL statements.

View the statement used to create the 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 of the current tenant

You can run the SHOW CATALOGS; statement to view the catalogs of the current tenant.

Here is an example:

obclient> SHOW CATALOGS;

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 Database supports querying historical data of an Iceberg table (Time Travel), which means that you can query the state of the table at a specific snapshot in the past, even if the data has been modified or deleted later.

The syntax of a 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

Parameter description:

• select_expr_list: specifies the list of columns or expressions to be displayed in the query result. For more information about the SELECT statement, see SELECT.

• iceberg_table_name: specifies the name of the Iceberg table.

• branch_name: specifies the name of the branch (string type). The query will Time Travel to the head snapshot of this branch.

• tag_name: specifies the name of the tag (string type). The query will Time Travel to the snapshot corresponding to this tag.

• snapshot_id: specifies the snapshot ID (integer type). The query will Time Travel to the snapshot corresponding to this snapshot ID.

• timestamp: specifies the timestamp (timestamp type). The query will return the latest snapshot before or equal to this timestamp.

  Note

  If the table has not been created at the specified timestamp (timestamp), an error is returned: Table snapshot does not exist.

Here is an example:

• Time Travel to the head snapshot of the audit-branch branch.

  obclient> SELECT * FROM prod.db.table VERSION AS OF 'audit-branch';
  

• Time Travel to the snapshot corresponding to the historical-snapshot tag.

  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 before 01:21:00 on October 26, 1986.

  obclient> SELECT * FROM prod.db.table TIMESTAMP AS OF '1986-10-26 01:21:00';
  

Switch catalogs

You can run the SET CATALOG catalog_name; statement to switch to a catalog.

You can also run the USE internal.test; or USE odps_catalog.default statement to switch to a catalog and a database at the same time.

Here is an example:

• Switch to the internal catalog.

  obclient> SET CATALOG internal;
  

• Switch to the external catalog.

  obclient> SET CATALOG external_catalog_name;
  

References

• Create an external catalog

• SHOW