//在復習Hive中..于是寫了寫語句做練習create database mydb; show database; drop database mydb; use mydb; show tables; //create table methods create table stu(id int,name string,age int ) row format delimited fields terminated by","; create external table stu(id int,name string) row format delimited fields terminated by "," location '/home'; create table stu2(id int,name string,age int) partitioned by (city string) row format delimited terminated fields terminated by ","; create table stu3(id int,name string,age int) clustered by(id) sorted by(age) into 4 buckets row format delimited terminated fields terminated by ","; drop table if exists stu3; //table alter table stu rename to student; alter table stu add columns (PRovice string,class string); alter table stu change age newage string; alter table stu replace (id int,name string,sex string); //partition alter table stu add partition(city="beijing"); alter table stu add partition(city="string") partition(city="other"); alter table stu drop partition(city="beijing"); //show show database; show tables; show functions; show partition stu; desc stu; desc extened stu; desc formatted stu; //load datas load data local inpath "/home.txt" into table stu; load data local inpath "http://m.txt" override into table stu; load data inpath "hdfs://hadoop01:9000/1.txt" into table stu; //insert methods insert into table stu values(001,'wang','male',50); insert into table stu select id,name,age form students; form school insert into table stu1 select id,name insert into table stu2 select id,sex; from school insert into table stu partition(department="MA") select id,name where department="MA" insert into table stu2 partition(city="beijing")select id ,name where city="beijing"; load data local inpath "/root/k.txt" into table stu partition(city="henan"); insert table stu partition(department="Cs") select name,delimited,city from school; //注意：動態分區插入的分區字段必須是查詢語句當中出現的字段中的最后一個 //cats create table dem as select id,name,age form mingxing; //like create table stu4 like stu; //insert導出數據到本地 insert override local directory '/home/data' select id,name from stu; from school insert override local directory '/home/yxy' select id ,name insert override local directory '/home/hadoop01' select sex ,department; insert override directory "hdfs://hadoop01:9000/1" select id,name form school; //truncate table truncate table school; //select methods select * from school order by age desc,id asc; set maperd.job.tasks=3; //如果數據量過大,我們采用局部排序的方式: select * from mingxing sort by id asc; //分桶查詢 set hive.enforace.bucketing=true; select * from mingxing distribute by sex; select * from mingxing cluster by id sort by id desc,age asc; //inner join select school.*,stu.* from school join on stu on school.id=stu.id; //left out join select stu.*,mingxing.* from stu left out join mingxing on stu.id=mingxing.id; //right out join //full out join //Left semi join (in /exist 高效實現) select school.* ,mingxing.* from school left semi join mingxing on school.id=mingxing.id;
