Hive Question & Answers

1.What is Hive?
2.What is Hive Metastore?
3.What kind of datawarehouse application is suitable for Hive?
4.How can the columns of a table in hive be written to a file?
5.CONCAT function in Hive with Example?
6.REPEAT function in Hive with example?
7.TRIM function in Hive with example?
8.REVERSE function in Hive with example?
9.LOWER or LCASE function in Hive with example?
10.UPPER or UCASE function in Hive with example?
11.Double type in Hive – Important points?
12.Rename a table in Hive – How to do it?
13.How to change a column data type in Hive?
14.Difference between order by and sort by in hive?
15.RLIKE in Hive?
16.Difference between external table and internal table in HIVE ?

Answers –
1. It is a data Warehousing package built on top of Hadoop which is used for data analysis, targeted towards users comfortable with SQL. HIve was developed by Facebook. It is used to process structured data.

2.The metastore is service and database that can be configured in different ways. The default Hive configuration is that Hive driver, metastore interface and the db (derby) all use the same JVM. Metastore has all the table schema and partition details.

3. Data Warehouse application which supports web and JDBC clients is suitable for hive, and also the one which is similar to SQL languages.

5. CONCAT( string str1, string str2… )

The CONCAT function concatenates all the stings.
Example: CONCAT(‘hadoop’,’-‘,’hive’) returns ‘hadoop-hive’

6. REPEAT( string str, int n )

The REPEAT function repeats the specified string n times.
Example: REPEAT(‘hive’,2) returns ‘hivehive’

7. TRIM( string str )

The TRIM function removes both the trailing and leading spaces from the string.
Example: LTRIM(‘ hive ‘) returns ‘hive’

8. REVERSE( string str )

The REVERSE function gives the reversed string
Example: REVERSE(‘hive’) returns ‘evih’

9. LOWER( string str ), LCASE( string str )

The LOWER or LCASE function converts the string into lower case letters.
Example: LOWER(‘HiVe’) returns ‘hive’

10. UPPER( string str ), UCASE( string str )

The UPPER or LCASE function converts the string into upper case letters.
Example: UPPER(‘HiVe’) returns ‘HIVE’

11. Double data type in hive – An 8-byte (double precision) floating-point data type used in CREATE TABLE and ALTER TABLE statements.
Range: 4.94065645841246544e-324d .. 1.79769313486231570e+308, positive or negative, The data type REAL is an alias for DOUBLE

12. Renaming a table in Hive –
Syntax :
ALTER TABLE name RENAME TO new_name
For example if we have to rename a table from employee to emp,
hive> ALTER TABLE employee RENAME TO emp;

13. To change a cloumn data type in hive –
Syntax –
ALTER TABLE name CHANGE column_name new_name new_type

14. Order by

The ORDER BY clause is used to retrieve the details based on one column and sort the result set by ascending or descending order.
SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list]]
[LIMIT number]

Sort by

Hive uses the columns in SORT BY to sort the rows before feeding the rows to a reducer. The sort order will be dependent on the
column types. If the column is of numeric type, then the sort order is also in numeric order. If the column is of string type,
then the sort order will be lexicographical order

SELECT key, value FROM src SORT BY key ASC, value DESC

15. RLIKE (Right-Like) is a special function in Hive where if any substring of A matches with B then it evaluates to true.
It also obeys Java regular expression pattern. Users don’t need to put % symbol for a simple match in RLIKE

Reference – https://www.facebook.com/HadoopTutorial/posts/456214807841657

16. The main difference is that when you drop an external table, the underlying data files stay intact. This is because
the user is expected to manage the data files and directories. With a managed table, the underlying directories and
data get wiped out when the table is dropped.

Reference – http://docs.qubole.com/en/latest/faqs/hive/difference-external-table-managed-table.html