31.What does the following query do?
INSERT OVERWRITE TABLE employees
PARTITION (country, state)
SELECT …, se.cnty, se.st
FROM staged_employees se;
It creates partition on table employees with partition values coming from the columns in the select clause. It is called Dynamic partition insert.
32.What is a Table generating Function on hive?
A table generating function is a function which takes a single column as argument and expands it to multiple column or rows. Example exploe()
33.How can Hive avoid mapreduce?
If we set the property hive.exec.mode.local.auto to true then hive will avoid mapreduce to fetch query results.
34.What is the difference between LIKE and RLIKE operators in Hive?
The LIKE operator behaves the same way as the regular SQL operators used in select queries. Example −
street_name like ‘%Chi’
But the RLIKE operator uses more advance regular expressions which are available in java
Example − street_name RLIKE ‘.*(Chi|Oho).*’ which will select any word which has either chi or oho in it.
35.Is it possible to create Cartesian join between 2 tables, using Hive?
No. As this kind of Join can not be implemented in mapreduce
36.As part of Optimizing the queries in HIve, what should be the order of table size in a join query?
In a join query the smallest table to be taken in the first position and largest table should be taken in the last position.
37.What is the usefulness of the DISTRIBUTED BY clause in Hive?
It controls ho wthe map output is reduced among the reducers. It is useful in case of streaming data
38.How will you convert the string ’51.2’ to a float value in the price column?
Select cast(price as FLOAT)
39.What will be the result when you do cast(‘abc’ as INT)?
Hive will return NULL
40.Can the name of a view be same as the name of a hive table?
No. The name of a view must be unique whne compared to all other tables and views present in the same database.