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
- 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.
-
Make sure 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.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the Dataplex API for the project.
-
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": "[email protected]", "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": "[email protected]", "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": "[email protected]", "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": "[email protected]", "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": "[email protected]", "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": "[email protected]", "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.
Go to the Dataplex Catalog search page.
In Choose search platform, select Dataplex Catalog.
In the Filters panel, click Systems, and then select Cloud SQL.
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.
In the Aspects panel, click the Add more aspect types menu.
Select SQL Access.
Click OK.
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.
- Optional. Select the Version field, and enter a specific version of SQL Server.
Click Apply. Dataplex Catalog displays only SQL Server assets.
Search by aspect type
Dataplex Catalog includes a few built-in aspect types that you can use to perform searches.
In the Aspects panel, click the Add more aspect types menu.
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.
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
Click OK.
In the results table, click the name of the asset to view the metadata for that asset.
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.
Go to the Dataplex Catalog search page.
For the Choose search platform option, select Dataplex Catalog.
In the Filters panel, select Systems and then Cloud SQL. Alternatively, enter
system=Cloud_SQL
in the search field.Select an instance name.
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.
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.
Select a table name, and then on the Cloud SQL table details page, click Schema to view the table columns.
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 theAuto Close
option to false in SQL Server. However, if you want to use Dataplex Catalog, then you are required to set theAuto Close
option to false. For Express editions, theAuto Close
option is set to true by default, as designed by the SQL Server engine.