Create saved queries

When you write SQL in the query editor, you can save your query and share your query with others. Saved queries are BigQuery Studio code assets powered by Dataform.

For more information on deleting saved queries and managing saved query history, see Manage saved queries.

Required permissions

Set the appropriate permissions to create, edit, or view saved queries.

All users with the Dataform Admin role (roles/dataform.admin) have owner access to all saved queries created in the project.

For more information about BigQuery Identity and Access Management (IAM), see Access control with IAM.

Permissions to create saved queries

To create and run saved queries, you need the following IAM permissions:

  • dataform.locations.get
  • dataform.locations.list
  • dataform.repositories.list
  • dataform.repositories.create

You can get these permissions from the following IAM roles:

Permissions to edit saved queries

To edit and run saved queries, you need the following IAM roles:

Permissions to view saved queries

To view and run saved queries, you need the following IAM roles:

Security considerations for saved queries

Because code assets in BigQuery are powered by Dataform, you should consider the following security implications for users with access to these assets:

  • Visibility for code assets is governed by project-level Dataform permissions. Users with the dataform.repositories.list permission—which is included in standard BigQuery roles such as BigQuery Job User, BigQuery Studio User, and BigQuery User—can see all code assets in the Explorer panel of the Google Cloud project, regardless of whether they created these assets or these assets were shared with them. To restrict visibility, you can create custom roles that exclude the dataform.repositories.list permission.
  • Any secrets shared with the Dataform service agent can potentially be accessed by users who can edit these assets. To secure your credentials, restrict creation and edit access to trusted users, and limit the secrets accessible to the Dataform service agent. For more information, see Secrets access during package installation.

For more information, see Security considerations for Dataform permissions.

Set the default region for code assets

All new code assets in your Google Cloud project use a default region. After the asset is created, you can't change its region.

To set the default region for new code assets, do the following:

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the left pane, click Files to open the file browser:

    Click **Files** to open the file browser.

  3. Next to the project name, click View files panel actions > Switch code region.

  4. Select the code region that you want to use as a default.

  5. Click Save.

For a list of supported regions, see BigQuery Studio locations.

Encrypt with CMEK

You can encrypt your Dataform-backed saved queries with customer-managed encryption keys (CMEK) by using the default project-level CMEK key for the Dataform API. You can't apply individual CMEK keys to saved queries in Google Cloud console. New settings are applied only to newly created saved queries.

For more information and instructions on setting the default key, see Use CMEK to protect BigQuery Studio code assets.

Create saved queries

To create a saved query, follow these steps:

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

    Go to BigQuery

  2. Click SQL query.

  3. In the query editor, enter a valid SQL query. For example, you can query a public dataset:

    SELECT
  name,
  SUM(number) AS total
FROM
  `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
  name
ORDER BY
  total DESC
LIMIT
  10;

    Alternatively, you can use the Reference panel to construct new queries.

  4. Click Save > Save query or press Control+S (or Command+S on macOS).

  5. In the Save query dialog, type a name for the saved query.

  6. Optional: To change the region used by this saved query and all other code assets in the future, select a new region in the Region field.

  7. Click Save.

    The first version of the saved query is created.

  8. Optional: After you save the query, use the following toolbar to view the query details or the version history, add new comments, or reply to or get a link to an existing comment:

    Toolbar adjacent to the query editor.

    The Comments toolbar feature is in Preview. To provide feedback or request support for this feature, send an email to bqui-workspace-pod@google.com.

Share saved queries

To share a saved query with a user, you first grant that user access to the saved query and add them to an appropriate IAM role. Then you generate a link to the saved query and share that link with the user.

Users that you share a query with only see the most recent version of a query. Autosaved changes that you haven't explicitly saved don't appear in the shared query.

To share a saved query, follow these steps:

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

    Go to BigQuery

  2. In the left pane, click Files to open the file browser.

    If you don't see the left pane, click Expand left pane to open the pane.

  3. In the Files pane, expand your project and subfolders to find the saved query that you want to share.

  4. Click View actions next to the saved query, and then click Share > Manage permissions.

  5. In the Manage permissions pane, click Add user/group.

  6. In the New principals field, enter a principal.

  7. In the Role list, select one of the following roles:

  8. Optional: To view a complete list of roles and advanced sharing settings, click Advanced sharing.

  9. Click Save.

  10. To return to the saved query details, click Close.

  11. To generate a link to the saved query, click View actions > Share > Copy link.

    The link is copied to your clipboard.

Open a saved query version as a new query

To open any version of an existing saved query as a new query, follow these steps:

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

    Go to BigQuery

  2. In the left pane, click Files to open the file browser: