Hive metastore 结构

  2. -- Hive 文件存储信息相关的元数据表 (SDS、SD_PARAMS、SERDES、SERDE_PARAMS)
  3. -- Hive表分区相关的元数据表 (PARTITIONS、PARTITION_KEYS、PARTITION_KEY_VALS、PARTITION_PARAMS)
  6. -- 显示所有 hive 中所有的表
  7. SELECT
  8.   db_tb.DB_ID,
  9.   db_tb.NAME,
  10.   db_tb.DB_LOCATION_URI,
  11.   db_tb.OWNER_NAME,
  12.   db_tb.DESC,
  13.   tb_tb.TBL_ID,
  14.   tb_tb.SD_ID,
  15.   tb_tb.TBL_NAME,
  16.   tb_tb.TBL_TYPE,
  17.   tb_tb.CREATE_TIME,
  18.   tb_tb.OWNER AS tb_OWNER,
  19.   tb_sds.LOCATION,
  20.   tb_params.lastDdlTime,
  21.   FROM_UNIXTIME(tb_params.lastDdlTime,'%Y-%m-%d %h:%i:%s') AS lastTime
  22. FROM
  23.   DBS AS db_tb
  25. LEFT JOIN
  26.   TBLS AS tb_tb
  27. ON
  28.   db_tb.DB_ID = tb_tb.DB_ID
  30. LEFT JOIN
  31.   SDS AS tb_sds
  32. ON
  33.   tb_sds.SD_ID = tb_tb.SD_ID
  35. -- 表部分属性信息
  36. LEFT JOIN (
  37.   SELECT
  38.     TBL_ID,
  39.     PARAM_VALUE AS lastDdlTime
  40.   FROM
  41.     TABLE_PARAMS
  42.   WHERE
  43.     PARAM_KEY = 'transient_lastDdlTime'
  44. ) AS tb_params
  45. ON
  46.   tb_params.TBL_ID = tb_tb.TBL_ID
  49. --WHERE tb_tb.TBL_NAME = 'jason_test_member'
  50. ;
  54. --- 显示数据表的字段信息 START ---
  55. SELECT
  56.   tb_tb.TBL_ID,
  57.   tb_tb.SD_ID,
  58.   tb_tb.TBL_NAME,
  59.   tb_sds.LOCATION,
  60.   tb_columns.COLUMN_NAME,
  61.   tb_columns.TYPE_NAME,
  62.   tb_columns.COMMENT,
  63.   tb_partition.PKEY_NAME,
  64.   tb_partition.PKEY_TYPE,
  65.   tb_partition.PKEY_COMMENT
  66. FROM
  67.   TBLS AS tb_tb
  69. LEFT JOIN
  70.   SDS AS tb_sds
  71. ON
  72.   tb_sds.SD_ID = tb_tb.SD_ID
  74. LEFT JOIN
  75.   COLUMNS_V2 AS tb_columns
  76. ON
  77.   tb_columns.CD_ID = tb_sds.CD_ID
  79. LEFT JOIN
  80.   PARTITION_KEYS AS tb_partition
  81. ON
  82.   tb_partition.TBL_ID = tb_tb.TBL_ID
  84. WHERE
  85.   tb_tb.TBL_NAME = 'dw_app_access_log'
  86. ORDER BY
  87.   tb_columns.INTEGER_IDX ASC
  88. ;
  91.   -- 处理思路流程
  92.   SELECT TBL_ID,SD_ID,TBL_NAME FROM TBLS WHERE TBL_NAME = 'dw_app_access_log';
  94.   -- 显示存储格式以及地址信息 (通过 SD_ID = SD_ID)
  95.   SELECT SD_ID,CD_ID,LOCATION FROM SDS WHERE SD_ID = 19054
  97.   -- 通过 CD_ID 找出字段信息
  98.   SELECT * FROM COLUMNS_V2 WHERE CD_ID = 17723
  100. --- 显示数据表的字段信息 END ---
  104. -- 显示表所有分区信息
  105. SELECT
  106.   tb_tb.TBL_ID,
  107.   tb_tb.SD_ID,
  108.   tb_tb.TBL_NAME,
  109.   tb_par.PART_ID,
  110.   tb_par.CREATE_TIME,
  111.   tb_par.PART_NAME
  112. FROM
  113.   TBLS AS tb_tb
  114. LEFT JOIN
  115.   PARTITIONS AS tb_par
  116. ON
  117.   tb_par.TBL_ID = tb_tb.TBL_ID
  118. WHERE
  119.   tb_tb.TBL_NAME = 'dw_app_access_log'
  120. ;
  127. -- 该表存储表/视图的属性信息。
  128. SELECT * FROM TABLE_PARAMS WHERE TBL_ID = 66
  129. -- 该表存储表/视图的授权信息
  130. SELECT * FROM TBL_PRIVS WHERE TBL_ID = 66