Work with saved queries

This document describes how to work with saved queries, including how to create, view, share, and delete saved queries.

Queries that are saved or shared contain only the query itself, which means the following:

  • A shared query provides only the query text. To query the data, you still need to have appropriate access. 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 GoogleSQL.

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 that have the required permissions.
  • Public. Public saved queries are visible to anyone with a link to the query.

The saved query functionality is available only in the Google Cloud console.

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.

Create saved queries

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. Click Compose a new query.

  3. In the query editor, enter a valid SQL query. 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

  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.

View saved queries

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project.

  3. Expand the Saved queries (NUMBER) node, where NUMBER indicates the number of saved queries in your project.

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

  4. In the list of queries, click the name of your saved query. The query details are displayed.

    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, follow these steps:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, 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 View actions > 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 then clicking Share > Get link.

Update saved queries

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project.

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

  4. To open your saved query in the query editor, from the list of queries, click the name of your saved query.

  5. Modify the query.

  6. To update the query without changing the name, click Save > Save query. To change the name, click Save > Save query as, making updates in the dialog as necessary.

Delete saved queries

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, 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 View actions > Delete.

  6. To confirm deletion, enter delete in the dialog.

  7. Click Delete.