MapReduce Scripts in Hive / Hive Scripts

MapReduce Scripts in Hive / Hive Scripts :

 

Similar to any other scripting language, Hive scripts are used to execute a set of Hive commands collectively.

Hive scripting helps us to reduce the time and effort invested in writing and executing the individual commands manually.

Hive scripting is supported in Hive 0.10.0 or higher versions of Hive.

Joins and SubQueries :

JOINS :

Join queries can perform on two tables present in Hive.

Joins are of 4 types, these are :

• Inner join: The Records common to both tables will be retrieved by this Inner Join.
• Left outer Join: Returns all the rows from the left table even though there are no matches in the right table.
• Right Outer Join:  Returns all the rows from the Right table even though there are no matches in the left table.
• Full Outer Join: It combines records of both the tables based on the JOIN Condition given in the query. It returns all the records from both tables and fills in NULL Values for the columns missing values matched on either side.

 

SUBQUERIES :

A Query present within a Query is known as a subquery.

The main query will depend on the values returned by the subqueries.

Subqueries can be classified into two types :

• Subqueries in FROM clause
• Subqueries in WHERE clause

When to use :

• To get a particular value combined from two column values from different tables.
• Dependency of one table values on other tables.
• Comparative checking of one column values from other tables.

Syntax :

              

Subquery in FROM clause               SELECT <column names 1, 2...n>From (SubQuery) <TableName_Main >               Subquery in WHERE clause               SELECT <column names 1, 2...n> From<TableName_Main>WHERE col1 IN (SubQuery);