Abstract: Because Hive uses the SQL query Language HQL, it is easy to interpret hive as a database. In fact
Structurally, there is no similarity between Hive and the database, in addition to having a similar query language. This article will
The differences between Hive and database are explained in several ways. 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. Comparison of Hive and database
| 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 |
Mapredcue |
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.
- The location where the data is stored. 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, and the user-defined data format requires three attributes: column delimiter (usually space, "\ T", "\x001″"), line delimiter ("\ n"), and Method of reading file data (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 into an RDBMS database can be time-consuming .
- Data updates. Because Hive is designed for data warehouse applications, the content of the Data warehouse is much less read and 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 has been said before, Hive does not do any processing of the data during the loading of the data, or even scans the data, and therefore 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, 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 .
- Perform. The execution of most queries in Hive is done 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 before, 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. Hive Execution Latency is high, and its query efficiency can compensate for its high latency disadvantage only if the data reaches a certain level .
- Scalability. 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 is around 4000 nodes in the yahoo!,2009 year). However, due to the strict limitation of ACID semantics, the database is very limited in extension lines. 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.
Similarities and differences of RDBMS between hive and relational database
