标题
- 1、Hive元数据表
- （1）存储Hive版本的元数据表
- （2）Hive数据库相关的元数据表
- 2）DATABASE_PARAMS
- （3）Hive表和视图相关的元数据表
- （4）Hive文件存储信息相关的元数据表
- （5）Hive表字段相关的元数据表
- （6）Hive表分区相关的元数据表
- 2）PARTITION_KEYS
- 3）PARTITION_KEY_VALS
- 4）PARTITION_PARAMS
- （7）Hive函数相关的元数据表
- （8）其他不常用的元数据表
- 2、Hive中Join的用法
- （1）join六种用法
- （2）join语法格式
- 官网参考链接：
- （3）基本join使用
1、Hive元数据表
我们配置文件里面写的没有就创建hive_sql 名字随便我这里是这个
- VERSION
- DBS、DATABASE_PARAMS
- TBLS、TABLE_PARAMS、TBL_PRIVS、COLUMNS_V2
- PARTITIONS、PARTITION_KEYS、 PARTITION_KEY_VALS、PARTITION_PARAMS
hive的元数据存在于mysql中，在mysql中会有一个hive库，存放相应的表，一共35张表。
（1）存储Hive版本的元数据表
VERSION
该表比较简单，但很重要。
VER_ID | SCHEMA_VERSION | VERSION_COMMENT |
ID主键 | Hive版本 | 版本说明 |
1 | 1.1.0 | Set by MetaStore hadoop@192.168.1.14 |
如果该表出现问题，根本进入不了Hive-Cli。
比如该表不存在，当启动Hive-Cli时候，就会报错”Table ‘hive.version’ doesn’t exist”。
（2）Hive数据库相关的元数据表
1）DBS
该表存储Hive中所有数据库的基本信息，字段如下:
表字段 | 说明 | 示例数据 |
DB_ID | 数据库ID | 1 |
DESC | 数据库描述 | Default Hive |
DB_LOCATION_URI | 数据HDFS路径 | hdfs://hadoop000:9000/user/hive/warehouse |
NAME | 数据库名 | default |
OWNER_NAME | 数据库所有者用户名 | public |
OWNER_TYPE | 所有者角色 | ROLE |
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)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 |
+-------+-----------+-------------+表字段 | 说明 | 示例数据 |
DB_ID | 数据库ID | 1 |
PARAM_KEY | 参数名 | createdby |
PARAM_VALUE | 参数值 | root |
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)表字段 | 说明 | 示例数据 |
TBL_ID | 表ID | 21 |
CREATE_TIME | 创建时间 | 1447675704 |
DB_ID | 数据库ID | 1 |
LAST_ACCESS_TIME | 上次访问时间 | 1447675704 |
OWNER | 所有者 | root |
RETENTION | 保留字段 | 0 |
SD_ID | 序列化配置信息 | 41，对应SDS表中的SD_ID |
TBL_NAME | 表名 | ex_detail_ufdr_30streaming |
TBL_TYPE | 表类型 | EXTERNAL_TABLE |
VIEW_EXPANDED_TEXT | 视图的详细HQL语句 | |
VIEW_ORIGINAL_TEXT | 视图的原始HQL语句 |
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 |
+--------+-----------------------+-------------+表字段 | 说明 | 示例数据 |
TBL_ID | 表ID | 21 |
PARAM_KEY | 属性名 | totalSize，numRows，EXTERNAL |
PARAM_VALUE | 属性值 | 970107336、21231028、TRUE |
3）TBL_PRIVS
该表存储表/视图的授权信息，一般不用Hive的权限，而使用sentry来进行权限控制。
表字段 | 说明 | 示例数据 |
TBL_GRANT_ID | 授权ID | 1 |
CREATE_TIME | 授权时间 | 1436320455 |
GRANT_OPTION | 0 | |
GRANTOR | 授权执行用户 | root |
GRANTOR_TYPE | 授权者类型 | USER |
PRINCIPAL_NAME | 被授权用户 | username |
PRINCIPAL_TYPE | 被授权用户类型 | USER |
TBL_PRIV | 权限 | Select、Alter |
TBL_ID | 表ID | 21，对应TBLS表的TBL_ID |
（4）Hive文件存储信息相关的元数据表
主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive 表时候也可以指定各种文件格式，Hive在将HQL解析成MapReduce时候，需要知道去哪里，使用哪种 格式去读写HDFS文件，而这些信息就保存在这几张表中。
1）SDS
该表保存文件存储的基本信息，如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的 SD_ID与该表关联，可以获取Hive表的存储信息。
表字段 | 说明 | 示例数据 |
SD_ID | 存储信息ID | 41 |
CD_ID | 字段信息ID | 21，对应CDS表 |
INPUT_FORMAT | 文件输入格式 | org.apache.hadoop.mapred.TextInputFormat |
IS_COMPRESSED | 是否压缩 | 0 |
IS_STOREDASSUBDIRECTORIES | 是否以子目录存储 | 0 |
LOCATION | HDFS路径 | hdfs://193.168.1.75:9000/detail_ufdr_streaming_test |
NUM_BUCKETS | 分桶数量 | 0 |
OUTPUT_FORMAT | 文件输出格式 | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
SERDE_ID | 序列化类ID | 41，对应SERDES表 |
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)2）SD_PARAMS
该表存储Hive存储的属性信息，在创建表时候使用STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)指定。
表字段 | 说明 | 示例数据 |
SD_ID | 存储配置ID | 41 |
PARAM_KEY | 存储属性名 | |
PARAM_VALUE | 存储属性值 |
3）SERDES
该表存储序列化使用的类信息
表字段 | 说明 | 示例数据 |
SERDE_ID | 序列化类配置ID | 41 |
NAME | 序列化类别名 | NULL |
SLIB | 序列化类 | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
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
|
+----------+------+-------------------------------------------------------------
+4）SERDE_PARAMS
该表存储序列化的一些属性、格式信息，比如:行、列分隔符
表字段 | 说明 | 示例数据 |
SERDE_ID | 序列化类配置ID | 41 |
PARAM_KEY | 属性名 | field.delim |
PARAM_VALUE | 属性值 |
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 | |
+----------+----------------------+-------------+（5）Hive表字段相关的元数据表
主要涉及COLUMNS_V2
COLUMNS_V2
该表存储表对应的字段信息
表字段 | 说明 | 示例数据 |
CD_ID | 字段信息ID | 21 |
COMMENT | 字段注释 | NULL |
COLUMN_NAME | 字段名 | air_port_duration |
TYPE_NAME | 字段类型 | bigint |
INTEGER_IDX | 字段顺序 | 119 |
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 |
+-------+---------+-------------+-----------+-------------+（6）Hive表分区相关的元数据表
主要涉及PARTITIONS、PARTITION_KEYS、PARTITION_KEY_VALS、PARTITION_PARAMS
1）PARTITIONS
该表存储表分区的基本信息
表字段 | 说明 | 示例数据 |
PART_ID 分区ID 21 | ||
CREATE_TIME | 分区创建时间 | 1450861405 |
LAST_ACCESS_TIME | 最后一次访问时间 | 0 |
PART_NAME | 分区名 | hour=15/last_msisdn=0 |
SD_ID | 分区存储ID | 43 |
TBL_ID | 表ID | 22 |
LINK_TARGET_ID | NULL |
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 |
+---------+-------------+------------------+----------------------------+-------+--------2）PARTITION_KEYS
该表存储分区的字段信息
表字段 | 说明 | 示例数据 |
TBL_ID | 表ID | 22 |
PKEY_COMMENT | 分区字段说明 | NULL |
PKEY_NAME | 分区字段名 | hour |
PKEY_TYPE | 分区字段类型 | int |
INTEGER_IDX | 分区字段顺序 | 0 |
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 |
+--------+--------------+-------------+-----------+-------------+3）PARTITION_KEY_VALS
该表存储分区字段值
表字段 | 说明 | 示例数据 |
PART_ID | 分区ID | 21 |
PART_KEY_VAL | 分区字段值 | 0 |
INTEGER_IDX | 分区字段值顺序 | 1 |
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 |
+---------+--------------+-------------+4）PARTITION_PARAMS
该表存储分区的属性信息
表字段 | 说明 | 示例数据 |
PART_ID | 分区ID | 21 |
PARAM_KEY | 分区属性名 | numFiles，numRows |
PARAM_VALUE | 分区属性值 | 1，502195 |
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 |
+---------+-----------------------+-------------+（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)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)（8）其他不常用的元数据表
名称 | 说明 |
1）DB_PRIVS | 数据库权限信息表。通过GRANT语句对数据库授权后，将会在这里存储。 |
2）IDXS | 索引表，存储Hive索引相关的元数据 |
3）INDEX_PARAMS | 索引相关的属性信息 |
4）TBL_COL_STATS | 表字段的统计信息。使用ANALYZE语句对表字段分析后记录在这里 |
5）TBL_COL_PRIVS | 表字段的授权信息 |
6）PART_PRIVS | 分区的授权信息 |
7）PART_COL_PRIVS | 分区字段的权限信息 |
8）PART_COL_STATS | 分区字段的统计信息 |
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切记，使用 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）（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 302）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 NULL3）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 254）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 255）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 256）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
