Create queries with table explorer

Preview

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

To request support or provide feedback for this feature, email [e-mail].

This document shows you how to use table explorer to examine table data and create data exploration queries.

About table explorer

Table explorer offers an automated way to visually explore table data and create queries based on your selection of table fields.

In table explorer, you select table fields to examine. You can select up to 10 table fields at a time.

Table explorer displays the selected fields as interactive cards with the list of up to 10 most common values for each field, sorted by the count column. You can interact with the cards by selecting fields and distinct values which you want to examine more closely. Table explorer creates a data exploration query based on your selection.

You can copy this query into a new query in query editor, or apply the query in table explorer. When you apply the query, table explorer runs it and refreshes the displayed cards with query results. To continue table data exploration, select more fields or values from the refreshed cards.

Limitations

• Table explorer is available for BigQuery tables, BigLake tables, external tables, and views.
• Table explorer lets you explore a single table at a time. The feature does not support simultaneous exploration of multiple tables or generating cross-table operations, for example, JOIN operations.
• Table explorer creates SQL queries that directly reflect your selection of table fields and distinct values. You can execute queries created by table explorer or manually edit them in the query editor. Table explorer does not provide AI-powered assistance to generate, complete, or explain SQL queries.
• To explore table data and generate queries for tables with column-level access control (ACLs) or restricted user permissions, you must have read access for all selected fields. To run the generated queries, you must have sufficient permissions.

Pricing

Table explorer runs queries based on your selection of table fields and distinct values to display table exploration results. These queries incur compute pricing charges. Table explorer displays the amount of data that will be processed for each query before you confirm your selection of table fields, triggering the query execution.

You can also incur compute charges if you run a query generated by table explorer.

For more information about BigQuery compute pricing, see Pricing.

Before you begin

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. Enable the BigQuery API.

   Enable the API

5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

6. Make sure that billing is enabled for your Google Cloud project.

7. Enable the BigQuery API.

   Enable the API

Required roles and permissions

To get the permissions that you need to view table data and generate queries with table explorer, ask your administrator to grant you the following IAM roles:

• BigQuery Job User (roles/bigquery.jobUser) on the project.
• BigQuery Data Viewer (roles/bigquery.dataViewer) on all tables and views that you want to explore.

For more information about granting roles, see Manage access.

These predefined roles contain the permissions required to view table data and generate queries with table explorer. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to view table data and generate queries with table explorer:

• bigquery.jobs.create on the project from which the query is being run, regardless of where the data is stored.
• bigquery.tables.getData on all tables and views that you want to explore.

You might also be able to get these permissions with custom roles or other predefined roles.

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

Explore data in a table to create a query

To explore table data and create a query based on your selection of table fields and values, follow these steps:

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

   Go to BigQuery Studio

2. In the Explorer pane, select the table for which you want to create a query.

3. Click the Table explorer tab, and then click Select fields.

4. In the Select fields pane, select up to 10 table fields to explore.

5. For a partitioned table, in the Partitioning filter section, set a custom partitioning filter. Partition filters can reduce the billable compute when exploring tables.

   1. Select Apply custom partitioning filter.

   2. In the displayed settings fields, configure the partitioning filter.

   Display of filter settings depends on the partition type of the table: hour, day, month, year, or range.

6. Click Save.

   When you click Save, BigQuery runs a query to display common values for the selected fields, which incurs charges. You can see the amount of data which will be processed at the top of the Select fields pane.

   Table explorer displays the selected fields as cards in a list of up to the ten most common values sorted by the Count column. In the Generated Query section, you can see a query which you can run to show the same data.

7. Optional: To modify your results, you can try the following:

   1. In the displayed selected field cards, select distinct values to further filter the data.
   2. To revert all changes, click Reset.
   3. In the Generated Query section, click Copy to query to copy the generated code into a new, untitled query in the query editor. In the newly created query tab, you can edit, run, and manage the query.

8. To run the generated query, click Apply.

   BigQuery executes the generated query and refreshes displayed cards with results of the query.

9. To continue table exploration, select new fields or distinct values from the refreshed displayed cards.

Troubleshooting

Access Denied: Project [project_id]: User does not have bigquery.jobs.create
permission in project [project_id].

This error occurs when a principal lacks permission to create a query jobs in the project.

Resolution: An administrator must grant you the bigquery.jobs.create permission on the project you are querying. This permission is required in addition to any permission required to access the queried data.

For more information about BigQuery permissions, see Access control with IAM.

What's next

• Learn how to explore data with data insights.
• Learn how to write queries with Gemini assistance in BigQuery.