Manage your Cloud SQL resources using Dataplex Catalog

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

Dataplex Catalog is a platform for storing, managing, and accessing your metadata. After you enable the Dataplex Catalog integration on your Cloud SQL instance, Dataplex Catalog automatically retrieves the following metadata from Cloud SQL instances, databases, tables, columns, and views:

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

Dataplex Catalog retrieves metadata only from Cloud SQL primary instances and not from read replicas.

You can use Dataplex Catalog to discover and understand your Cloud SQL metadata. Use Dataplex 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 Catalog, you curate metadata by attaching aspects to Cloud SQL metadata entries. Each aspect can have multiple metadata fields and can be based on a predefined or custom aspect type.

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

  pii:true
  pii_type:SSN

For more information about Dataplex Catalog, see Dataplex Catalog overview.

Before you begin

  1. 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.
  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

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

    Go to project selector

  5. Make sure that billing is enabled for your Google Cloud project.

  6. Enable the Dataplex API for the project.

    Enable the Dataplex API

  7. 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, see Required IAM roles and permissions for Dataplex Catalog.

Required IAM roles and permissions for Dataplex Catalog

Cloud SQL uses the cloudsql.schemas.view permission to provide access to the metadata on Dataplex.

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, see Predefined Cloud SQL IAM roles.

Enable the integration of Dataplex Catalog on your Cloud SQL instance

To enable the integration of Dataplex Catalog on your Cloud SQL instance, use one of the following procedures.

gcloud

Create the instance

To create a Cloud SQL instance, use the gcloud sql instances create command.

gcloud sql instances create INSTANCE_NAME \
  --database-version=DATABASE_VERSION \
  --tier=MACHINE_TYPE \
  --region=REGION_NAME \
  --enable-dataplex-integration

Make the following replacements:

  • INSTANCE_NAME: the name of the instance
  • DATABASE_VERSION: the database version for the instance (for example, POSTGRES_13); for a complete list of available strings, see SqlDatabaseVersion
  • MACHINE_TYPE: the machine type for the instance
  • REGION_NAME: the region name for the instance

Update the instance

To enable the integration for an existing instance, use the gcloud sql instances patch command.

gcloud sql instances patch INSTANCE_NAME \
  --enable-dataplex-integration

If you need to enable and update all the Cloud SQL instances in your project, then you can run a script similar to the following:

gcloud sql instances list --format="(NAME)" \
| tail -n +2 | xargs -t -I %
gcloud sql instances patch % --enable-dataplex-integration

This example is Linux-based.

REST v1

Create the instance

Use this example to create an instance with the integration enabled. For a complete list of parameters that are available for this call, see the instances.insert page.

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
  • INSTANCE_NAME: the name of the instance
  • REGION_NAME: the region name for the instance
  • DATABASE_VERSION: enum string of the database version (for example, POSTGRES_13). For a complete list of available strings, see SqlDatabaseVersion.
  • PASSWORD: the password for the root user
  • MACHINE_TYPE: enum string of the machine (tier) type, as: db-custom-[CPUS]-[MEMORY_MBS]
  • EDITION_TYPE: your Cloud SQL edition

HTTP method and URL:

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

Request JSON body:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "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"
}

Update the instance

Use this example to update an existing instance. For a complete list of parameters that are available for this call, see the instances.patch page.

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
  • INSTANCE_NAME: the name of the instance

HTTP method and URL:

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

Request JSON body:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "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"
}

REST v1beta4

Create the instance

Use this example to create an instance with the integration enabled. For a complete list of parameters that are available for this call, see the instances.insert page.

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
  • INSTANCE_NAME: the name of the instance
  • REGION_NAME: the region name for the instance
  • DATABASE_VERSION: enum string of the database version (for example, POSTGRES_13). For a complete list of available strings, see SqlDatabaseVersion.
  • PASSWORD: the password for the root user
  • MACHINE_TYPE: enum string of the machine (tier) type, as: db-custom-[CPUS]-[MEMORY_MBS]
  • EDITION_TYPE: your Cloud SQL edition

HTTP method and URL:

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

Request JSON body:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "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"
}

Update the instance

Use this example to update an existing instance. For a complete list of parameters that are available for this call, see the instances.patch page.

Use this example to update an existing instance. For a complete list of parameters that are available for this call, see the instances.patch page.

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
  • INSTANCE_NAME: the name of the instance

HTTP method and URL:

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

Request JSON body:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "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 Catalog integration on your Cloud SQL instance

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

gcloud

To verify that the Dataplex Catalog integration is enabled on an existing instance, use the gcloud sql instances describe command.

gcloud sql instances describe INSTANCE_NAME

Replace INSTANCE_NAME with the name of the instance. In the output, look for the configuration setting enableDataplexIntegration set to true.

REST v1

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the project ID
  • INSTANCE_ID<: the instance ID

HTTP method and URL:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

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

REST v1beta4

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the project ID
  • INSTANCE_ID<: the instance ID

HTTP method and URL:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

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

Deactivate the integration of Dataplex Catalog on your Cloud SQL instance

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

gcloud

To deactivate the integration for an existing instance, use the gcloud sql instances patch command.

gcloud sql instances patch INSTANCE_NAME \
  --no-enable-dataplex-integration

If you need to deactivate the integration and update all the Cloud SQL instances in your project, then you can run a script similar to the following:

gcloud sql instances list --format="(NAME)" \
| tail -n +2 | xargs -t -I %
gcloud sql instances patch % --no-enable-dataplex-integration

This example is Linux-based.

REST v1

Use this example to deactivate the integration.

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
  • INSTANCE_NAME: the name of the instance

HTTP method and URL:

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

Request JSON body:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "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"
}

REST v1beta4

Use this example to deactivate the integration.

Before using any of the request data, make the following replacements:

  • PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
  • INSTANCE_NAME: the name of the instance

HTTP method and URL:

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

Request JSON body:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "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 Catalog to create the aspect types that you need.

After you create custom aspect types, you can attach aspects to your Cloud SQL assets. Attaching aspects to Cloud SQL assets lets 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, see Manage aspects and enrich metadata.

Search for Cloud SQL assets

Use the Dataplex Catalog search page in the Google Cloud console to search for Cloud SQL assets.

  1. Go to the Dataplex Catalog search page.

    Go to Dataplex

  2. In Choose search platform, select Dataplex Catalog.

  3. In the Filters panel, click Systems, and then select Cloud SQL.

  4. Optional. In Type aliases, you can filter the search results to a specific type of Cloud SQL asset by the selecting one or more of the following type alias:

    • Database
    • Database schema
    • Service
    • Table
    • View

Use queries to perform search

You can also use the search field in Dataplex Catalog to perform search queries. To view all Cloud SQL assets, enter system=Cloud_SQL.

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

system=Cloud_SQL AND type=Database

To view all Cloud SQL tables, enter the following query:

system=Cloud_SQL AND type=Table

You can also use parentheses and the logical operators AND and OR for complex expressions. To learn more about the expressions that you can use in the search field, see Search syntax for Dataplex Catalog.

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

type="projects/PROJECT_ID/locations/global/entryTypes/QUERY_STRING"

Replace the following:

  • PROJECT_ID: the ID of your project
  • QUERY_STRING: use the following table to identify a query string based on the Cloud SQL engine and type of asset that you want to query

    Cloud SQL engine Query strings
    Cloud SQL for MySQL
    • cloudsql-mysql-database
    • cloudsql-mysql-instance
    • cloudsql-mysql-table
    • cloudsql-mysql-view
    Cloud SQL for PostgreSQL
    • cloudsql-postgresql-database
    • cloudsql-postgresql-instance
    • cloudsql-postgresql-table
    • cloudsql-postgresql-schema
    • cloudsql-postgresql-view
    Cloud SQL for SQL Server
    • cloudsql-sqlserver-database
    • cloudsql-sqlserver-instance
    • cloudsql-sqlserver-table
    • cloudsql-sqlserver-schema
    • cloudsql-sqlserver-view

An example query might look like the following:

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

Filter Cloud SQL assets by Cloud SQL dialect

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

  1. In the Aspects panel, click the Add more aspect types menu.

  2. Select SQL Access.

  3. Click OK.

  4. Click the playlist_add edit aspect button. In the SQL Access page, do the following:

    • In the Dialect field, select SQL Server to filter for SQL Server assets.
  5. Optional. Select the Version field, and enter a specific version of SQL Server.
  6. Click Apply. Dataplex Catalog displays only SQL Server assets.

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

  1. In the Aspects panel, click the Add more aspect types menu.

  2. Optional. Select SQL Access to filter the results by Cloud SQL dialect. 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 results to that type.

    • Cloud SQL Database
    • Cloud SQL Instance
    • Cloud SQL View
    • Cloud SQL Schema
    • Cloud SQL Table
  4. Click OK.

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

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

    • In Overview, click Add to add a rich text description of the asset.
    • In Aspects, click Add to attach an aspect to the asset.
    • For an instance, to view member databases, click the Entry List tab, and then click Show all children entries in search. If the Entry List tab doesn't appear, then the instance has no databases.
    • In Entry details, view the full details of the asset. Click the entry 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 SQL instance, then view a member database, then view a table in that database, and then view the columns in the table.

  1. Go to the Dataplex Catalog search page.

    Go to Dataplex

  2. For the Choose search platform option, select Dataplex Catalog.

  3. In the Filters panel, select Systems and then Cloud SQL. Alternatively, enter system=Cloud_SQL in the search field.

  4. Select an instance name.

  5. On the Cloud SQL details page, click the Entry list tab, and then click Show all children entries in search. Dataplex Catalog displays the databases in the instance.

  6. On the Cloud SQL database details page, click the Entry list tab, and then click View child entries in search. Dataplex Catalog displays the tables in the database.

  7. Select a table name, and then on the Cloud SQL table details page, click Schema to view the table columns.

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

This workflow demonstrates drilling down from an instance to a table. You can go directly to a list of tables by entering system=Cloud_SQL AND type=Table in the search field.

Pricing

There is no charge for storing Cloud SQL technical metadata in Dataplex Catalog. Standard Dataplex pricing applies for API calls and additional business metadata enrichment. For more information, see the Dataplex pricing page.

Resource usage

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

Limitations

This section lists limitations with using Cloud SQL and Dataplex Catalog.

  • The Dataplex Catalog integration with Cloud SQL is turned off for any instance that restricts access to resources due to resource enrollment in Assured Workloads.

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

  • SQL Server databases that have the Auto Close option enabled won't be able to sync schema information to Dataplex Catalog. As a general best practice and for performance reasons, we recommend that you set the Auto Close option to false in SQL Server. However, if you want to use Dataplex Catalog, then you are required to set the Auto Close option to false. For Express editions, the Auto Close option is set to true by default, as designed by the SQL Server engine.

What's next