Query a public dataset with the console

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

For step-by-step guidance on this task directly in 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 console.

To open the public datasets, follow these steps:

  1. In the 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 datasets.

  3. To pin the bigquery-public-data dataset to your project, click Click to pin.

  4. To continue, clear the search field.

  5. To see underlying datasets, expand the bigquery-public-data dataset.

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

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

      name, gender,
      SUM(number) AS total
      name, gender
      total DESC

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