Quickstart using the classic web UI

You can use the classic web UI as a visual interface to complete tasks like running queries, loading data, and exporting data. This quickstart shows you how to query public tables and load sample data into BigQuery using the classic web UI.

Caution: BigQuery is moving to the Google Cloud Console. The classic web UI described in this document remains available through June 2020. The preferred option is the BigQuery web UI in the Cloud Console.

Before you begin

1. Sign in to your Google Account.

   If you don't already have one, sign up for a new account.

2. 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

3. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to Enable the BigQuery API.

   Enable the API

4. 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.

Query a public dataset

The classic web UI provides an interface to query tables, including public datasets offered by BigQuery.

1. Go to the classic web UI.

   Go to the classic web UI

2. Click the Compose query button.

3. Copy and paste the following query into the New Query text area:

   #standardSQL
   SELECT
     weight_pounds,
     state,
     year,
     gestation_weeks
   FROM
     `bigquery-public-data.samples.natality`
   ORDER BY
     weight_pounds DESC
   LIMIT
     10
   

4. Click the circular icon to activate the query validator.

   

   A green or red section displays above the buttons depending on whether the query is valid or invalid. If the query is valid, the validator also describes the amount of data to be processed after you run the query. This information is helpful for determining the cost to run a query.

5. Click the Run query button. The query results display below the buttons.

   

The above query accesses a table from a public dataset that BigQuery provides.

You can browse the other public datasets by clicking bigquery-public-data in the navigation pane.

Load data into a table

Next, you download some supplied data, you load the data into a BigQuery table, and you 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.

1. Download the baby names zip file.

2. Unzip the file onto your machine.

   The zip file contains a read me file that describes the dataset schema. Learn more about the dataset.

3. Open the file named yob2014.txt to see what it looks like. The file is a comma-separated value (CSV) file with the following three columns: name, sex (M or F), and number of children with that name. The file has no header row.

4. Note the location of the yob2014.txt file so that you can find it later.

Create a dataset

Next, create a dataset in the classic web UI to store the data.

1. Go to the classic web UI.

   Go to the classic web UI

2. Click the down arrow icon next to your project name in the navigation and click Create new dataset.

3. In the Create Dataset dialog:

   • For Dataset ID, enter babynames.
   • For Data location, choose US. Currently, the public datasets are stored in the US multi-region location. For simplicity, you should place your dataset in the same location.

   • For Data expiration, leave the default value — Never. When you set the data expiration to never, tables created in the dataset are never automatically deleted. You must delete them manually.

     

   • Click OK.

   Dataset IDs are unique on a per-project basis, so if babynames is already listed under your project name in the navigation, append a number to the name to make it unique. Click the question mark icon to see the field help.

Load the data into a new table

Next, load the data into a new table.

1. In the navigation, hover over the babynames dataset that you just created.

2. Click the down arrow icon next to the ID and click Create new table.

   

   Use the default values for all settings unless otherwise indicated.

3. For Source Data, click the Choose file button. Navigate to the data you unzipped previously, and select the yob2014.txt file.

4. For Destination Table, enter the following value for the destination table name.

   names_2014
   

5. In the Schema section, click the Edit as Text link.

   

   Then replace the contents of the Schema input area with the following schema definition:

   name:string,gender:string,count:integer
   

6. Click the Create Table button.

7. Wait for BigQuery to create the table and load the data. While BigQuery loads the data, a (loading) string displays after your table name in the navigation pane. The string disappears after the data is loaded.

Preview the table

After the (loading) string disappears, you can access the table. To preview the first few rows of the data:

1. Select names_2014 in the navigation pane.

2. Click Preview in the Table Details: names_2014 section.

   

Query the table

Now that you've loaded data into a table, you can run queries against it. The process is identical to the query a public dataset example above, except that this time, you're querying your table instead of a table in a public dataset.

1. Click the Compose query button.

2. Copy and paste the following query into the New Query text area.

   #standardSQL
   SELECT
     name,
     count
   FROM
     `babynames.names_2014`
   WHERE
     gender = 'M'
   ORDER BY
     count DESC
   LIMIT
     5
   

3. (Optional) For Processing Location, click Unspecified and choose US. When your dataset is in the US multi-region location, the processing location is automatically detected.

4. Click the Run query button. The query displays the top 5 men's names for the year of data you loaded into the table.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this quickstart, follow these steps.

1. If necessary, open the classic web UI.

   Go to the classic web UI

2. In the navigation, hover on the babynames dataset you created.

3. Click the down arrow icon next to your dataset name in the navigation pane and click Delete dataset. This action deletes the dataset, the table, and all the data.

4. In the Delete dataset dialog box, confirm the delete command by typing the name of your dataset (babynames) and then click OK.

What's next

• To learn more about the classic web UI, see Using the BigQuery classic 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 BigQuery API, see Create a simple application with the API.