hive高级数据类型

hive高级数据类型

hive的高级数据类型主要包括：数组类型、map类型、结构体类型、集合类型，以下将分别详细介绍。 1）数组类型array_type：array -- 建表语句 create table test.array_table(name   string,age    int,addr   array)row format delimited fields terminated by ',' collection items terminated by ':'; hive> desc test.array_table;OKname                    string                                      age                     int                                         addr                    array -- 测试数据 chavin,28,beijing:shanghai:shenyangnope,28,beijing:shanghai:taiyuanwzq,28,beijing:dalian:shenyang:fuxinzhangshuai,28,beijing:shenyang:fuxin -- 加载数据 hive> load data local inpath '/home/hadoop/array_table.txt' into table test.array_table;Loading data to table test.array_tableOKTime taken: 0.918 seconds -- 查询 hive> select * from test.array_table;OKchavin    28    ["beijing","shanghai","shenyang"]nope    28    ["beijing","shanghai","taiyuan"]wzq    28    ["beijing","dalian","shenyang","fuxin"]zhangshuai    28    ["beijing","shenyang","fuxin"]Time taken: 0.199 seconds, Fetched: 4 row(s) hive> select addr[1] from test.array_table;OKshanghaishanghaidalianshenyangTime taken: 0.186 seconds, Fetched: 4 row(s) hive> select addr[0] from test.array_table;OKbeijingbeijingbeijingbeijingTime taken: 0.212 seconds, Fetched: 4 row(s) 2）map类型map_type：map -- 建表语句 create table test.map_table(name      string,detail    map)row format delimited fields terminated by '\t' collection items terminated by ','map keys terminated by ':'; hive> desc test.map_table;OKname                    string                                      detail                  map                          Time taken: 0.146 seconds, Fetched: 2 row(s) -- 测试数据 chavin    age:28,addr:beijing,phone:15998888888,email:811651208@qq.comnope    age:28,addr:beijing,phone:13899998888,email:nope@gmail.comwzq    age:28,addr:shanghai,phone:13899998888,email:wzq@gmail.comzhangshuai    age:28,addr:tianjing,phone:13899998888,email:zs@gmail.com -- 加载数据 hive> load data local inpath '/home/hadoop/map.txt' into table test.map_table;Loading data to table test.map_tableOKTime taken: 0.641 seconds -- 查询数据 hive> select * from test.map_table;OKchavin    {"age":"28","addr":"beijing","phone":"15998888888","email":"811651208@qq.com"}nope    {"age":"28","addr":"beijing","phone":"13899998888","email":"nope@gmail.com"}wzq    {"age":"28","addr":"shanghai","phone":"13899998888","email":"wzq@gmail.com"}zhangshuai    {"age":"28","addr":"tianjing","phone":"13899998888","email":"zs@gmail.com"}Time taken: 0.183 seconds, Fetched: 4 row(s) hive> select detail from test.map_table;OK{"age":"28","addr":"beijing","phone":"15998888888","email":"811651208@qq.com"}{"age":"28","addr":"beijing","phone":"13899998888","email":"nope@gmail.com"}{"age":"28","addr":"shanghai","phone":"13899998888","email":"wzq@gmail.com"}{"age":"28","addr":"tianjing","phone":"13899998888","email":"zs@gmail.com"}Time taken: 0.177 seconds, Fetched: 4 row(s) hive> select name,detail['email'] from test.map_table;OKchavin    811651208@qq.comnope    nope@gmail.comwzq    wzq@gmail.comzhangshuai    zs@gmail.comTime taken: 0.179 seconds, Fetched: 4 row(s) 3）结构体类型struct_type：struct -- 建表语句 create table test.struct_table(name      string,detail    struct)row format delimited fields terminated by '\t' collection items terminated by ','; hive> desc test.struct_table;OKname                    string                                      detail                  struct                        Time taken: 0.139 seconds, Fetched: 2 row(s) -- 测试数据 chavin    28,15998888888,811651208@qq.comnope    28,13899998888,nope@gmail.comwzq    28,13899998888,wzq@gmail.comzhangshuai    28,13899998888,zs@gmail.com -- 加载数据 hive> load data local inpath '/home/hadoop/struct.txt' into table test.struct_table;Loading data to table test.struct_tableOKTime taken: 0.564 seconds -- 查询数据 hive> select * from test.struct_table;OKchavin    {"age":28,"phone":"15998888888","email":"811651208@qq.com"}nope    {"age":28,"phone":"13899998888","email":"nope@gmail.com"}wzq    {"age":28,"phone":"13899998888","email":"wzq@gmail.com"}zhangshuai    {"age":28,"phone":"13899998888","email":"zs@gmail.com"}Time taken: 0.183 seconds, Fetched: 4 row(s) hive> select name,detail.email from test.struct_table;OKchavin    811651208@qq.comnope    nope@gmail.comwzq    wzq@gmail.comzhangshuai    zs@gmail.comTime taken: 0.869 seconds, Fetched: 4 row(s) 4）集合类型union_type：uniontype -- 建表语句 create table test.union_table(detail    uniontype)row format delimited fields terminated by '\t' collection items terminated by ','; hive> desc test.union_table;OK                                     detail                  uniontype                        Time taken: 0.141 seconds, Fetched: 2 row(s)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。