하이브Hive 입문하기최범균, 2014-01-07

참고서

* 하이브 완벽 가이드에서 쿼리 발췌

하이브Hive란?

● 하둡 데이터(파일)를 SQL과 비슷한 쿼리를 이용해서 다룰 수 있게 해 주는 기술

● DW 어플리케이션에 적합○ 하둡에 기반

■ 대량의 데이터를 배치 연산■ 레코드 단위 갱신/삭제, 트랜잭션 지원 안 함

○ SQL과 유사한 하이브 QL 지원■ 테이블 생성, 데이터 조회, 테이블 간 조인 지원

하이브 구조

CLI(Command Line Interface)

Hive Server(Thrift Server)

HWI(Web Interface)

메타스토어(RDBMS)

하둡 클러스터웹브라우저

Thrift ClientJDBC

하이브 DB

● 데이터베이스○ 테이블의 네임스페이스○ 테이블을 논리적인 묶음으로 구성하는데 사용

● 관련 쿼리○ 생성 쿼리

■ CREATE DATABASE dbname;■ CREATE DATABASE IF NOT EXISTS dbname;

○ 조회■ SHOW DATABASES;

○ 설명■ DESCRIBE DATABASE dbname;

데이터베이스와 파일시스템

● 데이터베이스/테이블은 파일시스템 경로와 매칭

hive> describe database default; OKdefault Default Hive database hdfs://bt0:9000/user/hive/warehouse

hive> describe database madvirus; OKdefault Default Hive database hdfs://bt0:9000/user/hive/warehouse/madvirus.db

하이브 테이블

● 테이블○ RDBMS 테이블과 유사한 스키마 가짐○ 파일 시스템의 파일을 테이블 데이터로 사용

● 테이블의 종류○ 관리(managed) 테이블

■ 하이브가 데이터 파일을 관리■ 테이블 삭제시 메타 정보와 파일 함께 삭제

○ 외부(external) 테이블■ 기존 파일을 테이블의 데이터로 사용■ 테이블 삭제시 메타 정보만 삭제

관리 테이블 생성

● 생성 쿼리CREATE TABLE IF NOT EXISTS dbname.tablename (

컬럼1이름 타입 COMMENT ‘설명’,컬럼2이름 타입 COMMENT ‘설명2’,…컬럼n이름 타입 COMMENT ‘설명n’

) COMMENT ‘테이블 설명’ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\001’LINES TERMINATED BY ‘\n’LOCATION ‘/user/hive/warehouse/dbname.db/tablename’;

dbname: [dbname.] 생략시 default db 사용LOCATION: * 테이블 데이터가 보관될 디렉토리 경로 * 지정하지 않을 경우 데이터베이스 디렉토리의 하위 디렉토리에 생성구분자: \001 = ^A, \002 = ^B, \003 = ^C

● 로딩 쿼리

○ 디렉토리 경로를 사용해야 함■ 디렉토리에 포함된 모든 파일을 로딩

○ LOCAL: 로컬 파일을 테이블 데이터 경로에 로딩■ local이 없으면 HDFS 파일을 데이터 위치로 이동

○ OVERWRITE: 기존 파일들 삭제

관리 테이블에 데이터 로딩

LOAD DATA LOCAL INPATH ‘디렉토리경로' OVERWRITE INTO TABLE 테이블이름

외부 테이블 생성

● 생성 쿼리

● 디렉토리의 파일들을 테이블 데이터로 사용

CREATE EXTERNAL TABLE IF NOT EXISTS dbname.tablename (컬럼1이름 타입 COMMENT ‘설명’,컬럼2이름 타입 COMMENT ‘설명2’,…컬럼n이름 타입 COMMENT ‘설명n’

) COMMENT ‘테이블 설명’ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\001’LINES TERMINATED BY ‘\n’LOCATION ‘디렉토리경로 ’;

데이터 타입데이터 타입 설명

TINYINT 1바이트 크기 정수

SMALLINT 2바이트 크기 정수

INT 4바이트 크기 정수

BIGINT 8바이트 크기 정수

BOOLEAN TRUE 또는 FALSE

FLOAT 4바이트 부동소수점

DOUBLE 8바이트 부동소수점

STRING 문자열

TIMESTAMP 타임스탬프 (1970년 1월 1일 0시 이후 흘러간 시간)

기타: BINARY, STRUCT, MAP, ARRAY

쿼리: SELECT

● select * from access_log○ from access_log select *

● select server, ip, appname from access_log○ from access_log select server, ip, appname

● select * from access_log limit 1000○ from access_log select * limit 1000

● select * from access_log where server = ‘S1’○ from access_log select * where server = ‘S1’

● select appname, avg(res_time) as avg from access_log where year = ‘2013’group by appname having avg(res_time) > 3000

쿼리: 중첩 SELECT

● 형식1:○ select e.appname from

(select appname, avg(res_time) as ravg from access_log where year = ‘2013’ group by appname) ewhere e.ravg > 3000;

● 형식2:○ from (

select appname, avg(res_time) as ravg from access_log where year = '2013’ group by appname) eselect e.app_name where e.ravg > 3000;

쿼리: 조인

● 내부 조인○ select s.ymd, s.symbol, s.price_close, d.dividend

from stocks s JOIN dividends d ON s.ymd = d.ymd and s.symbol = d.symbolwhere s.symbol = ‘APPL’

○ on 절의 제약■ 동등 조인만 가능 (a.ymd = b.ymd)

● 비동등 조인은 사용 불가 (a.ymd >= b.ymd)■ 각 조건의 연결은 and 만 사용 가능

● n개 이상 조인 가능○ select … from stocks a

join stocks b on a.ymd = b.ymd join stocks c on a.ymd = c.ymd

쿼리: 조인

● 외부 조인○ LEFT, RIGHT, FULL 모두 지원○ 예

○ 주의: 조인 후 where 평가■ 외부 조인 결과가 null로 표시되어야 하는 컬럼에 대해 where 절에서 검사하면 내부 조인처럼 됨

select s.ymd, s.symbol, s.price_close, d.dividendfrom stocks s LEFT OUTER JOIN dividends d ON s.ymd = d.ymd and s.symbol = d.symbolwhere s.symbol = ‘APPL’

쿼리: Order by, Sort by, Distribute by, Cluster by

● order by: 전체 데이터 정렬 (1개 리듀스)● sort by: 리듀스 별로 정렬

○ 즉, 부분 데이터에 대한 정렬○ distribute by: 리듀스로 보낼 데이터 분류 기준

■ select s.ymd, s.symbol, s.price_closefrom stocks sdistribute by s.symbolsort by s.symbol, s.ymd

● cluster by○ 지정 컬럼의 동일 값을 가진 행을 동일 리듀서로 보냄

■ 지정 컬럼에 대한 정렬 (리듀서의 키로 사용되니까)

○ from stocks s select s.* cluster by s.symbol

쿼리: Union All

● 두 쿼리 결과를 합침○ 서브 쿼리에서만 사용 가능

select log.ymd, log.level, log.messagefrom ( select l1.ymd, l1.level, l1.message from log1 l1 union all select l2.ymd, l2.level, l2.message from log2 l2) logorder by log.ymc asc

쿼리: 쿼리 결과를 테이블/파일 넣기

● 테이블에 넣기

● 파일에 넣기○

○ local이 없으면 HDFS 사용○ row format이 없으면 테이블의 포맷 사용○ 지정 디렉토리에 ‘000000_0’ 등의 이름으로 파일 생성

from access_log linsert overwrite table server1_log select * where l.server = ‘SVR1’insert overwrite table server2_log select * where l.server = ‘SVR2’insert into table low_perf select * where l.res_time > 3000;

create table low_perfas select * from mis_access_plogwhere year='2011' and month='01' and request_time > 3000;

insert overwrite local directory '/Users/madvirus/data'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'select * from mis_access_plog where year = '2011' and month = '01';

파티션

● 파일 기반 테이블의 약점○ 전체 row를 모두 검색해야 결과를 알 수 있음

■ 예, ● select count(*) from log where ym = ‘201310’● select count(*) from log where ym = ‘201311’

○ 긴 응답 시간으로 연결● 이에 대한 해결책 중 하나 → 파티션

○ 테이블 데이터를 분리 저장■ 파티션 키: 분리 기준

● 성능 상의 이점○ 검색 조건에 파티션 키를 사용 → 전체 파일이 아닌 파티션된 영역의 파일만 처리하므로 속도 향상

파티션 된 관리 테이블

● 생성

● 데이터 추가

create table access_log ( server string, ip string, ...생략(파티션키와 같은 이름을 가진 컬럼은 사용 불가)) partitioned by (year string, month string)row format ...생략location …

LOAD DATA LOCAL INPATH ‘디렉토리경로' OVERWRITE INTO TABLE access_logPARTION (year = ‘2011’, month = ‘10’)

* 파티션 데이터는 .../access_log/year=2011/month=10 형식의 이름을 갖는 디렉토리에 저장

● 생성

● 데이터 추가

파티션 된 외부 테이블 생성

create external table if not exists mis_access_plog ( server STRING, ip STRING, ...생략) partitioned by (year STRING, month STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

alter table mis_access_plog add partition (year = '2011', month = '01')location '/mis/accesslog/2011/01';// location으로 지정한 디렉토리에 있는 모든 파일을 테이블 데이터로 사용

// 미리 파일을 HDFS에 업로드 필요hadoop fs -copyFromLocal 2011-01.tsv hdfs://bt0:9000/mis/accesslog/2011/01/2011-01.tsv

파티션 정보 보기

● 파티션 정보 확인hive> show partitions mis_access_plog;OKyear=2010/month=01year=2010/month=02year=2010/month=03year=2010/month=04…hive> show partitions mis_access_plog partition(year=’2011’)OKyear=2010/month=01year=2010/month=02…year=2011/month=12Time taken: 0.746 seconds, Fetched: 12 row(s)

파티션 테이블 vs 일반 테이블파티션 된 테이블 파티션 되지 않은 테이블

쿼리508,409 건

select count(*) from mis_access_plog where year = '2011' and month = ‘07’

select count(*) from mis_access_log where year = '2011' and month = ‘07’

실행 시간 25초 6분 3초

쿼리149,386,135 건

select count(*) from mis_access_plog where year = '2013'

select count(*) from mis_access_log where year = '2013'

실행 시간 2분 12초 5분 31초

쿼리333,832,015 건

select count(*)from mis_access_plog

select count(*) from mis_access_log

실행 시간 4분 53초 5분 41초

* 데이터 노드: 3대* 파티션: year/month 로 파티션 됨

주요 집계 함수함수 리턴타입 설명

count(*), count(expr),count(distinct expr)

bigint 총 수를 구한다.

sum(col), sum(distinct col) double 합을 구한다.

avg(col), avg(distinct col) double 평균을 구한다.

min(col), max(col) double 최소/최대를 구한다.

variance(col) double 분산을 구한다.

stddev_pop(col) double 표준 편차를 구한다.

covar_pop(col1, col2) double 공분산을 구한다.

corr(col1, col2) double 두 집합의 상관관계를 구한다.

collect(set(col) array 중복된 요소를 제거한 집합을 구한다.

percentile(정수타입컬럼 , p) double 컬럼에서 백분위 p에 해당하는 값을 구한다.

percentile_approx(실수타입컬럼 , p) double 컬럼에서 백분위 p에 해당하는 값을 구한다.

기타 함수함수 리턴타입 설명

length(s) int 문자열의 길이를 구한다.

concat(s1, s2, …), concat(sep, s2, s2) string 문자열을 연결한다. sep는 구분자.

substring(s, start), substring(s, start, len) string 문자열의 일부를 구한다.

upper(s), ucase(s), lower(s), lcase(s) string 대문자/소문자로 변환한다.

trim(s), ltrim(s), rtrim(s) string 끝의 공백을 제거한다.

regexp_replace(s, regex, replacement) string 정규표현식과 일치하는 부분을 대치한다.

regexp_extract(s, regex, idx) string 정규표현식과 일치하는 idx 번째 부분을 구한다.

parse_url(url, partname, key) string URL에서 일부를 구한다.

from_unixtime(int unixtime) string 유닉스 기준시로부터 타임스탬프(yyyy-MM-dd HH:mm:ss)를 구한다.

to_date(ts타임스탬프) string 타임스탬프에서 ‘yyyy-MM-dd’ 부분을 구한다.

year(ts), month(ts), day(ts) int 타임스탬프에서 연, 월, 일 부분을 구한다.

기타

● 클라이언트 연결○ hive 클라이언트○ 자바/그루비 등 JVM 언어

● 우지 연동● 쿼리 튜닝 (*)● 파일 포맷/레코드 포맷/압축● UDF(User Defined Function)● 스토리지 핸들러