Query a public dataset with the Trusted Cloud console

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

Before you begin

  1. In the Trusted Cloud console, on the project selector page, select or create a Trusted Cloud project.

    Go to project selector

  2. Make sure that you have the following role or roles on the project: BigQuery Job User, Service Usage Admin

    Check for the roles

    1. In the Trusted Cloud console, go to the IAM page.

      Go to IAM
    2. Select the project.

    3. In the Principal column, find all rows that identify you or a group that you're included in. To learn which groups you're included in, contact your administrator.

    4. For all rows that specify or include you, check the Role column to see whether the list of roles includes the required roles.

    Grant the roles

    1. In the Trusted Cloud console, go to the IAM page.

      Go to IAM
    2. Select the project.
    3. Click Grant access.

    4. In the New principals field, enter your user identifier. This is typically the identifier for a user in a workforce identity pool. For details, see Represent workforce pool users in IAM policies, or contact your administrator.

    5. In the Select a role list, select a role.
    6. To grant additional roles, click Add another role and add each additional role.
    7. Click Save.

  3. Make sure that billing is enabled for your Trusted Cloud project.

    4. If you don't enable billing for the Trusted 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.

  4. 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 Trusted Cloud console.

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

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

    Go to BigQuery

  2. In the Explorer pane, click +Add data.

  3. In the Add data dialog, in the Filter By pane, click Public dataset icon. Public datasets.

  4. You can use the Search Marketplace field or filters to narrow down your search.

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

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

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