Saving and sharing 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 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 pre-defined BigQuery IAM roles with the required permissions.
• Public: Public saved queries are visible to anyone with the query link.

Note: Currently, saved query functionality is available only in the GCP Console and the classic BigQuery web UI.

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. The bigquery.admin predefined, project-level IAM role includes bigquery.savedqueries.create permissions.
  • Viewing a project-level saved query requires bigquery.savedqueries.get and bigquery.savedqueries.list permissions. The bigquery.admin and bigquery.user predefined, project-level IAM roles include bigquery.savedqueries.get and bigquery.savedqueries.list permissions.
  • Updating a project-level saved query requires bigquery.savedqueries.update permissions. The bigquery.admin predefined, project-level IAM role includes bigquery.savedqueries.update permissions.
  • Deleting a project-level saved query requires bigquery.savedqueries.delete permissions. The bigquery.admin predefined, project-level IAM role includes bigquery.savedqueries.delete permissions.
• 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 Access control.

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:

Classic UI

1. Go to the BigQuery web UI.
   Go to the BigQuery web UI

2. Click Compose query.

3. Enter a SQL query in the New Query text area. For example, you can use the query from the classic web UI quickstart.

   #standardSQL
   SELECT
    weight_pounds, state, year, gestation_weeks
   FROM
    `bigquery-public-data.samples.natality`
   ORDER BY weight_pounds DESC LIMIT 10;

4. Click Save Query to open the Save Query dialog box.

5. In the Save Query dialog box, enter a name for your saved query, leave the Visibility set to Private (Visible only to you), and then click OK.

   

6. To view your saved query, ensure that Query History is selected in the left navigation and click Saved Queries in the Queries section.

   

Sharing a saved query

To share a query, set the visibility to Project or Public when you save the query. Project visibility allows members of the pre-defined 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:

Classic UI

1. Go to the BigQuery web UI.
   Go to the BigQuery web UI

2. Click Compose Query.

3. Enter a SQL query in the New Query text area. For example, you can use the query from the classic web UI quickstart.

   #standardSQL
   # Find highest birth weights
   SELECT
    weight_pounds, state, year, gestation_weeks
   FROM
    `bigquery-public-data.samples.natality`
   ORDER BY weight_pounds DESC LIMIT 10;

4. Click Save Query to open the Save Query dialog box.

5. In the Save Query dialog box, enter a name for your saved query, set the Visibility to Project (Project members can view and edit), and then click OK.

   

6. To view your saved query, ensure that Query History is selected in the left navigation and click Project Queries in the Queries section.

   

7. Click the name of your saved query to expand it.

   

8. Click Share Link to access the shareable link and to copy the link to the clipboard.

   

   Alternatively, you can access the link via the link icon above the New Query text area.

   

9. Share the link with another member of your team.

Creating a public saved query

To share a query with anyone:

Classic UI

1. Go to the BigQuery web UI.
   Go to the BigQuery web UI

2. Click Compose Query.

3. Enter a SQL query in the New Query text area. For example, you can use the query from the classic web UI quickstart.

   #standardSQL
   # Find highest birth weights
   SELECT
    weight_pounds, state, year, gestation_weeks
   FROM
    `bigquery-public-data.samples.natality`
   ORDER BY weight_pounds DESC LIMIT 10;

4. Click Save Query to open the Save Query dialog box.

5. In the Save Query dialog box, enter a name for your saved query, set the Visibility to Public (Anyone with the link can view, but not edit), and then click OK.

   

6. To view your saved query, ensure that Query History is selected in the left navigation and click Saved Queries in the Queries section.

   

7. Click the name of your saved public query to expand it.

   

8. Click Share Link to access the shareable link and to copy the link to the clipboard.

   

   Alternatively, you can access the link via the link icon above the New Query text area.

   

9. Share the link.

Updating saved queries

To update a saved query:

Classic UI

1. Go to the BigQuery web UI.
   Go to the BigQuery web UI

2. Click Query History in the left navigation and in the Queries section, click Saved Queries (to edit a private query) or Project Queries (to edit a project-level or public query).

   

3. To the right of the query, click Edit Query.

   

4. Modify the query.

5. Click Save Query to open the Save Query dialog box.

6. In the Save Query dialog box, leave the name unchanged and click OK.

Note: You cannot rename a query, but you can save it with a new name and delete the old one.

Deleting saved queries

To delete a saved query:

Classic UI

1. Go to the BigQuery web UI.
   Go to the BigQuery web UI

2. Click Query History in the left navigation.

3. To the right of the query, click the Delete saved query button (X).

4. When prompted, click OK to confirm.