(1) 创建student库,在库内创建一个新的内部表stu_1(数据、结构见stu.txt),把数据导入到表内
stu.txt的数据如下:
95002,刘晨,gril,19,IS
95017,王风娟,gril,18,IS
95018,王一,gril,19,IS
95013,冯伟,boy,21,CS
95014,王小丽,gril,19,CS
95019,邢小丽,gril,19,IS
95020,赵钱,boy,21,IS
95003,王敏,gril,22,MA
95004,张立,boy,19,IS
95012,孙花,gril,20,CS
95010,孔小涛,boy,19,CS
95005,刘刚,boy,18,MA
95006,孙庆,boy,23,CS
95007,易思玲,gril,19,MA
95008,李娜,gril,18,CS
95021,周二,boy,17,MA
95022,郑明,boy,20,MA
95001,李勇,boy,20,CS
95011,包小柏,boy,18,MA
95009,梦圆圆,gril,18,MA
95015,王君,boy,18,MA
命令操作:
0: jdbc:hive2://hadoop01:11240> create database student;
0: jdbc:hive2://hadoop01:11240> use student;
0: jdbc:hive2://hadoop01:11240> create table stu_1(sno int,sname string,sex string,sage int,sdept string)
. . . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . . > fields terminated by ',';
0: jdbc:hive2://hadoop01:11240> load data local inpath '/home/xiaokang/hivedata/stu.txt'
. . . . . . . . . . . . . . . > into table stu_1;
0: jdbc:hive2://hadoop01:11240> select * from stu_1;
+------------+--------------+------------+-------------+--------------+
| stu_1.sno | stu_1.sname | stu_1.sex | stu_1.sage | stu_1.sdept |
+------------+--------------+------------+-------------+--------------+
| 95002 | 刘晨 | gril | 19 | IS |
| 95017 | 王风娟 | gril | 18 | IS |
| 95018 | 王一 | gril | 19 | IS |
| 95013 | 冯伟 | boy | 21 | CS |
| 95014 | 王小丽 | gril | 19 | CS |
| 95019 | 邢小丽 | gril | 19 | IS |
| 95020 | 赵钱 | boy | 21 | IS |
| 95003 | 王敏 | gril | 22 | MA |
| 95004 | 张立 | boy | 19 | IS |
| 95012 | 孙花 | gril | 20 | CS |
| 95010 | 孔小涛 | boy | 19 | CS |
| 95005 | 刘刚 | boy | 18 | MA |
| 95006 | 孙庆 | boy | 23 | CS |
| 95007 | 易思玲 | gril | 19 | MA |
| 95008 | 李娜 | gril | 18 | CS |
| 95021 | 周二 | boy | 17 | MA |
| 95022 | 郑明 | boy | 20 | MA |
| 95001 | 李勇 | boy | 20 | CS |
| 95011 | 包小柏 | boy | 18 | MA |
| 95009 | 梦圆圆 | gril | 18 | MA |
| 95015 | 王君 | boy | 18 | MA |
+------------+--------------+------------+-------------+--------------+
(2) 创建一个外部表stu_2,数据也是stu.txt
0: jdbc:hive2://hadoop01:11240> create external table stu_2(sno int,sname string,sex string,sage int,sdept string)
. . . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . . > fields terminated by ','
. . . . . . . . . . . . . . . > location '/stu';
0: jdbc:hive2://hadoop01:11240> load data local inpath '/home/xiaokang/hivedata/stu.txt'
. . . . . . . . . . . . . . . > into table stu_2;
0: jdbc:hive2://hadoop01:11240> select * from stu_2;
+------------+--------------+------------+-------------+--------------+
| stu_2.sno | stu_2.sname | stu_2.sex | stu_2.sage | stu_2.sdept |
+------------+--------------+------------+-------------+--------------+
| 95002 | 刘晨 | gril | 19 | IS |
| 95017 | 王风娟 | gril | 18 | IS |
| 95018 | 王一 | gril | 19 | IS |
| 95013 | 冯伟 | boy | 21 | CS |
| 95014 | 王小丽 | gril | 19 | CS |
| 95019 | 邢小丽 | gril | 19 | IS |
| 95020 | 赵钱 | boy | 21 | IS |
| 95003 | 王敏 | gril | 22 | MA |
| 95004 | 张立 | boy | 19 | IS |
| 95012 | 孙花 | gril | 20 | CS |
| 95010 | 孔小涛 | boy | 19 | CS |
| 95005 | 刘刚 | boy | 18 | MA |
| 95006 | 孙庆 | boy | 23 | CS |
| 95007 | 易思玲 | gril | 19 | MA |
| 95008 | 李娜 | gril | 18 | CS |
| 95021 | 周二 | boy | 17 | MA |
| 95022 | 郑明 | boy | 20 | MA |
| 95001 | 李勇 | boy | 20 | CS |
| 95011 | 包小柏 | boy | 18 | MA |
| 95009 | 梦圆圆 | gril | 18 | MA |
| 95015 | 王君 | boy | 18 | MA |
+------------+--------------+------------+-------------+--------------+
(3)数据、结构见cdt.txt,建立对应的表cdt,注意复杂数据类型结构,有map,struct等。
cdt.txt的数据如下:
1 huangbo guangzhou,xianggang,shenzhen a1:30,a2:20,a3:100 beijing,112233,13522334455,500
2 xuzheng xianggang b2:50,b3:40 tianjin,223344,13644556677,600
3 wangbaoqiang beijing,zhejinag c1:200 chongqinjg,334455,15622334455,20
cdt结构解析:id,name,work_location(array),piaofang(map),address(struct(city,zipcode,phone,value))
0: jdbc:hive2://hadoop01:11240> create table cdt(id int,
. . . . . . . . . . . . . . . > name string,
. . . . . . . . . . . . . . . > work_location array<string>,
. . . . . . . . . . . . . . . > piaofang map<string,int>,
. . . . . . . . . . . . . . . > address struct<city:string,zipcode:int,phone:string,value:int>)
. . . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . . > fields terminated by ' '
. . . . . . . . . . . . . . . > collection items terminated by ','
. . . . . . . . . . . . . . . > map keys terminated by ':'
. . . . . . . . . . . . . . . > lines terminated by '\n';
0: jdbc:hive2://hadoop01:11240> load data local inpath '/home/xiaokang/hivedata/cdt.txt'
. . . . . . . . . . . . . . . > into table cdt;
0: jdbc:hive2://hadoop01:11240> select * from cdt;
+---------+---------------+---------------------------------------+-----------------------------+----------------------------------------------------+
| cdt.id | cdt.name | cdt.work_location | cdt.piaofang | cdt.address |
+---------+---------------+---------------------------------------+-----------------------------+----------------------------------------------------+
| 1 | huangbo | ["guangzhou","xianggang","shenzhen"] | {"a1":30,"a2":20,"a3":100} | {"city":"beijing","zipcode":112233,"phone":"13522334455","value":500} |
| 2 | xuzheng | ["xianggang"] | {"b2":50,"b3":40} | {"city":"tianjin","zipcode":223344,"phone":"13644556677","value":600} |
| 3 | wangbaoqiang | ["beijing","zhejinag"] | {"c1":200} | {"city":"chongqinjg","zipcode":334455,"phone":"15622334455","value":20} |
+---------+---------------+---------------------------------------+-----------------------------+----------------------------------------------------+
要求:
- 按照work_location的第一个数据进行查询
0: jdbc:hive2://hadoop01:11240> select work_location[0] from cdt;
+------------+
| _c0 |
+------------+
| guangzhou |
| xianggang |
| beijing |
+------------+
- 查询piaofang的key值是a1的value
0: jdbc:hive2://hadoop01:11240> select piaofang['a1'] from cdt;
+-------+
| _c0 |
+-------+
| 30 |
| NULL |
| NULL |
+-------+
(4)根据内部表stu_1的数据,创建新表stu_3,要求id<95102
0: jdbc:hive2://hadoop01:11240> create table stu_3 like stu_1;
0: jdbc:hive2://hadoop01:11240> insert overwrite table stu_3
. . . . . . . . . . . . . . . > select * from stu_1 where sno<95102;
0: jdbc:hive2://hadoop:11240> select * from stu_3;
+------------+--------------+------------+-------------+--------------+
| stu_3.sno | stu_3.sname | stu_3.sex | stu_3.sage | stu_3.sdept |
+------------+--------------+------------+-------------+--------------+
| 95001 | 李勇 | boy | 20 | CS |
| 95002 | 刘晨 | gril | 19 | IS |
| 95003 | 王敏 | gril | 22 | MA |
| 95004 | 张立 | boy | 19 | IS |
| 95005 | 刘刚 | boy | 18 | MA |
| 95006 | 孙庆 | boy | 23 | CS |
| 95007 | 易思玲 | gril | 19 | MA |
| 95008 | 李娜 | gril | 18 | CS |
| 95009 | 梦圆圆 | gril | 18 | MA |
| 95010 | 孔小涛 | boy | 19 | CS |
| 95011 | 包小柏 | boy | 18 | MA |
| 95012 | 孙花 | gril | 20 | CS |
| 95013 | 冯伟 | boy | 21 | CS |
| 95014 | 王小丽 | gril | 19 | CS |
| 95015 | 王君 | boy | 18 | MA |
| 95016 | 钱国 | boy | 21 | MA |
| 95017 | 王风娟 | gril | 18 | IS |
| 95018 | 王一 | gril | 19 | IS |
| 95019 | 邢小丽 | gril | 19 | IS |
| 95020 | 赵钱 | boy | 21 | IS |
| 95021 | 周二 | boy | 17 | MA |
| 95022 | 郑明 | boy | 20 | MA |
+------------+--------------+------------+-------------+--------------+
(5)创建分区表stu_4,要求添加city静态分区,利用stu_1的数据,所有人city分区为baoding。
0: jdbc:hive2://hadoop:11240> create table stu_4(sno int,sname string,sex string,sage int, sdept string)
. . . . . . . . . . . . . . > partitioned by (city string)
. . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . > fields terminated by ',';
0: jdbc:hive2://hadoop:11240> insert overwrite table stu_4 partition (city="baoding")
. . . . . . . . . . . . . . > select * from stu_1;
0: jdbc:hive2://hadoop:11240> select * from stu_4;
+------------+--------------+------------+-------------+--------------+-------------+
| stu_4.sno | stu_4.sname | stu_4.sex | stu_4.sage | stu_4.sdept | stu_4.city |
+------------+--------------+------------+-------------+--------------+-------------+
| 95001 | 李勇 | boy | 20 | CS | baoding |
| 95002 | 刘晨 | gril | 19 | IS | baoding |
| 95003 | 王敏 | gril | 22 | MA | baoding |
| 95004 | 张立 | boy | 19 | IS | baoding |
| 95005 | 刘刚 | boy | 18 | MA | baoding |
| 95006 | 孙庆 | boy | 23 | CS | baoding |
| 95007 | 易思玲 | gril | 19 | MA | baoding |
| 95008 | 李娜 | gril | 18 | CS | baoding |
| 95009 | 梦圆圆 | gril | 18 | MA | baoding |
| 95010 | 孔小涛 | boy | 19 | CS | baoding |
| 95011 | 包小柏 | boy | 18 | MA | baoding |
| 95012 | 孙花 | gril | 20 | CS | baoding |
| 95013 | 冯伟 | boy | 21 | CS | baoding |
| 95014 | 王小丽 | gril | 19 | CS | baoding |
| 95015 | 王君 | boy | 18 | MA | baoding |
| 95016 | 钱国 | boy | 21 | MA | baoding |
| 95017 | 王风娟 | gril | 18 | IS | baoding |
| 95018 | 王一 | gril | 19 | IS | baoding |
| 95019 | 邢小丽 | gril | 19 | IS | baoding |
| 95020 | 赵钱 | boy | 21 | IS | baoding |
| 95021 | 周二 | boy | 17 | MA | baoding |
| 95022 | 郑明 | boy | 20 | MA | baoding |
+------------+--------------+------------+-------------+--------------+-------------+
(6)创建分区表stu_5,要求根据性别动态分区,数据来源stu_1。
#开启动态分区,默认是false
0: jdbc:hive2://hadoop:11240> set hive.exec.dynamici.partition=true;
#开启允许所有分区都是动态的,否则必须要有静态分区才能使用。
0: jdbc:hive2://hadoop:11240> set hive.exec.dynamic.partition.mode=nonstrict;
0: jdbc:hive2://hadoop:11240> create table stu_5(sno int,sname string,sage int,sdept string)
. . . . . . . . . . . . . . > partitioned by (sex string)
. . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . > fields terminated by ',';
0: jdbc:hive2://hadoop:11240> insert overwrite table stu_5 partition(sex)
. . . . . . . . . . . . . . > select sno,sname,sage,sdept,sex as sex from stu_1;
0: jdbc:hive2://hadoop:11240> select * from stu_5;
+------------+--------------+-------------+--------------+------------+
| stu_5.sno | stu_5.sname | stu_5.sage | stu_5.sdept | stu_5.sex |
+------------+--------------+-------------+--------------+------------+
| 95013 | 冯伟 | 21 | CS | boy |
| 95020 | 赵钱 | 21 | IS | boy |
| 95004 | 张立 | 19 | IS | boy |
| 95010 | 孔小涛 | 19 | CS | boy |
| 95005 | 刘刚 | 18 | MA | boy |
| 95006 | 孙庆 | 23 | CS | boy |
| 95021 | 周二 | 17 | MA | boy |
| 95022 | 郑明 | 20 | MA | boy |
| 95001 | 李勇 | 20 | CS | boy |
| 95011 | 包小柏 | 18 | MA | boy |
| 95015 | 王君 | 18 | MA | boy |
| 95002 | 刘晨 | 19 | IS | girl |
| 95017 | 王风娟 | 18 | IS | girl |
| 95018 | 王一 | 19 | IS | girl |
| 95014 | 王小丽 | 19 | CS | girl |
| 95019 | 邢小丽 | 19 | IS | girl |
| 95003 | 王敏 | 22 | MA | girl |
| 95012 | 孙花 | 20 | CS | girl |
| 95007 | 易思玲 | 19 | MA | girl |
| 95008 | 李娜 | 18 | CS | girl |
| 95009 | 梦圆圆 | 18 | MA | girl |
+------------+--------------+-------------+--------------+------------+
(7)创建桶表,数据来源是stu_1,按照ID分为4个桶表。
0: jdbc:hive2://hadoop:11240> set hive.enforce.bucketing=true;
0: jdbc:hive2://hadoop:11240> set mapreduce.job.reduces=4;
0: jdbc:hive2://hadoop:11240> set hive.enforce.bucketing;
+------------------------------+
| set |
+------------------------------+
| hive.enforce.bucketing=true |
+------------------------------+
0: jdbc:hive2://hadoop:11240> create table stu_6(sno int,sname string,sex string,sage int,sdept string)
. . . . . . . . . . . . . . > clustered by(sno)
. . . . . . . . . . . . . . > into 4 buckets
. . . . . . . . . . . . . . > row format delimited
. . . . . . . . . . . . . . > fields terminated by ',';
0: jdbc:hive2://hadoop:11240> insert overwrite table stu_6
. . . . . . . . . . . . . . > select * from student cluster by(sno);
0: jdbc:hive2://hadoop:11240> select * from stu_6;
+------------+--------------+------------+-------------+--------------+
| stu_6.sno | stu_6.sname | stu_6.sex | stu_6.sage | stu_6.sdept |
+------------+--------------+------------+-------------+--------------+
| NULL | NULL | NULL | NULL | NULL |
| 95004 | 张立 | boy | 19 | IS |
| 95008 | 李娜 | gril | 18 | CS |
| 95012 | 孙花 | gril | 20 | CS |
| 95016 | 钱国 | boy | 21 | MA |
| 95020 | 赵钱 | boy | 21 | IS |
| 95001 | 李勇 | boy | 20 | CS |
| 95005 | 刘刚 | boy | 18 | MA |
| 95009 | 梦圆圆 | gril | 18 | MA |
| 95013 | 冯伟 | boy | 21 | CS |
| 95017 | 王风娟 | gril | 18 | IS |
| 95021 | 周二 | boy | 17 | MA |
| 95002 | 刘晨 | gril | 19 | IS |
| 95006 | 孙庆 | boy | 23 | CS |
| 95010 | 孔小涛 | boy | 19 | CS |
| 95014 | 王小丽 | gril | 19 | CS |
| 95018 | 王一 | gril | 19 | IS |
| 95022 | 郑明 | boy | 20 | MA |
| 95003 | 王敏 | gril | 22 | MA |
| 95007 | 易思玲 | gril | 19 | MA |
| 95011 | 包小柏 | boy | 18 | MA |
| 95015 | 王君 | boy | 18 | MA |
| 95019 | 邢小丽 | gril | 19 | IS |
+------------+--------------+------------+-------------+--------------+