Realtime Compute for Apache Flink:Manage MySQL catalogs

UI (recommended)

1. Go to the Catalogs page.

   1. Log on to the Realtime Compute for Apache Flink console. Find the workspace that you want to manage and click Console in the Actions column.

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

2. On the Catalog List page, click Create Catalog. On the Built-in Catalog tab of the Create Catalog dialog box, click MySQL and click Next.

3. In the Configure Catalog step, configure the parameters.

   Important

   After you create a catalog, the following parameter configurations cannot be modified. To modify the catalog, you must drop it and create another one.

   

   Parameter	Description	Required
   catalogname	The name of the custom MySQL catalog.	Yes
   hostname	The IP address or hostname that is used to access the MySQL database. Note If the MySQL instance does not reside in the same VPC as Realtime Compute for Apache Flink or you want to access the MySQL instance and Realtime Compute for Apache Flink over the Internet, you must establish network connections. For more information, see FAQ about network connectivity.	Yes
   port	The port number of the MySQL database. Default value: 3306.	No
   default-database	The name of the default MySQL database.	Yes
   username	The username that is used to access the MySQL database.	Yes
   password	The password that is used to access the MySQL database. To prevent risks caused by a plaintext AccessKey pair, we recommend that you use a variable to specify this parameter. In the sample figure, a variable named mysqlpw is used. For more information, see Create a variable.	Yes

4. Click Confirm.

   In the Catalogs pane on the left side of the Catalog List page, view the catalog that you created.

SQL

1. Go to the Scripts page.

   1. Log on to the Realtime Compute for Apache Flink console. Find the workspace that you want to manage and click Console in the Actions column.

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

2. On the page that appears, click to create a script. Then, 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	The name of the custom MySQL catalog. Important You must remove the angle brackets <>. Otherwise, an error is returned during the syntax check.	Yes
   type	The type of the catalog. Set the value to mysql.	Yes
   hostname	The IP address or hostname that is used to access the MySQL database. Note If the MySQL instance does not reside in the same VPC as Realtime Compute for Apache Flink or you want to access the MySQL instance and Realtime Compute for Apache Flink over the Internet, you must establish network connections. For more information, see FAQ about network connectivity.	Yes
   port	The port number of the MySQL database. Default value: 3306.	No
   default-database	The name of the default MySQL database.	Yes
   username	The username that is used to access the MySQL database.	Yes
   password	The password that is used to access the MySQL database. To prevent risks caused by a plaintext AccessKey pair, we recommend that you use a variable to specify this parameter. In the sample figure, a variable named mysqlpw is used. For more information, see Create a variable.	Yes
   property-version	The version of the catalog parameter. Valid values: 0 and 1. The value 0 is the default value and the value 1 is the recommended value. The available parameters and the default values of the parameters may vary based on the version of the catalog parameter. For more information, see the parameter description. Note Only Realtime Compute for Apache Flink that uses VVR 8.0.6 or later supports this parameter.	No
   catalog.table.metadata-columns	The metadata columns in a MySQL CDC source table that you want to add to the schema of a table when you query the table. By default, no metadata column is added. Separate multiple metadata columns with semicolons (;). Example: op_ts;table_name;database_name. Note Only Realtime Compute for Apache Flink that uses VVR 6.0.5 or later supports this parameter. If you specify this parameter, the specified metadata columns are added to the schema of the returned table. The metadata columns are available only in MySQL CDC source tables. Therefore, the tables that are created in the MySQL catalog can be used as only source tables and cannot be used as result tables or dimension tables.	No
   catalog.table.treat-tinyint1-as-boolean	Specifies whether to map the TINYINT(1) and BOOLEAN data types of MySQL to the BOOLEAN data type of Flink when Realtime Compute for Apache Flink obtains the schema of a table. Valid values: true: The TINYINT(1) and BOOLEAN data types of MySQL are mapped to the BOOLEAN data type of Flink. false: The TINYINT(1) and BOOLEAN data types of MySQL are mapped to the TINYINT data type of Flink. Default value: If you set the property-version parameter to 0, the default value of this parameter is true. If you set the property-version parameter to 1, the default value of this parameter is false. Note Only Realtime Compute for Apache Flink that uses VVR 8.0.4 or later supports this parameter. We recommend that you use fields of the TINYINT(1) data type of MySQL to store only values 0 and 1. You must select data types that meet your business requirements for mapping. For more information, see Data type mappings.	No

3. Select the code that is used to create a catalog and click Run on the left side of the code.

   If the The following statement has been executed successfully! message appears, the catalog is created.

   

UI (recommended)

On the Catalogs page, click the created catalog to view the Name and Type parameters of the catalog in the Catalog List section.

• View a catalog: Click View in the Actions column of the catalog that you want to manage and view the databases and tables in the catalog.

  The comment field is not displayed in the table schema details.

• Drop a catalog: Click Delete in the Actions column of the catalog that you want to delete.

  The drop operation only drops the catalogs that you create and does not drop the tables in the related services. After a catalog is dropped, the running deployments that use a table in the catalog are not adversely affected. If the deployments are redeployed or restarted, an error occurs, indicating that the table cannot be found. Exercise caution when you perform drop operations.

SQL

1. In the code editor of the Scripts tab on the Scripts page, enter the following code:

   -- View the schema information of the table in the catalog. The comment field is not displayed. 
   DESCRIBE `<catalogname>`.`<dbname>`.`<tablename>`;
   
   -- Drop a catalog.
   DROP CATALOG `<catalogname>`;

   Note

   The drop operation only drops the catalogs that you create and does not drop the tables in the related services. After a catalog is dropped, the running deployments that use a table in the catalog are not affected. If the deployments are redeployed or restarted, an error will occur indicating that the table cannot be found. Exercise caution when you perform drop operations.

2. Right-click the statements and choose Run from the shortcut menu.