Import metadata from MySQL

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

This document describes how to import metadata from MySQL into Knowledge Catalog (formerly Dataplex Universal Catalog) using a Knowledge Catalog connector.

This connector supports MySQL instances hosted on-premises, in Cloud SQL, or in other cloud environments.

Before you can import metadata from MySQL, complete the following tasks:

1. Grant the required roles and permissions.
2. Enable the Knowledge Catalog and BigQuery Data Transfer Service APIs.
3. Meet the MySQL prerequisites.
4. Meet the networking prerequisites.

IAM roles and permissions

To create and manage a Knowledge Catalog connector job, you need Identity and Access Management (IAM) roles that grant permissions for Knowledge Catalog and BigQuery Data Transfer Service.

To get the permissions that you need to configure a MySQL connector, ask your administrator to grant you the following IAM roles:

• To create and manage entry groups: Dataplex Catalog Admin (roles/dataplex.catalogAdmin), Dataplex Catalog Editor (roles/dataplex.catalogEditor), or Dataplex Entry Group Owner (roles/dataplex.entryGroupOwner) on the project.
• To create and manage BigQuery Data Transfer Service transfer jobs: BigQuery Admin (roles/bigquery.admin) on the project.
• To view logs in Cloud Logging: Logs Viewer (roles/logging.viewer) on the project.

Additionally, you must grant the BigQuery Data Transfer Service service agent ([e-mail]) the dataplex.entryGroups.import permission or the Dataplex Entry Group Importer (roles/dataplex.entryGroupImporter) role. You can grant this role at either the project level or the entry group level.

For more information about granting roles, see Manage access.

Enable APIs

Enable the Knowledge Catalog and BigQuery Data Transfer Service APIs.

Enable the APIs

MySQL prerequisites

Ensure that you meet the prerequisites for MySQL transfers. For details, see MySQL prerequisites in the BigQuery Data Transfer Service documentation.

In addition to the BigQuery Data Transfer Service prerequisites, your MySQL database must meet the following requirements for metadata extraction:

• Minimum version: MySQL version 8.0 or later.
• Minimum required permissions: The extraction tool requires specific permissions to successfully query the INFORMATION_SCHEMA and gather metadata.

These permissions ensure the following metadata coverage:

• Instance and database information: Hostname, port, version, and schema configurations.
• Tables and columns: Core structure, data types, and operational metadata, such as sizes and row counts.
• Indexes and partitions: Secondary indexes, partition methods, and expressions.
• Views: Virtual table structures and definitions.
• Routines: Stored procedures, functions, and their parameters.
• Constraints: Detailed primary key, foreign key, unique, and check constraints.
• Triggers: Trigger definitions and event timing.

To grant these permissions, run the following SQL scripts.

Note: Replace USERNAME, PASSWORD, and DATABASE_NAME with your actual values.

1. Create the user:

   CREATE USER 'USERNAME'@'%'
   IDENTIFIED BY 'PASSWORD';
   

2. Grant metadata access. You can grant access for a single target database or across all databases on the instance.

   To grant access for a single target database or schema:

   GRANT SELECT, SHOW VIEW, REFERENCES, EXECUTE, TRIGGER
   ON DATABASE_NAME.*
   TO 'USERNAME'@'%';
   

   Alternatively, to grant access across all databases on the instance:

   GRANT SELECT, SHOW VIEW, REFERENCES, EXECUTE, TRIGGER
   ON *.*
   TO 'USERNAME'@'%';
   

3. Allow the extraction tool to list all available databases on the instance:

   GRANT SHOW DATABASES ON *.* TO 'USERNAME'@'%';
   

4. Apply the changes:

   FLUSH PRIVILEGES;
   

Networking prerequisites

A network attachment for connection through Private Service Connect is required when securely connecting to external databases or third-party cloud data sources with a private IP address inside a private network, to let BigQuery Data Transfer Service access your database.

If you connect to an on-premises environment or a cloud-hosted instance using a public IP address, a network attachment is not required.

Configure a MySQL connector

1. In the Google Cloud console, go to the Knowledge Catalog page.

   Go to Knowledge Catalog

2. In the navigation menu, in the Manage section, click Connectors.

3. Click Add connection.

4. In the Connectors list, select the MySQL card.

5. In the Data source details section, provide connection details for your MySQL instance:

   • For Network attachment, select an existing network attachment if required, or create one.
   • Enter the Host, Port, Database name, Username, and Password.
   • If using TLS, select a TLS Mode and provide a Trusted PEM Certificate.
   • For MySQL metadata objects to import, click Browse to select objects.

6. In the Destination settings section:

   • Click Browse and select an existing Knowledge Catalog entry group to store the imported metadata, or click Create new entry group.
   • Choose whether to set permissions on the entry group now or later. Set permissions so that users can view the imported metadata. If you didn't grant the Dataplex Entry Group Importer (roles/dataplex.entryGroupImporter) role or the dataplex.entryGroups.import permission to the BigQuery Data Transfer Service service agent ([e-mail]) at the project level, you must grant it at the entry group level.

7. In the Connector config name section, for Display name, enter a name for the metadata import job.

8. In the Schedule options section, configure the frequency for the metadata import job. If you select On-demand, the job runs only when you manually trigger it.

9. Optional: In the Notification options section, configure email or Pub/Sub notifications for job failures.

10. Optional: In the Advanced options section, configure encryption settings. If you select a customer-managed encryption key (CMEK), note that the key is used to encrypt any transient data staged before starting the Knowledge Catalog metadata import jobs. It is not used to encrypt the metadata in the destination Knowledge Catalog entry group.

11. Click Save.

After you create the job, Knowledge Catalog schedules the first run according to your configuration, or you can start it manually.

What's next

• Learn how to Manage connector jobs.