이번 포스팅에서는 Hive에 내장되어 있는

 

 - (1) 내장함수(Hive built-in functions)

 

 - (2) 수학함수(Hive arithmetic functions)

 

 - (3) 집계함수(Hive aggregate functions)

 

에 대해서 알아보겠습니다.

 

기본 SELECT ... FROM ... 에 더해서 지난번 포스팅에서 다루었던 연산자와 함께 이번 포스팅에서 다룰 다양한 함수를 잘 알고 있으면 "Hive 좀 하네" 소리 들을 수 있을 겁니다. 

 

다행스러운 것은 SQL에서 사용하는 함수와 매우 비슷하다는 점입니다. ( <= 이점이 Hive 진입 장벽을 낮추어서 Hive가 많이 사용되는 이유이지요)

 

자주 쓰는 함수 외에는 다 외우기 힘들 수 있으므로, 필요할 때마다 검색해서 사용하시기 바랍니다.

 

 

 

 

먼저 다양한 내장 함수 먼저 살펴보겠습니다.

 

 

(1) 내장 함수 (Hive Built-in functions)

 

반환 데이터형

내장 함수 

설명 

 BIGINT

round(double a)

double 데이터형 a의 반올림한 값을

BIGINT 데이터형으로 반환

예) round(4.8) = 5

 DOUBLE

round(double a, N) 

double 데이터형 a의 N개 소수점 이하의 값까지를

반올림하여 double 데이터형으로 반환

예) round(4.816, 2) = 4.82

 DOUBLE

floor(double a)

double 데이터형 a보다 같거나 작은 값 중

가장 큰 BIGINT 값을 반환

예) floor(4.8) = 4

 BIGINT

ceil(double a)

double 데이터형 a보다 크거나 같은 값 중

가장 작은 BIGINT 값을 반환

예) ceil(4.8) = 5

 DOUBLE

rand(),

rand(int seed)

행에서 행으로 변하는 무작위 숫자를 반환

 string

concat(string A,

string B, ...)

string A와 string B를 연속으로 붙여서 반환

 string

concat_ws(seperator, string A, string B, ...) 

string A와 string B를 구분자(seperator)를 두어서

연속으로 붙여서 반환

 string

substr(string A,

int start)

string A를 시작 색인 지점부터 끝까지 잘라서 반환.

* 색인은 1부터 시작함.

예) substr('20160625', 5) = '0625'

 string

substr(string A,

int start, int length)

string A를 시작 색인 지점부터 int length만큼 잘라서 반환.

* 색인은 1부터 시작함.

예) substr('20160625', 5, 2) = '06'

 ARRAY

<string>

split(string A, pattern)

pattern이 나타날 때마다 string A를 분할하여

문자열의 배열을 반환.

 string

repeat(string A, n) 

 string A를 n회 반복하여 반환.

 string

upper(string A)

or ucase(string A)

string A의 모든 문자를 대문자로 반환.

예) upper('Love') = 'LOVE'

 string

lower(string A)

or lcase(string A)

string A의 모든 문자를 소문자로 반환.

예) lower('Love') = 'love'

 string

trim(string A)

string A의 양끝의 공백을 제거하여 반환.

예) trim('  Love  ') = 'Love'

 string

ltrim(string A)

string A의 왼쪽 시작부분의 공백을 제거하여 반환.

예) ltrim('  Love  ') = 'Love  '

 string

rtrim(string A)

string A의 오른쪽 끝부분의 공백을 제거하여 반환.

예) rtrim('  Love  ') = Love'

 string space(n) 

n개의 공백을 반환.

 string

regexp_replace(string A, string B, string C)

string A에서 string B의 부분 문자열(substring)과 일치하는

Java 정규표현식을 string C로 교체하여 반환.

예) regexp_replace('Love', 'oe', 'i') => returns 'Livi'

 int length(string A) 

string A의 길이를 반환.

예) length('Love') = 4

 int

size(Map<K.V>)

Map<K.V> 의 요소 개수를 반환.

 int

size(Array<T>)

Array<T> 의 요소 개수를 반환.

value of <type>

cast(<expr> as <type>)

표현 <expr>을 <type>으로 형변환하며,

변환에 실패하면 NULL을 반환함.

예) cast('1' as BIGINT) => 문자열 '1'을 BIGINT로 형변환

 string

from_unixtime(int unixtime)

Unix 기준시간(1970-01-01 00:00:00 UTC)을

=> 현재 시스템 시간대의 초단위 시간 표현식 포맷

     ("1970-01-01 00:00:00")으로 변환.

 string

to_date(string timestamp)

timestamp 문자열에서 년/월/날짜를 반환.

예) to_date("1970-01-01 00:00:00") = "1970-01-01"

 int

year(string date)

timestamp 문자열에서 년(year)을 반환.

예) year("1970-01-01 00:00:00") = 1970,

     year("1970-01-01") = 1970

 int

month(string date)

timestamp 문자열에서 월(month)을 반환.

예) month("1970-11-01 00:00:00") = 11,

     month("1970-11-01") = 11

 int

day(string date)

timestamp 문자열에서 일(day)을 반환.

예) day("1970-11-01 00:00:00") = 1,

     day("1970-11-01") = 1

 string

get_json_object(string json_string, string path)

json string으로부터 json 객체를 추출하며,

추출한 json 객체로부터 json string을 반환함.

json string이 유효하지 않으면 NULL을 반환함.

 MAP<string, string>

str_to_map(string A, delim1, delim2)

delim1을 Key-Value 쌍의 구분자로 사용하고 

delim2를 Key와 Value의 구분자로 사용하여

string A를 parsing 한 후에 Map을 생성함.

 ARRAY<ARRAY

<string>>

sentences(string A, lang, locale)

string A를 단어의 배열로 이루어진 문장의 배열로 반환.

lang, locale 옵션을 설정하지 않을 경우 기본 locale 사용.

 BOOLEAN

in_file(string A,

file B) 

filen B에 string A가 들어 있으면 TRUE를 반환.

 

 

 

(2) 수학 함수 (Hive Arithmetic functions)

 

아래의 수학 함수들의 full name을 알고 있는 분들이라면 축약어 형식의 함수이름을 보고 '아, 이거 뭐겠구나...'하고 짐작할 수 있을 것입니다.

 

 반환 데이터형

수학함수

설명 

 DOUBLE

exp(double A) 

 double A의 지수(exponential) 함수값을 반환

 DOUBLE

ln(double A) 

 double A의 자연로그(natural logarithm)값을 반환

* 자연로그 : e로 밑으로 하는 로그 (e=2.71828182846)

 DOUBLE

log10(double A) 

 double A의 상용로그(common logarithm) 값을 반환

* 상용로그 : 10을 밑으로 하는 로그

 DOUBLE

log2(double A) 

 double A의 밑을 2로 하는 로그값을 반환

 DOUBLE

log(base, double A) 

 double A의 밑을 base로 하는 로그값을 반환

(base는 double 데이터형)

 DOUBLE

pow(double A, p)

or

power(double A, p)

 double A의 p 거듭제곱(power)한 값을 반환

(p는 double 데이터형)

 DOUBLE

sqrt(double A)

 double A의 제곱근(square root)을 반환

 string

bin(BIGINT i)

 BIGINT i 의 이진수(binary)값을 문자열로 반환

 string

hex(BIGINT i) 

 BIGINT i 의 16진수(hexadecimal)값을 문자열로 반환

 string

hex(string S) 

 string S의 각 문자를 16진수값으로 변환하여 문자열로 반환

 string unhex(i) 

 hex(string S) 함수와 반대로 동작

 string

conv(BIGINT i, from_base, to_base)

 BIGINT i 를 from_base 진수에서 to_base 진수로 변환하여

문자열로 반환

(from_base와 to_base는 정수형)

 string

conv(string S, from_base, to_base) 

 string S 를 from_base 진수에서 to_base 진수로 변환하여

문자열로 반환

(from_base와 to_base는 정수형)

 DOUBLE

abs(double A

 double A의 절대값(absolute value)을

DOUBLE 데이터형으로 반환

 INT

pmod(INT 1, INT 2) 

 INT 1을 INT 2로 모듈러 연산(modular arithmetic)한 후

양수(positive) INT 값을 반환 (음이 아닌 나머지를 반환)

 DOUBLE pmod(double 1, double 2

 double 1을 double 2로 모듈러 연산한 후

양수 DOUBLE 값을 반환

 DOUBLE

sin(double A) 

 double A의 sine 값을

DOUBLE 데이터형의 radian 값으로 반환

 

* 1 radian : 원둘레 위에서 반지름의 길이(r)와 같은 길이를 가지는 호(s)에 대응하는 중심각의 크기, 약 57.2958˚)

 DOUBLE

asin(double A) 

 double A의 arc-sine 값을

DOUBLE 데이터형의 radian 값으로 반환

 

* arc-sine : sine 함수의 역함수(inverse trigonometric function)

 DOUBLE cos(double A) 

 double A의 cosine 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE acos(double A) 

 double A의 arc-cosine 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE

tan(double A) 

 double A의 tangent 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE atan(double A) 

 double A의 arc-tangent 값을

DOUBLE 데이터형의 radian 값으로 반환

 DOUBLE

degrees(double A) 

 double A radian 값을 각도로 변환하여 반환

 DOUBLE radians(double A) 

 double A 각도 값을 radian 값으로 변환하여 반환

 INT positive(INT i) 

 INT i의 양의 INT 데이터형 값을 반환

 INT

negative(INT i) 

 INT i의 음의 INT 데이터형 값을 반환

 DOUBLE positive(double A) 

 double A의 양의 DOUBLE 데이터형 값을 반환

 DOUBLE negative(double A)   double A의 음의 DOUBLE 데이터형 값을 반환

 FLOAT

sign(double A) 

 double A가 양수이면 부동소수점형 1.0을 반환,

                음수이면 부동소수점형 -1.0을 반환,

                모두 아니면 부동소수점형 0.0을 반환

 DOUBLE e() 

 지수형 상수값 2.718281828459045를

DOUBLE 데이터형으로 반환

 DOUBLE pi 

 파이 상수값 3.141592653589793을

DOUBLE 데이터형으로 반환

 

 

 

 

(3) 집계 함수 (Hive Aggregate functions)

 

특정 열을 기준으로 그룹 별로 행의 값들을 요약, 집계해서 하나의 값으로 반환해주는 함수가 집계 함수입니다.  GRUOP BY 와 함께 쓰여서

 

hive > SET hive.map.aggr = true; -- 집계 처리 성능 향상

 

SELECT count(DISTINCT var_1) as var_1_dist_cnt, avg(var_2) as var_2_avg 

FROM my_table 

GROUP BY var_group

 

형식으로 아주 아주 아주 많이 사용하는 함수들입니다.  R 학습할 때 초반부에 기초 요약통계량에서 다루는 함수들에 해당합니다.

 

 반환 데이터형

집계 함수

설명 

 BIGINT

count(*)

NULL값을 포함한 행의 총 개수를 반환

 BIGINT

count(expr)

주어진 표현식이 NULL이 아닌 행의 개수를 반환

 BIGINT

count(DISTINCT expr[, expr_.]) 

주어진 표현식의 값이 유일하면서 NULL이 아닌

행의 개수를 반환

 DOUBLE

sum(col)

그룹 내 요소들의 합을 반환

 

 DOUBLE

sum(DISTINCT col)

그룹 내 열의 고유한 값들의 합을 반환

 DOUBLE

avg(col)

그룹 내 요소들의 평균을 반환

 DOUBLE avg(DISTINCT col)

그룹 내 열의 고유한 값들의 평균을 반환

 DOUBLE

min(col)

그룹 내 열의 최소값을 반환

 DOUBLE

max(col)

그룹 내 열의 최대값을 반환

 DOUBLE

variance(col)

or var_pop(col)

특정 열의 모집단 분산을 반환

 DOUBLE

var_samp(col)

특정 열의 표본 분산을 반환

 DOUBLE

stddev_pop(col)

특정 열의 모집단 표준편차를 반환

 DOUBLE

stddev_samp(col)

특정 열의 표본 표준편차를 반환

 DOUBLE

covar_pop(col1, col2)

두개 열(col1, col2)의 공분산을 반환

 DOUBLE

covar_samp(col1, col2)

두개 열(col1, col2)의 표본 공분산을 반환

 DOUBLE

corr(col1, col2)

두개 열(col1, col2)의 상관계수를 반환

 DOUBLE

percentile(int_expr, p)

p percent에 해당하는 int_expr 백분위수 값을 반환.

p는 0과 1사이의 DOUBLE 데이터형 값임.  

예) p가 0.25이면 전체 값을 크기 순으로 오름차순 정렬하였을 때 25%에 해당하는 값을 반환

 

참고) 백분위수(percentile)는 총값의 백분율이 해당 값보다

        작거나 같은 측정치임

 ARRAY(DOUBLE)

percentile(int_expr, [p1, ...])

p1, p2, ... percent에 해당하는 int_expr 백분위수 값을

ARRAY 데이터형으로 반환함

 DOUBLE

percentile_approx(int_expr, p, NB)

p percent에 해당하는 int_expr 의 근사 백분위수 값을 반환.

NB는 추정을 위한 Histogram bins 의 수임

(NB default 값 : 10,000)

 DOUBLE

percentile_approx

(int_expr, [p1, ...], NB) 

p1, p2, ... percent에 해당하는 int_expr 의

근사 백분위수 값을 반환

 

 

 

(4) 테이블 생성 함수 (Hive table creation functions)

 

 반환 데이터형

테이블 생성 함수 

설명 

 N개의 row

explode(ARRAY)

 ARRAY 의 각 요소별로 한 개의 행(row)을 만들며,

0개 이상의 row를 반환

 N개의 row

explode(MAP)

 MAP의 Key와 Value에 대해 각각 하나의 열(field)을 생성.

각 Key-Value 쌍에 대해 하나의 행(row)이 생성되며,

0개 이상의 row를 반환

 TUPLE

json_tuple(jsonStr,
p1, p2, ..., pn)

 get_json_object 와 같은 기능,

반면 여러 이름을 받아들이고 하나의 tuple을 반환.

(모든 입력 인자와 출력 행의 데이터형은 문자열)

 TUPLE

N>=1 일 때 parse_url_tuple(url, part name1,

part name2, ..,

part nameN)

 한 URL로부터 N개의 part를 추출하고,

1, 2, ..., N개의 part 이름을 부여함.

 

(모든 입력 인자와 출력 행의 데이터형은 문자열.

유효한 part 이름은 대소문자를 구분, 공백은 하나만 포함)

 N개의 row

stack(n, col1, ..., colM)

 M개의 행을 N개의 열로 반환.

각 행은 M/N의 크기를 가짐.

 

[예제]

SELECT parse_rul_tuple(url, 'HOST', 'PATH', 'QUERY') as (host, path, query)

FROM url_table;

 

 

[Reference]

1) Programing Hive, Edward Capriolo, Dean Wampler, Jason Rutherglen, O'REILLY

2) http://www.tutorialspoint.com/hive/hive_built_in_functions.htm

 

 

다음 포스팅에서는 중첩 SELECT 문, LIKE, CASE... WHEN ... THEN 문, GROUP BY, HAVING절, ORDER BY, SORT BY, DISTRIBUTE BY에 대해서 알아보겠습니다.

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요. ^^

 

 

 

Posted by R Friend R_Friend