13HiveProgramming

Hive Programming

Outline

Data Selection and Scope
Data Manipulation
Data Aggregation and Sampling

A Hive SELECT statement is used to retrieve data from a table. It projects, or returns, rows that meet specific criteria. This statement is often used with several other clauses to refine the query and control the output.

Key Keywords and Clauses

FROM: This clause specifies the table from which to retrieve data. For example, FROM customer indicates you are querying the customer table.

WHERE: This clause filters the data, returning only the rows that satisfy the specified condition. In the example, WHERE C_ACCTBAL > 0 filters for customers whose account balance is greater than zero.

DISTINCT: This keyword is used to return only unique values, eliminating duplicate rows from the result set.

LIMIT: This clause restricts the number of rows returned by the query. LIMIT 2 would only show the first two rows that meet the conditions.

In the provided example query, SELECT C_NAME, C_PHONE FROM customer WHERE C_ACCTBAL > 0 LIMIT 2;, the statement is perform

ing the following actions:

Selecting the C_NAME and C_PHONE columns.

From the customer table.

Where the C_ACCTBAL (customer account balance) is greater than 0.

Limiting the output to the first two results that meet all the previous criteria.

Data Selection and Scope

To query data Hive provides SELECT statement
Typically SELECT statement projects the rows satisfying the query conditions specified in the WHERE clause and returns the result set
SELECT statement is usually used with FROM, DISTINCT, WHERE, and LIMIT keywords

Subqueries and Common Table Expressions (CTEs)

Hive allows for complex queries by nesting one SELECT statement within another, which is known as a subquery or nested query. A more readable and often more efficient way to handle these complex queries is by using a Common Table Expression (CTE).

A CTE is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It's defined using the WITH clause. This makes complex queries easier to read and debug by breaking them down into logical, named parts. You must provide an alias (a temporary name) for the CTE, which is then used in the main query.

Understanding a sample query (see slide)

In this query, the WITH clause defines a CTE named cord.

WITH cord AS (...): This part creates the temporary result set. The query inside the parentheses joins the customer and orders tables on their common key, c_custkey.

SELECT * FROM ...: The inner query selects all columns from the joined result. This combined data is stored in the temporary cord table.

SELECT c_name, c_phone, o_orderkey, o_orderstatus FROM cord;: This is the main query. It operates on the temporary cord table created by the CTE, selecting specific columns to display as the final result.

By using the CTE, the complex join operation is isolated and named, making the final SELECT statement much cleaner and easier to understand.

Data Selection and Scope

Multiple SELECT statements can be combined into complex queries using nested queries or subqueries
Subqueries can use Common Table Expressions (CTE) in the format of WITH clause

When using subqueries, an alias should be given for the subquery

	      
WITH cord AS ( SELECT *
               FROM customer JOIN orders
                             ON c_custkey = o_custkey)
SELECT c_name, c_phone, o_orderkey, o_orderstatus
FROM cord;

In Hive, you can create complex queries by nesting one SELECT statement inside another. This is known as a nested query or subquery. A nested query can be used anywhere a table is expected, and the result of the subquery is treated as a temporary table. This allows you to perform an operation on data that is itself the result of another query.

How a Nested Query Works

The provided example shows a subquery in the FROM clause:

Inner Query: The subquery (SELECT * FROM customer JOIN orders ON c_custkey = o_custkey) is executed first. This part joins the customer and orders tables on their common key, c_custkey.

Aliasing: The result of this inner query is a temporary dataset that's given the alias cord. Hive treats this aliased dataset just like a regular table for the purpose of the outer query.

Outer Query: The outer query SELECT c_name, c_phone, o_orderkey, o_orderstatus FROM cord then selects specific columns from the temporary cord table.

This technique is useful when you need to perform multiple steps to get to the final result, such as filtering, joining, or aggregating data before performing the final projection.

Data Selection and Scope

Multiple SELECT statements can be combined into complex queries using nested queries or subqueries

Nested queries can use SELECT statement wherever a table is expected or a scalar value is expected

	      
SELECT c_name, c_phone, o_orderkey, o_orderstatus
FROM ( SELECT *
       FROM customer JOIN orders
                     ON c_custkey = o_custkey) cord

Hive translates a multi-table JOIN into a MapReduce job to process data on HDFS. When performing an inner join with multiple tables, Hive's default strategy is to load all but the last table into memory on the reducer side. The final table is then streamed through the reducers.

The Role of STREAMTABLE

To optimize this process, it's recommended to place the largest table last in the JOIN sequence. This is because streaming the largest table minimizes the amount of data that needs to be buffered in memory, preventing potential out-of-memory errors and improving performance.

The /*+ STREAMTABLE(table_name) */ hint can be used to explicitly tell Hive which table to stream. This overrides Hive's default behavior, allowing you to manually optimize the join.

In the example (see slide)

The hint /*+ STREAMTABLE(lineitem) */ instructs Hive to stream the lineitem table through the reducers.

This is an optimization technique because lineitem is typically the largest table in the TPC-H benchmark schema, so streaming it is more efficient than trying to buffer it in memory.

The customer and orders tables are buffered in the reducers, and then the lineitem table is processed one record at a time against this buffered data. This reduces the memory footprint and speeds up the join operation.

Data Selection and Scope

When inner join is performed between multiple tables the MapReduce jobs are created to process data in HDFS

It is recommended to put the big table right at the end for better because the last table in the sequence is streamed through the reducers where the others are buffered in the reducer by default

	      
SELECT /*+ STREAMTABLE(lineitem) */ c_name, o_orderkey, l_linenumber
FROM customer JOIN orders
              ON c_custkey = o_custkey
              JOIN lineitem
              ON l_orderkey = o_orderkey;

An outer join (left, right, and full) in Hive preserves its standard SQL semantics. A map join, on the other hand, is a performance optimization where the join is completed in the map phase of a MapReduce job, skipping the reduce phase entirely. This is achieved by loading a small table into memory and broadcasting it to all mappers.

Outer and Cross Joins

Hive's outer joins behave just like they do in standard SQL. They're used to return rows from one or both tables even when no matching rows are found in the other.

LEFT OUTER JOIN: Returns all rows from the left table and the matched rows from the right table. If there's no match, the columns from the right table will contain NULL.

RIGHT OUTER JOIN: The opposite of a left join. It returns all rows from the right table and the matched rows from the left table.

FULL OUTER JOIN: Returns all rows from both tables, combining them where a join condition is met. Rows that don't have a match in the other table will have NULL values for that table's columns.

A CROSS JOIN in Hive creates a Cartesian product of two tables. This means every row from the first table is combined with every single row from the second table. This join doesn't require a join condition and can produce a very large result set, so it should be used with caution.

Map Joins (Map-Side Joins)

A map join is a highly efficient join optimization in Hive. It's used when one of the tables in the join is significantly smaller than the others and can fit entirely into the memory of a single machine.

How It Works:

Instead of using a traditional MapReduce job that involves a shuffle-and-sort phase in the reducers, Hive performs the join directly within the map phase.

Read and Broadcast: Hive first reads all the data from the small table and loads it into a hash table in memory. This hash table is then distributed to all the mappers that will process the large table. This process is called broadcasting.

Map Phase Join: Each mapper reads a chunk of the big table. For each row it processes, it looks up the corresponding key in the in-memory hash table of the small table. The join is performed locally on each mapper.

No Reduce Phase: Since all the joining happens in the map phase, there's no need to shuffle data across the network to reducers. This eliminates the costly reduce phase and significantly improves query performance, especially for joins involving one small table and one or more large tables.

You can explicitly hint Hive to perform a map join using the /*+ MAPJOIN(table_name) */ syntax, or Hive can be configured to automatically convert joins to map joins if a table is below a certain size threshold.

Data Selection and Scope

Outer join (left, right, and full) and cross join preserve their HQL semantics
Map join means that join is computed only by map job without reduce job
In map join all data are read from a small table to memory and broadcasted to all maps
During map phase each row in from a big table is compared with the rows in small tables against join conditions
Join performance is improved because there is no reduce phase

Hive automatically optimizes certain joins into map joins for improved performance. The /*+ MAPJOIN(orders) */ hint explicitly instructs Hive to use a map join.

How It Works

In the query SELECT /*+ MAPJOIN(orders) */ c_name, c_phone, o_orderkey, o_orderstatus FROM customer JOIN orders ON c_custkey = o_custkey;, the /*+ MAPJOIN(orders) */ hint tells Hive to load the orders table into memory and distribute it to all mappers. This is done because the orders table is likely smaller than the customer table. The join operation is then performed by each mapper as it processes the customer table, avoiding the need for a costly shuffle and reduce phase.

Automatic Map Joins and Bucket Map Joins

Automatic Conversion: Hive can automatically convert a join into a map join if the hive.auto.convert.join property is set to true. This happens when the smaller table's size is below a certain threshold (defined by hive.mapjoin.smalltable.filesize).

Bucket Map Join: This is a specialized, more efficient type of map join. If both tables are bucketed on the same join key and have the same number of buckets, Hive can perform a bucket map join. Instead of loading the entire smaller table into memory, it only loads the corresponding bucket, further reducing the memory footprint and improving performance. This optimization is enabled by setting hive.optimize.bucketmapjoin to true.

Data Selection and Scope


SELECT /*+ MAPJOIN(orders) */ c_name, c_phone, o_orderkey, o_orderstatus
FROM customer JOIN orders
              ON c_custkey = o_custkey;

Hive automatically converts the JOIN to MAPJOIN at runtime when hive.auto.convert.join setting is set to true
Bucket map join is a special type of MAPJOIN that uses bucket columns in join condition.
Then instead of fetching the whole table bucket map join only fetches the required bucket data.
A variable hive.optimize.bucketmapjoin must be set to true to enable bucket map join

Hive's LEFT SEMI JOIN is a specialized type of join that checks for the existence of a record. It returns all rows from the left table that have at least one match in the right table.

Key Characteristics

Filter, Not Join: A LEFT SEMI JOIN doesn't actually join the two tables in the traditional sense. Instead, it acts as a filter on the left table, returning only those records that have a corresponding match in the right table.

Performance: It's highly efficient because Hive can stop processing the right table as soon as it finds the first match for a row from the left table.

Restrictions: You can only reference columns from the left-hand side table in the SELECT or WHERE clauses. Columns from the right-hand side table are only allowed in the ON join condition.

In the example SELECT c_name, c_phone FROM customer LEFT SEMI JOIN orders ON c_custkey = o_custkey;, the query is asking for:

All c_name and c_phone records from the customer table.

But only for those customers who have at least one corresponding entry in the orders table (based on the matching c_custkey).

This is an efficient way to find which customers have placed an order without having to duplicate customer information for every order they've placed. It's often used as a more performant alternative to a WHERE EXISTS subquery.

Data Selection and Scope

Hive supports LEFT SEMI JOIN

	      
SELECT c_name, c_phone
FROM customer LEFT SEMI JOIN orders
              ON c_custkey = o_custkey;

In LEFT SEMI JOIN the right-hand side table should only be referenced in the join condition and not in WHERE or SELECT clauses

Hive supports the UNION ALL operation to combine the result sets of two or more SELECT statements. It does not natively support the INTERSECT or MINUS operations, but you can achieve the same functionality using alternative query structures.

UNION ALL

The UNION ALL operator combines the results of multiple SELECT queries into a single result set. The columns in the combined queries must be the same number, have similar data types, and be in the same order. UNION ALL is often preferred over UNION because it returns all rows, including duplicates, which is more efficient as it avoids the overhead of sorting and removing duplicates.

In the example SELECT p_name FROM PART UNION ALL SELECT c_name FROM CUSTOMER;, the query will return a single list containing all product names and all customer names.

Simulating INTERSECT

An INTERSECT operation returns only the rows that exist in both result sets. In Hive, you can achieve this by using an INNER JOIN.

Example:

To find common names between PART and CUSTOMER tables, you could use:

SELECT T1.p_name FROM PART T1JOIN CUSTOMER T2 ON T1.p_name = T2.c_name;

Simulating MINUS

A MINUS (or EXCEPT) operation returns rows from the first result set that are not present in the second result set. This can be simulated using a LEFT OUTER JOIN with a WHERE clause that checks for NULL values.

Example:

To find product names that do not exist as customer names, you could use:

SELECT T1.p_name FROM PART T1 LEFT OUTER JOIN CUSTOMER T2 ON T1.p_name = T2.c_name WHERE T2.c_name IS NULL;

This query joins all product names with customer names, and if a product name doesn't have a matching customer name, the c_name column from the CUSTOMER table will be NULL, which is then filtered by the WHERE clause.

Data Selection and Scope

Hive supports UNION ALL it does not support INTERSECT and MINUS operations
INTERSECT operation can be implemented as JOIN operation
MINUS operation can be implemented as LEFT OUTER JOIN operation with IS NULL condition in WHERE clause

Hive Programming

The LOAD DATA statement in Hive is used to move data from a file system into a Hive table. This command physically moves the data files, rather than just linking to them.

Key Elements of the LOAD DATA Statement

LOAD DATA: The core command for loading data into a Hive table.

LOCAL: This keyword specifies that the input file(s) are located on the local file system of the machine where the Hive client is running. Without this keyword, Hive assumes the files are on HDFS.

INPATH: Defines the path to the source file or directory.

OVERWRITE: This is an optional keyword. If included, it deletes all existing data in the target table or partition before loading the new data. If omitted, the new data is appended to the table.

INTO TABLE: Specifies the name of the destination Hive table.

PARTITION: For partitioned tables, this clause is used to specify which partition the data should be loaded into.

Loading Data from a Local File

The command LOAD DATA LOCAL INPATH '/local/home/janusz/HIVE-EXAMPLES/TPCHR/part.txt' OVERWRITE INTO TABLE part; performs the following actions:

It locates the file part.txt on the local file system.

The OVERWRITE keyword tells Hive to clear any data currently in the part table.

The data from part.txt is then copied and loaded into the Hive part table.

Loading Data into a Partitioned Table

For a partitioned table, you must specify the target partition.

The command LOAD DATA LOCAL INPATH '/local/home/janusz/HIVE-EXAMPLES/TPCHR/part.txt' OVERWRITE INTO TABLE part PARTITION(P_BRAND='GoldenBolts'); does the following:

It takes the data from part.txt on the local file system.

It overwrites the data specifically in the partition where the P_BRAND column equals 'GoldenBolts'.

The data from the file must correspond to this partition; for example, every record in part.txt should logically belong to the GoldenBolts brand.

Data Manipulation

LOAD statement can be used to load data to Hive tables from local file system or from HDFS

Load data to Hive table from a local file

	      
LOAD DATA LOCAL INPATH '/local/home/janusz/HIVE-EXAMPLES/TPCHR/part.txt'
OVERWRITE INTO TABLE part;

Load data to Hive partitioned table from a local file


LOAD DATA LOCAL INPATH '/local/home/janusz/HIVE-EXAMPLES/TPCHR/part.txt'
OVERWRITE INTO TABLE part PARTITION
(P_BRAND='GoldenBolts');

LOCAL keyword determines a location of the input files

You can load data from HDFS into a Hive table using the LOAD DATA INPATH command. When the LOCAL keyword is not specified, Hive assumes the source files are located on HDFS.

HDFS Path Specification

Default System Path: If you provide a relative path (e.g., /user/janusz/part.txt), Hive uses the default HDFS URI configured in its settings (fs.defaultFS) to locate the file. The command LOAD DATA INPATH '/user/janusz/part.txt' OVERWRITE INTO TABLE part; tells Hive to find the part.txt file in the /user/janusz/ directory of the default HDFS cluster and load it into the part table.

Full URI: For clusters with multiple HDFS instances, you can specify the full URI to ensure Hive loads the data from the correct cluster. The command LOAD DATA INPATH 'https://url.au.m.mimecastprotect.com/s/Pa3nCVAROOCpWRGEUyiotETfjB?domain=10.9.28.14' OVERWRITE INTO TABLE part; explicitly points to a file on the HDFS Namenode located at 10.9.28.14 on port 8020.

The OVERWRITE keyword is crucial for controlling how data is loaded. It determines whether to append to the existing data or replace it entirely. Omitting OVERWRITE will add new data to the table, while including it will first delete all existing data in the table or partition before loading the new files.

Data Manipulation

Load HDFS data to the Hive table using the default system path

	      
LOAD DATA INPATH '/user/janusz/part.txt'
OVERWRITE INTO TABLE part;

Load HDFS data to the Hive table using using full URI

	      
LOAD DATA INPATH 'https://url.au.m.mimecastprotect.com/s/Pa3nCVAROOCpWRGEUyiotETfjB?domain=10.9.28.14'
OVERWRITE INTO TABLE part;

If LOCAL keyword is not specified, the files are loaded from the full URI specified after INPATH or the value from the fs.default
OVERWRITE keyword decides whether to append or replace the existing data in the target table/partition

The EXPORT and IMPORT statements in Hive are used to migrate or back up data and its schema. The EXPORT command is particularly useful because it exports both the data and the metadata (schema) of a table or partition to a specified HDFS directory.

EXPORT Statement

The syntax EXPORT TABLE part TO '/user/tpchr/part' performs two key actions:

Exports Data: It copies the data files from the Hive table named part to the designated HDFS directory /user/tpchr/part.

Exports Metadata: It generates a special file named _metadata within the export directory. This file contains the table's schema, including column names, data types, and other properties. This ensures that the table can be re-created with the correct structure during an IMPORT operation.

As seen in the example. the exported directory contains the _metadata file and the data files (e.g., in a directory for a partition like p_brand=GoldenBolts). After the EXPORT command, this entire directory can be easily moved to another HDFS cluster or used for a later restore.

Data Manipulation

EXPORT and IMPORT statements are available to support the import and export of data in HDFS for data migration or backup/restore purposes
EXPORT statement exports both data and metadata from a table or partition

Metadata is exported to a file called _metadata


-rwxr-xr-x 3 janusz supergroup 2739 2017-07-09
14:37 /user/tpchr/part/_metadata
drwxr-xr-x - janusz supergroup 0 2017-07-09
14:37 /user/tpchr/part/p_brand=GoldenBolts

After EXPORT the exported files can be copied to other Hive instances or to other HDFS clusters

The IMPORT statement in Hive is used to restore a table and its data from a directory that was created using the EXPORT command. It's a quick way to migrate or restore an entire table, including its schema, from one Hive instance to another.

Standard IMPORT

The command IMPORT TABLE new_part FROM '/user/tpchr/part'; does the following:

It reads the _metadata file and data files from the specified HDFS location (/user/tpchr/part).

It creates a new internal table named new_part.

The data files from the source directory are then moved (not copied) into Hive's default warehouse directory (e.g., /user/hive/warehouse/new_part). This means the source directory will be empty after the import.

IMPORT EXTERNAThe command IMPORT EXTERNAL TABLE new_extpart FROM '/user/tpchr/part'; is similar but creates an external table instead of an internal one.

It creates an external table named new_extpart.

Instead of moving the data, it links to the existing data files in the source HDFS directory (/user/tpchr/part). The data remains in its original location.

This is useful for scenarios where you want to keep the data separate from Hive's managed storage, such as when data is shared with other applications.

In both cases, the _metadata file ensures that the imported table's schema (column names, types, etc.) is exactly the same as the original exported table. The key difference lies in whether the data is moved into Hive's warehouse (IMPORT) or simply referenced in place (IMPORT EXTERNAL).

Data Manipulation

IMPORT statement imports files exported from other HIVE instances into an internal table

An imported table is located in a default HIVE location in HDFS

drwxrwxr-x - janusz supergroup 0 2017-07-09
14:56 /user/hive/warehouse/new_part

IMPORT EXTERNAL statement imports a file exported from other HIVE instances into an external table

An imported table is located in a default HIVE location in HDFS


drwxrwxr-x - janusz supergroup 0 2017-07-09
15:04 /user/hive/warehouse/new_extpart

ORDER BY and SORT BY are both used to sort data in Hive, but they have key differences in how they perform the sort, which impacts performance.

The ORDER BY clause performs a global sort on the entire result set. This is done by a single reducer, which receives all the output from the mappers, sorts it, and then writes the final result. While this ensures the data is completely sorted across the entire output, it can be very slow and resource-intensive for large datasets because a single reducer can become a bottleneck.

SELECT p_partkey, p_name FROM part ORDER BY p_name ASC;

This query will sort all part names alphabetically across the entire dataset, using only one reducer for the sort operation.

The SORT BY clause performs a local sort, meaning it sorts the data within each reducer. It does not guarantee a total global order. This is a more efficient method because the sorting work is distributed across multiple reducers.

SET mapred.reduce.tasks = 2;

SELECT p_partkey, p_name FROM part SORT BY p_name ASC;

In this example, with two reducers, the data will be sent to the two reducers and each reducer will sort its own set of records independently. The final output will be two sorted files, but the records across the two files will not be globally sorted.

This is a good choice for performance when you only need to ensure that the data within each partition or reducer is sorted, not the entire result set.

Data Manipulation

ORDER BY sorts the results of SELECT statement
An order is maintained across all of the output from every reducer and global sort is performed using only one reducer
SORT BY does the same job as ORDER BY and indicates which columns to sort when ordering the reducer input records
SORT BY completes sorting before sending data to the reducer
SORT BY statement does not perform a global sort and only makes sure data is locally sorted in each reducer

The DISTRIBUTE BY clause in Hive is used to control how rows are partitioned among the reducers. It ensures that all rows with the same value for the specified columns are sent to the same reducer. This is crucial for operations that require related data to be processed together, such as joins or aggregations.

Partitioning: When you use DISTRIBUTE BY, Hive hashes the values of the specified columns to determine which reducer will process the row. All rows with the same hash value (and thus the same column value) are guaranteed to go to the same reducer.

Analogy: Think of it like sorting mail. DISTRIBUTE BY is the first step where you put all the letters for the same address into the same pile. SORT BY is the second step, where you arrange the letters within each pile by name.

Relationship with SORT BY: DISTRIBUTE BY is often used in combination with SORT BY. The DISTRIBUTE BY clause handles the partitioning, and the SORT BY clause then sorts the data locally within each reducer. This provides a more efficient way to sort large datasets than a global ORDER BY clause.

The DISTRIBUTE BY clause must come before the SORT BY clause in a query.

In the query SELECT p_partkey, p_name FROM part DISTRIBUTE BY p_partkey SORT BY p_name;:

DISTRIBUTE BY p_partkey: All rows with the same p_partkey value are sent to the same reducer.

SORT BY p_name: The data within each of those reducers is then sorted alphabetically by p_name.

This approach ensures that all related data is grouped together for processing while still providing localized sorting for efficiency.

Data Manipulation

When DISTRIBUTE BY clause is applied rows with matching column values are partitioned by the same reducer
DISTRIBUTE BY clause is similar to GROUP BY in relational systems in terms of deciding which reducer is used to distribute the mapper
When using with SORT BY, DISTRIBUTE BY must be specified before the SORT BY statement

The CLUSTER BY clause is a convenient shorthand in Hive that combines the functionality of both DISTRIBUTE BY and SORT BY on the same set of columns.

When you use CLUSTER BY on a column, Hive performs two actions:

DISTRIBUTE BY: It partitions the data by the specified column, sending all rows with the same value for that column to the same reducer.

SORT BY: It then sorts the data locally within each reducer based on the same column.

The query SELECT p_partkey, p_name FROM part CLUSTER BY p_name; is equivalent to SELECT p_partkey, p_name FROM part DISTRIBUTE BY p_name SORT BY p_name;. This ensures that all records with the same part name are sent to the same reducer and are then sorted within that reducer.

ORDER BY vs. CLUSTER BY

ORDER BY performs a global sort, using a single reducer, which can be a performance bottleneck for large datasets. CLUSTER BY performs a local sort across multiple reducers. While the data is sorted within each reducer, there is no global sort order.

Combining CLUSTER BY and ORDER BY

You can use both clauses to get the benefits of both local and global sorting. By first using CLUSTER BY to distribute and locally sort the data across multiple reducers, you can then perform a final ORDER BY for a global sort. This can be faster because the ORDER BY operation receives partially sorted data from the reducers, reducing the final sort's workload.

Data Manipulation

CLUSTER BY clause is a shorthand operator to perform
DISTRIBUTE BY and SORT BY operations on the same group of columns.
ORDER BY performs a global sort, while CLUSTER BY sorts in each distributed group
To fully utilize all the available reducers we can do CLUSTER BY first and then ORDER BY

Hive Programming

Outline

Data Selection and Scope
Data Manipulation
Data Aggregation and Sampling

Hive, a data warehouse software built on Hadoop, provides a variety of functions for aggregating and analyzing data. These functions allow users to perform complex data analysis and create meaningful reports. Hive supports basic aggregation using GROUP BY, advanced aggregation with GROUPING SETS, ROLLUP, and CUBE, and also offers powerful analytic functions and windowing clauses.

The most common form of aggregation in Hive uses the GROUP BY clause. This clause groups rows that have the same values in specified columns into summary rows. The GROUP BY clause is typically used with aggregation functions like COUNT, SUM, AVG, MIN, and MAX.

For example, to count the number of parts for each part type (p_type), you'd use the following query:

SELECT p_type, count(*) FROM part GROUP BY p_type;

This query groups all rows with the same p_type and then counts the number of rows in each group.

Advanced Aggregation: GROUPING SETS, ROLLUP, and CUBE

Hive provides more advanced aggregation options for generating multiple grouping combinations in a single query, which is more efficient than writing multiple GROUP BY queries.

GROUPING SETS: This clause allows you to specify multiple GROUP BY clauses in a single statement. It's useful when you want to see subtotals for different sets of columns. For example, you could get a count of parts by p_type and also a total count of all parts in one query.

ROLLUP: This is a shorthand for GROUPING SETS that generates all possible subtotals for a given hierarchy. It creates a hierarchy of groupings, from the most detailed to the grand total. For example, GROUP BY ROLLUP(col1, col2) would produce groupings for (col1, col2), (col1), and (), where () represents the grand total.

CUBE: This is another shorthand that generates all possible combinations of subtotals. It's similar to ROLLUP but it generates a combination of subtotals for all possible combinations of the specified columns. GROUP BY CUBE(col1, col2) would generate groupings for (col1, col2), (col1), (col2), and ().

Analytic Functions and Windowing Clauses

Analytic functions (also known as window functions) compute values over a "window" of rows. Unlike aggregation functions that collapse rows into a single summary row, analytic functions return a value for each row in the result set. They are often used to calculate a running total, a moving average, or a rank within a group.

The OVER clause defines the window or set of rows over which the function operates. Within the OVER clause, you can use:

PARTITION BY: This divides the rows into partitions (groups) on which the analytic function is applied independently. This is similar to GROUP BY, but it doesn't collapse the rows.

ORDER BY: This specifies the order of rows within each partition. This is crucial for functions like RANK or ROW_NUMBER.

Windowing clauses: These clauses, like ROWS BETWEEN or RANGE BETWEEN, further refine the set of rows within a partition. For instance, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW would define a window of the current row and the two rows before it.

collect_set Function

The collect_set function is an aggregation function that returns an array of unique objects for a given group. This is useful for gathering all distinct values for a column into a single list for each group. .

The example SELECT p_type, collect_set(p_name), count(*) FROM part GROUP BY p_type; groups the data by p_type. For each group, it returns a unique set of p_name values and a count of the rows. The result for each p_type would be a single row containing the type, an array of unique part names, and the total count.

Data Aggregation and Sampling

Hive supports several aggregation functions, analytic functions working with GROUP BY and PARTITION BY, and windowing clauses
Hive supports advanced aggregation by using GROUPING SETS, ROLLUP, CUBE, analytic functions, and windowing
Basic aggregation uses GROUP BY clause and aggregation functions

To aggregate into sets a function collect_set can be used


SELECT p_type, collect_set(p_name), count(*)
FROM part
GROUP BY p_type;

Advanced Aggregation: GROUPING SETS, ROLLUP, and CUBE

Hive provides more advanced aggregation options for generating multiple grouping combinations in a single query, which is more efficient than writing multiple GROUP BY queries.

Data Aggregation and Sampling

GROUPING SETS clause implements advanced multiple GROUP BY operations against the same set of data


SELECT p_type, p_name, count(*)
FROM part
GROUP BY p_type, p_name
GROUPING SETS ( (p_type), (p_name) );

ROLLUP clause allows to calculate multiple levels of aggregations across a specified group of dimensions
CUBE clause allows to create aggregations over all possible subsets of attributes in a given set

GROUPING__ID and HAVING are both powerful tools in Hive for more advanced data analysis, particularly when working with aggregation functions and multi-dimensional queries.

The GROUPING__ID function is a key feature in Hive that allows you to determine which columns are part of the grouping set for a given row. This is especially useful when using GROUPING SETS, ROLLUP, or CUBE, as these clauses generate rows with NULL values that can be difficult to distinguish from actual NULL data in the original table.

GROUPING__ID returns an integer value that is a bitmap representing the grouping columns for that row. The bitmap is created by assigning a bit to each column in the GROUP BY clause, in order from right to left. A 1 indicates that the column is not part of the grouping for that row (it's a subtotal or grand total), and a 0 indicates that the column is part of the grouping.

For the query SELECT GROUPING__ID, p_type, p_name, count(*) FROM part GROUP BY p_type, p_name WITH CUBE ORDER BY grouping__id;, here's how the GROUPING__ID values would break down:

GROUP BY p_type, p_name: GROUPING__ID would be 0. (p_type=0, p_name=0)

GROUP BY p_type: GROUPING__ID would be 2 (binary 10). (p_type=0, p_name=1)

GROUP BY p_name: GROUPING__ID would be 1 (binary 01). (p_type=1, p_name=0)

Grand Total (GROUP BY none): GROUPING__ID would be 3 (binary 11). (p_type=1, p_name=1)

By ordering the results by GROUPING__ID, you can easily see the different levels of aggregation in a structured way.

Data Aggregation and Sampling

GROUPING__ID function works as an extension to distinguish entire rows from each other


SELECT GROUPING__ID, p_type, p_name, count(*)
FROM part
GROUP BY p_type, p_name WITH CUBE
ORDER BY grouping__id;

HAVING can be used for the conditional filtering of GROUP BY results


SELECT GROUPING__ID, p_type, p_name, count(*)
FROM part
GROUP BY p_type, p_name WITH CUBE
HAVING count(*) > 1
ORDER BY grouping__id;

Analytic functions are a powerful set of functions in Hive that calculate a value for each row in a result set based on a group of related rows. Unlike standard aggregate functions (SUM, AVG, COUNT) that collapse multiple rows into a single summary row, analytic functions retain all the original rows while adding a new column with the calculated value. This is extremely useful for tasks like calculating running totals, moving averages, or ranking data.

The OVER clause is the key component that distinguishes an analytic function. It defines the "window" or set of rows on which the function operates. The rows in this window are related to the current row, allowing the function to perform a calculation based on that specific group of data.

Inside the OVER clause, you can use the following subclauses to define the window:

PARTITION BY: This clause divides the input rows into distinct partitions. The analytic function is then applied to each partition independently. It's similar to GROUP BY but does not collapse the rows. For example, you could partition sales data by product category to calculate a running total of sales for each category separately.

ORDER BY: This clause sorts the rows within each partition. This is essential for functions that depend on the order of the data, such as RANK, ROW_NUMBER, and LAG. For a running total, ORDER BY ensures the sum is calculated incrementally in the correct order.

Windowing Specification: This part of the OVER clause defines the specific frame of rows within the partition that the function should consider. You can specify a range of rows relative to the current row, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to calculate a running total, or ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING to calculate a moving average.

The general syntax for an analytic function in Hive is:

function (arg1, ..., argn) OVER ([PARTITION BY <...>][ORDER BY <...>][])

For standard aggregate functions like COUNT(), SUM(), MIN(), MAX(), or AVG(), using the OVER clause transforms them from aggregate functions into analytic functions. For example, SUM(sales) OVER (PARTITION BY product_category ORDER BY sale_date) would calculate a running total of sales for each product category, ordered by date.

Data Aggregation and Sampling

Analytic functions scan multiple input rows to compute each output value
Analytic functions are usually used with OVER, PARTITION BY, ORDER BY, and the windowing specification
Analytic functions operate on windows where the input rows are ordered and grouped using flexible conditions expressed through an OVER PARTITION clause

Syntax is the following

	      
function (arg1,..., argn)
OVER ([PARTITION BY <...>]
[ORDER BY <....>] [])

For standard aggregation function (arg1,..., argn) can be either COUNT(), SUM(), MIN(), MAX(), or AVG()

Let's look at the examples at the slide.

SELECT l_orderkey, l_partkey, l_quantity, RANK() OVER (ORDER BY l_quantity), DENSE_RANK() OVER (ORDER BY l_quantity) FROM lineitem;

This query ranks all rows in the entire lineitem table based on the l_quantity. It uses the OVER (ORDER BY ...) clause without a PARTITION BY, so the ranking is applied globally to the whole result set.

SELECT l_orderkey, l_partkey, l_quantity, RANK() OVER (PARTITION BY l_orderkey ORDER BY l_quantity), DENSE_RANK() OVER (PARTITION BY l_orderkey ORDER BY l_quantity) FROM lineitem;

This query is more specific. The PARTITION BY l_orderkey clause tells Hive to treat each unique l_orderkey as a separate group. The RANK() and DENSE_RANK() functions are then applied independently within each order, assigning a rank to each part based on its quantity for that specific order. This is a common and powerful pattern for finding the top N items within different categories.

Data Aggregation and Sampling

Typical aggregations implemented as analytic functions in the following way

	      
SELECT p_name,
       COUNT(*) OVER (PARTITION BY p_name)
FROM PART;

Other analytic functions are used as follows

	      
SELECT l_orderkey, l_partkey, l_quantity,
       RANK() OVER (ORDER BY l_quantity),
       DENSE_RANK() OVER (ORDER BY l_quantity)
FROM lineitem;

	      
SELECT l_orderkey, l_partkey, l_quantity,
       RANK() OVER (PARTITION BY l_orderkey ORDER BY l_quantity),
       DENSE_RANK() OVER (PARTITION BY l_orderkey ORDER BY l_quantity)
FROM lineitem;

Hive supports a wide variety of analytic functions beyond basic aggregations and ranking. These functions, used with the OVER clause and windowing specifications, allow for more complex analysis of data within a defined window of rows.

FIRST_VALUE and LAST_VALUE are analytic functions that return the value of an expression from the first and last row in a window frame, respectively. They are particularly useful for finding the first or last occurrence of a value within a group.

The query SELECT l_orderkey, l_partkey, l_quantity, FIRST_VALUE(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_quantity), LAST_VALUE(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_quantity) FROM lineitem; demonstrates this. For each row, the FIRST_VALUE function returns the smallest l_quantity for that l_orderkey, while the LAST_VALUE returns the largest. Notice how the PARTITION BY clause ensures the calculation is performed separately for each order.

Hive allows you to define a specific subset of rows within a partition using a windowing clause like ROWS BETWEEN ... AND .... This gives you precise control over which rows are included in the analytic function's calculation. This is particularly useful for performing calculations like moving averages or sliding sums.

Here are two examples using the MAX function with different windowing clauses:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: This window includes the current row and the two rows immediately preceding it, as defined by the ORDER BY clause. The query SELECT l_orderkey, l_partkey, l_quantity, MAX(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_partkey ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM lineitem; calculates a "sliding maximum". For each row, it finds the maximum l_quantity within its order, considering only the current row and the two preceding rows.

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: This window is a "sliding window" that includes the current row, the row immediately before it, and the row immediately after it. The query SELECT l_orderkey, l_partkey, l_quantity, MAX(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_partkey ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM lineitem; calculates a maximum over a three-row window that moves down the data set. This is a great way to smooth out data or find local extremes.

Data Aggregation and Sampling

More analytic functions ...

	      
SELECT l_orderkey, l_partkey, l_quantity,
       FIRST_VALUE(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_quantity),
       LAST_VALUE(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_quantity)
FROM lineitem;;

	      
SELECT l_orderkey, l_partkey, l_quantity,
       MAX(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_partkey
                             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM lineitem;

	      
SELECT l_orderkey, l_partkey, l_quantity,
       MAX(l_quantity) OVER (PARTITION BY l_orderkey ORDER BY l_partkey
                             ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM lineitem;

To efficiently analyze extremely large datasets, Hive offers various sampling techniques. These methods allow you to work with a smaller, manageable subset of data, which significantly speeds up query execution. Hive provides both random sampling and a more optimized method called bucket table sampling.

Random sampling selects rows randomly from the entire dataset. This method is straightforward and effective for generating a representative sample. You can achieve random sampling using the RAND() function along with the LIMIT clause.

The query SELECT * FROM lineitem DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 5; demonstrates this technique. Let's break down the clauses:

DISTRIBUTE BY RAND(): This clause randomly distributes the rows across different mappers, ensuring that the sampling is not biased towards a particular subset of the data. This improves efficiency and ensures a more random sample.

SORT BY RAND(): After distribution, this clause randomly sorts the rows within each mapper. This is a common practice when combined with DISTRIBUTE BY RAND().

LIMIT 5: This clause restricts the final output to a specific number of rows, effectively giving you a sample of 5 random rows from the table.

Bucket table sampling is an optimized method designed specifically for bucketed tables. Bucketing is a technique used in Hive to organize data into smaller, more manageable files based on the hash value of a specific column. This physical organization of data allows for very efficient sampling.

The query SELECT * FROM customer TABLESAMPLE(BUCKET 3 OUT OF 8 ON rand()); illustrates bucket table sampling.

TABLESAMPLE: This is the keyword used to specify table sampling.

BUCKET 3 OUT OF 8: This is the core of the sampling clause. It means that the query will select data from the third bucket out of a total of eight buckets. The sampling is based on the bucket number, making it extremely fast because Hive only needs to read a small fraction of the data.

ON rand(): While ON rand() is specified, the sampling is still based on the bucket number. This is a subtle point, as the bucket number is determined by the hash of the column defined during table creation, not by a random function at query time. The ON rand() keyword is often used to ensure that the chosen bucket is random, although for practical purposes, picking a specific bucket is more common for testing and analysis.

Bucket table sampling is significantly faster than random sampling on large datasets because it avoids a full table scan. Instead, it directly accesses and reads only the specific files (buckets) that contain the required sample, making it the preferred method for large-scale sampling.

Data Aggregation and Sampling

When data volume is extra large we can use a subset of data to speed up data analysis.

Random sampling uses the RAND() function and LIMIT clause to get the samples of data

	      
SELECT *
FROM lineitem DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 5;

DISTRIBUTE and SORT clauses are used here to make sure the data is also randomly and efficiently distributed among mappers and reducers
Bucket table sampling is a special sampling optimized for bucket tables

Hive's block sampling allows you to retrieve a random subset of data based on a specified number of rows, a percentage of the total data, or a specific byte size. This method is useful for quickly analyzing large datasets without the need to read the entire table.

Block Sampling by Row Count

This method lets you specify the exact number of rows you want to sample. The query SELECT * FROM lineitem TABLESAMPLE(4 ROWS); will randomly select and return exactly four rows from the lineitem table. Hive achieves this by reading the file metadata and picking the appropriate number of rows from different data blocks to satisfy the request.

Block Sampling by Percentage

You can also sample a specific percentage of your data. The query SELECT * FROM lineitem TABLESAMPLE(50 PERCENT); instructs Hive to return approximately 50% of the data from the lineitem table. Hive performs this by reading the first 50% of the input files, as the sampling is done at the file or block level. This is a quick and efficient way to get a representative portion of the dataset.

Block Sampling by Byte Size

This method is useful when you want to limit the size of your sample to a specific number of bytes. The query SELECT * FROM lineitem TABLESAMPLE(20B); will randomly pick data blocks from the table until the total size of the sampled data reaches approximately 20 bytes. This is an effective way to manage the amount of data processed, especially when dealing with very wide tables where the number of rows might not be the primary concern.

Data Aggregation and Sampling

Block sampling allows to randomly pick up N rows of data, percentage (n percentage) of data size, or N byte size of data


SELECT *
FROM lineitem TABLESAMPLE(4 ROWS);


SELECT *
FROM lineitem TABLESAMPLE(50 PERCENT);


SELECT *
FROM lineitem TABLESAMPLE(20B);

References

Gross C., GuptaA., Shaw S., Vermeulen A. F., Kjerrumgaar D., Practical Hive: A guide to Hadoop's Data Warehouse System, Apress 2016, Chapter 4 (Available through UOW library)
Lee D., Instant Apache Hive essentials how-to: leverage your knowledge of SQL to easily write distributed data processing applications on Hadoop using Apache Hive, Packt Publishing Ltd. 2013 (Available through UOW library)
Apache Hive TM, https://url.au.m.mimecastprotect.com/s/2kTaCWLV66CnMWJQTnsvtoe1w8?domain=hive.apache.org