根据数据创建表

案例1:简单用户信息

1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

use hive01;
​
create table t_user(id int,uname string,pwd string,sex string,age int )
row format delimited fields terminated by ','; --指定 字段之间用 , 分隔
​
当我们创建表后 在HDFS上会产生对应的文件夹 
/user/hive/warehouse/hive01.db/t_user
但是此时里面没有任何文件 
我们可以将user.txt文件上传到这个位置
​
select  * from t_user;
+------------+---------------+-------------+-------------+-------------+
| t_user.id  | t_user.uname  | t_user.pwd  | t_user.sex  | t_user.age  |
+------------+---------------+-------------+-------------+-------------+
| 1          | admin         | 123456      | 男           | 18          |
| 2          | zhangsan      | abc123      | 男           | 23          |
| 3          | lisi          | 654321      | 女           | 16          |
+------------+---------------+-------------+-------------+-------------+
​

案例2:复杂人员信息

liuyan,tangtang_mimi,liuliu:18_yanyan:14,hui long guan_beijing
jinlian,dalang_qingqing,jinjin:18_lianlian:19_aa:20,chao yang_beijing
​

结构

{"name": "liuyan","friends": ["tangtang" , "mimi"] ,       //列表Array, "children": {                      //键值Map,"liuliu": 18 ,"yanyan": 14}"address": {                      //结构Struct,"street": "hui long guan" ,"city": "beijing" }
}

建表

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
-- 语法解释
row format delimited fields terminated by ','  -- 列分隔符
collection items terminated by '_'      -- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':'              -- MAP中的key与value的分隔符

复杂类型查询操作

1.数组

- arr[index]   取值
- size(arr)     长度
- 数组角标越界返回NULL
- explode(arr)
​
select name,friend[0],friend[1],friend[2] from t_user2;
+----------+-----------+-----------+-------+
|   name   |    _c1    |    _c2    |  _c3  |
+----------+-----------+-----------+-------+
| liuyan   | tangtang  | mimi      | NULL  |
| jinlian  | dalang    | qingqing  | NULL  |
+----------+-----------+-----------+-------+
​
select name,size(friend) as frendsize from  t_user2;
+----------+------------+
|   name   | frendsize  |
+----------+------------+
| liuyan   | 2          |
| jinlian  | 2          |
+----------+------------+
​

2.Map

map_keys(字段)  所有key
map_values(字段) 所有value
​
select map_keys(children),map_values(children) from t_user2;
+------------------------+----------+
|          _c0           |   _c1    |
+------------------------+----------+
| ["liuliu","yanyan"]    | [18,14]  |
| ["jinjin","lianlian"]  | [18,19]  |
+------------------------+----------+
select map_keys(children)[0],map_values(children)[0] from t_user2;
+---------+------+
|   _c0   | _c1  |
+---------+------+
| liuliu  | 18   |
| jinjin  | 18   |
+---------+------+
-- 根据key获取值
select children['liuliu'] from t_user2
+-------+
|  _c0  |
+-------+
| 18    |
| NULL  |
+-------+
​

3.Struct

select name,address.city,address.street from t_user2;
+----------+----------+---------------+
|   name   |   city   |    street     |
+----------+----------+---------------+
| liuyan   | beijing  | huilong guan  |
| jinlian  | beijing  | chao yang     |
+----------+----------+---------------+