arrow_back

Visualize Real Time Geospatial Data with Google Data Studio

Join Sign in

Visualize Real Time Geospatial Data with Google Data Studio

1 hour 15 minutes 5 Credits

GSP201

Google Cloud Skills Boost logo

Overview

This lab demonstrates how to use Google Data Studio to visualize real-time geospatial data. You gather data for Data Studio using Google Dataflow to process real-time streaming data from a historical dataset, and then store the results in BigQuery.

Data Studio is a free tool that turns your data into informative, easy to read, easy to share, and fully customizable dashboards and reports. With Data Studio, you connect to a wide variety of datasets, then easily share insights and collaborate on dashboards and reports. See Welcome to Data Studio for more information.

Dataflow is a fully-managed service that transforms and enriches data in stream (real time) and batch (historical) modes via Java and Python APIs with the Apache Beam SDK. Dataflow provides a serverless architecture you can use to shard and process very large batch datasets, or high volume live streams of data, in parallel.

BigQuery is a RESTful web service that enables interactive analysis of massive datasets working in conjunction with Google Storage. See BigQuery for more information.

This lab uses a dataset provided by US Bureau of Transport Statistics. The dataset provides historic information about internal flights in the United States and can be used to demonstrate a wide range of data science concepts and techniques.

Objectives

  • Create a Google Dataflow processing job for streaming data

  • Generate real-time streaming data using Python.

  • Analyze streaming data in BigQuery

  • Create a real-time geospatial dashboard for streaming data

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

What you need

To complete this lab, you need:

  • Access to a standard internet browser (Chrome browser recommended).
  • Time to complete the lab.

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab.

Note: If you are using a Chrome OS device, open an Incognito window to run this lab.

How to start your lab and sign in to the Google Cloud Console

  1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is a panel populated with the temporary credentials that you must use for this lab.

    Open Google Console

  2. Copy the username, and then click Open Google Console. The lab spins up resources, and then opens another tab that shows the Sign in page.

    Sign in

    Tip: Open the tabs in separate windows, side-by-side.

  3. In the Sign in page, paste the username that you copied from the left panel. Then copy and paste the password.

    Important: You must use the credentials from the left panel. Do not use your Google Cloud Training credentials. If you have your own Google Cloud account, do not use it for this lab (avoids incurring charges).

  4. Click through the subsequent pages:

    • Accept the terms and conditions.
    • Do not add recovery options or two-factor authentication (because this is a temporary account).
    • Do not sign up for free trials.

After a few moments, the Cloud Console opens in this tab.

Task 1. Prepare your environment

This lab uses code samples and scripts developed for Data Science on the Google Cloud Platform, 2nd Edition from O'Reilly Media, Inc. You will clone the sample repository used in Chapter 4 from Github to a VM and then carry out all of the lab tasks.

Note: The Dataflow API is enabled by default for this lab. For your own projects, you will have to enable the Dataflow API by going to APIs and Services > Library and searching for Dataflow.

Clone the Data Science on Google Cloud repository

To clone the sample repository to the VM:

  1. In the Cloud Console, on the Navigation menu (Navigation menu), click Compute Engine > VM instances.

  2. Click the SSH button next to lab-vm-ql VM to launch a terminal and connect. This terminal is called terminal-1.

  3. Click Connect to confirm the connection.

  4. Enter the following commands to clone the repository:

git clone https://github.com/GoogleCloudPlatform/data-science-on-gcp/
  1. Navigate to the repository source directory for this lab:

cd ~/data-science-on-gcp/04_streaming
  1. Install the required Python packages:

cd transform; ./install_packages.sh

Task 2. Publish an event stream to Cloud Pub/Sub

In this section, you set up a simulator to simulate a stream of current flight information, notifying Pub/Sub of events. The simulator sends events from Feb 1, 2015, to Feb 4, 2015, at 30 times real-time speed, so that an hour of data is sent to Cloud Pub/Sub in two minutes.

  1. Navigate to the simulate directory:

cd ~/data-science-on-gcp/04_streaming/simulate
  1. Run this simulation script so that it creates the Google Pub/Sub topics:

export PROJECT_ID=$(gcloud info --format='value(config.project)') python3 ./simulate.py --startTime '2015-02-01 00:00:00 UTC' --endTime '2015-02-04 00:00:00 UTC' --speedFactor=30 --project $PROJECT_ID

Click Check my progress below to verify the objective.

Publish an Event Stream to Cloud Pub/Sub

Task 3. Process stream data using Cloud Dataflow

  1. In Cloud Console, click the SSH button next to lab-vm-ql VM to launch another terminal. This terminal is terminal-2.

  2. Click Connect to confirm the connection.

  3. In terminal-2, start avg01.py to read the stream of events and write them out to BigQuery.

cd ~/data-science-on-gcp/04_streaming/realtime export PROJECT_ID=$(gcloud info --format='value(config.project)') export BUCKET=$PROJECT_ID-ml ./avg01.py --project $PROJECT_ID --bucket $BUCKET --region us-central1
  1. In the Cloud Console, on the Navigation menu (Navigation menu), click BigQuery.

  2. Copy and paste the query below into the Query Editor, and then click Run.

This query lists the first 5 events from dsongcp.streaming_events ordered by EVENT_TIME DESC.

SELECT * FROM dsongcp.streaming_events ORDER BY EVENT_TIME DESC LIMIT 5

It takes up to a minute for avg01.py to write data to BigQuery. Re-run the query if you get no results on the first run.

  1. In the terminal-2, stop avg01.py by pressing Ctrl+C.

  2. Run avg02.py:

./avg02.py --project $PROJECT_ID --bucket $BUCKET --region us-central1
  1. In BigQuery, click Compose New Query to clear the Query Editor and then run the following query.

This query lists the first 5 events from dsongcp.streaming_delays ordered by END_TIME DESC.

SELECT * FROM dsongcp.streaming_delays ORDER BY END_TIME DESC LIMIT 5

It takes up to a minute for avg02.py to write data to BigQuery. Re-run the query if you get no results on the first run.

  1. Click Compose New Query, and then run the following script to view how often the data is coming in:

SELECT END_TIME, num_flights FROM dsongcp.streaming_delays ORDER BY END_TIME DESC LIMIT 5

It is likely, that Cloud Shell or your local laptop will struggle to keep up with the event stream. Improve the process by executing this pipeline in Dataflow in a serverless way.

  1. In the terminal 2 window, stop avg02.py by hitting Ctrl+C.

Deploy job to process stream data

To avoid confusion, clean up before you start this pipeline. Delete the rows already written by avg02.py script.

  1. In BigQuery, run the following query.

TRUNCATE TABLE dsongcp.streaming_delays
  1. In terminal 2, run avg03.py to launch a Dataflow job:

./avg03.py --project $PROJECT_ID --bucket $BUCKET --region us-central1
  1. In the Cloud Console, click Navigation menu > Dataflow to open the Dataflow console.

  2. If needed, confirm that you're leaving.

  3. Click on the name of the streaming Dataflow job to inspect it.

The pipeline processes flight events as they stream into Pub/Sub, aggregates them into time windows, and streams the resulting statistics into BigQuery.

Dataflow Job Details

Click Check my progress below to verify the objective.

Process stream data using Cloud Dataflow

Task 4. Analyze streaming data in BigQuery

Use BigQuery to analyze the streaming data:

  1. Return to BigQuery, click Navigation menu > BigQuery.

  2. Copy and paste the following into the Query editor and click Run:

SELECT * FROM dsongcp.streaming_delays ORDER BY END_TIME DESC;

It takes 2-5 minutes for your Dataflow job to create and put data in the table. Re-run the query if your first run returned no results. Re-run the query a few times to watch the Dataflow job create and put data into the table. When you see 5-6 records, you can analyze the data.

  1. Click COMPOSE NEW QUERY.

  2. Copy and paste the updated query below into the query dialog field. This creates a view of the latest arrival delay by airport:

CREATE OR REPLACE VIEW dsongcp.airport_delays AS WITH delays AS ( SELECT d.*, a.LATITUDE, a.LONGITUDE FROM dsongcp.streaming_delays d JOIN dsongcp.airports a USING(AIRPORT) WHERE a.AIRPORT_IS_LATEST = 1 ) SELECT AIRPORT, CONCAT(LATITUDE, ',', LONGITUDE) AS LOCATION, ARRAY_AGG( STRUCT(AVG_ARR_DELAY, AVG_DEP_DELAY, NUM_FLIGHTS, END_TIME) ORDER BY END_TIME DESC LIMIT 1) AS a FROM delays GROUP BY AIRPORT, LONGITUDE, LATITUDE
  1. Click Run.

Click Check my progress below to verify the objective.

Prepare your data in BigQuery

Task 5. Visualize your data in Data Studio

  1. Click to open this Google Data Studio link.

  2. Click Blank Report.

  3. Check the checkbox to acknowledge the Google Data Studio Additional Terms, and click Continue.

  4. Select No to all the questions, then click Continue.

  5. Click Blank Report.

  6. Select the BigQuery tile.

  7. Click Authorize to authorize the connection to BigQuery.

  8. Select My Projects > [Project-ID] > dsongcp > airport_delays.

Remember, [PROJECT-ID] is the Project ID in the left pane of these lab instructions under Username and Password.

  1. Click Add in the lower right.

  2. In the confirmation dialog, click ADD TO REPORT.

  3. Click to select the auto generated chart, then delete it.

  4. Select Insert > Geo chart in the top ribbon, and then drop it on the canvas.

  5. On the right panel, in the Data tab, notice the Geo dimension field and the Available fields list on the right. Drag location from Available fields to Geo dimension field, replacing Invalid dimension.

  6. Click on the ABC icon for location to change the Type to Geo > Latitude, Longitude.

  7. Drag a.AVG_ARR_DELAY into Metric.

  8. Click in the Zoom Area and select United States.

  9. Click the Style tab in the right panel and change the style so that color bar goes from green to red through white.

  10. Click the Text tool in the top ribbon and add a label to the chart. Type Arrival delay to identify this as the chart that displays the arrival delay results.

  11. Copy and paste the map and label onto the canvas. Click on the new map, which switches you back to the Data tab in the right menu.

  12. Change the metric to a.AVG_DEP_DELAY. Change the label for this chart to Departure delay.

Dashboard of latest flight data from across the United States

You can now see that the average arrival delay is significantly smaller than the average departure delay, indicating that time lost due to departure delays is often recovered.

Congratulations!

Now you know how to use Google Dataflow to process streaming data and how to visualize real-time geospatial event data using Google Data Studio.

Data Science on Google Cloud Badge

Finish your Quest

This self-paced lab is part of the Data Science on Google Cloud Quest. A Quest is a series of related labs that form a learning path. Completing this Quest earns you the badge above, to recognize your achievement. You can make your badge (or badges) public and link to them in your online resume or social media account. Enroll in this Quest and get immediate completion credit if you've taken this lab. See other available Quests.

Take your next lab

Continue your Quest with Interactive Data Exploration with Vertex AI Workbench, or check out Evaluating a Data Model.

Next steps / learn more

Here are some follow-up steps:

Google Cloud Training & Certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated April 25, 2022
Lab Last Tested April 25, 2022

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.