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

Cloudera CDP Data Analyst: Performance Tuning and Troubleshooting Queries

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

All guide sections

Performance Tuning and Troubleshooting Queries

This domain tests whether you can diagnose a slow query from evidence - plan, statistics, partition counts, file sizes - rather than guessing. Expect scenario items: a query was fast, a load changed, now it is slow; which fix applies.

The diagnostic ladder

  1. Statistics: missing or stale stats produce wrong join order and join strategy. In Impala run COMPUTE STATS tbl (or COMPUTE INCREMENTAL STATS on partitioned tables); in Hive ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS.
  2. Partition pruning: check the plan and SHOW PARTITIONS. A predicate on a non-partition column scans everything.
  3. File layout: thousands of small files per partition cost open/close overhead. Target 128–256 MB files; use COMPUTE INCREMENTAL STATS and compaction or rewrite jobs to consolidate.
  4. Plan reading: EXPLAIN shows scan, join type, and exchange steps; the Impala query profile shows per-node time, rows, and memory, and whether runtime filters were applied.
  5. Concurrency: admission control and resource pools cap memory and running queries; a query queued behind others looks slow but is not inefficient.

Tuning levers

  • Columnar formats plus column pruning: never SELECT * in a wide table; Parquet/ORC read only referenced columns.
  • Broadcast/map-side joins for small dimensions; partitioned or bucketed joins for large-large joins sharing the key.
  • Skew handling: Hive skew join optimization, Spark salting, or splitting the dominant key.
  • Hive: enable vectorization and cost-based optimization; use Tez rather than MapReduce.
  • Avoid repeated small INSERTs; batch writes and refresh metadata once.

Worked example

An Impala join between a 2-billion-row fact table and a 500-row country dimension takes 45 minutes. EXPLAIN shows a partitioned (shuffle) join. Reasoning: without statistics on country, Impala cannot tell it is tiny and cannot broadcast it. Running COMPUTE STATS country and re-explaining shows a broadcast join; runtime drops to under two minutes. If the fact table also lacked stats, the join order could invert entirely, so compute stats on both sides.

Traps

  • Running COMPUTE STATS on a table with 50,000 partitions during business hours - use incremental stats.
  • Tuning SQL when the real cause is stale Impala metadata after a load; run REFRESH first.
  • Assuming LIMIT reduces scan cost on an unpartitioned table.
  • Blaming the query for queueing delays caused by resource pools.
  • Adding executors or memory before checking shuffle partition counts and file sizes.