Hive第二课简单应用

Hive 语句的简单案例：

hive -hiveconf hive.root.logger=DEBUG,console 调试

[hduser@cloud00 second]$ vi stu2015 
liubing,20031001,23
liuy,20034001,24
zhangwu,20040333,21
lizhang,20033233,20
wuzh,23232323,19
[hduser@cloud00 second]$ vi infor2015 
20031001,man,beijing xidan       	
20034001,woman,shanghai road
20040333,man,guangzhou road
20033233,woman,fujian road
23232323,man,jiangshu road

create table if not exists student ( name string,number string,age  string) PARTITIONED BY ( dt STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',\t' STORED AS TEXTFILE;

create table if not exists studentinfor ( number string,sex string,address  string) PARTITIONED BY ( dt STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',\t' STORED AS TEXTFILE;

hive> alter table student add partition (dt='2015') location '/student/2015';  
alter table student add partition (dt='2014') location '/student/2014/'; '; 

hive> load data local inpath '/home/hduser/second/stu2015' overwrite into table student partition (dt='2015');

hive> alter table student drop partition ( dt='2014' );

hive> alter table studentinfor add partition (dt='2015');     
hive> load data local inpath '/home/hduser/second/infor2015' overwrite into table studentinfor partition (dt='2015');

修改学生表
hive> create table student_copy like student;
hive> ALTER TABLE student_copy RENAME TO students_czj;
hive> desc students_czj;
hive> drop table students_czj;


hive> select if(name ='liubing',"OK","faile") from student;

hive> select * from student order by name asc;

hive> select * from student order by name desc;

hive> insert overwrite table t1 select name,age
hive> insert overwrite table t2 select name,count(name) group by name;

hive> select name,max(age) from ( select name,age from student) tt group by name;

hive> select student.* ,studentinfor.* from student join studentinfor on ( student.number=studentinfor.number);

hive> select A.*,B.sex,B.address from student as A LEFT OUTER join studentinfor B on ( A.number=B.number);                   
hive> select A.*,B.sex,B.address from student as A LEFT OUTER join studentinfor B on ( A.number=B.number) where A.dt='2015' and B.dt='2015';
hive> select t1.name,C.address,B.age,B.number from t1 join student B on ( t1.name=B.name ) join studentinfor C on ( B.number=C.number );

hive> create view student_view as select t1.name,C.address,B.age,B.number from t1 join student B on ( t1.name=B.name ) join studentinfor C on ( B.number=C.number );

CREATE INDEX inedx_number ON TABLE student(number) AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with DEFERRED REBUILD;


编译与运行
ant
[hduser@cloud00 bin]$ hive --service hiveserver2& 根据端口号10000 10001来进行定义 
[root@cloud00 builder]# java -jar dist/builder-0.0.1.jar main.HiveClent
java -jar dist/builder-0.0.1.jar main.myhivecli;
执行hwi工具
[hduser@cloud00 bin]$ hive --service hwi& 
http://10.9.110.56:9999/hwi/