Using BigQuery In The Google Cloud Console

Open BigQuery

1. The BigQuery console provides an interface to query tables, including public datasets offered by BigQuery.
2. In the Google Cloud Console, select Navigation menu > BigQuery:
   
3. The Welcome to BigQuery in the Cloud Console message box opens. This message box provides a link to the quickstart guide and the release notes.
4. Click Done.

Query a public dataset

In this section, you load a public dataset, USA Names, into BigQuery, then query the dataset to determine the most common names in the US between 1910 and 2013.

Load USA Name dataset

1. In the left pane, click ADD DATA > Explore public datasets.
   
   The Datasets window opens.
2. In the searchbox, type “USA Names” then enter.
3. Click on the USA Names tile you see in the search results.
   
4. Click VIEW DATASET.
   BigQuery opens in a new browser tab. The project bigquery-public-data is added to your resources and you see the dataset usa_names listed in the left pane in your Explorer section.
   

Query the USA Name dataset

Query bigquery-public-data.usa_names.usa_1910_2013 for the name and gender of the babies in this dataset, and then list the top 10 names in descending order.

1. Copy and paste the following query into the query EDITOR 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
2. In the upper right of the window, view the query validator.
   
3. Click Run.
   The query results opens below the Query editor. At the top of the Query results section, BigQuery displays the time elapsed and the data processed by the query. Below the time is the table that displays the query results. The header row contains the name of the column as specified in GROUP BY in the query.
   

Create a custom table

In this section, you create a custom table, load data into it, and then run a query against the table.

Download the data to your local computer

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 to your local computer.
2. Unzip the file onto your computer.
3. The zip file contains a NationalReadMe.pdf file that describes the dataset. Learn more about the dataset.
4. Open the file named yob2014.txt to see what the data 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.
5. Note the location of the yob2014.txt file so that you can find it later.

Create a dataset

In this section, you create a dataset to hold your table, add data to your project, then make the data table you’ll query against.

Datasets help you control access to tables and views in a project. This lab uses only one table, but you still need a dataset to hold the table.

1. Back in the console, in the Explorer section, click on the View actions icon next to your project ID and select Create dataset.
   
   2. On the Create dataset page:
   • For Dataset ID, enter babynames.
   • For Data location, choose United States (US).
   • For Default table expiration, leave the default value.
2. Currently, the public datasets are stored in the US multi-region location. For simplicity, place your dataset in the same location.
   
3. Click Create dataset at the bottom of the panel.

Load the data into a new table

In this section, you load data into the table you made.

1. Create a table by clicking on the View actions icon next to your babynames dataset in the Explorer section. Select Open, then click Create table.
   Use the default values for all settings unless otherwise indicated.
2. On the Create table page:
   1. For Source, choose Upload from the dropdown menu.
   2. For Select file, click Browse, navigate to the yob2014.txt file and click Open.
   3. For File format, choose CSV from the dropdown menu.
   4. For Table name, enter names_2014.
   5. In the Schema section, click the Edit as text toggle and paste the following schema definition in the text box.
      name:string,gender:string,count:integer

3. Click Create table (at the bottom of the window).
4. 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 left pane. The string disappears after the data is loaded.

Preview the table

1. In the left pane, select babynames > names_2014 in the navigation panel.
2. In the details panel, click the Preview tab.
   

Query the table

Now that you’ve loaded data into your 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.

1. In the Query editor, click Compose new query.
2. Copy and paste the following query into the query EDITOR. 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
3. Click Run. The results are displayed below the query window.