Hive元数据在MySQL中的存储结构

拼搏现实的明天。 2022-04-25 05:34 506阅读 0赞

元数据表有以下：

### 1、存储Hive版本的元数据表(VERSION) ###

![20190506084259724.png][]

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> VER_ID </td> 
 <td style="vertical-align:top;"> SCHEMA_VERSION </td> 
 <td style="vertical-align:top;"> VERSION_COMMENT </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> ID主键 </td> 
 <td style="vertical-align:top;"> Hive版本 </td> 
 <td style="vertical-align:top;"> 版本说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> 1 </td> 
 <td style="vertical-align:top;"> 1.1.0 </td> 
 <td style="vertical-align:top;"> Set &nbsp;by MetaStore </td> 
 </tr> 
 </tbody> 
</table>

如果该表出现问题，根本进入不了Hive-Cli。比如该表不存在，当启动Hive-Cli时候，就会报错”Table ‘hive.version’ doesn’t exist”。

### 2、Hive数据库相关的元数据表(DBS、DATABASE\_PARAMS) ###

DBS：该表存储Hive中所有数据库的基本信息，字段如下:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70][]

DATABASE\_PARAMS：该表存储数据库的相关参数，在CREATE DATABASE时候用WITH DBPROPERTIES(property\_name=property\_value, …)指定的参数。

![20190506084654222.png][]

DBS和DATABASE\_PARAMS这两张表通过DB\_ID字段关联。

### 3、Hive表和视图相关的元数据表 ###

主要有TBLS、TABLE\_PARAMS、TAB\_COL\_STATS，这三张表通过TBL\_ID关联。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 1][]

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 表字段 </td> 
 <td style="vertical-align:top;"> 说明 </td> 
 <td style="vertical-align:top;"> 示例数据 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> TBL_ID </td> 
 <td style="vertical-align:top;"> 表ID </td> 
 <td style="vertical-align:top;"> 21 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> CREATE_TIME </td> 
 <td style="vertical-align:top;"> 创建时间 </td> 
 <td style="vertical-align:top;"> 1447675704 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> DB_ID </td> 
 <td style="vertical-align:top;"> 数据库ID </td> 
 <td style="vertical-align:top;"> 1 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> LAST_ACCESS_TIME </td> 
 <td style="vertical-align:top;"> 上次访问时间 </td> 
 <td style="vertical-align:top;"> 1447675704 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> OWNER </td> 
 <td style="vertical-align:top;"> 所有者 </td> 
 <td style="vertical-align:top;"> root </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> RETENTION </td> 
 <td style="vertical-align:top;"> 保留字段 </td> 
 <td style="vertical-align:top;"> 0 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> SD_ID </td> 
 <td style="vertical-align:top;"> 序列化配置信息 </td> 
 <td style="vertical-align:top;"> 41，对应SDS表中的SD_ID </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> TBL_NAME </td> 
 <td style="vertical-align:top;"> 表名 </td> 
 <td style="vertical-align:top;"> ex_detail_ufdr_30streaming </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> TBL_TYPE </td> 
 <td style="vertical-align:top;"> 表类型 </td> 
 <td style="vertical-align:top;"> EXTERNAL_TABLE </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> VIEW_EXPANDED_TEXT </td> 
 <td style="vertical-align:top;"> 视图的详细HQL语句 </td> 
 <td style="vertical-align:top;">&nbsp;</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> VIEW_ORIGINAL_TEXT </td> 
 <td style="vertical-align:top;"> 视图的原始HQL语句 </td> 
 <td style="vertical-align:top;">&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 2][]

### 4、Hive表分分区相关的元数据表 ###

主要涉及PARTITIONS、PARTITION\_KEYS、PARTITION\_KEY\_VALS、PARTITION\_PARAMS；

PARTITIONS:该表存储表分区的基本信息。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 3][]

PARTITION\_KEYS:该表存储分区的字段信息。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 4][]

PARTITION\_KEY\_VALS:该表存储分区字段值

![20190506085308954.png][]

PARTITION\_PARAMS:该表存储分区的属性信息

![20190506085337292.png][]

[20190506084259724.png]: /images/20220205/5fc2c2a74bc5421caed489d11f6e62d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70]: /images/20220205/7378e6203ca44f93b7b6f1427f6172ca.png
[20190506084654222.png]: /images/20220205/b73ed07f5283486cac6d5bc5a87f736a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 1]: /images/20220205/e14af773f3224b46b2f4692c1864aaa0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 2]: /images/20220205/5287c84f7d594ea38601d1d02c6abac1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 3]: /images/20220205/0b2ed6101e2945098b1f7456fd9515cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ltMDEyMTM_size_16_color_FFFFFF_t_70 4]: /images/20220205/77eeb1351fb44f9293516c2e874f00bd.png
[20190506085308954.png]: /images/20220205/63200eb0fb3940649d6f98184c1a8e61.png
[20190506085337292.png]: /images/20220205/6ec286494c8a4c70946edb02caf22d42.png