Google BigQuery

BigQuery Browser Tool

BigQuery exposes a graphical web interface that you can use to load and export data, run queries, and perform other user and management tasks in your browser.

The browser tool runs best in the Chrome web browser.

Contents

Basics

The BigQuery browser tool 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: run 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 display menu icon icon next to any dataset to expand it to show the tables within that dataset.

  • The top of the page:

    In query view, 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 top left of the navigation bar.

Note: The BigQuery browser tool is still changing, and some of the page elements or text mentioned in this document might not match the current version of the tool.

Browse Tables

The left side of the page lists datasets that you can browse. Click the display menu icon next to a dataset 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, and also 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.

Switch Projects

All of your work is billed to a specific project. 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. The list of available datasets can change depending on which project you select, since you must have read access to a project to be able to run queries in that project.

Switch to 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 APIs 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 Projects option.

To manually add a dataset:

  1. Select the display menu icon next to the project name in browser tool.
  2. Hover over the Switch to Project option.
  3. Click the Display Project... option.
  4. On the popup that appears, enter the project ID of the dataset that was shared with you.

Your shared dataset now appears at the bottom of the dataset list.

Create and Populate a Table

You can load an uncompressed CSV local file or a compressed (gzipped) or uncompressed CSV file as a new table. You cannot load a CSV file and append the results to an existing table.

To load a CSV file:

  1. Select the display menu icon next to the dataset in which to create your dataset and click Create new table to open the "Create Table" dialog.
  2. Select File upload to upload from your local computer, or Google Storage URI to upload from Google Cloud Storage.
  3. In Table ID, enter an ID for the new table. The table ID must be unique in that dataset, be from 1-1024 characters long, and contain only a-zA-Z0-9 or _ (the underscore character), and the first character must be a letter.
  4. In Schema, enter the schema of the file. Schema is described as column1_name:data_type_1,column2_name:data_type_2,...
    The values "column1_name" and "column2_name" are labels assigned to the columns in your new table.
  5. Specify the file location:
    • If loading a local file, select Choose file and browse to one of the yob****.txt files that you downloaded and unzipped.
    • If loading from Google Cloud Storage, fill in the file path in the Google Storage URI textbox.
  6. Click OK to load your data.

Copy an Existing Table

To copy a table:

  1. Click the display menu icon next to the table you want to copy and click Copy Table. You must have READ permission to the source dataset of the table being copied, and WRITE permission to the destination dataset.
  2. Select the dataset where you want to store the new table.
  3. 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 charaters long, and can contain only a-zA-Z0-9 or _ (the underscore character) characters, and the first character must be a letter.
  4. Click OK to start the copy job.

Append Data to a Table

It is not possible to append data to a table through the browser tool, but you can append data to a table using the API by setting the writeDisposition of your job to WRITE_APPEND. For more information, review the API reference for your desired job.

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.

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:

  1. Click the display menu icon next to the project name in the browser tool and select Create new dataset.
  2. 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, but it cannot start with an underscore or have spaces.
  3. Click Create

You have automatic owner privileges on any workspaces that you create.

To delete a dataset:

  1. Click the display menu icon next to the project name in the browser tool and select Delete dataset.

Share a Dataset

  1. Click the display menu icon next to the dataset.
  2. Click Share dataset to open the manage collaborators dialog.
  3. Add new users by Google ID and specify what permissions they have on the dataset. Note that you must grant write permission on a dataset for users to be able to load data or create tables. However, you can run queries against that dataset in the browser tool.
  4. Optionally remove or change permissions of existing users (if you are the dataset owner).
  5. Click Save changes when you're done making changes.

Run a Query

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 with the query selected.

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.

Examine a Table Schema

To see table schema, select a table from the open dataset on the left side of the screen.

Examine Table Data

You can preview the first few rows in a table by clicking on the table in the navigation panel and then clicking on Click to preview table data.

The only way to see all the data in a table using the browser tool is to run a SELECT query against it. You must specify all columns manually; the BigQuery syntax does not support the * wildcard to indicate all fields.

Downloading, Saving, and Exporting Data

If a query result set has fewer than 16,000 rows, you can download it as a CSV file. If it has more than that number of rows, you can only save it as a table.

You can also export an entire table to Google Cloud Storage.

To download a query as a CSV file:

  1. Click the Download as CSV button above the query results.

To save query results as a table:

  1. Click the Save as Table button above the query results

To export an entire table:

  1. Select the table to export, then click the Actions button on the right side of the screen, and select Export...
  2. Enter the fully-qualified Google Cloud Storage address for the exported file. You must have write access on the target bucket.

See 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, select the query and click Run Query.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.