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

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

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.

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. 

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. 

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.

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

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

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.

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

Hive provides us with these Date and Mathematical functions.

This video explains various string functions in Hive.

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

These string functions are widely used in Hive.

Following are the list of conditional statements used in Hive.

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.

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

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().

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 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

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

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

Tablesampling is Advance Hive concept and a provision of bucketing.

Video explains:

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

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 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

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

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.

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 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 in Hive.

Where to use Views in Hive

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

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

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 (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.

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

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

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 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

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 properties in Hive  - Advance Hive concept

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.