0345 4506120

Google BigQuery for Data Analysts

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.

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

 

Reset

Learning 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

Pre-Requisites

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

Course Content

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

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the request service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

Our Customers Include