manage, AnalyticDB for MySQL, catalog - Realtime Compute for Apache Flink

[INFO] Doc info: docId=7455002, topicId=2257227, spaceId=133 [INFO] Document content read: nodeId=3873491, 13790 chars Manage catalogs

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.
Catalogs do not support SSL connections. To connect to AnalyticDB for MySQL over SSL, use a temporary table instead.

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

警告

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.