Apache Hive test (Intermediate)

Sample questions

5 sample questions from the Apache Hive test, with the answers. Samples are taken out of the live test, so you will not meet these when you sit it.

1. You are working on a project that requires Hive ACID transaction support for a managed table. Which of the following actions would correctly enable this functionality?

   1. Create a managed table using ORC format and enable ACID transactions.
   2. Create a managed table using TEXTFILE format and enable ACID transactions.
   3. Create an external table using ORC format and enable ACID transactions.
   4. Create an external table using Parquet format and enable ACID transactions.

   Answer: A. Create a managed table using ORC format and enable ACID transactions.

   Hive ACID tables require managed tables stored in ORC format and must have ACID transactions enabled. External tables do not support ACID transactions, and using non-ORC formats like TEXTFILE or Parquet would not provide the required ACID guarantees. Additionally, ACID support is only available in Hive 3 and later versions when using ORC.

2. What is the issue with the following Hive query: SELECT * FROM large_table ORDER BY some_column;

   1. The query is using a MapReduce execution engine, which is deprecated and should be replaced with Tez.
   2. The query is not using partition pruning, which may lead to reading unnecessary data from HDFS.
   3. The query is using a single reducer for the ORDER BY operation, which can cause performance issues with large datasets.
   4. The query is not using predicate pushdown, which may result in unnecessary data scanning.

   Answer: C. The query is using a single reducer for the ORDER BY operation, which can cause performance issues with large datasets.

   The query uses ORDER BY without a LIMIT clause, which forces Hive to use a single reducer to sort the entire dataset. This can lead to performance bottlenecks and long execution times for large datasets. To improve performance, the query should use SORT BY instead of ORDER BY if global sorting is not required, or add a LIMIT clause to restrict the number of rows processed.

3. In a Hive query, how should a subquery be properly structured when used as a derived table in the FROM clause?

   1. The subquery can be used in the WHERE clause without any restrictions.
   2. The subquery must be written as a separate query and executed before the main query.
   3. The subquery must be enclosed in parentheses and placed in the FROM clause as a derived table.
   4. The subquery must be placed directly after the SELECT clause without any enclosing syntax.

   Answer: C. The subquery must be enclosed in parentheses and placed in the FROM clause as a derived table.

   In Hive, subqueries are used to create derived tables, which are temporary tables that exist only for the duration of the query. To use a subquery in the FROM clause, it must be enclosed in parentheses. This allows the outer query to treat the result of the subquery as a table. The other options are incorrect because they either describe invalid syntax or misrepresent how subqueries are used in Hive.

4. Which of the following best describes the appropriate use of ORC and Parquet compression formats in Hive?

   1. ORC compression is typically chosen for Hive ACID tables, while Parquet is preferred for cross-tool compatibility and data sharing across systems like Spark, Impala, and Presto.
   2. ORC compression is used for text files, while Parquet is used for binary data.
   3. ORC compression is used for row-based storage, while Parquet is used for column-based storage.
   4. ORC compression is better for data sharing between systems, while Parquet is used for Hive ACID tables.

   Answer: A. ORC compression is typically chosen for Hive ACID tables, while Parquet is preferred for cross-tool compatibility and data sharing across systems like Spark, Impala, and Presto.

   ORC (Optimized Row Columnar) is specifically designed for Hive and is required for Hive ACID tables, offering efficient compression and storage with features like row indexes and bloom filters. Parquet is a more widely adopted columnar format that supports data sharing across multiple systems, making it a better choice when compatibility with other tools is needed. Choosing ORC over Parquet is not about compression efficiency alone but about use case and tooling compatibility.

5. Which Hive configuration setting enables parallel execution of independent stages in a query to improve performance?

   1. hive.exec.parallelism=1
   2. hive.exec.parallel=false
   3. hive.exec.parallel=true
   4. hive.exec.parallel.mode=strict

   Answer: C. hive.exec.parallel=true

   Hive allows parallel execution of independent stages in a query by setting hive.exec.parallel=true. This is a common configuration to improve query performance by utilizing multiple reducers. The other options are either unrelated to parallelism or incorrect configurations.