Load and query data in BigQuery Studio

Create a BigQuery dataset

Use the Trusted Cloud console to create a dataset to store the data. You create your dataset in the US multi-region location. For information on BigQuery regions and multi-regions, see Locations.

1. In the Trusted Cloud console, open the BigQuery Studio page.
Go to BigQuery Studio
2. In the Explorer pane, click your project name.
3. Click more_vert View actions.
4. Select Create dataset.
5. On the Create dataset page, do the following:
1. For Dataset ID, enter babynames.
2. For Location type, select Multi-region, and then choose US (multiple regions in United States). The public datasets are stored in the us multi-region location. For simplicity, store your dataset in the same location.
3. Leave the remaining default settings as they are, and click Create dataset.

Download the file that contains the source data

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 zip file's NationalReadMe.pdf file.

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. Note the location of the yob2024.txt file so that you can find it later.

Load data into a table

Next, load the data into a new table.

1. In the Explorer pane, expand your project name.
2. Next to the babynames dataset, click more_vert View actions and select Open.
3. Click add_box Create table.

   Unless otherwise indicated, use the default values for all settings.

4. On the Create table page, do the following:
1. In the Source section, for Create table from, choose Upload from the list.
2. In the Select file field, click Browse.
3. Navigate to and open your local yob2024.txt file, and click Open.
4. From the File format list, choose CSV.
5. In the Destination section, in the Table field, enter names_2024.
6. In the Schema section, click the Edit as text toggle, and paste the following schema definition into the text field:

name:string,assigned_sex_at_birth:string,count:integer

7. Click Create table.

   Wait for BigQuery to create the table and load the data.

Preview table data

To preview the table data, follow these steps:

1. In the Explorer pane, expand your project and babynames dataset, and then select the names_2024 table.
2. Click the Preview tab. BigQuery displays the first few rows of the table.

Query table data

Next, query the table.

1. Next to the names_2024 tab, click the add_box SQL query option. A new editor tab opens.
2. In the query editor, paste the following query. This query retrieves the top five names for babies born in the US that were assigned male at birth in 2024.
   

   
     SELECT
       name,
       count
     FROM
       `babynames.names_2024`
     WHERE
       assigned_sex_at_birth = 'M'
     ORDER BY
       count DESC
     LIMIT
       5;
     

3. Click Run. The results are displayed in the Query results section.
   

You have successfully queried a table in a public dataset and then loaded your sample data into BigQuery using the Trusted Cloud console.