This page describes how to save and share queries. Queries that are saved or shared contain only the query itself, which means that:
A shared query provides the query text only. You still need to have appropriate access to query the data. One advantage of saving only the query text is that you can share a query that is incomplete, even if it isn't a valid query, and use it as a template that other users 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 standard SQL.
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 members of the predefined BigQuery IAM roles with the required permissions.
- Public: Public saved queries are visible to anyone with a link to the query.
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.
Creating a private saved query
When you save a query and set the visibility to Private, the query can be viewed, updated, or deleted only by you.
To create a private shared query:
Console
Open the BigQuery page in the Cloud Console.
Click Compose new query.
Enter a valid SQL query in the Query editor text area. For example, you can use the query from the 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 query.
In the Save query dialog, enter a name for your query, set Visibility to Personal (editable only by you), and then click Save.
To view your saved query, click Saved queries. The Personal queries page opens by default.
Sharing a saved query
You can share your query using project or public visibility. Project visibility allows members of the predefined BigQuery IAM roles 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 the query, users must have access to the data that is accessed by the query. For more information, see Controlling access to datasets.
When you share a query, consider including a comment that describes the query as shown in the example that follows.
Creating a project-level saved query
To share a query with another member of your project:
Console
Open the BigQuery page in the Cloud Console.
Click Compose new query.
Enter a valid BigQuery SQL query in the Query editor text area. For example, you can use the query from the 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
In the Save drop-down list, select Save query.
In the Save query dialog, enter a name for your query, set Visibility to Project (editable by project members), and then click Save.
To view your saved query, click Saved queries.
Click the name of your saved query to expand it.
Click Share query URL to access the shareable link and to copy the link to the clipboard. Alternatively, you can access the link by clicking Link sharing above the Query editor text area.
Share the link with another member of your team.
Creating a public saved query
To share a query with anyone:
Console
Open the BigQuery page in the Cloud Console.
Click Compose new query.
Enter a valid SQL query in the Query editor text area. For example, you can use the query from the 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 query.
In the Save query dialog, enter a name for your query, set Visibility to Personal (editable only by you), and then click Save.
To view your saved query, click Saved queries. The Personal queries page opens by default.
Click the name of your saved query to expand it.
Turn on Link sharing to access the shareable link and to copy the link to the clipboard. Alternatively, you can access the link by clicking Link sharing above the Query editor text area.
Share the link.
Updating saved queries
To update a saved query:
Console
Open the BigQuery page in the Cloud Console.
Click Saved queries in the left navigation, and click Personal queries (to edit a private query) or Project Queries (to edit a project-level query). Click on your query to expand it.
To the right of the query, click Open query in editor.
Modify the query.
Click Save query to update the query without changing the name. If you want to change the name, click the down arrow next to Save query and select Save as, making updates in the dialog box as necessary.
Deleting saved queries
To delete a saved query:
Console
Open the BigQuery page in the Cloud Console.
Click Saved queries in the left navigation, and click Personal queries (to delete a private query) or Project Queries (to delete a project-level query). Click on your query to expand it.
Click Delete to the right of the query.
When prompted, click Delete to confirm.