Unit4 - Subjective Questions

Apache Hive is a data warehouse software project built on top of Apache Hadoop for providing data query and analysis. Hive gives a SQL-like interface to query data stored in various databases and file systems that integrate with Hadoop.

Primary Use Cases:

• Data Warehousing: It is used to analyze historical data and generate reports.
• Log Processing: Ideal for text mining, analyzing web logs, and extracting user behavioral patterns.
• ETL Operations: Extract, Transform, and Load operations can be efficiently performed on massive datasets.
• Data Analysis: Enabling data scientists and analysts to query large datasets without needing to write complex Java MapReduce code.

The Apache Hive Architecture consists of several key components that work together to process SQL-like queries on Hadoop:

1. User Interfaces: Hive provides various interfaces like the Web UI, Hive Command Line Interface (CLI), and Beeline (JDBC/ODBC client) to submit queries.
2. Thrift Server: It allows remote clients (using different programming languages) to submit commands and requests to Hive.
3. Driver: This component receives the queries. It implements the notion of session handles and provides execute and fetch APIs. It acts as the orchestrator of the query execution.
4. Compiler: It parses the query, does semantic analysis, and generates an execution plan. The execution plan is a Directed Acyclic Graph (DAG) of MapReduce, Tez, or Spark jobs.
5. Metastore: The central repository of Hive schema and metadata. It stores metadata for Hive tables, partitions, columns, types, and the mapping to the actual HDFS directories.
6. Execution Engine: It executes the execution plan created by the compiler. It submits the jobs to the underlying cluster (e.g., Hadoop MapReduce, Apache Tez) and monitors their progress.

The Hive Metastore is a central repository that stores the metadata associated with Hive. This includes definitions of tables, partitions, columns, data types, and the location of the data in HDFS.

Modes of Deploying Metastore:

1. Embedded Metastore: Both the Metastore service and the underlying database (typically Apache Derby) run in the same JVM as the Hive service. Used primarily for unit testing. Only one active user can connect at a time.
2. Local Metastore: The Metastore service runs in the same JVM as the Hive service, but it connects to an external database (like MySQL or PostgreSQL) running in a separate process. Allows multiple sessions.
3. Remote Metastore: The Metastore service runs in its own separate JVM process, and clients communicate with it using the Thrift network API. The backend database also runs as a separate process. This provides the best scalability and security.

Managed (Internal) Tables:

• Data Ownership: Hive owns the data and the metadata.
• Storage Location: Data is stored in the default Hive warehouse directory in HDFS (/user/hive/warehouse).
• Drop Behavior: When a managed table is dropped, Hive deletes both the metadata (schema) and the actual data files from HDFS.
• Use Case: Best when Hive is expected to manage the lifecycle of the data.

External Tables:

• Data Ownership: Hive only owns the metadata. The data is managed externally.
• Storage Location: Data can be stored anywhere in HDFS; the user specifies the LOCATION during table creation.
• Drop Behavior: When an external table is dropped, Hive only deletes the metadata. The actual data files remain intact in HDFS.
• Use Case: Best when data is shared with other Hadoop tools (like Pig or Spark) or when you want to ensure data is not accidentally deleted.

Partitioning in Hive is a technique to divide large datasets into more manageable parts based on the values of one or more columns (e.g., date, country). It creates sub-directories in HDFS, significantly improving query performance through "partition pruning" (scanning only relevant directories).

Static Partitioning:

• The user must explicitly pass the partition values while loading the data into the table.
• Data is loaded individually into each partition.
• Recommended when loading large files into specific, known partitions.
• Slower for creating many partitions as it requires separate commands.

Dynamic Partitioning:

• The partition values are dynamically determined by Hive during data insertion based on the values in the selected columns.
• Allows bulk loading of data into multiple partitions using a single SQL INSERT statement.
• Requires enabling the property: set hive.exec.dynamic.partition=true;.
• Best when the number of partitions is large or unknown beforehand.

Bucketing (or Clustering) is a technique used to decompose large datasets into more manageable chunks called buckets. It is implemented by applying a hash function to a specific column (the bucketing column) and dividing the data into a fixed number of buckets.

How it works mathematically:
When a record is inserted, Hive evaluates the hash value of the bucketing column. It then applies the modulo operator against the total number of buckets configured.

Formula:

Advantages:

1. Map-Side Joins: Improves the performance of joins when joining two tables bucketed on the same column with the same number of buckets.
2. Sampling: Enables efficient data sampling using the TABLESAMPLE clause, as Hive can just pick specific buckets instead of scanning the whole dataset.

SerDe stands for Serializer/Deserializer. It is a Hive interface that tells Hive how to interpret data in HDFS files and map it to table columns, and vice versa.

Role during Read and Write Operations:

• Read Operation (Deserialization): When Hive queries data from an HDFS file, it reads a chunk of bytes, and the Deserializer converts this byte stream into a format that Hive can process (like Java objects or an internal row structure). It extracts the individual fields based on delimiters or patterns.
• Write Operation (Serialization): When data is inserted into a Hive table, the Serializer converts the internal Hive row representation into a byte stream formatted appropriately for the output file (e.g., CSV, JSON, ORC) so it can be written to HDFS.

Hive comes with built-in SerDes for formats like Regex, JSON, CSV, SequenceFile, ORC, and Parquet. Users can also write Custom SerDes for proprietary data formats.

Hive allows users to write custom functions in Java to process data when built-in functions fall short. There are three main types:

1. UDF (User Defined Function):

   • Input-to-Output: 1-to-1.
   • Takes a single row as input and produces a single row/value as output.
   • Example: String manipulation functions like UPPER(), LOWER(), or a custom function to format phone numbers.

2. UDAF (User Defined Aggregate Function):

   • Input-to-Output: N-to-1.
   • Takes multiple rows (or a group of rows) as input and produces a single aggregated output.
   • Example: SUM(), COUNT(), MAX(), or a custom function to calculate the standard deviation.

3. UDTF (User Defined Tabular Function):

   • Input-to-Output: 1-to-N.
   • Takes a single row as input and produces multiple rows/columns (a table) as output.
   • Example: EXPLODE(), which takes an array and outputs a row for each element in the array.

Reduce-Side Join (Common Join):

• Mechanism: The default join in Hive. Mappers read the tables and output join keys and values. The data is shuffled and sorted over the network. Reducers then perform the actual join operation based on the keys.
• Pros: Can handle arbitrarily large tables.
• Cons: Very slow due to massive network I/O during the shuffle phase and potential data skew issues.
• Use Case: Used when both joining tables are extremely large.

Map Join (Broadcast Join):

• Mechanism: If one table is small enough to fit into memory, Hive loads the small table into an in-memory hash table and broadcasts it to all Mapper nodes. The Mapper then reads the large table sequentially and performs the join entirely in the Map phase.
• Pros: Extremely fast as it bypasses the Shuffle and Reduce phases entirely. No network bottleneck.
• Cons: Constrained by the memory capacity of the Mapper nodes. If the small table exceeds memory, it will fail.
• Use Case: Used when joining a massive fact table with a small dimension table.

While Hive provides a SQL-like interface, it is fundamentally different from a traditional Relational Database Management System (RDBMS):

Apache Pig vs Apache Hive:

1. Language: Hive uses HiveQL (a declarative, SQL-like language), whereas Pig uses Pig Latin (a procedural, data flow language).
2. Target Audience: Hive is designed for data analysts and business intelligence engineers familiar with SQL. Pig is designed for programmers and data engineers who prefer scripting data pipelines step-by-step.
3. Execution: Both compile down to MapReduce (or Tez/Spark) jobs.
4. Data Types: Hive has strict schemas and robust complex types. Pig is more flexible and handles semi-structured/unstructured data natively (nested bags/tuples) better than Hive.

When to choose what:

• Choose Hive when dealing with highly structured data, generating reports, performing complex ad-hoc analytics, or when the team consists of SQL developers.
• Choose Pig when writing complex ETL data pipelines, dealing with semi-structured data, or when the operations require a procedural step-by-step transformation flow.

Hive supports complex data types that allow the storage of multi-valued or nested data within a single column. The primary complex types are:

1. ARRAY: A collection of identical data types, indexed by integers (starting from 0).

   • Example definition: skills ARRAY<STRING>
   • Example data: ['Java', 'Python', 'Hadoop']
   • Access: skills[0] returns 'Java'.

2. MAP: A collection of key-value pairs, where fields are accessed using keys. Keys must be primitives.

   • Example definition: deductions MAP<STRING, FLOAT>
   • Example data: {'Tax': 200.5, 'Insurance': 150.0}
   • Access: deductions['Tax'] returns 200.5.

3. STRUCT: A collection of elements of different types, similar to a C struct or an object. Fields are accessed using dot notation.

   • Example definition: address STRUCT<street:STRING, city:STRING, zip:INT>
   • Example data: {'Main St', 'New York', 10001}
   • Access: address.city returns 'New York'.

4. UNIONTYPE: A collection that can hold exactly one value from a specified list of data types.

When a user submits a HiveQL query, the following steps occur:

1. Execute Query: The UI/Client submits the query to the Driver.
2. Get Plan: The Driver asks the Compiler to generate an execution plan.
3. Get Metadata: The Compiler communicates with the Metastore to retrieve metadata (schema, table locations, column types).
4. Semantic Analysis & Optimization: The Compiler type-checks the query, verifies the schema, and generates a logical plan. It then optimizes this plan (e.g., predicate pushdown, map-join conversion) to create a Directed Acyclic Graph (DAG) of physical tasks.
5. Send Plan: The Compiler sends the final execution plan back to the Driver.
6. Execute Plan: The Driver sends the plan to the Execution Engine.
7. Submit Job: The Execution Engine submits the tasks (MapReduce, Tez, or Spark jobs) to the underlying cluster's Resource Manager (YARN).
8. Fetch Results: Once the jobs are completed, the Execution Engine informs the Driver. The Client/UI then fetches the results from the Driver.

Hive supports various file formats, including TextFile (CSV, TSV), SequenceFile, RCFile, ORC, and Parquet. Selecting the right format is crucial for query optimization and storage efficiency.

1. ORC (Optimized Row Columnar):

• Stores data in a highly optimized columnar format.
• Breaks data into Stripes (typically 250MB), which contain index data, row data, and stripe footers.
• Advantages: Excellent compression (ZLIB/Snappy), lightweight indexes (min/max/sum) that allow Hive to skip reading entire blocks of data (Predicate Pushdown), and highly integrated into the Hive ecosystem.

2. Parquet:

• A columnar storage format heavily inspired by Google's Dremel.
• Advantages: Highly efficient for querying specific columns. It supports deep nesting of data natively (unlike ORC, which flattens it).
• Use Case: Parquet is the preferred format when sharing data across different Hadoop ecosystem tools (like Spark, Impala, and Drill) due to its widespread compatibility, whereas ORC is more optimized specifically for Hive.

To improve query execution times in Hive, developers use several optimization techniques:

1. Partitioning & Bucketing: Divides data logically, allowing queries to skip scanning irrelevant data (Partition Pruning).
2. Columnar File Formats: Using ORC or Parquet reduces I/O by only reading the necessary columns and provides excellent compression.
3. Map Joins: If a table is small, using Map Joins avoids the costly shuffle and sort phases of a Reduce Join.
4. Vectorization: Processes batches of rows (typically 1024) at a time instead of one row at a time. This reduces CPU overhead and improves cache usage (set hive.vectorized.execution.enabled=true).
5. Cost-Based Optimization (CBO): Hive uses statistics (like table size, number of rows) to generate the most efficient query plan, ordering joins and operations intelligently.
6. Execution Engine: Switching from the legacy MapReduce engine to Apache Tez or Spark (set hive.execution.engine=tez) significantly speeds up processing by avoiding intermediate disk writes.

Views in Hive are logical constructs (virtual tables) created by executing a query. They do not store any physical data themselves; they only store the query definition in the Metastore.

Differences from Tables:

• Storage: Tables store actual data in HDFS. Views store no data, only the SQL statement used to define them.
• Execution: When a view is queried, Hive executes the underlying query that defines the view dynamically.
• Use Cases for Views:
  1. Security/Access Control: Restricting access to a subset of data (e.g., hiding sensitive columns like SSN or Salary) by granting users access to the view instead of the base table.
  2. Query Simplification: Encapsulating complex, frequently used JOINs or subqueries into a single virtual table, making subsequent queries easier to write and read.
  3. Logical Abstraction: Insulating users from changes in the underlying table schemas.

Data Definition Language (DDL): Used to define or modify the structure of tables.

• Create Table:
  CREATE TABLE employees (id INT, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
• Alter Table:
  ALTER TABLE employees ADD COLUMNS (age INT);
• Drop Table:
  DROP TABLE IF EXISTS employees;

Data Manipulation Language (DML): Used to insert or modify data.

• Load Data: Moves/copies files from HDFS or local filesystem into the Hive table.
  LOAD DATA LOCAL INPATH '/tmp/data.csv' INTO TABLE employees;
• Insert Data: Inserts data using a query.
  INSERT INTO TABLE managers SELECT id, name FROM employees WHERE age > 40;
• Select Data (DQL, but often grouped with DML):
  SELECT name, age FROM employees LIMIT 10;

HCatalog is a table and storage management layer for Hadoop that enables users with different data processing tools (like Pig, MapReduce, and Spark) to easily read and write data on the grid.

Relationship with Hive:

• HCatalog is built on top of the Hive Metastore.
• It exposes the tabular data of Hive to other Hadoop ecosystem components.
• Without HCatalog, tools like Pig or MapReduce would need to know the specific HDFS file paths, delimiters, and file formats (CSV, ORC, etc.) to process the data.
• With HCatalog, these tools can simply refer to the data by its Hive "table name." HCatalog handles the underlying serialization/deserialization (SerDe) and file discovery.
• It ensures that users of different tools share a common, unified view of data in the cluster.

The Execution Engine in Hive is responsible for taking the logical query plan compiled by the Hive driver and executing it physically on the Hadoop cluster.

1. MapReduce (Legacy):

• Originally, Hive translated all queries into a series of MapReduce jobs.
• Drawback: MapReduce writes intermediate data to HDFS between the Map and Reduce phases, and between chained MR jobs. This high disk I/O causes significant latency, making it unsuitable for interactive querying.

2. Apache Tez (Modern):

• Tez models data processing as a Directed Acyclic Graph (DAG).
• Advantages: Tez optimizes the execution by avoiding unnecessary disk writes. Intermediate data can be streamed directly from one phase to the next in memory.
• It reuses containers, reducing the startup overhead of MapReduce jobs.
• Modern Hive default execution engine is Tez (set hive.execution.engine=tez;), providing a massive performance boost, especially for complex queries with multiple joins and aggregations.