Master Hive SQL: 10 Advanced Use Cases & Performance Optimizations for Hive 3.x

Case 11: Session Segmentation (Adjacent Event Analysis)

SQL Example

SELECT
  user_id,
  event_time,
  SUM(session_flag) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM (
  SELECT
    user_id,
    event_time,
    CASE WHEN DATEDIFF(event_time, LAG(event_time) OVER w) > 30 THEN 1 ELSE 0 END AS session_flag
  FROM user_clickstream
  WINDOW w AS (PARTITION BY user_id ORDER BY event_time)
) t;

Key Points

Use LAG() + DATEDIFF() to detect session gaps.

Cumulative SUM(session_flag) creates a unique session ID.

Applicable to user behavior analysis.

Case 12: Funnel Conversion Rate Analysis

SQL Example

WITH funnel AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_type='step1' THEN 1 ELSE 0 END) AS step1,
    MAX(CASE WHEN event_type='step2' THEN 1 ELSE 0 END) AS step2,
    MAX(CASE WHEN event_type='step3' THEN 1 ELSE 0 END) AS step3
  FROM user_events
  WHERE dt = '2025-04-15'
  GROUP BY user_id
)
SELECT
  COUNT(*) AS total_users,
  SUM(step1) AS step1_count,
  SUM(step2) AS step2_count,
  SUM(step3) AS step3_count,
  ROUND(SUM(step2)/SUM(step1), 2) AS step1_to_2_rate,
  ROUND(SUM(step3)/SUM(step2), 2) AS step2_to_3_rate
FROM funnel;

Key Points

Use WITH clause to structure logic. MAX(CASE WHEN) implements behavior attribution. ROUND() controls conversion‑rate precision.

Case 13: Median Calculation (Two Methods)

SQL Example

-- Method 1: Built‑in function
SELECT PERCENTILE(CAST(salary AS BIGINT), 0.5) AS median_salary FROM employee;

-- Method 2: Precise window function
SELECT AVG(salary) AS median FROM (
  SELECT
    salary,
    ROW_NUMBER() OVER (ORDER BY salary) AS rn,
    COUNT(*) OVER () AS cnt
  FROM employee
) t
WHERE rn BETWEEN cnt/2 AND cnt/2 + 1;

Key Points

Built‑in function provides an approximate median.

Precise median uses ROW_NUMBER() + COUNT().

Remember type conversion with CAST.

Case 14: Array Column to Multiple Rows (Advanced LATERAL VIEW)

SQL Example

SELECT
  user_id,
  pos + 1 AS item_index,
  item
FROM orders
LATERAL VIEW POSEXPLODE(SPLIT(REGEXP_REPLACE(items, '[\[\]"]', ''), ',')) items AS pos, item;

Key Points

POSEXPLODE

retains array index information. SPLIT + REGEXP_REPLACE parses JSON‑style arrays.

Useful for expanding nested structures.

Case 15: Hierarchical Data Recursive Query (Simulated Implementation)

SQL Example

WITH RECURSIVE org_cte AS (
  SELECT emp_id, name, manager_id, 1 AS level
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, c.level + 1
  FROM employee e
  JOIN org_cte c ON e.manager_id = c.emp_id
)
SELECT * FROM org_cte LIMIT 100;

Key Points

Hive does not support true recursive CTE; simulate with UNION ALL.

Use UNION ALL to expand hierarchy.

Beware of circular‑dependency issues.

Case 16: Efficient Deduplication Strategy

SQL Example

INSERT OVERWRITE TABLE user_latest_info
SELECT
  user_id,
  info,
  update_time
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY update_time DESC) AS rn
  FROM user_info
  WHERE is_valid = 1
) t
WHERE rn = 1;

Key Points

ROW_NUMBER()

implements conditional deduplication.

Suitable for retaining the most recent record.

Can be extended with RANK() or DENSE_RANK().

Case 17: Small‑File Merge Optimization

SQL Example

SET hive.merge.mapfiles = true;
SET hive.merge.mapredfiles = true;
SET hive.merge.size.per.task = 256000000;
SET hive.merge.orcfile.stripe.size = 67108864;

INSERT OVERWRITE TABLE target_table
SELECT * FROM source_table;

Key Points

Set Hive merge parameters to reduce small files.

Applicable to ORC format write optimization.

Watch task concurrency and merge granularity.

Case 18: Conditional Aggregation Statistics

SQL Example

SELECT
  dt,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status = 'unpaid' THEN 1 ELSE 0 END) AS unpaid_count,
  AVG(CASE WHEN status = 'paid' THEN amount ELSE NULL END) AS avg_paid_amount
FROM orders
GROUP BY dt;

Key Points

Use CASE WHEN for multi‑dimensional statistics. AVG() + NULL limits calculation scope.

Suitable for reporting‑type aggregates.

Case 19: Approximate Statistics Algorithms

SQL Example

SELECT
  APPROX_COUNT_DISTINCT(user_id) AS uv,
  APPROX_PERCENTILE(CAST(amount AS DOUBLE), 0.95) AS p95_amount
FROM user_payments;

Key Points

APPROX_COUNT_DISTINCT()

replaces COUNT(DISTINCT) for speed. APPROX_PERCENTILE() provides fast percentile calculation.

Ideal for large‑scale data where performance outweighs exactness.

Case 20: Data Quality Checks

SQL Example

SELECT
  'user_profiles' AS table_name,
  COUNT(CASE WHEN user_id IS NULL THEN 1 END) AS null_user_ids,
  COUNT(CASE WHEN age < 0 OR age > 120 THEN 1 END) AS invalid_ages,
  COUNT(CASE WHEN email NOT LIKE '%@%' THEN 1 END) AS invalid_emails
FROM user_profiles;

Key Points

Template‑based data‑quality monitoring using CASE WHEN + COUNT().

Can be extended to automated monitoring scripts.

Optimization Tips Summary

Use WITH clause to break down complex, multi‑level queries.

Prefer POSEXPLODE over EXPLODE when array index is needed.

Apply ROW_NUMBER() for precise deduplication; use APPROX functions for fast statistics.

Run ANALYZE TABLE regularly to collect statistics and improve execution plans.

Create Bloom filter indexes (e.g., orc.bloom.filter.columns) on high‑frequency columns to accelerate filtering.