【Hive】公司数据仓库字典/元数据库应用案例

文章目录

• ⌘ 常用SQL查询应用案例：
• • -- 数据仓库表结构字典表
  • -- 数据仓库授权信息字典表
  • -- 通过字段名注释找表名
  • -- 根据表注释找表
  • -- 查询指定库中的分区表
  • -- 查询指定库中某种存储类型的分区表
• ⌘ 元数据库常用表说明
• • -- DBS
  • -- TBLS
  • -- TABLE_PARAMS
  • -- TBL_PRIVS
  • -- COLUMNS_V2
  • -- SDS
  • -- SD_PARAMS:
  • -- PARTITIONS
  • -- PARTITION_KEYS
  • -- PARTITION_PARAMS
  • -- VERSION

⌘ 常用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 TBLSJOIN DBS ON TBLS.DB_ID = DBS.DB_IDJOIN SDS ON TBLS.SD_ID = SDS.SD_IDJOIN CDS ON SDS.CD_ID = CDS.CD_IDJOIN TBL_PRIVS ON TBLS.TBL_ID = TBL_PRIVS.TBL_IDJOIN COLUMNS_V2 ON CDS.CD_ID = COLUMNS_V2.CD_IDLEFT JOIN (SELECT TBL_ID, PARAM_VALUEFROM TABLE_PARAMSWHERE PARAM_KEY = 'COMMENT'
) COMM ON (COMM.TBL_ID = TBLS.TBL_ID)

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

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

– 通过字段名注释找表名

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

– 根据表注释找表

select d.NAME, a.TBL_NAME, b.PARAM_VALUE
from TBLS ajoin(select TBL_ID, PARAM_VALUEfrom TABLE_PARAMSwhere PARAM_KEY = 'comment'and PARAM_VALUE like '%销售商%') bon (a.TBL_ID = b.TBL_ID)join DBS don (a.DB_ID = d.DB_ID)
where d.NAME = 'dw_idl'
order by a.TBL_NAME

– 查询指定库中的分区表

selectdb.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';

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

selectdb.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%';

⌘ 元数据库常用表说明

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)

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！