Google BigQuery Training

BigQuery is a fully-managed enterprise data warehouse that helps you manage and analyze your data with built-in features like machine learning, geospatial analysis, and intelligent caching for business intelligence. This learning path will first show you the fundamentals of how to use BigQuery and then how to optimize BigQuery to reduce costs, speed up your queries, and apply proper access control.

Learning Objectives

Load data into BigQuery using files or by streaming one record at a time
Run a query using standard SQL and save your results to a table
Export data from BigQuery using Google Cloud Storage
Reduce your BigQuery costs by reducing the amount of data processed by your queries
Create, load, and query partitioned tables for daily time-series data
Speed up your queries by using denormalized data structures, with or without nested repeated fields
Implement fine-grained access control using roles and authorized views

Course Content:

Module 1: Interacting with BigQuery

● Introduction to BigQuery
● BigQuery Sandbox and Web UI
● Command-Line Tools
● BigQuery Classic Web UI

Module 2: Running and Managing Jobs
● Introduction
● Running Jobs Programmatically
● Managing Jobs

Module 3: Working with Datasets
● Define Datasets
● Dataset Locations
● Creating and Copying Datasets
● Controlling Access to Datasets
● Listing Datasets
● Updating Dataset Properties
● Managing Datasets
● Availability and Durability

Module 4: Working with Table Schemas
● Specifying a Schema
● Specifying Nested and Repeated Columns
● Auto-Detecting Schemas
● Modifying Table Schemas
● Manually Changing Table Schemas

Module 5: Working with Tables
● Creating and Using Tables
● Managing Tables and Table Data
● Exporting Table Data
● Updating Table Data using DML

Module 6: Working with Partitioned Tables
● What are Partitioned Tables?
● Creating Ingestion-time Partitioned Tables
● Creating Date/Time Partitioned Tables
● Creating Integer Range Partitioned Tables
● Managing and Querying Partitioned Tables
● Using DML with Partitioned Tables

Module 7: Working with Clustered Tables
● Define Clustered Tables
● Creating and Using Clustered Tables
● Querying Clustered Tables

Module 8: Working with Views
● Introduction to Views
● Creating Views
● Controlling Access to Views
● Creating Authorised Views
● Listing Views
● Updating View Properties
● Managing Views

Module 9: Labeling BigQuery Resources
● Adding Labels
● Viewing Labels
● Updating Labels
● Filtering Using Labels
● Deleting Labels

Module 10: Loading Data into BigQuery
● Loading Data from Cloud Storage
● Loading Data from a Local File
● Streaming Data into BigQuery

Module 11: Querying BigQuery Data
● Running Interactive and Batch Queries
● Performing a Query Dry Run
● Writing Query Results
● Using Cached Results
● Running Parameterised Queries
● Querying Data Using a Wildcard Table
● Saving and Sharing Queries
● Scheduling Queries
● Using the Query Plan Explanation
● Using the BigQuery Connector for Excel

Module 12: Querying External Data Sources
● Creating a Table Definition File
● Querying Externally Partitioned Data
● Federated Queries with Cloud SQL Data
● Querying Cloud Bigtable and Storage Data
● Querying Google Cloud Drive Data

Module 13: Controlling BigQuery Costs
● Introduction to BigQuery Costs