集合类型相关函数

构建复杂对象的函数

  • 构建函数 操作数 说明
    map (key1, value1, key2, value2, …) Creates a map with the given key/value pairs.
    struct (val1, val2, val3, …) Creates a struct with the given field values.Struct field names will be col1, col2, ….
    named_struct (name1, val1, name2, val2, …) Creates a struct with the given field names and values. (As of Hive 0.8.0.)
    array (val1, val2, …) Creates an array with the given elements.
    create_union (tag, val1, val2, …) Creates a union type with the value that is being pointed to by the tag parameter.

获取元素

  • 操作 操作数类型 说明
    A[n] A is an Array and n is an int Returns the nth element in the array A. The first element has index 0. For example, if A is an array comprising of [‘foo’, ‘bar’] then A[0] returns ‘foo’ and A[1] returns ‘bar’.
    M[key] M is a Map<K, V> and key has type K Returns the value corresponding to the key in the map. For example, if M is a map comprising of {‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’} then M[‘all’] returns ‘foobar’.
    S.x S is a struct Returns the x field of S. For example for the struct foobar {int foo, int bar}, foobar.foo returns the integer stored in the foo field of the struct.

其他

  • 返回值类型 函数 说明
    int size(Map<K.V>) Returns the number of elements in the map type.
    int size(Array) Returns the number of elements in the array type.
    array map_keys(Map<K.V>) Returns an unordered array containing the keys of the input map.
    array map_values(Map<K.V>) Returns an unordered array containing the values of the input map.
    boolean array_contains(Array, value) Returns TRUE if the array contains value.
    array sort_array(Array) Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0). 
with tmp as(
    select
        array(1,3,2,5,4) as a1,
        map('1','a','2','b','3','c') as m1,
        struct(1,'X',array('Y','Z')) as s1
)
select
    a1[0],
    m1['2'],
    s1.col3,
    size(a1),
    map_keys(m1),
    array_contains(a1, 0),
    sort_array(a1)
from tmp;

index( [array | map], int) 函数获取元素

  • select
    index(array(1,2,3,4,5), 2),
    index(map('1','a','2','b','3','c'), 2),
    index(map('1','a','22','b','3','c'), 2);

022

sort_array_by 排序

  • sort_array_by(array(struct1, struct2,...),'f1'[,'f2',...][,'ASC','DESC'])

  • 对 array(<struct>) 进行排序

  • with tmp as(
    select array(
        struct('g',100),
        struct('b',200),
        struct('v',300),
        struct('b',400)
    ) as a1
)
SELECT
    chr(10),
    a1,
    chr(10),
    sort_array_by(a1, 'col1'),
    chr(10),
    sort_array_by(a1, 'col1', 'asc'),
    chr(10),
    sort_array_by(a1, 'col1', 'desc'),
    chr(10),
    sort_array_by(a1, 'col1', 'col2', 'desc')
from tmp;

  • 022a