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;