如何获取Hive库表字段分区信息

如何获取Hive库表字段分区信息

  使用Hive关联的Mysql读取Hive的库表字段分区信息，也可以使用Hive的sys数据库查询相关的信息，但是会比较慢。所以直接使用Mysql去读，Hive在Mysql保存的信息如下图所示：

获取Hive的所有数据库信息

  如果注释字段中文乱码，则需要修改字符集（正常情况都需要修改）。

SELECT
	`NAME` NAME,
	ANY_VALUE ( `DESC` ) COMMENT,
	ANY_VALUE ( `DB_LOCATION_URI` ) path 
FROM
	`DBS` 
GROUP BY
	`NAME`

根据数据库名获取数据表信息

  path字段对应着Hive表在Hdfs的路径，在做数据同步的时候很重要（比如使用dataX进行数据同步，当然手写JSON也是很累的，但是还好有datax-web开源项目）,type字段可以看出表的储存格式(目前已知Hive可以直接加载Hdfs目录中的text格式和parquet格式文件)。

SELECT
	tb.TBL_NAME NAME,
	tp.PARAM_VALUE COMMENT,
	sds.INPUT_FORMAT type,
	sds.LOCATION path 
FROM
	DBS db
	LEFT JOIN TBLS tb ON tb.DB_ID = db.DB_ID
	LEFT JOIN TABLE_PARAMS tp ON tp.TBL_ID = tb.TBL_ID 
	AND tp.PARAM_KEY = 'comment'
	LEFT JOIN SDS sds ON sds.SD_ID = tb.SD_ID 
WHERE
	db.`NAME` = 'test'
ORDER BY
	tb.TBL_NAME

根据数据库名、数据表名获取表字段信息

  此处查询结果只会展示表的字段信息（分区字段不会在该查询语句的结果中显示）。

SELECT
	col.COLUMN_NAME NAME,
	col.TYPE_NAME type,
	col.`COMMENT` COMMENT,
	col.INTEGER_IDX position 
FROM
	DBS db
	LEFT JOIN TBLS tb ON tb.DB_ID = db.DB_ID
	LEFT JOIN SDS sds ON sds.SD_ID = tb.SD_ID
	LEFT JOIN COLUMNS_V2 col ON sds.CD_ID = col.CD_ID 
WHERE
	db.NAME = 'test' 
	AND tb.TBL_NAME = 'person'
ORDER BY
	col.INTEGER_IDX

根据数据库名、数据表名获取表分区信息

  至于分区字段为什么单独拿出来不合到表字段里，是因为每个分区对应着一个Hdfs目录。分区有目录，字段没有啊怎么能放到一起。（在使用datax同步分区数据时会将分区数据同步到分区表指定的分区目录中）。

查看分区表分区字段信息

  创建一个分区表用年、月、日三级目录进行分区：

create table if not exists person_partition(
    name string comment '姓名',
    age  int comment '年龄'
) comment '测试表'
partitioned by (
    year string comment '年',
    month string comment '月',
    day string comment '日'
)
stored as parquet;

  分区表字段信息如下所示：

  分区表分区字段信息如下所示：

SELECT
	pk.PKEY_NAME NAME,
	pk.PKEY_TYPE type,
	pk.PKEY_COMMENT COMMENT,
	pk.INTEGER_IDX position 
FROM
	DBS db
	LEFT JOIN TBLS tb ON tb.DB_ID = db.DB_ID
	LEFT JOIN PARTITION_KEYS pk ON pk.TBL_ID = tb.TBL_ID 
WHERE
	db.`NAME` = 'test' 
	AND tb.TBL_NAME = 'person_partition' 
ORDER BY
	pk.INTEGER_IDX

查看分区表的分区信息

  当前表中还没有分区，先来加几个分区：

• 添加分区【同时会在Hdfs中创建目录】:alter table [table] add partition(col,..);
• 删除分区【同时会在Hdfs中删除目录】:alter table [table] drop partition(col,..);

alter table person_partition add if not exists partition(year=2015,month=12,day=26);
alter table person_partition add if not exists partition(year=2016,month=12,day=26);
alter table person_partition add if not exists partition(year=2017,month=12,day=26);
alter table person_partition add if not exists partition(year=2018,month=12,day=26);
alter table person_partition add if not exists partition(year=2019,month=12,day=26);
alter table person_partition add if not exists partition(year=2020,month=12,day=26);

  接下来查询下分区表的分区信息，看看每个分区对应的Hdfs路径（使用datax数据同步时将会使用到）：

SELECT
	db.`NAME` dbName,
	tb.TBL_NAME tbName,
	part.PART_NAME partName,
	sds.LOCATION path 
FROM
	`PARTITIONS` part
	LEFT JOIN TBLS tb ON tb.TBL_ID = part.TBL_ID
	LEFT JOIN DBS db ON db.DB_ID = tb.DB_ID
	LEFT JOIN SDS sds ON sds.SD_ID = part.SD_ID 
WHERE
	db.`NAME` = 'test' 
	AND tb.TBL_NAME = 'person_partition'

  去hdfs中验证下是不是有这个几个目录：

  如果还需要hive的其它信息，请认真翻阅hive关联的mysql数据库，查看hive库保存的相关表信息（o(╥﹏╥)o）。