BigQuery exposes a graphical web UI that you can use to load and export data, run queries, and perform other user and management tasks in your browser.
The web UI runs best in the Chrome web browser.
See the BigQuery Web UI Quickstart for a working introduction to the web UI.
The BigQuery web UI has three main sections:
- The left side of the page (the navigation bar):
The top half of the navigation bar 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 bar contains a list of datasets in the current project that you have at least read access to, plus a public dataset called publicdata:samples, that holds public data that BigQuery exposes to all users. Each entry is a separate dataset, prefixed by the project name in the syntax projectId:datasetId. Click the icon next to any dataset or the dataset's name to expand it and show the tables within that dataset.
- The top of the page:
After clicking the Compose Query button, contains a query box where you can type a query in the BigQuery SQL syntax. To run a query, click the Run Query button next to the query box.
- The rest of the page:
Contains information appropriate to the selected action in the navigation bar.
Note: The BigQuery web UI is still changing, and some of the page elements or text mentioned in this document might not match the current version of the tool.
The left side of the page lists datasets that you can browse. Click the icon next to a dataset or the dataset's name to reveal the tables in that dataset. If you have read access to a dataset, you have read access to all tables in that dataset.
Clicking on a table will show a description of the table schema in the main section of the screen. You can then click the Query Table button on the right side of the main view 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.
All of your work is billed to a specific project. This project's name can be seen in the left navigation above the list of datasets. You can browse a list of projects that you have access to by selecting the display menu icon next to your project name on the left of the screen and clicking Switch to Project. You can click on any of these projects to switch to billing your work to this new project.
View a shared dataset
It is possible for another user to share a dataset with you but not add you as a user to the corresponding Google Cloud Platform Console project. If that happens, the dataset does not automatically appear in the projects list, and you need to manually add it using the Display Project option.
To manually add a dataset:
- Select the display menu icon next to the project name.
- Hover over the Switch to Project option.
- Click the Display Project... option.
- On the popup that appears, enter the project ID of the dataset that was shared with you.
Datasets shared with you in that project now appear at the bottom of the dataset list.
Add or delete datasets
The list on the left side of the screen is a list of datasets that you have read, write, or owner access to.
To create a new dataset:
- Click the display menu icon next to the project name and select Create new dataset.
- Choose a dataset name that isn't already listed on the left side navigation bar. The name can be up to 1,024 characters long, and consist of A-Z, a-z, 0-9, and _ (the underscore character), but it cannot start with an underscore or have spaces.
- Click OK to create the dataset.
To delete a dataset:
- Click the display menu icon and select Delete dataset.
Share a dataset
- Click the display menu icon next to the dataset.
- Click Share dataset to open the manage collaborators dialog.
- Add new users by Google ID and specify what permissions they have on the dataset, then click the Add button. Note that you must grant "Can Edit" permissions on a dataset for users to be able to load data or create tables. You can also share with a group or all users using the role dropdown to the left of the e-mail input box.
- Optionally remove or change permissions of existing users (if you are the dataset owner).
- Click Save changes when you're done making changes.
Create a new table
You can load a file as a new table. Loading to an existing table will append the results to the existing table content.
To load a file:
- Select the display menu icon next to the dataset in which to create your table and click Create new table.
- In the Source Data section, specify the file location:
- If loading a local file, select Choose file and browse to the CSV file you want to upload.
- If loading from Google Cloud Storage, select Google Cloud Storage and fill in the file path in the Google Storage URI textbox. If you've forgotten the file path, you can view your files by clicking the View files link, which takes you to the Google Cloud Storage browser.
- Next, specify the file format. Click the File format selector button to select a supported file format.
In the Destination Table section:
- Enter a name for the new table. The destination table name must be unique in that dataset, be from 1-1024 characters long, and contain only a-z, A-Z, 0-9 or _ (the underscore character). You can also change the dataset using the dataset selector button that precedes the table name field.
- For Table type, choose Native table if you want to store your table using BigQuery storage. Choose External table if you want to use an external data source.
- In the Schema section, enter the schema of the file. You can use the schema builder
provided, or click Edit as text and enter the schema as a string.
In string format, the schema
is described as
The values "column1_name" and "column2_name" are labels assigned to the columns in your new table and "data_type_1" and "data_type_2" are data types for the data you are uploading.
- Click Create Table to load your data.
Copy an existing table
To copy a table:
- Click the display menu icon next to the table you want to copy and click Copy Table. You must have
READpermission to the source dataset of the table being copied, and
WRITEpermission to the destination dataset.
- Select the dataset where you want to store the new table.
- Enter a name for the new table. The name must be unique in the dataset where the table will belong. The table name can be up to 1024 characters long, and can contain only a-z, A-Z, 0-9 or _ (the underscore character).
- Click OK to start the copy job.
Append data to a table
You can append data to a table by specifying it as the destination of a load operation, or by specifying "Append Table" write disposition within the query options when running a query.
Delete a table
To delete a table, click the display menu icon next to a table that you have write rights to and click Delete Table.
Examine a table's schema
To see a table's schema, select a table from an open dataset on the left side of the screen.
Examine a table's data
You can preview data within a table by clicking on the table in the navigation panel and then clicking on the Details tab on the right side of the main view.
To run a query, click Compose Query at the top of the navigation bar and enter the query string in the resizable box at the top of the page. When finished composing the query text, click Run Query to execute the query. You can re-run the query by clicking Run Query again.
You can create queries that access multiple datasets or projects by qualifying the table names using the syntax projectId:datasetId.tableId. If you do not specify a project ID, the current project is assumed.
Download, save, and export data
If a query result set has fewer than 16,000 rows and is 10MB or smaller, you can download it to your local machine as a CSV or newline-delimited JSON file. Otherwise, you can only save it as a table.
Download query results
To download query results as a CSV file:
- Click the Download as CSV button above the query results.
To download query results as a JSON file:
- Click the Download as JSON button above the query results.
Save query results
To save query results as a table:
- Click the Save as Table button above the query results.
To save query results to Google Sheets:
- Click the Save to Google Sheets button above the query results. When saving to Google Sheets, the result set must have fewer than 16,000 rows and must be 10MB or smaller.
You can also export an entire table to Google Cloud Storage.
To export an entire table:
- Select the table to export, then click the display menu icon next to the table.
- Click Export table to open the export table dialog.
- Enter the desired output format and a fully-qualified Google Cloud Storage URI for the exported file. You must have write access on the target bucket.
View query history
You can see a complete history of all your most recent queries by clicking Query History in the navigation bar. To re-run a query on the history page, click to expand the query and click Run Query.
You can also sort your query history using the Sort By option.
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.
Only 1,000 datasets can be displayed in the UI. For each dataset, only 1,000 tables are displayed in the navigation panel. To see more tables, click on the dataset name in the navigation panel to open the Dataset Details page. The Dataset Details page shows up to 30,000 tables.
In rare cases, performance can be impacted when a project has a large number of datasets that contain a large number of tables. To improve performance for that specific scenario, use the "?minimal" parameter to limit the load operation to 30,000 tables per project. For example:
The following keyboard shortcuts are supported:
|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.|