현재 업무에서는 임팔라를 주력 데이터베이스로 활용하고 있다. 오라클 엑사 DW에서 하둡으로 ETL 한 뒤, 운영 DW에는 하기 부담스러운 헤비한 쿼리를 하는데에 사용한다.
원천이 오라클 데이터베이스인 탓에 소스 데이터베이스인 오라클과 주기적으로 스키마를 맞춰주어야 하는데, 오라클에는 ALL_TABLES, ALL_TAB_COLUMNS 와 같은 시스템 카탈로그가 있어, 테이블 스키마 관련 필요 정보를 쿼리해보기 좋았는데 임팔라에는 이런 시스템 카탈로그가 기본 제공되고 있지 않아 메타 조회를 하는데 번거로움이 있어 두 데이터베이스 간의 스키마를 비교해보기가 쉽지 않았다.
이런 작업을 하는데에 있어서 HUE의 테이블 브라우저를 이용해 일일이 눈으로 비교할 수는 없는 노릇이고 Impala에서 describe 를 이용한 외부 작업을 만드는 것도 효율적이지 않은 것 같아 찾아보니, Impala는 내부적으로 Hive Metastore 를 통해 스키마를 관리하고 있었고 Hive 에서 제공하는 시스템 카탈로그를 이용해 필요한 정보를 찾아낼 수 있었다.
hive repository 데이터베이스인 metastore 의 DBS, TBLS, SDS, PARTITION_KEYS, COLUMNS_V2 가 바로 그것들이었고 이 테이블들을 바탕으로 다음과 같은 VIEW 를 생성해 활용하고 있다.
- ALL_TAB_COLUMNS [for Impala]
- OWNER
- TABLE_NAME
- COLUMN_NAME
- DATA_TYPE
- COLUMN_ID
- IS_PARTKEY
- INPUT_FORMAT
- DB_NAME
ALL_TAB_COLUMNS for Impala DDL
임팔라를 운영하는데 있어서 몇가지 필요한 컬럼을 추가해 위와 같이 설계했고 DDL은 다음과 같다. 다음 SQL은 Impala에서 지원하는 문법 기반으로 짜여진 것으로, DBS, TBLS, SDS, PARTITION_KEYS, COLUMNS_V2 테이블을 Impala 테이블로 동기화한 뒤에 활용할 수 있다. 개인 로컬 테스트 환경인 CDH 데모 이미지의 MySQL 5.1 버전에서 다음 쿼리를 해보니 WITH 절과 ROW_NUMBER() 와 같은 윈도우 쿼리가 지원되지 않아 해당 버전에 맞게 다시 작성해봐야 할 듯 하다.
CREATE VIEW ALL_TAB_COLUMNS
AS
WITH TABS AS (SELECT B.`OWNER`
, B.TBL_NAME AS TABLE_NAME
, CASE C.INPUT_FORMAT
WHEN 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' THEN 'PARQUET'
WHEN 'org.apache.mapreduce.KuduTableInputFormat' THEN 'KUDU'
WHEN 'org.apache.hadoop.mapred.TextInputFormat' THEN 'TEXT'
END AS INPUT_FORMAT
, A.NAME AS DB_NAME
, B.TBL_ID
, C.CD_ID
FROM DBS A INNER JOIN
TBLS B ON (A.DB_ID = B.DB_ID) INNER JOIN
SDS C ON (B.SD_ID = C.SD_ID)
)
SELECT A.`OWNER`
, A.TABLE_NAME
, A.COLUMN_NAME
, A.DATA_TYPE
, ROW_NUMBER() OVER(PARTITION BY A.`OWNER`, A.TABLE_NAME ORDER BY A.INTEGER_IDX ASC) COLUMN_ID
, A.IS_PARTKEY
, A.INPUT_FORMAT
, A.DB_NAME
FROM (SELECT A.`OWNER`
, A.TABLE_NAME
, B.PKEY_NAME AS COLUMN_NAME
, B.PKEY_TYPE AS DATA_TYPE
, B.INTEGER_IDX - 100 AS INTEGER_IDX
, 'Y' IS_PARTKEY
, A.INPUT_FORMAT
, A.DB_NAME
FROM TABS A INNER JOIN
PARTITION_KEYS B ON (A.TBL_ID = B.TBL_ID)
UNION ALL
SELECT A.`OWNER`
, A.TABLE_NAME
, B.COLUMN_NAME
, B.TYPE_NAME AS DATA_TYPE
, B.INTEGER_IDX AS INTEGER_IDX
, 'N' IS_PARTKEY
, A.INPUT_FORMAT
, A.DB_NAME
FROM TABS A INNER JOIN
COLUMNS_V2 B ON (A.CD_ID = B.CD_ID)
) A임팔라는 오라클 만큼 세부적인 설정이 없어 위와 같은 컬럼만으로도 당장은 사용하는 데에 크게 무리가 없는 것 같은데 오라클의 ALL_TAB_COLUMNS 에 비하면 많이 초라한 것 같기도.. 추후 다른 수요가 생기게 되면 고도화를 해봐야 겠다.
Hive 시스템 카탈로그
- DBS
mysql> desc DBS;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| DB_ID | bigint(20) | NO | PRI | NULL | |
| DESC | varchar(4000) | YES | | NULL | |
| DB_LOCATION_URI | varchar(4000) | NO | | NULL | |
| NAME | varchar(128) | YES | UNI | NULL | |
| OWNER_NAME | varchar(128) | YES | | NULL | |
| OWNER_TYPE | varchar(10) | YES | | NULL | |
+-----------------+---------------+------+-----+---------+-------+- TBLS
mysql> desc TBLS;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| TBL_ID | bigint(20) | NO | PRI | NULL | |
| CREATE_TIME | int(11) | NO | | NULL | |
| DB_ID | bigint(20) | YES | MUL | NULL | |
| LAST_ACCESS_TIME | int(11) | NO | | NULL | |
| OWNER | varchar(767) | YES | | NULL | |
| RETENTION | int(11) | NO | | NULL | |
| SD_ID | bigint(20) | YES | MUL | NULL | |
| TBL_NAME | varchar(128) | YES | MUL | NULL | |
| TBL_TYPE | varchar(128) | YES | | NULL | |
| VIEW_EXPANDED_TEXT | mediumtext | YES | | NULL | |
| VIEW_ORIGINAL_TEXT | mediumtext | YES | | NULL | |
| LINK_TARGET_ID | bigint(20) | YES | MUL | NULL | |
+--------------------+--------------+------+-----+---------+-------+- SDS
mysql> desc SDS;
+---------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------+------+-----+---------+-------+
| SD_ID | bigint(20) | NO | PRI | NULL | |
| CD_ID | bigint(20) | YES | MUL | NULL | |
| INPUT_FORMAT | varchar(4000) | YES | | NULL | |
| IS_COMPRESSED | bit(1) | NO | | NULL | |
| IS_STOREDASSUBDIRECTORIES | bit(1) | NO | | NULL | |
| LOCATION | varchar(4000) | YES | | NULL | |
| NUM_BUCKETS | int(11) | NO | | NULL | |
| OUTPUT_FORMAT | varchar(4000) | YES | | NULL | |
| SERDE_ID | bigint(20) | YES | MUL | NULL | |
+---------------------------+---------------+------+-----+---------+-------+- PARTITION_KEYS
mysql> desc PARTITION_KEYS;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| TBL_ID | bigint(20) | NO | PRI | NULL | |
| PKEY_COMMENT | varchar(4000) | YES | | NULL | |
| PKEY_NAME | varchar(128) | NO | PRI | NULL | |
| PKEY_TYPE | varchar(767) | NO | | NULL | |
| INTEGER_IDX | int(11) | NO | | NULL | |
+--------------+---------------+------+-----+---------+-------+- COLUMNS_V2
mysql> desc COLUMNS_V2;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CD_ID | bigint(20) | NO | PRI | NULL | |
| COMMENT | varchar(256) | YES | | NULL | |
| COLUMN_NAME | varchar(128) | NO | PRI | NULL | |
| TYPE_NAME | varchar(4000) | YES | | NULL | |
| INTEGER_IDX | int(11) | NO | | NULL | |
+-------------+---------------+------+-----+---------+-------+
