Analyzing Big Data with SQL

Module 1: Orientation to SQL on Big Data

Welcome to the Course

00:00
Review and Preparation

00:00
Using the Hue Query Editors

00:00
Running SQL Utility Statements

00:00
Running SQL SELECT Statements

00:00
Understanding Different SQL Interfaces

00:00
Overview of Beeline and Impala Shell

00:00
Using Beeline

00:00
Using Impala Shell

00:00

5 readings

2 quizzes

1 discussion prompt

Module 2: SQL SELECT Essentials

Introduction

00:00
SQL SELECT Building Blocks

00:00
Introduction to the SELECT List

00:00
Expressions and Operators

00:00
Data Types

00:00
Column Aliases

00:00
Built-In Functions

00:00
Data Type Conversion

00:00
The DISTINCT Keyword

00:00
Introduction to the FROM Clause

00:00
Identifiers

00:00
Formatting SELECT Statements

00:00
Using Beeline in Non-Interactive Mode

00:00
Using Impala Shell in Non-Interactive Mode

00:00
Formatting the Output of Beeline and Impala Shell

00:00
Saving Hive and Impala Query Results to a File

00:00

4 readings

2 quizzes

1 discussion prompt

Module 3: Filtering Data

Introduction

00:00
About the Datasets

00:00
Introduction to the WHERE Clause

00:00
Using Expressions in the WHERE Clause

00:00
Comparison Operators

00:00
Data Types and Precision

00:00
Logical Operators

00:00
Other Relational Operators

00:00
Understanding Missing Values

00:00
Handling Missing Values

00:00
Conditional Functions

00:00
Using Variables with Beeline and Impala Shell

00:00
Calling Beeline and Impala Shell from Scripts

00:00
Querying Hive and Impala in Scripts and Applications

00:00

6 readings

2 quizzes

1 discussion prompt

Module 4: Grouping and Aggregation Data

Introduction

00:00
Introduction to Aggregation

00:00
Common Aggregate Functions

00:00
Using Aggregate Functions in the SELECT Statement

00:00
Introduction to the GROUP BY Clause

00:00
Choosing an Aggregate Function and Grouping Column

00:00
Grouping Expressions

00:00
Grouping and Aggregation, Together and Separately

00:00
NULL Values in Grouping and Aggregation

00:00
The COUNT Function

00:00
Tips for Applying Grouping and Aggregation

00:00
Filtering on Aggregates

00:00
The HAVING Clause

00:00
Understanding Hive and Impala Version Differences

00:00
Understanding Hue Version Differences

00:00

6 readings

2 quizzes

2 discussion prompts

Module 5: Sorting and Limiting Data

Introduction

00:00
Introduction to the ORDER BY Clause

00:00
Controlling Sort Order

00:00
Ordering Expressions

00:00
Missing Values in Ordered Results

00:00
Using ORDER BY with Hive and Impala

00:00
Introduction to the LIMIT Clause

00:00
When to Use the LIMIT Clause

00:00
Using LIMIT with ORDER BY

00:00
Using LIMIT for Pagination

00:00
Review

00:00
How to Effectively Use the Hive and Impala Documentation

00:00
Tips for Using the Hive Documentation

00:00
Tips for Using the Impala Documentation

00:00

1 reading

2 quizzes

1 discussion prompt

Module 6: Combining Data

Introduction

00:00
Combining Query Results with the UNION Operator

00:00
Using ORDER BY and LIMIT with UNION

00:00
Introduction to Joins

00:00
Join Syntax

00:00
Inner Joins

00:00
Outer Joins

00:00
Conclusion

00:00
Handling NULL Values in Join Key Columns

00:00
Non-Equijoins

00:00
Cross Joins

00:00
Left Semi-Joins

00:00

5 readings

2 quizzes

1 peer review

1 discussion prompt

What Will You Learn?

Course Content

Module 1: Orientation to SQL on Big Data

Welcome to the Course

Review and Preparation

Using the Hue Query Editors

Running SQL Utility Statements

Running SQL SELECT Statements

Understanding Different SQL Interfaces

Overview of Beeline and Impala Shell

Using Beeline

Using Impala Shell

5 readings

Instructions for Downloading and Installing the Exercise Environment

Troubleshooting the VM

(Optional) What about Spark SQL?

Expectations for Learners

(Optional) Using Other SQL Engines

2 quizzes

Week 1 Core Quiz

Week 1 Honors Quiz

1 discussion prompt

Your Expectations

Module 2: SQL SELECT Essentials

Introduction

SQL SELECT Building Blocks

Introduction to the SELECT List

Expressions and Operators

Data Types

Column Aliases

Built-In Functions

Data Type Conversion

The DISTINCT Keyword

Introduction to the FROM Clause

Identifiers

Formatting SELECT Statements

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

4 readings

Order of Operations

Division and Modulo Operators

Common String Functions

Case (In)Sensitivity in SQL

2 quizzes

Week 2 Core Quiz

Week 2 Honors Quiz

1 discussion prompt

Data Analysis and You

Module 3: Filtering Data

Introduction

About the Datasets

Introduction to the WHERE Clause

Using Expressions in the WHERE Clause

Comparison Operators

Data Types and Precision

Logical Operators

Other Relational Operators

Understanding Missing Values

Handling Missing Values

Conditional Functions

Using Variables with Beeline and Impala Shell

Calling Beeline and Impala Shell from Scripts

Querying Hive and Impala in Scripts and Applications

6 readings

Data Reference

(Optional) Unicode Characters

Working with Literal Strings

Missing Values with Logical Operators

Missing Values in String Columns

(Optional Exercise) Change VM Desktop Color

2 quizzes

Week 3 Core Quiz

Week 3 Honors Quiz

1 discussion prompt

Filtering Examples

Module 4: Grouping and Aggregation Data

Introduction

Introduction to Aggregation