Hive文法相關

一些常用的hive相關的文法，詳細請檢視官網：https://cwiki.apache.org/confluence/display/Hive

DDL

1. create

   建立資料庫：create database [if not exists] 資料庫名;

   建立表：create table [external] 表名

   (column1 type1,

   column2 type2,

   …)

   row format delimited fields terminated by ‘列分隔符’

   stored as textfile/rcfile；

   如果是内部表，預設存儲在/user/hive/warehouse下，如果是external表，如果不想讓其存放在hive預設目錄下的話，可使用location指定需要在hdfs上的存儲路徑，如果此時再把需要加載的資料放在external表在hdfs上的存儲目錄下的話，資料将會直接加載到external表中，不需要再load加載資料。

   複制表結構,隻能複制全部表結構：create table 新表 like 舊表;

   複制表結構及表資料，可選擇部分字段複制：create table 新表 as select * from 舊表;

2. drop

   删除資料庫(cascade強制删除資料庫)：drop database 資料庫名 [cascade] ;

   删除表：drop table [if exists]表名；

3. alter

   修改表名：alter table table_name rename to new_table_name;

4. truncate

   清空表資料：truncate table 表名;

5. show

   檢視資料庫：show databases；

   檢視目前資料庫下所有表：show tables；

   檢視某個表的建表語句：show create table 表名；

   檢視某個表的字段：show columns from 表名;

6. describe

   檢視某表的所有字段及字段類型：desc 表名；

   desc formated 表名;

DML

1.load

加載表資料：load data [local] inpath ‘本地路徑資料/hdfs路徑資料’ [overwrite] into table 表名;

2.insert

插入表資料(該表1必須先建立)：insert into table 表1 select column1,column2…| * from 表2;

導出表中的資料到本地或者hdfs：

insert overwrite [local] directory ‘本地路徑/hdfs路徑’

[row format delimited fields terminated by ‘,’]

select column1,column2…| * from 表名;

3.update/

4.delete/

5.merge

6.import

7.export,

8.explain plan

關于hive中order by，sort by，cluster by，distribute by的差別？

1.order by是全局排序，當使用order by的時候reduce task的個數為1個即最終輸出檔案也會隻有一個，那麼此時就是全局排序,如果此時hive為嚴格模式的話需要加limit。 set hive.mapred.mode=xx;

2.sort by是局部排序，比如說設定reduce task的個數為3個的時候即最終輸出的檔案也會有三個，那麼此時使用sort by即可實作每個檔案内的資料是有序的，不能保證全局有序，但是如果reduce task的個數為 一個的時候，那麼此時也相當于全局有序。。set mapred.reduce.tasks=xx;預設-1 表示1個。

eg:首先将reduce的個數設定為3個，然後使用sort by實作三個輸出檔案内分别升序，最終結果将會生成三個檔案，并且通過檢視，每個檔案内将會按照sal字段升序

hive (hwzhdb)> set mapred.reduce.tasks=3;
    hive (hwzhdb)> insert overwrite local directory './data/sortby' 
                 > row format delimited fields terminated by ','
                 > select * from emp sort by sal ;
                .....執行mr....
    [[email protected] data]$ cd sortby/
    [[email protected] sortby]$ ll
    total 12
    -rw-r--r--. 1 hadoop hadoop 367 Jul 20 14:34 000000_0
    -rw-r--r--. 1 hadoop hadoop 361 Jul 20 14:34 000001_0
    -rw-r--r--. 1 hadoop hadoop 116 Jul 20 14:34 000002_0
           

但是這三個檔案是根據哪個字段進行劃分的呢？

其實是因為hive對單獨使用sort by的這種有個算法，是随機生成的這三個檔案，并不是根據某個字段值進行分區的，是以針對同一份資料集，無論你執行多少次這個語句，最終生成的三個檔案中的内容都是一樣的，不會改變

3.distribute by 不是排序，而是可以按照一定的規則将資料分發(此處的分發規則是%reduce的個數然後根據餘數将資料分發到同一個reduce上)到不同的reduce上，然後可以結合使用sort by進行局部排序

hive (hwzhdb)> set mapred.reduce.tasks=3;
 hive (hwzhdb)> insert overwrite local directory './data/distributeByAndSortBy' 
                     > row format delimited fields terminated by ','
                     > select * from emp distribute by deptno sort by sal ;
  這種方式最終也是生成三個檔案，隻不過跟單獨使用sort by不同的是這三個檔案是根據對
  deptno進行hash，然後%reduce的個數，将資料分散到三個檔案中的。                   
           

4.cluster by 是distribute by+sort by的結合，就是說如果你的distribute by後面的字段跟sort by後面的字段一緻的話就可以簡寫為cluster by，但是這種方式隻能倒序，不能指定asc或者desc

hive内置函數相關？

檢視内置函數指令：show functions;

檢視某一個函數說明：desc function 方法名;

檢視某一個函數說明并且帶有example的：desc function extended 方法名;

類Oracle建立一個dual表，供我們友善使用
hive (hwzhdb)> create table dual(x string);
OK
Time taken: 0.857 seconds
hive (hwzhdb)> insert into table dual values('');
			...mr...

時間函數相關：yyyy-MM-dd HH:mm:ss
current_date：2019-07-20

current_timestamp：2019-07-20 16:40:05.975

unix_timestamp：傳入一個date，然後指定時間格式，傳回一個timestamp
eg：
hive (hwzhdb)> select unix_timestamp('2019-7-21 11:26','yyyy-MM-dd HH:mm') from dual;
OK
_c0
1563679560

from_unixtime:傳入一個timestamp，然後指定要傳回的date格式，傳回一個date
eg：
hive (hwzhdb)> select from_unixtime(1563679560,'yyyy-MM-dd HH:mm:ss') from dual;
OK
_c0
2019-07-21 11:26:00

-----------------
date_add：增加/減少天數
add_months：增加/減少月份
-----------------
year：傳回一個date的年份
month：傳回一個date的月份
day：傳回一個date的天
hour：傳回一個date的小時
minute：傳回一個date的分鐘
second：傳回一個date的秒
           

數值相關的：

abs：取絕對值

ceil：對于小數，向上取整

floor：對于小數，向下取整

round：對于小數，四舍五入，可以傳入第二個參數設定精度(即小數點後保留幾位)

least：傳入多個數字，傳回其中最小的一個數字

greatest：傳入多個數字，傳回其中最大的一個數字

字元串相關的：

substr：字元串截取

eg：

SELECT substr(‘Facebook’, 5) FROM dual; --從正數第五個一直截取到末尾

‘book’

SELECT substr(‘Facebook’, -5) FROM dual; --從倒數第五個的位置一直截取到末尾

‘ebook’

SELECT substr(‘Facebook’, 5, 1) FROM dual; --從正數第五個的位置，截取一個

‘b’

concat：字元串拼接

eg：

SELECT concat(‘abc’,‘def’) FROM dual;

‘abcdef’

concat_ws:字元串拼接，能夠指定拼接符

eg：

SELECT concat_ws(’.’, ‘www’, array(‘facebook’, ‘com’)) FROM dual;

‘www.facebook.com’

其它一些常用函數：

1.to_date:将字元串日期轉換為date格式

2.cast：類型轉換函數，類似java中Inter.parse,可以将字元串數值轉為數值類型

3.split：字元串切分函數

eg： SELECT split(‘oneAtwoBthreeC’, ‘[ABC]’) FROM dual;

[“one”, “two”, “three”]

4.explode：行轉列函數，一行變多行，可以和split結合使用實作hql的wc功能 ，

> eg： 造一份資料模拟hql實作wc
 > [[email protected] data]$ cat wc.txt  
 > yuwen#shuxue#yingyu 
 > yuwen#shuxue#shuxue 
 > yingyu
  hive (hwzhdb)> create table wc(
              > subjects string
              > );
 hive (hwzhdb)> load data local inpath '/home/hadoop/data/wc.txt' into table wc;
 hive (hwzhdb)> select tmp.word,count(1)  conum
 				from
                 (
                 select explode(split(subjects,'#')) as word 
                 from wc
                 ) tmp 
                 group by tmp.word
                 order by conum desc;
         result:
         tmp.word        conum
         shuxue  3
         yuwen   2
         yingyu  2