insert overwrite table employees partition (country='US'，state='OR')

                select * from staged_employees se where se.cntry='US' and se.st='OR';

                from staged_employees se

                insert overwrite table employees

                    partition(country='US' , state='OR')

                    select * where se.cnty='US' and se.st='OR'

                insert overwrite table employees

                    partition(country='US',state='CA')

                    select * where se.cnty='US' and se.st='CA';

                    insert overwrite table employees

                    partition (country,state)

                    select ... ，se.cnty，se.st

                    from staged_employees se;

也可以混合使用动态和静态分区，下面例子指定了country字段的值为静态的US，而分区字段state是动态值：

                    insert overwrite table employees partition (country='US'，state)

                    select ...，se.cnty，se.st from staged_employees se where se.cnty='US'；

5.3 单个查询语句中创建表并加载数据

                    create table ca_employees

                    as select name,salary,address

                    from employees where se.state='CA'；

select name,subordinates[0] from employees;

select name,deductions["State Taxes"] from employees;

select name,address.city from employees;

   

select * from employees limit 2;

                      select name,salary,

                            case

                                when salary<5000.0 then 'low'

                                when salary>=5000.0 and salary<7000.0 then 'middle'

                                when salary>7000.0 then 'high'

                            end as bracket from employees;

select name,address.street from employees where address.street rlike '.*(Chicago|Ontario).*'；

                    select a.ymd, a.price_close, b.price_close from stocks a join stocks b on a.ymd=b.ymd where a.symbol='APPL' and b.symbol='IBM'；

                    select a.name, b.companyname from company a join employees b on a.companycode=b.companycode，要保证表大小company <employees 。

                        select s.ymd, s.symbol, s.price_close from stocks s distribute by s.symbol sort by s.symbol ASC, s.ymd ASC；

select s.ymd, s.symbol, s.price_close from stocks s cluster by s.symbol；

        from (

                select * from people join cart

                    on (cart.people_id=people.id) where firstname='john'

        ) a select a.lastname where a.id=3;

        create view shorter_join as

        select * from people join cart on (cart.people_id=people.id) where firstname='john';

        select lastname from shorter_join where id=3;

        create index employees_index on table employees(country)

        as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'

        with deferred rebuild

        idxproperties('creator'='me', 'created_at'='some_time') in table employees_index_table

        partitioned by (country,name) comment 'Employees indexed by country and name.'；

        create table supply(id int, part string, quantity int) partitioned by (int day)；

        alter table supply add partition (day=20110102);

        alter table supply add partition (day=20110103);

        alter table supply add partition (day=20110104);

        insert overwrite table sales

                select * from history where action='purchased'；

        insert overwrite table credits

                select * from history where action='returned'；

        from history

        insert overwrite sales select * where action='purchased'

        insert overwrite credits select * where action='returned'；

         hive.limit.optimize.enable=true --- 开启对数据源进行采样的功能

         hive.limit.row.max.size --- 设置最小的采样容量

         hive.limit.optimize.limit.file --- 设置最大的采样样本数

set hive.exec.mode.local.auto=true;

        set hive.exec.parallel=true,可以开启并发执行。

        set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。

select transform(e.name, e.salary) using '/bin/cat' as name, salary from employee e；

            create table colunmTable(key int, value int)

            row format serde

            'org.apache.hive.serde2.columnar.ColumnarSerde'

            stored as

            inputformat 'org.apache.hadoop.hive.ql.io.RCFileInputFormat'

            outputformat 'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'

            create external table messages (

                msg_id          bigint,

                tstamp          string,

                text               string,

                user_id          bigint,

                user_name,   string

            )

            row format serde "org.apache.hadoop.hive.contrib.serde2.JsonSerde"

            with serdeproperties (

                "msg_id"="$.id",

                "tstamp"="$.created_at",

                "text"="$.text",

                "user_id"="$.user.id",

                "user_name"="$.user.name"

            )

            location '/data/messages';

    create table habase_stocks(key int, name string, price float)

    stored by 'org.apache.hive.hbase.HBaseStorageHandler'

    with serdeproperties("hbase.columns.mapping"=":key,stock:val")

    tblproperties("hbase.table.name"="stocks")；

grant create on database edsstuff to user edward；  

        create table autorized_part (key int, value string) partitioned by (ds string);   //创建一张分区表autorized_part

        alter table autorized_part  set tblproperties("partition_level_privilege"="true")；//为该表开启分区级别的授权

        alter table autorized_part  add partition (ds='3')； //为表新增一个分区，值为3

        revoke select on table autorized_part   partition(ds='3') from user edward；  //给edward赋予分区ds='3'的select 权限

      show locks；      //查询当前所有锁

      show locks 表名；   //查询某张表上的锁

      show locks 表名 partition (...)； //查询某个分区上的锁