Hive Learning Path (iii) Hive metadata information corresponds to MySQL database table

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

• Db_privs

Database permissions Information table. Once the database is authorized through the grant statement, it is stored here.

• Idxs

Index table, storing metadata related to hive indexes

• Index_params

Index-related property information.

• Tab_col_stats

The statistics for the table field. Use the Analyze statement to record the table field after it has been parsed here.

• Tbl_col_privs

Authorization Information for table fields

• Part_privs

Authorization information for a partition

• Part_col_stats

The statistics for the partition field.

• Part_col_privs

The permission information for the partition field.

• Funcs

User-registered function information

• Func_ru

Resource information for the user registration function

Hive Learning Path (iii) Hive metadata information corresponds to MySQL database table