Google BigQuery for Data Analysts (GO8327)

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