1.in 不支持子查詢 eg. select * from src where key in(select key from test);
支持查詢個(gè)數(shù) eg. select * from src where key in(1,2,3,4,5);
in 40000個(gè) 耗時(shí)25.766秒
in 80000個(gè) 耗時(shí)78.827秒

2.union all/union
不支持頂層的union all eg. select key from src UNION ALL select key from test;
支持select * from (select key from src union all select key from test)aa;
不支持 union
支持select distinct key from (select key from src union all select key from test)aa;

3.intersect 不支持

4.minus 不支持

5.except 不支持

6.inner join/join/left outer join/right outer join/full outer join/left semi join 都支持
left outer join/right outer join/full outer join 中間必須有outer
join是最簡(jiǎn)單的關(guān)聯(lián)操作，兩邊關(guān)聯(lián)只取交集;
left outer join是以左表驅(qū)動(dòng)，右表不存在的key均賦值為null；
right outer join是以右表驅(qū)動(dòng)，左表不存在的key均賦值為null；
full outer join全表關(guān)聯(lián)，將兩表完整的進(jìn)行笛卡爾積操作，左右表均可賦值為null;
left semi join最主要的使用場(chǎng)景就是解決exist in;
Hive不支持where子句中的子查詢，SQL常用的exist in子句在Hive中是不支持的
不支持子查詢 eg. select * from src aa where aa.key in(select bb.key from test bb);
可用以下兩種方式替換：
select * from src aa left outer join test bb on aa.key=bb.key where bb.key <> null;
select * from src aa left semi join test bb on aa.key=bb.key;
大多數(shù)情況下 JOIN ON 和 left semi on 是對(duì)等的
A,B兩表連接，如果B表存在重復(fù)數(shù)據(jù)
當(dāng)使用JOIN ON的時(shí)候，A,B表會(huì)關(guān)聯(lián)出兩條記錄，應(yīng)為ON上的條件符合； 
而是用LEFT SEMI JOIN 當(dāng)A表中的記錄，在B表上產(chǎn)生符合條件之后就返回，不會(huì)再繼續(xù)查找B表記錄了，
所以如果B表有重復(fù)，也不會(huì)產(chǎn)生重復(fù)的多條記錄。 
left outer join 支持子查詢 eg. select aa.* from src aa left outer join (select * from test111)bb on aa.key=bb.a;

7. hive四中數(shù)據(jù)導(dǎo)入方式
1）從本地文件系統(tǒng)中導(dǎo)入數(shù)據(jù)到Hive表
create table wyp(id int,name string) ROW FORMAT delimited fields terminated by '\t' STORED AS TEXTFILE;
load data local inpath 'wyp.txt' into table wyp;
2)從HDFS上導(dǎo)入數(shù)據(jù)到Hive表
[wyp@master /home/q/hadoop-2.2.0]$ bin/hadoop fs -cat /home/wyp/add.txt
hive> load data inpath '/home/wyp/add.txt' into table wyp;
3)從別的表中查詢出相應(yīng)的數(shù)據(jù)并導(dǎo)入到Hive表中
hive> create table test(
> id int, name string
> ,tel string)
> partitioned by
> (age int)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS TEXTFILE;

注：test表里面用age作為了分區(qū)字段，分區(qū)：在Hive中，表的每一個(gè)分區(qū)對(duì)應(yīng)表下的相應(yīng)目錄，所有分區(qū)的數(shù)據(jù)都是存儲(chǔ)在對(duì)應(yīng)的目錄中。
比如wyp表有dt和city兩個(gè)分區(qū)，則對(duì)應(yīng)dt=20131218city=BJ對(duì)應(yīng)表的目錄為/user/hive/warehouse/dt=20131218/city=BJ，
所有屬于這個(gè)分區(qū)的數(shù)據(jù)都存放在這個(gè)目錄中。

hive> insert into table test
> partition (age='25')
> select id, name, tel
> from wyp;

也可以在select語(yǔ)句里面通過(guò)使用分區(qū)值來(lái)動(dòng)態(tài)指明分區(qū)：
hive> set hive.exec.dynamic.partition.mode=nonstrict;
hive> insert into table test
> partition (age)
> select id, name,
> tel, age
> from wyp;

Hive也支持insert overwrite方式來(lái)插入數(shù)據(jù)
hive> insert overwrite table test
> PARTITION (age)
> select id, name, tel, age
> from wyp;

Hive還支持多表插入
hive> from wyp
> insert into table test
> partition(age)
> select id, name, tel, age
> insert into table test3
> select id, name
> where age>25;
4)在創(chuàng)建表的時(shí)候通過(guò)從別的表中查詢出相應(yīng)的記錄并插入到所創(chuàng)建的表中
hive> create table test4
> as
> select id, name, tel
> from wyp;

8.查看建表語(yǔ)句
hive> show create table test3;

9.表重命名
hive> ALTER TABLE events RENAME TO 3koobecaf; 

10.表增加列
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT); 

11.添加一列并增加列字段注釋 
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment'); 

12.刪除表
hive> DROP TABLE pokes; 

13.top n
hive> select * from test order by key limit 10;
14.創(chuàng)建數(shù)據(jù)庫(kù)
Create Database baseball;

14.alter table tablename  change oldColumn newColumn column_type 修改列的名稱和類型

alter table yangsy CHANGE product_no phone_no string

15.導(dǎo)入.sql文件中的sql

insert into table CI_CUSER_20141117154351522 select mainResult.PRODUCT_NO,dw_coclbl_m02_3848.L1_01_02_01,dw_coclbl_d01_3845.L2_01_01_04 from (select PRODUCT_NO from CI_CUSER_20141114203632267) mainResult left join DW_COCLBL_M02_201407 dw_coclbl_m02_3848 on mainResult.PRODUCT_NO = dw_coclbl_m02_3848.PRODUCT_NO left join DW_COCLBL_D01_20140515 dw_coclbl_d01_3845 on dw_coclbl_m02_3848.PRODUCT_NO = dw_coclbl_d01_3845.PRODUCT_NO

insert into CI_CUSER_20141117142123638 ( PRODUCT_NO,ATTR_COL_0000,ATTR_COL_0001) select mainResult.PRODUCT_NO,dw_coclbl_m02_3848.L1_01_02_01,dw_coclbl_m02_3848.L1_01_03_01 from (select PRODUCT_NO from CI_CUSER_20141114203632267) mainResult left join DW_COCLBL_M02_201407 dw_coclbl_m02_3848 on mainResult.PRODUCT_NO = dw_coclbl_m02_3848.PRODUCT_NO 


CREATE TABLE ci_cuser_yymmddhhmisstttttt_tmp(product_no string) row format serde 'com.bizo.hive.serde.csv.CSVSerde' ; 
LOAD DATA LOCAL INPATH '/home/ocdc/coc/yuli/test123.csv' OVERWRITE INTO TABLE test_yuli2;

創(chuàng)建支持CSV格式的testfile文件
CREATE TABLE test_yuli7 row format serde 'com.bizo.hive.serde.csv.CSVSerde' as select * from CI_CUSER_20150310162729786;

不依賴CSVSerde的jar包創(chuàng)建逗號(hào)分隔的表
"create table " +listName+ " ROW FORMAT DELIMITED FIELDS TERMINATED BY ','" +
" as select * from " + listName1;

create table aaaa ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE as select * from

ThriftServer 開(kāi)啟FAIR模式
SparkSQL Thrift Server 開(kāi)啟FAIR調(diào)度方式:
1. 修改$SPARK_HOME/conf/spark-defaults.conf,新增
2. spark.scheduler.mode FAIR
3. spark.scheduler.allocation.file /Users/tianyi/github/community/apache-spark/conf/fair-scheduler.xml
4. 修改$SPARK_HOME/conf/fair-scheduler.xml(或新增該文件), 編輯如下格式內(nèi)容
5. <?xml version="1.0"?>
6. <allocations>
7. <pool name="production">
8. <schedulingMode>FAIR</schedulingMode>
9. <!-- weight表示兩個(gè)隊(duì)列在minShare相同的情況下,可以使用資源的比例 -->
10. <weight>1</weight>
11. <!-- minShare表示優(yōu)先保證的資源數(shù) -->
12. <minShare>2</minShare>
13. </pool>
14. <pool name="test">
15. <schedulingMode>FIFO</schedulingMode>
16. <weight>2</weight>
17. <minShare>3</minShare>
18. </pool>
19. </allocations>
20. 重啟Thrift Server
21. 執(zhí)行SQL前,執(zhí)行 
22. set spark.sql.thriftserver.scheduler.pool=指定的隊(duì)列名

等操作完了 create table yangsy555 like CI_CUSER_YYMMDDHHMISSTTTTTT 然后insert into yangsy555 select * from yangsy555

創(chuàng)建一個(gè)自增序列表，使用row_number() over()為表增加序列號(hào) 以供分頁(yè)查詢

Sparksql的解析與Hiveql的解析的執(zhí)行流程: