Orientation to SQL on Big Data-Introduction
Welcome to the Course
()
Review and Preparation
()
Instructions for Downloading and Installing the Exercise Environment
Troubleshooting the VM
(Optional) What about Spark SQL?
Expectations for Learners
Orientation to SQL on Big Data-Running SQL Statements
Using the Hue Query Editors
()
Running SQL Utility Statements
()
Running SQL SELECT Statements
()
Understanding Different SQL Interfaces
()
(Optional) Using Other SQL Engines
Orientation to SQL on Big Data-Using Beeline and Impala Shell
Overview of Beeline and Impala Shell
()
Using Beeline
()
Using Impala Shell
()
SQL SELECT Essentials-Introduction to Week 2
Introduction
()
SQL SELECT Building Blocks
()
SQL SELECT Essentials-The SELECT List
Introduction to the SELECT List
()
Expressions and Operators
()
Order of Operations
Division and Modulo Operators
SQL SELECT Essentials-More about the SELECT List
Data Types
()
Column Aliases
()
Built-In Functions
()
Common String Functions
Data Type Conversion
()
The DISTINCT Keyword
()
SQL SELECT Essentials-The FROM Clause
Introduction to the FROM Clause
()
Identifiers
()
Formatting SELECT Statements
()
Case (In)Sensitivity in SQL
SQL SELECT Essentials-Using Beeline and Impala Shell in Non-Interactive Mode
Using Beeline in Non-Interactive Mode
()
Using Impala Shell in Non-Interactive Mode
()
Formatting the Output of Beeline and Impala Shell
()
Saving Hive and Impala Query Results to a File
()
Filtering Data-Introduction to Week 3
Introduction
()
About the Datasets
()
Data Reference
(Optional) Unicode Characters
Filtering Data-The WHERE Clause
Introduction to the WHERE Clause
()
Using Expressions in the WHERE Clause
()
Filtering Data-Using Operators and Functions in the WHERE Clause
Comparison Operators
()
Data Types and Precision
()
Working with Literal Strings
Logical Operators
()
Other Relational Operators
()
Filtering Data-Working with Missing Values
Understanding Missing Values
()
Handling Missing Values
()
Missing Values with Logical Operators
Missing Values in String Columns
Conditional Functions
()
Filtering Data-Using Hive and Impala in Scripts and Applications
Using Variables with Beeline and Impala Shell
()
Calling Beeline and Impala Shell from Scripts
()
(Optional Exercise) Change VM Desktop Color
Querying Hive and Impala in Scripts and Applications
()
Grouping and Aggregating Data-Introduction to Week 4
Introduction
()
Grouping and Aggregating Data-Aggregate Operations
Introduction to Aggregation
()
Common Aggregate Functions
()
COUNT(*) and SUM(1)
Using Aggregate Functions in the SELECT Statement
()
Interpreting Aggregates: Populations and Samples
The least and greatest Functions
Grouping and Aggregating Data-The GROUP BY Clause
Introduction to the GROUP BY Clause
()
Choosing an Aggregate Function and Grouping Column
()
Grouping Expressions
()
Grouping and Aggregation, Together and Separately
()
Grouping and Aggregating Data-More about Grouping and Aggregation
NULL Values in Grouping and Aggregation
()
Why Aggregate Expressions Ignore NULL Values
The COUNT Function
()
Tips for Applying Grouping and Aggregation
()
(Optional) Shortcuts for Grouping
How Grouping and Aggregation Can Mislead
Grouping and Aggregating Data-The HAVING Clause
Filtering on Aggregates
()
The HAVING Clause
()
Grouping and Aggregating Data-Working with Different Versions of Hue, Hive, and Impala
Understanding Hive and Impala Version Differences
()
Understanding Hue Version Differences
()
Sorting and Limiting Data-Introduction to Week 5
Introduction
()
Sorting and Limiting Data-The ORDER BY Clause
Introduction to the ORDER BY Clause
()
Controlling Sort Order
()
Ordering Expressions
()
Ordering by String Columns
Missing Values in Ordered Results
()
Using ORDER BY with Hive and Impala
()
Sorting and Limiting Data-The LIMIT Clause
Introduction to the LIMIT Clause
()
When to Use the LIMIT Clause
()
Using LIMIT with ORDER BY
()
Using LIMIT for Pagination
()
Sorting and Limiting Data-Review of the SELECT Statement
Review
()
Sorting and Limiting Data-Using the Hive and Impala Documentation
How to Effectively Use the Hive and Impala Documentation
()
Tips for Using the Hive Documentation
()
Tips for Using the Impala Documentation
()
Combining Data-Introduction to Week 6
Introduction
()
Combining Data-UNION and UNION ALL
Combining Query Results with the UNION Operator
()
Missing or Truncated Values from Type Conversion
Using ORDER BY and LIMIT with UNION
()
Using UNION to Combine Three or More Results
Combining Data-Joins
Introduction to Joins
()
Join Syntax
()
Inner Joins
()
Outer Joins
()
Alternative Join Syntax
Joining Three or More Tables
Combining Data-Peer-Graded Assignment and Conclusion
Conclusion
()
Combining Data-Advanced Joins
Handling NULL Values in Join Key Columns
()
Non-Equijoins
()
Cross Joins
()
Left Semi-Joins
()
Specifying Two or More Join Conditions