Cloudera CDP Data Analyst cheat sheet

Cloudera Data Platform (CDP) ships two deployment forms: CDP Public Cloud (CDP-PC) with Data Hub clusters and CDP Private Cloud (CDP-PVC) with Base and Data Mart clusters; analysts mostly touch Data Hub or Data Mart compute.

Core analyst-facing engines: Apache Hive (batch SQL on HDFS/object storage via HiveServer2), Apache Impala (MPP SQL with a long-running daemon and coordinator), Apache Kudu (mutable columnar store), Apache HBase (NoSQL key-value), Apache Spark (Spark SQL/DataFrame), and Apache Ozone or S3 as object storage.

Hive metastore (HMS) is the shared catalog: databases, tables, columns, partitions, and storage locations. Impala, Spark, and Hive all read the same metastore, so a table created in Hive is visible to Impala after INVALIDATE METADATA.

Managed vs external tables: a managed (internal) table's data is dropped with DROP TABLE; an external table only drops metadata. In CDP, default managed locations are governed by the warehouse directory setting.

Table formats: Hive ACID transactional tables (ORC, requires hive.support.concurrency and compaction), Apache Iceberg tables (snapshot-based, supports row-level deletes and time travel), and plain Parquet/ORC/Avro external tables.

File formats ranked for analytics: Parquet and ORC (columnar, compressed, predicate pushdown) are preferred; Avro is row-based and good for write-heavy ingestion; CSV/JSON are text and slow for large scans.

Partitioning prunes data at the directory level; bucketing (CLUSTERED BY ... INTO n BUCKETS) hashes rows into files and helps joins and sampling. Partition columns are virtual, not stored in the data files.

Cloudera Data Warehouse (CDW) provides isolated virtual warehouses (Hive or Impala) on Kubernetes; each Virtual Warehouse has its own compute and can autoscale, while data and metastore are shared.

Both speak HiveQL-style SQL. Impala supports a broad ANSI SQL subset with window functions, WITH clauses, and complex types; Hive supports the same plus Hive-specific features like TRANSFORM, LATERAL VIEW, and multi-insert.

Impala requires fully qualified or USE-resolved database names and refreshes metadata manually: INVALIDATE METADATA for new tables, REFRESH table for new partitions/files on existing tables.

Hive on Tez or MapReduce executes as staged jobs; Impala executes as a distributed plan across impalad nodes with a coordinator. Impala is generally faster for interactive queries, Hive is more tolerant of huge ETL and ACID.

Window functions: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), RANK(), DENSE_RANK(), LAG/LEAD, and SUM() OVER (...). Impala and Hive both support them; use them instead of self-joins for ranking and running totals.

Complex types: ARRAY, MAP, STRUCT. Access with arr[0], map['key'], struct_col.field. Hive uses LATERAL VIEW explode(arr) tbl AS item; Impala supports explode in the FROM clause.

Date handling differs: Hive uses date_format(ts,'yyyy-MM-dd'), unix_timestamp, from_unixtime; Impala uses to_date, date_add, date_sub, trunc, and cast(ts AS timestamp). CAST between string and timestamp is common in both.

NULL semantics: comparisons with NULL yield NULL; use IS NULL / IS NOT NULL and COALESCE or NVL. Aggregates ignore NULLs; COUNT(*) counts rows, COUNT(col) counts non-null values.

Join hints and strategies: Impala supports /*+ BROADCAST / and /+ SHUFFLE /; Hive supports /+ MAPJOIN */ for small-table broadcast. Use them when the optimizer mis-picks a plan.

Profiling order: row count, null counts per column, distinct counts, min/max, and top-N frequency. In Impala use COMPUTE STATS table; in Hive use ANALYZE TABLE ... COMPUTE STATISTICS [FOR COLUMNS].

Data quality checks: duplicate key detection with GROUP BY ... HAVING COUNT(*) > 1, referential checks with LEFT JOIN ... WHERE right.key IS NULL, and range checks with BETWEEN or CASE buckets.

Deduplication pattern: ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY updated_at DESC) = 1 in a CTE, then SELECT the deduplicated rows.

Schema-on-read: raw landing tables often store strings; cast to typed columns in a staging view. Keep raw, staging, and curated layers separate so reprocessing is possible.

Partitioning strategy: choose low-cardinality, frequently filtered columns (date, region). Avoid over-partitioning (thousands of tiny files). Target files of roughly 128 MB to 512 MB.

Small-file problem: many tiny files slow Impala and Hive. Fix with INSERT ... SELECT rewriting, Hive compaction (for ACID), or Iceberg rewrite_data_files procedures.

Slowly changing dimensions: Type 1 overwrites, Type 2 adds version rows with effective/expiry dates and a current flag. Iceberg MERGE INTO simplifies upserts.

Data cleansing in SQL: TRIM, REGEXP_REPLACE for pattern cleanup, CASE for standardization, and COALESCE for defaulting. Always validate after transform by comparing row counts and null rates.

Apache Kudu is a columnar, mutable store optimized for fast scans and updates; it complements HDFS/Parquet for tables that need random reads/writes and near-real-time analytics.

Kudu tables require a primary key; the primary key defines the sort order and cannot be updated in place. Design keys to support the most common range scans.

Kudu partitioning: RANGE partitioning (e.g., by date) plus HASH partitioning on a high-cardinality column to spread writes. Use RANGE for time-based pruning and HASH to avoid hotspots.

Kudu supports INSERT, UPSERT, UPDATE, DELETE via Impala SQL (and the Kudu API). Impala is the usual SQL front end; Kudu tables appear as normal tables in the metastore.

Kudu limitations: no complex types (ARRAY/MAP), no multi-row transactions across tablets, and no secondary indexes beyond the primary key. Column count and row width affect performance.

Apache HBase is a sparse, sorted map: rows keyed by rowkey, column families, and cells versioned by timestamp. It is good for point lookups and wide sparse data, not for full scans.

HBase rowkey design: hot-spotting occurs when sequential keys (timestamps, IDs) concentrate writes on one RegionServer. Salt, hash-prefix, or reverse keys to distribute load.

HBase access from analysts: HBase shell, Phoenix (SQL over HBase with secondary indexes), or Spark HBase connector. Phoenix is preferred for SQL-style analytics; direct scans should use Get with a rowkey.

Spark SQL: use spark.sql("...") or DataFrame API. Catalyst optimizes logical plans; Tungsten handles code generation and memory. Prefer DataFrame/SQL over RDDs for analytics.

Lazy evaluation: transformations (select, filter, join) build a DAG; actions (count, show, write) trigger execution. Caching with .cache() or .persist() helps when a DataFrame is reused.

Shuffles occur on groupBy, join, and distinct. Reduce shuffle by filtering early, broadcasting small tables (broadcast(df)), and using the right number of partitions (spark.sql.shuffle.partitions).

File writing: df.write.mode('overwrite').format('parquet').partitionBy('dt').save(path). Partition columns become directories; avoid too many small partitions.

Cloudera Data Engineering (CDE) runs Spark jobs and sessions as containerized virtual clusters on Kubernetes. Analysts create jobs from Python/Scala/JAR artifacts, schedule them, and monitor runs in the CDE UI or CLI.

CDE job types: Spark (batch), Airflow (orchestration), and custom. Jobs use a virtual cluster with resource quotas; dependencies and schedules are configured per job.

Spark on Iceberg: use the Iceberg Spark runtime, CREATE TABLE ... USING iceberg, and MERGE INTO for upserts. Time travel with SELECT ... VERSION AS OF or TIMESTAMP AS OF.

Interactive analysis in CDP: use Spark Shell or CDE Sessions for exploration, then promote code to a scheduled CDE job. Persist intermediate results to Iceberg/Parquet to avoid recomputation.

BI tools connect to CDP through JDBC/ODBC drivers: Cloudera provides drivers for Impala, Hive, and Hive-on-Knox. Use the Knox gateway URL for secured clusters instead of direct host:port.

Impala JDBC/ODBC parameters: set AuthMech (0 none, 1 Kerberos, 3 username, 4 username+password), and for Knox use the gateway host with the hive/impala topology path.

Hive/Impala result caching: Impala has no result cache by default; HiveServer2 has query result caching in some versions. For dashboards, pre-aggregate into summary tables rather than scanning raw data.

Dashboard design: push filters (date, region) into the WHERE clause so partition pruning applies; avoid SELECT * on wide tables; limit high-cardinality GROUP BYs.

Semantic layer: define reusable views or BI extracts for consistent metrics. Materialized views (Hive) or Iceberg tables can serve as precomputed aggregates refreshed on a schedule.

Extract vs live: extracts (BI-engine cached) are fast but stale; live queries hit Impala/Hive directly and are fresh but load the cluster. Choose per dashboard freshness requirement.

Row-level security in BI: enforce via Ranger row filters and column masks so all tools inherit the same policy; avoid duplicating rules in each dashboard.

Export and scheduling: use CDE or Oozie/Airflow to refresh summary tables before dashboard refresh windows; align time zones explicitly (UTC vs local) to avoid date-shift errors.

Apache Ranger provides fine-grained authorization: policies on databases, tables, columns, and URLs for Hive, Impala, HDFS, HBase, and Kafka. Deny policies override allow policies.

Ranger row-level filters restrict which rows a user sees (e.g., region = 'EU'); Ranger column masking transforms values (e.g., show only last 4 digits of a card number) at query time.

Apache Atlas is the metadata and lineage service: it stores entities (tables, columns, processes) and tracks lineage from source to report. Analysts use it to trace where a metric comes from.

Cloudera SDX (Shared Data Experience) combines Ranger, Atlas, and the metastore into a shared governance layer across CDP compute clusters, so policies and lineage follow the data.

Tags in Atlas (e.g., PII, SENSITIVE) can drive Ranger tag-based policies, so classification propagates to access control without per-table policy edits.

Kerberos authenticates users; Ranger authorizes them. For BI tools, Knox provides a single authenticated gateway endpoint that proxies to Impala/Hive.

Audit: Ranger records access decisions; Atlas records metadata changes. Use Ranger audit logs to investigate denied queries and Atlas lineage to assess impact of schema changes.

Least privilege: grant at the column level where possible, use roles not individual users, and test policies with a non-admin account before rollout.

Check statistics first: run COMPUTE STATS (Impala) or ANALYZE TABLE (Hive) after loading data. Missing or stale stats cause bad join orders and broadcast choices.

Impala plan reading: EXPLAIN shows the fragment plan; look for broadcast vs partitioned joins, missing predicates, and scans of all partitions. Use SUMMARY for runtime counters.

Partition pruning: filter on the partition column with constants or stable expressions. Functions on the partition column (e.g., substr(dt,1,7)) disable pruning.

Join tuning: broadcast the small side (Impala BROADCAST hint, Spark broadcast()), or ensure both sides are bucketed/sorted on the join key to enable efficient joins. Avoid cross joins.

File and format tuning: Parquet/ORC with compression (Snappy/Zstd), target 128-512 MB files, avoid SELECT * on wide tables, and push filters into the scan.

Memory errors: Impala 'out of memory' on joins/aggregations can be fixed by increasing MEM_LIMIT, enabling spill-to-disk, or reducing data with earlier filters. Spark OOM: raise executor memory or repartition.

Concurrency: CDW virtual warehouses isolate workloads; Impala admission control queues queries by resource pool. Route heavy ETL to a separate warehouse from BI dashboards.

Diagnose slow queries: compare EXPLAIN plan to runtime SUMMARY, check skew (one reducer/partition much larger), and inspect Ranger audit and Impala logs for denied or queued queries.