Last Update made on March 20, 2018
|Preparing for a Hadoop job interview then this list of most commonly asked Hive Interview questions and answers will help you ace your hadoop job interview.These Hive Interview questions and answers are formulated just to make candidates familiar with the nature of questions that are likely to be asked in a Hadoop job interview on the subject of Hive.|
1) What is the difference between Pig and Hive ?
|Type of Data||Apache Pig is usually used for semi structured data.||Used for Structured Data|
|Schema||Schema is optional.||Hive requires a well-defined Schema.|
|Language||It is a procedural data flow language.||Follows SQL Dialect and is a declarative language.|
|Purpose||Mainly used for programming.||It is mainly used for reporting.|
|General Usage||Usually used on the client side of the hadoop cluster.||Usually used on the server side of the hadoop cluster.|
|Coding Style||Verbose||More like SQL|
For a detailed answer on the difference between Pig and Hive, refer this link -
2) What is the difference between HBase and Hive ?
|HBase does not allow execution of SQL queries.||Hive allows execution of most SQL queries.|
|HBase runs on top of HDFS.||Hive runs on top of Hadoop MapReduce.|
|HBase is a NoSQL database.||Hive is a datawarehouse framework.|
|Supports record level insert, updated and delete operations.||Does not support record level insert, update and delete.|
2) I do not need the index created in the first question anymore. How can I delete the above index named index_bonuspay?
DROP INDEX index_bonuspay ON employee;
3) Can you list few commonly used Hive services?
For the complete list of big data companies and their salaries- CLICK HERE
4) Suppose that I want to monitor all the open and aborted transactions in the system along with the transaction id and the transaction state. Can this be achieved using Apache Hive?
Hive 0.13.0 and above version support SHOW TRANSACTIONS command that helps administrators monitor various hive transactions.
5) What is the use of Hcatalog?
Hcatalog can be used to share data structures with external systems. Hcatalog provides access to hive metastore to users of other tools on Hadoop so that they can read and write data to hive’s data warehouse.
6) Write a query to rename a table Student to Student_New.
Alter Table Student RENAME to Student_New
7) Where is table data stored in Apache Hive by default?
8) Explain the difference between partitioning and bucketing.
9) Explain about the different types of partitioning in Hive?
Partitioning in Hive helps prune the data when executing the queries to speed up processing. Partitions are created when data is inserted into the table. In static partitions, the name of the partition is hardcoded into the insert statement whereas in a dynamic partition, Hive automatically identifies the partition based on the value of the partition field.
Based on how data is loaded into the table, requirements for data and the format in which data is produced at source- static or dynamic partition can be chosen. In dynamic partitions the complete data in the file is read and is partitioned through a MapReduce job based into the tables based on a particular field in the file. Dynamic partitions are usually helpful during ETL flows in the data pipeline.
When loading data from huge files, static partitions are preferred over dynamic partitions as they save time in loading data. The partition is added to the table and then the file is moved into the static partition. The partition column value can be obtained from the file name without having to read the complete file.
10) When executing Hive queries in different directories, why is metastore_db created in all places from where Hive is launched?
When running Hive in embedded mode, it creates a local metastore. When you run the query, it first checks whether a metastore already exists or not. The property javax.jdo.option.ConnectionURL defined in the hive-site.xml has a default value jdbc: derby: databaseName=metastore_db; create=true.
The value implies that embedded derby will be used as the Hive metastore and the location of the metastore is metastore_db which will be created only if it does not exist already. The location metastore_db is a relative location so when you run queries from different directories it gets created at all places from wherever you launch hive. This property can be altered in the hive-site.xml file to an absolute path so that it can be used from that particular location instead of creating multiple metastore_db subdirectory multiple times.
11) How will you read and write HDFS files in Hive?
i) TextInputFormat- This class is used to read data in plain text file format.
ii) HiveIgnoreKeyTextOutputFormat- This class is used to write data in plain text file format.
iii) SequenceFileInputFormat- This class is used to read data in hadoop SequenceFile format.
iv) SequenceFileOutputFormat- This class is used to write data in hadoop SequenceFile format.
12) What are the components of a Hive query processor?
Query processor in Apache Hive converts the SQL to a graph of MapReduce jobs with the execution time framework so that the jobs can be executed in the order of dependencies. The various components of a query processor are-
13) Differentiate between describe and describe extended.
Describe database/schema- This query displays the name of the database, the root location on the file system and comments if any.
Describe extended database/schema- Gives the details of the database or schema in a detailed manner.
14) Is it possible to overwrite Hadoop MapReduce configuration in Hive?
Yes, hadoop MapReduce configuration can be overwritten by changing the hive conf settings file.
15) I want to see the present working directory in UNIX from hive. Is it possible to run this command from hive?
Hive allows execution of UNIX commands with the use of exclamatory (!) symbol. Just use the ! Symbol before the command to be executed at the hive prompt. To see the present working directory in UNIX from hive run !pwd at the hive prompt.
16) What is the use of explode in Hive?
Explode in Hive is used to convert complex data types into desired table formats. explode UDTF basically emits all the elements in an array into multiple rows.
17) Explain about SORT BY, ORDER BY, DISTRIBUTE BY and CLUSTER BY in Hive.
SORT BY – Data is ordered at each of ‘N’ reducers where the reducers can have overlapping range of data.
ORDER BY- This is similar to the ORDER BY in SQL where total ordering of data takes place by passing it to a single reducer.
DISTRUBUTE BY – It is used to distribute the rows among the reducers. Rows that have the same distribute by columns will go to the same reducer.
CLUSTER BY- It is a combination of DISTRIBUTE BY and SORT BY where each of the N reducers gets non overlapping range of data which is then sorted by those ranges at the respective reducers.
18) Difference between HBase and Hive.
19) Write a hive query to view all the databases whose name begins with “db”
SHOW DATABASES LIKE ‘db.*’
20) How can you prevent a large job from running for a long time?
This can be achieved by setting the MapReduce jobs to execute in strict mode set hive.mapred.mode=strict;
The strict mode ensures that the queries on partitioned tables cannot execute without defining a WHERE clause.
21) What is a Hive Metastore?
Hive Metastore is a central repository that stores metadata in external database.
22) Are multiline comments supported in Hive?
23) What is ObjectInspector functionality?
ObjectInspector is used to analyse the structure of individual columns and the internal structure of the row objects. ObjectInspector in Hive provides access to complex objects which can be stored in multiple formats.
24) Explain about the different types of join in Hive.
HiveQL has 4 different types of joins –
JOIN- Similar to Outer Join in SQL
FULL OUTER JOIN – Combines the records of both the left and right outer tables that fulfil the join condition.
LEFT OUTER JOIN- All the rows from the left table are returned even if there are no matches in the right table.
RIGHT OUTER JOIN-All the rows from the right table are returned even if there are no matches in the left table.
25) How can you configure remote metastore mode in Hive?
To configure metastore in Hive, hive-site.xml file has to be configured with the below property –
thrift: //node1 (or IP Address):9083
IP address and port of the metastore host
26) Is it possible to change the default location of Managed Tables in Hive, if so how?
Yes, we can change the default location of Managed tables using the LOCATION keyword while creating the managed table. The user has to specify the storage path of the managed table as the value to the LOCATION keyword.
27) How data transfer happens from HDFS to Hive?
If data is already present in HDFS then the user need not LOAD DATA that moves the files to the /user/hive/warehouse/. So the user just has to define the table using the keyword external that creates the table definition in the hive metastore.
Create external table table_name (
28) In case of embedded Hive, can the same metastore be used by multiple users?
We cannot use metastore in sharing mode. It is suggested to use standalone real database like PostGreSQL and MySQL.
29) The partition of hive table has been modified to point to a new directory location. Do I have to move the data to the new location or the data will be moved automatically to the new location?
Changing the point of partition will not move the data to the new location. It has to be moved manually to the new location from the old one.
30) What will be the output of cast (‘XYZ’ as INT)?
It will return a NULL value.
31) What are the different components of a Hive architecture?
Hive Architecture consists of a –
32) What happens on executing the below query? After executing the below query, if you modify the column –how will the changes be tracked?
Hive> CREATE INDEX index_bonuspay ON TABLE employee (bonus)
The query creates an index named index_bonuspay which points to the bonus column in the employee table. Whenever the value of bonus is modified it will be stored using an index value.
33) What is the default database provided by Hive for Metastore ?
Derby is the default database.
34) Is it possible to compress json in Hive external table ?
Yes, you need to gzip your files and put them as is (*.gz) into the table location.
There are various ways to run Hive queries faster -
We hope that these Hive Interview questions and answers have pre-charged you for your next Hadoop interview on the subject of Hive. Let us know about your experience on Hive interview questions in Hadoop interviews in the comments below.