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

Permissions

The following Cloud 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 web UI in the Cloud Console.
    Go to the Cloud Console

  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 web UI 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 box, enter a name for your query, set the Visibility to Personal (editable only by you), and then click Save.

    Save query private

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

  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 via the Link sharing button icon above the Query editor text area.

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

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.

    Save query dialog Box

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

    Save queries List

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 Cloud 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 web UI in the Cloud Console.
    Go to the Cloud Console

  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 web UI 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 box, enter a name for your query, set the Visibility to Project (editable by project members), and then click Save.

    Save query project

  6. To view your saved query, click Saved queries in the left navigation pane and click Project queries.

    Project queries tab

  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 via the Link sharing button icon above the Query editor text area.

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

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.

    Share query project

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

    Project queries list

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

    Project query expanded

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

    Project query share link

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

    Query Link icon

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

Creating a public saved query

To share a query with anyone:

Console

Currently it is not possible to create a public saved query using the Cloud Console.

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.

    Share query public

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

    Saved public queries list

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

    Public query expanded

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

    Public query share link

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

    Public query link icon

  9. Share the link.

Updating saved queries

To update a saved query:

Console

  1. Open the BigQuery web UI in the Cloud Console.
    Go to the Cloud Console

  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.

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

    Project Queries List

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

    Edit query button

  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.

Deleting saved queries

To delete a saved query:

Console

  1. Open the BigQuery web UI in the Cloud Console.
    Go to the Cloud Console

  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.

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.