Hive Join & SubQuery Tutorial with Examples

Join queries

Join queries can be performed on two tables present in Hive. To understand join concepts clearly, we are creating two tables here:

• sample_joins (related to customer details)
• sample_joins1 (related to order details placed by employees)

Step 1) Creation of the table “sample_joins” with the column names Id, Name, Age, address and salary of the employees. The screenshot below shows the CREATE TABLE statement and its confirmation.

Step 2) Loading and displaying data. The next screenshot shows the load command followed by the contents of the table.

From the above screenshot:

1. Loading data into sample_joins from Customers.txt
2. Displaying sample_joins table contents

Step 3) Creation of the sample_joins1 table, then loading and displaying its data, as shown in the screenshot below.

From the above screenshot, we can observe the following:

1. Creation of table sample_joins1 with the columns Orderid, Date1, Id and Amount
2. Loading data into sample_joins1 from orders.txt
3. Displaying records present in sample_joins1

Moving forward, we will see the different types of joins that can be performed on the tables we have created. Before that, you have to consider the following points about joins.

Some points to observe in joins:

• Only equality joins are allowed in joins
• More than two tables can be joined in the same query
• LEFT, RIGHT and FULL OUTER joins exist in order to provide more control over the ON clause for which there is no match
• Joins are not commutative
• Joins are left-associative irrespective of whether they are LEFT or RIGHT joins

The equality restriction reflects Hive as it was for many years. From Hive 2.2.0 onwards, complex expressions in the ON clause are supported (HIVE-15211), so a non-equality condition is accepted on a current release. On older releases the condition has to be an equality test, with anything else moved into a WHERE clause.

Different type of joins

Joins are of 4 types. These are:

• Inner join
• Left outer join
• Right outer join
• Full outer join

Each type is demonstrated below against the same two tables, so the only thing that changes between the examples is which unmatched rows survive.

Inner Join

The records common to both tables will be retrieved by this inner join. The result in the screenshot below contains only the customers that have a matching order.

From the above screenshot, we can observe the following:

1. Here we are performing a join query using the JOIN keyword between the tables sample_joins and sample_joins1, with the matching condition (c.Id = o.Id).
2. The output displays the common records present in both tables, selected by checking the condition mentioned in the query.

Query:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

Left Outer Join

• HiveQL LEFT OUTER JOIN returns all the rows from the left table even though there are no matches in the right table
• If the ON clause matches zero records in the right table, the join still returns a record in the result with NULL in each column from the right table

The screenshot below shows that every customer appears, including those with no order.

From the above screenshot, we can observe the following:

1. Here we are performing a join query using the “LEFT OUTER JOIN” keyword between the tables sample_joins and sample_joins1, with the matching condition (c.Id = o.Id). For example, here we are using the employee id as a reference; it checks whether the id is common to the right table as well as the left table. It acts as the matching condition.
2. The output displays the records selected by the condition mentioned in the query. NULL values in the above output are columns with no values from the right table, that is sample_joins1.

Query:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Right Outer Join

• HiveQL RIGHT OUTER JOIN returns all the rows from the right table even though there are no matches in the left table
• If the ON clause matches zero records in the left table, the join still returns a record in the result with NULL in each column from the left table
• RIGHT joins always return records from the right table and matched records from the left table. If the left table has no value corresponding to the column, it will return NULL values in that place.

The screenshot below shows the mirror image of the previous result: every order appears, matched or not.

From the above screenshot, we can observe the following:

1. Here we are performing a join query using the “RIGHT OUTER JOIN” keyword between the tables sample_joins and sample_joins1, with the matching condition (c.Id = o.Id).
2. The output displays the records selected by checking the condition mentioned in the query.

Query:

  SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Full Outer Join

It combines records of both the tables sample_joins and sample_joins1 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 whose matching values are missing on either side, as the screenshot below shows.

From the above screenshot, we can observe the following:

1. Here we are performing a join query using the “FULL OUTER JOIN” keyword between the tables sample_joins and sample_joins1, with the matching condition (c.Id = o.Id).
2. The output displays all the records present in both tables, selected by checking the condition mentioned in the query. NULL values in the output here indicate the missing values from the columns of both tables.

Query:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Sub queries

Joins put tables side by side. A subquery does something different: it nests one query inside another so that the outer query can work from a result that has already been calculated.

A query present within a query is known as a subquery. The main query will depend on the values returned by the subquery.

Subqueries can be classified into two types:

• Subqueries in the FROM clause
• Subqueries in the WHERE clause

When to use:

• To get a particular value combined from two column values from different tables
• Dependency of one table’s values on other tables
• Comparative checking of one column’s values against 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);

Example:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

Here t1 and t2 are table names. The inner statement is the subquery performed on table t1. Here a and b are columns that are added in the subquery and assigned to col1. Col1 is the column value present in the main table. This column “col1” present in the subquery is equivalent to the main table query in column col1.

Embedding custom scripts

Where a subquery reshapes data with HiveQL alone, an embedded script hands rows to code written outside Hive.

Hive makes it possible to write user-specific scripts for client requirements. Users can write their own map and reduce scripts for those requirements. These are called embedded custom scripts. The coding logic is defined in the custom script, and we can use that script at ETL time.

When to choose embedded scripts:

• Where client-specific requirements mean developers have to write and deploy scripts in Hive
• Where Hive built-in functions are not going to work for specific domain requirements

For this, Hive uses the TRANSFORM clause to embed both map and reducer scripts.

In these embedded custom scripts, we have to observe the following points:

• Columns will be transformed to string and delimited by TAB before being given to the user script
• Standard output of the user script will be treated as TAB-separated string columns

Sample embedded script:

FROM (
	FROM pv_users
	MAP pv_users.userid, pv_users.date
	USING 'map_script'
	AS dt, uid
	CLUSTER BY dt) map_output

INSERT OVERWRITE TABLE pv_users_reduced
	REDUCE map_output.dt, map_output.uid
	USING 'reduce_script'
	AS date, count;

From the above script, we can observe the following. This is only a sample script for understanding.

• pv_users is the users table, which has fields such as userid and date as mentioned in map_script
• The reducer script is defined on the date and count of the pv_users table

FAQs