15 Essential Hive Queries Every Data Engineer Must Know

Introduction

Apache Hive has become an indispensable tool for data engineers and analysts working with big data. Built on top of Hadoop, Hive provides a SQL-like interface for querying and analyzing massive datasets stored in distributed storage systems like HDFS. Its familiar query language, HiveQL (or just HQL), lowers the barrier to entry for those already experienced with SQL.

In this article, we‘ll walk through 15 of the most essential and frequently used Hive queries that every data engineer should have in their toolkit. Whether you‘re just getting started with big data or looking to deepen your Hive expertise, mastering these queries will boost your efficiency and effectiveness in wrangling and extracting insights from your data. Let‘s dive in!

1. Simple SELECT Queries

The foundation of HQL is the SELECT statement, which retrieves data from one or more tables. In its most basic form, you can select all columns from a table using:

SELECT * 
FROM my_table;

More often, you‘ll want to select only specific columns of interest:

SELECT user_id, purchase_amount, transaction_date
FROM transactions
WHERE transaction_date >= ‘2023-01-01‘;

The WHERE clause allows you to filter the result set based on specified conditions. You can use comparison operators like =, <, <=, >, >=, and logical operators like AND, OR.

2. Creating New Columns

HQL makes it easy to create new computed columns on the fly using arithmetic operators, string concatenation, and built-in functions. Simply include the expression in your SELECT clause and (optionally) provide an alias using AS:

SELECT 
    user_id,
    purchase_amount * 1.08 AS amount_with_tax,
    concat(state, ‘ - ‘, country) AS location  
FROM transactions;

3. Sorting with ORDER BY

To sort query results by one or more columns, add an ORDER BY clause. Sorting defaults to ascending order (ASC), but you can specify descending order with DESC.

SELECT user_id, purchase_amount
FROM transactions
WHERE purchase_amount > 100
ORDER BY purchase_amount DESC;

4. Limiting Results with LIMIT

When exploring a new dataset or testing out queries, it‘s often helpful to limit the number of rows returned to get a quick sense of the data. In HQL, the LIMIT keyword at the end of the query lets you specify how many rows to retrieve.

SELECT *
FROM users
LIMIT 10;

5. Built-in String Functions

Hive provides an assortment of built-in functions for working with string data. Some of the most handy ones include:

• concat(str1, str2, …) – concatenates multiple strings together
• substr(str, pos, len) – extracts a substring from str starting at position pos with length len
• length(str) – returns the length of the string
• instr(str, substr) – returns the position of the first occurrence of substr in str
• trim(str) – removes leading and trailing whitespace from str

SELECT 
    product_id,
    concat(manufacturer, ‘ ‘, name) AS product_name,
    substr(description, 1, 50) AS short_description,
    length(product_id) AS id_length  
FROM products;

6. Aggregation Functions

Aggregation functions operate on multiple rows and return a single aggregated value. COUNT, SUM, AVG, MIN, and MAX are some of the most frequently used ones.

SELECT
    category,
    count(*) AS num_products,
    sum(price) AS total_revenue,
    avg(price) AS avg_price,
    min(price) AS cheapest,
    max(price) AS most_expensive
FROM products
GROUP BY category;

The GROUP BY clause specifies which columns to group the aggregations by. Make sure to include all non-aggregated columns in GROUP BY.

7. Filtering Aggregations with HAVING

While WHERE filters rows before aggregation, the HAVING clause lets you filter grouped results after aggregation. It‘s useful for things like finding categories with sales above some threshold.

SELECT
    category, 
    sum(amount) AS total_sales
FROM transactions
GROUP BY category
HAVING total_sales > 1000000;

8. Date Functions

Hive offers several built-in functions for parsing dates and extracting date parts. The most commonly used ones are:

• unix_timestamp(str, fmt) – parses str into a Unix timestamp according to the format specified by fmt
• from_unixtime(unix_time, fmt) – converts Unix timestamp into a string per the given format
• year(date), month(date), day(date) – extracts the year, month, or day from a date or timestamp

SELECT
    from_unixtime(order_unixtime, ‘yyyy-MM-dd‘) AS order_date,
    year(from_unixtime(order_unixtime)) AS order_year,
    month(from_unixtime(order_unixtime)) AS order_month,
    count(*) AS num_orders  
FROM orders
GROUP BY order_year, order_month;

9. Joining Tables

Hive supports the standard SQL join types (inner, left, right, full outer) for combining data from multiple tables. The ON clause specifies the columns to join on.

SELECT 
    o.order_id,
    o.user_id, 
    sum(i.price * i.quantity) AS order_total
FROM orders o
JOIN order_items i ON o.order_id = i.order_id  
GROUP BY o.order_id, o.user_id;

10. Analytical Window Functions

Window functions compute values over a set of rows (a "window") related to the current row. They‘re powerful for calculating moving averages, rankings, and more. Some common ones include ROW_NUMBER, RANK, DENSE_RANK, and LEAD/LAG.

SELECT
    user_id,
    order_date,
    order_total,
    sum(order_total) OVER (
        PARTITION BY user_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total    
FROM orders;

This query calculates a running total of order amounts for each user over time.

11. Dealing with NULL Values

Hive provides special syntax for handling NULL values in queries:

• IS NULL checks if a value is NULL
• IS NOT NULL checks if a value is not NULL
• COALESCE returns the first non-NULL value in a list
• ISNULL evaluates an expression and returns true if the result is NULL

SELECT
    user_id,
    COALESCE(last_order_date, ‘NEVER‘) AS last_order 
FROM users;

12. Views

As your queries get more complex, you can encapsulate them into logical views for reuse and abstracting away details. Use CREATE VIEW:

CREATE VIEW user_monthly_stats AS
SELECT
    user_id,
    year(order_date) AS y,
    month(order_date) AS m, 
    count(distinct order_id) AS num_orders,
    sum(order_total) AS total_revenue  
FROM orders
GROUP BY user_id, y, m;

13. CASE Expressions

CASE allows you to evaluate conditions and return different values depending on the result, similar to if/else statements in programming languages.

SELECT 
    product_id,
    price,
    CASE 
        WHEN price < 50 THEN ‘cheap‘
        WHEN price < 100 THEN ‘mid-range‘
        ELSE ‘premium‘
    END AS price_category  
FROM products;

14. Subqueries

Subqueries let you nest one query inside another, either in the SELECT, FROM, or WHERE clause. They‘re helpful for calculations that depend on aggregated results.

SELECT 
    category,
    total_sales
FROM (
    SELECT
        category,
        sum(amount) AS total_sales
    FROM transactions
    GROUP BY category
) t
WHERE total_sales > (SELECT avg(total_sales) FROM t);  

This finds categories with above-average total sales, using a subquery to first calculate per-category totals.

15. Relational Set Operators

Hive supports standard SQL set operations for combining result sets of multiple queries:

• UNION combines result sets and removes duplicates
• UNION ALL combines result sets without removing duplicates
• INTERSECT returns distinct rows present in both result sets
• EXCEPT returns distinct rows present in first result set but not second

SELECT user_id FROM customers2021
INTERSECT 
SELECT user_id FROM customers2022;

This finds users who were customers in both 2021 and 2022.

Performance Optimization Tips

To make your Hive queries run efficiently on large datasets, keep these best practices in mind:

• Only select the columns you actually need, to minimize I/O
• Make joins and filters as selective as possible to reduce the amount of data shuffled
• Use partitioned and/or bucketed tables for faster querying of relevant data
• Avoid expensive cross joins and UDFs if possible
• Use the EXPLAIN command to examine the query execution plan and identify bottlenecks

Conclusion

Mastering these 15 essential Hive queries will make you a more effective and efficient data engineer. You‘ll be able to quickly slice and dice big datasets to extract valuable insights.

Of course, this is just the tip of the iceberg – Hive has many more functions and features to help tackle complex data problems. So keep on learning and practicing! With its SQL-like interface and integration with the Hadoop ecosystem, Hive will continue to be a go-to tool for wrangling big data.