A detailed summary of Hive VS traditional relational database

This article thought, looks at the diagram to speak, a picture, clearly summarizes the difference between the two

The following is a detailed summary of the articles in the diagram

1. Query Language

Not to repeat.

2. Data storage location

Not to repeat.

3. Data format

Hive : 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 line delimiter ("\ n"), and the method of reading the file data (three file formats default in hive 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.

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.

4. Data Update

Hive: 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 it is loaded.

Database: Database data is often modified frequently, so you can use INSERT into ... VALUES add data, use update ... SET to modify the data.

5. Index

Hive: As previously mentioned, 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.

Database : Database, it is usually indexed for one or several columns, so the database can be highly efficient 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 .

6. Implementation

Hive: The execution of most queries in Hive is done through the MapReduce provided by Hadoop (queries like select *from tbl do not require MapReduce).

Database: The database usually has its own execution engine.

7. Execution delay

Hive: 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.

Database: Relative, 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.

8. Scalability

Hive: Because Hive is built on top of Hadoop, the scalability of hive is consistent with the scalability of Hadoop (the world's largest Hadoop cluster in the yahoo!,2009 year at 4000 nodes or so).

Database: The expansion lines are very limited due to the strict limitations of the acid semantics of the database. At present, the most advanced parallel database Oracle has a theoretical expansion capacity of only about 100 units.

9. Data size

Hive: Because Hive is built on a cluster and can be computed in parallel using MapReduce, it can support large-scale data.

Database: Corresponding, the database can support the size of the data is small.