Google BigQuery for Data Analysts

3 Day Course
Hands On
Code GO8327

This course has been retired. Please view currently available Google.

Modules

Hide all

Introducing Google BigQuery (4 topics)

  • 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

BigQuery Functional Overview (7 topics)

  • 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

BigQuery Fundamentals (8 topics)

  • 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

Ingesting, Transforming, and Storing Data (11 topics)

  • 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

Pricing and Quotas (6 topics)

  • 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

Clauses and Functions (6 topics)

  • 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

Nested and Repeated Fields (8 topics)

  • 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

Query Performance (6 topics)

  • 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

Troubleshooting Errors (5 topics)

  • 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

Access Control (6 topics)

  • 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

Exporting Data (5 topics)

  • 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

Interfacing with External Tools (3 topics)

  • 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

Working with Google Analytics Premium Data (2 topics)

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

Data Visualization (2 topics)

  • Options available for visualizing BigQuery data
  • Use Google Cloud Datalab to visualize 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

Additional Learning

The courses below may help you meet the knowledge level required to take this course. If you are unsure please ask a training advisor .

Course PDF

Print

Sections