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 Google Cloud project.

    5. If you don't enable billing for the Google 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 Explorer pane, click +Add.

  3. In the Add dialog, do the following:

    1. In the Search for data sources field, enter public datasets, and then press Enter.
    2. Click Public dataset icon. Public Datasets.

  4. Select a dataset, and then click View dataset.

    In the Explorer pane, your dataset is selected and you can view its details.

  5. Optional: Click View actions next to your dataset to view more options.

    Each dataset contains tables, which you can view by clicking Toggle node next to any dataset.

Query a public dataset

In the following steps, you query the USA Names 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. Click SQL query.

  3. In the query editor, copy the following query:

    SELECT
  name,
  SUM(number) AS total
FROM
  `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
  name
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