Overview

The purpose of this lab is to show learners how to instantiate a Jupyter notebook running on Google Cloud's Vertex AI service. To aid in the demonstration, a dataset with various flight departure and arrival times will be leveraged.

Objectives

In this lab, you will learn how to perform the following tasks:

• Instantiate a Jupyter notebook on Vertex AI.
• Execute a BigQuery query from within a Jupyter notebook and process the output using Pandas.

Setup and requirements

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Open BigQuery Console

1. In the Google Cloud Console, on the Navigation menu , click BigQuery.
   The Welcome to BigQuery in the Cloud Console dialog opens. This dialog provides a link to the quickstart guide and lists UI updates.

2. Click Done to close the dialog.

Task 1. Launch Vertex AI Workbench instance

1. In the Google Cloud console, from the Navigation menu (), select Vertex AI > Dashboard.

2. Click Enable All Recommended APIs.

3. In the Navigation menu, click Workbench.

   At the top of the Workbench page, ensure you are in the Instances view.

4. Click Create New.

5. Configure the Instance:

   • Name: lab-workbench
   • Region: Set the region to
   • Zone: Set the zone to
   • Advanced Options (Optional): If needed, click "Advanced Options" for further customization (e.g., machine type, disk size).

6. Click Create.

This will take a few minutes to create the instance. A green checkmark will appear next to its name when it's ready.

7. Click Open Jupyterlab next to the instance name to launch the JupyterLab interface. This will open a new tab in your browser.

8. Click the Python 3 icon to launch a new Python notebook.

9. Right-click on the Untitled.ipynb file in the menu bar and select Rename Notebook to give it a meaningful name.

Your environment is set up. You are now ready to start working with your Vertex AI Workbench notebook.

Click Check my progress to verify the objective. Launch Vertex AI Workbench instance

Task 2. Execute a BigQuery query

1. Enter the following query in the first cell of the notebook:

%%bigquery df --use_rest_api SELECT depdelay as departure_delay, COUNT(1) AS num_flights, APPROX_QUANTILES(arrdelay, 10) AS arrival_delay_deciles FROM `cloud-training-demos.airline_ontime_data.flights` WHERE depdelay is not null GROUP BY depdelay HAVING num_flights > 100 ORDER BY depdelay ASC

The command makes use of the magic function %%bigquery. Magic functions in notebooks provide an alias for a system command. In this case, %%bigquery runs the query in the cell in BigQuery and stores the output in a Pandas DataFrame object named df.

2. Run the cell by hitting Shift + Enter, when the cursor is in the cell. Alternatively, if you navigate to the Run tab you can click on Run Selected Cells. Note the keyboard shortcut for this action in case it is not Shift + Enter. There should be no output when executing the command.

Click Check my progress to verify the objective. Execute a BigQuery query

3. View the first five rows of the query's output by executing the following code in a new cell:

df.head()

Task 3. Make a plot with Pandas

We're going to use the Pandas DataFrame containing our query output to build a plot that depicts how arrival delays correspond to departure delays. Before continuing, if you are unfamiliar with Pandas the Ten Minute Getting Started Guide is recommended reading.

1. To get a DataFrame containing the data we need, we first have to wrangle the raw query output. Enter the following code in a new cell to convert the list of arrival_delay_deciles into a Pandas Series object. The code also renames the resulting columns.

import pandas as pd percentiles = df['arrival_delay_deciles'].apply(pd.Series) percentiles.rename(columns = lambda x : '{0}%'.format(x*10), inplace=True) percentiles.head()

2. Since we want to relate departure delay times to arrival delay times, we have to concatenate our percentiles table to the departure_delay field in our original DataFrame. Execute the following code in a new cell:

df = pd.concat([df['departure_delay'], percentiles], axis=1) df.head()

3. Before plotting the contents of the DataFrame, drop extreme values stored in the 0% and 100% fields. Execute the following code in a new cell:

df.drop(labels=['0%', '100%'], axis=1, inplace=True) df.plot(x='departure_delay', xlim=(-30,50), ylim=(-50,50));

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.