[하이브 완벽 가이드] Ch6. HiveQL : 쿼리

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

---

SELECT ... FROM .. WHERE ... 절

하이브에서 지원하는 컬렉션 데이터형에는 크게 세 종류가 있습니다.

1. ARRAY : 0으로 시작하는 index를 이용해서 참조. 배열의 크기를 벗어난 index 참조시 NULL 반환 ex) subordinates[0]
2. MAP : 숫자 대신 키 값을 색인으로 사용해서 참조. ex) deductions["State Taxes""]
3. STRUCT : 점 표기법을 사용해서 참조. ex) address.city

 

컬럼을 선택할 때 정규표현식을 사용할 수도 있습니다. 아래는 stocks에서 symbol 컬럼과 price로 시작하는 모든 컬럼을 선택하는 쿼리입니다.

 

SELECT symbol, 'price.*' FROM stocks;

 

하이브에서 사용하는 여러 연산, 함수에 관한 내용은 해당 링크를 참조합니다.

 

산술 연산자는 어떤 숫자 데이터형도 받아들입니다. 두 피연산자가 같은 숫자 데이터형이라면 강제로 형변환을 하지 않습니다. 두 데이터형이 같지 않은 경우 더 작은 값을 더 넓은 데이터형으로 변환합니다. 예를 들어 INT, BIGINT 피연산자가 있을 경우 INT는 BIGINT로 변환되고 INT, FLOAT 피연산자가 있을 경우 INT는 FLOAT으로 변환됩니다. 산술 연산 시 데이터 오버플로우와 언더플로우를 조심해야 합니다. 

 

집계 함수는 여러 로우에 대해서 어떤 계산을 한 후 하나의 값을 반환합니다. 하이브의 집계 처리의 성능을 향상시키기 위해서는 hive.map.aggr 속성값을 true로 설정합니다.

 

SET hive.map.aggr=true;
SELECT count(*), avg(salary) FROM employees;

 

보통 쿼리는 결과로 많은 수의 로우를 반환할 수 있습니다. LIMIT 절은 반환되는 로우 개수의 상한치를 설정합니다.

 

하이브는 대부분의 쿼리에서 맵리듀스를 동작시킵니다. 그런데 하이브는 로컬 모드 환경에서 맵리듀스를 사용하지 않고도 동작하는 몇몇 쿼리를 제공합니다. 예를 들어 아래와 같은 쿼리가 그렇습니다.

 

SELECT * FROM employees;
SELECT * FROM employees
-- 파티션 키 컬럼
WHERE country = 'US' AND state = 'CA' LIMIT 100;

 

만약 hive.exec.mode.local.auto 속성을 true로 설정하면 하이브는 다른 연산도 로컬 모드에서 실행하려고 시도할 것입니다. 그렇지 않다면 하이브는 다른 모든 쿼리를 실행하기 위해서 맵리듀스를 사용합니다.

 

서로 다른 부동소수점 데이터형(FLOAT, DOUBLE)의 숫자를 비교할 때 문제가 발생합니다. 이를 해결하기 위해 CAST 연산자를 사용해서 넓은 데이터형을 작은 데이터형을 형변환 해줍니다. 부동소수점을 비교할 때는 작은 데이터형에서 넓은 데이터형으로의 묵시적 형변환은 모두 피해야 합니다.

 

SELECT name, salary, deductions['Federal Taxes'] FROM employees
WHERE deductions['Federal Taxes'] > CAST(0.2 AS FLOAT);

 

LIKE 연산자를 사용하면 어떤 서브 문자열이 문자열의 시작이나 끝, 혹은 문자열 내에서 일치하는지를 확인할 수 있습니다. 하이브는 LIKE 절을 자바 정규표현식을 사용할 수 있는 RLIKE(REGEXP) 절로 확장하였습니다. 아래는 주소에 Chicago나 Ontario가 포함된 거리 이름을 갖는 모든 사원을 찾는 예제입니다.

 

-- . 문자는 어떤 한 문자와 일치
-- * 문자는 왼쪽에 있는 문자가 0번에서 여러 번 반복되는 것을 의미
-- (x|y) 표현식은 x 혹은 y가 일치하는 것을 의미
SELECT name, address.street FROM employees
WHERE address.street RLIKE '.*(Chicago|Ontario).*';

 

GROUP BY 절

GROUP BY 문은 하나 이상의 컬럼의 결과를 그룹으로 만들고 각 그룹에 대해서 집계 연산을 수행하는 집계 함수와 함께 자주 사용합니다. 아래 쿼리는 애플의 주식 레코드를 년 단위로 그룹을 만든 후 각 년도 종가의 평균을 구하는 예제입니다.

 

SELECT year(ymd), avg(price_close) FROM stocks
WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
GROUP BY year(ymd);

 

HAVING 절을 사용하면 GROUP BY가 생성한 그룹에 대해서 간단히 서브쿼리의 표현을 제약할 수 있습니다. 위 쿼리에 평균 종가가 50.5달러보다 큰 년도만 나오도록 결과를 제한하면 다음과 같습니다.

 

SELECT year(ymd), avg(price_close) FROM stocks
WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
GROUP BY year(ymd)
HAVING avg(price_close) > 50.0;

 

조인 문

하이브는 EQUI-JOIN 만을 제공하고 NON-EQUI-JOIN은 허용하지 않습니다. 다음은 하이브에서 동작하지 않는 쿼리의 예시입니다.

 

SELECT a.ymd, a.price_close, b.price_close
FROM stocks a JOIN stocks b ON a.ymd <= b.ymd;

 

또한 하이브는 현재 ON 절에서 OR 사용을 지원하지 않고 있습니다.

 

하이브는 세 개 이상의 테이블을 조인할 때 ON 절에서 같은 조인 키를 사용한다면 하나의 맵 리듀스 잡을 사용합니다. 또한 하이브는 쿼리의 마지막 테이블이 가장 크다고 가정하기 때문에 다른 테이블을 버퍼링하려고 시도하고 각 레코드에 대해서 조인을 수행하면서 마지막 테이블을 흘려보냅니다. 따라서 조인 쿼리를 구성할 때 가장 큰 테이블이 가장 마지막에 오도록 해야 합니다.

 

 그리고 하이브는 조인을 수행한 후에 WHERE 절을 평가하기 때문에 중첩 SELECT 문을 사용해서 쿼리를 최적화 할 수 있습니다.

 

-- 중첩 SELECT문으로 쿼리 최적화 예시
SELECT s.ymd, s.symbol, s.price_close, d.dividend FROM
(SELECT * FROM stocks WHERE symbol = 'AAPL' AND exchange = 'NASDAQ') s
LEFT OUTER JOIN
(SELECT * FROM dividends WHERE symbol = 'AAPL' AND exchange = 'NASDAQ') d
ON s.ymd = d.ymd;

 

LEFT SEMI-JOIN은 오른쪽 테이블에서 ON의 술어를 만족하는 레코드를 찾을 경우 왼쪽 테이블의 레코드를 반환합니다. 이는 일반적인 SQL 문법에서 IN ... EXISTS와 동일합니다.

 

SELECT s.ymd, s.symbol, s.price_close
FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol;

 

하이브는 조인 조건을 걸어주지 않으면 카타시안 프로덕트 조인을 수행합니다. 

 

SELECT * FROM stocks JOIN dividends;

 

예를 들어 다음 쿼리는 WHERE 절을 적용하기 전에 FULL CARTESIAN PRODUCT를 실행하기 때문에 아주 긴 시간이 걸립니다.

 

SELECT * FROM stocks s JOIN dividends d
WHERE s.symbol = d.symbol AND s.symbol = 'AAPL';

 

hive.mapred.mode 속성이 strict로 되어있으면 하이브는 사용자가 부주의로 카타시안 프로덕트를 실행하는 것을 막습니다.

 

ORDER BY, SORT BY, DISTRIBUTE BY, CLUSTER BY

ORDER BY는 쿼리 결과 집합에 대해서 전체 정렬(total ordering)을 수행합니다. 이는 모든 데이터가 하나의 리듀서로 가는 것을 의미하며 데이터셋의 크기가 클 경우 오랜 시간이 거릴 수 있습니다. ORDER BY 수행시간이 너무 길 수 있기 때문에 하이브는 hive.mapred.mode가 strict로 설정되었을 경우 LIMIT을 요구합니다.

 

또한, 하이브는 ORDER BY 대신 데이터를 각 리듀서에서 정렬하는 SORT BY를 추가했습니다. 이는 각 리듀서의 출력이 정렬되도록 지역 정렬(local ordering)을 수행하는 것을 의미합니다. 

 

DISTRIBUTE BY는 맵의 출력을 리듀서로 어떻게 나누어 보내는지를 제어합니다. 하둡은 맵리듀스 잡을 따라 흘러가는 모든 데이터를 키-값 쌍으로 구성합니다. 기본적으로 맵리듀스는 맵퍼가 출력하는 키에 대해서 해시 값을 계산하고 해시값을 이용하여 키-값 쌍을 가용한 리듀서로 균등하게 분산하려고 노력합니다. 하둡이 같은 값을 가진 레코드를 같은 리듀서로 보내는 것을 보장하기 위해 DISTRIBUTE BY를 사용합니다. 그리고 리듀서별로 데이터를 정렬하기 위해 SORT BY를 사용합니다.

 

SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
DISTRIBUTE BY s.symbol
SORT BY s.symbol ASC, s.ymd ASC;

 

CLUSTER BY는 같은 정렬 기준 컬럼에 대해 DISTRIBUTE BY와 SORT BY를 모두 적용한 것과 동일합니다.

 

데이터 표본을 만드는 쿼리

하이브는 테이블을 버킷으로 구성하여 표본을 만드는 쿼리로 TABLESAMPLE 함수를 지원합니다. 또한 로우를 기반으로 표본을 만드는 것 대신 블록의 백분율로 표본을 만들 수도 있습니다. 자세한 내용은 링크를 참조합니다.

 

UNION ALL

UNION ALL은 두 개 이상의 테이블을 합칩니다. 유니온 쿼리의 각 서브쿼리는 같은 수의 컬럼을 만들어야 하고 같은 위치에 있는 각 컬럼의 데이터형은 모두 일치해야 합니다. 유니온 쿼리를 이용하여 복잡한 WHERE 절을 쪼개어 가독성을 증가시킬 수 있지만 소스 테이블이 인덱싱되어 있지 않다면 테이블 스캔을 여러번 수행하기 때문에 비효율적입니다. 아래는 이에 대한 예제입니다.

 

FROM (
    FROM src SELECT src.key, src.value WHERE src.key < 100
    UNION ALL
    FROM src SELECT src.* WHERE src.key > 110
) unioninput
INSERT OVERWRITE DIRECTORY '/tmp/union.out' SELECT unioninput.*;