Training offering

Google Cloud Platform BigQuery for Data Analysts

Information

Price £1,695 before tax
Length: 3 Days
Course code: GOG_GCP-BQDA
Delivery Type

Session dates

This training is also available as onsite training. Feel free to contact us for more information.

Description



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




Objectives



  • 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




Participants



  • 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




Prerequisite(s)



  • 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




Programme

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




© 2018 Qual - Arrow ECS. All rights reserved.