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.

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.

    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. 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.

  5. 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.

In the following example, you access datasets in the public project bigquery-public-data.

  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 to check whether the public project is starred to the Explorer pane.

    You can use the Type to search field to find datasets, tables, and starred projects. You cannot search directly for unstarred projects.

    • If you see the public project is starred to the Explorer pane, then click Expand node for this project to display all the datasets.

    • If the public project is not starred to the Explorer pane, then do the following:

      1. In the Type to search field, enter the name of a dataset or table contained in the bigquery-public-data project, for example, austin_311 or gsod, and then click Broaden search to all projects.

      2. In the Explorer pane, hold the pointer over bigquery-public-data, and then click Star.

        You still can't see all the datasets in this project.

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

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

  3. To open an underlying dataset and view its details, click View 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