hive 常用函数整理 9.复合类型操作

1. map类型构建: map

语法: map (key1, value1, key2, value2, …)

说明：根据输入的key和value对构建map类型

举例：

hive> Create table lxw_test as select map('100','tom','200','mary') as t from student;

hive> describe lxw_test;

t       map<string,string>

hive> select t from lxw_test;

{"100":"tom","200":"mary"}

--通过key 查询value

hive> select t['100'] from lxw_test limit 1;

Tom

--查看键值对的个数

hive> select size(t) from lxw_test limit 1;

2

2. map类型访问: M[key]

语法: M[key]

操作类型: M为map类型，key为map中的key值

说明：返回map类型M中，key值为指定值的value值。比如，M是值为{‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’}的map类型，那么M[‘all’]将会返回’foobar’

举例：

hive> Create table lxw_test as select map('100','tom','200','mary') as t from student;

hive> select t['200'],t['100'] from lxw_test;

mary    tom

3. map 取出key集合

4. map类型长度函数: size(Map<K.V>)

语法: size(Map<K.V>)

返回值: int

说明: 返回map类型的长度

举例：

hive> select size(map('100','tom','101','mary')) from student;

2

5. array类型构建: array

语法: array(val1, val2, …)

说明：根据输入的参数构建数组array类型

举例：

hive> create table lxw_test as select array("tom","mary","tim") as t from student;

hive> describe lxw_test;

t       array<string>

hive> select t from lxw_test;

["tom","mary","tim"]

hive>  select t[1] from lxw_test3;

mary

6. array类型访问: A[n]

语法: A[n]

操作类型: A为array类型，n为int类型

说明：返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为[‘foo’, ‘bar’]的数组类型，那么A[0]将返回’foo’,而A[1]将返回’bar’

举例：

hive> create table lxw_test as select array("tom","mary","tim") as t from student;

hive> select t[0],t[1],t[2] from lxw_test;

tom     mary    tim

7. array类型长度函数: size(Array)

语法: size(Array)

返回值: int

说明: 返回array类型的长度

举例：

hive> select size(array('100','101','102','103')) from student;

4

8. struct类型构建: struct

语法: struct(val1, val2, val3, …)

说明：根据输入的参数构建结构体struct类型

举例：

hive> create table lxw_test2 as select struct('tom','mary','tim') as t from student;

hive> describe lxw_test2;

t       struct<col1:string,col2:string,col3:string>

hive> select t from lxw_test2;

{"col1":"tom","col2":"mary","col3":"tim"}

hive> select t.col1 from lxw_test2;

tom

9. struct类型访问: S.x

语法: S.x

操作类型: S为struct类型

说明：返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段

举例：

hive> create table lxw_test as select struct('tom','mary','tim') as t from student;

hive> describe lxw_test;

t       struct<col1:string,col2:string,col3:string>

hive> select t.col1,t.col3 from lxw_test;

tom     tim

10. 类型转换函数 cast(expr as )

类型转换函数: cast

语法: cast(expr as )

返回值: Expected “=” to follow “type”

说明: 返回array类型的长度

举例：

hive>  select cast(1 as bigint) from student;

1

hive>  Create table lxw_test5 as select 1 as t from student;

hive> desc lxw_test5;

t                           int

hive>  Create table lxw_test6 as select cast(1 as bigint) as t from student;

hive> desc lxw_test6;

t                           bigint