Sqoop, Hive and Impala for Data Analysts (Formerly CCA 159)

About Sqoop, Hive and Impala for Data Analysts (Formerly CCA 159)

As part of

Sqoop, Hive, and Impala for Data Analysts (Formerly CCA 159)

, you will learn key skills such as Sqoop, Hive, and Impala. This comprehensive course covers all aspects of the certification with real-world examples and data sets.

Overview of Big Data ecosystem

Overview Of Distributions and Management Tools

Properties and Properties Files - General Guidelines

Hadoop Distributed File System

YARN and Map Reduce2

Submitting Map ReduceJob

Determining Number of Mappers and Reducers

Understanding YARN and Map Reduce Configuration Properties

Review and Override Job Properties

Reviewing Map Reduce Job Logs

Map Reduce Job Counters

Overview of Hive

Databases and Query Engines

Overview of Data Ingestion in Big Data

Data Processing using Spark

HDFS Commands to manage files

Introduction to HDFS for Certification Exams

Overview of HDFS and PropertiesFiles

Overview of Hadoop CLI

Listing Files in HDFS

User Spaces or Home Directories in HDFS

Creating Directories in HDFS

Copying Files and Directories into HDFS

File and Directory Permissions Overview

Getting Files and Directories from HDFS

Previewing Text Files in HDFS

Copying or Moving Files and Directories within HDFS

Understanding Size of File System and Files

Overview of Block Size and ReplicationFactor

Getting File Metadata using hdfs fsck

Resources and Exercises

Getting Started with Hive

Overview of Hive Language Manual

Launching and using Hive CLI

Overview of Hive Properties

Hive CLI History and hiverc

Running HDFS Commands in Hive CLI

Understanding Warehouse Directory

Creating and Using Hive Databases

Creating and Describing Hive Tables

Retrieve Matadata of Tables using DESCRIBE

Role of Hive Metastore Database

Overview of beeline

Running Hive Commands and Queries using beeline

Creating Tables in Hive using Hive QL

Creating Tables in Hive - orders

Overview of Basic Data Types in Hive

Adding Comments to Columns and Tables

Loading Data into Hive Tables from Local File System

Loading Data into Hive Tables from HDFS

Loading Data - Overwrite vs Append

Creating External tables in Hive

Specifying Location for Hive Tables

Difference between Managed Table and External Table

Default Delimiters in Hive Tables using Text File

Overview of File Formats in Hive

Differences between Hive and RDBMS

Truncate and Drop tables in Hive

Resources and Exercises

Loading/Inserting data into Hive tables using Hive QL

Introduction to Partitioning and Bucketing

Creating Tables using Orc Format - order_items

Inserting Data into Tables using Stage Tables

Load vs. Insert in Hive

Creating Partitioned Tables in Hive

Adding Partitions to Tables in Hive

Loading into Partitions in Hive Tables

Inserting Data Into Partitions in Hive Tables

Insert Using Dynamic Partition Mode

Creating Bucketed Tables in Hive

Inserting Data into Bucketed Tables

Bucketing with Sorting

Overview of ACID Transactions

Create Tables for Transactions

Inserting Individual Records into Hive Tables

Update and Delete Data in Hive Tables

Overview of functions in Hive

Overview of Functions

Validating Functions

String Manipulation - Case Conversion and Length

String Manipulation - substr and split

String Manipulation - Trimming and Padding Functions

String Manipulation - Reverse and Concatenating Multiple Strings

Date Manipulation - Current Date and Timestamp

Date Manipulation - Date Arithmetic

Date Manipulation - trunc

Date Manipulation - Using date format

Date Manipulation - Extract Functions

Date Manipulation - Dealing with Unix Timestamp

Overview of Numeric Functions

Data Type Conversion Using Cast

Handling Null Values

Query Example - Get Word Count

Writing Basic Queries in Hive

Overview of SQL or Hive QL

Execution Life Cycle of Hive Query

Reviewing Logs of Hive Queries

Projecting Data using Select and Overview of From

Derive Conditional Values using CASE and WHEN

Projecting Distinct Values

Filtering Data using Where Clause

Boolean Operations in Where Clause

Boolean OR vs IN Operator

Filtering Data using LIKE Operator

Performing Basic Aggregations using Aggregate Functions

Performing Aggregations using GROUP BY

Filtering Aggregated Data Using HAVING

Global Sorting using ORDER BY

Overview of DISTRIBUTE BY

Sorting Data within Groups using SORT BY

Using CLUSTERED BY

Joining Data Sets and Set Operations in Hive

Overview of Nested Sub Queries

Nested Sub Queries - Using IN Operator

Nested Sub Queries - Using EXISTS Operator

Overview of Joins in Hive

Performing Inner Joins using Hive

Performing Outer Joins using Hive

Performing Full Outer Joins using Hive

Map Side Join and Reduce Side Join in Hive

Joining in Hive using Legacy Syntax

Cross Joins in Hive

Overview of Set Operations in Hive

Perform Set Union between two Hive Query Results

Set Operations - Intersect and Minus Not Supported

Windowing or Analytics Functions in Hive

Prepare HR Database in Hive with Employees Table

Overview of Analytics or Windowing Functions in Hive

Performing Aggregations using Hive Queries

Create Tables to Get Daily Revenue using CTAS in Hive

Getting Lead and Lag using Windowing Functions in Hive

Getting First and Last Values using Windowing Functions in Hive

Applying Rank using Windowing Functions in Hive

Applying Dense Rank using Windowing Functions in Hive

Applying Row Number using Windowing Functions in Hive

Difference Between rank, dense_rank, and row_number in Hive

Understanding the order of execution of Hive Queries

Overview of Nested Sub Queries in Hive

Filtering Data on Top of Window Functions in Hive

Getting Top 5 Products by Revenue for Each Day using Windowing Functions in Hive - Recap

Running Queries using Impala

Introduction to Impala

Role of Impala Daemons

Impala State Store and Catalog Server

Overview of Impala Shell

Relationship between Hive and Impala

Overview of Creating Databases and Tables using Impala

Loading and Inserting Data into Tables using Impala

Running Queries using Impala Shell

Reviewing Logs of Impala Queries

Synching Hive and Impala - Using Invalidate Metadata

Running Scripts using Impala Shell

Assignment - Using NYSE Data

Assignment - Solution

Getting Started with Sqoop

Introduction to Sqoop

Validate Source Database - MySQL

Review JDBC Jar to Connect to MySQL

Getting Help using Sqoop CLI

Overview of Sqoop User Guide

Validate Sqoop and MySQL Integration using Sqoop List Databases

Listing Tables in Database using Sqoop

Run Queries in MySQL using Sqoop Eval

Understanding Logs in Sqoop

Redirecting Sqoop Job Logs into Log Files

Importing data from MySQL to HDFS using Sqoop Import

Overview of Sqoop Import Command

Import Orders using target-dir

Import Order Items using warehouse-dir

Managing HDFS Directories

Sqoop Import Execution Flow

Reviewing Logs of Sqoop Import

Sqoop Import Specifying Number of Mappers

Review the Output Files generated by Sqoop Import

Sqoop Import Supported File Formats

Validating avro files using Avro Tools

Sqoop Import Using Compression

Apache Sqoop - Importing Data into HDFS - Customizing

Introduction to customizing Sqoop Import

Sqoop Import by Specifying Columns

Sqoop import Using Boundary Query

Sqoop import while filtering Unnecessary Data

Sqoop Import Using Split By to distribute import using non default column

Getting Query Results using Sqoop eval

Dealing with tables with Composite Keys while using Sqoop Import

Dealing with tables with Non Numeric Key Fields while using Sqoop Import

Dealing with tables with No Key Fields while using Sqoop Import

Using autoreset-to-one-mapper to use only one mapper while importing data using Sqoop from tables with no key fields

Default Delimiters used by Sqoop Import for Text File Format

Specifying Delimiters for Sqoop Import using Text File Format

Dealing with Null Values using Sqoop Import

Import Mulitple Tables from source database using Sqoop Import

Importing data from MySQL to Hive Tables using Sqoop Import

Quick Overview of Hive

Create Hive Database for Sqoop Import

Create Empty Hive Table for Sqoop Import

Import Data into Hive Table from source database table using Sqoop Import

Managing Hive Tables while importing data using Sqoop Import using Overwrite

Managing Hive Tables while importing data using Sqoop Import - Errors Out If Table Already Exists

Understanding Execution Flow of Sqoop Import into Hive tables

Review Files generated by Sqoop Import in Hive Tables

Sqoop Delimiters vs Hive Delimiters

Different File Formats supported by Sqoop Import while importing into Hive Tables

Sqoop Import all Tables into Hive from source database

Exporting Data from HDFS/Hive to MySQL using Sqoop Export

Introduction to Sqoop Export

Prepare Data for Sqoop Export

Create Table in MySQL for Sqoop Export

Perform Simple Sqoop Export from HDFS to MySQL table

Understanding Execution Flow of Sqoop Export

Specifying Number of Mappers for Sqoop Export

Troubleshooting the Issues related to Sqoop Export

Merging or Upserting Data using Sqoop Export - Overview

Quick Overview of MySQL - Upsert using Sqoop Export

Update Data using Update Key using Sqoop Export

Merging Data using allowInsert in Sqoop Export

Specifying Columns using Sqoop Export

Specifying Delimiters using Sqoop Export

Using Stage Table for Sqoop Export

Submitting Sqoop Jobs and Incremental Sqoop Imports

Introduction to Sqoop Jobs

Adding Password File for Sqoop Jobs

Creating Sqoop Job

Run Sqoop Job

Overview of Incremental Loads using Sqoop

Incremental Sqoop Import - Using Where

Incremental Sqoop Import - Using Append Mode

Incremental Sqoop Import - Create Table

Incremental Sqoop Import - Create Sqoop Job

Incremental Sqoop Import - Execute Job

Incremental Sqoop Import - Add Additional Data

Incremental Sqoop Import - Rerun Job

Incremental Sqoop Import - Using Last Modified

Here are the objectives for this course.

Provide Structure to the Data Use Data Definition Language (DDL) statements to create or alter structures in the metastore for use by Hive and Impala.

Create tables using a variety of data types, delimiters, and file formats

Create new tables using existing tables to define the schema

Improve query performance by creating partitioned tables in the metastore

Alter tables to modify the existing schema

Create views in order to simplify queries Data Analysis Use Query Language (QL) statements in Hive and Impala to analyze data on the cluster.

Prepare reports using SELECT commands including unions and subqueries

Calculate aggregate statistics, such as sums and averages, during a query

Create queries against multiple data sources by using join commands

Transform the output format of queries by using built-in functions

Perform queries across a group of rows using windowing functions Exercises will be provided to have enough practice to get better at Sqoop as well as writing queries using Hive and Impala.

All the demos are given on our state-of-the-art Big Data cluster. If you do not have multi-node cluster, you can sign up for our labs and practice on our multi-node cluster. You will be able to practice Sqoop and Hive on the cluster.