BigQuery for Data Analysts
What you’ll learn
Learn how to work with SQL functions for math, strings, datetimes, and more
Use structured and repeated fields for efficient data modeling
Apply access controls to columns, rows, tables, and datasets
Cost controls and performance optimizations
Requirements
Some basic experience with BigQuery
Description
Analyze data in BigQuery using both basic SQL statements as well as specialized functions to help you gain insight into your data. If you need to analyze data in BigQuery and have some familiarity with using BigQuery, then this course will help you expand your skills with lectures, quizzes, and assignments. (If you are new to BigQuery, consider taking the Introduction to BigQuery course first.)The course begins with data definition language statements for creating and altering tables and views followed by a review of data manipulation statements, including SELECT, INSERT, UPDATE, DELETE, and MERGE. Learn how to use common table expressions (CTEs) effectively to modularize your SQL and make even complex logic easy to understand.Note: There are several basic videos reviewing SELECT statements which you can skip if you have taken Introduction to BigQuery or are comfortable writing SELECT statements.Working with BigQuery has some differences from working with relational databases like Postgres and SQL Server. We often use arrays and structures in BigQuery and this course will teach you how to work with arrays, querying and unnesting arrays, using array functions as well as working with structures and arrays of structures.SQL functions are foundational building blocks for data analytics work and it is important to know the functions available in BigQuery to rapidly and effectively analyze data. In this course, we will learn about a range of SQL functions for:Math and statisticsAggregate functionsData type castingDates and timesString manipulationRegular expressionsApproximate functions for working with large datasetsAnalytical and window functionsData analysts need to be familiar with BigQuery operations so the course also covers BigQuery security, including access controls, column security, and row-level security, as well as performance and cost management.Test your understanding of BigQuery concepts with 8 assignments and 10 quizzes.In this course, you will learn a wide range of topics that data analysts need to understand and you will know how to apply that knowledge effectively in BigQuery while working cost-effectively. After completing this course you will know a wide range of functions and techniques for analyzing and transforming data in BigQuery, understand how access to data is controlled using IAM, row-level, and column security, and how to control the cost of your BigQuery operations.
Overview
Section 1: Introduction
Lecture 1 Introduction
Section 2: Creating Tables and Views in BigQuery
Lecture 2 Creating Tables using the BigQuery Console
Lecture 3 Creating Tables using the bq Command
Lecture 4 Creating Tables using SQL Statements
Lecture 5 Creating Views using the BigQuery Console
Lecture 6 Creating Views using SQL Statements
Lecture 7 Altering a Table
Section 3: Review of Querying with SELECT Statements
Lecture 8 Working with a 1 Table Public Data Set
Lecture 9 Working with a 2 Table Public Data Set
Lecture 10 Basic Select Statements
Lecture 11 WHERE Clauses
Lecture 12 ORDER BY Clauses
Lecture 13 GROUP BY Clauses
Lecture 14 WHERE, GROUP BY, and ORDER BY Clauses
Lecture 15 HAVING Clauses
Lecture 16 Aggregate Functions
Lecture 17 Simple Math Expressions
Lecture 18 CASE Statements and Error Checking
Lecture 19 Joining Two Tables
Lecture 20 Common Table Expressions
Section 4: Data Manipulation Language Statements
Lecture 21 Inserting Data
Lecture 22 Updating Data
Lecture 23 Deleting and Truncating Data
Lecture 24 Merging and Upserting Data
Section 5: Arrays and Structs
Lecture 25 Querying and Unnesting Arrays
Lecture 26 Array Functions
Lecture 27 Querying Structs
Lecture 28 Querying Arrays of Structs
Section 6: SQL Math and Cast Functions
Lecture 29 Basic Math Functions
Lecture 30 Sample Data for Numeric Functions
Lecture 31 ABS and SIGN Functions
Lecture 32 SQRT and POWER Functions
Lecture 33 RAND and MOD Functions
Lecture 34 Safe Operations
Lecture 35 Casting Data Types
Section 7: Date and Time Functions
Lecture 36 Date Functions
Lecture 37 Datetime Functions
Lecture 38 Time Functions
Lecture 39 Timestamp Functions
Section 8: Statistical Aggregate Functions
Lecture 40 Pearson Correlation with the CORR Function
Lecture 41 Calculating Covariance with COVAR Function
Lecture 42 Calculating Standard Deviation with STDDEV Functions
Section 9: String Functions
Lecture 43 Basic String Functions
Lecture 44 Trimming and Padding
Lecture 45 Splitting Delimited Strings
Lecture 46 Working with Substrings
Lecture 47 Matching with Regular Expressions
Section 10: Window Functions
Lecture 48 Introduction to Window Functions
Lecture 49 Lead and Lag Functions
Lecture 50 First Values Functions
Lecture 51 Numbering Functions
Lecture 52 Moving Average Functions
Section 11: Access Controls in BigQuery
Lecture 53 BigQuery and Identity and Access Management (IAM)
Lecture 54 Column Level Access Contols
Lecture 55 Row Level Security
Section 12: Cost Control and Query Performance Tips
Lecture 56 Query Performance Tips
Lecture 57 Cost Control Options
Section 13: Conclusion
Lecture 58 Next Steps
Data analysts,Data engineers,SQL developers,Database administrators,Analytics engineers
Course Information:
Udemy | English | 4h 56m | 1.55 GB
Created by: Dan Sullivan
You Can See More Courses in the IT & Software >> Greetings from CourseDown.com