Hive MetaStore(메타스토어) 활용방법 - (2)

앞 장에서 하이브 메타스토어(MetaStore)를 활용하는 방법에 대해서 설명했고, 이번에도 이어서 설명해보려 한다. 앞 장에서 설명한 쿼리는 아래 링크(https://developers-haven.tistory.com/45)를 참고하면 된다.

Hive MetaStore(메타스토어) 활용방법 - (1)

 

 

1. 테이블 데이터 건수 조회

• (참고) HIVE 통계 정보가 생성되지 않은 테이블은 메타 반영이 안된다.
• (설명) 테이블 파라미터 정보를 가진 TABLE_PARAMS 테이블에서 PARAM_KEY = 'numRows' 정보를 이용하여 조회한다.

SELECT DBS.NAME 		AS SCHEMA_
     , TBLS.TBL_NAME 	AS TBL_NAME
     , tp.PARAM_VALUE 	AS NumRows  -- 데이터 건수
  FROM DBS
  JOIN TBLS
    ON DBS.DB_ID = TBLS.DB_ID 
  LEFT JOIN (SELECT TBL_ID
  	          , PARAM_VALUE
	        FROM TABLE_PARAMS
	        WHERE PARAM_KEY = 'numRows'
	   ) tp
    ON TBLS.TBL_ID = tp.TBL_ID;

 

 

2. 테이블 컬럼 조회 (컬럼명/설명/데이터타입/순서)

• (step1) 테이블 포맷 관련 정보를 가진 SDS 테이블과 컬럼 정보를 가진 COLUMNS_V2 테이블을 SDS.CD_ID = COLUMNS_V2.CD_ID 기준으로 조인 (TBLS 테이블의 SD_ID 값별로 컬럼 CD_ID 가 필요해서 이렇게 진행)
• (step2) SDS 테이블과 테이블 정보를 가진 TBLS 테이블을 TBLS.SD_ID = SDS.SD_ID 기준으로 조인
• (step3) TBLS 테이블과 데이터베이스 정보를 가진 DBS 테이블을 DBS.DB_ID = TBLS.DB_ID 기준으로 조인

SELECT DBS.NAME				      AS SCHEMA_
     , TBLS.TBL_NAME              	      AS TBL_NAME
     , COLUMNS_V2.COLUMN_NAME     AS COLUMN_NAME  -- 컬럼영문명
     , COLUMNS_V2.COMMENT         AS COLUMN_DESCRIPTION  -- 컬럼설명
     , COLUMNS_V2.TYPE_NAME       AS DATE_TYPE  -- 컬럼데이터타입
     , COLUMNS_V2.INTEGER_IDX+1       AS COLUMN_ID  -- 컬럼순서(1이 첫번째를 의미)
  FROM SDS 
  JOIN COLUMNS_V2
    ON SDS.CD_ID  = COLUMNS_V2.CD_ID
  JOIN TBLS
    ON TBLS.SD_ID = SDS.SD_ID
  JOIN DBS 
    ON DBS.DB_ID  = TBLS.DB_ID
  ORDER BY 1,2,6;

 

 

3. 암호화 컬럼 조회

• (step1) TABLE_PARAMS 테이블에서 PARAM_KEY = 'column.encode.indices'인 경우로 암호화 컬럼 인덱스 추출 (암호화 컬럼 인덱스가 comma(,) 구분자로 구성되어 있음(ex.1,2,5))
• (step2) 반복문을 통해 위의 문자열을 comma(,) 구분자로 파싱 진행
• (참고) 아래 쿼리는 암호화 컬럼 최대 갯수가 50개인 경우로 작성했기 때문에 필요에 따라서 WITH TMP_NO AS (SELECT 1 AS N UNION ALL ~) 부분을 원하는 횟수만큼 반복하도록 수정하면 된다.

SELECT *
FROM (
	WITH TMP_NO AS (
	SELECT 1 AS  N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 
		)
		SELECT T2.SCHEMA_		AS SCHEMA_
		     , T2.TBL_NAME		AS TBL_NAME
		     , SUBSTRING_INDEX(SUBSTRING_INDEX(T2.DECRYTION_COLUMN, ',', N), ',', -1) + 1 	AS DECRYPT_COLUMN_ID -- 암호화 컬럼(comma 구분자로 파싱하며 1이 첫번째 컬럼을 의미)
		FROM (SELECT * 
			FROM (SELECT NAME 		AS SCHEMA_
			           , TBL_NAME 		AS TBL_NAME
			           , tp.PARAM_VALUE 	AS DECRYTION_COLUMN -- 암호화 컬럼인덱스
				 FROM DBS
				 JOIN TBLS
				 ON DBS.DB_ID = TBLS.DB_ID 
				 LEFT JOIN (SELECT TBL_ID
						 , PARAM_VALUE
				   	      FROM TABLE_PARAMS
				   	      WHERE PARAM_KEY = 'column.encode.indices'
					   ) tp
				 ON TBLS.TBL_ID = tp.TBL_ID 
				 WHERE tp.PARAM_VALUE IS NOT NULL -- 암호화컬럼 없는 테이블 제외
		       	   ) T1
		    ) T2, TMP_NO S1
	) A1
GROUP BY 1,2,3;

 

 

4. 최소 / 최대 파티션 조회

• (참고) HIVE 통계 정보가 생성되지 않은 테이블은 메타 반영이 안된다.
• 테이블 정보를 가진 TBLS 테이블과 파티션 정보를 가진 PARTITIONS 테이블을 TBLS.TBL_ID = PARTITIONS.TBL_ID 기준으로 조인한다.

SELECT DBS.NAME 			AS SCHEMA_
     , TBLS.TBL_NAME 		AS TBL_NAME
     , min(part.PART_NAME)  	AS MIN_PARTITION  -- 최초 파티션
     , max(part.PART_NAME)  	AS MAX_PARTITION  -- 마지막 파티션
  FROM TBLS
  JOIN DBS
    ON DBS.DB_ID = TBLS.DB_ID
  LEFT JOIN PARTITIONS part
    ON TBLS.TBL_ID = part.TBL_ID
  GROUP BY TBLS.TBL_ID;

 

 

5. 다중 파티션 조회

• (step1) 테이블 파라미터 정보를 가진 TABLE_PARAMS 테이블에서 PARAM_KEY = 'default-partition-spec' 인 경우로 파티션 정보 추출
• (step2) JSON 파싱을 통해 최상단/두번째/세번째 파티션 정보 추출
• (참고) 아래 쿼리는 최대 파티션 컬럼이 3개인 경우로 작성했기 때문에 필요에 따라 json_extract 부분을 추가하면 된다.

-- json 예시: {"spec-id":0,"fields":[{"name":"BASE_DATE","transform":"identity","source-id":21,"field-id":1000},{"name":"ETL_YMD","transform":"identity","source-id":22,"field-id":1001}]}

SELECT DBS.NAME 			AS SCHEMA_
     , TBLS.TBL_NAME 		AS TBL_NAME
     , json_unquote(json_extract(json_extract(GROUP_CONCAT(IF(tp.PARAM_KEY = 'default-partition-spec', tp.PARAM_VALUE, NULL)), '$.fields[0]'), '$.name')) AS 1ST_PARTITION -- partition depth 1(최상단)
     , json_unquote(json_extract(json_extract(GROUP_CONCAT(IF(tp.PARAM_KEY = 'default-partition-spec', tp.PARAM_VALUE, NULL)), '$.fields[1]'), '$.name')) AS 2ND_PARTITION -- partition depth 2
     , json_unquote(json_extract(json_extract(GROUP_CONCAT(IF(tp.PARAM_KEY = 'default-partition-spec', tp.PARAM_VALUE, NULL)), '$.fields[2]'), '$.name')) AS 3RD_PARTITION -- partition depth 3
  FROM DBS
  JOIN TBLS
    ON DBS.DB_ID = TBLS.DB_ID 
  JOIN TABLE_PARAMS tp
    ON TBLS.TBL_ID = tp.TBL_ID 
  GROUP BY DBS.DB_ID, TBLS.TBL_ID
  HAVING 1ST_PARTITION <> 'partition'; -- 파티션없는 경우 제외

 

 

 

 

주로 사용되는 테이블들이 어떤 컬럼값을 기준으로 조인되어야 하는지만 익힌다면 어떤 내용이든지 원하는 정보를 추출할 수 있을 것이다. 만약 테이블 간의 조인 기준이 헷갈린다면 Hive MetaStore 엔터티 관계도를 보면서 테이블 간의 관계를 해석해보는 것을 추천한다! (엔터티 관계도를 같이 첨부했으니 참고하길 바라며 본인의 Hive 버전에 따라 테이블명은 조금씩 달라질 수 있다.)

 

 

 

자료 출처: https://datacadamia.com/db/hive/metastore#er_diagram

Hive - Metastore

 

 

HiveMetaStore-entity-relationship.pdf

0.22MB