하이브 스키마 직접 질의

하둡은 처리를 위해 읽고 쓰는 모든 데이터를 하둡 파일시스템 HDFS에 저장합니다. HDFS는 여타의 파일시스템처럼 파일에 대한 엄격한 스키마가 필요 없습니다. 스키마 생략은 곧 데이터를 쓰는 시점에 데이터 필드와 타입, 관계에 대한 검증이 없다는 의미이고, 이런 특징이 분산 시스템에서 빠른 읽고 쓰기를 가능하게 해주었습니다. 이런 특징으로 HDFS는 데이터가 어떻게 쓰였는지 정확하게 알지 못하면 사용할 방법이 없는 문제가 있습니다.

하이브는 HDFS에 느슨한 스키마를 부여함으로 저장된 데이터의 품질 기준을 정하고 관계형 데이터베이스(RDB)에서의 소중한 유산을 활용할 수 있는 길을 열어주었습니다. 하이브의 기능은 하이브 메타스토어 API를 통해 접근할 수 있고, 하이브가 사용하는 데이터는 주로 MySQL을 사용해서 관리합니다.

1 배경

서비스 운영을 하다보면 HDFS에 저장된 데이터가 정확한 위치에 저장되었는지, 하이브 메타스토어의 정보와 실제 HDFS의 내용이 일치하는지 검증하기 위해 하이브 메타스토어 API와 HDFS 명령을 사용해서 비교하는 작업을 진행하곤 합니다. 하지만 하이브 메타스토어는 동시성이 뛰어난 데이터베이스가 아니기 때문에 검증할 데이터가 늘어남에 따라 대량의 API 호출을 지속하는 것이 하이브 전체의 성능 저하나 서비스 중단의 문제로 이어질 가능성이 있습니다.

이번엔 가능한 서비스에 영향은 적게주고 빠르게 질의를 마치기 위해 하이브가 사용하는 MySQL에 직접 질의하기로 했습니다. 아래 글은 읽기전용 슬래이브 MySQL에 연결해서 SQL을 통해 메타스토어 API와 유사한 기능 구현하는 중에 작성한 SQL만을 정리했습니다. 테이블이나 피티션의 수가 적거나 하이브 메타스토어 장비의 성능이 매우 좋다면 이 방법을 사용할 필요는 없습니다.

2. 주요 테이블

하이브는 53개의 테이블을 통해 메타데이터를 관리합니다. 그 중 제가 관심있는 스키마와 파티션관련 주요 테이블은 다섯 개 입니다.

• DBS: 데이터베이스. 주요 필드 -> DB_ID, DB_LOCATION, NAME
• TBLS: 테이블. 주요 필드 -> TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE, VIEW_ORIGINAL_TEXT
• PARTITIONS: 파티션. 주요 필드 -> PART_ID, PART_NAME, SD_ID, TBL_ID
• PARTITION_KEY_VALS: 파티션 키, 값. 주요 필드 -> PART_ID, PART_KEY_VAL
• PARTITION_KEYS: 파티션 키. 주요 필드 -> TBL_ID, PKEY_NAME

이제 이 테이블들로부터 원하는 데이터를 질의하는 SQL문을 보겠습니다.

3. 직접 사용하는 SQL문

모든 데이터 베이스 조회

설명할 것도 없네요. show databases 와 동일한 결과가 나옵니다.

SELECT NAME FROM DBS

특정 데이터베이스의 모든 테이블 (목록, 타입) 조회

show tables 처럼 테이블의 이름과 타입을 반환합니다.

• 변수(variable)
  • database name: 테이블이 속한 데이터베이스 이름.

주어진 'database name'에 속한 모든 테이블 조회합니다.

  SELECT t.TBL_NAME, t.TBL_TYPE, t.CREATE_TIME
    FROM TBLS t
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
  WHERE d.NAME='{database name}';

테이블 타입은 MANAGED_TABLE, EXTERNAL_TABLE, VIRTUAL_VIEW 가 있습니다.

• MANAGED_TABLE: 하이브 테이블이 관리하는 데이터를 가진 테이블. 하이브 테이블을 삭제하면 데이터도 함께 지워진다.
• EXTERNAL_TABLE: 하이브가 메타데이터만 관리하는 테이블. 하이브 테이블을 삭제해도 데이터는 남아있다.
• VIRTUAL_VIEW: 뷰. 이 글에서는 하이브 뷰와 Presto 뷰로 나눠서 처리한다.

테이블 존재 여부 확인

tbl_exist 값은 테이블이 존재하면 1, 존재하지 않으면 0 입니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT COUNT(t.DB_ID) as tbl_exist FROM TBLS t
    JOIN DBS d ON t.DB_ID = d.DB_ID
    WHERE d.NAME = '{database name}' AND TBL_NAME = '{table name}';

특정 날짜에 생성된 파티션 이름 목록

특정 테이블의 파티션을 확인할 때, 전체 파티션을 다 보기보단 어떤 날짜에 동작한 배치(batch)의 결과를 확인하는 과정에서 날짜 기준으로 파티션을 확인합니다. 일별(daily) 또는 시간별(hourly) 주기적으로 생성되는 데이터는 첫 파티션 키를 날짜(년, 월, 일, 시간, 분) 단위로 삼는 경우가 많습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름
  • partition key: 날짜 단위의 파티션 키
  • partition value: 날짜 단위의 파티션 값

  SELECT p.PART_NAME
    FROM PARTITIONS p
    JOIN TBLS t
      ON t.TBL_ID = p.TBL_ID
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
    WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'
      AND PART_NAME like '{partition_key}={partition value}%';

가장 최근 파티션 값

하이브 스키마는 데이터와 느슨한 결합을 특징으로 합니다. 즉, 파티션의 키나 위치 정보를 운영 중에 변경할 수 있기 때문에 그 이력을 PARTITION_KEY_VALS 테이블로 관리합니다.

아래 질의는 가장 최근 파티션의 PART_ID로 PARTITION_KEY_VALS 의 값을 얻습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT v.PART_KEY_VAL
    FROM PARTITION_KEY_VALS v
    INNER JOIN (
        SELECT t.TBL_ID, MAX(p.PART_ID) as MAX_PART_ID
          FROM TBLS t
          JOIN PARTITIONS p
            ON t.TBL_ID = p.TBL_ID
          JOIN DBS d
            ON t.DB_ID = d.DB_ID
         WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'
    ) m on v.PART_ID = MAX_PART_ID

파티션 키 목록

테이블의 파티션 키, 타입, 주석을 가져옵니다. INTEGER_IDX 는 파티션 순서를 올바르게 가져오기 위해 필요합니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT p.PKEY_NAME, p.PKEY_TYPE, p.PKEY_COMMENT
    FROM PARTITION_KEYS p
    JOIN TBLS t
      ON t.TBL_ID = p.TBL_ID
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
    WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'
    ORDER BY INTEGER_IDX;

파티션 값 목록

특정 파티션의 값을 가져옵니다. 이미 파티션을 알고 있는 경우라서 직접 partition part 문자열을 파싱해도 어렵진 않습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름
  • partition part:

  SELECT v.PART_KEY_VAL
    FROM PARTITION_KEY_VALS v
    JOIN PARTITIONS p
      ON v.PART_ID = p.PART_ID
    JOIN TBLS t
      ON t.TBL_ID = p.TBL_ID
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
    WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'
      AND p.PART_NAME = '{partition part}';

하이브 뷰 CREATE VIEW sql 문 확인

테이블 타입이 VIRTUAL_VIEW 인 경우에 뷰(VIEW)를 만드는 SQL 문이 VIEW_ORIGINAL_TEXT 컬럼에 들어있습니다. 하이브 명령은 SHOW CREATE TABLE 명령과 동일 결과를 보여줍니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT t.VIEW_ORIGINAL_TEXT
    FROM TBLS t
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
   WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'

프레스토 뷰의 CREATE VIEW sql 문 확인

프레스토(Presto)에서 만든 뷰는 직접 확인할 수 없습니다. 앞에서 살펴본 하이브 뷰의 CREATE VIEW sql 문으로 얻은 VIEW_ORIGINAL_TEXT 를 디코딩 해야합니다. 프레스토는 VIEW 내용을 json 형태로 구조화하고 base64 인코딩해서 저장합니다. 이를 역순으로 수행하면 원하는 내용을 얻을 수 있습니다.

import base64
import json

enc_text = # VIEW_ORIGINAL_TEXT
orig_text_body = enc_text.replace('/* Presto View:', '').replace('*/', '')
decoded = base64.b64decode(orig_text_body)
view_meta_json = json.loads(decoded)
view_meta_json['originalSql'] # CREATE VIEW 문장

테이블 컬럼 목록

테이블의 컬럼 목록을 얻어옵니다. 파티션 키를 얻어올 때와 같이 INTEGER_IDX 순서로 가져와야 올바른 순서로 확인할 수 있습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT c.* FROM TBLS t
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
    JOIN SDS s
      ON t.SD_ID = s.SD_ID
    JOIN COLUMNS_V2 c
      ON s.CD_ID = c.CD_ID
    WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'
    ORDER by INTEGER_IDX;

프레스토 뷰 컬럼 목록

프레스토 뷰와 동일한 디코딩 방법으로 프레스토 뷰의 컬럼 정보를 확인합니다.

import base64
import json

enc_text = # VIEW_ORIGINAL_TEXT
orig_text_body = enc_text.replace('/* Presto View:', '').replace('*/', '')
decoded = base64.b64decode(orig_text_body)
view_meta_json = json.loads(decoded)
[c['name'] for c in view_meta_json('columns')] # 컬럼 정보

테이블 속성

하이브에서 테이블 생성시 키=값 형태로 테이블의 속성을 부여할 수 있습니다. 이를 TBLPROPERTIES 라고 하고 "orc.compress"="ZLIB" 같은 유용한 속성들을 설정해서 사용할 수 있습니다. (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL)

테이블을 생성한 이후에는 하이브 명령 desc formatted 를 통해 Table Parameters 항목에서 그 값을 확인할 수 있는데요, MySQL의 TABLE_PARAMS 테이블에 해당 정보가 있습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT r.PARAM_KEY, r.PARAM_VALUE
    FROM TBLS t
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
    JOIN TABLE_PARAMS r
      ON t.TBL_ID = r.TBL_ID
    WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'

테이블 데이터(파티션) 위치

테이블 파티션의 위치는 HDFS 상의 위치를 가리킵니다. 이 정보는 TBLS 에 없고, 대신 관습적으로 데이터베이스의 위치 DBS.DB_LOCATION_URI 와 테이블 이름의 결합으로 확인할 수있습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT CONCAT(d.DB_LOCATION_URI, '/', t.TBL_NAME) AS LOCATION
    FROM TBLS t
    JOIN DBS d
      ON t.DB_ID = d.DB_ID
    WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'

가장 최근 파티션 위치

가장 최근에 생성된 파티션 이름을 통해 역으로 마지막으로 성공한 파티션 생성 작업을 알 수 있습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT CONCAT(m.LOCATION, '/', p.PART_NAME) AS LOCATION
    FROM PARTITIONS p
  INNER JOIN (
    SELECT t.TBL_ID, MAX(p.PART_ID) as MAX_PART_ID,
            CONCAT(d.DB_LOCATION_URI, '/', t.TBL_NAME) AS LOCATION
      FROM TBLS t
      JOIN PARTITIONS p
        ON t.TBL_ID = p.TBL_ID
      JOIN DBS d
        ON t.DB_ID = d.DB_ID
     WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'
  ) m ON m.MAX_PART_ID = p.PART_ID;

SERDE

HDFS 데이터의 형태는 매우 다양합니다. 데이터는 목적에 맞게 직렬화(serialization)과 Sequence File, RC File, ORC, Parquet 중 하나의 방법을 선택하거나 고유의 Serde (serial/deserialization) 구현을 하기도 합니다. 테이블의 SERDE 는 SERDES.SLIB 과 SDS.OUTPUT_FORMAT 을 통해 확인할 수 있습니다.

• 변수
  • database name: 테이블이 속한 데이터베이스 이름.
  • table name: 원하는 테이블 이름

  SELECT r.SLIB AS SERDE, s.OUTPUT_FORMAT, CONCAT(m.LOCATION, '/', p.PART_NAME) AS LOCATION
    FROM PARTITIONS p
    JOIN SDS s
      ON s.SD_ID = p.SD_ID
    JOIN SERDES r
      ON s.SD_ID = r.SERDE_ID
  INNER JOIN (
    SELECT t.TBL_ID, MAX(p.PART_ID) as MAX_PART_ID, CONCAT(d.DB_LOCATION_URI, '/', t.TBL_NAME) AS LOCATION
      FROM TBLS t
      JOIN PARTITIONS p
        ON t.TBL_ID = p.TBL_ID
      JOIN DBS d
        ON t.DB_ID = d.DB_ID
     WHERE d.NAME='{database name}' AND TBL_NAME='{table name}'
  ) m ON m.MAX_PART_ID = p.PART_ID;

4. 맺음말

이번 글에서는 하이브 메타스토어 API를 통하지 않고 직접 MySQL에 질의해서 동일한 정보를 읽는 방법을 알아봤습니다. 저는 실제 읽기전용 슬래이브의 테이블에 제한된 권한으로 접속하여 읽기만 수행했습니다. 서비스 중인 데이터를 다루실 때는 직접 MySQL 데이터를 업데이트 하거나 삭제하지 않도록 주의하시기 바랍니다.