Manage saved queries

This document describes how to manage saved queries and classic saved queries and how to manage saved query metadata in Dataplex.

Saved queries

Use the following sections to learn how to manage saved queries. Saved queries are BigQuery Studio code assets powered by Dataform.

Before you begin

To manage BigQuery Studio saved queries and to migrate classic saved queries to BigQuery Studio saved queries, do the following:

1. Enable BigQuery Studio in your project.

2. Optional: To set IAM permissions on migrated public or project classic saved queries during migration (Preview), create or select a BigQuery Studio saved query and grant selected Identity and Access Management (IAM) permissions to that saved query.

   1. During migration of public or project classic saved queries to BigQuery Studio saved queries, select a BigQuery Studio saved query to copy the permissions granted on it to the migrated saved queries.

3. To manage saved query metadata in Dataplex, ensure that the Dataplex API is enabled in your Google Cloud project.

Required roles

To get the permissions that you need to manage saved queries, ask your administrator to grant you the following IAM roles on the project that you want to manage saved queries for:

• To manage BigQuery Studio saved queries in the Google Cloud console:
  • BigQuery Job User (roles/bigquery.jobUser)
  • BigQuery Read Session User (roles/bigquery.readSessionUser)
  • Code Owner (roles/dataform.codeOwner)
• To manage BigQuery Studio saved queries by using the BigQuery API: Code Owner (roles/dataform.codeOwner)
• To migrate project classic saved queries to BigQuery Studio saved queries:
  • BigQuery Studio Admin (roles/bigquery.studioAdmin)
  • Project IAM Admin (roles/resourcemanager.projectIamAdmin)
• To let authenticated users view public access queries: Code Viewer (roles/dataform.codeViewer)

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to manage saved queries. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to manage saved queries:

• To manage BigQuery Studio saved queries in the Google Cloud console: bigquery.config.get, bigquery.jobs.create, dataform.locations., resourcemanager.projects.get, resourcemanager.projects.list, bigquery.readsessions., dataform.repositories., dataform.workspaces.
• To manage BigQuery Studio saved queries by using the BigQuery API: dataform.locations., dataform.repositories., dataform.workspaces.*, resourcemanager.projects.get, resourcemanager.projects.list
• To migrate project classic saved queries to BigQuery Studio saved queries: bigquery.savedqueries.get, bigquery.savedqueries.list, bigquery.savedqueries.update, resourcemanager.projects.setIamPolicy
• To let authenticated users view public access queries: dataform.locations.*, dataform.repositories.computeAccessTokenStatus, dataform.repositories.fetchHistory, dataform.repositories.fetchRemoteBranches, dataform.repositories.get, dataform.repositories.getIamPolicy, dataform.repositories.list, dataform.repositories.queryDirectoryContents, dataform.repositories.readFile, dataform.workspaces.fetchFileDiff, dataform.workspaces.fetchFileGitStatuses, dataform.workspaces.fetchGitAheadBehind. dataform.workspaces.get, dataform.workspaces.getIamPolicy, dataform.workspaces.list, dataform.workspaces.queryDirectoryContents, dataform.workspaces.readFile, dataform.workspaces.searchFiles, resourcemanager.projects.get, resourcemanager.projects.list

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about BigQuery IAM, see Access control with IAM.

To manage saved query metadata in Dataplex, ensure that you have the required Dataplex roles and the dataform.repository.get permission.

Grant access to saved queries

To grant other users access to a saved query, add those users to an appropriate IAM role.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, the Shared queries folder. Find the saved query that you want to grant access to.

3. Click more_vert View actions next to the saved query, and then click Share > Manage Permissions.

4. In the Manage permissions pane, click Add user/group.

5. In the New principals field, enter a principal.

6. In the Role list, select one of the following roles:

   • Code Owner: Can perform any action on the saved query, including deleting or sharing it.
   • Code Editor: Can edit the query.
   • Code Viewer: Can view the query.

7. Optional: To view a complete list of roles and advanced sharing settings, click Advanced sharing.

8. Click Save.

9. To return to the saved query info, click Close.

Share saved queries

You can share a saved query with other users by generating and sharing a link to the saved query. Before users can see the saved query you share, you must first grant them access to the saved query.

To run a shared query, users must have access to the data that the query accesses. For more information, see Grant access to a dataset.

If you plan to share a saved query, consider including a comment in the query that describes its purpose.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, expand the Shared queries folder. Find the saved query that you want to share.

3. Click more_vert View actions next to the query and then click Share > Copy link.

4. Share the link with other users.

Grant public access to a saved query

You can grant public access to a BigQuery Studio saved query by granting the Code Viewer (roles/dataform.codeViewer) role on the saved query to the allAuthenticatedUsers principal.

When you assign an IAM role to the allAuthenticatedUsers principal, service accounts and all users on the internet who have authenticated with a Google Account are granted that role. This includes accounts that aren't connected to a Google Workspace account or Cloud Identity domain, such as personal Gmail accounts. Users who aren't authenticated, such as anonymous visitors, aren't included. For more information, see All authenticated users.

For example, when you grant the Code Viewer role to allAuthenticatedUsers on the sales saved query, all service accounts and users on the internet who have authenticated with a Google Account have read-only access to the sales saved query.

To grant public access to a BigQuery Studio saved query, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, the Shared queries folder. Find the saved query that you want to grant public access to.

3. Click more_vert View actions next to the saved query, and then click Share > Manage Permissions.

4. In the Manage permissions pane, click Add user/group.

5. In the New principals field, enter allAuthenticatedUsers.

6. In the Role list, select the Code Viewer role.

7. Click Save.

8. To return to the saved query info, click Close.

Prevent public access to saved queries

To ensure no public access is granted to any BigQuery Studio saved query, restrict the allAuthenticatedUsers principal in your project.

To restrict allAuthenticatedUsers in your project, you can set the iam.allowedPolicyMemberDomains policy, and remove allAuthenticatedUsers from the list of allowed_values.

When you restrict allAuthenticatedUsers in the iam.allowedPolicyMemberDomains policy, the allAuthenticatedUsers principal cannot be used in any IAM policy in your project, which prevents granting public access to all resources, including BigQuery Studio saved queries.

For more information about the iam.allowedPolicyMemberDomains policy and instructions to set it, see Restricting identities by domain.

View all saved queries

To view a list of all saved queries in your project, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, click more_vert View actions next to Queries, and then do one of the following:

• To open the list in the current tab, click Show all.
• To open the list in a new tab, click Show all in > New tab.
• To open the list in a split tab, click Show all in > Split tab.

View saved query metadata

To view saved query metadata, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, the Shared queries folder. Click the name of the saved query you want to view metadata for.

3. Look at the Summary pane to see information about the saved query such as the region it uses and the date it was last modified.

Work with saved query versions

Use the following sections to learn how to view, compare, and restore versions of a saved query.

View saved query versions

To view saved query versions, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, the Shared queries folder. Click the name of the saved query you want to view activity for.

3. Click the Activity tab to see a list of the saved query versions in descending order by date.

Compare saved query versions

To compare saved query versions, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, the Shared queries folder. Click the name of the saved query you want to compare version information for.

3. In the Activity tab, click more_vert View actions next to a saved query version and then click Compare. The comparison pane opens, comparing the saved query version that you selected with the current saved query version.

4. Optional: The current saved query version also shows unsaved changes. To save these changes, click Overwrite.

5. Optional: To compare the versions inline instead of in separate panes, click Compare and then click Inline.

Restore a saved query version

Use one of the following options to restore a saved query version. Restoring from the comparison pane lets you compare the previous version of the saved query to the current version before choosing whether to restore it.

Download saved queries

To download a saved query, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, the Shared queries folder. Click the name of a saved query to open it.

3. Click Download.

Delete saved queries

To delete a saved query, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the Queries folder, and if necessary, the Shared queries folder. Find the saved query you want to delete.

3. Click more_vert View actions next to the saved query and then click Delete.

4. To confirm deletion, type delete in the dialog.

5. Click Delete.

Classic saved queries

Use the following sections to learn how to manage classic saved queries.

Share classic saved queries

You can share classic saved queries that you have given project or public visibility. Project visibility allows principals with the required permissions to view, update, or delete the query. Public visibility allows anyone with the query link to view but not update or delete the query.

You share a classic saved query with other users by generating and sharing a link to the classic saved query.

To run a classic shared query, users must have access to the data that the query accesses. For more information, see Grant access to a dataset.

If you are plan to share a classic saved query, consider including a comment in the query that describes its purpose.

1. In the Explorer pane, expand your project and the (Classic) Queries folder, and then find the classic saved query you want to share.
2. Click more_vert View actions next to the query and then click Get link.
3. Share the link with the users you want to grant access to the query.

Save a classic query as a saved query

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the (Classic) Queries folder, and if necessary, the Project queries folder.

3. Click the name of a classic saved query to open it.

4. Click Save Query (Classic) > Save query as....

5. In the Save query dialog, type a name and choose the location for the query.

6. Click Save.

Migrate classic saved queries

To request support or provide feedback for this feature, email bigquery-studio-support-alias@google.com.

To batch migrate classic saved queries, you must be granted the required roles. The permissions granted by these roles determine the type of classic saved queries you can migrate.

You can batch migrate the following classic saved queries:

Personal classic saved queries

Personal classic saved queries are visible only to the user who creates them. They are identified by the person icon. Personal classic saved queries can only be migrated by their owners. Administrators and users granted access to the migrated BigQuery Studio saved queries may be able to access, modify, or delete personal saved queries.

Public classic saved queries

Public classic saved queries are visible to anyone with a link to the query. They are identified by the share icon. Public classic saved queries can only be migrated by their owners.

IAM permissions on public classic saved queries don't map to permissions on BigQuery Studio saved queries. This means that BigQuery Studio saved queries migrated from public classic saved queries are not publicly available by default. You need to set IAM permissions for migrated BigQuery Studio saved queries, either during or after migration.

To set IAM permissions for the migrated BigQuery Studio saved queries during migration, you can select an existing BigQuery Studio saved query that has permissions which you want to apply to the migrated saved queries. BigQuery will copy permissions granted on the selected BigQuery Studio saved query, and apply them to the migrated saved queries. You can also manually add users or groups with whom you want to share the migrated saved queries.

If you don't set IAM permissions during migration, only you will have access to the migrated BigQuery Studio saved queries.

Project classic saved queries

Project-level saved queries are visible to principals that have the required permissions. They are identified by the people icon. You can batch-migrate all project classic saved queries in your project.

IAM permissions on project classic saved queries don't directly map to permissions on BigQuery Studio saved queries. You need to set IAM permissions for migrated BigQuery Studio saved queries, either during, or after migration.

To set IAM permissions for the migrated BigQuery Studio saved queries during migration, you can select an existing BigQuery Studio saved query that has permissions which you want to apply to the migrated saved queries. BigQuery will copy permissions granted on the selected BigQuery Studio saved query, and apply them to the migrated saved queries. You can also manually add users or groups with whom you want to share the migrated saved queries.

If you don't set IAM permissions during migration, only you will have access to the migrated BigQuery Studio saved queries.

During batch migration of classic saved queries, BigQuery does the following:

• Saves all of the migrating classic saved queries as BigQuery Studio saved queries, stored in the selected region.
• Converts all of the migrating classic saved queries to read-only classic saved queries, available until 2025.

After migration, you can access your personal, public, and project classic saved queries both as BigQuery Studio saved queries and as read-only classic saved queries. Read-only classic saved queries are available until 2025.

Migration risks

After batch migration, you won't be able to modify migrated classic saved queries. Your migrated personal, public, and project classic saved queries become read-only. The read-only personal, public, and project classic saved queries are available until 2025.

BigQuery will add migrated BigQuery Studio saved queries to your Google Cloud project using the Dataform API. Reverting these changes requires manual cleanup.

Batch migrate classic saved queries

To batch migrate classic saved queries in your project to BigQuery Studio saved queries, do the following:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and click more_vert View actions next to (Classic) Queries, and then click Migrate classic saved queries.

3. In the Classic saved queries migration pane, in the Check migration readiness section, click Next to confirm that you have the required roles.

   Your IAM permissions determine which type of classic saved queries you can migrate and which sections of the Classic saved queries migration pane are visible to you.

4. In the Region section, in the Region drop-down, select a region where BigQuery will store the migrated saved queries.

   We recommend selecting your default region for BigQuery Studio code assets. For more information, see Set the default region for code assets.

5. To migrate all your personal classic saved queries, In the Migrate personal queries section, select the Migrate all personal queries checkbox, and then click Next.

6. To migrate all public classic saved queries in your project, in the Migrate public queries section, do the following:

   1. Select the Migrate all public queries checkbox.
   2. In the SQL drop-down, select a BigQuery Studio saved query that has the IAM policies which you want apply to the migrated saved queries.

   3. Optional: To add a user or group with whom you want to share the migrated saved queries, click Add User/Group and Grant access to saved queries.

      To share the migrated saved queries publicly, set allAuthenticatedUsers as the principal, and grant it the Code Viewer role. For more information, see Grant public access.

   4. Click Next.

7. To migrate project-level classic saved queries, in the Migrate project queries section, do the following:

   1. Select the Migrate all project queries checkbox.
   2. In the SQL drop-down, select a BigQuery Studio saved query that has the IAM policies which you want apply to the migrated saved queries.
   3. Optional: To add a user or group with whom you want to share the migrated saved queries, click Add User/Group and Grant access to saved queries.
   4. Click Next.

8. To confirm that you understand the migration risks and that you want to batch migrate classic saved queries, in the Confirm section, in the Confirm field, enter confirm, and then click Next.

9. Click Submit.

Migration can take over 15 minutes, depending on the number of migrating queries.

Delete classic saved queries

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, expand your project and the (Classic) Queries folder, and if necessary, the Project queries folder.

3. Find the classic saved query you want to delete.

4. Click more_vert View actions next to the query and then click Delete.

5. To confirm deletion, type delete in the dialog.

6. Click Delete.

Manage metadata in Dataplex

Dataplex lets you store and manage metadata for saved queries. Saved queries are available in Dataplex by default, without additional configuration.

You can use Dataplex to manage saved queries in all saved query locations. Managing saved queries in Dataplex is subject to Dataplex quotas and limits and Dataplex pricing.

Dataplex automatically retrieves the following metadata from saved queries:

• Data asset name
• Data asset parent
• Data asset location
• Data asset type
• Corresponding Google Cloud project

Dataplex logs saved queries as entries with the following entry values:

System entry group

The system entry group for saved queries is @dataform. To view details of saved query entries in Dataplex, you need to view the dataform system entry group. For instructions about how to view a list of all entries in an entry group, see View details of an entry group in the Dataplex documentation.

System entry type

The system entry type for saved queries is dataform-code-asset. To view details of saved queries,you need to view the dataform-code-asset system entry type, filter the results with an aspect-based filter, and set the type field inside dataform-code-asset aspect to SQL_QUERY. Then, select an entry of the selected saved query. For instructions about how to view details of a selected entry type, see View details of an entry type in the Dataplex documentation. For instructions about how to view details of a selected entry, see View details of an entry in the Dataplex documentation.

System aspect type

The system aspect type for saved queries is dataform-code-asset. To provide additional context to saved queries in Dataplex by annotating data saved query entries with aspects, view the dataform-code-asset aspect type, filter the results with an aspect-based filter, and set the type field inside dataform-code-asset aspect to SQL_QUERY. For instructions about how to annotate entries with aspects, see Manage aspects and enrich metadata in the Dataplex documentation.

Type

The type for saved queries is SQL_QUERY. This type lets you filter saved queries in the dataform-code-asset system entry type and the dataform-code-asset aspect type by using the aspect:dataplex-types.global.dataform-code-asset.type=SQL_QUERY query in an aspect-based filter.

For instructions about how to search for assets in Dataplex, see Search for data assets in Dataplex in the Dataplex documentation.

What's next

• To learn more about BigQuery Studio saved queries, see Introduction to saved queries .
• To learn how to create saved queries, see Create saved queries.