Explore query results in notebooks

You can explore BigQuery query results by usingColab Enterprise notebooks inBigQuery.

In this tutorial, you query data from aBigQuery public dataset and explore the query results in a notebook.

Objectives

  • Create and run a query in BigQuery.
  • Explore query results in a notebook.

Costs

This tutorial uses a dataset available through theGoogle Cloud Public Datasets Program.Google pays for the storage of these datasets and provides public access to thedata. You incur charges for the queries that you perform on the data. Formore information, seeBigQuery pricing.

Before you begin

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

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.
    Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

    Go to project selector

  2. Verify that billing is enabled for your Google Cloud project.

  3. Enable the BigQuery API.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

    Enable the API

    For new projects, BigQuery is automatically enabled.

Set the default region for code assets

If this is the first time you are creating a code asset, you should set thedefault region for code assets. You can't change the region for a code assetafter it is created.

Note: If you create a code asset and choose a different default region than theone you have been using for code assets—for example, choosingus-west1when you have been usingus-central1—then that code asset and all codeassets you create afterwards use that new region by default. Existing codeassets continue to use the region they were assigned when they were created.

All code assets in BigQuery Studio use the same default region.To set the default region for code assets, follow these steps:

  1. Go to theBigQuery page.

    Go to BigQuery

  2. In theExplorer pane, find the project in which you have enabled codeassets.

  3. ClickView actions next to the project, and then clickChange my default code region.

  4. ForRegion, select the region that you want to use for code assets.

  5. ClickSelect.

For a list of supported regions, seeBigQuery Studio locations.

Required permissions

To create and run notebooks, you need the following Identity and Access Management (IAM)roles:

Open query results in a notebook

You can run a SQL query and then use a notebook to explore the data. Thisapproach is useful if you want to modify the data in BigQuerybefore working with it, or if you need only a subset of the fields in the table.

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

    Go to BigQuery

  2. In theType to search field, enterbigquery-public-data.

    If the project is not shown, enterbigquery in the search field, and thenclickSearch to all projects to match the search string with theexisting projects.

  3. Selectbigquery-public-data> ml_datasets> penguins.

  4. For thepenguins table,clickView actions,and then clickQuery.

  5. Add an asterisk (*) for field selection to the generated query, so thatit reads like the following example:

    SELECT*FROM`bigquery-public-data.ml_datasets.penguins`LIMIT1000;

  6. ClickRun.

  7. In theQuery results section, clickOpen in, and then clickNotebook.

Prepare the notebook for use

Prepare the notebook for use by connecting to a runtime and setting applicationdefault values.

  1. In the notebook header, clickConnect toconnect to the default runtime.
  2. In theSetup code block, clickRun cell.

Explore the data

  1. To load thepenguins data into aBigQuery DataFrameand show the results, clickRun cell in the code block in theResult set loaded from BigQuery job as a DataFrame section.
  2. To get descriptive metrics for the data, clickRun cell in the code blockin theShow descriptive statistics using describe() section.
  3. Optional: Use other Python functions or packages to explore and analyzethe data.

The following code sample shows usingbigframes.pandasto analyze data, andbigframes.mlto create a linear regression model frompenguins data in aBigQuery DataFrame:

importbigframes.pandasasbpd# Load data from BigQueryquery_or_table="bigquery-public-data.ml_datasets.penguins"bq_df=bpd.read_gbq(query_or_table)# Inspect one of the columns (or series) of the DataFrame:bq_df["body_mass_g"]# Compute the mean of this series:average_body_mass=bq_df["body_mass_g"].mean()print(f"average_body_mass:{average_body_mass}")# Find the heaviest species using the groupby operation to calculate the# mean body_mass_g:(bq_df["body_mass_g"].groupby(by=bq_df["species"]).mean().sort_values(ascending=False).head(10))# Create the Linear Regression modelfrombigframes.ml.linear_modelimportLinearRegression# Filter down to the data we want to analyzeadelie_data=bq_df[bq_df.species=="Adelie Penguin (Pygoscelis adeliae)"]# Drop the columns we don't care aboutadelie_data=adelie_data.drop(columns=["species"])# Drop rows with nulls to get our training datatraining_data=adelie_data.dropna()# Pick feature columns and label columnX=training_data[["island","culmen_length_mm","culmen_depth_mm","flipper_length_mm","sex",]]y=training_data[["body_mass_g"]]model=LinearRegression(fit_intercept=False)model.fit(X,y)model.score(X,y)

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

The easiest way to eliminate billing is to delete the Google Cloud projectthat you created for this tutorial.

    Caution: Deleting a project has the following effects:
    • Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
    • Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

    If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

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

    Go to Manage resources

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

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.