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

View saved queries

Saved query functionality is available only in the Cloud console. To view a saved query, open the BigQuery 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

  1. In the Cloud console, open the BigQuery page.

    Go to BigQuery

  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 > Save query.

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

  6. Click Save.

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

  1. In the Cloud console, open the BigQuery page.

    Go to BigQuery

  2. In the Explorer panel, expand your project.

  3. Expand Saved queries. If your query is a project-level saved query, also expand Project queries.

  4. From the list of queries, click the name of your saved query.

  5. To access the shareable link and to copy the link to the clipboard, click > Get link.

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

  1. In the Cloud console, open the BigQuery page.

    Go to BigQuery

  2. In the Explorer panel, expand your project.

  3. Expand Saved queries. If your query is a project-level saved query, also expand Project queries.

  4. From the list of queries, click the name of your saved query to open it in the Query editor.

  5. Modify the query.

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

  1. In the Cloud console, open the BigQuery page.

    Go to BigQuery

  2. In the Explorer panel, expand your project.

  3. Expand Saved queries. If your query is a project-level saved query, also expand Project queries.

  4. From the list of queries, click the name of your saved query.

  5. Click > Delete.

  6. Confirm deletion by typing delete in the dialog.

  7. Click Delete.