All posts

Hive Interview Questions and Answers

Find 100+ Hive interview questions and answers to assess candidates' skills in SQL-like querying, data warehousing, partitioning, optimization, and Hadoop ecosystem integration.
By
WeCP Team
Table of Content
Schedule A DemoAssess Candidate's Skills

As organizations continue processing massive datasets across distributed systems, recruiters must identify Hive professionals who can efficiently query, manage, and structure big data in Hadoop ecosystems. With expertise in HiveQL, data warehousing concepts, partitioning, and performance tuning, Hive specialists play a crucial role in scalable analytics and ETL workflows.

This resource, "100+ Hive Interview Questions and Answers," is tailored for recruiters to simplify the evaluation process. It covers topics ranging from Hive fundamentals to advanced optimization techniques, including bucketing, Tez/Spark execution engines, and schema design.

Whether you're hiring Big Data Engineers, Hive Developers, Hadoop Engineers, or Data Engineers, this guide enables you to assess a candidate’s:

  • Core Hive Knowledge: Databases, tables (managed/external), partitions, buckets, HiveQL syntax, and data types.
  • Advanced Skills: ACID tables, ORC/Parquet formats, Tez/Spark execution engines, vectorized queries, JOIN optimizations, and handling skewed data.
  • Real-World Proficiency: Designing warehouse schemas, optimizing query performance, building ETL pipelines, and integrating Hive with tools like Spark, Sqoop, or Kafka.

For a streamlined assessment process, consider platforms like WeCP, which allow you to:

  • Create customized Hive assessments tailored to big data analytics or ETL engineering roles.
  • Include hands-on tasks such as writing HiveQL queries, optimizing partition strategies, or debugging job failures.
  • Proctor exams remotely while ensuring integrity.
  • Evaluate results with AI-driven analysis for faster, more accurate decision-making.

Save time, enhance your hiring process, and confidently hire Hive professionals who can build efficient, scalable, and production-ready big data solutions from day one.

Hive Interview Questions

Hive – Beginner (1–40)

  1. What is Apache Hive?
  2. How does Hive differ from a traditional RDBMS?
  3. What is Hive metastore?
  4. What is the default file format used in Hive?
  5. What is the role of HiveQL?
  6. What are managed and external tables in Hive?
  7. What is a Hive partition?
  8. What is a Hive bucket?
  9. What is a SerDe in Hive?
  10. What is the default data location of a Hive table?
  11. What is the importance of schema-on-read in Hive?
  12. What happens when you drop a managed table?
  13. What happens when you drop an external table?
  14. What file formats does Hive support?
  15. What are Hive primitive data types?
  16. What are complex data types in Hive?
  17. What is the difference between ORDER BY and SORT BY?
  18. What is the difference between SORT BY and DISTRIBUTE BY?
  19. What is the difference between DISTRIBUTE BY and CLUSTER BY?
  20. What is a Hive view?
  21. What is the purpose of the ANALYZE command in Hive?
  22. How does Hive handle NULL values?
  23. What is the purpose of LIMIT clause in Hive?
  24. What is LazySerDe in Hive?
  25. What is the difference between TEXTFILE and SEQUENCEFILE?
  26. How does Hive store metadata?
  27. What is a Hive query execution engine?
  28. What are UDFs in Hive?
  29. What is the purpose of the CONCAT function in Hive?
  30. What is a Hive CLI?
  31. What is the role of Beeline in Hive?
  32. What is the difference between Hive CLI and Beeline?
  33. What is the purpose of the SHOW TABLES command?
  34. What does DESCRIBE FORMATTED show?
  35. What is a Hive metastore service?
  36. What is the default mode of Hive execution?
  37. What is the significance of LOCAL vs DFS paths in Hive?
  38. What will happen if data fields exceed the defined column count in Hive?
  39. What is the use of the MAP data type in Hive?
  40. What are Hive temporary tables?

Hive – Intermediate (1–40)

  1. Explain Hive architecture in detail.
  2. How does Hive interact with Hadoop?
  3. Explain the full query lifecycle inside Hive.
  4. What is the role of the Driver in Hive?
  5. What is the role of the Compiler in Hive?
  6. What is the role of the Executor in Hive?
  7. What is the role of the metastore DB?
  8. How does Hive translate queries into MapReduce jobs?
  9. What execution engines does Hive support besides MapReduce?
  10. Explain differences between ORC, Parquet, and Avro.
  11. Why is ORC preferred in Hive?
  12. How do partitions improve query performance in Hive?
  13. What happens internally when a partition is created?
  14. What is dynamic partitioning?
  15. What is the difference between dynamic and static partitioning?
  16. What is skewed data and how does Hive address it?
  17. Explain map-side joins in Hive.
  18. Explain reduce-side joins in Hive.
  19. What is a sort-merge join in Hive?
  20. What is a common cause for small files issue in Hive?
  21. How do you handle small files in Hive?
  22. Explain Tez execution engine and its benefits.
  23. What is vectorization in Hive?
  24. What is ACID support in Hive?
  25. What are transactional tables in Hive?
  26. Explain the difference between INSERT OVERWRITE and INSERT INTO.
  27. What is a Hive UDTF?
  28. What is a Hive UDAF?
  29. How are custom UDFs written in Java?
  30. What is the significance of bucketing in joins?
  31. What is the Hive query compiler optimization pipeline?
  32. What is a materialized view in Hive?
  33. Explain how cost-based optimization works in Hive.
  34. What causes Hive queries to fail on schema mismatch?
  35. What is the difference between SHOW CREATE TABLE and DESCRIBE FORMATTED?
  36. What are Hive built-in aggregate functions?
  37. How does Hive handle schema evolution?
  38. What is predicate pushdown in Hive?
  39. What is index-based query optimization in Hive?
  40. How does Hive support columnar compression?

Hive – Experienced (1–40)

  1. Explain Hive execution plan in depth.
  2. How do you debug slow-running Hive queries?
  3. How do you profile Hive queries using EXPLAIN ANALYZE?
  4. Explain file-level vs table-level vs partition-level statistics.
  5. How does ORC pushdown pruning work internally?
  6. What are advanced ORC optimizations?
  7. How do you tune Hive for optimal performance?
  8. What are the major performance bottlenecks in Hive?
  9. How do you optimize shuffle operations in Hive?
  10. What is the difference between MapJoin, SMB Join, and Bucket MapJoin?
  11. When does Hive automatically convert joins to map-side joins?
  12. How do you detect and fix data skew in Hive?
  13. What is the impact of too many partitions?
  14. How do you repair partitions in Hive?
  15. Explain the role of HiveServer2 in enterprise architecture.
  16. How do you secure Hive in a production environment?
  17. How does Kerberos authentication work with Hive?
  18. What are Ranger policies for Hive and why are they needed?
  19. How do you implement row-level and column-level security in Hive?
  20. Explain LLAP architecture in detail.
  21. What problem does LLAP solve that Tez cannot?
  22. How does Hive caching work in LLAP?
  23. How do you tune Tez memory parameters for large queries?
  24. Explain the internal mechanics of Hive compaction.
  25. What kinds of compactions exist in Hive and when are they triggered?
  26. What are limitations of Hive ACID tables?
  27. Explain streaming ingest into Hive ACID tables.
  28. How do you handle schema evolution in ORC with backward compatibility?
  29. What is materialized view rewriting in Hive?
  30. How do you design enterprise-grade data models in Hive?
  31. What are the best practices for partition design at scale?
  32. How do you implement late arriving data handling?
  33. What is the impact of SerDe choice on performance?
  34. How do you manage Hive metadata growth in the metastore?
  35. What is the impact of HDFS block size on Hive performance?
  36. Explain how to use bloom filters in ORC.
  37. What is HMS High Availability and how is it configured?
  38. What are issues caused by large IN clause queries and how do you fix them?
  39. How do you integrate Hive with Kafka for streaming?
  40. Explain differences between Presto, Hive, and Spark SQL under the hood.

Hive Interview Questions and Answers

Beginner (Q&A)

1. What is Apache Hive?

Apache Hive is a data warehousing and SQL-like query system built on top of Hadoop. It was created at Facebook to simplify the process of analyzing massive datasets stored in HDFS without requiring users to write complex MapReduce programs. Hive enables users to write queries in HiveQL (HQL), which closely resembles SQL. Internally, Hive converts these HiveQL statements into MapReduce, Tez, or Spark jobs depending on the execution engine.

Hive follows a schema-on-read approach, meaning the data is validated against the schema only when it is read, not when it is loaded. This is ideal for big data environments where raw, semi-structured, or irregular data may be ingested continuously. Hive is primarily designed for batch processing, not real-time analytics. It handles large-scale summarization, querying, and analysis workloads that involve terabytes or petabytes of data.

2. How does Hive differ from a traditional RDBMS?

Hive differs from relational database management systems in several fundamental ways:

  1. Data Processing Model:
    Hive uses distributed computation engines like MapReduce, Tez, or Spark, whereas RDBMS runs on a single-node or limited cluster architecture optimized for transactions.
  2. Latency:
    RDBMS is designed for low-latency responses and real-time reads/writes. Hive is optimized for high-latency batch processing and complex analytics.
  3. Schema:
    RDBMS follows schema-on-write, enforcing strict schema rules during data insertions.
    Hive uses schema-on-read, allowing flexible ingestion of raw data and validating structure only at query time.
  4. Transaction Support:
    Traditional RDBMS fully supports ACID transactions.
    Hive supports ACID only for special transactional tables and is not optimized for OLTP.
  5. Storage:
    Hive stores data in HDFS, not in database files or proprietary storage engines.
  6. Query Language:
    RDBMS uses SQL.
    Hive uses SQL-like HiveQL but lacks certain SQL features like row-level updates (except in ACID tables).

In summary, Hive is built for big data analytics, not transactional systems, and cannot replace RDBMS but complements it in enterprise architectures.

3. What is Hive metastore?

The Hive metastore (HMS) is a central repository that stores the metadata for all Hive tables, databases, partitions, and their physical storage locations. It contains information such as:

  • table names
  • column names and data types
  • SerDe information
  • table location in HDFS
  • partition details
  • storage format and file type

The metastore uses an external RDBMS like MySQL, PostgreSQL, or MariaDB. It serves two main components:

  1. Metastore Service: A Thrift service that provides metadata access to clients like HiveServer2, Spark, and Presto.
  2. Metastore Database: Stores the actual metadata tables.

Without the metastore, Hive would not know where data is located or how to interpret it. It is the backbone of Hive’s schema management and supports interoperability across big data tools.

4. What is the default file format used in Hive?

The default file format in Hive is TEXTFILE. This means that when you create a table without specifying a file format, Hive assumes the underlying data files are plain text files, typically using delimited values (such as comma, tab, or custom delimiters).

TEXTFILE is simple and easy to use but has limitations:

  • No compression by default
  • Inefficient for large dataset analytics
  • Does not support advanced features like predicate pushdown

More optimized formats like ORC, Parquet, and Avro are preferred for performance-critical workloads. Still, TEXTFILE remains the default for quick and flexible ingestion.

5. What is the role of HiveQL?

HiveQL (Hive Query Language) is the SQL-like language used to interact with Hive. It simplifies big data processing by abstracting away the complexity of writing distributed processing programs. Key roles of HiveQL include:

  • Creating and managing databases and tables
  • Defining schemas (columns, types, SerDes)
  • Writing queries for filtering, aggregating, and joining data
  • Loading data into Hive tables
  • Managing partitions and buckets
  • Controlling table properties and storage formats
  • Expressing complex ETL transformations

HiveQL closely resembles SQL, making it easy for developers, analysts, and SQL learners to analyze large datasets without learning MapReduce or Tez programming.

6. What are managed and external tables in Hive?

Hive supports two types of tables: managed and external.

Managed Table

  • Hive fully controls the table data and metadata.
  • Data is stored under the default warehouse directory:
    /user/hive/warehouse/
  • When a managed table is dropped, both metadata and data are deleted.
  • Best used when Hive owns the lifecycle of data.

External Table

  • Hive only manages metadata; the data location is external to Hive.
  • Dropping the table removes metadata but preserves the data.
  • Useful for:
    • shared datasets accessed by multiple tools
    • log files
    • data stored in non-default HDFS locations

External tables give Hive flexibility to query existing HDFS data without moving or copying it.

7. What is a Hive partition?

A partition in Hive is a way to divide table data into logical segments based on the values of one or more columns. Each partition corresponds to a directory in HDFS, enabling:

  • Faster query performance
  • Reduced data scanning
  • Efficient management of large datasets

Example: Partitioning a sales table by year and month:
/sales/year=2024/month=01/

When a query filters on partition keys, Hive reads only the relevant directories instead of scanning the entire table. Partitioning is essential for optimizing large-scale analytics.

8. What is a Hive bucket?

Bucketing is a data organization technique where Hive distributes data into a fixed number of files, called buckets, based on the hash value of a column.

Example:
CLUSTERED BY (user_id) INTO 16 BUCKETS

Key benefits of bucketing:

  • Efficient sampling
  • Improved join performance (bucketed map joins)
  • Reduced shuffle operations during query execution

Bucketing is especially useful when combined with sorting, enabling Sort-Merge-Bucket (SMB) joins, which significantly improve join efficiency for very large tables.

9. What is a SerDe in Hive?

A SerDe (Serializer/Deserializer) is a component in Hive that defines how data should be parsed (deserialized) when reading and formatted (serialized) when writing.

SerDes allow Hive to work with:

  • Delimited text
  • JSON
  • Avro
  • Parquet
  • ORC
  • CSV
  • Custom formats

For example:

  • LazySimpleSerDe handles TEXTFILE
  • OrcSerde handles ORC format
  • ParquetHiveSerDe handles Parquet

SerDes give Hive the flexibility to manage complex and semi-structured data formats without modifying core systems.

10. What is the default data location of a Hive table?

By default, Hive stores managed table data in the warehouse directory:

/user/hive/warehouse/

Each table gets its own subdirectory:

/user/hive/warehouse/<table_name>/

External tables, however, store data wherever the user specifies using the LOCATION clause. This separation ensures that Hive can differentiate between data that it owns and external data it only references.

11. What is the importance of schema-on-read in Hive?

Schema-on-read is a core principle in Hive and big data systems. It means that data is not validated or structured during ingestion; instead, the schema is applied only when the data is queried. This approach provides several benefits:

  1. Flexibility for Raw Data
    It allows storing data in its raw, unprocessed form. This is essential in big data environments where data comes from multiple sources in varying formats.
  2. Faster Data Ingestion
    Since Hive doesn’t enforce schema at write time, ingestion is extremely fast, enabling users to load terabytes of logs or event data without transformation delays.
  3. Schema Evolution Support
    You can modify or extend the schema without rewriting existing data. New columns can be added, or types changed for future queries.
  4. Support for Semi-Structured Data
    Schema-on-read allows Hive to handle JSON, CSV, XML, and custom text formats easily by applying appropriate SerDes at read time.
  5. Cost Efficiency
    Computation (schema enforcement) happens at query time, distributing expensive operations across a cluster.

This approach makes Hive ideal for analytical workloads where data volume is massive and schema flexibility is required.

12. What happens when you drop a managed table?

A managed table in Hive means Hive owns and manages both the metadata and the data. When you drop a managed table:

  1. Metadata is deleted from the Hive metastore, removing all table definitions.
  2. Data is permanently deleted from the default warehouse directory (/user/hive/warehouse/).
  3. The corresponding HDFS folder for the table is also removed.

In other words, dropping a managed table results in complete and irreversible deletion of both data and metadata. Managed tables are ideal when Hive is supposed to control the full lifecycle of the data.

13. What happens when you drop an external table?

An external table in Hive means the data does not belong to Hive, and only the metadata is managed by Hive. When you drop an external table:

  1. Only metadata is deleted from the Hive metastore.
  2. The underlying data files are preserved in HDFS.
  3. The LOCATION directory remains untouched.

This design ensures that external tables are safe when multiple systems or tools share the same dataset. Dropping the external table will not remove the actual data.

14. What file formats does Hive support?

Hive supports a wide range of file formats to accommodate different data types and performance needs. These include:

Text-Based Formats

  • TEXTFILE (default)
  • CSV
  • JSON
  • XML (via custom SerDe)

Binary and Row-Based Formats

  • SEQUENCEFILE
  • AVRO

Columnar Formats (optimized for analytics)

  • ORC (Optimized Row Columnar)
  • Parquet

Custom Formats

Hive allows custom SerDes to support user-defined formats.

Each format has trade-offs:

  • ORC and Parquet support compression, predicate pushdown, and efficient column storage.
  • Text formats are simple but slow for large analytical workloads.

15. What are Hive primitive data types?

Hive primitive data types represent basic data elements such as numbers, strings, and boolean values. Key primitive types include:

  • Numeric Types
    • TINYINT
    • SMALLINT
    • INT
    • BIGINT
    • FLOAT
    • DOUBLE
    • DECIMAL
  • String Types
    • STRING
    • CHAR
    • VARCHAR
  • Date/Time Types
    • DATE
    • TIMESTAMP
    • INTERVAL
  • Miscellaneous Types
    • BOOLEAN
    • BINARY

Primitive types form the base for columns and are essential for defining Hive table schemas.

16. What are complex data types in Hive?

Complex data types allow Hive to store nested and hierarchical structures within a single column. Hive supports three main complex types:

  1. ARRAY
    Represents an ordered collection of elements of the same type.
    Example: ARRAY<string>
  2. MAP
    Represents a key-value pair collection.
    Example: MAP<string, int>
  3. STRUCT
    Represents a record with multiple fields, possibly of different types.
    Example: STRUCT<name: string, age: int>

Complex types enable Hive to model semi-structured data (like JSON or logs) and support modern analytics use cases.

17. What is the difference between ORDER BY and SORT BY?

Although both ORDER BY and SORT BY sort data, they serve very different purposes:

ORDER BY

  • Performs a global sort across the entire dataset.
  • Uses a single reducer, making it slow for large datasets.
  • Guarantees a fully sorted output.

Use case: Small datasets where full sorting is required.

SORT BY

  • Sorts data within each reducer, not globally.
  • Allows multiple reducers, enabling parallel sorting.
  • Output is partially sorted, not global.

Use case: Preliminary sorting before bucketing or distributed workloads.

In summary:

  • ORDER BY = global, slower, single reducer
  • SORT BY = partitioned sorting, faster, parallel reducers

18. What is the difference between SORT BY and DISTRIBUTE BY?

Both statements influence how data is divided among reducers:

SORT BY

  • Sorts rows within each reducer.
  • Does NOT control which reducer a row goes to.
  • Provides ordering but not guaranteed data distribution.

DISTRIBUTE BY

  • Controls how rows are distributed to reducers.
  • Ensures that rows with the same key go to the same reducer.
  • Does NOT guarantee order.

Used heavily in ETL pipelines for grouping similar data together before transformation.

Key difference:

  • SORT BY = ordering
  • DISTRIBUTE BY = grouping/destinating

You can combine them to get both distribution and ordering.

19. What is the difference between DISTRIBUTE BY and CLUSTER BY?

These commands are often confused because they look similar, but they have one major difference.

DISTRIBUTE BY

  • Distributes rows to reducers based on a key.
  • Does NOT sort the data within the reducer.

CLUSTER BY

  • A shortcut for DISTRIBUTE BY + SORT BY on the same column.
  • Ensures rows with the same key go to the same reducer and are sorted within that reducer.

Example:

CLUSTER BY user_id

is equivalent to:

DISTRIBUTE BY user_id
SORT BY user_id

Use case: Efficient preparation for bucketing or SMB joins.

20. What is a Hive view?

A Hive view is a logical, virtual table created from a query. It does not store data physically but stores only the query definition in the metastore.

Key properties:

  • Acts like a table for querying.
  • Reflects real-time data from underlying tables.
  • Does not support indexing or partitioning.
  • Helps in abstraction, simplification, and access control.

Example:

CREATE VIEW high_value_customers AS
SELECT name, total_spent
FROM sales
WHERE total_spent > 100000;

Views improve readability, reusability, and security by hiding complex queries or sensitive columns.

21. What is the purpose of the ANALYZE command in Hive?

The ANALYZE command in Hive is used to compute statistics for tables and partitions. These statistics help the Hive Cost-Based Optimizer (CBO) make smarter decisions to improve query performance.

The command collects information such as:

  • Number of rows
  • Number of files
  • Total data size
  • Column-level statistics (max, min, NDV, null count, histogram)

Example:

ANALYZE TABLE sales COMPUTE STATISTICS;
ANALYZE TABLE sales COMPUTE STATISTICS FOR COLUMNS;

Why it’s important:

  1. Better Query Optimization
    CBO uses statistics to choose the best query plan, such as which join strategy to use.
  2. Efficient Join Reordering
    Hive can automatically rearrange joins based on table size.
  3. Improved Predicate Pushdown
    More accurate data filtering improves query performance.
  4. Partition Pruning
    Helps Hive skip irrelevant partitions.

Without statistics, Hive may pick suboptimal plans, causing unnecessary full table scans or expensive joins. Thus, ANALYZE is essential for production workloads.

22. How does Hive handle NULL values?

Hive treats NULL values with specific rules:

  1. During Comparisons
    • NULL = NULL → returns NULL, not true
    • NULL != NULL → returns NULL
    • Any comparison with NULL results in NULL, except IS NULL or IS NOT NULL
  2. Sorting Behavior
    • In ascending order, NULLs appear first
    • In descending order, NULLs appear last
  3. Aggregate Functions
    • Aggregations like SUM, AVG, COUNT ignore NULLs
    • COUNT(*) counts NULLs, but COUNT(column) does not
  4. Joins
    NULL keys do not match any other key including another NULL.
  5. Loading Data
    Missing fields or empty strings may be interpreted as NULL depending on SerDe settings.

This behavior aligns with SQL standards and ensures predictable outcomes in analytical queries.

23. What is the purpose of the LIMIT clause in Hive?

The LIMIT clause restricts the number of rows returned by a Hive query. It is especially useful in:

  1. Testing Queries
    When developing queries on huge tables, LIMIT helps you preview small samples.
  2. Saving Resources
    It reduces memory consumption and network transfer by returning only required rows.
  3. Debugging and Data Validation
    Analysts can quickly validate patterns or data quality.

Example:

SELECT * FROM customers LIMIT 100;

Hive often executes LIMIT queries faster because it stops processing after retrieving the specified number of rows. This improves developer productivity and reduces cluster load.

24. What is LazySerDe in Hive?

LazySimpleSerDe—commonly referred to as LazySerDe—is the default SerDe used by Hive for TEXTFILE tables. It is designed to parse data only when needed.

Key characteristics:

  1. Lazy Deserialization
    Hive does not read and convert every column upfront.
    It only deserializes a column if the query requires it.
  2. Performance Optimization
    Improves performance for wide tables (many columns) where only a subset is queried.
  3. Flexible Parsing
    Supports customizable field delimiters, collection delimiters, and escape characters.
  4. Efficient for Big Data
    Reduces overhead when scanning large text datasets.

LazySerDe is simple, efficient, and ideal for log-style or raw text datasets.

25. What is the difference between TEXTFILE and SEQUENCEFILE?

TEXTFILE and SEQUENCEFILE are two file formats supported by Hive. They differ in several ways:

TEXTFILE

  • Plain text file (default format)
  • Human-readable
  • No compression (unless manually compressed)
  • Row-based format
  • Slow for large analytical workloads
  • Works with LazySerDe

SEQUENCEFILE

  • Binary file format
  • Key-value based
  • Supports block-level compression
  • Faster read/write than text files
  • Suitable for MapReduce intermediate output
  • Not human-readable

When to use what:

  • TEXTFILE → for raw data ingestion, semi-structured logs, easy debugging
  • SEQUENCEFILE → for better performance, compression, and internal processing

SEQUENCEFILE is more optimized but TEXTFILE remains useful for flexible ingestion and debugging.

26. How does Hive store metadata?

Hive stores metadata in a central component called the Hive Metastore, which consists of:

  1. Metastore Database
    Stores metadata tables such as:
    • Table definitions
    • Column types
    • Partition information
    • Storage formats
    • SerDe details
    • File locations
    The database can be MySQL, PostgreSQL, Derby, Oracle, etc.
  2. Metastore Service (HMS)
    A Thrift-based service allowing Hive and other engines (Spark, Presto, Impala) to retrieve metadata.

Example metadata stored:

table_name -> employee
location -> /user/hive/warehouse/employee
columns -> id:int, name:string
format -> ORC

Metadata is essential for Hive’s schema-on-read architecture and query compilation.

27. What is a Hive query execution engine?

The execution engine is the backend engine responsible for running Hive queries. Hive originally used MapReduce, but now supports multiple engines:

  1. MapReduce
    • Older, slower, but reliable
    • Batch processing
  2. Tez
    • DAG-based execution
    • Faster than MapReduce
    • Default execution engine in modern Hive
  3. Spark
    • In-memory execution
    • Very fast for iterative workloads

The execution engine performs tasks like:

  • Query parsing
  • Logical and physical plan generation
  • Task scheduling
  • Job submission
  • Data shuffling and execution

Users can switch engines using configuration settings, allowing Hive to adapt to various workloads.

28. What are UDFs in Hive?

UDFs (User-Defined Functions) allow users to extend Hive’s capabilities by defining custom logic not available in built-in functions.

Types of UDFs:

  1. Simple UDF
    Used for row-level transformations.
  2. UDAF (User-Defined Aggregate Function)
    Used for aggregations like SUM, AVG.
  3. UDTF (User-Defined Table-Generating Function)
    Expands a row into multiple rows (e.g., explode()).

Why use UDFs?

  • Perform complex operations
  • Implement domain-specific logic
  • Support custom parsing
  • Extend Hive functionality

UDFs are written in Java and then registered in Hive, enabling seamless integration into HiveQL queries.

29. What is the purpose of the CONCAT function in Hive?

The CONCAT function in Hive is used to merge two or more strings into a single string. It is widely used in ETL tasks, formatting, and generating unique keys.

Example:

SELECT CONCAT(first_name, ' ', last_name) FROM employees;

Key behaviors:

  1. Accepts multiple string arguments
  2. Returns NULL if any argument is NULL
  3. Useful for creating composite keys, tags, formatted text

It is a simple but essential function for text manipulation in Hive.

30. What is a Hive CLI?

Hive CLI (Command Line Interface) was the original interface for interacting with Hive before HiveServer2 and Beeline were introduced.

Features:

  • Direct command-line access to Hive
  • Supports running HiveQL queries
  • Simple for development and testing
  • Does not support multi-user concurrency well
  • Less secure compared to HiveServer2

Example usage:

hive
hive> SELECT * FROM customers LIMIT 10;

Note: Hive CLI is now deprecated in favor of Beeline and HiveServer2.

31. What is the role of Beeline in Hive?

Beeline is a JDBC-based command-line interface used to interact with HiveServer2. It was introduced as a modern, secure, and improved replacement for the older Hive CLI.

Key roles and features of Beeline:

  1. Connects to HiveServer2
    Beeline uses JDBC to connect remotely to HiveServer2, supporting secure multi-user environments.
  2. Improved Security
    Beeline supports:
    • Kerberos authentication
    • LDAP
    • Custom authentication mechanisms
    This makes it suitable for enterprise deployments.
  3. Better Performance and Stability
    It provides:
    • Clear session management
    • Error handling
    • Better control over query execution
  4. Client/Server Architecture
    Unlike Hive CLI, Beeline does not run execution locally.
    Instead, it sends commands to HiveServer2 and receives results.
  5. Lightweight and Portable
    Beeline does not require running a full Hive environment locally.
    Only a JDBC connection URL is needed.

In summary, Beeline is the preferred interface in modern Hive deployments for reliability, security, and scalability.

32. What is the difference between Hive CLI and Beeline?

Although both Hive CLI and Beeline execute Hive queries, they differ in architecture, security, and usage.

Hive CLI

  • Legacy, now deprecated
  • Connects directly to Hive services using Thrift
  • Runs locally on the same machine as the metastore
  • Less secure, lacks multi-user support
  • Does not use HiveServer2
  • Suitable mainly for standalone or development use

Beeline

  • Modern, recommended interface
  • Uses JDBC to connect to HiveServer2 remotely
  • Highly secure (Kerberos, LDAP)
  • Multi-user, scalable
  • Works well in distributed/production environments

Major difference:
Hive CLI = local, non-secure, single-user
Beeline = remote, secure, multi-user

33. What is the purpose of the SHOW TABLES command?

The SHOW TABLES command is used to list all tables within a specific Hive database.

Example:

SHOW TABLES;
SHOW TABLES LIKE 'sales*';

Its purposes include:

  1. Metadata Exploration
    Allows users to see which tables exist in the current database.
  2. Wildcards for Pattern Matching
    Helps filter tables when dealing with large numbers of tables.
  3. Navigation
    Essential when switching between databases and exploring their structure.
  4. Quick Validation
    Helps confirm creation or deletion of tables.

SHOW TABLES is a simple yet essential command for interacting with Hive databases.

34. What does DESCRIBE FORMATTED show?

DESCRIBE FORMATTED provides a detailed, structured, and extensive description of a Hive table or partition.

Example:

DESCRIBE FORMATTED employees;

It shows:

  1. Column details
    • Column names
    • Data types
    • Comments
  2. Table metadata
    • Location in HDFS
    • SerDe information
    • Input/output format
    • File format (e.g., ORC, Parquet)
  3. Storage information
    • Number of files
    • Total data size
  4. Table properties
    • Bucketing info
    • Partitioning
    • Owner and permissions
  5. Statistics
    • Row count
    • Column-level stats (if ANALYZE run)

It is the most comprehensive command for understanding table structure and storage.

35. What is a Hive metastore service?

The Hive Metastore Service (HMS) is a dedicated service that stores and manages all metadata required by Hive. It acts as the metadata backbone for Hive and other query engines.

Key roles:

  1. Metadata Storage
    Stores table definitions, schemas, column types, file locations, partition info, SerDes, and more.
  2. Metadata Access via Thrift
    Exposes a Thrift server so HiveServer2, Spark, Presto, Impala, and other clients can access metadata.
  3. Central Metadata Repository
    Ensures consistency across tools that query Hive tables.
  4. High Availability
    Can be deployed in HA mode for enterprise reliability.

Metastore service separates metadata management from query execution, allowing scalability and multi-engine interoperability.

36. What is the default mode of Hive execution?

The default execution mode of Hive has evolved over versions:

Older Versions

  • Default engine: MapReduce

Modern Hive (Hive 2.x and 3.x)

  • Default engine: Apache Tez

Tez is significantly faster than MapReduce because:

  • It uses Directed Acyclic Graphs (DAGs)
  • Reduces disk I/O
  • Optimizes shuffle phases
  • Enables query pipelining

If no configuration is changed, Hive will automatically run queries using Tez.

Spark engine can also be used if configured, but it is not the default.

37. What is the significance of LOCAL vs DFS paths in Hive?

Hive supports loading data from two types of paths:

LOCAL Path

Example:

LOAD DATA LOCAL INPATH '/home/user/data.txt' INTO TABLE employees;

Meaning:

  • The file resides on the local filesystem of the machine running the Hive command.
  • Hive copies data from local machine → HDFS table directory.

Use cases:

  • Small datasets
  • Test datasets
  • Developer environments

DFS Path

Example:

LOAD DATA INPATH '/user/data/sales.txt' INTO TABLE sales;

Meaning:

  • The file already resides in HDFS.
  • Hive simply moves the file to the table directory (for managed tables).

Use case:

  • Production-scale datasets
  • Distributed ingestion

Key Significance:

  • LOCAL → reads from local machine
  • DFS → reads from HDFS
  • LOCAL copies data; DFS moves or links data
  • Choosing the wrong one can impact performance or availability

38. What will happen if data fields exceed the defined column count in Hive?

When reading data, if a row in a file contains more fields than the number of defined columns, Hive behaves as follows:

  • The extra fields are ignored.
  • Only the first N fields (where N = number of columns) are mapped.
  • No errors are thrown; Hive silently truncates excess data.

Example:

Table definition:

(id INT, name STRING)

Data:

1,John,Manager

Hive output:

1    John

“Manager” is discarded.

This behavior results from Hive’s schema-on-read model. However, it may lead to unnoticed data loss unless the file is validated.

39. What is the use of the MAP data type in Hive?

The MAP type allows Hive to store key-value pairs within a single column.

Example:

MAP<STRING, INT>

Uses of MAP:

  1. Representing JSON-like data
    Logs or event data often contain key-value structures.
  2. Storing Dynamic Attributes
    When each row has variable attributes (e.g., product specs).
  3. Simplifying ETL
    Allows storing semi-structured data without normalization.
  4. Efficient Lookups
    MAP values can be accessed using keys:
mymap['age']
  1. Capturing Metadata
    For storing additional row-level properties like tags, labels, or scores.

MAP is essential for Hive’s ability to handle semi-structured and evolving datasets.

40. What are Hive temporary tables?

Hive temporary tables are session-level tables that:

  • Exist only during the user’s session
  • Are automatically dropped at session end
  • Store data in the Hive scratch directory
  • Do not appear in the Hive metastore
  • Are not accessible to other users or sessions

Example:

CREATE TEMPORARY TABLE temp_sales AS 
SELECT * FROM sales WHERE amount > 10000;

Use cases:

  1. Intermediate storage during ETL
  2. Testing transformations
  3. Improving performance by breaking down large queries
  4. Avoiding metadata pollution
    (No permanent table entries created)

Temporary tables make Hive more flexible and help optimize complex workflows.

Intermediate (Q&A)

1. Explain Hive architecture in detail.

Hive architecture is designed to process massive datasets using SQL-like queries while leveraging Hadoop’s distributed storage and computation capabilities. The architecture consists of several major components:

a. User Interfaces

Users interact with Hive using:

  • Beeline (JDBC-based CLI)
  • HiveServer2 clients
  • JDBC/ODBC connections
  • Hue or other UI tools

These interfaces send HiveQL queries to the Hive server.

b. HiveServer2

HiveServer2 (HS2) is the service layer receiving queries from clients. It handles:

  • Session management
  • Authentication
  • Thrift/REST communication
  • Multi-user concurrency

HS2 forwards queries to the Driver.

c. Driver

The Driver is responsible for:

  • Receiving queries
  • Managing sessions
  • Orchestrating the full execution flow

It communicates with the Compiler to generate an execution plan.

d. Compiler

The Compiler transforms HiveQL into a Directed Acyclic Graph (DAG) of tasks by performing:

  • Query parsing
  • Semantic analysis
  • Type checking
  • Query optimization (CBO)
  • Physical plan generation

During analysis, the Compiler retrieves metadata from the Metastore.

e. Metastore

The Hive Metastore (HMS) stores metadata about:

  • Tables
  • Columns
  • Partitions
  • SerDe info
  • File locations

The Compiler and Driver use this metadata to create execution plans.

f. Execution Engine

The physical plan is executed using:

  • Tez (default modern engine)
  • MapReduce (legacy)
  • Spark (optional)

The engine runs the tasks (map, reduce, DAG stages) on a Hadoop/YARN cluster.

g. Hadoop Storage Layer

Hive stores data in:

  • HDFS
  • S3 (in cloud deployments)
  • Azure ADLS

The output of tasks is written back to the warehouse directory /user/hive/warehouse.

In summary:
Hive converts SQL queries into distributed jobs executed across a Hadoop cluster using metadata-driven architecture.

2. How does Hive interact with Hadoop?

Hive is closely integrated with Hadoop and uses its components for storing and processing data.

a. Hadoop HDFS (Storage Layer)

  • Hive stores all table data on HDFS (or cloud storage).
  • Managed tables are stored under Hive’s warehouse directory.
  • External tables point to arbitrary HDFS paths.

b. Hadoop YARN (Resource Management)

Hive does not run computational tasks directly. Instead:

  • It submits jobs to YARN.
  • YARN allocates cluster resources.
  • NodeManagers run containers to execute Hive tasks.

c. Hadoop MapReduce / Tez / Spark (Execution Layer)

Hive uses Hadoop’s execution engines:

  • MapReduce: original execution backend
  • Tez: modern DAG-based engine
  • Spark: for in-memory processing

HiveQL → DAG tasks → submitted to Hadoop processing engines via YARN.

d. Hadoop Input/Output Formats

Hive uses Hadoop InputFormat and OutputFormat classes to read and write data blocks.

3. Explain the full query lifecycle inside Hive.

The complete query lifecycle in Hive consists of 7 major steps:

1. Submit Query

A user sends a HiveQL command via Beeline or JDBC.

2. Driver Initiates Processing

The Driver:

  • Receives the query
  • Manages the session
  • Sends query to the Compiler

3. Parsing

The Compiler:

  • Parses HiveQL into an Abstract Syntax Tree (AST)
  • Validates syntax

4. Semantic Analysis

Compiler performs:

  • Column/field validation
  • Type checking
  • Table existence checks
  • Partition and bucket inspection

It queries the Metastore for metadata.

5. Optimization

Query optimizations include:

  • Predicate pushdown
  • Join reordering
  • Compression options
  • Vectorization
  • Cost-based optimizations (CBO)

6. Physical Plan Generation

Compiler builds an execution DAG:

  • Map tasks
  • Reduce tasks
  • Tez DAG tasks
  • Spark job stages

7. Execution

Execution engine (Tez/MapReduce/Spark):

  • Submits tasks to YARN
  • Executes DAG stages in parallel
  • Reads/writes data from/to HDFS

8. Result Handling

The Driver:

  • Collects results
  • Sends them back to the client

This entire lifecycle transforms SQL queries into distributed Hadoop jobs.

4. What is the role of the Driver in Hive?

The Driver acts as the controller and coordinator of the entire query execution process. Its responsibilities include:

a. Query Management

  • Accepts the query submitted by the user
  • Manages session and state

b. Workflow Control

  • Sends query to Compiler
  • Receives execution plan
  • Submits tasks to Execution Engine

c. Orchestration

  • Maintains the lifecycle of a query
  • Tracks progress of tasks
  • Monitors failures and retries

d. Result Management

  • Collects output from execution engine
  • Sends results back to the user/client

e. Session Management

  • Maintains user sessions
  • Handles metadata caching
  • Tracks temporary tables
  • Manages configuration options

In short, the Driver is the brain that coordinates query parsing, planning, execution, and result delivery.

5. What is the role of the Compiler in Hive?

The Compiler is responsible for translating HiveQL into an optimized execution plan.

Its major functions include:

1. Parsing

  • Converts HiveQL into an Abstract Syntax Tree (AST).

2. Semantic Analysis

  • Validates column names, table names.
  • Checks data types and function calls.
  • Resolves metadata from the metastore.

3. Logical Plan Generation

  • Builds a logical DAG based on relational algebra.
  • Defines how operations should work at a high level.

4. Optimization

  • Join reordering
  • Predicate pushdown
  • Filter merging
  • Partition pruning
  • Selecting the best join strategy (map-side vs reduce-side)

5. Physical Plan Creation

  • Splits tasks into map and reduce stages
  • Defines Tez DAG or Spark stages
  • Assigns operator pipelines

The Compiler is crucial because it ensures Hive generates efficient distributed jobs for analytical workloads.

6. What is the role of the Executor in Hive?

The Executor is the component that manages the actual execution of the tasks defined by the Compiler.

Its responsibilities include:

a. Task Submission

  • Submits tasks to YARN via the execution engine (Tez/MapReduce/Spark).

b. Monitoring

  • Tracks progress of tasks
  • Monitors container allocation
  • Handles failures and retries

c. Execution Pipeline

  • Coordinates multiple stages in DAGs
  • Handles shuffle phases
  • Ensures correct data flow between tasks

d. Resource Management

  • Ensures tasks get required memory and CPU
  • Works with YARN’s resource manager

e. Final Result Delivery

  • Returns output to Driver or writes to HDFS

In summary, the Executor acts as the runtime engine that runs the actual work in the cluster.

7. What is the role of the metastore DB?

The Metastore DB is the metadata repository used by Hive to store all structural information about databases and tables.

It stores:

  • Database names
  • Table names
  • Column names and data types
  • Table and partition locations
  • Storage formats (ORC, Parquet, etc.)
  • SerDe information
  • Table properties
  • Statistics (row count, file size)
  • Partition information

Roles:

  1. Metadata Lookup
    Queries depend on accurate table/column definitions.
  2. Query Optimization
    CBO uses statistics stored in metastore.
  3. Interoperability
    Spark, Presto, and Impala also read Hive metastore.
  4. Schema Management
    Ensures consistency across all tools.

Without the metastore, Hive cannot function, because it wouldn’t know where data is stored or how to interpret it.

8. How does Hive translate queries into MapReduce jobs?

Although newer engines like Tez and Spark are common, Hive originally translated HiveQL queries into MapReduce jobs as follows:

1. Query is parsed into an AST

Compiler parses HiveQL.

2. Logical Plan is generated

Operators like filter, join, group-by are created.

3. Plan is converted to MapReduce tasks

Compiler determines:

  • Which operations run in the map phase
  • Which need reduce stages (like joins or aggregations)

4. DAG of tasks is created

Sequence of MR jobs:

  • Map-only jobs
  • Map + Reduce jobs
  • Multi-stage reducements

5. Plan submitted to YARN

Execution engine launches the MR jobs.

6. Results are collected

Reducer output written to HDFS.

Key example:
A simple SELECT query may produce a single map-only job, while a JOIN might produce multiple MapReduce stages.

9. What execution engines does Hive support besides MapReduce?

Modern Hive supports several execution engines to improve performance:

1. Apache Tez

  • Default in Hive 2.x and 3.x
  • DAG-based execution
  • Much faster than MapReduce
  • Reduces disk I/O
  • Enables pipelined execution

2. Apache Spark

  • Optional execution engine
  • In-memory processing
  • Ideal for iterative jobs

3. Presto / Impala (via Hive metastore)

Though not execution engines within Hive, they use the Hive metastore to run SQL queries faster.

4. LLAP (Low Latency Analytical Processing)

  • Enhances Tez with caching and long-running daemons
  • Very fast interactive queries

So beyond MapReduce, Hive mainly uses:

  • Tez (modern default)
  • Spark (optional)
  • LLAP (accelerator)

10. Explain differences between ORC, Parquet, and Avro.

These are popular storage formats used in big data systems.

1. ORC (Optimized Row Columnar)

  • Created by Hortonworks for Hive
  • Highly optimized for Hive workloads
  • Provides:
    • Predicate pushdown
    • Inbuilt indexes
    • Lightweight compression
    • Fast reads and writes
    • Vectorization support

Best Use:
Hive analytical workloads with heavy scanning.

2. Parquet

  • Developed by Twitter + Cloudera
  • Widely used across Spark, Hive, Presto
  • Columnar format with:
    • Nested data support
    • Efficient compression
    • Predicate pushdown

Best Use:
Cross-platform big data processing.

3. Avro

  • Row-based format
  • Excellent for data serialization
  • Ideal for streaming pipelines
  • Schema stored with data
  • Supports schema evolution

Best Use:
Kafka ingestion, row-based storage, ETL.

Key Differences

FeatureORCParquetAvroStorage TypeColumnarColumnarRow-basedBest ForHiveMulti-platformStreamingSchema EvolutionGoodGoodExcellentPredicate PushdownExcellentExcellentLimitedCompressionHighHighMediumRandom AccessVery fastFastSlow

11. Why is ORC preferred in Hive?

ORC (Optimized Row Columnar) is the most preferred storage format for Hive because it is specifically designed to maximize the performance of Hive’s execution engines like Tez and LLAP.

Key reasons ORC is preferred:

  1. Highly Efficient Compression
    • ORC reduces storage size by 70–90% through built-in compression.
    • Supports lightweight compression codecs like Zlib, Snappy, and LZO.
    • Smaller data size → faster reads → lower HDFS I/O.
  2. Columnar Storage
    • Only the needed columns are read during queries.
    • Avoids scanning entire rows when only a few columns are required.
  3. Predicate Pushdown
    • ORC stores min/max value stats for each stripe/row group.
    • Hive automatically skips reading irrelevant stripes.
    • Massive performance boost for filter-heavy queries.
  4. Indexing and Metadata
    ORC files include:
    • Row group indexes
    • Bloom filters
    • Statistics
    This helps Hive avoid scanning unnecessary data blocks.
  5. Vectorized Query Execution
    • ORC is built for vectorization.
    • Processes batches of 1024 rows at once.
    • Reduces CPU instructions and increases throughput.
  6. ACID Transaction Support
    • ORC is the only format fully optimized for Hive ACID tables.

Result:
ORC is typically 3–5X faster than TEXTFILE, AVRO, or even PARQUET for Hive workloads.

12. How do partitions improve query performance in Hive?

Partitions divide a Hive table into logical subdirectories based on column values. This improves performance in several ways:

1. Reduces Data Scanned

A query on a partitioned table only scans the relevant partitions.

Example:

SELECT * FROM sales WHERE year = 2024;

Hive scans only:

/sales/year=2024/

Instead of scanning the entire table.

2. Enables Partition Pruning

Hive automatically eliminates partitions that do not match query predicates.

3. Faster Query Execution

By skipping unnecessary data, partitioning dramatically reduces:

  • Disk I/O
  • CPU usage
  • Network shuffle

Result → quicker execution times, especially for large tables.

4. Efficient Data Organization

Partitions help in:

  • Incremental data loading
  • Time-series analysis
  • Multi-level partitioning (year, month, day)

5. Parallel Processing

Each partition can be processed by separate mappers in parallel.

Conclusion:
Partitioning is essential for improving performance in big datasets where column-based filtering is common.

13. What happens internally when a partition is created?

When you create a partition in Hive, the following internal steps occur:

1. A Directory is Created in HDFS

For a partition:

PARTITION(year=2024, month=02)

Hive creates:

/warehouse/sales/year=2024/month=02/

2. Metadata Entry is Added to Metastore

Hive metastore stores:

  • Partition key
  • Location
  • File format
  • SerDe info
  • Stats

This metadata is critical for partition pruning.

3. Data Load is Mapped to the Directory

When loading data into a partition:

LOAD DATA INPATH ... INTO TABLE sales PARTITION(year=2024, month=02);

Hive moves the data file(s) into the corresponding partition directory.

4. Partition is Used by the Query Planner

During compilation:

  • Hive checks metastore for partition keys
  • Unrelated partitions are skipped

5. Optimizer Uses Partition-level Stats

If ANALYZE is run, stats help CBO plan optimal joins and aggregations.

In short:
Partition creation is both an HDFS operation (directories) and a metadata operation (metastore entries).

14. What is dynamic partitioning?

Dynamic partitioning allows Hive to automatically determine the partition values from the data itself during INSERT operations.

Example:

INSERT INTO TABLE sales PARTITION(year, month)
SELECT item, amount, year, month FROM raw_sales;

Hive dynamically creates directories like:

year=2023/month=01  
year=2023/month=02  
year=2024/month=01  
...

Benefits:

  • Eliminates manual creation of hundreds of partitions
  • Supports ETL pipelines that load data with varying date ranges
  • Automatically handles new partition values

Common Configuration Required:

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

Dynamic partitioning is especially useful in ingestion from raw logs or time-series data.

15. What is the difference between dynamic and static partitioning?

Both static and dynamic partitioning are used to load data into partitioned tables in Hive.

Static Partitioning

  • Partition key values are explicitly provided.
    Example:
INSERT INTO TABLE sales PARTITION(year=2024, month=01)
SELECT * FROM raw_sales;

Characteristics:

  • Hive loads all data into a single partition.
  • Faster and simpler.
  • Requires known partition values in advance.

Dynamic Partitioning

  • Partition values are determined from the data.
    Example:
INSERT INTO TABLE sales PARTITION(year, month)
SELECT item, amount, year, month FROM raw_sales;

Characteristics:

  • Automatically creates multiple partitions.
  • Useful when loading data with many different partition values.
  • Slightly slower due to overhead of creating directories.

Key Differences Table

FeatureStatic PartitioningDynamic PartitioningPartition valuesFixed in queryDerived from dataUse caseKnown partitionsUnknown or many partitionsPerformanceFasterSlowerFlexibilityLowHigh

16. What is skewed data and how does Hive address it?

Skewed data occurs when certain values are disproportionately frequent in a dataset, causing uneven distribution during joins, aggregations, or reduce tasks.

Example:
If 80% of data has country='US', then reducers processing US receive more data and become bottlenecks.

Problems caused by skew:

  • Reducer hotspots
  • Long-running queries
  • Out-of-memory errors
  • Excessive shuffle volume

How Hive addresses skewed data:

1. Skewed Join Optimization

Hive can detect skew and split skewed keys to process them separately:

set hive.optimize.skewjoin=true;
  • Skewed keys are processed in a separate map-only job
  • Non-skewed keys go through normal reduce-side join
  • Balances load

2. Salting Technique

Add random prefixes to distribute skewed keys:

CONCAT(user_id, '_', RAND())

3. Map-Side Aggregation

Reduces load on reducers by aggregating early.

4. Bucketing

Distributes data more evenly using hash keys.

5. Using ORC Statistics

ORC helps optimizer detect skew by checking row group stats.

Skew handling is crucial for large-scale performance optimization.

17. Explain map-side joins in Hive.

A map-side join is a highly efficient join strategy where the entire joining process happens in the map phase without involving reducers.

How it works:

  • One table (usually smaller) is loaded into memory (distributed cache).
  • The larger table is streamed and matched with the in-memory copy.
  • No shuffle, no reduce phase → much faster.

Requirements:

  1. One table must be small enough to fit into memory.
  2. Enable mapjoin:
set hive.auto.convert.join=true;

Advantages:

  • Avoids expensive shuffle and sort stages.
  • Significantly faster for star-schema queries.
  • Ideal for dimension-table lookups.

Limitations:

  • Not suitable for large tables.
  • Requires available memory in mappers.

Map-side joins are the fastest join method in Hive when applicable.

18. Explain reduce-side joins in Hive.

A reduce-side join is the default join mechanism when joining large tables.

How it works:

  1. Both tables are read by mappers.
  2. Rows are shuffled to reducers based on join key hash values.
  3. Reducers perform the join operation.
  4. Final merged rows are produced.

Characteristics:

  • Handles large datasets that cannot fit into memory.
  • Involves full shuffle and sort processes.
  • Can be slow because reducers must gather data from all mappers.

Advantages:

  • Supports all join types (inner, outer, full).
  • Works with unpartitioned or unsorted data.
  • No size limitation like map-side joins.

Disadvantages:

  • Very expensive for large tables.
  • Can create data skew.
  • Heavy network I/O.

Reduce-side joins should be avoided unless necessary.

19. What is a sort-merge join in Hive?

A sort-merge join (SMB Join) is an optimized join mechanism where tables are:

  1. Bucketed on the join column
  2. Sorted within each bucket

Hive can perform the join by matching buckets without shuffling data.

How it works:

  • Tables have identical number of buckets.
  • Buckets are sorted by the join key.
  • Hive reads corresponding buckets and performs merge join.

Advantages:

  • Very fast joins (no shuffle).
  • Suitable for large tables.
  • Efficient for repeated ETL pipelines.

Requirements:

CLUSTERED BY (key) INTO N BUCKETS;
SORT BY key;

Limitations:

  • Requires pre-bucketing and sorting.
  • Bucketing must match across tables.

SMB joins are ideal for large, structured datasets used in enterprise data warehouses.

20. What is a common cause for small files issue in Hive?

Small files are a serious issue in Hadoop because HDFS is optimized for large files (128MB+). Small files cause:

  • Excessive NameNode memory usage
  • Too many map tasks
  • Inefficient processing
  • Slow query performance

Common causes of small files in Hive:

  1. Frequent inserts or small ETL batch loads
    Each INSERT creates multiple small files.
  2. Dynamic partitioning without bucketing
    Each partition creates tiny files.
  3. Streaming data ingestion
    Log files generated per second/minute are small.
  4. Using TEXTFILE format
    Raw logs often come as multiple tiny files.
  5. Using many reducers with small output sizes
    Too many reduce tasks produce multiple small output files.
  6. Lack of file merging or compaction
    Not running:
ALTER TABLE ... CONCATENATE;
  1. or compaction for ACID tables.

Small file issues degrade performance significantly and must be managed with proper ingestion design, compaction, and file format choices like ORC and Parquet.

21. How do you handle small files in Hive?

Small files are a major performance bottleneck in Hive because Hadoop and HDFS are optimized for large block-sized files (typically 128MB or 256MB). When too many small files exist, Hive jobs create excessive map tasks, increase NameNode memory usage, and slow down queries.

Techniques to Handle Small Files:

1. Use File Formats That Combine Data Automatically

Formats like ORC and Parquet merge small files at write time:

  • ORC stripes combine many rows into larger blocks.
  • Parquet does similar columnar block consolidation.

This significantly reduces file fragmentation.

2. Use Hive Compaction (for ACID tables)

For transactional tables:

ALTER TABLE table_name COMPACT 'major';

Hive compaction merges small files generated by inserts/updates.

3. Use INSERT OVERWRITE to Rewrite Larger Files

Rewriting table data consolidates small files:

INSERT OVERWRITE TABLE big_table SELECT * FROM big_table;

4. Use CONCATENATE

For non-ACID ORC tables:

ALTER TABLE sales CONCATENATE;

This merges small ORC files into fewer, larger ones.

5. Optimize Dynamic Partitioning

Small files often occur when using dynamic partitions.
Reduce the number of partitions created by:

  • Avoiding over-partitioning (e.g., partitioning by hour instead of second).
  • Using bucketing to distribute load evenly.

6. Use CombineHiveInputFormat

This combines small files at read time, reducing map tasks:

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

7. Use Larger ETL Batch Sizes

Instead of loading tiny amounts of data frequently, load larger batches.

8. Tune Reducer Settings

Avoid creating too many reducers which produce small output files:

set hive.exec.reducers.bytes.per.reducer=512000000;

22. Explain Tez execution engine and its benefits.

Apache Tez is a modern, DAG-based execution engine designed for high-performance analytical processing on Hadoop. Hive uses Tez as its default execution engine starting from Hive 2.x.

How Tez Works:

Tez replaces MapReduce’s rigid map → reduce pipeline with a flexible Directed Acyclic Graph (DAG) of tasks. Each node may represent map, reduce, or custom operators.

This removes:

  • Intermediate disk writes
  • Unnecessary MapReduce boundaries
  • Excessive shuffle operations

Benefits of Tez:

1. Faster Execution

Tez reduces disk I/O by:

  • Pipelining operations
  • Avoiding unnecessary materialization of intermediate data

It is 10–20x faster than MapReduce for typical Hive workloads.

2. DAG Optimization

Tez can:

  • Reorder operations
  • Combine multiple operations into a single stage
  • Avoid redundant stages

3. More Efficient Shuffle

Tez intelligently handles shuffle data:

  • Uses memory whenever possible
  • Reduces disk usage
  • Eliminates slowdowns from skewed data

4. Better Integration with Hive CBO

Tez works directly with the optimizer to:

  • Determine best join strategy
  • Prune unnecessary work
  • Improve partition and predicate pushdown

5. LLAP Support

Tez integrates with LLAP (Low Latency Analytical Processing) for:

  • In-memory caching
  • Faster lookups
  • Sub-second response times for queries

23. What is vectorization in Hive?

Vectorization is a performance optimization where Hive processes rows in batches instead of processing one row at a time.

Key Concepts:

1. Batch Processing

Instead of handling each row individually, Hive reads:

  • 1024 rows (default batch size)
    into a vectorized in-memory structure.

2. CPU Efficiency

Column-oriented operations (e.g., filters, arithmetic) run on large batches, enabling:

  • Fewer CPU instructions
  • Reduced overhead
  • Efficient CPU pipeline usage

3. ORC Format Compatibility

ORC files are optimized for vectorization due to:

  • Column-level encoding
  • Dictionary compression
  • Stripe-level indexes

This makes ORC + vectorization extremely fast.

4. Performance Gains

Vectorization can improve query speed by:

  • 2x–10x depending on CPU and data size.

5. Enabling Vectorization

set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled=true;

24. What is ACID support in Hive?

ACID stands for Atomicity, Consistency, Isolation, Durability. Hive adds ACID support to enable transactional operations, making it suitable for traditional data warehousing and slowly changing dimensions (SCDs).

ACID Operations Supported:

  • INSERT
  • UPDATE
  • DELETE
  • MERGE

Before Hive ACID, Hive only supported INSERT into tables—it couldn't modify existing rows.

How ACID Works Internally:

1. Delta Files

Writes, updates, and deletes generate delta files:

  • Base files (initial data)
  • Delta files (changes)

Compaction merges these to maintain efficiency.

2. Write-Ahead Logging

Ensures durability and failure recovery.

3. Snapshot Isolation

Queries see a consistent view of the data even during concurrent writes.

4. ORC Format

ACID tables must use ORC because it supports:

  • Row-level delete markers
  • Versioning

25. What are transactional tables in Hive?

Transactional tables are Hive tables with ACID properties enabled. These tables support row-level insert, update, and delete operations.

Key Characteristics:

  1. ACTIVATED VIA:
TBLPROPERTIES ('transactional'='true');
  1. Must use ORC + Bucketing
    This is required for row-level operations.
  2. Stores Delta Files
    Transactional tables store:
  • base_X files
  • delta_X files
  • delete_delta files
  1. Supports Full ACID
    Perfect for OLAP workloads, SCD Type 1 & 2, and business-sensitive data operations.

26. Explain the difference between INSERT OVERWRITE and INSERT INTO.

INSERT OVERWRITE

  • Replaces the existing data in a table or partition.
  • Deletes previous files and writes new ones.
  • Used for:
    • Recomputing data
    • Full refresh ETL

Example:

INSERT OVERWRITE TABLE sales SELECT * FROM staging_sales;

INSERT INTO

  • Appends new records to existing data.
  • Does not delete old files.
  • Useful for incremental loading.

Example:

INSERT INTO TABLE sales SELECT * FROM new_sales_data;

Key Differences:

FeatureINSERT OVERWRITEINSERT INTOBehaviorReplaces dataAppends dataUse caseFull reloadIncremental loadsRiskData loss if used incorrectlyNo data lossFile creationFewer large filesMay cause many small files

27. What is a Hive UDTF?

A UDTF (User-Defined Table-Generating Function) is a function that takes a single row as input and returns multiple rows as output.

Common Example:

SELECT explode(array_col) AS element FROM table;

explode() is a UDTF because it generates multiple rows from a single row.

Use Cases:

  • Expanding arrays, maps, and structs
  • Flattening nested data
  • Converting JSON arrays into multiple rows

Characteristics:

  • Must appear in the SELECT clause
  • Cannot be combined with other expressions directly
  • Produces a variable number of rows

28. What is a Hive UDAF?

A UDAF (User-Defined Aggregate Function) is used to perform custom aggregation across rows.

Examples of built-in UDAFs:

  • SUM
  • AVG
  • MIN
  • MAX
  • COUNT

Custom UDAFs Are Needed When:

  • Complex business rules require specialized aggregations
  • You need to aggregate nested structures or custom metrics

UDAF Phases:

  1. init()
  2. iterate()
  3. merge()
  4. terminate()

UDAFs are powerful when built-in aggregation functions are not enough.

29. How are custom UDFs written in Java?

Creating a custom UDF in Hive involves writing a Java class that extends specific Hive UDF classes depending on the type:

1. For Simple Row-Based UDF

Extend:

org.apache.hadoop.hive.ql.exec.UDF

Example structure:

public class MyUDF extends UDF {
    public Text evaluate(Text input) {
        // logic
    }
}

2. UDAF (aggregate)

Extend:

org.apache.hadoop.hive.ql.exec.UDAF

With:

  • Evaluator classes
  • Iteration methods

3. UDTF (table function)

Extend:

org.apache.hadoop.hive.ql.exec.UDTF

Implement:

  • process()
  • close()

Steps to Deploy:

  1. Compile Java code into a JAR.
  2. Add JAR to Hive:
ADD JAR myudf.jar;

Create function:

CREATE TEMPORARY FUNCTION myfunc AS 'com.example.MyUDF';

30. What is the significance of bucketing in joins?

Bucketing distributes data into fixed “buckets” (files) based on the hash of a column.

Example:

CLUSTERED BY (customer_id) INTO 16 BUCKETS;

Why Bucketing Helps Joins:

1. Reduces Data Shuffle

If two tables are bucketed on the same key with the same number of buckets:

  • Hive knows matching keys are in corresponding buckets.
  • Only matching buckets are compared.

This eliminates expensive shuffles in reduce-side joins.

2. Enables Bucket Map Joins

This allows Hive to convert reduce-side joins into faster map-side joins by reading corresponding bucket files directly.

3. Enables Sort-Merge Bucket (SMB) Joins

When sorted + bucketed tables are joined:

  • No reducer needed
  • No re-sort needed
  • Extremely fast merge joins

4. Better Data Distribution

Helps avoid skew since hashing distributes keys evenly.

5. Efficient Sampling

Bucketing allows deterministic sampling:

SELECT * FROM table TABLESAMPLE(BUCKET 1 OUT OF 16);

Conclusion:
Bucketing dramatically improves join performance and should always be used in star-schema and large table join scenarios.

31. What is the Hive query compiler optimization pipeline?

The Hive query compiler optimization pipeline is a multi-stage process that transforms HiveQL into an efficient execution plan. It involves layers of parsing, semantic analysis, optimization, and physical plan generation.

1. Parsing

  • HiveQL query is converted into an Abstract Syntax Tree (AST).
  • Syntax is validated.

2. Semantic Analysis

  • Resolves table names, columns, functions.
  • Checks data types and validates expressions.
  • Retrieves metadata from the metastore.
  • Builds a logical operator tree (SELECT, FILTER, JOIN operators).

3. Logical Optimization

The logical operator tree is optimized through:

  • Predicate pushdown
  • Constant folding (e.g., 2+3 → 5)
  • Projection pruning (select only necessary columns)
  • Partition pruning (skip irrelevant partitions)
  • Join reordering and simplification

This is where the Cost-Based Optimizer (CBO) comes into play to choose the optimal join strategy.

4. Physical Plan Generation

A physical plan or DAG (Tez/Spark/MapReduce tasks) is created:

  • Determines the number of map/reduce stages.
  • Applies hash joins, merge joins, map-side joins.
  • Selects the best data movement strategies (shuffle, broadcast).

5. Task Compilation

Breaks down DAG into:

  • Map tasks
  • Reduce tasks
  • Tez vertices
  • Spark stages

6. Task Optimization

  • Combines map tasks where possible.
  • Avoids unnecessary reduce phases.
  • Uses bucketing/sorting to reduce shuffle operations.

32. What is a materialized view in Hive?

A materialized view in Hive is a precomputed result set stored as a table-like object. Unlike regular views, materialized views store physical data.

Characteristics:

  • Acts like a cached version of a query.
  • Can significantly speed up analytical workloads.
  • Hive can automatically rewrite queries to use materialized views.

How it works:

If a query matches or is compatible with a materialized view, Hive replaces the query with the view’s data.

Benefits:

  1. Performance Boost
  2. ‍
    Expensive aggregations or joins are precomputed.
  3. Reduced Resource Consumption
    Less computation at runtime.
  4. Query Rewriting
    Hive’s optimizer automatically detects when a materialized view can satisfy a query.
  5. Incremental Refresh
    Hive supports incremental rebuild:
ALTER MATERIALIZED VIEW mv REFRESH;

Materialized views are extremely useful in OLAP environments where queries frequently reference aggregated or pre-joined data.

33. Explain how cost-based optimization works in Hive.

Cost-Based Optimization (CBO) helps Hive select the most efficient execution plan for a query based on statistical information.

Key Components of CBO:

1. Statistics

CBO requires:

  • Table size
  • Row count
  • Column statistics (min, max, NDV – number of distinct values)
  • Null count

Collected using:

ANALYZE TABLE tablename COMPUTE STATISTICS;
ANALYZE TABLE tablename COMPUTE STATISTICS FOR COLUMNS;

2. Choose the Best Join Strategy

CBO evaluates join options:

  • Map-side join
  • Shuffle join
  • Merge join
  • Bucket map join

It selects a strategy that minimizes:

  • Data scanned
  • Shuffle volume
  • Number of reducers

3. Predicate Pushdown

If column stats allow filters to eliminate data early, CBO pushes them down to the lowest level.

4. Join Reordering

Reorders multi-table joins:

  • Smaller tables joined first
  • Reduce shuffle size
  • Improve parallelism

5. Partition Pruning

CBO uses partition stats to skip entire partitions.

6. Cost Estimation

Hive computes:

  • CPU cost
  • I/O cost
  • Network cost
  • Memory usage

Then selects the lowest-cost plan.

34. What causes Hive queries to fail on schema mismatch?

Schema mismatch errors occur when table schema definitions do not match the actual data or when incompatible changes occur.

Common Reasons:

1. Incorrect SerDe Definitions

Wrong SerDe leads to:

  • Wrong field parsing
  • Type mismatches
  • NULL values everywhere

2. Column Count Mismatch

If data has more or fewer columns than schema:

  • Extra values get truncated
  • Missing values become NULL
  • Parser errors may occur in strict modes

3. Data Type Changes

Changing column types can cause issues:

  • STRING → INT conversion fails if values are non-numeric
  • DATE → STRING may cause incorrect parsing

4. Schema Evolution Issues

ORC and Parquet support evolution, but:

  • Dropping/reordering columns may break compatibility
  • Queries expecting old schema fail

5. Wrong Delimiters

TEXTFILE with wrong delimiter results in:

  • Columns shifted incorrectly
  • All data read as one field

6. Corrupted Data Files

Binary formats (ORC/Parquet) may fail if:

  • Split or truncated files
  • Improper compression blocks

35. What is the difference between SHOW CREATE TABLE and DESCRIBE FORMATTED?

SHOW CREATE TABLE

  • Returns the DDL statement used to create the table.
  • Shows:
    • Table structure
    • SerDe info
    • Table properties
    • File format
    • Partition info

Example:

SHOW CREATE TABLE sales;

Useful for:

  • Replicating table structure
  • Understanding original table design

DESCRIBE FORMATTED

  • Returns detailed metadata about the table.
  • Shows:
    • Column definitions
    • Storage properties
    • File location in HDFS
    • Statistics
    • Partition info
    • SerDe details
    • Owner & permissions

Example:

DESCRIBE FORMATTED sales;

Key Differences Table

FeatureSHOW CREATE TABLEDESCRIBE FORMATTEDPurposeShow DDLShow metadata & statsOutputSQL-likeHuman-friendly tableIncludesCreate statementColumn stats, size, locationUse caseReplicationDebugging, analysis

36. What are Hive built-in aggregate functions?

Hive includes a wide range of built-in aggregate functions that operate on groups of rows and return a single value.

Common Aggregate Functions:

1. Basic Aggregates

  • COUNT()
  • SUM()
  • AVG()
  • MIN()
  • MAX()

2. Statistical Functions

  • STDDEV_POP()
  • STDDEV_SAMP()
  • VAR_POP()
  • VAR_SAMP()

3. Collection Aggregates

  • COLLECT_LIST()
  • COLLECT_SET()

4. Conditional Aggregates

  • COUNT_IF()
  • SUM_IF()

5. Complex Aggregates

  • CORR()
  • COVAR_POP()
  • COVAR_SAMP()

6. Percentile Functions

  • PERCENTILE()
  • PERCENTILE_APPROX()

These built-in functions handle a wide variety of analytical and statistical use cases.

37. How does Hive handle schema evolution?

Hive supports schema evolution primarily for ORC, Parquet, and Avro formats.

Supported Schema Evolution:

1. Adding Columns

  • Supported for ORC/Parquet/Avro.
  • New columns appear as NULL for old data.

2. Renaming Columns

  • Supported only with caution.
  • Tools referencing old schema may break.

3. Reordering Columns

  • NOT recommended for ORC/Parquet.
  • Can cause read failures.

4. Changing Column Types

  • Allowed in limited scenarios:
    • INT → BIGINT
    • STRING → VARCHAR
  • Risky in most cases.

5. Changing Table Properties

  • Changing SerDe, compression, etc. is allowed.

How Schema Evolution Works Internally:

For ORC/Parquet:

  • Schema is stored inside the file footer.
  • Reader merges old and new schemas at read time.
  • Missing columns become NULL.

For Avro:

  • Writer schema + reader schema are reconciled using Avro rules.

38. What is predicate pushdown in Hive?

Predicate pushdown is a powerful optimization technique where filters are pushed closer to the data, reducing the amount of data read and processed.

How Predicate Pushdown Works:

1. Filters Applied Early

Instead of filtering rows after reading them, Hive asks the storage layer to:

  • Skip row groups
  • Skip stripes
  • Skip blocks

Using metadata like min/max values.

2. Supported by ORC & Parquet

Example:

SELECT * FROM sales WHERE amount > 1000;

ORC checks stripe-level stats:

  • If stripe's max(amount) < 1000 → skip entire stripe.

3. Benefits

  • Less I/O
  • Faster scans
  • Lower CPU usage
  • Smaller shuffle size

4. Not Supported for TEXTFILE

Because no metadata statistics exist in text files.

Predicate pushdown is one of the reasons ORC/Parquet significantly outperform raw text formats.

39. What is index-based query optimization in Hive?

Indexing helps Hive reduce the amount of data scanned by creating an auxiliary structure that helps locate relevant rows quickly.

Types of Hive Indexes:

  • Compact Index
  • Bitmap Index

How Index Optimization Works:

  1. Create an index:
CREATE INDEX idx_sales_amount ON TABLE sales(amount)
AS 'BITMAP' WITH DEFERRED REBUILD;
  • Index stores the location of values in HDFS.
  • Query uses index:
    • SELECT * FROM sales WHERE amount = 100;

    Hive checks the index to see which blocks contain matching rows.

    Limitations:

    • Indexes are stored in separate tables.
    • Require maintenance.
    • Not widely used today because ORC/Parquet + predicate pushdown are faster.

    Hive 3.x has effectively deprecated indexing in favor of more modern optimization techniques.

    40. How does Hive support columnar compression?

    Hive supports columnar compression primarily through ORC and Parquet formats.

    How Columnar Compression Works:

    1. Columnar Storage

    Since data is stored column-wise:

    • Similar values stored together
    • Great for compression

    This enables:

    • Run-length encoding
    • Dictionary encoding
    • Bit-packing

    2. ORC Compression

    ORC supports:

    • Zlib
    • Snappy
    • LZO

    ORC automatically selects the best compression method based on column characteristics.

    3. Parquet Compression

    Parquet supports:

    • Snappy
    • GZip
    • LZO
    • Brotli

    Parquet applies compression on pages within column chunks.

    4. Reduced Disk & I/O

    Columnar compression reduces:

    • Disk usage
    • Network transfer
    • Read-time I/O

    Often compression ratios of 5x–10x.

    5. Vectorization Synergy

    Compressed columnar blocks integrate with vectorized execution for higher throughput.

    Experienced (Q&A)

    1. Explain Hive execution plan in depth.

    A Hive execution plan is the end result of the compiler’s transformation of a HiveQL statement into a set of physical operations that will be executed on the cluster. Conceptually it has multiple layers:

    1. Logical Plan — a tree of relational operators (scan, filter, project, join, group-by, sort, etc.) that represents what the query must compute. This is independent of execution engine and mostly used for semantic checks and logical optimizations (predicate pushdown, projection pruning, join reordering).
    2. Optimized Logical Plan — the logical plan after applying rule-based and cost-based optimizations (push filters down to scans, collapse redundant operators, reorder joins based on statistics, add partial aggregation). The Cost-Based Optimizer (CBO) uses table/column/partition stats to choose cheaper alternatives.
    3. Physical Plan / DAG — a concrete plan that maps logical operators to physical tasks and operator pipelines. For Tez/Spark this is a DAG of vertices and edges; for MapReduce it becomes a sequence of map and reduce jobs. The physical plan specifies which operators run in the same task (operator chaining), where data is shuffled, which operators require sort/shuffle, and how many reducers to use.
    4. Execution Tasks — low-level tasks that will be submitted to the execution engine (YARN containers). They include I/O formats, SerDes, operator pipelines, memory & buffer settings, and shuffle/sort configuration.

    Important runtime pieces attached to the plan:

    • Partition/stripe/bucket pruning points so scans only touch necessary data.
    • Statistics-driven decisions (broadcast/map-side join vs reduce-side join).
    • Operator parallelism: how many mappers/reducers or parallel tasks are used.
    • Physical properties: whether input is sorted/bucketed (enables SMB join), whether vectorization is enabled, which SerDe/format (ORC/Parquet) features to use (predicate pushdown, bloom filters).

    Reading an execution plan (EXPLAIN) shows the pipeline, joins and reduce stages, which tables are scanned, and where heavy shuffles occur. EXPLAIN ANALYZE (when available) augments the plan with runtime metrics so you can see actual rows processed per stage, time taken, and bottlenecks — invaluable for tuning.

    2. How do you debug slow-running Hive queries?

    Debugging slow Hive queries is systematic: reproduce, gather evidence, isolate, and iterate. Typical steps:

    1. Collect the plan and runtime metrics: run EXPLAIN to inspect the logical/physical plan and EXPLAIN ANALYZE (or get Tez/Spark job counters) to see actual rows, task durations, and shuffle sizes.
    2. Check data statistics & pruning: ensure ANALYZE TABLE ... COMPUTE STATISTICS has run. Lack of stats often causes bad join order or unnecessary full-table scans.
    3. Inspect IO and formats: confirm table storage (ORC/Parquet) and predicates are taking advantage of predicate pushdown and column pruning. Text files will be far slower.
    4. Examine shuffle and skew: large shuffle bytes or huge variance in reducer times point to data skew. Check reducer task histograms in the resource manager or job UI.
    5. Check resource configuration: container memory, number of reducers, I/O sort buffer sizes, Tez/Spark memory settings, and whether vectorization/LLAP are enabled.
    6. Look for small files / file layout issues: many small files cause many mappers and overhead. Consolidate files or use CombineHiveInputFormat.
    7. Check for unnecessary serialization or UDF cost: expensive UDFs or non-vectorized operators slow CPU-bound stages.
    8. Run focused micro-benchmarks: isolate problematic steps (e.g., run only the join stage or only the scan) to determine if the issue is I/O, CPU, or network-bound.
    9. Examine logs and YARN UI: failed attempts, GC pauses, or container restarts will show in logs and indicate resource misconfiguration.
    10. Apply fixes iteratively: add or refresh stats, change join strategy (broadcast/map-side), increase parallelism or memory, enable vectorization/LLAP, rebalance buckets, or rewrite the query.

    Document changes and metrics after each fix; good instrumentation (metrics, query logging) makes root cause analysis faster.

    WeCP Team
    Team @WeCP
    WeCP is a leading talent assessment platform that helps companies streamline their recruitment and L&D process by evaluating candidates' skills through tailored assessments

    Check out these other Interview Questions...

    Interviews, tips, guides, industry best practices, and news.
    Angular Interview Questions and Answers
    ASP.NET Interview Questions and Answers
    Java 8 Interview Questions and Answers
    VB.Net Interview Questions and Answers
    AutoCAD Interview Questions and Answers
    Large Language Models interview Questions and Answers
    Reinforcement Learning interview Questions and Answers
    Graphic Design Interview Questions and Answers
    Jira Interview Questions and Answers
    View all posts