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 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. The bigquery.admin predefined 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 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 role includes bigquery.savedqueries.update permissions.
    • Deleting a project-level saved query requires bigquery.savedqueries.delete permissions. The bigquery.admin predefined 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 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

  1. Open the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. Click Compose new query.

  3. 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

  4. Click Save query.

  5. In the Save query dialog, enter a name for your query, set Visibility to Personal (editable only by you), and then click Save.

  6. 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

  1. Open the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. Click Compose new query.

  3. 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

  4. In the Save drop-down list, select Save query.

  5. In the Save query dialog, enter a name for your query, set Visibility to Project (editable by project members), and then click Save.

  6. To view your saved query, click Saved queries.

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

  8. 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.

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

Creating a public saved query

To share a query with anyone:

Console

  1. Open the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. Click Compose new query.

  3. 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

  4. Click Save query.

  5. In the Save query dialog, enter a name for your query, set Visibility to Personal (editable only by you), and then click Save.

  6. To view your saved query, click Saved queries. The Personal queries page opens by default.

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

  8. 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.

  9. Share the link.

Updating saved queries

To update a saved query:

Console

  1. Open the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. 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.

  3. To the right of the query, click Open query in editor.

    Update query

  4. Modify the query.

  5. 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

  1. Open the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. 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.

  3. Click Delete to the right of the query.

    Delete query

  4. When prompted, click Delete to confirm.