10 Apache Hive Interview Questions and Answers

Apache Hive is a powerful data warehousing tool built on top of Hadoop, designed to facilitate the querying and analysis of large datasets. It provides a SQL-like interface, making it accessible for those familiar with traditional relational databases while leveraging the scalability and flexibility of Hadoop’s distributed storage and processing capabilities. Hive is widely used in big data environments for tasks such as data summarization, querying, and analysis.

This article offers a curated selection of interview questions and answers focused on Apache Hive. By reviewing these questions, you will gain a deeper understanding of Hive’s core concepts, functionalities, and best practices, helping you to confidently demonstrate your expertise in interviews.

Apache Hive Interview Questions and Answers

1. Describe the main components of Hive architecture and their roles.

Hive architecture consists of several components, each with a specific role:

• Metastore: Stores metadata about Hive tables, essential for query planning and execution.
• Driver: Manages the lifecycle of a HiveQL statement, from receiving to executing it.
• Compiler: Translates HiveQL queries into a DAG of map-reduce tasks, performing parsing and optimization.
• Execution Engine: Executes tasks generated by the Compiler, interacting with Hadoop’s MapReduce or Tez.
• HiveServer2: Enables clients to execute queries against Hive, supporting multiple clients and better concurrency.
• CLI (Command Line Interface): Allows users to interact with Hive by submitting queries directly from the terminal.
• Web Interface: Provides a web-based interface for users to interact with the system and view results.

2. How would you create a partitioned table? Provide a HiveQL example.

Partitioning in Hive divides a large table into smaller pieces based on column values, improving query performance by scanning only relevant partitions. Here’s how to create a partitioned table:

CREATE TABLE sales (
    sale_id INT,
    product_id INT,
    amount DOUBLE
)
PARTITIONED BY (sale_date STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

In this example, the sales table is partitioned by sale_date, storing data in separate directories based on its values.

3. Write a HiveQL query to perform an inner join between two tables ‘orders’ and ‘customers’ on the ‘customer_id’ column.

To perform an inner join between ‘orders’ and ‘customers’ on ‘customer_id’, use:

SELECT 
    orders.order_id,
    orders.order_date,
    customers.customer_name
FROM 
    orders
INNER JOIN 
    customers
ON 
    orders.customer_id = customers.customer_id;

4. How would you use a window function to rank employees by their ‘salary’ within each ‘department’? Provide a HiveQL example.

Window functions in Hive perform calculations across related rows. To rank employees by salary within each department, use:

SELECT 
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM 
    employees;

This query partitions results by department, orders by salary, and assigns ranks.

5. How would you create a bucketed table? Provide a HiveQL example.

A bucketed table divides data into fixed buckets based on a column’s hash, optimizing joins and aggregations. Here’s how to create one:

CREATE TABLE employee (
    id INT,
    name STRING,
    department STRING,
    salary FLOAT
)
CLUSTERED BY (department) INTO 4 BUCKETS;

This example buckets the employee table by department into 4 buckets.

6. Explain how Hive supports ACID transactions and what configurations are required.

Hive supports ACID transactions for reliable data operations. To enable them, configure the following in hive-site.xml:

1. <name>hive.support.concurrency</name> set to true.
2. <name>hive.txn.manager</name> set to org.apache.hadoop.hive.ql.lockmgr.DbTxnManager.
3. <name>hive.compactor.initiator.on</name> set to true.
4. <name>hive.compactor.worker.threads</name> set to 1.

Create transactional tables with:

CREATE TABLE example_table (
    id INT,
    name STRING
) CLUSTERED BY (id) INTO 3 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

Ensure the Metastore uses a supported RDBMS like MySQL or PostgreSQL.

7. Write a HiveQL query that uses a subquery to find the names of employees who have a salary higher than the average salary in their department.

To find employees with salaries higher than their department’s average, use:

SELECT e.name
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

8. What are some techniques for optimizing queries for better performance?

Optimizing Hive queries involves:

• Partitioning: Reduces data scanned during execution.
• Bucketing: Further divides data for efficient joins.
• Indexing: Speeds up execution by reducing scanned data.
• File Formats: Use ORC or Parquet for efficient compression and encoding.
• Query Optimization: Techniques like predicate pushdown and vectorization improve execution plans.
• Resource Management: Properly configure resource allocation and tuning parameters.

9. Explain the differences between ORC and Parquet file formats.

ORC and Parquet are columnar storage formats with differences:

• Data Storage: ORC optimizes for read performance; Parquet balances read and write performance.
• Compression: ORC uses lightweight techniques; Parquet supports various algorithms.
• Schema Evolution: Parquet allows easier schema changes; ORC is less flexible.
• Performance: ORC is read-optimized; Parquet suits a wider range of use cases.
• Compatibility: Parquet is widely supported; ORC is primarily used in the Hadoop ecosystem.

10. Discuss the use of UDFs (User-Defined Functions).

User-Defined Functions (UDFs) in Hive allow custom operations not available through built-in functions. They can be written in Java and registered in Hive for use in SQL queries.

Example:

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class MyUpperCase extends UDF {
    public Text evaluate(Text input) {
        if (input == null) {
            return null;
        }
        return new Text(input.toString().toUpperCase());
    }
}

To use this UDF in Hive:

• Compile the Java code and create a JAR file.
• Add the JAR file to the Hive session using the ADD JAR command.
• Create a temporary function in Hive using the CREATE TEMPORARY FUNCTION command.

ADD JAR /path/to/your/udf.jar;
CREATE TEMPORARY FUNCTION my_upper_case AS 'com.example.MyUpperCase';
SELECT my_upper_case(column_name) FROM table_name;