The similarities and differences between Hive and common relational database

1. Query the 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. 2. 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. 3. 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 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. 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. 4. 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. 5. 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. 6. Implementation. 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. 7. 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. 8. 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. 9. 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 common relational database