Create saved queries

This document describes how to create saved queries and classic saved queries.

Saved queries

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

For support during the preview, email [e-mail].

Use the following sections to learn how to create saved queries. Saved queries are BigQuery Studio code assets powered by Dataform.

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

Enable BigQuery Studio

Follow the instructions at Enable BigQuery Studio for asset management to save, share, and manage versions of code assets such as saved queries.

Required permissions

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

All users with the Dataform Admin (roles/dataform.admin) role 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:

• resourcemanager.projects.get
• resourcemanager.projects.list
• bigquery.config.get
• bigquery.jobs.create
• bigquery.readsessions.create
• bigquery.readsessions.getData
• bigquery.readsessions.update
• resourcemanager.projects.get
• resourcemanager.projects.list
• dataform.locations.get
• dataform.locations.list
• dataform.repositories.create
• dataform.repositories.list
• dataform.collections.create
• dataform.collections.list

You can get these permissions from the following IAM roles:

• Bigquery Job User (roles/bigquery.jobUser)
• Bigquery Read Session User (roles/bigquery.readSessionUser)

• Code Creator (roles/dataform.codeCreator)

  Note: Users assigned the Code Creator role in a project can list the names of code assets in that project by using the Dataform API or the Dataform command-line interface (CLI). In preview, non-administrators using BigQuery Studio can only see code assets that they created or that were shared with them.

Permissions to edit saved queries

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

• Bigquery Job User (roles/bigquery.jobUser)
• Bigquery Read Session User (roles/bigquery.readSessionUser)
• Code Editor (roles/dataform.codeEditor)

Permissions to view saved queries

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

• Bigquery Job User (roles/bigquery.jobUser)
• Bigquery Read Session User (roles/bigquery.readSessionUser)
• Code Viewer (roles/dataform.codeViewer)

Set the default region for code assets

If this is the first time you are creating a code asset, set the default region for code assets. You can't change the region for a code asset after it is created.

Note: If you create a saved query and choose a different default region than the one you have been using for code assets, for example choosing us-west1 when you have been using us-central1, then that query and all code assets you create afterwards use that new region by default. Existing code assets continue to use the region they were assigned when they were created.

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 Compose a new 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;
   

4. Click Save Query > Save query.

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

7. Click Save.

Grant access to saved queries

To grant other users access to a saved query, add those users to an appropriate IAM role.

1. In the Explorer pane, expand the Queries folder and if necessary the Shared queries folder. Find the saved query you want to grant access to.
2. Click View actions next to the saved query and then click Share > Manage Permissions.
3. Click Add Principal.
4. In the New principals field, enter a principal.

5. In the Select a role list, select Dataform and then select one of the following roles:

   • Code Owner: Can perform any action on the saved query, including deleting or sharing it.
   • Code Editor: Can edit the query.
   • Code Viewer: Can view the query.
   Note: The principal must also have the BigQuery User (roles/bigquery.user) role to run the saved query.

6. Click Save.

7. To return to the saved query info, click Close.

Share saved queries

You can share a saved query with other users by generating and sharing a link to the saved query. Before users can see the saved query you share, you must first grant them access to the saved query.

To run a shared query, users must have access to the data that the query accesses. For more information, see Grant access to a dataset.

If you plan to share a saved query, consider including a comment in the query that describes its purpose.

1. In the Explorer pane, expand the Queries folder and, if necessary, expand the Shared queries folder. Find the saved query you want to share.
2. Click View actions next to the query and then click Share > Get link.
3. Share the link with other users.

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 Explorer pane, expand the Queries folder and if necessary the Shared queries folder. Select a saved query.
2. Select the Activity pane.
3. Click View actions next to a saved query version and then click Open as new query.

Update saved queries

If you update a saved query, you can save the changes in a new version of the query.

1. In the Explorer pane, expand the Queries folder and if necessary the Shared queries folder. Click the name of a saved query to open it.
2. Modify the query.

3. To save the modified query, click Save Query > Save query.

   A new version of the query is created, which you can see in the Activity pane.

Classic saved queries

Use the following sections to learn how to create and update classic saved queries. For more information on sharing, migrating, and deleting classic saved queries, see Classic saved queries.

Note: If you are not enrolled in the BigQuery Studio preview, classic saved queries appear in the Saved queries (NUMBER) folder of the Explorer pane instead of the (Classic) Queries folder.

Required permissions for classic saved queries

The following IAM permissions are required to create, view, update, and delete classic saved queries:

• Private classic saved queries:
  • Creating private classic 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 classic saved queries:
  • Creating a project-level classic saved query requires bigquery.savedqueries.create permissions. The bigquery.admin predefined role includes bigquery.savedqueries.create permissions.
  • Viewing a project-level classic 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 classic saved query requires bigquery.savedqueries.update permissions. The bigquery.admin predefined role includes bigquery.savedqueries.update permissions.
  • Deleting a project-level classic saved query requires bigquery.savedqueries.delete permissions. The bigquery.admin predefined role includes bigquery.savedqueries.delete permissions.
• Public classic saved queries:
  • Creating public classic saved queries requires no special permissions. You can save a public classic saved query in any project, but only you can update or delete the query. Anyone with the link can view a public classic saved query.

For more information on IAM roles in BigQuery, see Predefined roles and permissions.

Create classic 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 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;
   

4. Click Save Query (Classic) > Save query (Classic).

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 clasic shared query.
   • Project (editable by principals with appropriate permissions) for a project-level classic saved query.
   • Public for a public classic saved query.

6. Click Save.

Share classic saved queries

You can share classic saved 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.

You share a classic saved query with other users by generating and sharing a link to the classic saved query.

To run a classic shared query, users must have access to the data that the query accesses. For more information, see Grant access to a dataset.

If you are plan to share a classic saved query, consider including a comment in the query that describes its purpose.

1. In the Explorer pane, expand the (Classic) Queries folder and find the classic saved query you want to share.
2. Click View actions next to the query and then click Get link.
3. Share the link with the users you want to grant access to the query.

Update classic saved queries

1. In the Explorer pane, expand the (Classic) Queries folder and if necessary the Project queries folder.
2. Click the name of a classic saved query to open it.
3. Modify the query.
4. To save the modified query, click Save Query (Classic) > Save query (Classic).

What's next

• Learn how to manage saved queries.