Manage saved queries

This document describes how to managesaved queries and classic saved queriesand how to manage saved query metadata inDataplex Universal Catalog.

Saved queries areBigQuery Studiocode assets powered byDataform.

Before you begin

You can optionally set IAM permissions on migrated public orproject classic saved queries duringmigration,createor select a BigQuery Studio saved query andgrant selected Identity and Access Management (IAM) permissionsto that saved query.

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.

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)
• To letauthenticated users viewpublic access queries:Code Viewer (roles/dataform.codeViewer)

For more information about granting roles, seeManage 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 theRequired 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, bigquery.savedqueries.delete, bigquery.savedqueries.create
• To letauthenticated users viewpublic 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 withcustom roles or otherpredefined roles.

For more information about BigQuery IAM, seeAccess control with IAM.

To manage saved query metadata in Dataplex Universal Catalog,ensure that you have the requiredDataplex Universal Catalog roles and thedataform.repositories.get permission.

Share saved queries

To share a saved query with a user, you first grant that user access to thesaved query and add them to an appropriate IAM role. Then yougenerate a link to the saved query and share that link with the user.

Users that you share a query with only see the most recent version of a query.Autosaved changes that you haven't explicitly saved don't appear in the sharedquery.

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, expand your project and clickQueries.

4. Find and click the saved query that you want to grant access to. You can usethe search feature or filters to find your query.

5. Clickperson_addShare,and then clickManage permissions.

6. In theManage permissions pane, clickAdd user/group.

7. In theNew principals field, enter a principal.

8. In theRole list, select one ofthe following roles:

   • Code Owner: canperform any action on the saved query, including deleting or sharing it.
   • Code Editor: canedit the query.
   • Code Viewer: canview the query.
   Note: The principal must also have theBigQuery User (roles/bigquery.user)role to run the saved query.

9. Optional: To view a complete list of roles and advanced sharing settings,clickAdvanced sharing.

10. ClickSave.

11. To return to the saved query info, clickClose.

12. To generate a link to the saved query, clickperson_addShare,and then clickGet link.

    The link is copied to your clipboard.

Grant public access to a saved query

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

When you assign an IAM role to theallAuthenticatedUsersprincipal,service accounts and all users on the internet who have authenticated with aGoogle Account are granted that role. This includes accounts that aren'tconnected 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, seeAll authenticated users.

For example, when you grant the Code Viewer role toallAuthenticatedUserson thesales saved query, all service accountsand users on the internet who have authenticated with a Google Account haveread-only access to thesales saved query.

Caution: Granting administrator, edit, or execution level permissionstoallAuthenticatedUsers can allow bad actors to access yourdata. Grant only the minimal required permissions.

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

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, expand your project and clickQueries.

4. Find and click the saved query that you want to grant public access to. Youcan use the search feature or filters to find your query.

5. Clickmore_vertView actions next to the saved query, and then clickShare> Manage Permissions.

6. In theManage permissions pane, clickAdd user/group.

7. In theNew principals field, enterallAuthenticatedUsers.

8. In theRole list, select theCode Viewer role.

9. ClickSave.

10. To return to the saved query info, clickClose.

Prevent public access to saved queries

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

To restrictallAuthenticatedUsers in your project, you canset theiam.allowedPolicyMemberDomains policy,and removeallAuthenticatedUsers from the list ofallowed_values.

When you restrictallAuthenticatedUsers in theiam.allowedPolicyMemberDomainspolicy, theallAuthenticatedUsers principal cannot be used in anyIAM policy in your project, which prevents granting public accessto all resources, including BigQuery Studio saved queries.

For more information about theiam.allowedPolicyMemberDomains policyand instructions to set it, seeRestricting identities by domain.

Set default region

If this is the first time you are creating a code asset, you should set thedefault region for code assets. You can't change the region for a code assetafter it is created.

Note: If you create a saved query and choose a different default region than theone you have been using for code assets—for example, choosingus-west1when you have been usingus-central1—then that query and all codeassets you create afterwards use that new region by default. Existing codeassets continue to use the region they were assigned when they were created.

All code assets in BigQuery Studio use the same default region.To set the default region for code assets, follow these steps:

1. Go to theBigQuery page.

   Go to BigQuery

2. In theExplorer pane, find the project in which you have enabled codeassets.

3. Clickmore_vertView actions next to the project, and then clickChange my default code region.

4. ForRegion, select the region that you want to use for code assets.

5. ClickSelect.

For a list of supported regions, seeBigQuery Studio locations.

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 theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane,clickmore_vertView actions next toQueries, and then do one of the following:

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

View saved query metadata

To view saved query metadata, do the following:

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand your project and clickQueries.

4. Find and click the saved query that you want to view metadata for.

5. ClickinfoDetailsto see information about the saved query such astheregionit uses and the date it was last modified.

Work with saved query versions

You can choose to create a saved query either inside of or outside ofarepository. Saved query versioningis handled differently based on where the saved query is located.

Saved query versioning in repositories

Repositories are Git repositories that reside either in BigQueryor with a third-party provider. You can useworkspaces in repositories to performversion control on saved queries. For more information, seeUse version control with a file.

Saved query versioning outside of repositories

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

View saved query versions

To view saved query versions, do the following:

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand your project and clickQueries.

4. Click the name of the saved query you want to view version history for.

5. ClickhistoryVersion historyto 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 theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand your project and clickQueries.

4. Click the name of the saved query you want to compare version information for.

5. ClickhistoryVersion history.

6. Clickmore_vertView actions next to a saved query version and then clickCompare.The comparison pane opens, comparing the saved query version that youselected with the current query.

7. Optional: The current query also shows autosaved changes. Toexplicitly save these changes, clickOverwrite.

8. Optional: To compare the versions inline instead of in separate panes,clickCompare and then clickInline.

Restore a saved query version

Restoring from the comparison pane lets you compare the previous version ofthe saved query to the current version before choosing whether to restore it.

1. In the left pane, clickexploreExplorer:

   

2. In theExplorer pane, expand your project and clickQueries.

3. Click the name of the saved query you want to restore a previous version of.

4. ClickhistoryVersion history.

5. Clickmore_vertView actions next to the version of the saved query that you want torestore and then clickCompare. The comparison pane opens, comparing the saved query versionyou selected with the most recent query version, including any autosavedchanges.

6. If you want to restore the previous saved query version aftercomparison, clickRestore.

7. ClickConfirm.

Open saved queries in Connected Sheets

To open a saved query in Connected Sheets, follow these steps:

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand your project and clickQueries. Find the saved query that you want to open in Connected Sheets.

4. Clickmore_vertOpen actions next to the saved query, and then clickOpen in> Connected Sheets.

   Alternatively, click the name of the saved query to open it in the details pane, and then clickOpen in> Connected Sheets.

Download saved queries

To download a saved query, do the following:

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand your project and clickQueries.

4. Click the name of a saved query to open it.

5. ClickDownload.

Delete saved queries

To delete a saved query, do the following:

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

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand your project and clickQueries.

4. Find the saved query you want to delete.

5. Clickmore_vertOpen actions next to the saved query and then clickDelete.

6. To confirm deletion, typedelete in the dialog.

7. ClickDelete.

Classic saved queries

Deprecated: Saved queries, available inBigQuery Studio,will fully replace classic saved queries in the future. The deprecation timeline is being reviewed.For more information, seeDeprecation of classic saved queries.To learn how to migrate to saved queries, seeMigrate classic saved queries.

Use the following sections to learn how to manageclassic saved queries.

Note: If you have not enabled BigQuery Studio, then classicsaved queries appear in theSaved queries (NUMBER) folderin theClassic Explorer pane instead of the(Classic) Queries folder.

Share classic saved queries

You can share classic saved queries that you have given project or publicvisibility. Project visibility allows principals with therequired permissionsto view, update, or delete the query. Public visibility allows anyone withthe 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 queryaccesses. For more information, seeGrant access to a dataset.

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

1. In the left pane, clickcategoryClassic Explorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

2. In theClassic Explorer pane, expand your project, click(Classic) Queries, and then findthe classic saved query you want to share.

3. Clickmore_vertView actions next to the query and then clickGet link.

4. 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 theBigQuery page.

   Go to BigQuery

2. In the left pane, clickcategoryClassic Explorer:

   

3. In theClassic Explorer pane, expand your project and the(Classic) Queries folder, and if necessary, theProject queries folder.

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

5. ClickSave Query (Classic)> Save query as....

6. In theSave query dialog, type a name and choose the locationfor the query.

7. ClickSave.

Migrate classic saved queries

To batch migrate classic saved queries, you must be granted therequired roles.The permissions granted by these roles determine the type of classic savedqueries 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 whocreates them. They are identified by theperson icon. Personal classic savedqueries can only be migrated by their owners.

Public classic saved queries

Public classic saved queries are visible to anyone with a link tothe query. They are identified by theshare icon. Public classic saved queriescan only be migrated by their owners.

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

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

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

Project classic saved queries

Project-level saved queries are visible toprincipals that have therequired permissions.They are identified by thepeople icon.You can batch-migrate all project classic saved queries in your project.

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

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

If you don't set IAM permissions during migration, only you willhave 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 Studiosaved queries, stored in the selected region.
• Converts all of the migrating classic saved queries to read-only classicsaved queries.

After migration, you can access your personal, public, and project classic savedqueries both as BigQuery Studio saved queries and as read-only classic savedqueries.

Migration risks

After batch migration, you won't be able to modify migrated classic savedqueries. Your migrated personal, public, and projectclassic saved queries become read-only.

BigQuery will add migrated BigQuery Studio saved queriesto your Google Cloud project using the Dataform API. Reverting these changesrequires manual cleanup.

Warning: Migration cannot be stopped or canceled once started.

Batch migrate classic saved queries

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

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

   Go to BigQuery

2. In the left pane, clickcategoryClassic Explorer:

   

3. In theClassic Explorer pane, expand your project and clickmore_vertView actions next to(Classic) Queries,and then clickMigrate classic saved queries.

4. In theClassic saved queries migration pane, in theCheck migration readiness section,clickNext to confirm that you have therequired roles.

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

5. In theRegion section, in theRegion drop-down, select a regionwhere BigQuery will store the migrated saved queries.

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

6. To migrate all your personal classic saved queries, In theMigrate personal queries section, select theMigrate all personal queries checkbox, and then clickNext.

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

   1. Select theMigrate all public queries checkbox.
   2. In theSQL drop-down, select a BigQuery Studio saved querythat has the IAM policies which you want apply to themigrated saved queries.

   3. Optional: To add a user or group with whom you want to share themigrated saved queries, clickAdd User/Group.

      To share the migrated saved queries publicly, setallAuthenticatedUsersas the principal, and grant it the Code Viewer role.For more information, seeGrant public access.

   4. ClickNext.

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

   1. Select theMigrate all project queries checkbox.
   2. In theSQL drop-down, select a BigQuery Studio saved querythat 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 themigrated saved queries, clickAdd User/Group.
   4. ClickNext.

9. To confirm that you understand themigration risksand that you want to batch migrate classic saved queries,in theConfirm section, in theConfirm field, enterconfirm,and then clickNext.

   Warning: Migration cannot be stopped or canceled once started.

10. ClickSubmit.

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 theBigQuery page.

   Go to BigQuery

2. In the left pane, clickcategoryClassic Explorer:

   

3. In theClassic Explorer pane, expand your project and the(Classic) Queries folder, andif necessary, theProject queries folder.

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

5. Clickmore_vertView actions next to the query and then clickDelete.

6. To confirm deletion, typedelete in the dialog.

7. ClickDelete.

Manage metadata in Dataplex Universal Catalog

Dataplex Universal Catalog lets you store and manage metadata forsaved queries. Saved queries are available in Dataplex Universal Catalogby default, without additional configuration.

You can use Dataplex Universal Catalog to manage saved queriesin allsaved query locations.Managing saved queries in Dataplex Universal Catalogis subject toDataplex Universal Catalog quotas and limitsandDataplex Universal Catalog pricing.

Dataplex Universal Catalog automatically retrievesthe following metadata from saved queries:

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

Dataplex Universal Catalog logs saved queries asentries with the followingentry values:

System entry group

Thesystem entry groupfor saved queries is@dataform. To view details of saved query entriesin Dataplex Universal Catalog, you need to view thedataform system entry group.For instructions about how to view a list of all entries in an entry group, seeView details of an entry groupin the Dataplex Universal Catalog documentation.

System entry type

Thesystem entry typefor saved queries isdataform-code-asset. To view details ofsaved queries,you need to view thedataform-code-asset system entry type,filter the results with an aspect-based filter,andset thetype field insidedataform-code-asset aspect toSQL_QUERY.Then, select an entry of the selected saved query.For instructions about how to view details of a selected entry type, seeView details of an entry typein the Dataplex Universal Catalog documentation.For instructions about how to view details of a selected entry, seeView details of an entryin the Dataplex Universal Catalog documentation.

System aspect type

Thesystem aspect typefor saved queries isdataform-code-asset. Toprovide additional context to saved queries in Dataplex Universal Catalogby annotating data saved query entries withaspects,view thedataform-code-asset aspect type,filter the results with an aspect-based filter,andset thetype field insidedataform-code-asset aspect toSQL_QUERY.For instructions about how to annotate entries with aspects, seeManage aspects and enrich metadatain the Dataplex Universal Catalog documentation.

Type

The type for saved queries isSQL_QUERY.This type lets you filter saved queries in thedataform-code-assetsystem entry type and thedataform-code-asset aspect type by using theaspect:dataplex-types.global.dataform-code-asset.type=SQL_QUERYquery in anaspect-based filter.

For instructions about how to search for assets in Dataplex Universal Catalog, seeSearch for data assets in Dataplex Universal Catalogin the Dataplex Universal Catalog documentation.

What's next

• To learn more about BigQuery Studio saved queries, seeIntroduction to saved queries.
• To learn how to create saved queries, seeCreate saved queries.