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

Cloudera CDP Data Analyst practice questions

One question at a time. Build your Cloudera CDP Data Analyst understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Data Analysis Foundations on CDP

In a CDP environment managed through Cloudera Data Warehouse, which component is responsible for storing table data as files in HDFS or object storage while metadata is tracked in a metastore?

  1. Apache Hive tables Correct answer.
  2. Cloudera Manager parcels
  3. YARN ResourceManager queues
  4. Apache Ranger audit logs

Explanation

Hive tables persist their row data as files in HDFS or cloud object storage, while table and partition definitions live in the Hive metastore. The other components handle resource scheduling, authorization auditing, and software distribution rather than analytical table storage.

Question 2Data Analysis Foundations on CDP

An analyst needs a query engine on CDP that supports ACID transactions, upserts, and fine-grained row-level changes on large tables. Which engine should they choose?

  1. Apache Impala on Parquet with no transactional configuration
  2. Apache Hive on Tez with a text file format
  3. Apache Hive ACID with ORC and transactional tables Correct answer.
  4. Apache Solr collection with stored fields

Explanation

Hive ACID tables, typically stored as ORC, support inserts, updates, deletes, and merge operations with transactional guarantees. Plain text tables and non-transactional Impala Parquet tables do not provide row-level mutation semantics, and Solr is a search index rather than a transactional table engine.

Question 3Data Analysis Foundations on CDP

When writing a query in Hive or Impala, which clause combination applies row filtering before aggregation so that fewer rows are processed by GROUP BY?

  1. HAVING before GROUP BY
  2. LIMIT before GROUP BY
  3. WHERE before GROUP BY Correct answer.
  4. ORDER BY before GROUP BY

Explanation

The WHERE clause filters individual rows before grouping and aggregation occur, reducing the input to GROUP BY. HAVING filters aggregated results after grouping, while ORDER BY and LIMIT act on the final result set.

Question 4Data Analysis Foundations on CDP

A partitioned Hive table is partitioned by `dt` (a string date) and stored as Parquet. A query filters on `dt = '2023-05-01'`. What is the primary performance benefit of this filter?

  1. It converts the table into an ACID transactional table
  2. Partition pruning avoids reading directories for other dates Correct answer.
  3. It moves the data into the Hive metastore for faster access
  4. It automatically compresses the Parquet files further

Explanation

Partition columns map to directory structure, so a predicate on the partition column lets the engine skip directories that cannot match. This pruning reduces the number of files scanned and I/O performed, without changing compression or table type.

Question 5Data Analysis Foundations on CDP

Which file format typically gives the best compression and predicate pushdown for analytical scans in Impala when the data is columnar and nested structures are not required?

  1. CSV
  2. Parquet Correct answer.
  3. Avro
  4. JSON

Explanation

Parquet is a columnar format with per-column statistics and encoding that enable efficient compression and predicate pushdown during scans. CSV, Avro, and JSON are row-oriented or text formats with weaker analytical scan performance.

Question 6Data Analysis Foundations on CDP

An analyst runs a query joining a 5 TB fact table to a 2 MB dimension table in Hive. Which join strategy is most appropriate to avoid shuffling the large table?

  1. Sort-merge join with both tables repartitioned
  2. Broadcast (map-side) join with the small dimension Correct answer.
  3. Full outer join with no hints
  4. Cross join followed by filtering

Explanation

A broadcast join distributes the small dimension table to each mapper so the large fact table can be streamed without a full shuffle. Sort-merge joins repartition both sides, and cross joins produce explosive intermediate results.

Question 7Data Analysis Foundations on CDP

In Impala, which statement about `COMPUTE STATS` is accurate for query planning?

  1. It permanently rewrites the table into a sorted order
  2. It gathers table and column statistics used by the optimizer to choose join order and strategies Correct answer.
  3. It encrypts column values at rest
  4. It enforces primary key constraints on the table

Explanation

COMPUTE STATS collects row counts, distinct value counts, and size information that the Impala optimizer uses to estimate cardinalities and select join strategies. It does not sort data permanently, enforce constraints, or perform encryption.

Question 8Data Analysis Foundations on CDP

A table has many small files because data was streamed in with frequent micro-batch inserts. Which maintenance action best improves scan performance?

  1. Disable the Hive metastore
  2. Convert the table to a CSV format
  3. Run a compaction or rewrite operation to merge small files into larger ones Correct answer.
  4. Increase the replication factor of each small file

Explanation

Small files create per-file overhead in planning and reading; compaction or a rewrite merges them into larger files that scan more efficiently. Replication increases storage cost without fixing file count, and disabling the metastore breaks table access.

Question 9Data Analysis Foundations on CDP

Which CDP service provides a SQL interface over Hive tables and is often used for interactive analytical queries with a shared metastore?

  1. Apache ZooKeeper
  2. Apache Knox
  3. Apache HiveServer2 Correct answer.
  4. Apache Oozie

Explanation

HiveServer2 accepts JDBC and ODBC connections and executes SQL against Hive tables using the shared metastore. ZooKeeper coordinates services, Knox provides gateway security, and Oozie orchestrates workflows.

Question 10Data Analysis Foundations on CDP

An analyst wants to compute a running total of daily revenue ordered by date. Which SQL feature is designed for this?

  1. A window function with an ORDER BY inside the OVER clause Correct answer.
  2. A GROUP BY with ROLLUP
  3. A self cross join of the table
  4. A DISTINCT count on the date column

Explanation

A window function such as SUM(revenue) OVER (ORDER BY dt) computes a cumulative total across ordered rows without collapsing them. GROUP BY ROLLUP produces subtotal aggregates rather than a per-row running total.

Question 11Data Analysis Foundations on CDP

What does the `EXPLAIN` statement return in Hive or Impala?

  1. The actual result rows of the query
  2. The execution plan showing operators and data flow for a query Correct answer.
  3. The list of users authorized to run the query
  4. The physical storage location of each data block

Explanation

EXPLAIN displays the query plan, including scan, join, and aggregation operators and how data flows between them, which helps diagnose performance. It does not execute the query to return rows or report authorization or block locations.

Question 12Data Analysis Foundations on CDP

A Hive query returns NULL for many rows when joining on a key column that contains empty strings in one table and NULLs in another. What is the most likely cause?

  1. The join used a broadcast hint
  2. The tables use different file formats
  3. The metastore is out of sync
  4. NULL never equals any value, including another NULL, in join predicates Correct answer.

Explanation

In SQL, NULL represents an unknown value and does not compare equal to anything, so join predicates on NULL keys do not match. Empty strings and NULLs are distinct, so rows with NULL keys drop out of an inner join.

Question 13Data Analysis Foundations on CDP

Which statement correctly describes the difference between `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` in Hive?

  1. RANK always returns unique numbers while ROW_NUMBER allows ties
  2. ROW_NUMBER gives unique sequential numbers; RANK leaves gaps after ties; DENSE_RANK leaves no gaps Correct answer.
  3. All three assign identical numbers for tied values
  4. DENSE_RANK skips numbers after ties while RANK does not

Explanation

ROW_NUMBER assigns a distinct sequential value to each row regardless of ties. RANK assigns the same rank to ties but skips subsequent numbers, while DENSE_RANK assigns the same rank to ties without skipping.

Question 14Data Analysis Foundations on CDP

An analyst needs to load only new and changed records from a source into a Hive ACID table on a recurring basis. Which operation is most appropriate?

  1. DROP and recreate the table each run
  2. TRUNCATE then INSERT the full source
  3. MERGE INTO using the source key to update, insert, or delete Correct answer.
  4. INSERT OVERWRITE the entire table each run

Explanation

MERGE INTO matches source and target rows on a key and applies conditional updates, inserts, and deletes, which is the standard incremental upsert pattern. Full overwrite, truncate, or drop-and-recreate reloads all data and loses incremental efficiency.

Question 15Data Analysis Foundations on CDP

Which statement about external tables in Hive is correct?

  1. External tables must use the text file format
  2. Dropping an external table always deletes the underlying data files
  3. Dropping an external table removes the table definition but leaves the underlying data files intact Correct answer.
  4. External tables cannot be queried with Impala

Explanation

For external tables, Hive manages metadata only, so a DROP removes the table definition while the data files remain in their storage location. Managed tables, by contrast, delete their data when dropped.

Question 16Data Analysis Foundations on CDP

A query groups sales by region and month. The analyst wants subtotal rows for each region plus a grand total in a single result set. Which SQL construct produces this?

  1. A LEFT JOIN to the same table
  2. A UNION of two SELECT DISTINCT queries
  3. A window function with PARTITION BY only
  4. GROUPING SETS, ROLLUP, or CUBE Correct answer.

Explanation

GROUPING SETS, ROLLUP, and CUBE generate multiple aggregation levels, including subtotals and grand totals, in one query. Simple window partitioning or unions of distinct values do not produce the hierarchical subtotal rows.

Question 17Data Analysis Foundations on CDP

Which CDP component centrally defines and enforces column-masking and row-filter policies for Hive and Impala queries?

  1. Apache Sqoop
  2. Apache HBase
  3. Apache Ranger Correct answer.
  4. Apache Flume

Explanation

Apache Ranger provides centralized authorization policies, including column masking and row-level filters, for services such as Hive and Impala. HBase is a NoSQL store, Flume ingests streaming data, and Sqoop moves bulk data.

Question 18Data Analysis Foundations on CDP

An analyst notices that a query against a Parquet table is slow and the EXPLAIN plan shows a full table scan despite a filter on a non-partition column. Which action is most likely to enable effective data skipping?

  1. Collect column statistics and ensure the filter column has min/max statistics in the Parquet footers Correct answer.
  2. Add more partitions on a high-cardinality column
  3. Increase the number of reducers
  4. Convert the table to CSV

Explanation

Parquet stores per-row-group min/max statistics in file footers; when column statistics are accurate, the engine can skip row groups that cannot satisfy the predicate. Partitioning on a high-cardinality column creates many small partitions, and CSV lacks these statistics.

Question 19Data Analysis Foundations on CDP

Which SQL data type should be used in Hive to store a fixed-precision monetary amount without floating-point rounding error?

  1. FLOAT
  2. DECIMAL Correct answer.
  3. STRING
  4. DOUBLE

Explanation

DECIMAL stores exact fixed-point values with defined precision and scale, which is appropriate for monetary amounts. FLOAT and DOUBLE use binary floating point and can introduce rounding error, while STRING does not support arithmetic reliably.

Question 20Data Analysis Foundations on CDP

A table contains duplicate rows because the same event was ingested twice. Which approach removes duplicates while keeping one copy of each event?

  1. Use a CROSS JOIN with a numbers table
  2. Use UNION ALL to combine the table with itself
  3. Use SELECT DISTINCT on all columns or ROW_NUMBER() partitioned by the event key and filter to row 1 Correct answer.
  4. Use ORDER BY without a filter

Explanation

SELECT DISTINCT removes exact duplicate rows, and ROW_NUMBER() over the event key with a filter for row 1 keeps a single copy when a tiebreaker is needed. UNION ALL and cross joins increase rows rather than removing duplicates.

Question 21Data Analysis Foundations on CDP

In Impala, what is the effect of the `/* +BROADCAST */` hint on a join?

  1. It converts the join into a cross join
  2. It directs the planner to broadcast the hinted table to all nodes for a map-side join Correct answer.
  3. It forces the query to run on a single executor
  4. It disables predicate pushdown for the query

Explanation

The BROADCAST hint tells the planner to send the hinted table to every node so the other side can be streamed locally, avoiding a shuffle of the larger table. It does not restrict execution to one node or change join semantics.

Question 22Data Analysis Foundations on CDP

Which statement best describes how the Hive metastore relates to Impala queries on CDP?

  1. Impala maintains a completely separate catalog that cannot see Hive tables
  2. Impala reads table definitions from the shared metastore, so Hive-created tables are visible to Impala Correct answer.
  3. Impala requires tables to be recreated manually in Hive before querying
  4. The metastore stores the actual row data for Impala queries

Explanation

Impala and Hive share the metastore, so tables created in Hive are generally queryable in Impala after metadata refresh. The metastore holds metadata, not row data, and Impala does not keep an isolated catalog.

Question 23Data Analysis Foundations on CDP

An analyst wants to identify the top three products by revenue within each category. Which SQL pattern is most appropriate?

  1. A window function ranking products by revenue partitioned by category, filtered to rank <= 3 Correct answer.
  2. A DISTINCT count of products per category
  3. A GROUP BY category with a global LIMIT 3
  4. A self join on product ID

Explanation

Ranking with ROW_NUMBER() or RANK() partitioned by category and ordered by revenue, then filtering to the top three, returns the top products per category. A global LIMIT returns only three rows overall, not three per category.

Question 24Data Analysis Foundations on CDP

During analysis of a large join, an analyst observes that one side of the join is much smaller than the other and that the small side is being shuffled. Which change is most likely to improve performance?

  1. Broadcast the small side to avoid shuffling it across the cluster Correct answer.
  2. Add a DISTINCT to the large side
  3. Convert both sides to text format
  4. Increase the number of partitions for both sides equally

Explanation

When one input is small, broadcasting it to every node lets the large input stream locally, eliminating the shuffle of the small side. Increasing partitions or converting to text adds overhead rather than reducing data movement.

Question 25SQL Querying with Apache Hive and Apache Impala

In Apache Hive, which clause is evaluated before the GROUP BY clause when a query contains both WHERE and GROUP BY?

  1. GROUP BY
  2. WHERE Correct answer.
  3. ORDER BY
  4. HAVING

Explanation

The WHERE clause filters individual rows before any grouping occurs, so it is logically evaluated ahead of GROUP BY. HAVING then filters the aggregated groups and ORDER BY runs last on the result set.

Question 26SQL Querying with Apache Hive and Apache Impala

Which statement correctly describes how Impala handles a query that references a table whose metadata has changed since the last INVALIDATE METADATA or REFRESH?

  1. Impala may return stale results or errors until metadata is refreshed or invalidated Correct answer.
  2. Impala blocks all queries until the table is recreated
  3. Impala silently drops the changed columns from the result
  4. Impala automatically re-reads the Hive Metastore on every query

Explanation

Impala caches table metadata, so after external changes such as adding a partition or altering a table in Hive, the cached metadata can be stale. You resolve this by running REFRESH on the affected table or INVALIDATE METADATA for broader changes.

Question 27SQL Querying with Apache Hive and Apache Impala

You need to return the top three salary values per department in Hive. Which construct is designed for this?

  1. A CROSS JOIN of the employee table with itself
  2. A DISTINCT on department and salary
  3. A window function with ROW_NUMBER partitioned by department Correct answer.
  4. A GROUP BY on department with MAX(salary)

Explanation

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) assigns a rank within each department, and filtering rank <= 3 returns the top three per group. This is the standard analytic-function approach for per-group top-N queries.

Question 28SQL Querying with Apache Hive and Apache Impala

In Impala, which data type should you use for a column that stores whole numbers up to several billion and is used in arithmetic joins?

  1. STRING
  2. BOOLEAN
  3. TINYINT
  4. BIGINT Correct answer.

Explanation

BIGINT is a 64-bit signed integer and comfortably holds values in the billions. TINYINT is only 8 bits and would overflow, while STRING and BOOLEAN are not numeric integer types.

Question 29SQL Querying with Apache Hive and Apache Impala

Which Hive feature lets you define a table whose data files are managed outside Hive, so that dropping the table does not delete the underlying data?

  1. An external table Correct answer.
  2. A managed table
  3. A temporary table
  4. A view

Explanation

External tables store only metadata in the Metastore; the data files remain in their location and are not removed when the table is dropped. Managed tables, by contrast, delete their data on DROP.

Question 30SQL Querying with Apache Hive and Apache Impala

In Hive, which join type returns rows from the left table even when no matching row exists in the right table, filling right-side columns with NULL?

  1. LEFT OUTER JOIN Correct answer.
  2. RIGHT OUTER JOIN
  3. FULL OUTER JOIN
  4. INNER JOIN

Explanation

A LEFT OUTER JOIN preserves all rows from the left input and supplies NULLs for unmatched right-side columns. INNER JOIN drops unmatched rows, and the other outer joins preserve different sides.

Question 31SQL Querying with Apache Hive and Apache Impala

A Hive table is partitioned by year and month. Which WHERE predicate allows partition pruning to skip irrelevant partitions?

  1. WHERE year + 0 = 2023
  2. WHERE concat(year, month) = '202305'
  3. WHERE year = 2023 AND month = '05' Correct answer.
  4. WHERE substr(cast(year AS STRING), 1, 4) = '2023'

Explanation

Direct equality predicates on the partition columns let Hive eliminate partitions without scanning them. Wrapping partition columns in functions prevents pruning because the optimizer cannot map the expression to partition values.

Question 32SQL Querying with Apache Hive and Apache Impala

Which Impala statement recomputes table and column statistics that the optimizer uses for join ordering and scan planning?

  1. ANALYZE TABLE table_name COMPUTE STATISTICS
  2. REFRESH table_name
  3. INVALIDATE METADATA table_name
  4. COMPUTE STATS table_name Correct answer.

Explanation

COMPUTE STATS gathers row counts, distinct value estimates, and size information that Impala's cost-based optimizer relies on. REFRESH and INVALIDATE METADATA only update cached metadata and do not collect column statistics.

Question 33SQL Querying with Apache Hive and Apache Impala

In Hive, which function returns the current date as a DATE value without a time component?

  1. unix_timestamp()
  2. current_timestamp()
  3. now()
  4. current_date() Correct answer.

Explanation

current_date() returns today's date as a DATE. now() and current_timestamp() return a timestamp including time, and unix_timestamp() returns seconds since the epoch.

Question 34SQL Querying with Apache Hive and Apache Impala

In Impala, what does the CAST(amount AS DECIMAL(10,2)) expression do to a value of 12.345?

  1. Raises an error because of the extra digit
  2. Returns 12.345 unchanged
  3. Rounds to 12.35 Correct answer.
  4. Truncates to 12.34

Explanation

Casting to DECIMAL(10,2) limits the scale to two digits, and Impala rounds the value to the nearest representable value. Thus 12.345 becomes 12.35 rather than being truncated.

Question 35SQL Querying with Apache Hive and Apache Impala

Which statement about Hive and Impala string comparison is accurate?

  1. Impala is case-insensitive while Hive is case-sensitive
  2. Both perform case-insensitive comparisons by default
  3. Both treat string comparisons as case-sensitive by default Correct answer.
  4. Hive is case-insensitive while Impala is case-sensitive

Explanation

String equality and ordering comparisons in both Hive and Impala are case-sensitive, so 'Apple' and 'apple' are different values. Use lower() or upper() to normalize when case-insensitive matching is needed.

Question 36SQL Querying with Apache Hive and Apache Impala

You want to combine the result sets of two Hive queries with identical column types, keeping duplicate rows. Which operator should you use?

  1. UNION
  2. INTERSECT
  3. JOIN
  4. UNION ALL Correct answer.

Explanation

UNION ALL concatenates result sets and preserves duplicates, which is also faster because no deduplication step is required. Plain UNION removes duplicate rows, and the other operators do not concatenate rows.

Question 37SQL Querying with Apache Hive and Apache Impala

In Hive, which aggregate function returns the approximate most frequent value of a column using a sketch?

  1. percentile_approx Correct answer.
  2. histogram_numeric
  3. count(DISTINCT)
  4. approx_count_distinct

Explanation

percentile_approx estimates percentiles using a sketch and can be used to derive approximate distribution statistics. approx_count_distinct estimates cardinality, and histogram_numeric builds a histogram, not a most-frequent value.

Question 38SQL Querying with Apache Hive and Apache Impala

A Hive query groups by region and returns count(*) and sum(amount). Which clause filters out regions whose summed amount is below 1000?

  1. HAVING sum(amount) >= 1000 Correct answer.
  2. WHERE amount >= 1000
  3. WHERE sum(amount) >= 1000
  4. ORDER BY sum(amount) >= 1000

Explanation

HAVING is evaluated after aggregation and can reference aggregate expressions such as sum(amount). WHERE runs before grouping and cannot reference aggregates on the grouped result.

Question 39SQL Querying with Apache Hive and Apache Impala

Which Impala clause is used to write a query result into a new Parquet table in one statement?

  1. INSERT OVERWRITE new_tbl SELECT ...
  2. SELECT ... INTO new_tbl
  3. CREATE TABLE new_tbl LIKE old_tbl
  4. CREATE TABLE new_tbl STORED AS PARQUET AS SELECT ... Correct answer.

Explanation

Impala supports CREATE TABLE ... AS SELECT (CTAS) with a STORED AS clause to materialize query results into a new table in a chosen file format. The other options either require the target to exist or are not valid Impala syntax.

Question 40SQL Querying with Apache Hive and Apache Impala

In Hive, which expression safely handles a NULL in the column discount by substituting 0 before arithmetic?

  1. discount + 0
  2. coalesce(discount, 0) Correct answer.
  3. if(discount = NULL, 0, discount)
  4. nvl(discount)

Explanation

coalesce returns the first non-NULL argument, so coalesce(discount, 0) substitutes 0 when discount is NULL. Comparing with = NULL yields NULL rather than true, so that test never works as intended.

Question 41SQL Querying with Apache Hive and Apache Impala

Which statement about Impala's support for UPDATE and DELETE on tables is correct?

  1. They are supported on all Parquet tables
  2. They are supported only on Kudu tables Correct answer.
  3. They are supported only on Avro tables
  4. They are never supported in Impala

Explanation

Impala supports row-level UPDATE and DELETE only on Kudu tables, which provide the necessary primary-key and mutation support. HDFS-backed formats such as Parquet are append-oriented and do not support these operations.

Question 42SQL Querying with Apache Hive and Apache Impala

A Hive query uses ORDER BY on a very large table and runs slowly. What is the most likely cause?

  1. ORDER BY forces all rows through a single reducer to produce a global sort Correct answer.
  2. ORDER BY disables predicate pushdown
  3. ORDER BY cannot use more than one mapper
  4. ORDER BY requires the table to be partitioned

Explanation

ORDER BY guarantees a total ordering, which requires funneling all rows to one reducer and can bottleneck large datasets. SORT BY or DISTRIBUTE BY with SORT BY sorts within partitions and parallelizes better.

Question 43SQL Querying with Apache Hive and Apache Impala

In Impala, which function extracts the year from a TIMESTAMP column named event_ts?

  1. extract_year(event_ts)
  2. year(event_ts) Correct answer.
  3. datepart(event_ts, 'year')
  4. to_year(event_ts)

Explanation

Impala provides the year() function, which returns the year component of a timestamp or date. The other function names are not part of Impala's built-in date function library.

Question 44SQL Querying with Apache Hive and Apache Impala

Which Hive construct allows a query to reference a named subquery in the FROM clause for readability and reuse within that statement?

  1. A common table expression defined with WITH Correct answer.
  2. A temporary table created with CREATE TEMPORARY TABLE
  3. A materialized view
  4. A lateral view

Explanation

The WITH clause defines a common table expression that can be referenced in the main query, improving readability for complex logic. It is scoped to that statement rather than persisted like a table.

Question 45SQL Querying with Apache Hive and Apache Impala

In Impala, what happens when you join two large tables without statistics and the optimizer must choose a join order?

  1. The query fails immediately with a missing-statistics error
  2. The optimizer may pick a poor join order, causing excessive network and memory use Correct answer.
  3. The join is silently converted to a cross join
  4. The optimizer always broadcasts the right table regardless of size

Explanation

Without statistics, Impala's cost-based optimizer lacks size and cardinality estimates and may choose a suboptimal join strategy or order. Running COMPUTE STATS gives it the information needed for better plans.

Question 46SQL Querying with Apache Hive and Apache Impala

Which Hive serde or storage choice is most appropriate for a table whose data is stored as newline-delimited JSON?

  1. The ORC columnar format
  2. The JsonSerDe with the appropriate input format Correct answer.
  3. The Parquet columnar format
  4. The default LazySimpleSerDe with comma delimiters

Explanation

JSON data should be read with a JSON SerDe so that fields are parsed from JSON objects into columns. Delimited or columnar formats expect different physical layouts and would misread the data.

Question 47SQL Querying with Apache Hive and Apache Impala

In Impala, which statement about the LIMIT clause combined with ORDER BY is correct?

  1. LIMIT is applied first, then the retained rows are sorted
  2. ORDER BY is applied before LIMIT, so LIMIT returns the top rows of the sorted result Correct answer.
  3. LIMIT and ORDER BY cannot be used together in Impala
  4. LIMIT requires a GROUP BY clause

Explanation

The sort produces an ordered result set and LIMIT then restricts it to the first N rows, yielding a top-N result. This ordering of operations is what makes ORDER BY ... LIMIT useful for top-N queries.

Question 48SQL Querying with Apache Hive and Apache Impala

A Hive query on a partitioned ORC table filters on a non-partition column with a highly selective predicate. Which optimization most directly reduces the data read?

  1. Increasing the number of reducers
  2. Predicate pushdown combined with ORC stripe and row-group skipping Correct answer.
  3. Setting the output compression codec to gzip
  4. Using DISTRIBUTE BY on the filter column

Explanation

ORC stores min/max indexes per stripe and row group, so predicate pushdown lets the reader skip blocks that cannot satisfy the filter. This reduces I/O far more than tuning reducers or compression.

Question 49SQL Querying with Apache Hive and Apache Impala

Which Impala statement changes the HDFS location of an existing table's data without rewriting the files?

  1. ALTER TABLE tbl RENAME TO new_tbl
  2. ALTER TABLE tbl ADD COLUMNS (c INT)
  3. DROP TABLE tbl
  4. ALTER TABLE tbl SET LOCATION 'hdfs://path' Correct answer.

Explanation

ALTER TABLE ... SET LOCATION points the table metadata at a new directory, leaving the existing files in place. Renaming, adding columns, or dropping the table do not relocate the data.

Question 50SQL Querying with Apache Hive and Apache Impala

In Hive, which expression returns the number of rows in a group where the column customer_id is NULL for every row in that group?

  1. sum(customer_id)
  2. count(DISTINCT customer_id)
  3. count(*) Correct answer.
  4. count(customer_id)

Explanation

count(*) counts rows regardless of NULL values, so it still reports the group size. count(col) and count(DISTINCT col) skip NULLs, and sum over an all-NULL column returns NULL rather than a row count.