Hive高级、Hive的元数据_hive元数据库表

1、Hive元数据表

我们配置文件里面写的没有就创建hive_sql 名字随便我这里是这个

1. VERSION
2. DBS、DATABASE_PARAMS
3. TBLS、TABLE_PARAMS、TBL_PRIVS、COLUMNS_V2
4. PARTITIONS、PARTITION_KEYS、 PARTITION_KEY_VALS、PARTITION_PARAMS
   hive的元数据存在于mysql中，在mysql中会有一个hive库，存放相应的表，一共35张表。

（1）存储Hive版本的元数据表

VERSION
该表比较简单，但很重要。

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

（2）Hive数据库相关的元数据表

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

mysql> SELECT * FROM DBS\G;
*************************** 1. row *************************** 
          DB_ID: 1
           DESC: Default Hive database
DB_LOCATION_URI: hdfs://hadoop000:9000/user/hive/warehouse 
           NAME: default
     OWNER_NAME: public
     OWNER_TYPE: ROLE
     *************************** 2. row *************************** 
          DB_ID: 6
           DESC: NULL
DB_LOCATION_URI: hdfs://hadoop000:9000/user/hive/warehouse/test.db 
           NAME: test
     OWNER_NAME: hadoop
     OWNER_TYPE: USER
*************************** 3. row *************************** 
          DB_ID: 7
           DESC: NULL
DB_LOCATION_URI: hdfs://hadoop000:9000/user/hive/warehouse/test000.db 
           NAME: test000
     OWNER_NAME: hadoop
     OWNER_TYPE: USER
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2）DATABASE_PARAMS

该表存储数据库的相关参数，在CREATE DATABASE时候用WITH DBPROPERTIES(property_name=property_value, …)指定的参数。
t1表默认的创建 t2 加了注释 还有with后的属性信息 信息分别放在DBS; DATABASE_PARAMS; ( DB_ID进行关联)

mysql> SELECT * FROM DATABASE_PARAMS; 
+-------+-----------+-------------+
| DB_ID | PARAM_KEY | PARAM_VALUE | 
+-------+-----------+-------------+
|     7 | creator   | hadoop      | 
+-------+-----------+-------------+
1
2
3
4
5
6

DBS和DATABASE_PARAMS这两张表通过DB_ID字段关联。

（3）Hive表和视图相关的元数据表

主要有TBLS、TABLE_PARAMS、TBL_PRIVS，这三张表通过TBL_ID关联。
TBLS
该表中存储Hive表，视图，索引表的基本信息

mysql> select * from TBLS\G;
*************************** 1. row *************************** 
            TBL_ID: 1
       CREATE_TIME: 1598946409
             DB_ID: 1
  LAST_ACCESS_TIME: 0
             OWNER: hadoop
         RETENTION: 0
             SD_ID: 1
          TBL_NAME: job
          TBL_TYPE: MANAGED_TABLE
VIEW_EXPANDED_TEXT: NULL
VIEW_ORIGINAL_TEXT: NULL
22 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2）TABLE_PARAMS（扩展 附加属性在这里展示）
该表存储表/视图的属性信息

mysql> select * from TABLE_PARAMS; 
+--------+-----------------------+-------------+ 
| TBL_ID | PARAM_KEY             | PARAM_VALUE | 
+--------+-----------------------+-------------+ |      
1 | COLUMN_STATS_ACCURATE | true        | |      
1 | numFiles              | 1           | |      
1 | numRows               | 0           | |      
1 | rawDataSize           | 0           | |      
1 | totalSize             | 36          | |      
1 | transient_lastDdlTime | 1598946557  | |      
2 | COLUMN_STATS_ACCURATE | true        | |      
2 | numFiles              | 1           | |      
2 | numRows               | 3           | |      
2 | rawDataSize           | 33          | |      
2 | totalSize             | 65          | |      
2 | transient_lastDdlTime | 1598946800  | 
+--------+-----------------------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3）TBL_PRIVS
该表存储表/视图的授权信息，一般不用Hive的权限，而使用sentry来进行权限控制。

（4）Hive文件存储信息相关的元数据表

主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive 表时候也可以指定各种文件格式，Hive在将HQL解析成MapReduce时候，需要知道去哪里，使用哪种 格式去读写HDFS文件，而这些信息就保存在这几张表中。
1）SDS
该表保存文件存储的基本信息，如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的 SD_ID与该表关联，可以获取Hive表的存储信息。

mysql> select * from SDS\G;
*************************** 1. row *************************** 
                    SD_ID: 1
                    CD_ID: 1
             INPUT_FORMAT: org.apache.hadoop.mapred.TextInputFormat 
            IS_COMPRESSED:
IS_STOREDASSUBDIRECTORIES:
                 LOCATION: hdfs://hadoop000:9000/user/hive/warehouse/job 
              NUM_BUCKETS: -1
            OUTPUT_FORMAT: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 
                 SERDE_ID: 1
 22 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

2）SD_PARAMS
该表存储Hive存储的属性信息，在创建表时候使用STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)指定。

3）SERDES
该表存储序列化使用的类信息

mysql> SELECT * FROM SERDES; 
+----------+------+-------------------------------------------------------------
+
| SERDE_ID | NAME | SLIB
|
+----------+------+-------------------------------------------------------------
+
|        1 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|        2 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|        6 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|        7 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|        8 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|       11 | NULL | org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe
|
|       12 | NULL | org.apache.hadoop.hive.ql.io.orc.OrcSerde
|
|       14 | NULL | org.apache.hadoop.hive.ql.io.orc.OrcSerde
|
|       16 | NULL | org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
|
|       17 | NULL | org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
|
|       18 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|       19 | NULL | org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe
|
|       21 | NULL | org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe
|
|       22 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|       23 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|       25 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|       27 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|       28 | NULL | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
|
|       29 | NULL | org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe
|
|       30 | NULL | org.apache.hadoop.hive.ql.io.orc.OrcSerde
|
|       31 | NULL | org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
|
|       32 | NULL | org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
|
+----------+------+-------------------------------------------------------------
+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

4）SERDE_PARAMS
该表存储序列化的一些属性、格式信息，比如:行、列分隔符

mysql> SELECT * FROM SERDE_PARAMS; 
+----------+----------------------+-------------+
| SERDE_ID | PARAM_KEY            | PARAM_VALUE | 
+----------+----------------------+-------------+
|        1 | field.delim          |                | 
|        1 | serialization.format |                |
|        2 | serialization.format | 1              | 
|        6 | serialization.format | 1              | 
|        7 | serialization.format | 1              | 
|        8 | field.delim          |                | 
|        8 | serialization.format |                | 
|       11 | field.delim          |                | 
|       11 | serialization.format |                | 
|       12 | field.delim          |                | 
|       12 | serialization.format |                | 
|       14 | field.delim          |                | 
|       14 | serialization.format |                | 
|       16 | field.delim          |                |
 |      16 | serialization.format |                | 
 |      17 | field.delim          |                | 
 |      17 | serialization.format |                |
  |     18 | serialization.format | 1              | 
  |     19 | serialization.format | 1              | 
  |     21 | field.delim          |                | 
  |     21 | serialization.format |                | 
  |     22 | field.delim          |                | 
  |     22 | serialization.format |                | 
  |     23 | field.delim          |                | 
  |     23 | serialization.format |                | 
  |     25 | field.delim          |                | 
  |     25 | serialization.format |                | 
  |     27 | serialization.format | 1              | 
  |     28 | field.delim          |                | 
  |     28 | serialization.format |                | 
  |     29 | field.delim          |                | 
  |     29 | serialization.format |                | 
  |     30 | field.delim          |                | 
  |     30 | serialization.format |                | 
  |     31 | field.delim          |                | 
  |     31 | serialization.format |                | 
  |     32 | field.delim          |                | 
  |     32 | serialization.format |                |
   +----------+----------------------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

（5）Hive表字段相关的元数据表

主要涉及COLUMNS_V2
COLUMNS_V2
该表存储表对应的字段信息

mysql> SELECT * FROM COLUMNS_V2; 
+-------+---------+-------------+-----------+-------------+ 
| CD_ID | COMMENT | COLUMN_NAME | TYPE_NAME | INTEGER_IDX |
 +-------+---------+-------------+-----------+-------------+ |
      1 | NULL    | id          | int       |           0 | 
|     1 | NULL    | name        | string    |           1 | 
|     2 | NULL    | id          | int       |           0 | 
|     2 | NULL    | name        | string    |           1 | 
|     6 | NULL    | id          | int       |           0 | 
|     6 | NULL    | name        | string    |           1 | 
|     7 | NULL    | id          | int       |           0 | 
|     7 | NULL    | name        | string    |           1 | 
|     8 | NULL    | id          | int       |           0 | 
|     8 | NULL    | name        | string    |           1 | 
|    32 | NULL    | countrycode | string    |           2 | 
|    32 | NULL    | district    | string    |           3 | 
|    32 | NULL    | id          | string    |           0 | 
|    32 | NULL    | name        | string    |           1 | 
|    32 | NULL    | population  | string    |           4 | 
+-------+---------+-------------+-----------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（6）Hive表分区相关的元数据表

主要涉及PARTITIONS、PARTITION_KEYS、PARTITION_KEY_VALS、PARTITION_PARAMS
1）PARTITIONS
该表存储表分区的基本信息

mysql> SELECT * FROM PARTITIONS; 
+---------+-------------+------------------+----------------------------+-------+--------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME                 | SD_ID | TBL_ID | 
+---------+-------------+------------------+----------------------------+-------+--------
|       1 |  1599283268 |                0 | event_month=2015-11       |    37 |     36 |
|       2 |  1599283402 |                0 | event_month=2015-11/step=1|    39 |     37 |
|       3 |  1599283407 |                0 | event_month=2015-11/step=2|    40 |     37 | 
+---------+-------------+------------------+----------------------------+-------+--------
1
2
3
4
5
6
7
8

2）PARTITION_KEYS

该表存储分区的字段信息

mysql> SELECT * FROM PARTITION_KEYS; 
+--------+--------------+-------------+-----------+-------------+ 
| TBL_ID | PKEY_COMMENT | PKEY_NAME   | PKEY_TYPE | INTEGER_IDX 
| +--------+--------------+-------------+-----------+-------------+ 
|     36 | NULL         | event_month | string    |           0 | 
|     37 | NULL         | event_month | string    |           0 | 
|     37 | NULL         | step        | string    |           1 |
 +--------+--------------+-------------+-----------+-------------+
1
2
3
4
5
6
7
8

3）PARTITION_KEY_VALS

该表存储分区字段值

mysql> SELECT * FROM PARTITION_KEY_VALS; 
+---------+--------------+-------------+ 
| PART_ID | PART_KEY_VAL | INTEGER_IDX 
| +---------+--------------+-------------+ 
|       1 | 2015-11      |           0 | 
|       2 | 2015-11      |           0 | 
|       2 | 1            |           1 | 
|       3 | 2015-11      |           0 | 
|       3 | 2            |           1 |
 +---------+--------------+-------------+
1
2
3
4
5
6
7
8
9
10

4）PARTITION_PARAMS

该表存储分区的属性信息

mysql> SELECT * FROM PARTITION_PARAMS; 
+---------+-----------------------+-------------+ 
| PART_ID | PARAM_KEY             | PARAM_VALUE | 
+---------+-----------------------+-------------+ 
|       1 | COLUMN_STATS_ACCURATE | true        | 
|       1 | numFiles              | 1           | 
|       1 | numRows               | 0           | 
|       1 | rawDataSize           | 0           | 
|       1 | totalSize             | 423         | 
|       1 | transient_lastDdlTime | 1599283269  | 
|       2 | COLUMN_STATS_ACCURATE | true        | 
|       2 | numFiles              | 1           | 
|       2 | numRows               | 0           | 
|       2 | rawDataSize           | 0           | 
|       2 | totalSize             | 423         | 
|       2 | transient_lastDdlTime | 1599283402  | 
|       3 | COLUMN_STATS_ACCURATE | true        | 
|       3 | numFiles              | 1           | 
|       3 | numRows               | 0           | 
|       3 | rawDataSize           | 0           | 
|       3 | totalSize             | 423         | 
|       3 | transient_lastDdlTime | 1599283408  | 
+---------+-----------------------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

（7）Hive函数相关的元数据表

1）FUNCS
用户注册的函数信息

mysql> SELECT * FROM FUNCS\G;
*************************** 1. row *************************** 
    FUNC_ID: 1
 CLASS_NAME: com.kgc.hadoop.HelloUDF
CREATE_TIME: 1599201059
      DB_ID: 1
  FUNC_NAME: sayhello2
  FUNC_TYPE: 1
 OWNER_NAME: NULL
 OWNER_TYPE: USER
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

2）FUNC_RU
用户注册函数的资源信息

mysql> SELECT * FROM FUNC_RU\G;
*************************** 1. row *************************** 
      FUNC_ID: 1
RESOURCE_TYPE: 1
 RESOURCE_URI: hdfs://hadoop000:9000/lib/hive-1.0-SNAPSHOT.jar 
  INTEGER_IDX: 0
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（8）其他不常用的元数据表

2、Hive中Join的用法

hive 当中可以通过 join 和 union 两种方式合并表，其中 join 偏向于横向拼接（增加列的数量）， union 则主要负责纵向拼接（增加行的数量）。

（1）join六种用法

hive 中 join 主要分为六种，分别是：
（inner） join
out join：
。left (outer) join
。right (outer) join
。full (outer) join
。cross join
。left semi join

（2）join语法格式

官网参考链接：

join_table:
    table_reference [INNER] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

table_reference: 
    table_factor 
  | join_table

table_factor: 
    tbl_name [alias]
  | table_subquery alias 
  | ( table_references )

join_condition: 
    ON expression
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

切记，使用 join 时不能忘记关键字 on。如果结尾未写 on，则都相当于进行 cross join，笛卡儿积关联 （左表一万条数据，右表一万条数据，笛卡儿积之后就是一亿条数据，可怕吧~）。
另外，建议join 中将大表写在靠右的位置，hive 处理速度也会快一些。

（3）基本join使用

基础数据

create database hive_join;
use hive_join;

create table a(
id int,name string
)row format delimited fields terminated by '\t';

create table b( id int,age int)row format delimited fields terminated by '\t';

load data local inpath '/home/hadoop/data/join_a.txt' overwrite into table a; load data local inpath '/home/hadoop/data/join_b.txt' overwrite into table b;

hive> select * from a;
OK
1       zhangsan
2       lisi
3       wangwu
Time taken: 0.526 seconds, Fetched: 3 row(s) hive> select * from b;
OK
1       28
2       30
4       25
Time taken: 0.08 seconds, Fetched: 3 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1）（inner）join
内连接，返回两张表都有的数据。

select a.id,a.name,b.age from a inner join b on a.id=b.id;
hive> select a.id,a.name,b.age from a inner join b on a.id=b.id; OK
1       zhangsan        28
2       lisi    30
1
2
3
4

2）left outer join
左连接，以前面的表为主表，返回的数据行数跟主表相同，关联不上的字段为NULL。

select a.id,a.name,b.age from a left join b on a.id=b.id;
hive> select a.id,a.name,b.age from a left join b on a.id=b.id; OK
1       zhangsan        28
2       lisi    30
3       wangwu  NULL
1
2
3
4
5

3）right outer join
右连接，以后面的表为主表，返回的记录数和主表一致，关联不上的字段为NULL。

select a.id,a.name,b.age from a right join b on a.id=b.id;

hive> select a.id,a.name,b.age from a right join b on a.id=b.id; OK
1       zhangsan        28
2       lisi    30
NULL    NULL    25
1
2
3
4
5
6

4）full outer join
全连接，返回两个表的并集，空缺的字段为NULL。

select a.id,a.name,b.age from a full join b on a.id=b.id;

hive> select a.id,a.name,b.age from a full join b on a.id=b.id; OK
1       zhangsan        28
2       lisi    30
3       wangwu  NULL
NULL    NULL    25
1
2
3
4
5
6
7

5）cross join
返回两个表的笛卡尔积结果（数目为左表乘右表），不需要指定关联键。

select a.id,a.name,b.age from a cross join b;

hive> select a.id,a.name,b.age from a cross join b; OK
1       zhangsan        28
1       zhangsan        30
1       zhangsan        25
2       lisi    28
2       lisi    30
2       lisi    25
3       wangwu  28
3       wangwu  30
3       wangwu  25
1
2
3
4
5
6
7
8
9
10
11
12

6）left semi join
并不拼接两张表，两个表对 on 的条件字段做交集，返回前面表的记录，相较于其他的方法，这样子 hive 处理速度比较快。

select a.id,a.name from a left semi join b on a.id=b.id;

hive> select a.id,a.name from a left semi join b on a.id=b.id; OK
1       zhangsan
2       lisi
1
2
3
4
5