大数据之hive表操作

 

hive安装

 

---

---

1,xuhaitao,36
2,hunkxu,63
3,zhouwa,13

 create table biao001(id int,name string,age int) row format delimited fields terminated by ',';

---

034,1:2:3:4
035,5:6
036,7:8:9:10

 create table class_test(name string, student_id_list array<INT>)
 ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY ','
 COLLECTION ITEMS TERMINATED BY ':';

  

 或者使用下面命令查询list中指定索引值

 select   likes[1]  from table03;

---

注意下面的空格是tab键的空格，而非空格键的空格

xuhaitao        唱歌,跳舞
hunkxu  练武,写代码

 create table table03(name string,likes array<string>)
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY '\t'
 COLLECTION ITEMS TERMINATED BY ',';

 

---

分区表，注意分区表的字段一定不能够在表中存在

---

create table tb01(id int,city  string) partitioned by (country string)  row format 
delimited fields terminated by ',';

---

准备两批数据,，分别是1.txt和2.txt

---

1.txt

1,郑州
2,信阳
3,洛阳

 

 分区表，必须使用load加载数据

load data local  inpath '/1.txt' into table tb01  partition (country='henan');

 

 

 

---

 接着加载第二段数据

1,深圳
2,珠海
3,广州

---

load data local inpath '/2.txt' into table tb01  partition (country='guangdong');

 

 

---

分桶表，注意分桶表中字段必须是表中存在的

---

 做分桶需要打开分桶开关，默认是关着的 

set hive.enforce.bucketing =true;
set mapreduce.job.reduces=4;

 

---

 创建一个桶表

create table stu_bucket (bianhao int ,name string,sex string ,age int )
clustered by (bianhao)
into 4 buckets
row format delimited 
fields  terminated by ',';

还需要准备一张临时表

create table stu (bianhao int ,name string,sex string ,age int )
row format delimited 
fields  terminated by ',';

下面准备一些分桶数据 students.txt

22,xuhaitao,nan,30
24,hunkxu,nan,25
25,xiaoxu,nan,39
26,xiaotao,nan,40
27,xiaohuan,nan,35
28,xuaishuo,nan,3
29,daiping,nv,46
30,guozhu,nan,49
31,xiaojie,nv,31
32,xxx,nv,19

  将上面数据导入到stu表中

load data local inpath  '/students.txt' into table  stu;

 

---

 然后执行下面指令进行分桶

    insert overwrite table stu_bucket
    select * from stu cluster by (bianhao);

---

 

---

 

 

FR:徐海涛（hunk Xu)
QQ技术交流群：386476712