Hivemall Function Reference

SELECT HIVEMALL_VERSION()

approx_count_distinct(column)
approx_distinct(column)

base91(binary)

SELECT base91(deflate('aaaaaaaaaaaaaaaabbbbccc'));
> AA+=kaIM|WTt!+wbGAA

unbase91(string)

SELECT inflate(unbase91(base91(deflate('aaaaaaaaaaaaaaaabbbbccc'))));
> aaaaaaaaaaaaaaaabbbbccc

is_stopword(string word)

normalize_unicode(string str [, string form])

select normalize_unicode('ﾊﾝｶｸｶﾅ','NFKC');
ハンカクカナ
select normalize_unicode('㈱㌧㌦Ⅲ','NFKC');
(株)トンドルIII

singularize(string word)

split_words(string query [, string regex])

word_ngrams(array<string> words, int minSize, int maxSize)

tokenize(string englishText [, boolean toLowerCase])

tokenize_ja(String line [, const string mode = "normal", const list<string> stopWords, const list<string> stopTags, const array<string> userDict (or string userDictURL)])

select tokenize_ja("kuromojiを使った分かち書きのテストです。第二引数にはnormal/search/extendedを指定できます。デフォルトではnormalモードです。");
["kuromoji","使う","分かち書き","テスト","第","二","引数","normal","search","extended","指定","デフォルト","normal"," モード"]

select tokenize_ja("関西国際空港", "normal", null, null,
                   array("関西国際空港,関西 国際 空港,カンサイ コクサイ クウコウ,カスタム名詞"));
["関西","国際","空港"]

-- stoptags_exclude is useful for an include rule of part-of-speech information  
select tokenize_ja("kuromojiを使った分かち書きのテストです。", "normal", array("kuromoji"), stoptags_exclude(array("名詞")));
["分かち書き","テスト"]

-- using pre-defined library
select tokenize_ja("関西国際空港", "normal", null, null, "https://raw.githubusercontent.com/atilika/kuromoji/909fd6b32bf4e9dc86b7599de5c9b50ca8f004a1/kuromoji-core/src/test/resources/userdict.txt"); 
["関西","国際","空港"]

WITH tmp as (
  select
    tokenize_ja('kuromojiを使った分かち書きのテストです。','-mode search -pos') as r
)
select
  r.tokens,
  r.pos,
  r.tokens[0] as token0,
  r.pos[0] as pos0
from
  tmp;

select stoptags_exclude(array());

tokenize_ja_neologd(String line [, const string mode = "normal", const list<string> stopWords, const list<string> stopTags, const array<string> userDict (or string userDictURL)])

select tokenize_ja_neologd(); -- returns current UDF version with corresponding NEologd version date
["0.1.0-20180524"]

select tokenize_ja_neologd("彼女はペンパイナッポーアッポーペンと恋ダンスを踊った。");
["彼女","ペンパイナッポーアッポーペン","恋ダンス","踊る"]

tokenize_cn(string line, optional const array<string> stopWords)

select tokenize_cn("Smartcn为Apache2.0协议的开源中文分词系统，Java语言编写，修改的中科院计算所ICTCLAS分词系统。");
[smartcn, 为, apach, 2, 0, 协议, 的, 开源, 中文, 分词, 系统, java, 语言, 编写, 修改, 的, 中科院, 计算, 所, ictcla, 分词, 系统]

tokenize_ko(
       String line [, const string mode = "discard" (or const string opts),
       const array<string> stopWords,
       const array<string>
       stopTags,
       const array<string> userDict (or const string userDictURL)]
) - returns tokenized strings in array<string>

select tokenize_ko("", "-help");

usage: tokenize_ko(String line [, const string mode = "discard" (or const
       string opts), const array<string> stopWords, const array<string>
       stopTags, const array<string> userDict (or const string
       userDictURL)]) - returns tokenized strings in array<string> [-help]
       [-mode <arg>] [-outputUnknownUnigrams]
-helpShow function help
-mode <arg>              The tokenization mode. One of ['node', 'discard'
      (default), 'mixed']
-outputUnknownUnigrams   outputs unigrams for unknown words.

-- show version of lucene-analyzers-nori
select tokenize_ko();
> 8.8.2

select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!');
> ["중요","기능","개발","주","고맙"]

-- explicitly using default options
select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!', '-mode discard', 
  -- stopwords (null to use default)
  -- see https://github.com/apache/incubator-hivemall/blob/master/nlp/src/main/resources/hivemall/nlp/tokenizer/ext/stopwords-ko.txt 
  null, 
  -- stoptags
  -- see https://lucene.apache.org/core/8_8_2/analyzers-nori/org/apache/lucene/analysis/ko/POS.Tag.html
  array(
   'E',   -- Verbal endings
   'IC',  -- Interjection
   'J',   -- Ending Particle
   'MAG', -- General Adverb
   'MAJ', -- Conjunctive adverb
   'MM',  -- Determiner
   'SP',  -- Space 
   'SSC', -- Closing brackets
   'SSO', -- Opening brackets
   'SC',  -- Separator
   'SE',  -- Ellipsis
   'XPN', -- Prefix
   'XSA', -- Adjective Suffix
   'XSN', -- Noun Suffix
   'XSV', -- Verb Suffix
   'UNA', -- Unknown
   'NA',  -- Unknown
   'VSV'  -- Unknown
  )
);
> ["중요","기능","개발","주","고맙"]

-- None mode, without General Adverb (MAG)
select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!', 
  -- No decomposition for compound.
  '-mode none', 
  -- stopwords (null to use default)
  null, 
  array(
   'E',   -- Verbal endings
   'IC',  -- Interjection
   'J',   -- Ending Particle
   -- 'MAG', -- General Adverb
   'MAJ', -- Conjunctive adverb
   'MM',  -- Determiner
   'SP',  -- Space 
   'SSC', -- Closing brackets
   'SSO', -- Opening brackets
   'SC',  -- Separator
   'SE',  -- Ellipsis
   'XPN', -- Prefix
   'XSA', -- Adjective Suffix
   'XSN', -- Noun Suffix
   'XSV', -- Verb Suffix
   'UNA', -- Unknown
   'NA',  -- Unknown
   'VSV'  -- Unknown
  )
);
> ["중요","기능","개발","줘서","정말","고마워요"]

-- discard mode: Decompose compounds and discards the original form (default).
-- https://lucene.apache.org/core/8_8_2/analyzers-nori/org/apache/lucene/analysis/ko/KoreanTokenizer.DecompoundMode.html
select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!', '-mode discard');
> ["중요","기능","개발","주","고맙"]

-- default stopward (null), with stoptags
select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!', '-mode discard', null, array('E', 'VV'));
> ["중요","하","새","기능","을","개발","하","주","정말","고맙"]

-- mixed mode: Decompose compounds and keeps the original form.
select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!', 'mixed');
> ["중요","기능","개발","줘서","주","고마워요","고맙"]

select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!', '-mode mixed');
> ["중요","기능","개발","줘서","주","고마워요","고맙"]

-- node mode: No decomposition for compound.
select tokenize_ko('중요한 새 기능을 개발해줘서 정말 고마워요!', '-mode none');
> ["중요","기능","개발","줘서","고마워요"]

select tokenize_ko('Hello, world.', '-mode none');
> ["hello","world"]

select tokenize_ko('Hello, world.', '-mode none -outputUnknownUnigrams');
> ["h","e","l","l","o","w","o","r","l","d"]

select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard');
> ["나","c","언어","프로그래밍","언어","사랑"]

select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard', array(), null);
> ["나","는","c","언어","를","프로그래밍","언어","로","사랑","하","ᆫ다"]

-- default stopward (null), default stoptags (null)
select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard');
select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard', null, null);
> ["나","c","언어","프로그래밍","언어","사랑"]

-- no stopward (empty array), default stoptags (null)
select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard', array());
select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard', array(), null);
> ["나","c","언어","프로그래밍","언어","사랑"]

-- no stopward (empty array), no stoptags (emptry array), custom dict
select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard', array(), array(), array('C++'));
> ["나","는","c++","언어","를","프로그래밍","언어","로","사랑","하","ᆫ다"]

> -- default stopward (null), default stoptags (null), custom dict
select tokenize_ko('나는 C++ 언어를 프로그래밍 언어로 사랑한다.', '-mode discard', null, null, array('C++'));
> ["나","c++","언어","프로그래밍","언어","사랑"]

`select tokenize_ko('나는 c++ 프로그래밍을 즐긴다.', '-mode discard', null, null, 'https://raw.githubusercontent.com/apache/lucene/main/lucene/analysis/nori/src/test/org/apache/lucene/analysis/ko/userdict.txt');

> ["나","c++","프로그래밍","즐기"]

tile(double lat, double lon, int zoom)

haversine_distance(double lat1, double lon1, double lat2, double lon2, [const boolean mile=false])

-- Tokyo (lat: 35.6833, lon: 139.7667), Osaka (lat: 34.6603, lon: 135.5232)
select
  haversine_distance(35.6833, 139.7667, 34.6603, 135.5232) as km,
  haversine_distance(35.6833, 139.7667, 34.6603, 135.5232, true) as mile;
402.09212137829684    249.8484608500711

majority_vote(Primitive x) 

WITH data as (
select
explode(array('1', '2', '2', '2', '5', '4', '1', '2')) as k
)
select
majority_vote(k) as k
from 
data;
> 2

max_by(x, y)

WITH data as (
select 'jake' as name, 18 as age
union all
select 'tom' as name, 64 as age
union all
select 'lisa' as name, 32 as age
)
select
max_by(name, age) as name
from
data;
> tom

min_by(x, y)

WITH data as (
select 'jake' as name, 18 as age
union all
select 'tom' as name, 64 as age
union all
select 'lisa' as name, 32 as age
)
select
min_by(name, age) as name
from
data;
> jake 

arange([int start=0, ] int stop, [int step=1])

select arange(5), arange(1, 5), arange(1, 5, 1), arange(0, 5, 1);
> [0,1,2,3,4] [1,2,3,4] [1,2,3,4] [0,1,2,3,4]

select arange(1, 6, 2);
> 1, 3, 5

select arange(-1, -6, 2);
> -1, -3, -5

argmax(array<T> a)

select argmax(array(5,2,0,1));
> 0

argmin(array<T> a)

SELECT argmin(array(5,2,0,1));
> 2

argrank(array<ANY> a)

select argrank(array(5,2,0,1)), argsort(argsort(array(5,2,0,1)));
> [3, 2, 0, 1] [3, 2, 0, 1]

argsort(array<ANY> a)

select argsort(array(5,2,0,1));
> 2, 3, 1, 0

array_append(array<T> arr, T elem)

SELECT array_append(array(1,2),3);
> 1,2,3

SELECT array_append(array('a','b'),'c');
> "a","b","c"

array_avg(array<number>)

WITH input as (
select array(1.0, 2.0, 3.0) as nums
UNION ALL
select array(2.0, 3.0, 4.0) as nums
)
select 
array_avg(nums)
from
input

> ["1.5","2.5","3.5"]

array array_concat(array<ANY> x1, array<ANY> x2, ..)

select array_concat(array(1),array(2,3))
> [1,2,3]

array_flatten(array<array<ANY>>)

SELECT array_flatten(array(array(1,2,3),array(4,5),array(6,7,8)));
> [1,2,3,4,5,6,7,8]

array_intersect(array<ANY> x1, array<ANY> x2, ..)

select array_intersect(array(1,3,4),array(2,3,4),array(3,5))
> [3]

array_remove(array<int|text> original, int|text|array<int> target)

select array_remove(array(1,null,3),array(1));
> [null,3]

select array_remove(array("aaa","bbb"),"bbb");
> ["aaa"]

array_slice(array<ANY> values, int offset [, int length])

SELECT 
array_slice(array(1,2,3,4,5,6),2,4),
array_slice(
array("zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"),
0, -- offset
2 -- length
),
array_slice(
array("zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"),
6, -- offset
3 -- length
),
array_slice(
array("zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"),
6, -- offset
10 -- length
),
array_slice(
array("zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"),
6 -- offset
),
array_slice(
array("zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"),
-3 -- offset
),
array_slice(
array("zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"),
-3, -- offset
2 -- length
);

> [3,4]
> ["zero","one"] 
> ["six","seven","eight"]
> ["six","seven","eight","nine","ten"]
> ["six","seven","eight","nine","ten"]
> ["eight","nine","ten"]
> ["eight","nine"]

array_sum(array<NUMBER>)

WITH input as (
select array(1.0, 2.0, 3.0) as nums
UNION ALL
select array(2.0, 3.0, 4.0) as nums
)
select 
array_sum(nums)
from
input

> ["3.0","5.0","7.0"]

array_to_str(array arr [, string sep=','])

SELECT array_to_str(array(1,2,3),'-');
> 1-2-3

array_union(array1, array2, ...)

SELECT array_union(array(1,2),array(1,2));
> [1,2]

SELECT array_union(array(1,2),array(2,3),array(2,5));
> [1,2,3,5]

conditional_emit(array<boolean> conditions, array<primitive> features)

WITH INPUT AS(
SELECT 
ARRAY(TRUE,
FALSE,
TRUE) AS conditions,
ARRAY("one",
"two",
"three") AS features
UNION
ALL SELECT 
ARRAY(TRUE,
TRUE,
FALSE) AS conditions,
ARRAY("four",
"five",
"six") AS features
) SELECT 
conditional_emit(
conditions,
features
)
FROM
INPUT
;

element_at(array<T> list, int pos)

SELECT element_at(array(1,2,3,4),0);
> 1

SELECT element_at(array(1,2,3,4),-2);
> 3

SELECT first_element(array('a','b','c'));
> a

SELECT first_element(array());
> NULL

float_array(nDims)

SELECT last_element(array('a','b','c'));
> c

select_k_best(array<number> array, const array<number> importance, const int k)

sort_and_uniq_array(array<int>)

SELECT sort_and_uniq_array(array(3,1,1,-2,10));
> [-2,1,3,10]

subarray(array<int> orignal, int fromIndex, int toIndex)

SELECT subarray(array(1,2,3,4,5,6), 2,4)
> [3,4]

subarray_endwith(array<int|text> original, int|text key)

SELECT subarray_endwith(array(1,2,3,4), 3);
> [1,2,3]

subarray_startwith(array<int|text> original, int|text key)

SELECT subarray_startwith(array(1,2,3,4), 2);
> [2,3,4]

to_string_array(array<ANY>)

SELECT to_string_array(array(1.0,2.0,3.0));
> ["1.0","2.0","3.0"]

to_ordered_list(PRIMITIVE value [, PRIMITIVE key, const string options])
to_ordered_list(value, key [, const string options])

WITH t as (
     SELECT 5 as key, 'apple' as value
     UNION ALL
    SELECT 3 as key, 'banana' as value
    UNION ALL
    SELECT 4 as key, 'candy' as value
    UNION ALL
    SELECT 2 as key, 'donut' as value
    UNION ALL
    SELECT 3 as key, 'egg' as value
)
SELECT     -- expected output
    to_ordered_list(value, key, '-reverse'),       -- [apple, candy, (banana, egg | egg, banana), donut] (reverse order)
    to_ordered_list(value, key, '-k 2'),           -- [apple, candy] (top-k)
    to_ordered_list(value, key, '-k 100'),         -- [apple, candy, (banana, egg | egg, banana), dunut]
    to_ordered_list(value, key, '-k 2 -reverse'),  -- [donut, (banana | egg)] (reverse top-k = tail-k)
    to_ordered_list(value, key),                   -- [donut, (banana, egg | egg, banana), candy, apple] (natural order)
    to_ordered_list(value, key, '-k -2'),          -- [donut, (banana | egg)] (tail-k)
    to_ordered_list(value, key, '-k -100'),        -- [donut, (banana, egg | egg, banana), candy, apple]
    to_ordered_list(value, key, '-k -2 -reverse'), -- [apple, candy] (reverse tail-k = top-k)
    to_ordered_list(value, '-k 2'),                -- [egg, donut] (alphabetically)
    to_ordered_list(key, '-k -2 -reverse'),        -- [5, 4] (top-2 keys)
    to_ordered_list(key),      -- [2, 3, 3, 4, 5] (natural ordered keys)
    to_ordered_list(value, key, '-k 2 -kv_map'),   -- {4:"candy",5:"apple"}
    to_ordered_list(value, key, '-k 2 -vk_map')    -- {"candy":4,"apple":5}
FROM
    t;

bits_collect(int|long x)

bits_or(array<long> b1, array<long> b2, ..)

SELECT unbits(bits_or(to_bits(array(1,4)),to_bits(array(2,3))));
> [1,2,3,4]

to_bits(int[] indexes)

SELECT to_bits(array(1,2,3,128));
> [14,-9223372036854775808]

unbits(long[] bitset)

SELECT unbits(to_bits(array(1,4,2,3)));
> [1,2,3,4]

deflate(TEXT data [, const int compressionLevel])

SELECT base91(deflate('aaaaaaaaaaaaaaaabbbbccc'));
> AA+=kaIM|WTt!+wbGAA

inflate(BINARY compressedData)

SELECT inflate(unbase91(base91(deflate('aaaaaaaaaaaaaaaabbbbccc'))));
> aaaaaaaaaaaaaaaabbbbccc

sessionize(long timeInSec, long thresholdInSec [, String subject])

SELECT 
sessionize(time, 3600, ip_addr) as session_id, 
time, ip_addr
FROM (
SELECT time, ipaddr 
FROM weblog 
DISTRIBUTE BY ip_addr, time SORT BY ip_addr, time DESC
) t1;

to_json(object)

select to_json(ARRAY('a', 'b', 'c'));
'["a","b","c"]'

from_json(STRING json, const string type)

select from_json('["a","b","c"]', 'array<string>');
["a","b","c"]

map_exclude_keys(Map<K,V> map, array<K> filteringKeys)

SELECT map_exclude_keys(map(1,'one',2,'two',3,'three'),array(2,3));
> {1:"one"}

map_get(MAP<K> a, K n)

WITH tmp as (
SELECT "one" as key
UNION ALL
SELECT "two" as key
)
SELECT map_get(map("one",1,"two",2),key)
FROM tmp;
> 1
> 2

map_get_sum(map<int,float> src, array<int> keys)

map_include_keys(Map<K,V> map, array<K> filteringKeys)

SELECT map_include_keys(map(1,'one',2,'two',3,'three'),array(2,3));
> {2:"two",3:"three"} 

array<named_struct<key,value>> map_key_values(Map<K, V> map)

SELECT map_key_values(map("one",1,"two",2));
> [{"key":"one","value":1},{"key":"two","value":2}]

map_roulette(map<key, number> [, integer seed])

-- returns key by weighted random selection
SELECT 
map_roulette(to_map(a, b)) -- 25% Tom, 21% Zhang, 54% Wang
FROM ( -- see https://issues.apache.org/jira/browse/HIVE-17406
select 'Wang' as a, 54 as b
union all
select 'Zhang' as a, 21 as b
union all
select 'Tom' as a, 25 as b
) tmp;
> Wang

-- Weight random selection with using filling nulls with the average value
SELECT
map_roulette(map(1, 0.5, 'Wang', null)), -- 50% Wang, 50% 1
map_roulette(map(1, 0.5, 'Wang', null, 'Zhang', null)) -- 1/3 Wang, 1/3 1, 1/3 Zhang

-- NULL will be returned if every key is null
SELECT 
map_roulette(map()),
map_roulette(map(null, null, null, null));
> NULL NULL

-- Return NULL if all weights are zero
SELECT
map_roulette(map(1, 0)),
map_roulette(map(1, 0, '5', 0))
> NULL NULL

-- map_roulette does not support non-numeric weights or negative weights.
SELECT map_roulette(map('Wong', 'A string', 'Zhao', 2));
> HiveException: Error evaluating map_roulette(map('Wong':'A string','Zhao':2))
SELECT map_roulette(map('Wong', 'A string', 'Zhao', 2));
> UDFArgumentException: Map value must be greather than or equals to zero: -2

map_tail_n(map SRC, int N)

merge_maps(Map x)

SELECT 
merge_maps(m) 
FROM (
SELECT map('A',10,'B',20,'C',30) 
UNION ALL 
SELECT map('A',10,'B',20,'C',30)
) t;

to_map(key, value)

WITH input as (
select 'aaa' as key, 111 as value
UNION all
select 'bbb' as key, 222 as value
)
select to_map(key, value)
from input;

> {"bbb":222,"aaa":111}

to_ordered_map(key, value [, const boolean reverseOrder=false])

with t as (
select 10 as key, 'apple' as value
union all
select 3 as key, 'banana' as value
union all
select 4 as key, 'candy' as value
)
select
to_ordered_map(key, value, true), -- {10:"apple",4:"candy",3:"banana"} (reverse)
to_ordered_map(key, value, 1), -- {10:"apple"} (top-1)
to_ordered_map(key, value, 2), -- {10:"apple",4:"candy"} (top-2)
to_ordered_map(key, value, 3), -- {10:"apple",4:"candy",3:"banana"} (top-3)
to_ordered_map(key, value, 100), -- {10:"apple",4:"candy",3:"banana"} (top-100)
to_ordered_map(key, value), -- {3:"banana",4:"candy",10:"apple"} (natural)
to_ordered_map(key, value, -1), -- {3:"banana"} (tail-1)
to_ordered_map(key, value, -2), -- {3:"banana",4:"candy"} (tail-2)
to_ordered_map(key, value, -3), -- {3:"banana",4:"candy",10:"apple"} (tail-3)
to_ordered_map(key, value, -100) -- {3:"banana",4:"candy",10:"apple"} (tail-100)
from t;

string rowid()

SELECT rowid() as rowid, col1, col2 FROM input

long rownum()

SELECT rownum() as rowid, col1, col2 FROM input

double infinity()

boolean is_finite(number x)

SELECT is_finite(333), is_finite(infinity());
> true false

boolean is_infinite(number x)

SELECT is_infinite(333), is_infinite(infinity());
> false true 

boolean is_nan(number x)

SELECT is_nan(333), is_nan(nan());
> false true

double l2_norm(number x)

WITH input as (
select generate_series(1,3) as v
)
select l2_norm(v) as l2norm
from input;

> 19.621416870348583 = sqrt(1^2+2^2+3^2))

double nan()

SELECT nan(), is_nan(nan());
> NaN true

sigmoid(x)

WITH input as (
SELECT 3.0 as x
UNION ALL
SELECT -3.0 as x
)
select 
1.0 / (1.0 + exp(-x)),
sigmoid(x)
from
input;

> 0.04742587317756678 0.04742587357759476
> 0.9525741268224334 0.9525741338729858

transpose_and_dot(array<number> X, array<number> Y)

WITH input as (
select array(1.0, 2.0, 3.0, 4.0) as x, array(1, 2) as y
UNION ALL
select array(2.0, 3.0, 4.0, 5.0) as x, array(1, 2) as y
)
select
transpose_and_dot(x, y) as xy,
transpose_and_dot(y, x) as yx
from 
input;

> [["3.0","6.0"],["5.0","10.0"],["7.0","14.0"],["9.0","18.0"]] [["3.0","5.0","7.0","9.0"],["6.0","10.0","14.0","18.0"]]

vector_add(array<NUMBER> x, array<NUMBER> y)

SELECT vector_add(array(1.0,2.0,3.0), array(2, 3, 4));
> [3.0,5.0,7.0]

vector_dot(array<NUMBER> x, array<NUMBER> y)

SELECT vector_dot(array(1.0,2.0,3.0),array(2.0,3.0,4.0));
> 20

SELECT vector_dot(array(1.0,2.0,3.0),2);
> [2.0,4.0,6.0]

assert(boolean condition [, string errMsg])

SELECT count(1) FROM stock_price WHERE assert(price > 0.0);
SELECT count(1) FROM stock_price WHERE assert(price > 0.0, 'price MUST be more than 0.0')

raise_error()
raise_error(string errMsg)

SELECT product_id, price, raise_error('Found an invalid record') FROM xxx WHERE price < 0.0

moving_avg(NUMBER value, const int windowSize)

SELECT moving_avg(x, 3) FROM (SELECT explode(array(1.0,2.0,3.0,4.0,5.0,6.0,7.0)) as x) series;
> 1.0
> 1.5
> 2.0
> 3.0
> 4.0
> 5.0
> 6.0

convert_label(const int|const float)

each_top_k(int K, ANY_PRIMITIVE_TYPE group, double cmpKey, *)

SELECT 
   each_top_k(
      2, class, score,
      class, student -- optional argument(s) to be resulted in addition to rank and score
   ) as (rank, score, class, student) -- rank and score are resulted by the default
FROM (
SELECT class, score, student
FROM table
CLUSTER BY class -- Mandatory for `each_top_k`
) t

generate_series(const int|bigint start, const int|bigint end)

SELECT generate_series(2,4);
> 2
> 3
> 4

SELECT generate_series(5,1,-2);
> 5
> 3
> 1

SELECT generate_series(4,3);
> (no return)

SELECT date_add(current_date(),value),value from (SELECT generate_series(1,3)) t;
> 2018-04-21 1
> 2018-04-22 2
> 2018-04-23 3

WITH input as (
SELECT 1 as c1, 10 as c2, 3 as step
UNION ALL
SELECT 10, 2, -3
)
SELECT generate_series(c1, c2, step) as series
FROM input;
> 1
> 4
> 7
> 10
> 10
> 7
> 4

try_cast(ANY src, const string typeName)

SELECT try_cast(array(1.0,2.0,3.0), 'array<string>')
SELECT try_cast(map('A',10,'B',20,'C',30), 'map<string,double>')

x_rank(KEY)

to_libsvm_format(array<string> feautres [, double/integer target, const string options])

select to_libsvm_format(array(‘apple:3.4’,‘orange:2.1’));
> 6284535:3.4 8104713:2.1
select to_libsvm_format(array(‘apple:3.4’,‘orange:2.1’), ‘-features 10’);
> 3:2.1 7:3.4
select to_libsvm_format(array(‘7:3.4’,‘3:2.1’), 5.0);
> 5.0 3:2.1 7:3.4