HIVE sql使用總結

一、常用資料庫指令

1、查詢有哪些資料庫 show databases

 2、查詢有哪些資料表：show tables

3、顯示所有函數： show functions

4、使用use databasename;可以切換到某個資料庫下

示例（切換到test資料庫）：use test

5、檢視目前資料庫：select current_database()

6、查詢資料表有哪些字段及字段詳情：describe tablename

示例（查詢staged_employees資料表）：describe staged_employees

可以簡寫為desc staged_employees

結果如下：

7、檢視指定資料庫裡有哪些資料表：SHOW TABLES IN DbName;

示例：檢視xiaoxiao資料庫下面有哪些表

 SHOW TABLES IN xiaoxiao

8、獲得表的建表語句：

示例（檢視建立emailtest這個表使用的語句方法）：show create table emailtest

9、檢視資料庫的描述資訊和檔案目錄位置路徑資訊

示例（檢視datetest資料庫的描述資訊和檔案目錄位置資訊）：describe database datetest;

二、建立資料庫

建立xiaoxiao資料庫：

create database xiaoxiao;

二、建立資料表

create   table staged_employees (

id int   comment 'id',

user_name   string comment 'user name')

三、删除資料庫

删除資料庫的時候，不允許删除有資料的資料庫，如果資料庫裡面有資料則會報錯。如果要忽略這些内容，則在後面增加CASCADE關鍵字，則忽略報錯，删除資料庫。

DROP DATABASE DbName CASCADE(可選);

DROP DATABASE IF EXISTS DbName CASCADE;

三、删除資料表

drop table staged_employees

四、删除資料表中所有内容

删除emaitest資料表中的所有内容。

insert overwrite table emailtest select * from emailtest where 1=0

五、更改表名

1、更改表名

-- 重命名表名 ALTER TABLE table_name RENAME TO new_table_name;

六、資料表添加字段：

alter table lemailtest add columns(time int comment 'now time')

七、HIVE統計函數

1、count(1)與count(*)得到的結果一緻，包含null值。count(字段)不計算null值

2、集合統計函數

2.1 個數統計函數: count

文法: count(*), count(expr), count(DISTINCT expr[, expr_.])

傳回值: int

說明: count(*)統計檢索出的行的個數，包括NULL值的行；count(expr)傳回指定字段的非空值的個數；count(DISTINCTexpr[, expr_.])傳回指定字段的不同的非空值的個數

舉例：

hive> select count(*) from lxw_dual;

20

hive> select count(distinct t) from lxw_dual;

10

2.2 總和統計函數: sum

文法: sum(col), sum(DISTINCT col)

傳回值: double

說明: sum(col)統計結果集中col的相加的結果；sum(DISTINCT col)統計結果中col不同值相加的結果

hive> select sum(t) from lxw_dual;

100

hive> select sum(distinct t) from lxw_dual;

70

七、插入資料到emailtest資料表中（追加資料到原有的資料表中）

讀取fx01資料表資料的一行，然後插入到emailtest資料表，對emailtest資料表原有的資料不會動的。

insert into table emailtest

select * from fx01 limit 1

八、插入資料倒emailtest資料表中（覆寫原有資料表的資料，相當于把原有資料表先清空，再寫入新的資料）

從fx01資料表讀取資料寫入到emailtest資料表，對原有emailtest資料表資料清空處理。

INSERT OVERWRITE TABLE emailtest

SELECT email,y,m,d

FROM fx01 where m=06 and d=19

九、關鍵詞比對——like

1、從fx01表中查找列 content中包含"制度"的行數資訊

Select * from fx01 where content like '%制度%'

十、根據某個關鍵字去比對，然後去設定新的關鍵詞case…when..方法

1、case用法一：CASE  條件判斷函數 CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

如果a等于b，那麼傳回c；如果a等于d，那麼傳回e；否則傳回f

select policy,case policy when 'abc' then '測試' else 'ccc' end as policy from fx01 limit 6

2、case用法二：

假如要用到case when又要用到like這樣的功能，即如果字元串包含‘國文’就怎麼怎麼樣，包含‘數學’就怎麼怎麼樣，包含‘英語’就怎麼怎麼樣，like是用于where中的，放在case when裡面是無效的，可以用instr()這個函數來查找這些字元出現的位置，代替like的功能，這樣寫就好呐。 

case when instr(t.str,’國文’) > 0 then 0 

when instr(t.str,’國文’) > 0 then 1 

when instr(t.str,’國文’) > 0 then 2 

else 3 end 

示例：

select t1.policy,case when instr(t1.policy,'信托') > 0 then '信托'

when instr(t1.policy,'張三') > 0  then '張三1'

when instr(t1.policy,'李四') > 0  then '李四1'

when instr(t1.policy,'小明') > 0  then '小明1' else '小紅' end from (select distinct policy from fx01 limit 6) t1

十一、order by——TOP N

hive實作topN，使用order by和limit組合方式實作。Order by 是進行降序排列，limit是選取多少。預設按升序排列，如果要按降序排列在末尾加上DESC，ASC是升序。

取排名TOP 5的資料。

select distinct company,count(company) as num from fx01 where m=05 group by company order by num DESC limit5

十二、資料表多表連接配接——join

多表連接配接——join兩個以上的表

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);

join支援left join（左連接配接）、right join（右連接配接）、full join（全連接配接）

1、兩表相關聯示例

select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company

2、三表相關聯示例

select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_fangxing.fangxing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company  join fx01_fangxing on fx01_lanjie.company=fx01_fangxing.company

十三、實作某一列字段關鍵詞統計——split+explode函數

0  stu表資料:

stu:

id             name

hello,you zm2008

hello,me zm2015

1 實作單詞計數:     (列轉行)   ---> split切分+explode(炸開)

1.0 資料拆分成數組

select split(id,',') from  stu;    得到數組

[hello,you]

[hello,me]

1.1 繼續将數組拆分(hive explode函數會将數組繼續拆分成單個字元)

select explode(split(id,','))  from stu;   窗體函數

hello

you

me

1.2 分組統計:

select t1.c1, count(1) from (select explode(split(id,',')) as c1  from stu)  t1 group by t1.c1;

hello 2

you 1

me 1

案例實作需求：統計“關鍵詞”這一列每個關鍵詞出現的次數，把數字去掉，隻統計中文關鍵詞

表名：Testtable

ID	keword	y	m	d
1	北京；廣州；深圳；貴州	2017	2	8
	重慶；河南			5
7	12345555		9

實作語句：