Load and query data with the Google Cloud console

Learn how to use the Google Cloud console to create a dataset, load sample data into a BigQuery table, and query tables.

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

Guide me

Before you begin

Before you can explore BigQuery, you must log in to Google Cloud console and create a project. If you don't enable billing in your project, then all of the data you upload will be in the BigQuery sandbox. The sandbox makes it possible for you to learn BigQuery at no charge while working with a limited set of BigQuery features. For more information, see Enable the BigQuery sandbox.

  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.

    Go to project selector

    3.

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

    Go to project selector

    4.
  4. Optional: If you select an existing project, make sure that you enable the BigQuery API. The BigQuery API is automatically enabled in new projects.

Create a BigQuery dataset

Use the Google Cloud console to create a dataset that stores the data.

  1. In the Google Cloud console, open the BigQuery page.
    2. Go to BigQuery
  2. In the Explorer panel, click your project name.
  3. Expand the View actions > Create dataset.
  4. On the Create dataset page, do the following:
    1. For Dataset ID, enter babynames.
    2. From the Data location list, 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 source data file

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 dataset, see the Social Security Administration's dataset information page.

  1. Download the US Social Security Administration's dataset 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 yob2014.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 yob2014.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 panel, click your project name.
  2. Next to the babynames dataset, click More actions and select Open.
  3. In the details panel, click 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, choose Upload from the Create table from list.
    2. In the Select file field, click Browse.
    3. Navigate to and open your local yob2014.txt file, and click Open.
    4. From the File format list, choose CSV
    5. In the Destination section, enter names_2014 for Table name.
    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. When BigQuery finishes loading the data, expand the Personal history and Project history panel to review the job details.

Preview table data

To preview the table data, follow these steps:

  1. In the Explorer panel, expand your project and babynames dataset, and then select the names_2014 table.
  2. In the details panel, click Preview. BigQuery displays the first few rows of the table.
    3. Google Cloud console table preview.
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. The process is identical to the previous example, except that this time, you're querying your table instead of a public table.

  1. Click Compose new query. A new Editor tab opens.
  2. In the Editor tab, paste the following query. This query retrieves the top five names for US babies that were assigned male at birth in 2014. 
    
    SELECT
      name,
      count
    FROM
      `babynames.names_2014`
    WHERE
      assigned_sex_at_birth = 'M'
    ORDER BY
      count DESC
    LIMIT
      5;
  3. Click Run. The results are displayed in the Query results section.
    Names query results.

You have successfully queried a table in a public dataset and then loaded your sample data into BigQuery using the 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 Explorer panel, click the babynames dataset that you created.
  3. Expand the View actions option and click Delete.
  4. In the Delete dataset dialog, confirm the delete command: type the word delete and then click Delete.

What's next