You can use the BigQuery web UI as a visual interface to complete tasks like running queries, loading data, and exporting data. This quickstart shows you how to query tables in a public dataset and how to load sample data into BigQuery using the BigQuery web UI.
Before you begin
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
Select or create a GCP project.
Make sure that billing is enabled for your project.
- BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to Enable the BigQuery API.
Query a public dataset
The BigQuery web UI provides an interface to query tables, including public datasets offered by BigQuery.
In this example, you query the USA Name Data public dataset to determine the most common names in the US between 1910 and 2013.
BigQuery public datasets are not displayed by default in the BigQuery web UI. To open the public datasets project, enter the following URL in your browser.
Once you've opened the project, pin it.
To query data in a public dataset:
Go to the BigQuery web UI.
At the top right of the window, click Compose New Query.
Copy and paste the following query into the query text area.
SELECT name, gender, SUM(number) AS total FROM `bigquery-public-data.usa_names.usa_1910_2013` GROUP BY name, gender ORDER BY total DESC LIMIT 10
In the lower right of the window, view the query validator.
A green check mark icon is displayed if the query is valid. If the query is invalid, a red exclamation point icon is displayed. If the query is valid, the validator also shows the amount of data the query will process when you run it. The data processed is helpful for determining the cost of running the query.
Click Run query. The query results page displays below the query window. At the top of the query results page, the time elapsed and the data processed by the query are displayed. Below the
Query complete...message, a table displays the query results with a header row containing the name of each column you selected in the query.
The above query accesses a table from a public dataset that BigQuery provides. You can browse other public datasets by opening the bigquery-public-data project. To open the public data project, enter the following URL in your browser:
For more information, see BigQuery public datasets.
Load data into a table
Next, you load data into a table and query it.
Download the data
The file you're downloading contains approximately 7 MB of data about popular baby names, and it is provided by the US Social Security Administration.
Download the baby names zip file.
Unzip the file onto your machine.
The zip file contains a
NationalReadMe.pdffile that describes the dataset. Learn more about the dataset.
Open the file named
yob2014.txtto see what the data looks like. The file is a comma-separated value (CSV) file with the following three columns: name, sex (
F), and number of children with that name. The file has no header row.
Note the location of the
yob2014.txtfile so that you can find it later.
Create a dataset
Next, create a dataset in the web UI to store the data.
If necessary, open the BigQuery web UI.
In the navigation panel, in the Resources section, click your project name.
On the right side, in the details panel, click Create dataset.
On the Create dataset page:
- For Dataset ID, enter
For Data location, choose United States (US). Currently, the public datasets are stored in the
USmulti-region location. For simplicity, you should place your dataset in the same location.
- For Dataset ID, enter
Leave all of the other default settings in place and click Create dataset.
Load the data into a new table
Next, load the data into a new table.
In the navigation panel, in the Resources section, click the babynames dataset that you just created.
On the right side, in the details panel, click Create table.
Use the default values for all settings unless otherwise indicated.
On the Create table page:
- For Source Data, click Empty table and choose Upload.
- For Select file, click Browse, navigate to the
yob2014.txtfile and click Open.
- For File format, click Avro and choose CSV.
- For Destination table, enter
In the Schema section, click the Edit as text toggle and paste the following schema definition in the box.
Click Create Table.
Wait for BigQuery to create the table and load the data. While BigQuery loads the data, a (1 running) string displays beside the job history in the navigation panel. The string disappears after the data is loaded.
Preview the table
After the (1 running) string disappears, you can access the table. To preview the first few rows of the data:
Select babynames > names_2014 in the navigation panel.
In the details panel, click the Preview tab.
Query the table
Now that you've loaded data into a table, you can run queries against it. The process is identical to the previous example, except that this time, you're querying your table instead of a public table.
If necessary, click the Compose new query button. Unless you hid the query window previously, it should still be visible.
Copy and paste the following query into the query text area. This query retrieves the top 5 baby names for US males in 2014.
SELECT name, count FROM `babynames.names_2014` WHERE gender = 'M' ORDER BY count DESC LIMIT 5
Click Run query. The results are displayed below the query window.
To avoid incurring charges to your GCP account for the resources used in this quickstart:
If necessary, open the BigQuery web UI.
In the navigation panel, in the Resources section, click the babynames dataset you created.
In the details panel, on the right side, click Delete dataset. This action deletes the dataset, the table, and all the data.
In the Delete dataset dialog box, confirm the delete command by typing the name of your dataset (
babynames) and then click Delete.
To learn more about the BigQuery web UI, see BigQuery Web UI.
To learn how to load a JSON file with nested and repeated data, see Loading nested and repeated JSON data on the Loading JSON Data from Google Cloud Storage page.
To learn more about loading data, see Loading Data Into BigQuery.
To learn more about querying data, see Querying Data.
To learn how to create an application using the Google BigQuery API, see Create A Simple Application With the API.