Quickstart using the Cloud Console

This page shows you how to use the Google Cloud Console to query tables in a public dataset and load sample data into a BigQuery table.

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.

    Go to project selector

  3. BigQuery is automatically enabled in new projects. To activate BigQuery in a preexisting project, go to Enable the BigQuery API.

    Enable the API

  4. 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 a sandbox to perform the steps.

Query a public dataset

You can use the Cloud Console to query tables, including BigQuery public datasets.

In this example, you query the USA Name Data public dataset to determine the most common names in the US between 1910 and 2013.

https://console.cloud.google.com/bigquery?p=bigquery-public-data&page=project

To query a public dataset, follow these steps:

  1. In the Cloud Console, go to the BigQuery page.

    Go to BigQuery

  2. If the Editor tab isn't visible, then click Compose new query.

    Editor tab.

  3. In the Editor field, paste the following:

    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
    

    If the query is valid, then a check mark appears along with the amount of data that the query will process. If the query is invalid, then an exclamation point appears along with an error message.

    Query validator.

  4. Click Run. The Query results section displays the time that elapsed and the amount of data that the query processed.

  5. To see the detailed query results, click the Results tab. The table's header row contains each column name that you selected in the query.

    Query results in the Cloud Console.

Create and query a dataset

Next, create a dataset, load data into a table, and query it.

Create a dataset

Use the Cloud Console to create a dataset that stores the data.

  1. In the Cloud Console, open the BigQuery page.

    Go to BigQuery

  2. In the Explorer panel, click your project name.

  3. Expand the View actions option and click Create dataset.

  4. On the Create dataset page, do the following:

    • For Dataset ID, enter babynames.
    • From the Data location drop-down list, choose United States (US). The public datasets are stored in the US multi-region location. For simplicity, store your dataset in the same location.

      Create dataset page.

  5. Leave the remaining default settings as they are, and click Create dataset.

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

  1. Download the baby names zip file.

  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, sex (M or F), 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 the babynames dataset that you created.

  2. Expand the View actions option and click 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:

    • In the Source section, for Create table from, choose Upload.
    • For Select file, click Browse, navigate to the yob2014.txt file, and click Open.
    • From the File format drop-down list, choose CSV.
    • In the Destination section, in the Table name field, enter names_2014.
    • In the Schema section, click the Edit as text toggle, and paste the following schema definition into the text field.

        name:string,gender:string,count:integer
        

  5. Click Create table.

    Wait for BigQuery to create the table and load the data. When BigQuery finishes loading the data, a check mark appears in the Job history panel.

Preview table data

To preview the table data, follow these steps:

  1. In the Explorer panel, expand babynames and select names_2014.

  2. In the details panel, click Preview. BigQuery displays the first few rows of the table.

    Cloud Console table preview.

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 field, paste the following query. This query retrieves the top five 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 in the Query results section.

    Names query results.

Clean up

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

  1. In the Cloud Console, open the BigQuery page.

    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 name of your dataset (babynames) and click Delete.

What's next