Realtime Compute for Apache Flink:Manage AnalyticDB for MySQL (ADB) catalogs

Limitations

• Only Realtime Compute for Apache Flink running VVR 6.0.2 or later supports AnalyticDB for MySQL catalogs.

• A catalog cannot be modified after it is created.

• You can only query data tables. You cannot create, modify, or delete databases or tables.

• Tables can be used only as dimension tables and result tables, not as source tables.

• Catalogs do not support SSL connections. To use an SSL connection, define a temporary table to access AnalyticDB for MySQL.

Create an AnalyticDB for MySQL catalog

1. To create an AnalyticDB for MySQL catalog, enter the statement in the Scripts editor.

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

   Parameter	Type	Description	Required
   catalogName	String	The name of the AnalyticDB for MySQL catalog.	Yes
   type	String	The type of the catalog. The value is fixed at adb3.0.	Yes
   hostName	String	The IP address or hostname of the AnalyticDB for MySQL instance.	Yes
   port	Integer	The port number of the AnalyticDB for MySQL instance. Default: 3306.	No
   userName	String	The username for the AnalyticDB for MySQL instance.	Yes
   password	String	The password for the AnalyticDB for MySQL instance.	Yes
   defaultDatabase	String	The name of the default AnalyticDB for MySQL database.	Yes

2. Select the statement and click Run on the left side of the code.

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

View an AnalyticDB for MySQL catalog

Once an AnalyticDB for MySQL catalog is configured, follow these steps to view the AnalyticDB for MySQL metadata:

1. Go to the Catalogs page.

   1. Log on to the Realtime Compute for Apache Flink console.

   2. In the Actions column of the target workspace, click Console.

   3. In the left-side navigation pane, click Catalogs.

2. On the Catalog List page, view the Name and Type.

   Note

   To view the databases and tables in a catalog, click View.

Use an AnalyticDB for MySQL catalog

• Use a table from an AnalyticDB for MySQL catalog as a dimension table

  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 a table from an AnalyticDB for MySQL catalog as a result table

  INSERT INTO `${adb_mysql_catalog}`.`${db_name}`.`${table_name}`
  SELECT ...
  FROM ${other_source_table}

  If you need to specify additional WITH parameters when you use a table from an AnalyticDB for MySQL catalog, use a SQL hint to add the parameters. For more information about the parameters, see WITH parameters. The following example shows how to add the replaceMode parameter to an AnalyticDB for MySQL 3.0 result table.

  INSERT INTO `${adb_mysql_catalog}`.`${db_name}`.`${table_name}` /*+ OPTIONS('replaceMode'='true') */
  SELECT ...
  FROM ${other_source_table}

Delete an AnalyticDB for MySQL catalog

You can delete an AnalyticDB for MySQL catalog using the UI or an SQL statement. We recommend deleting the AnalyticDB for MySQL catalog using the UI.

UI

1. Go to the Catalogs page.

   1. Log on to the Realtime Compute for Apache Flink console.

   2. In the Actions column of the target workspace, click Console.

   3. In the left-side navigation pane, click Catalogs.

2. On the Catalog List page, find the catalog that you want to delete and click Delete in the Actions column.

3. In the confirmation dialog box, click Delete.

4. In the Catalogs pane on the left, verify that the catalog has been deleted.

SQL statement

1. In the Scripts editor, enter the following statement.

   DROP CATALOG <catalogName>;

   Replace <catalogName> with the name of the AnalyticDB for MySQL catalog that you want to delete.

2. Select the statement, right-click, and then choose Run.

3. In the Catalogs pane on the left, verify that the catalog is deleted.