Stay organized with collections Save and categorize content based on your preferences.

Query a public dataset with the Google Cloud console

Learn how to locate and query public datasets in BigQuery by using the Google Cloud console.


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

Guide me


The following sections take you through the same steps as clicking 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.

    Go to project selector

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

    Go to project selector

  4. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

  5. If you do not enable billing for the Cloud project that you use in this tutorial, then you will upload and work with data in the BigQuery sandbox. The BigQuery sandbox lets you learn BigQuery with a limited set of BigQuery features at no charge.

  6. Ensure that the BigQuery API is enabled.

    Enable the API

    If you created a new project, the BigQuery API is automatically enabled.

Open a public dataset

BigQuery public datasets are available by default in the Google Cloud console.

To open the public datasets, follow these steps:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Type to search field, enter bigquery-public-data.

    If the project is not shown, enter bigquery in the search field, and then click Broaden search to all projects to match the search string with the existing projects.

  3. In the Explorer pane, hold the pointer over the bigquery-public-data project, and then click Click to pin.

  4. To continue, clear the search field.

  5. To display the datasets in the bigquery-public-data project, click Expand node for this project.

  6. To open an underlying dataset and view its details, click More Actions > Open.

Query a public dataset

In the following steps, you query the USA Name public dataset to determine the most common names in the United States between 1910 and 2013:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. Go to the Editor field.

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

    Editor tab.

  3. In the Editor field, copy the following query:

    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 processes. If the query is invalid, then an exclamation point appears along with an error message.

    Query validator.

  4. Click Run.

    The most common names are listed in the Query results section. The table's header row contains each column name that you selected in the query.

    Query results in the Google Cloud console.

  5. Optional: To display the duration and the amount of data that the query processed, click the Job information tab in the Query results section.

Clean up

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

Delete the project

If you used the BigQuery sandbox to query the public dataset, then billing is not enabled for your project.

The easiest way to eliminate billing is to delete the project that you created for the tutorial.

To delete the project:

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next