I85PREP
⌘ K
Cloudera CDP Data Analyst study materials
Cloudera CDP Data Analyst study resources

Cloudera CDP Data Analyst: SQL Querying with Apache Hive and Apache Impala

Cloudera CDP Data Analyst · Work through your exam, one topic at a time.

All guide sections

SQL Querying with Apache Hive and Apache Impala

Hive and Impala share the Hive Metastore and a largely common SQL dialect, but their execution models, metadata-freshness rules, and write capabilities differ. Exam items concentrate on those differences: refresh semantics, partition DDL, dynamic partition inserts, join strategies, and analytic functions.

Engine differences that decide answers

  • Impala: MPP, in-memory exchange; low latency; a query fails entirely if a node drops mid-flight. Default choice for interactive BI.
  • Hive on Tez: spills to disk and retries tasks, so higher latency but resilient; supports ACID writes and MERGE INTO.
  • Metadata caching: Impala caches table and partition metadata. After data-only changes from Hive or Spark, run REFRESH tablename. After DDL such as ALTER TABLE ... ADD COLUMNS, run INVALIDATE METADATA tablename (or cluster-wide).
  • Statistics: Impala COMPUTE STATS tbl; Hive ANALYZE TABLE tbl COMPUTE STATISTICS FOR COLUMNS.

Partition syntax and writes

  • Static: INSERT OVERWRITE TABLE sales PARTITION (dt='2024-06-01') SELECT ...
  • Dynamic: INSERT OVERWRITE TABLE sales PARTITION (dt) SELECT ..., dt FROM stage; in Hive set hive.exec.dynamic.partition.mode=nonstrict and cap hive.exec.max.dynamic.partitions.
  • Both support CTAS, INSERT ... SELECT, GROUP BY, and window functions; Impala adds COMPUTE INCREMENTAL STATS on partitioned tables, Hive adds ACID MERGE.

Worked example

A Hive job loads dt=2024-06-01 into sales. An Impala dashboard still shows yesterday's totals. The partition list and file metadata were cached by Impala. Because only data changed, REFRESH sales is correct and cheap. Had the pipeline added a column via ALTER TABLE, Impala would need INVALIDATE METADATA sales, since the schema itself changed.

Traps

  • Using INVALIDATE METADATA after every load - it forces a full Metastore re-read and is much heavier than REFRESH.
  • Dynamic partition inserts without nonstrict mode: Hive rejects the query because the final partition column must be static.
  • Integer division: 5/2 evaluates to 2; use 5/2.0, CAST, or ROUND.
  • col <> 'x' silently drops rows where col IS NULL; add an explicit OR col IS NULL.
  • Assuming LIMIT 10 makes an unpartitioned scan cheap - it still reads until enough rows are produced.
  • Writing to a Hive ACID table from Impala without checking engine support in your CDP release.