This page explains how you can search for and manage your Cloud SQLresources by using Dataplex Universal Catalog.

Dataplex Universal Catalog is a platform for storing, managing, andaccessing your metadata. Dataplex Universal Catalog retrieves thefollowing metadata from Cloud SQL instances, databases, tables,columns, and views automatically:

• Name
• Location (region)
• Creation date and last modification date
• Schema (for tables and views)
• Description

Dataplex Universal Catalog can ingest metadata at the Cloud SQL instance level even if you don't enable the integration. However, Dataplex Universal Catalog retrieves metadata from the resources belonging to the instance, such as its databases, tables, columns and views, only after you enable the integration.

Dataplex Universal Catalog retrieves metadata only from Cloud SQLprimary instances and not from read replicas.

You can use Dataplex Universal Catalog todiscover and understand your Cloud SQL metadata. UseDataplex Universal Catalog to aid with the following activities:

• Analysis, including dependencies and suitability for a use case
• Change management
• Data movement (pipelines)
• Schema evolution

With Dataplex Universal Catalog, you curate metadata by attaching aspectsto Cloud SQL metadata entries. Each aspect can have multiple metadatafields and can be based on a predefined or custom aspect type.

For example, you might attach the following aspect to a column that contains aSocial Security number, which is personally identifiable information (PII):

  pii:true  pii_type:SSN

For more information about Dataplex Universal Catalog, seeAbout data catalog management in Dataplex Universal Catalog.

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. Enable the Dataplex API for the project.

   Enable the Dataplex API

2. Check permissions.

   You need certain Identity and Access Management (IAM) roles and permissions to search for and attach entries to Cloud SQL assets. For details, seeRequired IAM roles and permissions for Dataplex Universal Catalog.

Required IAM roles and permissions for Dataplex Universal Catalog

Cloud SQL uses thecloudsql.schemas.view permission toprovide access to the metadata on Dataplex Universal Catalog.

To provide this permission, create a custom role that includes this permission,or use one of predefined roles that has this permission.

For more information, seePredefined Cloud SQL IAM roles.

gcloudsqlinstancescreateINSTANCE_NAME\--database-version=DATABASE_VERSION\--tier=MACHINE_TYPE\--region=REGION_NAME\--enable-dataplex-integration

gcloudsqlinstancespatchINSTANCE_NAME\--enable-dataplex-integration

gcloudsqlinstanceslist--format="(NAME)"\|tail-n+2|xargs-t-I%gcloudsqlinstancespatch%--enable-dataplex-integration

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances

{  "name": "INSTANCE_NAME",  "region": "REGION_NAME",  "databaseVersion": "DATABASE_VERSION",  "rootPassword": "PASSWORD",  "settings": {    "tier": "MACHINE_TYPE",    "edition": "EDITION_TYPE",    "enableDataplexIntegration": true  }}

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-09-25T22:19:33.735Z",  "operationType": "CREATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_ID",  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME

{  "settings":  {    "enableDataplexIntegration": true  }}

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-01-16T02:32:12.281Z",  "operationType": "UPDATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_NAME",  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances

{  "name": "INSTANCE_NAME",  "region": "REGION_NAME",  "databaseVersion": "DATABASE_VERSION",  "rootPassword": "PASSWORD",  "settings": {    "tier": "MACHINE_TYPE",    "edition": "EDITION_TYPE",    "enableDataplexIntegration": true  }}

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-09-25T22:19:33.735Z",  "operationType": "CREATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_ID",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME

{  "settings":  {    "enableDataplexIntegration": true  }}

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-01-16T02:32:12.281Z",  "operationType": "UPDATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_NAME",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

Verify Dataplex Universal Catalog integration on your Cloud SQL instance

To verify that the Dataplex Universal Catalog integration is enabled onyour instance, use one of the following procedures.

gcloudsqlinstancesdescribeINSTANCE_NAME

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID

curl -X GET \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method GET `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

{"settings":  {  "enableDataplexIntegration": true  }}

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID

curl -X GET \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method GET `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

{"settings":  {  "enableDataplexIntegration": true  }}

Deactivate the integration of Dataplex Universal Catalog on your Cloud SQL instance

To deactivate the integration of Dataplex Universal Catalog on yourCloud SQL instance, use one of the following procedures.

gcloudsqlinstancespatchINSTANCE_NAME\--no-enable-dataplex-integration

gcloudsqlinstanceslist--format="(NAME)"\|tail-n+2|xargs-t-I%gcloudsqlinstancespatch%--no-enable-dataplex-integration

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME

{  "settings":  {    "enableDataplexIntegration": false  }}

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-01-16T02:32:12.281Z",  "operationType": "UPDATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_NAME",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME

{  "settings":  {    "enableDataplexIntegration": false  }}

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME"

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

{  "kind": "sql#operation",  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME",  "status": "PENDING",  "user": "user@example.com",  "insertTime": "2024-01-16T02:32:12.281Z",  "operationType": "UPDATE",  "name": "OPERATION_ID",  "targetId": "INSTANCE_NAME",  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",  "targetProject": "PROJECT_ID"}

Enrich Cloud SQL assets with aspects

Aspect types are reusable resources that you can use as templates for aspects.Aspect types help you avoid duplication of work and incomplete aspects.You can use Dataplex Universal Catalog to create the aspect types thatyou need.

After you create custom aspect types, you can attach aspects to yourCloud SQL assets. Attaching aspects to Cloud SQL assetslets you do the following:

• Add business metadata to the assets
• Search for assets by business metadata and other custom metadata

To learn more about creating aspect types and attaching aspects to Cloud SQL, seeManage aspects and enrich metadata.

Search for Cloud SQL assets

Use the Dataplex Universal Catalog search page in the Google Cloud consoleto search for Cloud SQL assets.

1. Go to the Dataplex Universal CatalogSearch page.

   Go to Search

2. InChoose search platform, selectDataplex Universal Catalog.

3. In theFilters panel, clickSystems, and then selectCloud SQL.

4. Optional. InType aliases, you can filter the search results to a specifictype of Cloud SQL asset by the selecting one or more of the followingtype alias:

   • Database
   • Service
   • Table
   • View
   Note:Service is a type alias for a Cloud SQL instance.

Use queries to perform search

You can also use the search field in Dataplex Universal Catalogto perform search queries. To view allCloud SQL assets, entersystem=Cloud_SQL.

Then you can enter specific keywords. For example, to view all Cloud SQLdatabases:

system=Cloud_SQL AND type=Database

system=Cloud_SQL AND type=Table

You can also use parentheses and the logical operatorsAND andOR forcomplex expressions. To learn more about the expressions that you can use inthe search field, seeSearch syntax for Dataplex Universal Catalog.

You can directly enter search queries for specific Cloud SQL assetsinto the search field. The format of the query string is as follows:

type="projects/dataplex-types/locations/global/entryTypes/QUERY_STRING"

ReplaceQUERY_STRING witha query string based on the Cloud SQL engine and type of asset thatyou want to query. Use the following table to identify the appropriate query string.

An example query might look like the following:

type="projects/dataplex-types/locations/global/entryTypes/cloudsql-postgresql-instance"

Filter Cloud SQL assets by Cloud SQL dialect

By default, Dataplex Universal Catalog displays all Cloud SQLassets. To filter the assets for Cloud SQL for MySQL, Cloud SQL for PostgreSQL,or SQL Server only, then perform the following steps.

1. In theAspects panel, click theAdd more aspect types menu.

2. SelectSQL Access.

3. ClickOK.

4. Click theplaylist_add edit aspect button. In theSQL Access page, do the following:

   • In theDialect field, selectMySQL to filter for Cloud SQL for MySQL assets.
   • Optional. Select theVersion field, and enter a specific version of Cloud SQL for MySQL.

5. ClickApply. Dataplex Universal Catalog displays onlyCloud SQL for MySQL assets.

Search by aspect type

Dataplex Universal Catalog includes a few built-in aspect types that youcan use to perform searches.

1. In theAspects panel, click theAdd more aspect types menu.

2. Optional. SelectSQL Access to filter the results by Cloud SQLdialect. For more information, see the previous procedure,Filter Cloud SQL assets by Cloud SQL dialect.

3. Select one or more of the following aspect types to limit the search resultsto that type.

   • Cloud SQL Database
   • Cloud SQL Instance
   • Cloud SQL View
   • Cloud SQL Schema
   • Cloud SQL Table

4. ClickOK.

5. In the results table, click the name of the asset to view the metadata forthat asset.

6. Optional: Enhance or view your assets. You can do any of the following:

   • InOverview, clickAdd to add a rich text description of the asset.
   • InAspects, clickAdd to attach an aspect to the asset.
   • For an instance, to view member databases, click theEntry List tab, and then clickShow all children entries in search.If theEntry List tab doesn't appear, then the instance has no databases.
   • InEntry details, view the full details of the asset. Click theentry name to drill down to additional entries.

Example workflow - Drill down from instance to columns

In this example workflow, you start by searching for a Cloud SQLinstance, then view a member database, then view a table in that database, andthen view the columns in the table.

1. Go to the Dataplex Universal CatalogSearch page.

   Go to Search

2. For theChoose search platform option, selectDataplex Universal Catalog.

3. In theFilters panel, selectSystems and thenCloud SQL.Alternatively, entersystem=Cloud_SQL in the search field.

4. Select an instance name.

5. On theCloud SQL details page, click theEntry list tab, and then clickShow all children entries in search.Dataplex Universal Catalog displays the databases in the instance.

   Note: If there is noEntry list tab, then return to theSearch pageand choose a different instance.

6. On theCloud SQL database details page, click theEntry list tab,and then clickView child entries in search. Dataplex Universal Catalogdisplays the tables in the database.

7. Select a table name, and then on theCloud SQL table details page, clickSchema to view the table columns.

8. Optional: To add an aspect type to a column, click theAdd aspect button.

This workflow demonstrates drilling down from an instance to a table. Youcan go directly to a list of tables by enteringsystem=Cloud_SQL AND type=Tablein the search field.

Pricing

There is no charge for storing Cloud SQL technical metadata inDataplex Universal Catalog. StandardDataplex Universal Catalog pricing applies for API calls and additional businessmetadata enrichment. For more information, see theDataplex Universal Catalog pricing page.

Resource usage

Dataplex Universal Catalog extracts data from your instances periodically.The extraction process requires a certain amount of CPU usage. Instances withsmaller machine types— for example, machines that have shared cores andlarger schemas (10,000 or more tables), can utilize up to 40% of their CPU duringthe extraction process.

Limitations

This section lists limitations with using Cloud SQL andDataplex Universal Catalog.

• The Dataplex Universal Catalog integration with Cloud SQLis turned off for any instance that restricts access to resourcesdue to resource enrollment inAssured Workloads.

• After you enable Dataplex Universal Catalog on an instance, it can takebetween 2 to 48 hours for your Cloud SQL resources to appearin Dataplex Universal Catalog as assets, depending on the machine size,version, and type of Cloud SQL engine that you are using.

• When you drop a Cloud SQL for MySQL database, table or view and thenrecreate that database, tableor view with the previous name, all existing Dataplex Universal CatalogCatalog entries belonging to the original database,table or view remain in Dataplex Universal Catalog.When you perform the same drop and recreate operations on aCloud SQL for PostgreSQL or SQL Server database, the entries are removedfrom Dataplex Universal Catalog, which is the expected behavior.
• When you perform aTRUNCATE operation ona Cloud SQL for MySQL database, all Dataplex Universal Catalog entries areremoved from the database.

What's next

• About data catalog management in Dataplex Universal Catalog
• Dataplex Universal Catalog Identity and Access Management roles