Shulou(Shulou.com)06/01 Report--

This article mainly explains the relationship between hive and mysql. The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the relationship between hive and mysql.

Hive is a data warehouse platform based on Hadoop. Through hive, we can easily do the work of ETL. Hive defines a query language similar to SQL: HQL, which can transform QL written by users into corresponding Mapreduce programs and execute them based on Hadoop.

Hive is a data warehouse framework opened by Facebook in August 2008. its system goal is similar to that of Pig, but it has some mechanisms that Pig does not support at present, such as richer type system, query language more similar to SQL, persistence of Table/ metadata and so on.

Hive can be seen as a mapper from SQL to Map-Reduce

The components and architecture of hive:

Hive web API starts:. / hive-- service hwi

Browser access: http://localhost:9999/hwi/

By default, Hive metadata is stored in an embedded Derby database, allowing only one session connection, which is only suitable for simple tests. In order to support multi-user and multi-session, we need a separate Metabase. We use MySQL as the Metabase, and Hive provides good support for MySQL internally.

Hive installation

Embedded mode: metadata remains in embedded Derby mode, allowing only one session connection

Local stand-alone mode: install Mysql locally and put metadata into Mysql

Remote mode: metadata is placed in a remote Mysql database.

Where do you put the Hive data?

The data is under the warehouse directory of HDFS, and a table corresponds to a subdirectory.

The local / tmp directory holds logs and execution plans

There are two types of hive tables, the inner table and the outer table.

When Hive creates an internal table, it moves the data to the path pointed to by the data warehouse; if an external table is created, only the path where the data is located is recorded, and no change is made to the location of the data.

When you delete a table, the metadata and data of the internal table are deleted together, while the external table deletes only the metadata, not the data. In this way, external tables are relatively more secure, data organization is more flexible, and it is convenient to share source data.

Using Mysql as the storage database for Hive metaStore

The main tables involved are as follows:

Table name description basic information about all hive tables associated with the key TBLS (table name, creation time, ownership, etc.)

TBL_ID,SD_IDTABLE_PARAM table-level attributes, such as whether external tables, table comments, last modification time, etc.) TBL_IDCOLUMNSHive field information (field comments, field names, field types, field serial numbers) SD_IDSDS all hive tables, table partitions corresponding to hdfs data catalogs and data format SD_ID,SERDE_IDSERDE_PARAM serialization and deserialization information Such as row separator, column separator, NULL representation character and other SERDE_IDPARTITIONSHive partition information (table, partition value) PART_ID,SD_ID,TBL_IDPARTITION_KEYSHive partition table partition key (partition field) TBL_IDPARTITION_KEY_VALSHive partition name (key value) PART_ID thank you for reading, this is the content of "the relationship between hive and mysql", after the study of this article I believe you have a deeper understanding of the relationship between hive and mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.