Skip to main content
Displaying 1 - 10 of 37

Hive MCQ: IceCream Palace is a famous Ice cream outlet chain in India.

Based on the information given below answer the questions.

IceCream Palace is a famous Ice cream outlet chain in India. Assume that the following details are recorded for deliveries of orders placed online:

  • Order ID - alpha-numeric code of length 6, unique across outlets, not null.
  • Customer ID - null for non-registered users and unique alphanumeric code of length 7 for registered users across outlets
  • Details - contains the ice cream codes (alpha-numeric of length 5) and corresponding numbers ordered
  • Customer Name - string of length 20, not null
  • Delivery Address - that consists of:
  • House No - holds the number of the house/building
  • Street - name of street/locality - of length 50
  • City - name of the city - of length 25
  • State - name of the state - of length 20
  • Postal Code - alphanumeric of length 8

Q1: Select the appropriate query to create an external table named OrderDelivery.

A. 

CREATE EXTERNAL TABLE IF NOT EXISTS OrderDelivery(
OrderID VARCHAR(6),
CustomerID VARCHAR(7),
Details  MAP<VARCHAR(5), INT>,
CustomerName  STRING,
HouseNo INT,
Street STRING,
City STRING,
State STRING,
PostalCode VARCHAR(8))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

B.

CREATE TABLE IF NOT EXISTS OrderDelivery(
OrderID VARCHAR(6),
CustomerID VARCHAR(7),
Details  MAP<VARCHAR(5), INT>,
CustomerName  STRING
HouseNo INT,
Street STRING,
City STRING,
State STRING,
PostalCode VARCHAR(8))
ROW FORMAT UNLIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

 C.

CREATE EXTERNAL TABLE IF NOT EXISTS OrderDelivery(
OrderID VARCHAR(6),
CustomerID VARCHAR(7),
Details STRING,
CustomerName  STRING
HouseNo INT,
State STRING,
PostalCode INT
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

 D.

CREATE EXTERNAL TABLE IF NOT EXISTS OrderDelivery(
OrderID VARCHAR(6),
CustomerID VARCHAR(7),
Details  MAP <VARCHAR(5), INT>,
CustomerName  STRING
HouseNo INT,
Street STRING,
City STRING,
State STRING,
PostalCode VARCHAR(8))
ROW FORMAT DELIMITED
STORED AS TEXTFILE;

Correct Answer: A [The Details column can be of MAP type or STRUCT Type. Based on this the corresponding delimiter details has to be provided. The other columns also need to be provided appropriate data types and details of corresponding delimiters need to be provided for each data type.]

Q2: Given that data is available at location “/test/iceCreamOrders” in multiple files, select the query to point the “OrderDelivery” table to the correct data files.

A. ALTER TABLE OrderDelivery SET LOCATION ‘/test/iceCreamOrders/’;

B. MODIFY TABLE OrderDelivery SET LOCATION ‘/test/iceCreamOrders/’;

C. FOR TABLE OrderDelivery SET LOCATION ‘/test/iceCreamOrders/’;

D. ALTER TABLE OrderDelivery UPDATE LOCATION = ‘/test/iceCreamOrders/’;

Correct Answer: A

Q3: Out of the listed columns, identify the one suitable for bucketing:

  • City
  • Customer ID
  • Order ID
  • Postal Code

Correct Answer: Order ID. Out of the listed, attributes - the order id column has the maximum cardinality and is suitable for bucketing. The customer id column is not preferred - as it may even include null values. The postal codes - even though having higher cardinality in comparison to city attribute - is not a good choice for bucketing.

Q4: Consider that the data is specifically used to address delivery issues and also promote delivery offers in the different cities. For such a use case, out of the listed pairs, identify the suitable combination and order for partitioning to improve query performance.

  • Postal Code, State
  • City, State
  • State, City
  • Street, City
  • City, Street

Correct Answer: Out of the listed, this pair and the ordering of partitioning keys is the suitable one to improve query performance. The “Postal Code, State” and “Street, City” options are the worst combination of partition keys for this query - as it creates a large no. of directories - with a lot of small files/no files. The II option is ruled out as this again creates a large no. of directories (one for each city) - with one subdirectory (state) as each city belongs to one state. The “City, Street” option is ruled out as offers are specific to city and not to the streets. So this partition combination wouldn’t help much.

Hive HQL: Identify the correct statement below.

Explanation: Hive is not optimized for row-level operations - and generally does not support it. The behaviour of the DROP TABLE statement depends on the type of the table - if it is external only the metadata is dropped. If it is a managed table, both data and metadata are dropped. With RESTRICT option, the DROP DATABASE statement fails if the database contains tables.

Select the correct statement about internal and external tables. (Note: Multiple options may be correct.)

Explanation: Internal tables are stored in the Hive warehouse directory, and deleting the internal table drops the metadata information as well as table data from the Hive warehouse directory. External tables are stored in the HDFS itself, and dropping an external table simply deletes the metadata information from the Hive warehouse directory and makes Hive unknown to the external tables. The data of the internal table is stored in the Hive warehouse directory.

Subscribe to Apache Hive MCQ

About

Elix is a premium wordpress theme for portfolio, freelancer, design agencies and a wide range of other design institutions.