External catalog collection

Step 1: Prepare the data

CREATE TABLE mysql_catalog_table(
  catalog_table_id INT,
  catalog_table_name VARCHAR(255)
)

Step 2: Configure the external connection

1. Connect to your StarRocks instance.

   1. Log on to the EMR console. In the left-side navigation pane, choose EMR Serverless > StarRocks. Click Instances.

   2. Find the StarRocks instance that you created and click Actions > Connect to Instance. The New Connection tab appears.

   3. On the New Connection tab, select the Region and Instance name of your StarRocks instance. Enter a Connection Name, Username, and Password. Click Test Network Connectivity. After the connection is successful, click OK to go to the StarRocks instance Query page.

2. In StarRocks, configure an external catalog for MySQL.

   1. Below the query list, click +File to open the Create File dialog box. Enter a Name, select a Storage Path, and click OK to create the file.

   2. Under All Files, double-click the name of the file that you created to open the StarRocks instance editor. Enter the following sample script to create an external catalog. For more information, see StarRocks external catalog configuration examples.

      CREATE EXTERNAL CATALOG mysql_db_catalog PROPERTIES (
          "driver_class" = "com.mysql.cj.jdbc.Driver", 
          "driver_url" = "https://catalog-bucket-oss.oss-cn-hangzhou-internal.aliyuncs.com/libs/mysql-connector-java-8.0.28.jar", 
          "type" = "jdbc", 
          "user" = "<UserName>", 
          "password"="<PassWord>",
          "jdbc_uri" = "jdbc:mysql://xxx:3306/mysql_catalog_db" );

      Note

      • mysql_db_catalog is the name of the external catalog for the MySQL data source.

      • Replace <UserName> and <PassWord> with your actual credentials.

      • jdbc_uri: Enter the connection URL of the database that you created.

   3. After editing, click Run to run the script. After the script runs successfully, you can view the related table information on the Database tab.

      Click the External Catalog tab. In the Catalog drop-down list, select the catalog you created, such as mysql_db_catalog. You can then expand it to view the corresponding database and table structures.

Step 3: Configure data sources

1. Log on to the DataWorks console. Switch to the target region. In the left-side navigation pane, click Workspace. In the Workspaces list, find the workspace that you want to manage and click Manage in the Actions column to open the Management Center.

2. In the left-side navigation pane of the Management Center, click Connection > Data Sources. On the Data Sources page, add StarRocks and MySQL data sources. For more information, see [Deprecated] Configure a StarRocks data source and Configure a MySQL data source.

   Note

   For the MySQL data source, you must select Connection String Mode. This mode is also required for any other JDBC-based external data source.

Step 4: Configure metadata collection

Log on to the DataWorks console. Switch to the target region. In the left-side navigation pane, choose Data Governance > Data Map, and then click Go to Data Map.

Step 5: Search for metadata

After the StarRocks and MySQL metadata collection tasks are complete, click the search icon on the left to open the search page.

1. On the Type tab, go to the Connection tab and select the StarRocks data source. Under Filter Conditions, select the StarRocks Instance you created, the external Data Catalog for MySQL, and the corresponding MySQL Database. You can then view the MySQL catalog information within the StarRocks data source.

   The search results show a record for a MySQL catalog table, such as mysql_catalog_table. This indicates that the metadata from the MySQL catalog has been successfully associated with the StarRocks data source.

2. Alternatively, on the Type tab, go to the Connection tab and select the MySQL data source. Under Filter Conditions, select the name of the MySQL database you created and verify that the table information is consistent.

   The search results show the table records from the MySQL data source, such as mysql_catalog_table. If the table name and the number of records match the results from the StarRocks external catalog search, it confirms that the MySQL data source metadata collection is configured correctly.

3. View table details.

   Click the target table name to view its details.

   The table details page contains three tabs: Details, Lineage, and Usage Notes. On the Details tab, the Column information section displays the Column name, Type, and Description of each column in a table. For example, the section displays columns such as catalog_table_id (int) and catalog_table_name (varchar).