[Hive] 내장 함수(Built-in Functions), 수학 함수(Arithmetic Functions), 집계 함수(Aggregation Functions)

이번 포스팅에서는 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에 대해서 알아보겠습니다.

 

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