Hive SQL匯總

創建數據庫
create database if not exists sopdm
comment 'this is test database'
with dbproperties('creator'='gxw','date'='2014-11-12') 
--數據庫鍵值對屬性信息
location '/my/preferred/directory';


顯示所有表
show tables ;
顯示表的描述信息
desc [extended,formatted] tablename;
顯示建表語句
show create table tablename;
刪除表
drop table tablename;

由一個表創建另一個表，相當于復制，表結構復制，數據沒復制
create table test3 like test2;

由其他表查詢創建表
create table test4 as select name,addr from test5;


stored as textfile
可以直接查看
stored as sequencefile
必須用hadoop fs -text查看
stored as rcfile
hive -service rcfilecat path 查看

stored as inputformat 'class'（自定義的）


加載jar包
shell窗口add jar path（作用范圍本shell）
加載到分布式緩存中供各個節點使用

或者直接拷貝到hive安裝目錄下的lib目錄


SerDe（hive使用SerDe讀、寫表的行）

讀寫順序：
HDFS文件-->InputFileFormat--> <key,value>-->Deserializer-->Row對象（供hive使用）
Row對象-->Serializer--> <key,value>-->OutputFileFormat-->HDFS文件

hive自帶RegexSerDe.class 正則表達式匹配每一行的數據

create table apachelog(
host STRING, 
identity STRING,
user STRING,
time STRING,
request STRING,
status STRING,
size STRING,
refer STRING,
agent STRING) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES (
"input.regex="([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([0-9]*) ([^ ]*) ([^ ]*)"
) stored AS TEXTFILE；


分區表（相當于表的子目錄）

create table tablename (name string) partitioned by (key type,...)


create external table employees(
name string,
salary float,
subordinates array<string>,
deductions map<string,float>,
address struct<street:string,city:string,state:string,zip:int)
)
partitioned by (dt string,type string)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/data';
數據格式：
wang  123  a1,a2,a3  k1:1,k2:2,k3:3  s1,s2,s3,4

查看分區：
show partitions employees
增加分區

alter table employees add if not exists partition(country='xxx'[,state='yyy'])
刪除分區
alter table employees drop if  exists partition(country='xxx'[,state='yyy'])

動態分區：
1.不需要為不同的分區添加不同的插入語句
2.分區不確定，需要從數據中獲取
參數：(動態分區前兩個必須開啟）
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrick;
//無限制模式，如果是strict，則必須有一個靜態分區，且放在最前面
set hive.exec.max.dynamic.partitions.pernode=10000;
//每個節點生成動態分區的最大個數
set hive.exec.max.dynamic.partitions=100000;
//每次sql查詢生成動態分區的最大個數
set hive.exec.max.created.files=150000;
//一個任務最多可以創建的文件數目
set dfs.datanode.max.xcievers=8182;
//限定一次最多打開的文件數
1. 創建分區表
create table d_part (
name string
)
partitioned by (value string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
2. 插入動態分區
insert overwrite table d_part partition(value)
select name,addr as value 
from testtext;


分桶
set hive.enforce.bucketing=true;

按id分桶
create table bucketed_user
(id string,
name string)
clustered by (id) sorted by(name)  into 4 buckets
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
分桶抽樣
select * from bucketed_user tablesample(bucket 1 out of 2 on id )
取一半的桶
優化
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

beeline底層使用的jdbc，命令行使用jdbc(可以遠程訪問）

hive -help
hive --help
hive --service -help

查詢結果寫到文件
hive -V -e "select name from testtext" > /home/data/result


hive命令行輸入list jar
顯示當前分布式緩存有哪些jar包也就是add jar命令加載的jar包）

命令行執行hql文件（類似于hive -f)，常用于設置初始化參數
source /home/data/hql/select_hql

配置變量
set val='';
hql使用hive變量
${hiveconf:val}
select * from testtext where name ='${hiveconf:val}';
env查看linux環境變量
HOME=/root
hql使用linux環境變量
select '${env:HOME}' from testtext;



1.內表數據加載(overwrite和into不能同時存在，只能存在一個）
(1) 創建表時加載
create table newtable as select col1,col2 from oldtable;
(2)創建表時指定數據位置(對location下的數據具有擁有權，刪除內表時也會刪除數據）

create table tablename() location '';
(3)本地數據加載
load data local inpath 'localpath' [overwrite] into table tablename;
(4)加載hdfs數據（移動數據，原來的數據移動到表的位置下）
load data  inpath 'hdfspath' [overwrite] into table tablename;

hive命令行執行linux shell命令在前面加個！
!ls /home/data

(5)通過查詢語句加載數據

insert  into table test_m select name,addr from testtext where name ='wer';
或者
from testtext insert  into table test_m select name,addr  where name ='wer';
或者
select name,addr from testtext  where name ='wer' insert  into table test_m ;

2.外部表數據加載
(1)創建表時指定數據位置
create external table tablename() location '';
(2)查詢插入，同內表
(3)使用hadoop命令拷貝數據到指定位置（hive的shell中執行和linux的shell執行）

3.分區表數據加載
（1）內部分區表數據加載類似于內表

（2）外部分區表數據加載方式類似于外表
注意：數據存放的路徑層次要和表的分區一致,并且表要增加相應分區才能查到數據

load data local inpath 'localpath' [overwrite] into table tablename partition(dt='20140905');

4.數據類型對應問題
Load數據，字段類型不能相互轉化時，查詢返回NULL；
select查詢輸入，字段類型不能相互轉化時，插入數據為NULL(文件保存是 \N)；
select查詢輸入數據，字段名稱可不一致，數據加載不做檢查，查詢時檢查



數據導出

導出到本地，默認分隔符^A
insert overwrite local directory '/home/data3' 
row format delimited fields terminated by '\t'
select name,addr from testtext; 

導出到hdfs，不支持row format delimited fields terminated by '\t'，只能采用默認分隔符是I
insert overwrite directory '/home/data3' 
select name,addr from testtext; 

表屬性操作

1.修改表名
alter table table_name rename to new_table_name；

2.修改列名
alter table tablename change column c1 c2 int comment 'xxx' after severity;
c1舊列，c2新列，int代表新列數據類型
after severity;可以把該列放到指定列的后面，或者使用'first'放到第一位

3.增加列(默認新增列放到最末尾）
alter  table tablename add column (c1 string comment 'xxxx',c2 string comment 'yyyy')；

4.修改tblproperties
alter table test set tblproperties('comment'='xxxx');

5.修改分隔符(分區表比較特殊）
方式一
alter table city set serdeproperties('field.delim'='\t');（對分區表原始數據無效，對新加分區有效）

方式二 對分區表原始數據也使用最新的分隔符
alter table city partitin(dt='20140908') set serdeproperties('field.delim'='\t');

6.修改location
alter table city [partition(...)] set location 'hdfs://master:9000/location' ;

7.內部表和外部表轉換
alter table test set tblproperties('EXTERNAL'='TRUE');內部表轉外部表
alter table test set tblproperties('EXTERNAL'='FALSE');外部表轉內部表

聚合操作
1.count計數
count(*)所有的字段不全為null，全為null不加1
count(1)不管記錄是啥，只要有這條記錄都加1
count(col)列不為空加1

2.sum求和
sum(可轉成數字的值)  返回bigint
sum(col)+cast(1 as bigint)

3.avg
avg(可轉成數字的值) 返回double

where條件在map端執行

group by是在reduce端執行 分組的列組合為key

having字句聚合操作之后執行判斷，也是在reduce端執行


groupby數據傾斜優化
hive.groupby.skewindata=true;（多起一個job）


join操作（普通join不支持不等值鏈接）
優化參數set hive.optimize.skewjoin=true;
樣例
select m.col as col,m.col2 as col2,n.col3 as col3
from 
(select col,col2
from test
where ...(map端執行)
) m
[left outer|right outer|left semi] join
n (右表)
on m.col=n.col
where condition (reduce端執行)

LEFT SEMI JOIN 是 IN/EXISTS 子查詢的一種更高效的實現

Hive 當前沒有實現 IN/EXISTS 子查詢，所以你可以用 LEFT SEMI JOIN 重寫你的子查詢語句。LEFT SEMI JOIN 的限制是， JOIN 子句中右邊的表只能在 

ON 子句中設置過濾條件，在 WHERE 子句、SELECT 子句或其他地方過濾都不行。

 SELECT a.key, a.value

 FROM a

 WHERE a.key in

  (SELECT b.key

  FROM b);

可以被重寫為：

  SELECT a.key, a.val

  FROM a LEFT SEMI JOIN b on (a.key = b.key)

order by全局排序，只有一個reduce
distribute by col分散數據，按col分散到不同的reduce
和sort by結合保證每個reduce輸出是有序的
 

union all（不去重）和union（去重）

hive只支持union all，子查詢不允許起別名
select col from 
(select a as col from t1 
union all
select b as col from t2
) tmp;

要求：
1.字段名字一樣
2.字段類型一樣
3.字段個數一樣
4.子表不能有別名
5.如果需要從合并之后的表中查詢數據，那么合并之后的表必須要有別名

hive創建索引表

hive> create index user_index on table user(id)     
        > as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'    
        > with deferred rebuild    
        > IN TABLE user_index_table;    
    hive> alter index user_index on user rebuild;    
    hive> select * from user_index_table limit 5;

直接用hadoop命令復制刪除hive存儲數據后，需要add partition或alter來同步源數據信息，否則drop表等操作時會查詢元數據metastore，查到metastore信息和hdfs信息不一致，會報錯。這個也可算是hive的bug，尚未修復，但也可以理解為初衷不建議直接操作hdfs數據。
目前，可以采用命令： 

1. MSCK REPAIR TABLE table_name;  

該命令會把沒添加進partition的數據，都增加對應的partition。同步源數據信息metadata。

Recover Partitions

Hive在metastore中存儲每個表的分區列表，如果新的分區加入HDFS后，metastore不會注意這些分區，除非

ALTER TABLE table_name ADD PARTITION

當然可以通過

MSCK REPAIR TABLE table_name;

類似EMR版本中的如下命令

ALTER TABLE table_name RECOVER PARTITIONS;