Google BigQuery for Data Analysts (GO8327)

Learn how to store, transform, analyze, and visualize data using Google BigQuery. In this course, you will be introduced to Google BigQuery. Through a combination of instructor-led presentations, demonstrations, and hands-on labs, you will learn how to store, transform, analyze, and visualize data using Google BigQuery.


Course Objectives

In this course you will learn:

  • Purpose of and use cases for Google BigQuery
  • Ways in which customers have used Google BigQuery to improve their businesses
  • Architecture of BigQuery and how queries are processed
  • Interact with BigQuery using the web UI and command-line interface
  • Purpose and structure of BigQuery schemas and data types
  • Purpose of and advantages of BigQuery destinations tables and caching
  • Use BigQuery jobs
  • Transform and load data into BigQuery
  • Export data from BigQuery
  • Store query results in a destination table
  • Create a federated query
  • Export log data to BigQuery and query it
  • Understand the BigQuery pricing structure and evaluate mechanisms for controlling query and storage costs
  • Best practices for optimizing query performance
  • Troubleshoot common errors in BigQuery
  • Use various BigQuery functions
  • Use external tools such as spreadsheets to interact with BigQuery
  • Visualize BigQuery data
  • Use access controls to restrict access to BigQuery data
  • Query Google Analytics Premium data exported to BigQuery


Target Audience

  • Data analysts and data scientists responsible for: Analyzing and visualizing big data
  • Implementing cloud-based big data solutions
  • Deploying or migrating big data applications to the public cloud
  • Implementing and maintaining large-scale data storage environments, and transforming/processing big data


Prerequisites

  • Have attended Google Cloud Platform Fundamentals (CP100A) or Google Cloud Platform Big Data And Machine Learning Fundamentals (CPB100) (or equivalent experience)
  • Experience using a SQL-like query language to analyze data
Show details


Course Modules

1. Introducing Google BigQuery

  • Purpose of and use cases for Google BigQuery
  • Ways in which customers have used Google BigQuery to improve their businesses
  • Register for the GCP free trial
  • Create a project using the Cloud Platform Console


2. BigQuery Functional Overview

  • Components of a BigQuery project
  • How BigQuery stores data and list the advantages of the storage model
  • Architecture of BigQuery and how queries are processed
  • Methods of interacting with BigQuery
  • Features of the BigQuery web UI
  • How to use the bq shell
  • Execute queries using the BigQuery CLI in Cloud Shell


3. BigQuery Fundamentals

  • Purpose of denormalizing data
  • Purpose and structure of BigQuery schemas and data types
  • Types of actions available in BigQuery jobs
  • Purpose of and advantages of BigQuery destinations tables and caching
  • How data is organized in BigQuery
  • Two types of table schemas
  • Jobs and how to cancel them
  • Caching and destination tables


4. Ingesting, Transforming, and Storing Data

  • Methods for ingesting data, transforming data, and storing data using BigQuery
  • Function of BigQuery 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
  • 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


5. Pricing and Quotas

  • Advantages of the BigQuery pricing model
  • Use the pricing calculator to calculate storage and query costs
  • Quotas that apply to BigQuery projects
  • 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
  • How changing a query affects query cost


6. Clauses and Functions

  • Differences between BigQuery SQL and ANSI SQL
  • Purpose of and use cases for user-defined functions
  • Purpose of various BigQuery 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


7. Nested and Repeated Fields

  • Purpose and structure of BigQuery nested, repeated, and nested repeated fields
  • Use cases for nested, repeated, and nested repeated fields
  • Create a BigQuery table using nested data
  • Run queries to explore the structure of the nested data
  • Create a BigQuery table using repeated data
  • Run queries to explore the structure of the repeated data
  • Create a BigQuery table using nested repeated data
  • Run queries to explore the structure of the nested repeated data


8. Query Performance

  • Impact of the following in query performance: JOIN and GROUP BY, table wildcards, and table decorators
  • Various best practices for optimizing query performance
  • 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


9. Troubleshooting Errors

  • How to handle the most common BigQuery errors: request encoding errors, resource errors, and HTTP 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


10. Access Control

  • Purpose of access control lists in BigQuery
  • The project and dataset roles available in BigQuery
  • Apply views for row-level security
  • Manage access to datasets using project-level ACLs
  • Manage access to datasets using dataset-level ACLs
  • Set row-level permissions using views


11. Exporting Data

  • Methods of exporting data from BigQuery and the data formats available
  • Process of creating a job to export data from BigQuery
  • Purpose of wildcard exports to partition export data
  • Export data from BigQuery using the web UI and CLI
  • Export large tables using wildcard URIs


12. Interfacing with External Tools

  • How to use external tools to interface with BigQuery, including: spreadsheets, ODBC and JDBC drivers, the BigQuery encrypted client, and R
  • Set up the BigQuery Reports add-on for Google Sheets
  • Use the Reports add-on to query BigQuery data


13. Working with Google Analytics Premium Data

  • Schema of the Google Analytics Premium and AdSense data exported to BigQuery
  • Build queries to analyze data from Google Analytics Premium


14. Data Visualization

  • Options available for visualizing BigQuery data
  • Use Google Cloud Datalab to visualize data