Realtime Compute for Apache Flink:Manage MySQL catalogs

Console (recommended)

1. Go to the Data Management page.

   1. Log on to the Realtime Compute for Apache Flink console. In the Actions column of the workspace that you want to manage, click Console.

   2. In the left-side navigation pane, click Data Management.

2. Click Create Catalog, select MySQL, and then click Next.

3. Configure the parameters.

   Important

   You cannot modify these configuration parameters after creation. To make changes, you must delete and recreate the catalog.

   Parameter	Description	Required
   catalogname	A name for the MySQL catalog.	Yes
   hostname	The IP address or hostname of the MySQL database. Note To connect across different VPCs or over the internet, you must establish network connectivity. For more information, see network connectivity.	Yes
   port	The port number of the MySQL database. Default: 3306.	No
   default-database	The name of the default MySQL database.	Yes
   username	The username for the MySQL database.	Yes
   password	The password for the MySQL database. To avoid exposing secrets in plaintext, we recommend using a variable. The example uses a variable named mysqlpw. For more information, see Create a variable.	Yes

4. Click OK.

   The created catalog appears in the Catalogs area on the left.

SQL command

1. Go to the Scripts page.

   1. Log on to the Realtime Compute for Apache Flink console. In the Actions column of the workspace that you want to manage, click Console.

   2. In the left-side navigation pane, click Development > Scripts.

2. Click , click New Script, enter the File Name and Storage Location, and then click Save.

3. Enter the following code.

   CREATE CATALOG YourCatalogName WITH(
     'type' = 'mysql',
     'hostname' = 'rm-bp1gcn0q0j0******.mysql.rds.aliyuncs.com',
     'port' = '3306',
     'username' = 'usertest',
     'password' = '${secret_values.mysqlpw}',
     'default-database' = 'flinktest',
     'catalog.table.metadata-columns'='table_name'
   );

   Parameter	Description	Required
   YourCatalogName	A name for the MySQL catalog.	Yes
   type	The catalog type. Set to mysql.	Yes
   hostname	The IP address or hostname of the MySQL database. Note To connect across different VPCs or over the internet, you must establish network connectivity. For more information, see network connectivity.	Yes
   port	The port number of the MySQL database. Default: 3306.	No
   default-database	The name of the default MySQL database.	Yes
   username	The username for the MySQL database.	Yes
   password	The password for the MySQL database. To avoid exposing secrets in plaintext, we recommend using a variable. The example uses a variable named mysqlpw. For more information, see Create a variable.	Yes
   property-version	The catalog property schema version. Set to 0 (default) or 1 (recommended). Different versions may support different properties and default values. See the property descriptions for details. Note Supported only in VVR 8.0.6 and later. In VVR 11.1 and later, the default value is 1.	No
   catalog.table.metadata-columns	Specifies the metadata columns of a MySQL CDC source table to add to the table schema when you query the table. By default, no metadata columns are added. Separate multiple metadata columns with semicolons (;), for example: op_ts;table_name;database_name. Note Supported only in VVR 6.0.5 and later. Setting this property adds the specified metadata columns to the schema. Because these columns are specific to MySQL CDC source tables, you can only use tables from this catalog as source tables, not as sink or dimension tables.	No
   catalog.table.treat-tinyint1-as-boolean	When fetching a table schema, specifies whether to map MySQL TinyInt(1) and Boolean to Flink Boolean. Valid values: true: Map to Boolean. false: Map to TINYINT. Default value: If property-version is 0, the default value is true. If property-version is 1, the default value is false. Note Supported only in VVR 8.0.4 and later. We do not recommend using TinyInt(1) in MySQL to store values other than 0 and 1. Choose an appropriate type mapping. For more information, see Type mapping.	No

4. Select the CREATE CATALOG statement, then click Run next to the line number on the left.

   A The following statement has been executed successfully! message indicates that the catalog was created.

   The SQL statement in the editor, CREATE CATALOG myCatalog, has configuration parameters that include type=mysql, hostname=rm-bp1gcn0q0j0**.mysql.rds.aliyuncs.com, port=3306, username=usertest, password=${secret_values.mysqlpw} (a reference to a variable), default-database=flinktest, and catalog.table.metadata-columns=table_name.

Console (recommended)

On the Data Management page, you can view the Name and Type of the created catalogs in the Catalog List.

• View: In the Actions column for the catalog, click View to see the databases and tables in the catalog.

  Table schema details do not display field comments.

• Delete: In the Actions column for the catalog, click Delete.

  This operation deletes only the catalog, not the underlying tables in the associated service. Active deployments that use tables from the catalog are unaffected. However, redeploying or restarting these deployments will cause an error because the tables will no longer be found. Proceed with caution.

SQL command

1. In the editor on the Scripts page, enter the following commands.

   -- View the table schema in Flink. Field comments are not displayed.
   DESCRIBE `<catalogname>`.`<dbname>`.`<tablename>`;
   -- Delete the catalog.
   DROP CATALOG `<catalogname>`;

   Note

   This operation deletes only the catalog and does not delete the underlying tables in the associated service. Running deployments that use tables from the catalog are not affected. However, if you redeploy or restart a deployment, an error is reported because the table cannot be found. Proceed with caution.

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

   Running the DESCRIBE statement returns the schema of a table, including fields such as orderkey, custkey, order_status, and total_price, along with their data types and properties.