CPB200: Google BigQuery for Data Analysts Training Course

Module 1: Introducing Google BigQuery

● Understand the purpose of and use cases for Google BigQuery

● Describe ways in which customers have used Google BigQuery to improve their businesses

Lab: Sign Up for the Free Trial and Create a Project

● Register for the GCP free trial

● Create a project using the Cloud Platform Console

Module 2: BigQuery Functional Overview

● Describe the components of a BigQuery project

● Identify how BigQuery stores data and list the advantages of the storage model

● Understand the architecture of BigQuery and how queries are processed

● Describe the methods of interacting with BigQuery

Lab: Explore BigQuery Interfaces

● Explore features of the BigQuery web UI

● Learn how to use the bq shell

● Execute queries using the BigQuery CLI in Cloud Shell

Module 3: BigQuery Fundamentals

● Describe the purpose of denormalizing data

● Identify the purpose and structure of BigQuery schemas and data types

● Explain the types of actions available in BigQuery jobs

● Understand the purpose of and advantages of BigQuery destinations tables and caching

Lab: BigQuery Components and Jobs

● Explore how data is organized in BigQuery

● Learn about the two types of table schemas

● Learn about jobs, and how to cancel them

● Investigate caching and destination tables

Module 4: Ingesting, Transforming, and Storing Data

● Describe the methods for ingesting data, transforming data, and storing data using BigQuery

● Explain the function of BigQuery federated queries

Lab 4, Part I: Loading Data into BigQuery and Using Federated Queries

● Load a CSV file into a BigQuery table using the web UI

● Load a JSON file into a BigQuery table using the CLI

● Transform data and join tables using the web UI

● Store query results in a destination table

● Query a destination table using the web UI to confirm your data was transformed and loaded correctly

● Export query results from a destination table to Google Cloud Storage

● Create a federated query that queries data in Cloud Storage

Lab 4, Part II: Exporting App Engine Logs to BigQuery

● Set up Google Cloud Logging to export App Engine log data from the Guestbook application

● Use the BigQuery web UI to query the log data

Module 5: Pricing and Quotas

● Explain the advantages of the BigQuery pricing model

● Use the pricing calculator to calculate storage and query costs

● Identify the quotas that apply to BigQuery projects

Lab: BigQuery Pricing

● Evaluate the size of a query within BigQuery using the BigQuery web UI

● Use the Pricing Calculator and the total size of the query to estimate the query cost

● Examine how changing a query affects query cost

Module 6: Clauses and Functions

● Explain the differences between BigQuery SQL and ANSI SQL

● Identify the purpose of and use cases for user­defined functions

● Explain the purpose of various BigQuery functions

Lab: BigQuery Clauses and Functions

● Create and run a query using a wildcard function

● Create and run a query using a window function

● Create and run a query using a user­defined function

Module 7: Nested and Repeated Fields

● Identify the purpose and structure of BigQuery nested, repeated, and nested repeated fields

● Describe the use cases for nested, repeated, and nested repeated fields

Lab: Nested Fields

● Create a BigQuery table using nested data

● Run queries to explore the structure of the nested data

Lab: Repeated Fields

● Create a BigQuery table using repeated data

● Run queries to explore the structure of the repeated data

Lab: Nested Repeated Fields

● Create a BigQuery table using nested repeated data

● Run queries to explore the structure of the nested repeated data

Module 8: Query Performance

● Explain the impact of the following in query performance: JOIN and GROUP BY, table wildcards, and table decorators

● Identify various best practices for optimizing query performance

Lab: BigQuery Best Practices and Optimization Techniques

● Use denormalization to improve query performance

● Use subselects to improve the performance of queries with JOIN clauses

● Use destination tables to lower costs when running multiple, similar queries

● Use table decorators and table wildcards to improve query performance and to reduce costs

Module 9: Troubleshooting Errors

● Describe how to handle the most common BigQuery errors: request encoding errors, resource errors, and HTTP errors

Lab: Handling Errors

● Correct queries that produce syntax­related error messages

● Correct an error involving the order of a JOIN clause

● Correct an error involving an invalid table name

● Modify queries that exceed resource constraints

Module 10: Access Control

● Describe the purpose of access control lists in BigQuery

● List and explain the project and dataset roles available in BigQuery

● Apply views for row­level security

Lab: Access Control

● Manage access to datasets using project­level ACLs

● Manage access to datasets using dataset­level ACLs

● Set row­level permissions using views

Module 11: Exporting Data

● List the methods of exporting data from BigQuery and the data formats available

● Describe the process of creating a job to export data from BigQuery

● Explain the purpose of wildcard exports to partition export data

Lab: Exporting Data

● Export data from BigQuery using the web UI and CLI

● Export large tables using wildcard URIs

Module 12: Interfacing with External Tools

● Describe how to use external tools to interface with BigQuery, including: spreadsheets, ODBC and JDBC drivers, the BigQuery encrypted client, and R

Lab: Interfacing with External Tools

● Set up the BigQuery Reports add­on for Google Sheets

● Use the Reports add­on to query BigQuery data

Module 13: Working with Google Analytics Premium Data

● Describe the schema of the Google Analytics Premium and AdSense data exported to BigQuery

Lab: Working with Google Analytics Premium Data

● Build queries to analyze data from Google Analytics Premium

Module 14: Data Visualization

● Describe the options available for visualizing BigQuery data

Lab: Visualizing Data

● Use Google Cloud Datalab to visualize data