[하이브 완벽 가이드] Ch4. HiveQL : 데이터 정의(DDL)

이 글은 하이브 완벽 가이드 책을 읽고 그 중 일부 내용을 정리한 글입니다.

---

HiveQL은 하이브 쿼리 언어로, SQL과 유사하지만 여러 차이점이 존재합니다. 하이브는 로우 레벨의 삽입, 변경, 삭제를 지원하지 않으며 트랜잭션 또한 지원하지 않습니다. 대신 하둡이 지원하는 범위 안에서 보다 나은 성능을 위해 확장할 수 있는 기능을 제공하며 사용자가 정의한 확장과 외부 프로그램을 하이브와 연동할 수 있습니다.

 

데이터베이스 생성, 삭제, 변경

하이브에서 데이터베이스 개념은 단지 테이블의 카탈로그 또는 네임스페이스에 지나지 않습니다. 이는 큰 규모로 작업시 테이블명의 충돌을 막는 유용한 방법입니다. 데이터베이스를 지정하지 않는다면 기본 데이터베이스를 사용합니다.

 

다음은 CREATE DABASE 명령을 통해 데이터베이스를 생성하고 나열하는 예제입니다. 아래의 DATABASE 예약어는 SCHEMA 예약어로 대체할 수 있습니다.

 

CREATE DATABASE financials;
-- 이미 존재하는 경우 에러, 따라서 IF NOT EXISTS 추가
CREATE DATABASE IF NOT EXISTS financials;
-- 존재하는 데이터베이스 나열
SHOW DATABASES;
-- h로 시작하는 데이터베이스만 나열
SHOW DATABASES LIKE 'h.*';

 

하이브는 각 데이터베이스마다 별도의 디렉터리를 생성하고 테이블을 그 하위 디렉터리 안에 저장합니다. 데이터베이스 디렉터리는 hive.metastore.warehouse.dir 속성으로 설정한 최상위 디렉터리 밑에 생성됩니다. 따라서 위의 예제에서는 /user/hive/warehouse/financials.db 디렉터리가 생성됩니다.

 

다음 예제처럼 새로운 데이터베이스의 기본 디렉터리 위치를 변경할 수 있습니다.

 

CREATE DATABASE financials 
LOCATION '/my/preferred/directory'
COMMENT 'Holds all financials tables';

-- 확인
DESCRIBE DATABASE financials;

 

데이터베이스에 키-값 속성을 지정할 수도 있습니다. 이 속성은 DESCRIBE DATABASE EXTENDED <databases> 문에서 정보를 출력할 때만 사용합니다.

 

CREATE DATABASE financials 
WITH DBLPROPERTIES ('creator' = 'me', 'date' = '2022-09-12');

DESCRIBE DATABASE EXTENDED financials;

 

USE 명령어는 파일시스템에서 워킹 디렉터리를 변경하는 것과 비슷하게 작업 데이터베이스를 설정하는데 사용합니다. SHOW TABLES 명령어를 사용하면 USE로 지정한 데이터베이스 내에 존재하는 테이블만을 나열합니다. 현재 작업 데이터베이스를 보기 위해서는 hive.cli.print.current.db 속성을 true로 설정합니다. 

 

USE financials;
SHOW TABLES;
SET hive.cli.print.current.db=true;
USE default;

 

DROP DATABASE 명령어를 통해 데이터베이스를 삭제할 수도 있습니다. 기본적으로 하이브는 테이블이 있는 데이터베이스를 삭제하는 것을 허용하지 않기 때문에, 사용자가 테이블을 먼저 사용하거나 하이브가 테이블을 삭제하도록 명령어에 CASCADE 예약어를 붙여서 해결할 수 있습니다. RESTRICT 예약어를 붙이면 테이블을 먼저 삭제해야 하는 기본 동작과 동일합니다. 데이터베이스가 삭제되면 해당 디렉터리도 같이 삭제됩니다.

 

DROP DATABASE financials;
-- error
DROP DATABASE financials;
-- no error
DROP DATABASE IF EXISTS financials;

 

ALTER DATABASE 명령어를 이용해서 데이터베이스의 DBLPROPERTIES, OWNER, LOCATION 등의 속성을 변경할 수 있습니다. 자세한 내용은 링크를 참조합니다.

 

테이블 생성

CREATE TABLE문으로 테이블을 생성할 수 있습니다. 해당 문법은 SQL 규칙을 따르지만 데이터 파일 생성 위치나 사용할 포맷 등에 관해 다양한 유연성을 주는 확장 기능을 제공합니다.

 

CREATE TABLE IF NOT EXISTS mydb.employees (
    name			STRING,
    salary			FLOAT,
    subordinates	    	ARRAY<STRING>,
    deductions		    	MAP<STRING, FLOAT>
    address			STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>)
COMMENT 'Description of the table'
TBLPROPERTIES ('creator'='me', 'created_at'='2022-09-12 10:00:00')
LOCATION '/user/hive/warehouse/mydb.db/employees';

 

주의할 점은 테이블이 이미 존재할 때는 위의 쿼리를 수행하지 않기 때문에 테이블 스키마 변경을 위해서는 삭제 후 다시 생성을 해야한다는 점입니다.

 

이미 존재하는 테이블에서 데이터를 제외한 스키마만을 복사할 수도 있습니다. 이 방법을 사용할 때는 옵션으로 LOCATION 문을 쓸 수 있지만 스키마를 비롯한 다른 속성을 정의할 수는 없습니다.

CREATE TABLE IF NOT EXISTS mydb.employees2 LIKE mydb.employees;

 

SHOW TABLES 명령어는 테이블을 나열할 때 사용합니다. 만약 다른 데이터베이스의 테이블을 보고 싶다면 IN 예약어를 사용합니다. 정규표현식을 이용하여 나열할 테이블을 줄일 수도 있습니다.

 

SHOW TABLES;
SHOW TABLES IN mydb;
SHOW TABLES LIKE 'empl.*'

 

DESCRIBE EXTENDED 명령어로 테이블을 자세히 살펴볼 수 있고, EXTENDED를 FORMATTED로 바꾸면 읽기 편한 형식으로 더 많은 내용을 출력합니다. 

 

DESCRIBE EXTENDED mydb.employees;
DESCRIBE FORMATTED mydb.employees;

 

Managed(Internal) vs External 테이블

하이브는 기본적으로 테이블을 생성할 때 managed(internal) 모드로 테이블을 생성합니다. Managed 테이블을 삭제할 때 하이브는 테이블 내의 데이터까지 모두 삭제합니다. 그러나 테이블을 삭제해도 데이터가 삭제되기 원하지 않을 때는 external 테이블을 정의하여 하이브가 해당 데이터를 소유하지 않도록 할 수 있습니다.

 

다음은 /data/stocks 내 쉼표로 구분된 데이터를 가진 파일을 읽을 수 있는 외부 테이블을 생성하는 예제입니다.

 

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
    exchange    STRING,
    symbol      STRING,
    ...)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

 

EXTERNAL 예약어는 해당 테이블이 외부에 있으며 LOCATION 절에서 지정한 위치에 존재하는 것을 하이브에게 알려줍니다. 테이블을 삭제할 때 메타데이터는 지워지지만 해당 데이터를 지우지는 않습니다.

 

Managed / External 테이블인지 여부는 DESCRIBE FORMATTED 명령어를 사용해서 Table Type: 뒤에 오는 내용을 보고 알 수 있습니다.

 

Managed 테이블과 마찬가지로 이미 존재하는 테이블로부터 데이터를 제외하고 스키마를 복사할 수 있습니다. 이때 원본 테이블이 외부 테이블이라면 EXTERNAL 예약어를 생략하더라도 새로 생성한 테이블은 외부 테이블이 됩니다.

 

테이블 파티셔닝

내부(Managed/Internal) 테이블 파티셔닝

하이브에서는 디렉토리 분할을 통해 테이블을 파티셔닝 합니다. 아래는 예제입니다.

 

CREATE TABLE employees (
    name			STRING,
    salary			FLOAT,
    subordinates	    	ARRAY<STRING>,
    deductions		    	MAP<STRING, FLOAT>
    address			STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>)
PARTITIONED BY (country STRING, state STRING);

 

employess 테이블이 저장된 디렉토리 밑에는 country=US/state=NY 처럼 country, state별로 하위 디렉토리가 생성됩니다.

 

테이블 파티셔닝이 존재하는 이유는 빠른 쿼리를 위해서입니다. 파티셔닝된 컬럼에 대한 WHERE 조건을 걸어주면 모든 디렉토리를 볼 필요없이 해당 조건을 만족하는 디렉토리의 데이터만 살펴봅니다. 특정값을 걸러내는 WHERE 조건절을 포함할 때 이러한 조건을 파티션 필터라고 부릅니다.

 

SELECT * FROM employees
WHERE country = 'US' AND state = 'IL';

 

그러나 모든 파티션에 대한 쿼리는 테이블 데이터와 파티션 개수가 많다면 거대한 맵리듀스 작업을 유발할 수 있습니다. 따라서 하이브에서는 hive.mapred.mode 속성의 기본값을 strict 방식으로 설정하여 파티션 필터가 없는 쿼리를 금지합니다. nonstrict 방식을 설정하면 파티션 필터가 없는 쿼리도 동작합니다.

 

SET hive.mapred.mode=strict;
-- error
SELECT * FROM employees LIMIT 100;
SET hive.mapred.mode=nonstrict;
-- ok
SELECT * FROM employees LIMIT 100;

 

SHOW PARTITIONS 명령어를 이용하여 파티셔닝 정보도 볼 수 있습니다. 특정 파티션 키에 대해서만 살펴보고자 한다면 PARTITION 문을 이용하여 특정값을 가진 한 개 이상의 파티션을 선택적으로 지정할 수 있습니다.

 

SHOW PARTITIONS employees;
SHOW PARTITIONS employees PARTITION(country='US');

 

Managed 테이블에 데이터를 로드하면서 파티션을 생성할 수도 있습니다. 아래는 예제입니다.

 

LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
INTO TABLE employees
PARTITION (country='US', state='CA');

 

외부(External) 테이블 파티셔닝

하이브에서 외부 테이블도 마찬가지로 파티셔닝할 수 있으며 ALTER TABLE 문으로 파티션을 추가할 수 있습니다. (Managed 테이블에도 적용 가능)

 

ALTER TABLE log_messages ADD PARITION (year=2022, month=9, day=12)
LOCATION 'hdfs://master_server/data/log_messages/20222/09/12';

 

하이브는 파티션 디렉터리가 없거나 파일이 존재하지 않더라도 에러를 내지 않고 해당 쿼리에 결과가 없음을 반환합니다.

 

테이블 삭제

하이브에서는 SQL에서 사용하는 DROP TABLE 명령어로 테이블을 삭제합니다. 위와 마찬가지로 IF EXISTS 예약어는 옵션입니다.

 

하둡 휴지통 기능이 활성화되었을 때 데이터는 분산 파일 시스템내 .Trash 디렉터리(/user/$USER/.Trash)로 옮겨집니다. 이 기능을 활성화 시키려면 fs.trash.interval 속성을 적절한 양수값으로 설정해야 합니다. 이 값은 trash checkpoints 사이의 분 단위 숫자로, 1440이면 24시간이 됩니다. 혹시 실수로 삭제한 경우 .Trash 내의 파일을 사용해서 다시 복구할 수 있습니다. .Trash 디렉터리로 옮기고 싶지 않다면 PURGE 옵션을 사용하면 됩니다. 

 

테이블 변경

대부분의 테이블 속성은 ALTER TABLE 문을 통해 변경합니다. ALTER TABLE은 테이블의 메타데이터만을 수정하고 테이블의 데이터는 건드리지 않기 때문에 실제 데이터와 메타데이터의 일관성을 유지하는 것은 사용자의 책임입니다.

 

테이블명을 변경할 수 있습니다.

 

ALTER TABLE log_message RENAME TO logmsgs;

 

테이블 파티션을 추가하고, 변경하고, 삭제할 수 있습니다.

 

-- 추가
ALTER TABLE log_messages ADD IF NOT EXISTS
PARTITION (year=2022, month=9, day=1) LOCATION '/logs/2022/09/01'
PARTITION (year=2022, month=9, day=1) LOCATION '/logs/2022/09/02'
...;
-- 위치 변경
ALTER TABLE log_messages PARTITION (year=2022, month=9, day=1) SET LOCATION '/new_logs/2022/09/01';
-- 파티션 삭제
ALTER TABLE log_messages DROP IF EXISTS PARTITION (year=2022, month=9, day=1);

 

테이블 컬럼명이나 위치, 데이터형 혹은 주석을 변경할 수 있습니다.

 

ALTER TABLE log_message CHANGE COLUMN hms hours_minutes_seconds INT 
COMMENT 'hms to hours_minutes_seconds'
AFTER severity;

 

컬럼을 추가할 수 있습니다.

 

ALTER TABLE log_message ADD COLUMNS (
   app_name STRING COMMENT 'App Name',
   session_id LONG COMMENT 'Current Session Id');

 

컬럼을 삭제하고 교체할 수 있습니다. 

 

ALTER TABLE log_message REPLACE COLUMNS (
hours_mins_secs INT
severity        STRING
message         STRING);

 

테이블 속성을 추가하거나 변경할 수도 있지만 삭제는 불가능합니다.

 

ALTER TABLE log_messages SET TBLPROPERTIES ('creator'='me');

 

저장소 속성을 변경할 수도 있습니다.

 

-- 파티션의 저장소 포맷 변경
ALTER TABLE log_message PARITION (year=2022, month=9, day=12)
SET FILEFORMAT SEQUENCEFILE;
-- SerDe 속성 변경
ALTER TABLE table_using_JSON_stroage
SET SERDE 'com.example.JSONSerDe'
WITH SERDEPROPERTIES (
'prop1' = 'value1',
'prop2' = 'value2');
-- 파일 저장 방식 변경
ALTER TABLE stocks
CLUSTERED BY (exchange, symbol)
SORTED BY (symbol)
INTO 48 BUCKETS;

 

파티션 파일을 하둡 아카이브 파일(HAR)로 만들거나, 보호 기능을 적용할 수도 있습니다. 하둡 아카이브 파일로 만들 경우 파일시스템의 파일 수를 줄여 네임노드의 부하를 줄이지만 압축과 같은 공간 절약의 효과는 없습니다. 보호 기능은 파티션이 삭제되거나 쿼리되는 것을 막습니다.

 

-- 아카이빙, 되돌리기 위해 UNARCHIVE 쓸 수 있음
ALTER TABLE log_messages ARCHIVE PARTITION (year=2022, month=9, day=12);
-- 파티션 삭제되는것 막음, 되돌리기 위해 DISABLE 사용
ALTER TABLE log_message PARTITION (year=2022, month=9, day=12) ENABLE NO_DROP;
-- 파티션 쿼리되는 것 막음, 되돌리기 위해 DISABLE 사용
ALTER TABLE log_message PARTITION (year=2022, month=9, day=12) ENABLE OFFLINE;