Using the BigQuery classic web UI

Try the new BigQuery web UI and send us your feedback. Learn more about migrating to the Google Cloud Console.

Overview

BigQuery exposes two graphical web UI's that you can use to create and manage BigQuery resources and to run SQL queries: the BigQuery web UI in the Cloud Console and the classic BigQuery web UI. This page introduces the classic web UI.

For an introduction to using the web UIs, see one of the following:

Before you begin

Before you can use the classic BigQuery web UI, you must create or select a project using the Cloud Console.

Sign in to your Google Account.
If you don't already have one, sign up for a new account.
In the 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 the project selector page
BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to Enable the BigQuery API.
Enable the API
BigQuery provides a sandbox if you do not want to provide a credit card or enable billing for your project. The steps in this topic work for a project whether or not your project has billing enabled. If you optionally want to enable billing, see Learn how to enable billing.

Opening the classic web UI

To open the classic web UI, enter the following URL in your browser and replace project_id with the id of the project you're opening in the classic web UI:

https://bigquery.cloud.google.com/queries/project_id

Clicking the button below will open the web UI using your most recently accessed project.

Go to the classic web UI

Classic web UI overview

The classic web UI has three main sections:

The left side of the page (the navigation pane):

The top half of the navigation pane contains a list of items describing what action you want to perform: compose a query, see query history, or see job history.

The bottom half of the navigation pane contains a list of datasets in the current project that you have access to, plus the BigQuery public datasets. A filter box is available that allows you to filter by dataset ID or label.
The details pane:

Contains information appropriate to the selected action in the navigation pane. For example, if you click a dataset in the navigation pane, the details pane displays information about the dataset.
The query window:

When you click Compose Query, a New Query window is added above the details pane. This window can be closed when you no longer need it.

BigQuery web UI screenshot

Displaying resources

The navigation pane lists datasets that you can browse in the current project. Click the expand dataset icon icon next to any dataset or click the dataset's name to expand it and to show the tables within that dataset.

Clicking a table will show information about the table in the details pane. You can then click the Query Table button on the right side of the pane to populate the query box with a basic query for that table. You can modify the query by hand to specify fields or customize the query however you like. You are not limited to querying the currently selected table. For more information on querying tables, see Running interactive queries.

Adding and removing projects

All of your BigQuery work is done within a project. This project's name can be seen in the navigation pane above the list of datasets. All your work within the project is charged to the billing account attached to the project. For more information on BigQuery charges, see the Pricing page.

In the classic web UI, you can:

Add (or pin) additional projects
Remove pinned projects
Switch to a different project

Adding a project

You can add or "pin" a project in the classic web UI using the project drop-down menu.

To add a project to the classic UI:

Click the down arrow icon next to your project name in the navigation pane.
In the drop-down menu, click Switch to project > Display project.
In the Display Project dialog:
- Enter the project ID.
- Leave Display project in navigation panel selected.
- Click OK to add the project to the navigation panel.

Removing a project from the classic UI

To remove a project you previously added (or "pinned"):

Click the down arrow icon next to the project name in the navigation pane.
In the drop-down menu, click Remove project from panel.

Switching projects

If you prefer to switch to another project (instead of pinning it in the UI), you can use the context menu.

To switch projects:

Click the down arrow icon next to your project name in the navigation pane.
In the drop-down menu, click Switch to project and choose the project from the list. This replaces your previous project in the UI with the one you chose.

Adding a shared dataset

It is possible for another user to share a dataset with you without giving you access to the corresponding project. If that happens, you should receive an email with the name of the project that contains the shared dataset. The project and dataset do not automatically appear in the classic UI.

If you click the browser tool link in the email, the web UI opens with the project displayed. If you prefer to pin the project containing the shared dataset, follow the steps for Adding a project.

Viewing job and query history

As you run jobs and run queries using the classic web UI, your history is preserved in the navigation pane. Queries are also a type of job, but your query history is preserved separately for ease of use.

The job and query histories in the UI include all load, export, copy, and query jobs you submitted in the past 6 months (up to 1,000 entries). The limit of 1,000 jobs is cumulative across all job types.

Viewing job history

To view your job history, you can do one of the following:

Click the Job History link in the navigation pane.

Enter the following URL in your browser:

https://bigquery.cloud.google.com/jobs/project_id

You can repeat a load job by using the web UI, but you cannot repeat an export or copy job.

Viewing query history

To view your query history, you can do one of the following:

Click the Query History link in the navigation pane.

Enter the following URL in your browser:

https://bigquery.cloud.google.com/queries/project_id

You can repeat a query job by using the web UI.

Searching job and query history

You can filter (or search) your query history or job history to show only particular jobs that meet your criteria. The results returned are limited to the pool of jobs retrieved by the classic UI. By default, the classic UI allows you to search the 1,000 most recent jobs.

To list all available jobs in the history, use the jobs.list API method or the bq ls CLI command.

To filter your query or job history:

In the navigation pane, click either Job History or Query History.
Click the "filter jobs" drop-down to open the filter expression box.
In the filter expression box:
- Click Any to choose a filter criteria.
- Click the operator drop-down and choose an operator. Depending on the criteria you chose, the operator list changes. For example, if you choose a text criteria, the operator list includes substring and regex.
- Enter a value in the box.
Click And to add an optional, additional criteria to the filter.
Click Search.

You can filter query jobs using any of the following job properties:

Any - Any property.
Job ID (jobid) — The job's unique, opaque ID.
User (user) — The user that submitted the job.
Start Time (start) — Start time of the job, in milliseconds since the epoch. This property is present when the job transitions from the PENDING state to either RUNNING or DONE.
End Time (end) — End time of the job, in milliseconds since the epoch. This property is present whenever a job is in the DONE state.
Destination table (dest) — The name of the destination table for the job operation (load, copy, or query).
Query text — The text of the submitted SQL query.
Bytes processed — The total number of bytes processed by the SQL query.
UDF code — An inline resource that contains code for a user-defined function (UDF).

You can filter load, copy, and extract jobs using any of the following job properties:

Any - Any property.
Job ID (jobid) — The job's unique, opaque ID.
User (user) — The user that submitted the job.
Start Time (start) — Start time of the job, in milliseconds since the epoch. This property is present when the job transitions from the PENDING state to either RUNNING or DONE.
End Time (end) — End time of the job, in milliseconds since the epoch. This property is present whenever a job is in the DONE state.
Destination table (dest) — The name of the destination table for the job operation (load, copy, or query).
Title (title) — The title as it's displayed in the history, for example, the title of a load job might be: "uploaded file to myproject:mydataset.mytable".
Type (type) — The job type: load, query, copy, or extract (export).
Copy source (copysrc) — The source table in a copy job.
Destination URI (desturi) — The Cloud Storage destination URI for an extract (export) job.
Source table (srctable) — The source table in an extract job.
Source URI (srcuri) — The Cloud Storage source URI for a load job.

Sorting query history

You can also sort your query history in the classic UI by using the Sort By option in the Queries pane. The Queries pane opens when you click Query History in the navigation pane.

Query history Sort By button

Sorting options include:

Date — Sorts queries by the run date.
Duration — Sorts queries by total run time.
Duration/byte — Sorts queries by duration, normalized by input bytes. This allows you to compare the run time of queries with varying input sizes. For example, you might have a set of tables that grow over time and want to compare the efficiency of your queries while discounting the difference that is only caused by the change in input size. This assumes that the relationship between input size and runtime is linear.
Input bytes — Sorts queries by bytes read.
Slot-ms — Sorts queries by the sum of milliseconds active across all slots used by the query. Sorting by slot-ms is a more stable measure of processing effort because it is less affected by system delays and other sources of uncertainty that can impact the observed duration.
Slot-ms/byte — Similar to Duration/byte, Slot-ms/byte is a normalized form of slot-ms.

Classic UI display limits

The classic web UI has the following display limits:

Only 1,000 datasets can be displayed in the navigation pane.
Up to 30,000 tables are displayed in the navigation pane per project.

Controlling classic UI display performance

In rare cases, classic web UI performance can be impacted when a project has a large number of datasets that contain a large number of tables. To improve UI performance when your project contains a large number of resources, use the ?minimal parameter to limit the number of resources displayed to 10,000 tables per project. For example, enter the following URL in your browser to limit the number of resources displayed:

https://bigquery.cloud.google.com/queries/project_id?minimal.

Keyboard shortcuts

The following keyboard shortcuts are supported in the classic web UI:

Key combination (Windows/Linux)	Key combination (Mac OS)	Action
Ctrl + Space	Ctrl + Space	If no query is open: compose new query If query editor is open: autocomplete current word
Ctrl + Enter	Cmd + Enter	Run current query
Tab	Tab	Autocomplete current word
Ctrl	Cmd	Highlight table names
Ctrl + click on table name	Cmd + click on table name	Open table schema
Ctrl + E	Cmd + E	Run query from selection
Ctrl + /	Cmd + /	Comment current or selected line(s)
Ctrl + Shift + F	Cmd + Shift + F	Format query

Examples

You can find classic web UI examples throughout the How-to guides section of the BigQuery documentation. Below are links to common web UI tasks such as running queries and creating, getting, listing, deleting, and modifying BigQuery resources.

Querying data

For information on using the classic web UI to create and run queries, see:

Creating resources

For information on using the classic web UI to create resources, see:

Getting information about resources

For information on using the classic web UI to get information about resources, see:

Listing resources

For information on using the classic web UI to list resources, see:

Updating resources

For information on using the classic web UI to update resources, see:

Loading data

For information on using the classic web UI to load data, see:

Using external data sources

For information on using the classic web UI to query data in external data sources, see:

Exporting data

For information on using the classic web UI to export data, see:

Exporting data stored in BigQuery

Using the BigQuery Data Transfer Service

For information on using the classic web UI with the BigQuery Data Transfer Service, see:

Moving to the Google Cloud Console

The BigQuery web UI in the Google Cloud Console reached general availability (GA) in April, 2019. The Cloud Console provides consistency with the rest of Google Cloud and allows faster releases of new features. In addition, the Cloud Console has easy-to-use UI elements such as controls to sort and filter your query history and to share datasets.

Now that the BigQuery web UI in the Google Cloud Console has reached general availability, the BigQuery classic web UI no longer receives feature updates and support, excluding critical security fixes.

The migration schedule for the classic BigQuery web UI is as follows:

December 2019:

If you attempt to access the classic web UI, you will be redirected to the Cloud Console. Redirected users will be able to opt out of future redirects.
June 2020:

Beginning in June, 2020, you will only be able to use the BigQuery web UI in the Google Cloud Console.

Most features of the classic web UI are already supported in the Google Cloud Console. The following is a summary of the main differences between the classic web UI and the Cloud Console:

Classic web UI	Google Cloud Console
The default query syntax is legacy SQL. To enable standard SQL, you must use a standard SQL query prefix.	Queries are run in standard SQL by default, but you can switch back to legacy SQL using the Query Settings menu option. User-defined functions must be written in standard SQL.
Supports using Cloud Bigtable as an external data source.	You will be able to query Cloud Bigtable tables in the Cloud Console at least three months before the classic web UI is decommissioned.
Supports refreshing the list of resources in a project.	You will be able to refresh the list of resources in a project at least three months before the classic web UI is decommissioned.