Save and share queries
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 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 Google 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 principals 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.
View saved queries
Saved query functionality is available only in the Google Cloud console. To view a saved query, open the BigQuery Google Cloud console, expand your project node in the Explorer panel, and then expand the Saved queries (N) node, where N indicates the number of saved queries in your project.
Create saved queries
To create a saved query:
Console
In the Google Cloud console, open the BigQuery page.
Click Compose new query.
Enter a valid SQL query in the Query editor text area. 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.
To view your saved query, in the Explorer panel, expand the project and then expand Saved queries. If this is a project-level saved query, also expand Project queries. 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:
Console
In the Google Cloud console, open the BigQuery page.
In the Explorer panel, 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.
Share 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 clicking Share > Get link.
Update saved queries
To update a saved query:
Console
In the Google Cloud console, open the BigQuery page.
In the Explorer panel, 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 open it in the Query editor.
Modify the query.
Click Save > Save query to update the query without changing the name. To change the name, click Save > Save query as, making updates in the dialog as necessary.
Delete saved queries
To delete a saved query:
Console
In the Google Cloud console, open the BigQuery page.
In the Explorer panel, 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.
Confirm deletion by typing delete in the dialog.
Click Delete.