Mastering Databricks Sql Warehouse And Spark Sql

A Comprehensive Course on Databricks SQL Warehouse and Spark SQL for Data Engineers, Data Analysts, BI Developers, etc

What you'll learn
Getting Started with Databricks SQL for Data Analysis or Data Engineering
Overview of building reports and dashboards using Databricks SQL
Creating Databases and Tables using Databricks SQL or Spark SQL
Writing Basic Queries using Databricks SQL or Spark SQL
DML to load data into Databricks SQL or Spark SQL Tables
Advanced Operations such as Ranking and Aggregations using Databricks SQL or Spark SQL
Processing Semi-Structured Data using Databricks SQL or Spark SQL
Requirements
Basic SQL Skills and Data Analysis Skills
Computer with decent configuration and Internet
Description
Databricks SQL Warehouse is relatively new technology to build Data Lakehouse or Data Warehouse leveraging powerful Apache Spark Engine where the analytics can be built at scale. As part of this comprehensive course, you will learn all key skills required to master Databricks SQL Warehouse including Spark SQL as the SQL in Databricks SQL Warehouse is based on Spark SQL.This course also covers most of the curriculum relevant to clear the Databricks Certified Data Analyst Associate Exam offered by Databricks itself.Here are the high-level details related to this course. This is a beginner level course where you will be able to not only learn syntax and semantics of Databricks SQL or Spark SQL, you will also understand the concepts of the same.Setup Course Material and Environment for Databricks SQL WarehouseManaging Databases using Databricks SQL WarehouseManage Delta Tables in Databricks SQL WarehouseSetup Data Set for Databricks SQL Views and Copy CommandsDatabricks SQL or Spark SQL Queries to Process Values in JSON String ColumnsCopy Data into Delta Tables in Databricks SQL WarehouseInsert or Merge Spark SQL or Databricks SQL Query Results or View into Delta TablesMerge Spark SQL or Databricks SQL Query Results and Data from Delta Table with Delete into Delta TablesBasic SQL Queries using Spark SQL or Databricks SQLPerforming Aggregations using Group By and filtering using Having leveraging Spark SQL or Databricks SQLAggregations using Windowing or Analytical Functions including Cumulative Aggregations using Spark SQL or Databricks SQLRanking using Windowing or Analytical Functions using Spark SQL or Databricks SQLDealing with different file formats such as parquet, json, csv, etc using Spark SQL or Databricks SQLAll Important types of Joins such as Inner, left or right outer, full outer using Spark SQL or Databricks SQLVisualizations and Dashboards using Databricks SQL WarehouseWe have also provided quite a few exercises along with solutions with explanations through the course.Key Takeaways of Mastering Databricks SQL and Spark SQL using Databricks SQL WarehouseSetup Environment to learn Databricks SQL and Spark SQL using AzureSupport via Udemy Q&A backed by our expert teamData Sets and Material via GitHub Repository along with instructions to practice Databricks SQL or Spark SQLLife Time Access to High Quality Video Lectures to learn Databricks SQL and Spark SQL

Overview

Section 1: Introduction to Mastering Databricks SQL Warehouse and Spark SQL

Lecture 1 Introduction

Section 2: Setup Course Material and Environment for Databricks SQL

Lecture 2 Install Databricks CLI using Python 3 Pip Command

Lecture 3 Configure Databricks CLI using Token and Profile

Lecture 4 Setup Git Repository for Material and Data Sets related to Databricks SQL Course

Section 3: Getting Started with Databricks SQL

Lecture 5 Overview of Databricks SQL Platform - Introduction

Lecture 6 Run First Query using SQL Editor of Databricks SQL

Lecture 7 Overview of Dashboards using Databricks SQL

Lecture 8 Overview of Databricks SQL Data Explorer to review Metastore Database and Tables

Lecture 9 Use Databricks SQL Editor to develop scripts or queries

Lecture 10 Review Metadata of Tables using Databricks SQL Platform

Lecture 11 Overview of loading data into retail_db tables

Lecture 12 Configure Databricks CLI to push data into Databricks Platform

Lecture 13 Copy JSON Data into DBFS using Databricks CLI

Lecture 14 Analyze JSON Data using Spark APIs

Lecture 15 Analyze Delta Table Schemas using Spark APIs

Lecture 16 Load Data from Spark Data Frames into Delta Tables

Lecture 17 Run Adhoc Queries using Databricks SQL Editor to validate data

Lecture 18 Overview of External Tables using Databricks SQL

Lecture 19 Using COPY Command to Copy Data into Delta Tables

Lecture 20 Manage Databricks SQL Endpoints

Section 4: Managing Databases using Databricks SQL Warehouse

Lecture 21 Review Databases using Databricks SQL Data Explorer

Lecture 22 Create Database or Schema using Databricks SQL

Lecture 23 Using IF NOT EXISTS while Creating Databases using Databricks SQL

Lecture 24 Listing or Showing Databases and Getting Metadata of Databases using Databricks

Lecture 25 Understand Default Location of Databricks SQL Database or Schema

Lecture 26 Create Database or Schema using Location in Databricks SQL Warehouse

Lecture 27 Drop Databases in Databricks SQL Warehouse

Lecture 28 Alter Database in Databricks SQL Warehouse

Lecture 29 Comments on Databases in Databricks SQL Warehouse

Section 5: Manage Delta Tables in Databricks SQL Warehouse

Lecture 30 List Databases and Save Databricks SQL Script

Lecture 31 Create Table using Delta Format in Databricks SQL Warehouse

Lecture 32 Understand Location and Using Clause to specify File Format for Databricks

Lecture 33 Create External Table using Delta Format in Databricks SQL Warehouse

Lecture 34 Drop External Table and Delete Folder in Databricks SQL Warehouse

Lecture 35 Overview of DML or CRUD Operations using Databricks SQL

Lecture 36 Insert Records into Databricks SQL Warehouse table

Lecture 37 Insert Multiple Records into Databricks SQL Warehouse table

Lecture 38 Update Existing Records in Databricks SQL Warehouse table

Lecture 39 Update Existing Records in Databricks SQL Warehouse table based on Null Values

Lecture 40 Delete Existing Records in Databricks SQL Warehouse table

Lecture 41 Cleanup Users Tables from Databricks SQL Warehouse Database or Schema

Section 6: Setup Data Set for Databricks SQL Views and Copy Commands

Lecture 42 Getting Started with Databricks fs Commands using Databricks CLI

Lecture 43 Create Folder in DBFS using Databricks CLI Commands

Lecture 44 Copy Files from Local File System into DBFS using Databricks CLI Commands

Lecture 45 Overwrite Files while Copying into DBFS using Databricks CLI Command

Lecture 46 Understand Course Catalog Data in the files uploaded to DBFS

Lecture 47 Options to Analyze Data using Databricks SQL Queries

Lecture 48 Run Select Queries using DBFS Path in From Cluase

Lecture 49 Run Queries using Temporary Views in Databricks SQL

Lecture 50 Run Queries using External Tables in Databricks SQL

Section 7: Queries to Process Values in JSON String Columns

Lecture 51 Queries to Process Values in JSON String Columns

Lecture 52 Get Distinct and Count based on Key using Course Catalog Data

Lecture 53 Filter Data using Basic Databricks SQL Queries using Course Catalog Data

Lecture 54 Exploring Functions using Databricks SQL

Lecture 55 Understand Record Column Values in Course Catalog Table

Lecture 56 Processing JSON String Values using Databricks SQL Queries

Lecture 57 Process Instructors JSON Records using Databricks SQL Queries

Lecture 58 Create View for Instructors using Databricks SQL Queries

Section 8: Copy Data into Delta Tables in Databricks SQL Warehouse

Lecture 59 Create Delta Table for Course Catalog Data Set

Lecture 60 Get File Names along with Data using Databricks SQL Queries

Lecture 61 Overview of Databricks SQL COPY Command

Lecture 62 Copy Data from single file into Delta Tables using Files

Lecture 63 Copy Data from multiple files into Delta Tables using Files

Lecture 64 Copy Data from multiple files into Delta Tables using Pattern

Lecture 65 Create Course Catalog Table in Databricks SQL Warehouse with additional Column

Lecture 66 Copy Data from Files using Queries into Delta Tables

Lecture 67 Validate Course Catalog Table in Bronze Layer

Section 9: Insert or Merge Query Results or View into Delta Tables using Databricks SQL

Lecture 68 Introduction to Insert or Merge Query Results or View into Delta Tables using D

Lecture 69 Create Course Catalog and Instructors Tables using Databricks SQL

Lecture 70 Copy Data into Course Catalog Table from JSON Files using Databricks SQL

Lecture 71 Insert Query Results into Delta Table using Databricks SQL

Lecture 72 Exercise to Create Courses Table and Insert Data

Lecture 73 Copy Instructors Data into Course Catalog Table from new file

Lecture 74 Understand the Concept of Merge or Upsert in DML or CRUD Operations

Lecture 75 Develop Query to Get the latest Instructors Records from Course Catalog Table

Lecture 76 Overview of Merge Statement Syntax using Databricks SQL

Lecture 77 Merge Data into Instructors Table from Course Catalog using Databricks SQL

Lecture 78 Exercise to merge Courses Data from Course Catalog into Courses Table

Section 10: Merge Query Results and Data from Delta Table with Delete into Delta Tables u

Lecture 79 Introduction to Merge Query Results and Data from Delta Table with Delete in

Lecture 80 Overview of Merge with Delete Real Life Scenario

Lecture 81 Create Users Table using Delta format for Merge with Delete

Lecture 82 Merge First Set of Records into Users Table.cmproj

Lecture 83 Perform DML Operations on Users Table in Bronze Layer

Lecture 84 Merge Updated Data into Users Table in Silver Layer

Lecture 85 Scenarios for Merge into Databricks SQL Delta Tables

Lecture 86 Reset Users Table to Merge Data Incrementally into the target table

Lecture 87 Merge Into Databricks Table using Conditions on Updated Timestamp

Lecture 88 Perform Operations to Insert and Update records in Bronze Users Table

Lecture 89 Merge Incremental Data with Delete into Silver Users Table with Condition on

Lecture 90 Exercise to Develop Merge Statement with Condition avoiding Column Names

Section 11: Getting Started with Basic SQL Queries using Databricks SQL

Lecture 91 Introduction to Getting Started with Basic SQL Queries using Databricks SQL

Lecture 92 Exploring Databricks Data Sets

Lecture 93 Create Database and Temporary Table to Preview the Data

Lecture 94 Understand Characteristics of Online Retail Dataset

Lecture 95 Create External Table with Columns and Datatypes

Lecture 96 Get Count of Not Null Values of all Columns using Databricks SQL

Lecture 97 Overview of Stack or Unpivot or Transpose of Data

Lecture 98 Transpose Data using Stack in Databricks SQL

Lecture 99 Transpose Not Null Counts into Multiple Rows using Databricks SQL

Lecture 100 Overview of Unique or Distinct in Databricks SQL

Lecture 101 Overview of Distinct to get unique records using Databricks SQL

Lecture 102 Exercise - Get Distinct Count of Every Column

Lecture 103 Create View using Query for Unique Records

Lecture 104 Structure of SQL Query and Overview of Clauses

Lecture 105 Overview of Comments in Databricks SQL Queries

Data Analysts and BI Developers who want to understand Databricks SQL or Spark SQL Queries to analyze the data,Data Engineers who would like to understand Databricks SQL,QA Analysts or Engineers who would like to understand Databricks SQL for the validation of the data,Business Analysts to analyze the Data in the Data Lake using Databricks SQL or Spark SQL Queries,BI Developers who want to understand how to connect BI Tools to Databricks SQL Endpoint and develop required reports and dashboards