4.32 out of 5
951 reviews on Udemy

Hive to ADVANCE Hive (Real time usage) :Hadoop querying tool

In and Out of Apache Hive - From Basic Hive to Advance Hive (Real-Time concepts) + Use cases asked in Interviews
J Garg
5,073 students enrolled
Learn Full In and out of Apache HIVE (From Basic to Advance level).
Strength of this course is ADVANCE HIVE which consists of those Hive areas that are actually used in Real-time projects.
Querying and managing large datasets that reside in distributed storage.
Learn Questions and Use cases asked in Interviews.
The course will be updated frequently, every-time including new topics.
Data-sets and Hive queries are available in resources tab. This will save your typing efforts.

Apache Hive is a data processing tool on Hadoop. It is a querying tool for HDFS and the syntax of it’s queries is almost similar to our old SQL. Hive is an open source-software that lets programmers analyze large data sets on Hadoop.

Benefits of this course:

“Basic Hive is not sufficient if you want to work on Real-time projects.”

Make yourself prepared to work on Real time Big data and Hive projects by learning Advance Hive from this course. Enroll into this course and get end to end knowledge of Basic + ADVANCE Hive + Interview asked Use cases. This course is very rare of its kind and includes even a very thin detail of Hive.

 In this course you will get to understand a step by step learning of very Basic Hive to Advance Hive (which is actually used in Real-time projects) like:

  • Variables in Hive 

  • Table properties of Hive

  • Map and Bucketed Joins

  • Advance functions in Hive

  • Compression techniques in Hive 

  • Configuration settings of Hive

  • Working with Multiple tables in Hive

  • Loading Unstructured data in Hive

And many more……

This course is a full package explaining even rarely used commands and concepts in Hive. After completing this course you won’t find any topic left in Hive. This course is made keeping in mind the Real Implementation of Hive in Live Projects.

Apart from this I have included 1 more section which is Use cases asked in Interviews. Usually students can answer the direct questions asked by Interviewers but got stuck on Use cases . For that I have explained the frequently asked Use cases with their proper practical working in Hive. 

Additionally ,You can download the Step Step Installation Guide (pdf) to Install Hadoop and Apache Hive

Introduction (Theory)

Introduction to Hive

This video gives a brief description of Hive.

Motivation of Hive

This videos explains the reason of Why Hive was developed.

Sql vs Hive

Since syntax of Hive is similar to that of SQL .This video will explain the similarities and difference between SQL and Hive

Trailer- Working of Hive

This videos explains the general working of Hive. Hive can process and stores structural data only. It does this by linking the metadata of it's table to the file in HDFS.

Important Note* - Hive is not a database . After loading data into hive table, Our HDFS file will not move to Hive rather after loading now Hive will sees that file in a tabular way

Architecture of Hive

Architecture of Hive-

Hive has these components in its architecture:

  • UI
  • Driver
  • Compiler
  • Metastore
  • Execution engine

Hive Basic Commands


This PDF contains a step by step procedure to install Hadoop and Hive along with other resources like Java,Virtual Box and Ubuntu

Create databases

Hive is not a database but to store the metadata of its tables Hive uses Databases .By default Hive provides Derby database but in real time projects we use strong databases like MYSQL.

This videos explains How to create database in various ways using different options.

Table creation and loading data into it |Part 1

Since Hive stores the data into structural format we create Tables. In this lecture we will create tables in Hive .Tables in Hive can be created in many ways with a lot of options.

After table creation we have to load the data in those Hive tables. Note that loading does not mean transferring data into Hive because Hive is not a database,Rather it will just link the metadata of Hive table to corresponding HDFS file. 

Table creation and loading data into it |Part 2

Part 2 

Since Hive stores the data into structural format we create Tables. In this lecture we will create tables in Hive .Tables in Hive can be created in many ways with a lot of options.

After table creation we have to load the data in those Hive tables. Note that loading does not mean transferring data into Hive because Hive is not a database,Rather it will just link the metadata of Hive table to corresponding HDFS file. 

Internal vs External table- Explained

There are two types of tables we can create in Hive i.e. Internal or Managed and External tables.

The main difference between these two tables appears while we drop a table. In case of dropping of Internal tables both the schema as well as data is lost since Hive is responsible for both schema and data but in case of dropping External table only the schema or metadata of table is lost, the data is not lost and will be present in the same HDFS location. The data can still be accessed by other applications.

Create Tables
Insert statement

Insert statement is used to load the data from one Hive table to another Hive table.

Multi insert statement (Advance)

Multi insert statement is used to load data from a 1 table into multiple tables.

Alter Table Schema

Once created a Hive table it's schema can be changed according to new requirements. This lecture explains how a Hive table's schema can be changed in various ways.

Sorting -- sort by, order by, distribute by, cluster by

Order by - In Hive, ORDER BY guarantees total ordering of data, and for that it has to be passed on to a single reducer.

Sort by-  Sort by does not ensure full ordering of data rather it ensures ordering of data within a reducer.

Distribute by-  Distribute by ensures that all rows with the same Distribute By columns will go to the same reducer.

Cluster by-  Cluster By is a shortcut for both Distribute By and Sort By. First Distribute by ensures all same column values in single reducer and then sorts those rows inside the reducer.

Lecture also explains 

difference between order by and sort by

order by with limit clause

behaviour order by command in strict and non strict mode


Date and Mathematical functions

Hive provides us with these Date and Mathematical functions.

String functions

This video explains various string functions in Hive.

Split(), Substr(), instr() functions
  • Split() function in Hive
  • Substr() function in Hive
  • instr() function in Hive

These string functions are widely used in Hive.

Conditional statements

Following are the list of conditional statements used in Hive.

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.

isnull( a )

Returns true if a is NULL and false otherwise.

isnotnull ( a )

Returns true if a is not NULL and false otherwise.

nvl(T value, T default_value)

Returns default value if value is null else returns value (as of HIve 0.11).

COALESCE(T v1, T v2, ...)

Returns the first v that is not NULL, or NULL if all v's are NULL.


When a = b, returns c; when a = d, returns e; else returns f.


When a = true, returns b; when c = true, returns d; else returns e.

Explode and Lateral view ( Advance functions)

These are concepts of Advance Hive. Explode function and lateral view

explode() takes in an array as an input and outputs the elements of the array as separate rows. 

Lateral view is used to select other table columns with exploded columns.

Rlike function (Advance)

This is an advance function in Hivewhere if any substring of A matches with B then it evaluates to true.

Example:  ‘Super’ RLIKE ‘Su’ –> True

Rank(), Dense_rank(), Row_number() (Advance)

Advance Hive functions. They come under hive analytical functions.

In Rank() function equal ranks are given same rank value 

In dense_rank() function equal ranks are given same rank value but there will be no gaps as are in rank() function

row_number() function will not give equal rank value for same ranks .

The lecture also contains difference between rank() and dense_rank().

Practice Mathematical Functions

Partitioning and Bucketing

What is Partitioning?
Static partitioning

Partitioning is a data organizing technique in Hive. It is a way of organizing tables into smaller partitions based on values of columns in table.

Partitioning can be done in two ways.

Static Partitioning: In static partitioning the partition column is hardcoded and we have to manually mention the partition name while loading data into it.

Dynamic Partitioning: In dynamic partitioning Hive automatically decides the partitions based on the values of partitioned column .

This lecture covers:

  • Static partition in Hive table
  • How to load data into partitioned table in Hive
Dynamic partitioning

Dynamic Partitioning in Hive: In dynamic partitioning Hive automatically decides the partitions based on the values of partitioned column.

This lecture covers: 

  • What is dynamic partition
  • Load data into dynamic partitioned table.
  • Difference between static and dynamic partition.
  • Set dynamic partition property
Alter Partitioned Table and MSCK Repair command (Advance)

This is an Advance Hive concept usually asked in Interviews.

Partitioned tables schema can also be altered like changing partition location, adding new partition, drop a partition.

MSCK repair table command in Hive is used to update the metadata of table in case of manually adding a partition in HDFS location

What is Bucketing?

Bucketing is another data organizing technique in Hive. While partitioning is organizing table into a number of directories, bucketing is organizing table in files.

This video explains:

  • Bucketing in Hive
  • Difference between Partitioning and Bucketing in Hive.
  • How to do bucketing.
  • Properties to be set to do bucketing.
  • Where to do buckering
Create Bucketed Table
Tablesampling (Advance)

Tablesampling is Advance Hive concept and a provision of bucketing.

Video explains:

  • What is tablesampling.
  • Difference between tablesampling and limit operator in hive

No_drop, Offline command (Advance)

Advance Hive concept:

With No_drop and offline command we can prevent a Hive table or partition from being queried or dropped.


Joins in Hive

Inner Joins on 2 Tables

Joins in Hive behave as same as in SQL i.e joining 2 tables based on a joining condition. 

This video explains how to join 2 tables in Hive.

These are types of joins supported by Hive : Inner join, Left outer join, Right outer join and Full outer join

Outer Joins on 2 Tables

Joins in Hive behave as same as in SQL i.e joining 2 tables based on a joining condition. 

This video explains how to join 2 tables in Hive.

These are types of joins supported by Hive : Inner join, Left outer join, Right outer join and Full outer join

Join 3 Tables in Hive

Advance Hive concept

We can also join 3 tables in a single query in Hive.

This video contains:

  • How to join 3 tables in Hive

  • Memory organisation while joining tables.

Memory Management & Optimization of Joins
Map Joins (Advance)

Map join is a Advance Hive join .

The logic behind Map join is that the join operation is executed totally on Map side .No reducer is used in Map joins.

These video contains:

  • What is Map join in Hive.
  • How Map join executes
  • When to use Map join in Hive

Views in Hive

What are views?
Creating Views in different ways

Views in Hive also serve the same purpose as in SQL.A View on a table can be thought of as an image of that table. 

This video contains

What are views in Hive

  • How to create views 
  • Different ways to create views
  • Dropping views in Hive
Advantages of Views

Advantages of Views in Hive.

Where to use Views in Hive

Indexing (Advance)

Creation of Indexes (Compact and Bitmap)

Indexing in Hive is a optimization technique to reduce the throughput time of query. There is a separate index table created in which indexes of all indexed columns are stored.

In this lecture we will learn:

  • What is Indexing in Hive
  • How to create indexing in Hive
  • Types of Indexing in Hive
  • With Deferred rebuild command in Indexing
  • Advantages of Indexing
  • Where and When to use Indexing in Hive
  • When not to use Indexing in Hive
Multiple Indexes on same table

As we know there are 2 types of indexes in Hive. The question is Can we create both Indexes on same table at same time. Answer is yes. We can create multiple Indexes on same table. More iis explained in the videos

When and When not to use Indexing

Indexing should be use blindly everywhere in Hive since Indexing can be disadvantageous also in some cases. This video explains When and When not to use Indexing in Hive.

UDF's (User defined functions) Advance

What is UDF

UDF (User defined functions) are backbone of any Real time Hive project because in Live projects requirements are complicated and can not be met with Built-in functions. WIth UDF we can write our own functions according to the requirement and then use those functions in Hive queries.

UDF Implementation - Practical

This video explains how to create a UDF function in Java and how to use it in Hive.

Table Properties (Advance)

Skipping Header and Footer records while loading in table

Advance Hive table property:

This property is used in Hive tables while loading data into them. We can skip some rows from file to be loaded into our Hive table


Hadoop Mapreduce course

Immutable Table property

Immutable property is also used in Real time projects of Hive. This lecture shows behaviour of Insert statement with into and overwrite options when immutable property is set to true.

Purge property + Difference between Drop and Truncate

Purge property is used In Hive when we don't our data doing to trash. When the purge property is set to true the data will be completely gone and cannot be recovered.

This Advance Hive lecture contains:

  • Purge property in Hive
  • How to drop a table in Hive
  • How to truncate table in Hive
  • Difference between dropping a table and truncating a table in Hive
Null Format property

Advance Hive table property:

By default nothing is null between delimiters in a file for Hive. This property tells Hive what value should be considered as NULL .

ACID/Transactional features of Hive ( Advance)

ACID properties in Hive  - Advance Hive concept

ORC Table properties

Advance Hive table property

There are different types of file formats supported by Hive. Hive can store the data in RC ,Parquet ,Textfile formats. Out of these 1 is ORC file format. This video explains the table properties of that Hive table which stores data in ORC format.

Configurations & Settings in Hive (Advance)

Part 1

This lecture includes the first set of configurations and settings that can made in Hive

Part 2

This lecture includes the second set of configurations and settings that can made in Hive

Merge files in Hive

Hive creates its own small files during and after  query execution. By setting some Hive properties we can tell Hive to automatically Merge files. It is an optimization technique and concept of Advance Hive

Parallelism Property

Parallelism in Hive means executing independent portions of a query parallely . This is done to reduce the execution time.

**Note: Parallelism should be used wisely, it may lead to a deadlock situation.

This video contains:

  • Parallel processing in HIve.
  • hive.exec.parallel property
  • Hive parallel join

Variables in Hive (Advance)

Executing Hive queries from Bash shell

;Advance Hive concept

Same like we can execute Unix and HDFS commands in Hive shell, Hive queries can also be executed in Bash shell. We use hive -e option to run individual hive commands and hive -f option to run Hive scripts from Bash shell.

Run Unix & Hadoop commands from Hive Shell

Advance Hive:

We can also run Unix and Hadoop commands from our Hive shell. This is a good approach to run Unix and Hadoop commands from Hive shell rather an opening a new JVM instance.

Variables in Hive (hiveconf & hivevar)

Advance Hive:

Hive variables are widely used in Real time Live Hive Projects. Variables in Hive behave as same in any other programming language. We can declare variables in two ways hiveconf and hivevar

Difference between hiveconf & hivevar
Using variables in bash shell

Advance Hive concept:

We can also pass value to Hive variables from Bash shell. These Hive variables can be passes to a query or to a Hive script.

Substituting value of a Variable

Advance Hive concept

This Hive lecture explains how to a variable gets it's value from other variable and to do this which Hive property should be set to true.


Different Types of Files in Hadoop

Text, Sequence, Avro Files
RC, ORC, Parquet Files
Performance Test results of Various Files
Which File Format to choose

Miscellaneous (Advance)

Using Custom Input Format in Hive

Custom input format in hive - Advance Hive

Modes of Hive

Hive works in these following 3 modes:

  • Embedded mode
  • Local mode
  • Remote mode
Compression techniques in Hive

This Advance Hive lecture explains:

  • What is compression in Hive
  • What are its advantages
  • What types of files can be compressed in Hive
  • Different types of compression formats in Hive
  • How to compress files in Hive
Hiverc file (Creation and Uses)

Hiverc is a configuration file in Hive. It runs when we launch our hive shell. Hiverc file is used to

  • Add jar files
  • Set configurations
  • Set propertie
Archiving files in Hive

Archiving in Hive is a rarely known feature of Hive and is also rarely used. By archiving we bundle less frequently used files in Hive for saving memory.

TEZ engine in Hive

What is Tez engine and its comparison with MR

Use Cases asked in Interviews

How to Load XML data in Hive tables-- Part1

Hive Use case 1:

This Use Case is about How to load Semi-structured data in Hive. Usually we load structured data in Hive tables but we can also load semi-structured data like XML in our Hive tables.

This videos explains:

  • What is XML file
  • Structure of XML file
  • Creation of Hive table for XML data
  • How to load XML file into Hive tables
How to Load XML data in Hive tables-- Part2

Hive Use case 1

Explained further.

What is Slowly changing Dimension (SCD)

Hive Use case 2:

This Hive Use case is usually asked in Interviews . This explains How to capture changed data in Hive or How we deal with Hive tables that are being updated frequently.

Implement SCD 1 in Hive--part1

Hive Use case 2

This Hive Use case is usually asked in Interviews . This explains How to capture changed data in Hive or How we deal with Hive tables that are being updated frequently.

Implement SCD 1 in Hive--part2
Word count in Hive

Hive Use case 3

In purely Hive based Interviews this Use case is very common. As in Big data training we all learn Word count by using MapReduce Program. The same Word count can be achieved by Hive also.

This Lecture will explain How to implement Word count in Hive .

Multiple Tables on a single file

Hive Use case 4:

This Use case explains How we deal with that situation when there are multiple Hive tables built on same file. We can create any number of Hive tables on 1 file. The Question is how those different Hive tables having different number of columns behaves since the file's data is same for every table.

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.3 out of 5
951 Ratings

Detailed Rating

Stars 5
Stars 4
Stars 3
Stars 2
Stars 1
30-Day Money-Back Guarantee


7 hours on-demand video
Full lifetime access
Access on mobile and TV
Certificate of Completion