Overview
The metadata information of Hive is usually stored in the relational database, and the common MySQL database is managed as a meta-database. The previous installation of Hive also stores metadata information in the MySQL database.
Hive metadata information has 57 tables in MySQL data
One, the metadata table (version) that stores the hive version
Version--Query the release information
This table is relatively simple, but important.
ver_id |
Schema_version |
Version_comment |
ID PRIMARY Key |
Hive version |
Release Notes |
1 |
0.13.0 |
Set by Metastore |
If there is a problem with the table, you cannot enter HIVE-CLI.
For example, the table does not exist, when the start HIVE-CLI, will be error "table ' hive.version ' doesn ' t exist".
Second, the Hive Database related metadata table (DBS, Database_params) 1, DBS
DBS--Store basic information about all databases in hive
Meta data table field |
Description |
Sample Data |
db_id |
Database ID |
2 |
DESC |
Database description |
Test Library |
Db_location_uri |
Database HDFs Path |
Hdfs://namenode/user/hive/warehouse/lxw1234.db |
NAME |
Database name |
lxw1234 |
Owner_name |
Database owner User Name |
lxw1234 |
Owner_type |
Owner role |
USER |
2, Database_params
Database_params--The table stores the relevant parameters of the database,
With Dbproperties (Property_name=property_value, ...) The specified parameter.
Meta data table field |
Description |
Sample Data |
db_id |
Database ID |
2 |
Param_key |
Name of parameter |
CreatedBy |
Param_value |
Parameter values |
lxw1234 |
Attention:
DBS and Database_params These two tables are associated through the db_id field.
Iii. Hive tables and view-related metadata tables
There are mainly TBLs, Table_params, Tbl_privs, these three tables through TBL_ID Association.
1, TBLs
This table stores basic information about hive tables, views, and Index tables.
Meta data table field |
Description |
Sample Data |
tbl_id |
Table ID |
1 |
Create_time |
Creation time |
1436317071 |
db_id |
Database ID |
2, corresponding to the db_id in DBS |
Last_access_time |
Last Access time |
1436317071 |
OWNER |
Owner |
Liuxiaowen |
RETENTION |
Reserved fields |
0 |
sd_id |
Serialization configuration information |
86, corresponding to the sd_id in the SDS table |
Tbl_name |
Table name |
lxw1234 |
Tbl_type |
Table type |
Managed_table, External_table, index_table, Virtual_view |
View_expanded_text |
Detailed HQL statements for views |
Select ' lxw1234 ', ' pt ', ' lxw1234 '. ' Pcid ' from ' Liuxiaowen '. ' lxw1234 ' |
View_original_text |
Original HQL statement for the view |
SELECT * FROM lxw1234 |
2.
Table_params
The table stores property information for the table/view.
Meta data table field |
Description |
Sample Data |
tbl_id |
Table ID |
1 |
Param_key |
Property name |
TotalSize, NumRows, EXTERNAL |
Param_value |
Property value |
970107336, 21231028, TRUE |
3.
Tbl_privs
This table stores authorization information for a table/view
Meta data table field |
Description |
Sample Data |
tbl_grant_id |
Authorization ID |
1 |
Create_time |
Authorization time |
1436320455 |
Grant_option |
|
0 |
Grantor |
Authorized Execution User |
Liuxiaowen |
Grantor_type |
Type of grantee |
USER |
Principal_name |
Authorized users |
Username |
Principal_type |
Authorized user Type |
USER |
Tbl_priv |
Permissions |
Select, Alter |
tbl_id |
Table ID |
22, corresponding to the tbl_id in the TBLs table |
Iv. Hive file stores information related metadata tables
Mainly related to SDS, Sd_params, SERDES, Serde_params
Because HDFs supports a large number of file formats, and the hive table can also specify a variety of file formats, hive, when parsing hql to MapReduce, need to know where to go, which format to use to read and write HDFs files, and this information is stored in these tables.
1. SDS
This table holds basic information about the file store, such as Input_format, Output_format, compression, and so on.
The sd_id in the TBLs table is associated with the table to obtain storage information for the hive table.
Meta data table field |
Description |
Sample Data |
sd_id |
Store information ID |
1 |
cd_id |
Field Information ID |
21, corresponding CDs table |
Input_format |
File input Format |
Org.apache.hadoop.mapred.TextInputFormat |
Is_compressed |
Whether to compress |
0 |
Is_storedassubdirectories |
Whether to store in a sub-directory |
0 |
Location |
HDFs Path |
Hdfs://namenode/hivedata/warehouse/ut.db/t_lxw |
Num_buckets |
Number of barrels |
5 |
Output_format |
File output format |
Org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
serde_id |
Serialization Class ID |
3, corresponding SerDes table |
2, Sd_params
This table stores the property information for hive storage, which is used when creating a table
STORED by ' Storage.handler.class.name ' [with serdeproperties (...) Specified.
Meta data table field |
Description |
Sample Data |
sd_id |
Storage Configuration ID |
1 |
Param_key |
Store Property name |
|
Param_value |
Store property values |
|
3, SERDES
The table stores the class information used for serialization
Meta data table field |
Description |
Sample Data |
serde_id |
Serialization class Configuration ID |
1 |
NAME |
Serializing class aliases |
|
Slib |
Serialization classes |
Org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
4, Serde_params
This table stores some of the serialized properties, format information, such as: Row, column delimiter
Meta data table field |
Description |
Sample Data |
serde_id |
Serialization class Configuration ID |
1 |
Param_key |
Property name |
Field.delim |
Param_value |
Property value |
, |
V. Metadata tables related to Hive table fields
Mainly related to COLUMNS_V2
1, COLUMNS_V2
The table stores the field information for the table.
metadata table field |
< strong> description |
sample Data |
cd_id |
Field information ID |
1 |
COMMENT |
Field comment |
&NBSP; |
column_name |
Field name |
pt |
type_name " /span> |
|
string |
integer_idx |
Field order |
2 |
Vi. metadata tables related to hive table partitioning
Mainly related to partitions, Partition_keys, Partition_key_vals, Partition_params
1, partitions
This table stores the basic information for a table partition.
Meta data table field |
Description |
Sample Data |
part_id |
Partition ID |
1 |
Create_time |
Partition creation time |
|
Last_access_time |
Last Access time |
|
Part_name |
Partition name |
Pt=2015-06-12 |
sd_id |
Partition Storage ID |
21st |
tbl_id |
Table ID |
2 |
2, Partition_keys
The table stores the field information for the partition.
metadata table field |
description |
sample Data |
tbl_id |
table ID |
2 |
pkey_comment |
partition field description |
|
pkey_name |
partition field name |
pt |
pkey_type |
partition field type |
string |
integer_idx |
partitioning field order |
1 |
3, Partition_key_vals
The table stores the partition field values.
Meta data table field |
Description |
Sample Data |
part_id |
Partition ID |
2 |
Part_key_val |
Partition field value |
2015-06-12 |
Integer_idx |
Partition field Value Order |
0 |
4, Partition_params
The table stores property information for the partition.
Meta data table field |
Description |
Sample Data |
part_id |
Partition ID |
2 |
Param_key |
Partition Property Name |
Numfiles, NumRows |
Param_value |
Partition attribute value |
15, 502195 |
Vii. other infrequently used meta-data tables
Database permissions Information table. Once the database is authorized through the grant statement, it is stored here.
Index table, storing metadata related to hive indexes
Index-related property information.
The statistics for the table field. Use the Analyze statement to record the table field after it has been parsed here.
Authorization Information for table fields
Authorization information for a partition
The statistics for the partition field.
The permission information for the partition field.
User-registered function information
Resource information for the user registration function
Hive Learning Path (iii) Hive metadata information corresponds to MySQL database table