Skip to Content
View all events

Google BigQuery in Action

Published by Pearson

Intermediate content levelIntermediate

Learn How to Use Google Cloud BigQuery for Data Analytics and Data Warehousing

  • Learn the fundamentals of Google BigQuery and how to use it to analyze data and build machine learning models
  • Maximize your analysis workflow by using features of BigQuery console to streamline your work
  • Understand how BigQuery differs from relational databases and learn techniques for troubleshooting and controlling costs
  • Run through exercises and follow along with demos to further cement your Google BigQuery skills
  • Understand BigQuery architecture and how to optimize data models, data ingestion, and querying.

Enterprises of all sizes are becoming more data driven and this means data analysis skills are increasingly important. Google Cloud BigQuery is one of the most popular platforms for data warehousing and data analysis. In this course, you will learn how to use BigQuery for analyzing data by learning how to write efficient SQL queries, organize tables with partitions and clustering, and use the query console to rapidly perform ad hoc analysis.

The course begins with creating tables and loading data, followed by exploring and linking third-party datasets. Next, we consider how to write efficient and precise SELECT statements, including common table expression (CTEs), joins, and aggregations. You will learn about the wide array of SQL functions available for math, statistics, stream processing with windowing, and more. We will also review cost control procedures and provide tips on how to troubleshoot problems with BigQuery. The course concludes with a discussion and demonstration of how to build a machine learning model in BigQuery.

What you’ll learn and how you can apply it

  • How Google Cloud BigQuery is different from a relational database and how that impacts how we work with it
  • Various ways to load and access datasets in BigQuery
  • How to use an array of SQL functions to enable more complex data analysis
  • How to work with deformalized data structures commonly used in BigQuery

And you’ll be able to:

  • Create tables and load data into BigQuery
  • Write efficient and cost effective SELECT statements in BigQuery
  • Transform and analyze numeric string date, time, and hierarchical data types
  • Query complex table structures, including arrays, structs, and arrays of structs

This live event is for you because...

  • You are a data analyst, data scientist, or database developer, or you work with SQL and want to work with large data sets and perform complex analysis
  • You are looking for a tool that allows you to focus more on analysis and less on managing data
  • You need to work with increasingly large data sets and finding it difficult to do that with commonly used spreadsheet and database tools

Prerequisites

  • Some experience with SQL

Course Set-up

  • Sign up for a free trial with Google Cloud
  • To follow along with the demos, download code and supporting material from the course Github Link

Recommended Preparation

Recommended Follow-up

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

Segment 1: Overview of BigQuery (20 minutes)

  • Serverless data warehouse service
  • Introduction to the BigQuery Console
  • Projects, Datasets, and Tables
  • Demo: Viewing Schemas
  • Jobs in BigQuery

Segment 2: Creating Tables, Loading Data, Linking Datasets (20 minutes)

  • Creating a Table in BigQuery
  • Loading Data into a BigQuery Table
  • Demo: Previewing Data in a BigQuery Table
  • Linking data from the Analytics Hub

Exercise: Linking a Dataset from the Analytics Hub (5 minutes)

Break (5 minutes)

Segment 3: Querying Data in BigQuery (20 minutes)

  • Basic SELECT statements
  • Joining Tables in BigQuery
  • Demo: Using Aggregate Functions: SUM, MIN, MAX, AVG, and COUNT
  • Grouping Rows
  • Demo: Filtering with WHERE and HAVING

Exercise: Write queries using joins, aggregates, and filters (10 minutes)

Segment 4: Using Math, Statistics, and Data Conversion Functions in BigQuery (20 minutes)

  • Basic Math Functions
  • Statistical Functions
  • Demo: CASTing Between Data Types

Exercises: Writing queries using math, statistics, and data conversion functions (10 minutes) Q&A (5 minutes)

Segment 5: Working with Strings and Regular Expressions (15 minutes)

  • Trimming, Padding, and Substrings
  • Demo: Working with Substrings
  • Splitting Strings on Delimiters
  • Demo: Matching with Regular expressions

Exercise: Writing queries using string functions and regular expressions (10 minutes)

Break (5 minutes)

Segment 6: Working with Dates and Times (10 minutes)

  • Date Functions
  • Demo: Datetime Functions
  • Time Functions
  • Timestamp Functions

Segment 7: Working with Arrays and Structs (30 minutes)

  • Querying Arrays
  • Demo: Unnesting Arrays
  • Demo: Querying Structs
  • Querying Arrays of Structs

Exercise: Writing queries using arrays, structs, and arrays of structs (15 minutes)

Segment 8: BigQuery Architecture (30 minutes)

  • Separating Compute and Storage
  • BigQuery Components
  • Data organization
  • Data Ingestion

Course wrap-up, Q&A, and next steps (10 minutes)

Your Instructor

Dan Sullivan

Dan Sullivan is a cloud architect and systems developer specializing in data architecture, data analytics, and machine learning. He is the author of the Official Google Cloud Professional Architect Study Guide, the Official Google Cloud Professional Data Engineer Study Guide, and the Official Google Cloud Associate Engineer Study Guide. He is an experienced trainer and his online training courses have been viewed over 1 million times.

Skill covered

Google BigQuery