Work with saved queries
This document describes how to work with saved queries, including how to create, view, share, and delete saved queries.
Queries that are saved or shared contain only the query itself, which means the following:
A shared query provides only the query text. To query the data, you still need to have appropriate access. One advantage of saving only the query text is for sharing. You can share a query that is incomplete, even if it isn't a valid query. Then, others can use it as a template, which they can complete or customize.
The only query option retained by a saved query is the SQL version, which governs whether the query uses legacy SQL or GoogleSQL.
There are 3 types of saved queries:
- Private. Private saved queries are visible only to the user who creates them.
- Project-level. Project-level saved queries are visible to principals that have the required permissions.
- Public. Public saved queries are visible to anyone with a link to the query.
The saved query functionality is available only in the Google Cloud console.
Permissions
The following IAM permissions are required to create, view, update, and delete saved queries:
- Private saved queries:
- Creating private saved queries requires no special permissions. You can save a private query in any project, but only you can view, update, or delete the query.
- Project-level saved queries:
- Creating a project-level saved query requires
bigquery.savedqueries.create
permissions. Thebigquery.admin
predefined role includesbigquery.savedqueries.create
permissions. - Viewing a project-level saved query requires
bigquery.savedqueries.get
andbigquery.savedqueries.list
permissions. Thebigquery.admin
andbigquery.user
predefined roles includebigquery.savedqueries.get
andbigquery.savedqueries.list
permissions. - Updating a project-level saved query requires
bigquery.savedqueries.update
permissions. Thebigquery.admin
predefined role includesbigquery.savedqueries.update
permissions. - Deleting a project-level saved query requires
bigquery.savedqueries.delete
permissions. Thebigquery.admin
predefined role includesbigquery.savedqueries.delete
permissions.
- Creating a project-level saved query requires
- Public saved queries:
- Creating public queries requires no special permissions. You can save a public query in any project, but only you can update or delete the query. Anyone with the link can view a public saved query.
For more information on IAM roles in BigQuery, see Predefined roles and permissions.
Create saved queries
In the Google Cloud console, go to the BigQuery page.
Click
Compose a new query.In the query editor, enter a valid SQL query. For example, you can use the query from the Google Cloud console quickstart.
# Find highest birth weights SELECT weight_pounds, state, year, gestation_weeks FROM `bigquery-public-data.samples.natality` ORDER BY weight_pounds DESC LIMIT 10
Click
Save > Save query.
In the Save query dialog, enter a name for your query, and then set Visibility to one of the following options:
- Personal (editable only by you) for a private shared query.
- Project (editable by principals with appropriate permissions) for a project-level saved query.
- Public for a public saved query.
Click Save.
View saved queries
In the Google Cloud console, go to the BigQuery page.
In the Explorer pane, expand your project.
Expand the
Saved queries (NUMBER) node, where NUMBER indicates the number of saved queries in your project.
If your query is a project-level saved query, also expand
Project queries.In the list of queries, click the name of your saved query. The query details are displayed.
Queries are identified with the following icons:
- Private:
- Project-level:
- Public:
Share saved queries
You can share 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.
To run a shared query, users must have access to the data that the query accesses. For more information, see Controlling access to datasets.
If you are planning on sharing a query, consider including a comment in the query that describes its purpose.
To share a query, follow these steps:
In the Google Cloud console, go to the BigQuery page.
In the Explorer pane, expand your project.
Expand
Saved queries. If your query is a project-level saved query, also expand Project queries.
From the list of queries, click the name of your saved query.
To access the shareable link and to copy the link to the clipboard, click > Get link.
View actionsShare the link with whoever you want to grant access to the query.
You can also get the shareable link by opening the query in the query editor, and then clicking > Get link.
Share
Update saved queries
In the Google Cloud console, go to the BigQuery page.
In the Explorer pane, expand your project.
Expand
Saved queries. If your query is a project-level saved query, also expand Project queries.
To open your saved query in the query editor, from the list of queries, click the name of your saved query.
Modify the query.
To update the query without changing the name, click
Save > Save query. To change the name, click
Save > Save query as, making updates in the dialog as necessary.
Delete saved queries
In the Google Cloud console, go to the BigQuery page.
In the Explorer pane, expand your project.
Expand
Saved queries. If your query is a project-level saved query, also expand Project queries.
From the list of queries, click the name of your saved query.
Click > Delete.
View actionsTo confirm deletion, enter
delete
in the dialog.Click Delete.