Load and query data

Get started with BigQuery by creating a dataset, loading data into a table, and querying the table.

To follow step-by-step guidance for this task directly in the Google Cloud console, click Guide me:

Guide me

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  3. If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  5. If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

  6. Enable the BigQuery API.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the API

    For new projects, the BigQuery API is automatically enabled.

  7. Optional: Enable billing for the project. If you don't want to enable billing or provide a credit card, the steps in this document still work. BigQuery provides you a sandbox to perform the steps. For more information, see Enable the BigQuery sandbox.

Required roles

To get the permissions that you need to create a dataset, create a table, load data, and query data, ask your administrator to grant you the following IAM roles on the project:

  • Run load jobs and query jobs: BigQuery Job User (roles/bigquery.jobUser)
  • Create a dataset, create a table, load data into a table, and query a table: BigQuery Data Editor (roles/bigquery.dataEditor)

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create a BigQuery dataset

Use the Google 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 Google Cloud console, open the BigQuery page.
    2. Go to BigQuery
  2. In the left pane, click Explorer.
  3. In the Explorer pane, click your project name.
  4. Click View actions.
  5. Select Create dataset.
  6. 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

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 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 left pane, click Explorer.
  2. In the Explorer pane, expand your project name.
  3. Click Datasets and then next to the babynames dataset, click View actions and select Open.
  4. Click Create table.

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

  5. 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:
      7. 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 left pane, click Explorer.
  2. In the Explorer pane, expand your project and click Datasets.
  3. Click the babynames dataset, and then select the names_2024 table.
  4. Click the Preview tab. BigQuery displays the first few rows of the table.
    5. The table preview tab.
The Preview tab is not available for all table types. For example, the Preview tab is not displayed for external tables or views.

Query table data

Next, query the table.

  1. Next to the names_2024 tab, click the 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.
    The query results panel

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

Clean up

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

  1. In the Google Cloud console, open the BigQuery page.
    2. Go to BigQuery
  2. In the left pane, click Explorer.
  3. In the Explorer pane, click Datasets and then click the babynames dataset that you created.
  4. Expand the View actions option and click Delete.
  5. In the Delete dataset dialog, confirm the delete command: type the word delete and then click Delete.

What's next