【Hive】公司数据仓库字典/元数据库应用案例_select dbs.name as dbname ,tbls.tbl_name,columns_v

⌘ 常用SQL查询应用案例：

常用表的ER图如下：

– 数据仓库表结构字典表

SELECT DBS.NAME                              -- 数据库名
     , TBLS.TBL_NAME                         -- 表名
     , COMM.PARAM_VALUE                      -- 表注释
     , COLUMNS_V2.COLUMN_NAME                -- 字段名
     , COLUMNS_V2.COMMENT                    -- 字段注释
     , TBLS.OWNER                            -- 表的拥有者
     , TBL_PRIVS.CREATE_TIME                 -- 被授权时间
     , TBL_PRIVS.PRINCIPAL_NAME              -- 被授权用户
     , TBLS.CREATE_TIME AS TABLE_CREATE_TIME -- 表创建时间
     , TBLS.LAST_ACCESS_TIME                 -- 最近一次访问时间
FROM TBLS
         JOIN DBS ON TBLS.DB_ID = DBS.DB_ID
         JOIN SDS ON TBLS.SD_ID = SDS.SD_ID
         JOIN CDS ON SDS.CD_ID = CDS.CD_ID
         JOIN TBL_PRIVS ON TBLS.TBL_ID = TBL_PRIVS.TBL_ID
         JOIN COLUMNS_V2 ON CDS.CD_ID = COLUMNS_V2.CD_ID
         LEFT JOIN (
    SELECT TBL_ID
         , PARAM_VALUE
    FROM TABLE_PARAMS
    WHERE PARAM_KEY = 'COMMENT'
) COMM ON (COMM.TBL_ID = TBLS.TBL_ID)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

– 数据仓库授权信息字典表

mysql -h {IP} -P 3306 -D hive2 -u{用户名} -p{密码} --default-character-set=utf8 -e "
SELECT DBS.NAME as '数据库名'
, TBLS.TBL_NAME as '数据库名'
, COMM.PARAM_VALUE as '表注释'
, FROM_UNIXTIME(TBLS.CREATE_TIME,'%Y-%m-%d') as '表创建时间'
, TBLS.OWNER as '表的拥有者'
, FROM_UNIXTIME(TBL_PRIVS.CREATE_TIME,'%Y-%m-%d')   as '表被授权时间'
, TBL_PRIVS.PRINCIPAL_NAME as '被授权用户'
, TBLS.LAST_ACCESS_TIME as '最近一次访问时间'
FROM TBLS
JOIN DBS ON TBLS.DB_ID = DBS.DB_ID
JOIN SDS ON TBLS.SD_ID = SDS.SD_ID
JOIN CDS ON SDS.CD_ID = CDS.CD_ID
JOIN TBL_PRIVS ON TBLS.TBL_ID = TBL_PRIVS.TBL_ID
LEFT JOIN ( SELECT TBL_ID , PARAM_VALUE FROM TABLE_PARAMS WHERE PARAM_KEY = 'comment') COMM
ON (COMM.TBL_ID = TBLS.TBL_ID)
WHERE TBLS.OWNER = 'prd_dw' --prd_dw 为hive表的属主用户
--- AND TBLS.TBL_NAME like '%表名%'  -- 查某个单表的历史授权信息
AND TBLS.OWNER <> TBL_PRIVS.PRINCIPAL_NAME
ORDER BY DBS.NAME,TBLS.TBL_NAME
" >> /wls/dw/tmp/Helz/table.csv
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

– 通过字段名注释找表名

select d.NAME,a.TBL_NAME,e.PARAM_VALUE,c.COLUMN_NAME,c.TYPE_NAME,c.COMMENT,c.INTEGER_IDX from TBLS a
join SDS b
on (a.SD_ID=b.SD_ID)
join
(select * from COLUMNS_V2 where comment like '%i%') c
ON (c.CD_ID=b.CD_ID)
join
DBS d
on (a.DB_ID=d.DB_ID)
join
(select TBL_ID,PARAM_VALUE from TABLE_PARAMS where PARAM_KEY='comment') e
on (a.TBL_ID=e.TBL_ID) where d.NAME IN ('dw_mdl','dw_idl')
order by TBL_NAME,INTEGER_IDX
1
2
3
4
5
6
7
8
9
10
11
12
13

– 根据表注释找表

select d.NAME, a.TBL_NAME, b.PARAM_VALUE
from TBLS a
    join
    (select TBL_ID, PARAM_VALUE
     from TABLE_PARAMS
     where PARAM_KEY = 'comment'
     and PARAM_VALUE like '%销售商%') b
     on (a.TBL_ID = b.TBL_ID)
     join DBS d
     on (a.DB_ID = d.DB_ID)
where d.NAME = 'dw_idl'
order by a.TBL_NAME
1
2
3
4
5
6
7
8
9
10
11
12

– 查询指定库中的分区表

select
  db.NAME,
  tb.TBL_NAME,
  pk.PKEY_NAME 
from TBLS tb
join DBS db
join PARTITION_KEYS pk
where tb.DB_ID = db.DB_ID
and tb.TBL_ID=pk.TBL_ID
and db.NAME='dbname';
1
2
3
4
5
6
7
8
9
10

– 查询指定库中某种存储类型的分区表

select
  db.NAME,     
  tb.TBL_NAME,
  pk.PKEY_NAME,
  s.INPUT_FORMAT,
  s.OUTPUT_FORMAT
from TBLS tb
join DBS db
join PARTITION_KEYS pk
join SDS s
where tb.DB_ID = db.DB_ID
and tb.TBL_ID=pk.TBL_ID
and tb.SD_ID = s.SD_ID
and db.NAME='test'
and s.INPUT_FORMAT like '%TextInputFormat%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

⌘ 元数据库常用表说明

---

Hive数据库相关的元数据表，主要有DBS和DATABASE_PARAMS这两张表通过DB_ID字段关联。

– DBS

数据库信息表，表存储Hive中数据库的信息，主键是DB_ID，default 为系统自带的库，HDFS路径默认为/user/hive/warehouse。

---

Hive表和视图相关的元数据表，主要有TBLS、TABLE_PARAMS、TBL_PRIVS，这三张表通过TBL_ID关联

– TBLS

表/视图/索引表基本信息表

– TABLE_PARAMS

表/视图的属性信息表

– TBL_PRIVS

表/视图授权信息表，该表存储表/视图的授权信息

---

Hive表字段相关的元数据表

– COLUMNS_V2

表字段信息表，新增字段后可以通过改变字段顺序的值来改变表的字段顺序

---

Hive文件存储信息相关的元数据表,主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive表时候也可以指定各种文件格式，Hive在将HQL解析成MapReduce时候，需要知道去哪里，使用哪种格式去读写HDFS文件，而这些信息就保存在这几张表中。

– SDS

表文件存储信息表,如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的SD_ID与该表关联，可以获取Hive表的存储信息。

– SD_PARAMS:

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

---

Hive表分分区相关的元数据表

– PARTITIONS

该表存储表分区的基本信息

– PARTITION_KEYS

该表存储分区的字段信息

– PARTITION_PARAMS

该表存储分区的属性信息

– VERSION

存储Hive版本的元数据表

mysql> select * from VERSION ;
+--------+----------------+----------------------------+
| VER_ID | SCHEMA_VERSION | VERSION_COMMENT            |
+--------+----------------+----------------------------+
|      1 | 3.1.0          | Hive release version 3.1.0 |
+--------+----------------+----------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

此表存着Hive的版本信息，有且只有一条数据，修改这条数据会导致Hive不可用