The similarities and differences between hive and database

Http://www.cnblogs.com/Richardzhu/p/3364909.html

First, Hive Introduction

Hive is a Hadoop-based data warehousing tool that maps structured data files into a single database table and provides full SQL query functionality that can be translated into a mapreduce task to run. The advantage is that the learning cost is low, the simple mapreduce statistics can be quickly realized through the class SQL statements, and it is very suitable for the statistical analysis of data Warehouse without developing specialized mapreduce applications.

Hvie is the Data Warehouse infrastructure built on Hadoop. It provides a range of tools that can be used for data extraction conversion loading (ETL), a mechanism that can store, query, and analyze large-scale data stored in Hadoop. Hive defines a simple class SQL query statement called HQL, which allows users who are familiar with SQL to query data. At the same time, the language also allows developers to familiarize themselves with the development of custom Mapper and reducer for the built-in mapper and reducer of complex analytical work that cannot be done.

Because hive uses the SQL query Language hql, it is easy to interpret hive as a database. In fact, in terms of structure, hive and database have similar query language, no similarity. This article will explain the differences between hive and database from several aspects. The database can be used in online applications, but hive is designed for the data warehouse, which is clear and helps to understand the characteristics of hive from an application perspective.

Query Language	HQL	Sql
Data storage location	Hdfs	Raw Device or local FS
Data format	User Defined	System decision
Data Update	Not supported	Support
Index	No	Yes
Perform	Mapreduce	Executor
Execution delay	High	Low
Scalability	High	Low
Data size	Big	Small

• Query Language : Because SQL is widely used in the Data Warehouse, the query Language hql of class SQL is designed specifically for the characteristics of hive. Developers who are familiar with SQL development can easily use hive for development.

• Data storage location : Hive is built on top of Hadoop, and all hive data is stored in HDFs. The database can then store the data in a block device or on a local file system.

• Data Format : There is no specific data format defined in hive, the data format can be specified by the user, the user-defined data format needs to specify three attributes: The column delimiter (typically a space, "\ T", "\x001"), the row delimiter ("\ n"), and the method of reading the file data ( The default in hive is three file formats Textfile, Sequencefile, and Rcfile). Because in the process of loading the data, there is no need to convert from the user data format to the data format defined by the hive, so hive does not make any modifications to the data itself during the loading process, but simply copies or moves the contents of the data into the appropriate HDFS directory. In the database, different databases have different storage engines and define their own data formats. All data is stored in a certain organization, so the process of loading data in a database can be time-consuming.

• Data Update : Because hive is designed for data warehouse applications, the content of the data warehouse is less than read-write. Therefore, overwriting and adding data is not supported in hive, and all data is determined when loaded. The data in the database is often modified, so you can use INSERT INTO ... Values add data, use update ... Set to modify the data.

• Index : As previously stated, Hive does not process data during loading, and does not even scan the data, so it does not index some of the keys in the data. When hive accesses a specific value in the data that satisfies a condition, it requires brute-force scanning of the entire data, so the access latency is high. Because of the introduction of MapReduce, hive can access the data in parallel, so even without an index, hive can still demonstrate its advantage in accessing large amounts of data. databases, which are typically indexed for one or several columns, the database can have high efficiency and low latency for data access to a small number of specific conditions. Because of the high latency of data access, it is decided that hive is not suitable for online data query.

• Execution : Most queries in hive are executed through the MapReduce provided by Hadoop (queries like select * from TBL do not require MapReduce). The database usually has its own execution engine.

• Execution delay : As mentioned earlier, hive, when querying data, needs to scan the entire table because there is no index, so the delay is high. Another factor that causes a high latency in hive execution is the MapReduce framework. Because MapReduce itself has a high latency, there is also a high latency when executing a hive query with MapReduce. In contrast, the database execution latency is low. Of course, this low is conditional, that is, the data size is small, when the data is large enough to exceed the processing capacity of the database, hive's parallel computing obviously can show the advantages.

• Scalability: Because Hive is built on top of Hadoop, the scalability of hive is consistent with the scalability of Hadoop. However, the database is very limited due to the strict limitation of acid semantics. At present, the most advanced parallel database Oracle has a theoretical expansion capacity of only about 100 units.

• Data size : Because hive is built on a cluster and can be used for parallel computing by MapReduce, it can support large-scale data, and correspondingly, the database can support a small amount of data.

The similarities and differences between hive and database