Create queries with table explorer

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

To request support or provide feedback for this feature, emailbq-studio-product-team@google.com.

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

About table explorer

Table explorer offers an automated way to visually explore table data andcreate 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 fieldsas interactive cards with the list of up to 10 most common values foreach field, sorted by thecount column. You can interact with the cards byselecting 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 explorerruns it and refreshes the displayed cards with query results. To continue tabledata 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 doesnot support simultaneous exploration of multiple tables orgenerating cross-table operations, for example,JOIN operations.
• Table explorer creates SQL queries that directly reflect yourselection of table fields and distinct values. You can executequeries 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-levelaccess control (ACLs) or restricted user permissions, you must have read accessfor all selected fields. To run the generated queries, you musthave sufficientpermissions.

Pricing

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

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

For more information about BigQuery compute pricing, seePricing.

Before you begin

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.

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

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

Verify that billing is enabled for your Google Cloud project.

Enable the BigQuery API.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the API

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

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

Verify that billing is enabled for your Google Cloud project.

Enable the BigQuery API.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

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, seeManage access to projects, folders, and organizations.

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 theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information about BigQuery Identity and Access Management (IAM),seeAccess 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 tablefields and values, follow these steps:

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

   Go to BigQuery Studio

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand your project and then clickDatasets.

4. ClickOverview> Tables, and then select a table for which youwant to create a query.

5. Click theTable explorer tab, and then clickSelect fields.

6. In theSelect fields pane, select up to 10 table fields to explore.

7. For a partitioned table, in thePartitioning filter section,set a custom partitioning filter. Partition filters can reduce the billablecompute when exploring tables.

   1. SelectApply 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.

8. ClickSave.

   When you clickSave, BigQuery runs a query todisplay common values for the selected fields, which incurs charges.You can see the amount of data which will be processed at the top of theSelect fields pane.

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

9. 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, clickReset.
   3. In theGenerated Query section, clickCopy to query to copy thegenerated code into a new, untitled query in the query editor. In the newlycreated query tab, you can edit, run, and manage the query.

10. To run the generated query, clickApply.

    BigQuery executes the generated query and refreshesdisplayed cards with results of the query.

11. To continue table exploration, select new fields or distinct values fromthe refreshed displayed cards.

Troubleshooting

Access Denied: Project [project_id]: User does not have bigquery.jobs.createpermission 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 thebigquery.jobs.createpermission on the project you are querying. This permission is required inaddition to any permission required to access the queried data.

For more information about BigQuery permissions, seeAccess control with IAM.

What's next

• Learn how toexplore your data by generating data insights.
• Learn how towrite queries with Gemini assistance in BigQuery.
• Learn how to iterate on query results with natural language questions by usingdata canvas.