Learn SQL for Data Analysis with Google Big Query
You’ll start by setting up your Sandbox Account, a free practice environment to explore BigQuery.
Next, you’ll learn how to add datasets and explore tables, understanding column data types along the way.
The course dives into the fundamentals of SQL, teaching you how to retrieve data with SELECT statements, filter results using WHERE clauses, and order your output with ORDER BY.
You’ll master advanced filtering techniques like BETWEEN, IN, LIKE, and combining conditions with AND, OR, and NOT.
The course covers handling NULL values and case sensitivity when working with text data.
Moving on, you’ll calculate metrics using aggregate functions like COUNT, SUM, AVG, MIN, MAX, VAR, and STD_DEV.
You’ll also learn to group data with GROUP BY and filter groups using the HAVING clause.
The course teaches you how to join tables together using different types of joins, such as INNER JOIN and LEFT JOIN.
You’ll also learn to create new columns using single-line functions like IF and CASE.
Finally, you’ll export your data to various formats, including Google Drive and CSV files, making it easy to share your analysis or integrate it with other tools like Google Data Studio.
Throughout the course, you’ll complete hands-on exercises, solidifying your understanding of each concept.
BigQuery for Big data engineers - Master Big Query Internals
You’ll start with an introduction to GCP and its various services like Compute, Storage, Big Data, and AI/ML.
This gives you a solid foundation to understand where BigQuery fits in the GCP ecosystem.
The course then dives deep into BigQuery itself.
You’ll learn about its architecture, features, and how it differs from traditional data warehouses.
You’ll get hands-on experience creating datasets, tables, and running queries using the BigQuery UI and command line tools.
One key aspect is learning efficient schema design principles for BigQuery, including working with nested and repeated columns.
This is crucial for optimizing performance and cost.
The course covers advanced BigQuery features like partitioned tables, clustered tables, views, and materialized views.
You’ll understand when to use each feature and how to implement them effectively.
Moving beyond just querying, you’ll learn how to load and query external data sources like Cloud Storage buckets.
You’ll also learn to build end-to-end data pipelines using Apache Beam to load data into BigQuery.
For those interested in streaming data, the course covers Google Pub/Sub and how to build streaming pipelines into BigQuery using Apache Beam.
Pricing and cost optimization are important topics covered, including understanding BigQuery’s pricing model, using the pricing calculator, and implementing best practices to reduce costs.
Additionally, you’ll explore different file formats like Avro, ORC, and Parquet, and learn how their performance compares for different use cases.
Introduction to Google Cloud BigQuery
You’ll start with an introduction to BigQuery, learning about its key features and data types.
From there, you’ll dive into the BigQuery console, getting hands-on experience with querying public datasets, exporting data, and saving your queries.
A significant portion of the course focuses on writing SQL queries using SELECT statements, WHERE clauses, ORDER BY, GROUP BY, HAVING, joins, and various SQL functions.
You’ll learn how to work with aggregate functions, date/time functions, math expressions, string functions, and the CASE statement for error handling.
Moving on, you’ll learn how to create tables and load data into BigQuery, whether from files or directly from a Cloud Storage bucket.
The course also covers using the command line interface, including installing the Cloud SDK, initializing the environment, uploading data with gsutil, and working with BigQuery using the bq command.
For those interested in using Python with BigQuery, there’s a section dedicated to Colab and interacting with BigQuery using Python clients, jobs, and dataframes.
Finally, you’ll gain insights into BigQuery’s architecture, including its component services, columnar data storage, partitioning and clustering tables, and performance tuning techniques.
Throughout the course, you’ll have opportunities to reinforce your learning with quizzes and hands-on exercises.
Google BigQuery & PostgreSQL : Big Query for Data Analysis
You’ll start by installing the necessary tools like PostgreSQL, pgAdmin, and setting up BigQuery on the Google Cloud Platform.
This hands-on approach ensures you gain practical experience from the get-go.
The course dives deep into fundamental SQL statements like CREATE, INSERT, SELECT, UPDATE, DELETE, and ALTER.
You’ll learn how to execute these commands in both PostgreSQL and BigQuery environments.
Exercises and quizzes are provided to reinforce your understanding.
Moving forward, you’ll explore advanced SQL concepts such as filtering data with IN, BETWEEN, and LIKE operators, ordering results with ORDER BY and LIMIT, and using aliases with AS.
Aggregate functions like COUNT, SUM, AVG, MIN, and MAX are covered in detail.
The course then delves into powerful techniques like GROUP BY and HAVING clauses, conditional statements with CASE WHEN, and various types of JOINs (INNER, LEFT, RIGHT, FULL OUTER, CROSS).
You’ll also learn set operations like INTERSECT, EXCEPT, and UNION.
Subqueries, a crucial aspect of complex SQL queries, are explained thoroughly.
The course even includes a fun “murder mystery” exercise to apply your SQL skills in a practical scenario.
You’ll gain insights into working with Views and Indexes, as well as string manipulation with functions like LENGTH, REPLACE, TRIM, CONCATENATION, and SUBSTRING.
Mathematical functions (CEIL, FLOOR, RANDOM, ROUND, POWER) and date-time functions (CURRENT DATE/TIME, AGE, EXTRACT) are also covered.
The course explores pattern matching using regular expressions, a valuable skill for data cleaning and transformation.
Finally, you’ll learn how to visualize BigQuery data using Google Data Studio and showcase your SQL proficiency on HackerRank.
Applied SQL For Data Analytics / Data Science With BigQuery
You’ll start by learning how to write basic SQL queries in BigQuery, including SELECT statements, aggregations, and subqueries.
The course then dives into more advanced SQL concepts like JOINs, window functions (ROW_NUMBER(), LAG()), and analytical use cases like calculating moving averages and identifying top N values.
One unique aspect of this course is its focus on real-world datasets and problems.
You’ll work with data from Stack Overflow, analyzing programming language trends and extracting insights from question titles and tags.
There’s even a section on using Python to generate larger SQL statements, which can be handy for automating complex queries.
The course also covers text processing with regular expressions, using examples from the Advent of Code programming challenges.
This hands-on approach helps reinforce the concepts and provides practical experience.
Another highlight is the Google Analytics attribution analysis section.
You’ll learn how to analyze multichannel funnels and paths, and implement different attribution models like last-click, first-click, uniform, and segmented uniform attribution.
This is particularly valuable for anyone working with marketing or e-commerce data.
The course even touches on the latest Google Analytics 4 (GA4) platform, teaching you how to work with the new data model, event parameters, and attribution in BigQuery.
This ensures you’re up-to-date with the latest industry trends.
Throughout the course, you’ll have the option to use ChatGPT for accelerated learning and example generation, which can be a valuable resource for those who prefer interactive learning.
Google BigQuery for Marketers and Agencies - 2022
The course starts by introducing you to Google BigQuery and SQL, setting up your BigQuery Sandbox account on Google Cloud Platform (GCP), and navigating the interface.
You’ll quickly dive into creating and querying tables using SQL.
You’ll learn how to ingest a CSV dataset into BigQuery, write your first SELECT, FROM, and LIMIT queries, and aggregate data with SUM, COUNT, GROUP BY, and aliases.
The course also covers filtering with WHERE, sorting with ORDER BY, and understanding subqueries using the WITH clause.
One unique aspect is exploring semi-structured data with ARRAY_AGG, UNNEST, and STRUCT.
You’ll learn about arrays, grouping values into hierarchies using STRUCT, creating arrays from flattened tables, and filtering/unnesting arrays.
The course then dives into combining multiple tables using different JOIN statements like INNER JOIN and LEFT JOIN.
You’ll calculate percentage differences within joins and handle many-to-many table relationships.
Two projects allow you to apply your skills.
The first explores eCommerce and CRM user-level data in Google Analytics 360, analyzing traffic sources, best-selling products/categories, and identifying high-value customers.
The second project focuses on visualizing BigQuery tables and queries in Google Data Studio, including writing custom SQL queries directly in Data Studio.
Throughout the course, you’ll work with real datasets, write queries, and complete practical activities to reinforce your learning.
The course covers key BigQuery features like arrays, joins, and integrations with tools like Google Analytics 360 and Data Studio.
ETL using Python: from MySQL to BigQuery
You’ll start by installing Python and creating a Google Account, which will give you access to BigQuery.
The course guides you through setting up a BigQuery project, dataset, and tables, as well as installing the Google SDK and authenticating with it.
You’ll also learn how to store connection properties and install the necessary modules.
The extraction phase covers connecting to MySQL and querying it using Python.
You’ll get an overview of Pandas, a powerful data manipulation library, and learn about its data types, filtering capabilities, and how to export data to CSV files.
In the transformation phase, you’ll dive into creating new columns in Pandas using SQL and Python functions, as well as performing various data transformations.
When it comes to loading data into BigQuery, the course covers creating tables using the BigQuery Console’s DDL (Data Definition Language) and file upload options.
You’ll then learn how to connect to BigQuery using Python and load data into it, with a focus on understanding the differences between truncating and appending data.
The course culminates in building a complete ETL program that ties everything together, and even includes bonus material for further learning.
Throughout the course, you’ll work with real-world examples and gain practical experience in using Python for ETL processes involving MySQL and BigQuery.
Practical Google BigQuery for those who already know SQL
You’ll start by learning how to set up and access BigQuery, including connecting it to Google Sheets.
The course will walk you through the BigQuery resource hierarchy, teaching you about datasets, tables, and views.
One of the key features covered is scheduled queries, which can automate your data analysis tasks.
You’ll also learn handy SQL editor shortcuts, both well-known and hidden ones, to boost your productivity.
The course refreshes your SQL skills, ensuring you have a solid foundation for working with BigQuery.
You’ll explore the advanced functionalities of the BigQuery UI, making the most of its powerful features.
Moving data in and out of BigQuery is a crucial skill, and the course covers that as well.
You’ll learn how to use BigQuery from Python, setting up the environment and working with the Python client, pandas, and Jupyter magic.
Understanding BigQuery’s inner workings is essential for efficient usage, and the course delves into the key concepts you need to grasp.
This includes how BigQuery operates and how its pricing model works.
You’ll learn to create tables with advanced options like partitioning, clustering, and retention policies.
The course also teaches you how to set up scheduled queries with advanced configurations.
The course covers useful tricks for working with BigQuery, such as monitoring the exact cost of each query and tracking your overall Google Cloud Platform costs related to BigQuery usage.
Additionally, you’ll learn how to access table column names and modification times directly from SQL, as well as how to recover deleted data using the SYSTEM TIME feature.
The course even shows you how to back up the SQL code for your BigQuery views using the Python client.
BigQuery for Data Analysts
You will learn how to create tables and views, query data using SQL, manipulate data, work with arrays and structs, use various SQL functions, implement access controls, and optimize query performance and cost.
You will start by learning how to create tables and views in BigQuery using different methods, including the BigQuery console, command-line interface, and SQL statements.
The course then dives into querying data with SELECT statements, covering essential concepts like WHERE, ORDER BY, GROUP BY, HAVING clauses, and aggregate functions.
Next, you will explore data manipulation language (DML) statements, enabling you to insert, update, delete, and merge data in BigQuery tables.
The course also covers working with arrays and structs, which are essential data types in BigQuery for handling complex data structures.
You will gain hands-on experience with SQL math and cast functions, date and time functions, statistical aggregate functions, and string functions.
These functions are crucial for data analysis and manipulation tasks.
The course also introduces you to window functions, which are powerful tools for performing calculations across rows or partitions of data.
You will learn about lead and lag functions, first values functions, numbering functions, and moving average functions.
Access controls in BigQuery are covered, including Identity and Access Management (IAM), column-level access controls, and row-level security.
These concepts are essential for managing data access and ensuring data privacy and security.
Finally, the course provides tips and best practices for optimizing query performance and controlling costs in BigQuery.
You will learn techniques for writing efficient queries and leveraging cost control options provided by BigQuery.
Throughout the course, you will work with real-world datasets and practical examples, ensuring you gain valuable hands-on experience with BigQuery and SQL.
BigQuery ML - Machine Learning in SQL using Google BigQuery
You’ll start by understanding the fundamentals of Google Cloud Platform (GCP) and its AI/ML services, with a focus on BigQuery ML (BQML).
The course dives deep into BQML’s features, advantages, and workflow, covering supported models like linear regression, logistic regression, k-means clustering, boosted trees, deep neural networks (DNNs), principal component analysis (PCA), matrix factorization, and ARIMA+ for time series forecasting.
You’ll learn how to set up a GCP account, navigate the BigQuery UI, and create datasets and tables.
For each model, you’ll understand the underlying concepts, create model queries, evaluate performance, and make predictions using real-world examples.
The course covers advanced topics like regularization, gradient descent, hyperparameter tuning, model explainability, and feature preprocessing.
You’ll also learn about BigQuery ML’s pricing models, reservations, and on-demand pricing.
Hands-on examples and quizzes reinforce your learning, ensuring you gain practical experience in building and deploying machine learning models within BigQuery.
Additionally, you’ll learn how to set up the Google Cloud SDK for seamless integration with BigQuery ML.