Manage AnalyticDB for MySQL catalogs - Realtime Compute for Apache Flink

An AnalyticDB for MySQL catalog maps your existing AnalyticDB for MySQL databases directly into Flink SQL, so you can reference tables without writing DDL statements manually. Once created, you can use catalog tables as dimension tables or result tables in your Flink SQL deployments.

Prerequisites

Before you begin, make sure you have:

Realtime Compute for Apache Flink running Ververica Runtime (VVR) 6.0.2 or later
The hostname (or IP address), port, username, and password of your AnalyticDB for MySQL database

Limitations

Catalog DDL statements cannot be modified after creation.
Catalog tables are read-only: you cannot create, modify, or drop databases or tables through a catalog.
Catalog tables can only be used as dimension tables and result tables, not source tables.

Create a catalog

In the Scripts tab of the SQL Editor, enter and run the following statement:

CREATE CATALOG <catalogName> WITH (
  'type' = 'adb3.0',
  'hostName' = '<hostname>',
  'port' = '<port>',
  'userName' = '<username>',
  'password' = '<password>',
  'defaultDatabase' = '<dbname>'
);

Parameter	Required	Description
`catalogName`	Yes	Name of the catalog.
`type`	Yes	Catalog type. Set to `adb3.0`.
`hostName`	Yes	IP address or hostname of the AnalyticDB for MySQL database.
`port`	No	Port used to connect to the database. Default: `3306`.
`userName`	Yes	Username for the database.
`password`	Yes	Password for the database.
`defaultDatabase`	Yes	Name of the default database.

Select the statement, then click Run on the left side of the editor.

View catalog metadata

Log on to the Realtime Compute for Apache Flink console.
Find your workspace and click Console in the Actions column.
In the left-side navigation pane, click Catalogs.
On the Catalog List page, find your catalog and check the Catalog Name and Type columns. To browse the databases and tables inside the catalog, click View in the Actions column.

Use catalog tables

Catalog tables use a three-part identifier that maps to AnalyticDB for MySQL namespaces:

Flink namespace	AnalyticDB for MySQL namespace
Catalog name	(Flink only)
Database name	Database name
Table name	Table name

Reference a catalog table using the format `<catalog>`.`<database>`.`<table>`.

Use as a dimension table

Reference the catalog table in a temporal join:

INSERT INTO <other_sink_table>
SELECT ...
FROM <other_source_table> AS e
JOIN `<adb_mysql_catalog>`.`<db_name>`.`<table_name>` FOR SYSTEM_TIME AS OF e.proctime AS w
ON e.id = w.id;

Use as a result table

Write query results directly into a catalog table:

INSERT INTO `<adb_mysql_catalog>`.`<db_name>`.`<table_name>`
SELECT ...
FROM <other_source_table>;

To pass additional connector options, use SQL hints instead of modifying the DDL. For example, to enable replaceMode for an AnalyticDB for MySQL V3.0 result table:

INSERT INTO `<adb_mysql_catalog>`.`<db_name>`.`<table_name>` /*+ OPTIONS('replaceMode'='true') */
SELECT ...
FROM <other_source_table>;

Drop a catalog

Warning

Dropping a catalog does not affect running deployments. However, any drafts that reference tables in this catalog will fail to find those tables if the drafts are published or their deployments are restarted.

Drop a catalog from the UI (recommended) or with an SQL statement.

Drop from the UI

Log on to the Realtime Compute for Apache Flink console.
On the Fully Managed Flink tab, find your workspace and click Console in the Actions column.
In the left-side navigation pane, click Catalogs.
On the Catalog List page, find the catalog and click Delete in the Actions column.
In the confirmation dialog, click Delete.
Verify that the catalog no longer appears in the Catalogs pane on the left side of the page.

Drop with an SQL statement

In the Scripts tab of the SQL Editor, enter the following statement:
```
DROP CATALOG <catalogName>;
```
Right-click the statement and select Run.
Verify that the catalog no longer appears in the Catalogs pane on the left side of the page.