ITVersity, Inc.

Overview of Database Views

Overview of Common Table Expressions or CTEs

Outer Join with Additional Conditions in SQL Queries

Explanation about Fix of SQL Queries with Filtering on Outer Join Results

Introduction to Cumulative Aggregations and Ranking in SQL Queries

Overview of CTAS to create tables based on Query Results

Create Tables for Cumulative Aggregations and Ranking

Overview of OVER and PARTITION BY Clause in SQL Queries

Compute Total Aggregation using OVER and PARTITION BY in SQL Queries

Overview of Ranking in SQL

Compute Global Ranks using SQL

Compute Ranks based on key using SQL

Rules and Restrictions to Filter Data based on Ranks in SQL

Filtering based on Global Ranks using Nested Queries and CTEs in SQL

Filtering based on Ranks per Partition using Nested Queries and CTEs in SQL

Create Students table with Data for ranking using SQL

Difference between rank and dense rank using SQL

Introduction to SQL Troubleshooting and Debugging Guide

Overview of Database Connectivity Issues

Validate and Setup Telnet on Mac or PC

Validate Connectivity to Database Server using telnet

Troubleshoot Database Connectivity Issue with Correct Host Details

Current Databases and Users in Postgres Database Server

Troubleshoot Database Credentials and Permissions Issues

Overview of Compilation of SQL Queries

Troubleshooting Syntax Errors in SQL Queries

Troubleshooting Semantec Errors in SQL Queries

Overview of Bugs in SQL Queries

Development Best Practices with tips to troubleshoot SQL bugs

Develop Initial Solution based on the requirement

Identify and Troubleshoot Bugs in SQL Queries

Develop Solution using Development Best Practices

Introduction to Performance Tuning of SQL Queries

Overview of SQL Compilation Process and Explain Plans

Generate Explain Plans for SQL Queries

Review Tables used for Performance Tuning of SQL Queries

Review Data Storage Internals for Tables and Indexes

Review key terms used in Explain Plans for SQL Queries

Interpret Explain Plans for Basic SQL Queries

Review the Common Application Scenarios for Performance Tuning

Write SQL Queries for Customer Orders

Performance Testing of SQL Queries using Stored Procedure

Add Required Indexes to tune performance of SQL Queries

Guidelines on adding Indexes on Tables for SQL Queries

Interpreting the explain plan for SQL Queries using Indexes

Conclusion of Performance Tuning of SQL Queries

Simple Exercises for Filtering and Aggregations

Solutions for Filtering and Aggregations

Solutions for Filtering and Aggregations

Exercises on Joins and Aggregations using SQL

Validate Data and Review Data Model Diagram

Solution for Exercise 1 to get Customer Order Count

Solution for Exercise 2 to get Dormant Customers using Outer Join

Solution for Exercise 3 to get Revenue Per Customer using Outer Join

Solution for Exercise 4 to get Revenue Per Category

Solution for Exercise 5 to get Product Count Per Department

Setup Visual Studio Workspace for Python Application Development

Setup Notebook Environment in VS Code Workspace

Overview of VS Code Notebook Environment

Overview of Cells in VS Code Notebook

Defining Functions in VS Code Notebooks

Run the Code in VS Code Notebook Cell by Line

Constants and Variables in Python

Overview of Python Data Types

Getting help on Python Variables and Functions

Pre-Defined String Manipulation Functions

Overview of Python Lists

Loops and Conditions in Python

User Defined Functions in Python

Overview of File IO using Python.

Read Data from CSV File into Python List

Overview of Python Collections.

Getting Started with Processing Python Lists

Overview of Lambda Functions in Python.

Usage of Lambda Functions.

Filter Data in Python Lists using filter and lambda

Get unique values from list using map and set.

Sort Python lists using key.

Read JSON Strings to Python dicts or lists.

Read JSON Schemas from file to Python dicts.

Overview of Processing JSON Data using Python.

Extract Details from Complex JSON Arrays using Python.

Sort Data in JSON Arrays using Python.

Create Function to get Column Details from Schemas JSON File.

Overview of JSON Strings and Files.

Overview of Pandas for Data Processing

Overview of Reading CSV Data using Pandas

Read Data from CSV Files to Pandas Dataframes

Filter Data in Pandas Dataframe using query

Get Count by Status using Pandas Dataframe APIs

Get count by Month and Status using Pandas Dataframe APIs

Create Dataframes using dynamic column list on CSV Data

Performing Inner Join between Pandas Dataframes

Perform Aggregations on Join results

Sort Data in Pandas Dataframes

Overview of Writing Pandas Dataframes to Files

Write Pandas Dataframes to JSON Files

Get File Names to be processed using glob

Get Column Names using Schemas File

Get Data Set Names from File Names or Paths using regular expressions

Read CSV Data into Pandas Dataframe with Schema Dynamically

Generate File Paths for Target JSON Files Dynamically

Recap of Writing Pandas Dataframe to JSON File

Write Pandas Dataframe to JSON Files

Modularize File Format Converter for Dataset

Wrapper to Process all Data Sets

Setup Project for File Format Converter using Python

Install Dependencies for the Python Project using pip

Add Core Logic to Python Application

Overview of Run-time Arguments and Environment Variables

Using Run Time Arguments in Python Applications

Overview of Environment Variables

Setting Environment Variables on Windows or Mac or Linux

Use Environment Variables in Python Applications

Use Environment Variables in File Format Converter

Pass JSON Array as argument to Python Applications

Pass Data Sets as Run Time Arguments to File Format Converter

Exception Handling in Python Applications

Raising Exceptions in Python Applications

Exception Handling in File Format Converter Application

Install Python Dependencies for Pandas and Database Integration

Run Queries from Notebook using SQL Magic

Validate Pandas and SQL Integration

Write CSV Data from File to Database Table

Write CSV Data from Files to Database Tables in Chunks

Overview of Deploying File to DB Loader Project

Introduction to Troubleshooting and Debugging Python issues

Guidelines for Troubleshooting and Debugging Python related Issues

Overview of Database Connectivity using Python Applications

Overview of Database Connectivity using Python

Troubleshoot Network Connectivity to the Database Server using telnet

Troubleshoot Module Related issues for Database Connectivity using Python

Troubleshoot Credentials Related issues for Database Connectivity using Python

Overview of Python process to run Python Applications

Troubleshooting Compilation Errors in Python

Troubleshooting Run Time Errors in Python

Overview of Software Development Life Cycle

Overview of Unit Testing or Validation of Applications

Overview of Debugging VS Code Notebooks using Debug Feature

Debug VS Code Notebooks using Debug Feature

Getting Started with Debugging of Python Programs using VS Code

Recap of running File Format Converter application

Debug Python Application using VS Code with breakpoints

Managing Breakpoints for Debugging in VS Code

Conclusion to Troubleshooting and Debugging Python Issues

Introduction to Performance of Python Applications

Setup Database Loader Python Application

Ensure Postgres Database is setup for file to db loader Python Application

Cleanup the tables to run file to db loader application

Run and Validate File to DB Loader Application

Fix the error message in file to db loader application

Overview of Execution of file to db loader application

Performance Tuning using Chunksize in Pandas

Review Pandas Data Frame API to load data into the target table

Overview of multi or batch insert into Database Tables

Develop application for multiprocessing

Getting Started with Multiprocessing using Python

Invoking User Defined Functions using multiprocessing in Python

Refactor File to Database Loader Application for Multiprocessing

Add Parallel Processing to file to db loader Python Application

Validate File to DB Loader Application with Multiprocessing

Understanding the concept of Multiprocessing in Python

Performance Tuning Scenarios of Python Applications

Getting Started with ITVersity Big Data Labs

Reviewing Big Data Cluster using Grafana

Launching Terminal using Jupyter Environment

Connecting to Gateway Node using Mac or Linux Terminal

Setup Password less login to Gateway Node

Running HDFS Commands

Launching Hive CLI

Setup Development Environment using VS Code Remote Development

Review Data Sets Provided as part of Gateway Nodesmp4

Validate HDFS on Multi Node Hadoop and Spark Cluster from Gateway Node

Validate Hive on Hadoop and Spark Multinode Cluster

Review Hadoop HDFS and YARN Property Files on Hadoop and Spark Cluster

Review Hadoop HDFS and YARN Property Files using Visual Studio Code Editor

Review Hive Property Files on Multinode Hadoop and Spark Cluster

Review Spark 2 Property Files and Important Properties

Validate Spark Shell CLI using Spark 3

Validate Spark Shell CLI using Spark 2

Review Spark 3 Property Files and Important Properties

Validate Pyspark CLI using Spark 3

Validate Pyspark CLI using Spark 2

Validate Spark SQL CLI using Spark 2

Validate Spark SQL CLI using Spark 3

Getting help or usage

Listing HDFS Files

Managing HDFS Directories

Copying files from local to HDFS

Copying files from HDFS to local

Getting File Metadata

Previewing Data in HDFS Files

HDFS Block Size

HDFS Replication Factor

Getting HDFS Storage Usage

Using HDFS Stat Commands

HDFS File Permissions

Overriding Properties

Getting Started - Overview

Overview of Spark Documentation

Launching and using Spark SQL CLI

Overview of Spark SQL Properties

Running OS Commands using Spark SQL

Understanding Warehouse Directory

Managing Spark Metastore Tables

Retrieve Metadata of Tables

Managing Spark Metastore Databases

Role of Spark Metastore or Hive Metastore

Exercise - Getting Started with Spark SQL

Basic Transformations - Introduction

Spark SQL - Overview

Define Problem Statement

Prepare Tables

Projecting Data

Filtering Data

Joining Tables - Inner

Joining Tables - Outer

Aggregating Data

Sorting Data

Conclusion - Final Solution

Introduction

Create Spark Metastore Tables

Overview of Data Types

Adding Comments

Loading Data Into Tables - Local

Loading Data Into Tables - HDFS

Loading Data - Append and Overwrite

Creating External Tables

Managed Tables vs External Tables

Overview of File Formats

Drop Tables and Databases

Truncating Tables

Exercise - Managed Tables

Introduction - DML and Partitioning

Introduction to Partitioning

Creating Tables using Parquet

Load vs. Insert

Inserting Data using Stage Table

Creating Partitioned Tables

Adding Partitions to Tables

Loading Data into Partitioned Tables

Inserting Data into Partitions

Using Dynamic Partition Mode

Exercise - Partitioned Tables

Introduction - Overview of Spark SQL Functions

Overview of Functions

Validating Functions

String Manipulation Functions

Date Manipulation Functions

Overview of Numeric Functions

Data Type Conversion

Dealing with Nulls

Using CASE and WHEN

Query Example - Word Count

Introduction - Windowing Functions

Prepare HR Database

Overview of Windowing Functions

Aggregations using Windowing Functions

Using LEAD or LAG

Getting first and last values

Ranking using Windowing Functions

Overview of Subqueries

Filtering Window Function Results

Order of execution of SQL

Starting Spark Context - pyspark

Overview of Spark Read APIs

Understanding airlines data

Inferring Schema

Previewing Airlines Data

Overview of Data Frame APIs

Overview of Functions

Overview of Spark Write APIs

Overview of Predefined Functions in Spark

Create Dummy Data Frame

Categories of Functions

Special Functions - col and lit

Common String Manipulation Functions

Extracting Strings using substring

Extracting Strings using split

Padding Characters around Strings

Trimming Characters from Strings

Date and Time Manipulation Functions

Date and Time Arithmetic

Using Date and Time Trunc Functions

Date and Time Extract Functions

Using to_date and to_timestamp

Using date_format Function

Dealing with Unix Timestamp

Dealing with Nulls

Using CASE and WHEN

Overview of Basic Transformations

Data Frame for basic transformations

Basic Filtering of Data

Filtering Example using dates

Boolean Operators

Using IN Operator or isin Function

Using LIKE Operator or like Function

Using BETWEEN Operator

Dealing with Nulls while Filtering

Total Aggregations

Aggregate data using groupBy

Aggregate data using rollup

Aggregate data using cube

Overview of Sorting Data Frames

Solution - Problem 1 - Get Total Aggregations

Solution - Problem 2 - Get Aggregations By FlightDate

Solution - Problem 3 - Row Level Transformations

Prepare Datasets for Joins

Analyze Datasets for Joins

Problem Statements for Joins

Overview of Joins

Using Inner Joins

Left or Right Outer Join

Solution - Get Flight Count Per US Airport

Solution - Get Flight Count Per US State

Solution - Get Dormant US Airports

Solution - Get Origins without master data

Solution - Get Count of Flights without master data

Solution - Get Count of Flights per Airport without master data

Solution - Get Daily Revenue

Solution - Get Daily Revenue rolled up till Yearly

Overview of Spark Metastore

Exploring Spark Catalog

Creating Metastore Tables using catalog

Inferring Schema for Tables

Define Schema for Tables using StructType

Inserting into Existing Tables

Read and Process data from Metastore Tables

Create Partitioned Tables

Saving as Partitioned Table

Creating Temporary Views

Using Spark SQL

Introduction to Getting Started with Semi Structured Data using Spark

Create Spark Metastore Table with Special Data Types

Overview of ARRAY Type in Spark Metastore Table

Overview of MAP and STRUCT Type in Spark Metastore Table

Insert Data into Spark Metastore Table with Special Type Columns

Create Spark Data Frame with Special Data Types

Create Spark Data Frame with Special Types using Python List

Insert Spark Data Frame with Special Types into Spark Metastore Table

Review Data in the JSON File with Special Data Types

Setup JSON Data Set to explore Spark APIs on Special Data Type Columns

Read JSON Data with Special Types into Spark Data Frame

Flatten Array Fields in Spark Data Frames using explode and explode_outer

Concatenate Array Values into Delimited String using Spark APIs

Convert Delimited Strings from Spark Data Frame Columns to Arrays

Setup Data Sets to Build Arrays using Spark.cmproj

Read JSON Data into Spark Data Frame and Review Aggregate Operations

Get Size or Length of Array Type Columns in Spark Data Frame

Build Arrays from Flattened Rows of Spark Data Frame

Getting Started with Spark Data Frames with Struct Columns

Concatenate Struct Column Values in Spark Data Frame

Filter Data on Struct Column Attributes in Spark Data Frame

Create Spark Data Frame using Map Type Column

Project Map Values as Columns using Spark Data Frame APIs

Conclusion of Getting Started with Semi Structured Data using Spark

Introduction to Process Semi Structured Data using Spark Data Frame APIs

Review the Data Sets to generate denormalized JSON Data using Spark

Setup JSON Data Sets in HDFS using HDFS Command

Create Spark Data Frames using Data Frame APIs

Join Orders and Order Items using Spark Data Frame APIs

Generate Struct Field for Order Details using Spark

Generate Array of Struct Field for Order Details using Spark

Join Data Sets to generate denormalized JSON Data using Spark

Denormalize Join Results using Spark Data Frame APIs

Write Denormalized Customer Details to JSON Files using Spark

Publish JSON Files for downstream applications

Read Denormalized Data into Spark Data Frame

Filter Denormalized Data Frame using Spark APIs

Perform Aggregations on Denormalized Data Frame using Spark

Flatten Semi Structured Data or Denormalized Data using Spark

Compute Monthly Customer Revenue using Spark on Denormalized Data

Conclusion of Processing Semi Structured Data using Spark Data Frame APIs

Setup Virtual Environment and Install Pyspark

Getting Started with Pycharm

Passing Run Time Arguments

Accessing OS Environment Variables

Getting Started with Spark

Create Function for Spark Session

Setup Sample Data

Read data from files

Process data using Spark APIs

Write data to files

Validating Writing Data to Files

Productionizing the Code

Setting up Data for Production Validation

Running the application using YARN

Detailed Validation of the Application

Deploying and Monitoring Spark Applications - Introduction

Overview of Types of Spark Cluster Managers

TBR Setup EMR Cluster with Hadoop and Spark

Overall Capacity of Big Data Cluster with Hadoop and Spark

Understanding YARN Capacity of an Enterprise Cluster

Overview of Hadoop HDFS and YARN Setup on Multi-node Cluster

Overview of Spark Setup on top of Hadoop

Setup Data Set for Word Count application

Develop Word Count Application

Review Deployment Process of Spark Application

Overview of Spark Submit Command

Switching between Python Versions to run Spark Applications or launch Pyspark CLI

Switching between Pyspark Versions to run Spark Applications or launch Pyspark CLI

Review Spark Configuration Properties at Run Time

Develop Shell Script to run Spark Application

Run Spark Application and review default executors

Overview of Spark History Server UI

Setup SSH Proxy to access Spark Application logs - Introduction

Overview of Private and Public ips of servers in the cluster

Overview of SSH Proxy

Setup sshuttle on Mac or Linux

Proxy using sshuttle on Mac or Linux

Accessing Spark Application logs via SSH Proxy using sshuttle on Mac or Linux

Side effects of using SSH Proxy to access Spark Application Logs

Steps to setup SSH Proxy on Windows to access Spark Application Logs

Setup PuTTY and PuTTYgen on Windows

Quick Tour of PuTTY on Windows

Configure Passwordless Login using PuTTYGen Keys on Windows

Run Spark Application on Gateway Node using PuTTY

Configure Tunnel to Gateway Node using PuTTY on Windows for SSH Proxy

Setup Proxy on Windows and validate using Microsoft Edge browser

Understanding Proxying Network Traffic overcoming Windows Caveats

Update Hosts file for worker nodes using private ips

Access Spark Application logs using SSH Proxy

Overview of performing tasks related to Spark Applications using Mac

Deployment Modes of Spark Applications - Introduction

Default Execution Master Type for Spark Applications

Launch Pyspark using local mode

Running Spark Applications using Local Mode

Overview of Spark CLI Commands such as Pyspark

Accessing Local Files using Spark CLI or Spark Applications

Overview of submitting spark application using client deployment mode

Overview of submitting spark application using cluster deployment mode

Review the default logging while submitting Spark Applications

Changing Spark Application Log Level using custom log4j properties

Submit Spark Application using client mode with log level info

Submit Spark Application using cluster mode with log level info

Submit Spark Applications using SPARK_CONF_DIR with custom properties files

Submit Spark Applications using Properties File