Load and query data with the bq tool

Download the file that contains the source data

The file that you're downloading contains approximately 7 MB of data about popular baby names. It's provided by the US Social Security Administration.

For more information about the data, see the Social Security Administration's Background information for popular names.

1. Download the US Social Security Administration's data by opening the following URL in a new browser tab:

   https://www.ssa.gov/OACT/babynames/names.zip
   

2. Extract the file.

   For more information about the dataset schema, see the NationalReadMe.pdf file you extracted.

3. To see what the data looks like, open the yob2024.txt file. This file contains comma-separated values for name, assigned sex at birth, and number of children with that name. The file has no header row.

4. Move the file to your working directory.

   • If you're working in Cloud Shell, click more_vert More Upload, click Choose Files, choose the yob2024.txt file, and then click Upload.

   • If you're working in a local shell, copy or move the file yob2024.txt into the directory where you're running the bq tool.

Create a dataset

1. If you launched Cloud Shell from the documentation, enter the following command to set your project ID. This prevents you from having to specify the project ID in each CLI command.

   gcloud config set project PROJECT_ID
   

   Replace PROJECT_ID with your project ID.

2. Enter the following command to create a dataset named babynames:

   bq mk --dataset babynames
   

   The output is similar to the following:

   Dataset 'babynames' successfully created.
   

3. Confirm that the dataset babynames now appears in your project:

   bq ls --datasets=true
   

   The output is similar to the following:

     datasetId
   -------------
     babynames
   

Load data into a table

1. In the babynames dataset, load the source file yob2024.txt into a new table named names2024:

   bq load babynames.names2024 yob2024.txt name:string,assigned_sex_at_birth:string,count:integer
   

   The output is similar to the following:

   Upload complete.
   Waiting on bqjob_r3c045d7cbe5ca6d2_0000018292f0815f_1 ... (1s) Current status: DONE
   

2. Confirm that the table names2024 now appears in the babynames dataset:

   bq ls --format=pretty babynames
   

   The output is similar to the following. Some columns are omitted to simplify the output.

   +-----------+-------+
   |  tableId  | Type  |
   +-----------+-------+
   | names2024 | TABLE |
   +-----------+-------+
   

3. Confirm that the table schema of your new names2024 table is name: string, assigned_sex_at_birth: string, and count: integer:

   bq show babynames.names2024
   

   The output is similar to the following. Some columns are omitted to simplify the output.

     Last modified        Schema                      Total Rows   Total Bytes
   ----------------- ------------------------------- ------------ ------------
   14 Mar 17:16:45   |- name: string                    31904       607494
                     |- assigned_sex_at_birth: string
                     |- count: integer
   

Query table data

1. Determine the most popular girls' names in the data:

   bq query \
       'SELECT
         name,
         count
       FROM
         babynames.names2024
       WHERE
         assigned_sex_at_birth = "F"
       ORDER BY
         count DESC
       LIMIT 5'
   

   The output is similar to the following:

   +-----------+-------+
   |   name    | count |
   +-----------+-------+
   | Olivia    | 14718 |
   | Emma      | 13485 |
   | Amelia    | 12740 |
   | Charlotte | 12552 |
   | Mia       | 12113 |
   +-----------+-------+
   

2. Determine the least popular boys' names in the data:

   bq query \
       'SELECT
         name,
         count
       FROM
         babynames.names2024
       WHERE
         assigned_sex_at_birth = "M"
       ORDER BY
         count ASC
       LIMIT 5'
   

   The output is similar to the following:

   +---------+-------+
   |  name   | count |
   +---------+-------+
   | Aaran   |     5 |
   | Aadiv   |     5 |
   | Aadarsh |     5 |
   | Aarash  |     5 |
   | Aadrik  |     5 |
   +---------+-------+
   

   The minimum count is 5 because the source data omits names with fewer than 5 occurrences.