In this article, we’ll explore how to enable vectorization in Hive and create an example to demonstrate its benefits.
1. Understanding Hive Vectorization
Vectorization in Hive is used to improve query performance by processing a block of 1024 rows at once instead of one row at a time. This can provide a significant performance boost, especially for analytical queries on large datasets.
2. Creating a Table with Vectorization
We’ll create a table named freshers_in_orders to store information about product orders.
CREATE TABLE freshers_in_orders (
order_id INT,
customer_id INT,
product_id INT,
quantity INT,
order_date DATE
)
STORED AS ORC;
Note: ORC (Optimized Row Columnar) file format is typically used with vectorization because of its efficient column-wise storage.
3. Inserting Data into the Table
Here’s an example of how you can insert data into the freshers_in_orders table:
INSERT INTO freshers_in_orders (order_id, customer_id, product_id, quantity, order_date) VALUES
(1, 101, 1001, 2, '2023-08-12'),
(2, 102, 1002, 1, '2023-08-11'),
(3, 101, 1003, 3, '2023-08-10');
4. Enabling Vectorization in Hive
Before running a query, make sure that vectorization is enabled by executing the following command:
SET hive.vectorized.execution.enabled = true;
5. Running a Query with Vectorization
Now, let’s run a query on the freshers_in_orders table to calculate the total quantity for each customer:
SELECT customer_id, SUM(quantity) as total_quantity
FROM freshers_in_orders
GROUP BY customer_id;
6. Sample Query Result
The result of the query could look like this:
+--------------+----------------+
| customer_id | total_quantity |
+--------------+----------------+
| 101 | 5 |
| 102 | 1 |
+--------------+----------------+
Vectorization in Hive is an incredibly powerful feature that can significantly enhance query performance, making it an essential tool for data analysts and data scientists working with large datasets.
Hive important pages to refer
