LanguageManual - Group By

Hive에서 GROUP BY를 사용하는 방법을 정리한 문서예요. 기본 구문부터 위치 기반 컬럼 지정, 멀티 그룹 인서트, 맵사이드 집계, GROUPPING SETS/CUBE/ROLLUP 같은 고급 기능까지 다룹니다.

출처: 문서

본문

Group By 구문 (Group By Syntax)

groupByClause: GROUP BY groupByExpression (, groupByExpression)*

groupByExpression: expression

groupByQuery: SELECT expression (, expression)* FROM src groupByClause?

groupByExpression에서 컬럼은 위치 번호가 아니라 이름으로 지정됩니다. 단, Hive 0.11.0 이상에서는 다음과 같이 구성하면 위치로도 지정할 수 있습니다:

Hive 0.11.0 ~ 2.1.x: hive.groupby.orderby.position.alias를 true로 설정(기본값 false).
Hive 2.2.0 이상: hive.groupby.position.alias를 true로 설정(기본값 false).

간단한 예제 (Simple Examples)

테이블의 행 수를 세려면:

  SELECT COUNT(*) FROM table2;

HIVE-287이 포함되지 않은 Hive 버전에서는 COUNT(*) 대신 COUNT(1)을 사용해야 합니다.

성별별 고유 사용자 수를 세려면 다음 쿼리를 작성할 수 있습니다:

  INSERT OVERWRITE TABLE pv_gender_sum
  SELECT pv_users.gender, count (DISTINCT pv_users.userid)
  FROM pv_users
  GROUP BY pv_users.gender;

여러 집계를 동시에 할 수 있지만, 서로 다른 DISTINCT 컬럼을 가진 두 집계는 동시에 할 수 없습니다. 예를 들어 count(DISTINCT)와 sum(DISTINCT)가 같은 컬럼을 지정하므로 다음은 가능합니다:

  INSERT OVERWRITE TABLE pv_gender_agg
  SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
  FROM pv_users
  GROUP BY pv_users.gender;

HIVE-287이 포함되지 않은 Hive 버전에서는 COUNT(*) 대신 COUNT(1)을 사용해야 합니다.

그러나 다음 쿼리는 허용되지 않습니다. 같은 쿼리에서 여러 DISTINCT 표현식을 허용하지 않습니다.

  INSERT OVERWRITE TABLE pv_gender_agg
  SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
  FROM pv_users
  GROUP BY pv_users.gender;

Select 문과 group by 절 (Select statement and group by clause)

group by 절을 사용할 때 select 문은 group by 절에 포함된 컬럼만 포함할 수 있습니다. 물론 select 문에는 원하는 만큼 많은 집계 함수(예: count)를 넣을 수 있습니다. 간단한 예를 들어봅시다.

CREATE TABLE t1(a INTEGER, b INTGER);

위 테이블에 대한 group by 쿼리는 다음과 같을 수 있습니다:

SELECT
   a,
   sum(b)
FROM
   t1
GROUP BY
   a;

select 절에 a(group by 키)와 집계 함수(sum(b))가 포함되어 있으므로 위 쿼리는 동작합니다.

그러나 아래 쿼리는 동작하지 않습니다:

SELECT
   a,
   b
FROM
   t1
GROUP BY
   a;

이는 select 절에 group by 절에 포함되지 않은(그리고 집계 함수도 아닌) 추가 컬럼(b)이 있기 때문입니다. 테이블 t1이 다음과 같다고 가정해 봅시다:

a    b
------
100  1
100  2
100  3

그룹핑이 a에만 수행되므로, a=100 그룹에 대해 Hive가 표시해야 할 b 값은 무엇일까요? 첫 번째 값이어야 한다고 주장할 수도, 최소값이어야 한다고 주장할 수도 있지만, 여러 가능한 옵션이 있다는 데 모두 동의할 것입니다. Hive는 select 절에 group by 절에 포함되지 않은 컬럼을 두는 것을 유효하지 않은 SQL(HQL, 정확히는)로 만들어 이런 추측을 없앴습니다.

고급 기능 (Advanced Features)

멀티 그룹-By 인서트 (Multi-Group-By Inserts)

집계 또는 단순 select의 출력은 여러 테이블로, 심지어 hadoop dfs 파일(그 후 hdfs 유틸리티로 조작 가능)로도 보낼 수 있습니다. 예를 들어 성별 분석과 함께 연령별 고유 페이지뷰 분석이 필요하다면 다음 쿼리로 해결할 수 있습니다:

  FROM pv_users
  INSERT OVERWRITE TABLE pv_gender_sum
    SELECT pv_users.gender, count(DISTINCT pv_users.userid)
    GROUP BY pv_users.gender
  INSERT OVERWRITE DIRECTORY '/user/facebook/tmp/pv_age_sum'
    SELECT pv_users.age, count(DISTINCT pv_users.userid)
    GROUP BY pv_users.age;

Group By의 맵사이드 집계 (Map-side Aggregation for Group By)

hive.map.aggr은 집계 방식을 제어합니다. 기본값은 false입니다. true로 설정하면 Hive가 1차 집계를 map 태스크에서 직접 수행합니다. 보통 더 나은 효율을 제공하지만 성공적으로 실행하기 위해 더 많은 메모리가 필요할 수 있습니다.

  set hive.map.aggr=true;
  SELECT COUNT(*) FROM table2;

HIVE-287이 포함되지 않은 Hive 버전에서는 COUNT(*) 대신 COUNT(1)을 사용해야 합니다.

Grouping Sets, Cubes, Rollups, 그리고 GROUPING__ID 함수

Version

Grouping sets, CUBE·ROLLUP 연산자와 GROUPING__ID 함수는 Hive 0.10.0 릴리스에서 추가되었습니다.

이 집계 연산자에 대한 정보는 Enhanced Aggregation, Cube, Grouping and Rollup 문서를 참고하세요.

다음 JIRA도 참고하세요:

HIVE-2397 Support with rollup option for group by
HIVE-3433 Implement CUBE and ROLLUP operators in Hive
HIVE-3471 Implement grouping sets in Hive
HIVE-3613 Implement grouping_id function

Hive 0.11.0 릴리스 신규:

HIVE-3552 많은 수의 grouping set 키에 대해 cubes/rollups/grouping sets를 수행하는 성능 개선

더 알아보기 (Learn more)

GROUP BY는 그룹 키 컬럼과 집계 함수만 select에 올릴 수 있고, 여러 DISTINCT를 같은 쿼리에 쓸 수 없어요. 멀티 테이블/디렉터리 인서트, hive.map.aggr 맵사이드 집계, 그리고 CUBE/ROLLUP/GROUPING__ID 등 고급 집계는 참고 문서를 함께 보면 좋아요.