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

하이브 메타스토어(MetaStore)에 대한 정보는 아래 링크(https://developers-haven.tistory.com/44)를 참고하길 바라며, 이번에는 메타스토어를 활용하기 위한 SQL 쿼리에 대해 알아보도록 하자.

Hive MetaStore(메타스토어)에 대하여

 

 

1. 스키마별 테이블 조회

• 아래 두개의 JOIN 방식은 동일한 결과를 가져오며, 본인이 편한 식으로 JOIN 쿼리를 작성하면 된다. 참고로 나는 첫번째 방식이 더 직관적으로 테이블 간의 관계를 이해할 수 있어서 해당 조인 방식을 사용했다.
• (설명) 데이터베이스 정보를 가진 DBS 테이블과 테이블 정보를 가진 TBLS 테이블을 DBS.DB_ID = TBLS.DB_ID 기준으로 조인한다.

 

(1) 첫번째 JOIN 방식

SELECT DBS.NAME			AS SCHEMA_
     , TBLS.TBL_NAME 	AS TBL_NAME
  FROM DBS
  JOIN TBLS
    ON DBS.DB_ID = TBLS.DB_ID;  -- 일반적인 JOIN ~ ON 조건 사용

 

(2) 두번째 JOIN 방식

SELECT DBS.NAME 		AS SCHEMA_
     , TBLS.TBL_NAME 	AS TBL_NAME
FROM DBS, TBLS
WHERE DBS.DB_ID = TBLS.DB_ID;  -- JOIN을 시켜주는 조건을 WHERE절에 적용

 

 

2. 테이블 정보 조회 (테이블명/형식/설명)

• (설명) 테이블 파라미터 정보를 가진 TABLE_PARAMS 테이블의 PARAM_KEY = 'comment' 에 테이블 설명이 저장된다. 테이블 정보를 가진 TBLS 테이블과 파라미터 정보를 가진 TABLE_PARAMS 테이블을 TBLS.TBL_ID = TP.TBL_ID 기준으로 조인한다.

SELECT DBS.NAME			   AS SCHEMA_
      , TBLS.TBL_NAME              AS TBL_NAME
      , TBLS.TBL_TYPE    	   AS OBJECT_TYPE  -- 테이블형태(MANAGED/EXTERNAL)
      , TP.TBL_COMMENT
  FROM TBLS
  JOIN DBS 
    ON DBS.DB_ID  = TBLS.DB_ID  
  LEFT JOIN (SELECT TBL_ID
  		  , PARAM_VALUE AS TBL_COMMENT 
  		 FROM TABLE_PARAMS 
  		 WHERE PARAM_KEY = 'comment'
 	   ) TP 
    ON TBLS.TBL_ID = TP.TBL_ID
  ORDER BY 1,2;

 

 

3. 테이블 정보 조회 (저장형식/서데)

• (step1) DBS와 TBLS 테이블은 DBS.DB_ID = TBLS.DB_ID로 조인
• (step2) TBLS와 SDS 테이블을 TBLS.SD_ID = SDS.SD_ID로 조인하여 INPUT / OUTPUT FORMAT 정보 생성
• (step3) SDS와 SERDES 테이블을 SDS.SERDE_ID = SERDES.SERDE_ID로 조인하여 SLIB (서데형식) 정보 생성

  SELECT DBS.NAME			  AS SCHEMA_
         , TBLS.TBL_NAME                  AS TBL_NAME
	 , SDS.INPUT_FORMAT  -- 테이블저장포맷(INPUT)
	 , SDS.OUTPUT_FORMAT  -- 테이블저장포맷(OUTPUT)
	 , SERDES.SLIB  -- 테이블서데형식
  FROM SDS
  JOIN SERDES
    ON SDS.SERDE_ID = SERDES.SERDE_ID
  JOIN TBLS
    ON TBLS.SD_ID = SDS.SD_ID
  JOIN DBS 
    ON DBS.DB_ID  = TBLS.DB_ID;

 

 

4. 테이블 경로 조회

• (설명) 테이블 정보를 가진 TBLS 테이블과 테이블 경로 정보를 가진 SDS 테이블을 TBLS.SD_ID = SDS.SD_ID 기준으로 조인한다.

SELECT DBS.NAME        AS SCHEMA_
     , TBLS.TBL_NAME   AS TBL_NAME
     , SDS.LOCATION    AS LOCATION  -- 테이블생성경로
  FROM SDS 
  JOIN TBLS
    ON TBLS.SD_ID = SDS.SD_ID
  JOIN DBS 
    ON DBS.DB_ID  = TBLS.DB_ID;

 

 

5. 가장 최근에 테이블 수정한 유저명 및 시간 조회

• 업무에서 관리할 테이블이 많아지면 어떤 유저가 해당 테이블을 수정했는지 파악하기가 어려워진다. 이를 추적하기 위해서 가장 최근에 테이블을 수정한 유저명과 시간을 조회하는 쿼리를 작성했다.
• (설명) 테이블 파라미터 정보를 가진 TABLE_PARAMS 테이블에서 PARAM_KEY = 'last_modified_by'와 PARAM_KEY = 'last_modified_time' 정보를 이용하여 조회한다.

SELECT DBS.NAME 		AS SCHEMA_
     , TBLS.TBL_NAME 		AS TBL_NAME
     , GROUP_CONCAT(IF(tp.PARAM_KEY = 'last_modified_by', tp.PARAM_VALUE, NULL)) 	AS 'LAST_MODIFY_USER' -- 최근 테이블 수정한 유저명
     , GROUP_CONCAT(IF(tp.PARAM_KEY = 'last_modified_time', DATE_FORMAT(FROM_UNIXTIME(tp.PARAM_VALUE), '%Y-%m-%d %H:%i:%S'), NULL)) 	AS 'LAST_MODIFY_TIME' -- 최근 테이블 수정한 시간
  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 LAST_MODIFY_USER IS NOT NULL;  -- 수정한 유저명이 없는 경우 제외

 

 

 

메타스토어(MetaStore)의 테이블들은 다양한 메타 정보를 담고 있기 때문에 쿼리를 통해 원하는 정보를 쉽고 빠르게 얻을 수 있다. 나도 업무를 수행하며 1천여종이 넘는 테이블의 컬럼/경로/파티션 정보를 확인하고 추출해서 전달하는 일이 많았는데, 이 과정에서 메타스토어의 활용법을 많이 익힐 수 있었다. 추가적인 내용은 다음장에 이어서 정리할 예정이고, 여러분들도 위의 내용을 참고해서 잘 활용했으면 좋겠다!