Apache Hive Training

You can explain distributed storage architecture and run basic HDFS shell commands to manage cluster files.

1. What Big Data is and why it matters90 min
   • Volume, velocity, and variety
   • Scaling up versus scaling out
   • The shared-nothing architecture
2. The Hadoop ecosystem overview90 min
   • Hadoop core components
   • Batch processing paradigm
   • The role of the distributed storage layer
3. HDFS: the distributed file system2 hrs
   • NameNode and DataNode architecture
   • Block size configuration
   • Replication factor and fault tolerance
   • hdfs dfs administrative commands
4. MapReduce and YARN2 hrs
   • Map and reduce phases
   • ResourceManager and NodeManager
   • Container allocation on YARN
   • Job submission workflow
5. Where Hive fits in2 hrs
   • Abstraction of MapReduce via SQL
   • The evolution of declarative queries on Hadoop
   • The Hive data warehousing lifecycle

Hands-on project: Provision a local pseudo-distributed Hadoop cluster and ingest a 5GB multi-line log dataset into HDFS using `hdfs dfs -put`.

You can connect to HiveServer2 via Beeline and execute your first schema-on-read queries against raw HDFS files.

1. What Hive is and why it exists90 min
   • Schema-on-read vs schema-on-write
   • Translating SQL into physical execution plans
   • Limitations of direct MapReduce programming
2. Hive architecture: metastore, driver and execution engine2 hrs
   • Derby vs MySQL backend for the Hive Metastore
   • Compiler, parser, and semantic analyzer
   • Logical and physical plan generation
3. Hive versus traditional databases90 min
   • OLTP vs OLAP workloads
   • Lack of row-level updates in early Hive
   • Latency trade-offs in distributed file systems
4. Setting up Hive and the Beeline CLI2 hrs
   • hive-site.xml configuration properties
   • Starting HiveServer2 and Metastore services
   • Connecting using `beeline -u jdbc:hive2://...`
5. Running your first HiveQL queries2 hrs
   • Executing basic SELECT statements
   • Checking query logs on YARN
   • Understanding job execution status in Beeline

Hands-on project: Configure Beeline authentication properties, connect to HiveServer2, and execute an explicit schema-on-read query over a comma-delimited CSV file.

You can define structured schemas using primitive and complex data types and manage internal versus external tables safely.

1. Databases and the DDL3 hrs
   • CREATE DATABASE and location properties
   • USE statement and namespace management
   • DROP DATABASE with CASCADE option
2. Managed (internal) versus external tables4 hrs
   • Data lifecycle management in managed tables
   • External table pointer mechanics
   • Recovering dropped tables via location preservation
3. Primitive and complex data types (ARRAY, MAP, STRUCT)4 hrs
   • TINYINT, BIGINT, STRING, TIMESTAMP primitives
   • Defining nested ARRAY and MAP types
   • Querying elements using bracket notation and dot syntax
4. Loading data into Hive4 hrs
   • LOAD DATA LOCAL INPATH syntax
   • Moving data without copying using HDFS paths
   • INSERT OVERWRITE DIRECTORY patterns
5. Inserting, updating and the table lifecycle5 hrs
   • INSERT INTO vs INSERT OVERWRITE
   • ACID transactions in Hive 3
   • Transactional table properties (`transactional=true`)

Hands-on project: Create an external table backed by an S3 or HDFS path with nested STRUCT and ARRAY fields, and load transactional records via `LOAD DATA INPATH`.

You can write complex multi-table analytical queries, execute map-side joins, and construct reusable views.

1. SELECT, WHERE, GROUP BY and ORDER BY4 hrs
   • Filter predicate evaluation order
   • Aggregation functions with GROUP BY
   • Sorting limitations with ORDER BY versus SORT BY
2. Joins: inner, outer and map-side joins4 hrs
   • Inner, Left, Right, and Full Outer syntax
   • MapJoin optimization hints (`/*+ MAPJOIN(t1) */`)
   • Handling data skew during joins
3. Built-in functions and operators4 hrs
   • String manipulation functions (`CONCAT`, `SUBSTR`)
   • Conditional evaluation (`CASE WHEN`, `COALESCE`)
   • Mathematical and logical operators
4. Subqueries and views4 hrs
   • Correlated subqueries and IN/EXISTS clauses
   • CREATE VIEW syntax
   • Materialized views for acceleration
5. Sorting, distributing and clustering4 hrs
   • DISTRIBUTE BY clause mechanics
   • CLUSTER BY shorthand syntax
   • SORT BY for intra-reducer ordering

Hands-on project: Write a multi-table join query combining user profiles and clickstream logs, sorting output partitions using `CLUSTER BY` and persisting results into a view.

You can design partitioned and bucketed table schemas that eliminate full table scans on petabyte-scale datasets.

1. Why partitioning matters for performance90 min
   • Avoiding full table scans
   • Directory structure mapping in HDFS
   • Partition key selection strategies
2. Static and dynamic partitioning2 hrs
   • Static partition insert syntax
   • Enabling dynamic partitioning configurations (`hive.exec.dynamic.partition=true`)
   • Non-strict mode settings
3. Bucketing and when to use it2 hrs
   • Hash-based record distribution
   • Clustering columns configuration
   • Enforcing bucketing during insertion (`hive.enforce.bucketing=true`)
4. Combining partitions and buckets2 hrs
   • Two-tier data layout design
   • Optimizing join performance on bucketed tables
   • SMB (Sort-Merge-Bucket) joins
5. Common pitfalls and best practices2 hrs
   • Mitigating the small files problem
   • Choosing appropriate partition granularities
   • Repairing partition metadata (`MSCK REPAIR TABLE`)

Hands-on project: Convert an unpartitioned daily log table into a date-partitioned and user_id-bucketed ORC table, enabling dynamic partition inserts.

You can select columnar storage formats like ORC and Parquet and implement custom SerDe configurations for semi-structured logs.

1. Text, SequenceFile, Avro, ORC and Parquet2 hrs
   • Row-oriented vs columnar storage formats
   • ORC stripe and index structures
   • Parquet Dremel record shredding model
2. Choosing the right format2 hrs
   • Read vs write workload trade-offs
   • Schema evolution support across formats
   • Compatibility with downstream analytics engines
3. Compression and storage tuning2 hrs
   • Gzip, Snappy, and LZO codecs
   • Block compression configuration in Hive
   • Evaluating disk footprint reduction
4. SerDe and working with JSON2 hrs
   • Serializer-Deserializer interface concepts
   • JsonSerDe for nested JSON records
   • RegexSerDe for unstructured log parsing
5. Schema design for analytics2 hrs
   • Denormalization strategies in Hive
   • Star schema layout for data warehousing
   • Handling sparse columns efficiently

Hands-on project: Ingest raw JSON application logs into an ORC-formatted table using a custom SerDe, configuring Snappy compression and checking storage reduction metrics.

You can write custom Java UDFs, apply windowing analytic functions, and transform complex date and string payloads.

1. Built-in, aggregate and table-generating functions2 hrs
   • EXPLODE and LATERAL VIEW syntax
   • COLLECT_LIST and COLLECT_SET aggregate functions
   • JSON tuple parsing functions
2. User Defined Functions (UDFs)2 hrs
   • Extending `org.apache.hadoop.hive.ql.exec.UDF`
   • Packaging and registering JARs using `ADD JAR`
   • Creating permanent function aliases
3. Windowing and analytics functions2 hrs
   • OVER clause syntax with PARTITION BY and ORDER BY
   • ROW_NUMBER(), RANK(), and DENSE_RANK()
   • LEAD, LAG, and sliding frame specifications
4. Working with dates and strings2 hrs
   • Date parsing and formatting functions
   • Timestamp arithmetic with `DATEDIFF` and `ADD_MONTHS`
   • String regex extraction
5. Advanced query patterns2 hrs
   • Pivot and unpivot transformations
   • Handling hierarchical event paths
   • Sessionization queries using window frames

Hands-on project: Develop a custom Java UDF for custom geolocation parsing, register it in Hive, and write a windowing query calculating rolling 7-day user activity metrics.

You can tune execution plans using Apache Tez, enable cost-based optimization, and diagnose query performance bottlenecks.

1. The Tez execution engine4 hrs
   • Directed Acyclic Graph (DAG) execution model
   • Tez container reuse and shuffle tuning
   • Replacing legacy MapReduce jobs with Tez
2. Vectorization and cost-based optimization4 hrs
   • Enabling vectorization (`hive.vectorized.execution.enabled=true`)
   • Cost-Based Optimization (CBO) with Calcite
   • Collecting column statistics (`ANALYZE TABLE`)
3. Join and query optimization4 hrs
   • Skew join optimization settings
   • MapJoin automatic conversion thresholds
   • Predicate pushdown configuration
4. Partition pruning and predicate pushdown4 hrs
   • Compile-time vs run-time partition pruning
   • Storage format predicate pushdown mechanics
   • Inspecting execution plans with EXPLAIN
5. Diagnosing and fixing slow queries4 hrs
   • Analyzing YARN and Tez application logs
   • Identifying memory spills and GC pressure
   • Resolving data skew bottlenecks

Hands-on project: Profile a slow-running multi-join query using Tez UI, apply partition pruning, vectorization flags, and cost-based optimization rules to reduce runtime by 70%.

You can integrate Hive with Spark SQL, share metastore catalogs, and deploy Hive workloads on cloud managed services.

1. Hive with Apache Spark (Spark SQL)2 hrs
   • Configuring Spark with Hive support (`enableHiveSupport()`)
   • Reading and writing Hive tables from Spark DataFrames
   • Performance comparison between Hive execution engines and Spark
2. Hive with HBase and BI tools2 hrs
   • Hive-HBase integration storage handler
   • Connecting Tableau and PowerBI via Hive JDBC drivers
   • Publishing aggregate tables for BI consumption
3. The Hive Metastore as a shared catalog2 hrs
   • Standalone HMS architecture
   • Externalizing metadata to PostgreSQL or MySQL
   • Catalog interoperability across analytics engines
4. Running Hive on the cloud (EMR, Dataproc, HDInsight)2 hrs
   • AWS EMR architecture with Hive and S3
   • Google Cloud Dataproc managed Hive service
   • Cloud storage decoupling from compute
5. ETL workflows with Hive2 hrs
   • Orchestrating multi-step Hive transformations
   • Idempotent ETL script design
   • Error handling and logging in batch pipelines

Hands-on project: Connect Apache Spark to the shared Hive Metastore, execute Spark SQL queries over Hive ORC tables, and spin up an AWS EMR cluster running Hive queries.

You can run transactional Hive tables and keep them compacted and queryable under continuous updates.

1. Transactional tables and the delta layout90 min
   • transactional=true
   • delta and base directories
   • ORC requirement
2. MERGE, UPDATE and DELETE in HiveQL90 min
   • MERGE INTO ... WHEN MATCHED
   • Bucketing requirements
   • Write-ID semantics
3. Minor and major compaction90 min
   • ALTER TABLE ... COMPACT
   • Compactor worker threads
   • SHOW COMPACTIONS
4. Modelling slowly changing dimensions90 min
   • Type 2 history in Hive
   • Late-arriving data
   • Reprocessing a partition

Hands-on project: Convert a partitioned external table to a transactional ORC table, apply a MERGE that upserts a day of late-arriving records, then trigger and verify a major compaction, reporting delta file counts before and after.

You can explain why Iceberg or Delta replaces plain Hive tables and migrate a table to one.

1. Why plain Hive tables hit a wall90 min
   • Directory-listing cost
   • No atomic commits
   • Schema evolution limits
2. Iceberg, Hudi and Delta compared90 min
   • Snapshot and manifest model
   • Copy-on-write vs merge-on-read
   • Engine support
3. Schema and partition evolution90 min
   • ALTER TABLE ADD COLUMN
   • Rename without rewrite
   • Hidden partitioning
4. Time travel and rollback90 min
   • Snapshot IDs
   • AS OF queries
   • Rolling back a bad write

Hands-on project: Register an Iceberg table against the existing Hive Metastore, migrate one partitioned dataset into it, perform a schema evolution that adds and renames a column, and demonstrate a time-travel query against a prior snapshot.

You can lock a warehouse down to column level and prove who read what.

1. Authentication with Kerberos90 min
   • Principals and keytabs
   • kinit and ticket lifetime
   • HiveServer2 impersonation
2. Authorisation with Apache Ranger90 min
   • Resource-based policies
   • Row-filter and column-mask
   • Policy precedence
3. Encryption and PII handling90 min
   • HDFS encryption zones
   • TLS for HiveServer2
   • Tokenisation vs masking
4. Audit, lineage and compliance90 min
   • Ranger audit logs
   • Atlas lineage capture
   • Proving an access trail

Hands-on project: Configure Ranger policies granting an analyst role SELECT on two columns of a PII table with the national-ID column masked, enable audit logging, and produce an access report showing a denied query.

You can secure Hive clusters, implement robust access controls, and build an end-to-end production data warehouse capstone.

1. Security, authentication and authorization in Hive2 hrs
   • Kerberos authentication for HiveServer2
   • Apache Ranger role-based access control policies
   • Column and row-level filtering configuration
2. Operational monitoring, logging and alerting2 hrs
   • Tracking Hive query metrics in Prometheus
   • Setting up YARN resource queue alerts
   • Auditing Metastore access logs
3. CI/CD and schema migration for Hive2 hrs
   • Managing DDL script version control with Git
   • Automated schema deployment pipelines
   • Zero-downtime table alterations
4. Capstone architecture and implementation2 hrs
   • Designing the staging, analytical, and reporting layers
   • Writing automated ingestion and transformation scripts
   • Executing performance verification benchmarks
5. Portfolio review, mock interviews and career readiness2 hrs
   • Data engineer interview question patterns on partitioning and skew
   • Resume optimization and GitHub portfolio structuring
   • Mock technical interview and feedback session

Hands-on project: Design and build a production-grade data warehouse capstone ingesting raw clickstream logs into partitioned ORC tables, secured with Ranger policies and tuned for analytics.