Exploring a BigQuery Public Dataset

Query a public dataset
1. In the left pane, click ADD DATA > Explore public datasets.
2. In the search box, type USA Names then press 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 Resources tree.
5. 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.
  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
```
6. In the upper right of the window, view the query validator.
  BigQuery displays a green check mark icon if the query is valid. If the query is invalid, a red exclamation point icon is displayed. When the query is valid, the validator also shows the amount of data the query processes when you run it. This helps to determine the cost of running the query.
7. 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
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.
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
1. Back in the Cloud Console, in the left pane, in the Explorer section, click your Project ID
2. Click on the three dots next to your project ID and then click Create dataset.
3. On the Create dataset page:
  1. For Dataset ID, enter babynames.
  2. For Data location, choose United States (US).
  3. For Default table expiration, leave the default value.
  4. For Encryption, leave the default value.
  5. Click Create dataset at the bottom of the pane.
Load the data into a new table
1. In the navigation pane, click babynames from the Explorer section, and then click on the three dots next to babynames and then click Open.
2. Click on Create table in the right side pane.
3. On the Create table page:
  1. For Source, choose Upload from the Create table from: 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.
4. Click Create table (at the bottom of the window).
5. 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 below pane. The string disappears after the data is loaded.
6. In the left pane, select babynames > names_2014 in the navigation pane.
7. In the details pane, click the Preview tab.
Query the 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.

Tutorial

Exploring a BigQuery Public Dataset

AZ-700 Study Guide Azure Network Engineer

How to Install and Configure Ansible on CentOS 7

Courses

About

Tutorial

You may also like

Courses

About

Login with your site account

Register a new account