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
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
