Hive 操作语句...

//在复习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 "//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;