Managed and External Tables in Hive

In hive, we can create two types of tables

• ·         Managed table
• ·         External table

By default the hive stores the data in the hive warehouse directory. When we create a table in hive, a directory corresponding to the table will be created in the hive warehouse directory. Hive warehouse directory is a location in hdfs where the hive stores the data of all the tables that we create in hive without specifying any location. By default the location of the warehouse directory is /user/hive/warehouse. We can modify this location globally by modifying this property with a different value in the hive-site.xml.

 We can point a hive table to any other location in hdfs rather than the default storage location. The main difference between external and managed tables is that if we drop a managed table, the table as well as the data will be deleted but if we delete an external table, only the table will get deleted, data will not be deleted.

External tables will be very useful in scenarios where we need to share the input data between multiple jobs or users.

Suppose a workflow with A as input of processes B, C and D. B is a hive job, C is a mapreduce job and D is a pig job. Here if we use managed hive table, when we use managed table for B, while loading data it will move the data from A’s actual location to the warehouse directory. So when the other processes C and D tries to access the data, it will not be present in the actual location. If the user drops the table at the end of the process B will delete the input data which may not be feasible in this situation.

Sample DDL for creating a managed hive table is given below.

create table details (id int, name string) row format
delimited fields terminated by ‘,’ lines terminated by ‘\n’;

Sample DDL for creating an external table

create external table details_ext(id int, name string) row format

delimited fields terminated by ‘,’ lines terminated by ‘\n’ 

location ‘/user/hadoop/external_table’;

The location specified in the external table can be any location in hdfs. You can avoid the ‘lines terminated by’ part in the DDL because the default value is ‘\n’.

What is hive ?

Hive is one of the members in the Hadoop ecosystem.  Hadoop is written in java. So initially hadoop was limited to only the subset of engineers who know java. Later, some smart guys in facebook designed a layer on top of hadoop which can act as a mediator between the SQL experts and hadoop. They made an application that will accept SQL standard queries and talks to hadoop by parsing the queries. This application is called hive. This is internally accessing the HDFS and data processing is happening through mapreduce. The main advantage is that the user doesn’t need to worry about the complexity of writing lengthier mapreduce programs. After the invention of this application, hadoop became popular among SQL experts through hive.  Another advantage of hive is that the development time for some solutions are very faster compared to writing java programs. Sometimes a few lines of queries may work well instead of writing several hundred lines of code.

In hive the data is represented as tables. A table is a representation of data with a schema. In hadoop data is stored in hdfs. So if we look at the data through a schema, we will be able to visualize the data in tabular format. In hive the schema is stored in metastore. A metastore is a lightweight database where the hive stores the metadata of tables. By default hive uses derby database, which is not suitable for production or multi-user environments. So usually people use mysql, postgresql etc as metastore.

Hadoop Interview Questions

1) What is the name of Hadoop's file system .?
Ans: HDFS

2) What is the full form of HDFS.?
Ans: Hadoop Distributed File System

3) What is the Processing Layer of Hadoop. ?
Ans: Mapreduce

4) Hadoop framework is written in which language .?
Ans: Java

5) What is the licencing cost for hadoop.?
Ans: Hadoop is an opensource technology. So it is free.

6) Who is known as father of Hadoop.?
Ans: Doug Cutting

7) How Hadoop differs from other data processing technologies..?
Ans: Hadoop is a framework which is having distributed storage as well as a distributed processing layer. The basic idea behind hadoop is to bring down the processing layer down to storage. Hadoop is a horizontally scaling framework So high end server grade hardware is not required. Only commodity hardware is required.

8) Is hadoop good for real time processing.?
Ans: Directly No. Hadoop is a batch processing framework. So it can't be used for real time processing. But it can work along with other technologies to produce real time outputs.

9) Is hadoop a replacement for RDBMS..?
Ans: Hadoop is not suitable for processing small or medium amount of data. Since hadoop is a batch processing framework, hadoop will not provide faster output. What hadoop guarantees is that, it will never fail with large data. In case of large data, which the other data processing technologies can't process, hadoop will perform well

10) If hadoop is open source and free, who is maintaining it and enhancing it.?
Ans: Hadoop is an Apache project, people all over the world are contributing and adding more enhancements to it. Lot of companies are also using hadoop, they are also contributing to hadoop.

11) Why hadoop became very popular.?
Ans: Analyzing hidden insight from data became a very important part of almost every organisation now. The correctness of the insights will be more as the size of the data is more. Now a days the usage of internet and social media is very high. So if we collect that data alone, we can analyse people upto some extent. Similar to this, we can analyse anything and everything using the history data. This is one reason. Similarly real time  monitoring and decision making also became very important now. This is another factor. If we go for a tool / product with licence, the licensing cost itself will be very high. Hadoop is opensource and free. Hadoop runs on commodity hardware, so the cost of the Infrastructure is also less. This made hadoop a hot cake in the market.

12) What do you mean by a pseudo distributed hadoop cluster.?
Ans If all the daemons of the hadoop are running in a single node, it is called pseudo distributed mode. This is not used for production. This is just for development and learning purpose.