hive-metastore.sql

hive-metastore.sql

SELECT
   a.DB_TABLE AS DB_TABLE,
   a.COLUMN_NAME AS COLUMN_NAME,
   a.COLUMN_TYPE AS COLUMN_TYPE
FROM
   (SELECT
      CONCAT(DBS.NAME, '.', TBLS.TBL_NAME) AS DB_TABLE,
      COLUMNS_V2.COLUMN_NAME AS COLUMN_NAME,
      COLUMNS_V2.TYPE_NAME AS COLUMN_TYPE
   FROM
      DBS
      JOIN TBLS ON DBS.DB_ID = TBLS.DB_ID
      JOIN SDS ON TBLS.SD_ID = SDS.SD_ID
      JOIN COLUMNS_V2 ON COLUMNS_V2.CD_ID = SDS.CD_ID
      JOIN
         (
            SELECT DISTINCT TBL_ID, TBL_COMMENT
            FROM
            (
               SELECT TBLS.TBL_ID TBL_ID, TABLE_PARAMS.PARAM_KEY, TABLE_PARAMS.PARAM_VALUE, CASE WHEN TABLE_PARAMS.PARAM_KEY = 'comment' THEN TABLE_PARAMS.PARAM_VALUE ELSE '' END TBL_COMMENT
               FROM TBLS JOIN TABLE_PARAMS
               ON TBLS.TBL_ID = TABLE_PARAMS.TBL_ID
            ) TBL_COMMENTS_INTERNAL
         ) TBL_COMMENTS
      ON TBLS.TBL_ID = TBL_COMMENTS.TBL_ID) a
WHERE
   a.DB_TABLE = '#dbTable#'