一酷含、基本使用

1檬某、進(jìn)入hive

使用xshell遠(yuǎn)程登陸替梨，進(jìn)入linux系統(tǒng)钓试。任意位置輸入hive即可。

任意位置輸入hive

2副瀑、進(jìn)入hive數(shù)據(jù)庫(kù)進(jìn)行查詢操作

查看數(shù)據(jù)庫(kù)

"show databases;"

（ctrl+l清屏）

"use wt;";"show tables;"

"select * from a"

set hive.cli.print.current.db=true; #意思是顯示當(dāng)前數(shù)據(jù)庫(kù)

括號(hào)里顯示的是當(dāng)前庫(kù)

3弓熏、使用shell腳本進(jìn)行查詢

方法一：hive -e執(zhí)行sql語(yǔ)句 ； hive -v -e展示log信息

適用于當(dāng)想把sql語(yǔ)句自動(dòng)化執(zhí)行時(shí)糠睡，就可以寫成shell腳本的方法挽鞠，然后通過(guò)調(diào)度平臺(tái)調(diào)度shell腳本即可，是最常用的一種方法狈孔⌒湃希可以做邏輯判斷，利用變量均抽，輸入不同得到不同的輸出嫁赏。

創(chuàng)建一個(gè).sh文件用于編寫查詢hive數(shù)據(jù)庫(kù)的shell腳本

與進(jìn)入hive數(shù)據(jù)庫(kù)不同，查詢完結(jié)果后到忽，回到了linux系統(tǒng)

可以看到橄教，在.sh文件中清寇，如果再添加一個(gè)hive -v -e，則會(huì)顯示出執(zhí)行的sql語(yǔ)句

方法二：hive -f 執(zhí)行sql腳本

適用于執(zhí)行大規(guī)模的sql語(yǔ)句护蝶，當(dāng)時(shí)相比較hive -e的方法华烟，hive -f不能使用shell變量沒那么靈活，因此不是很常用持灰。

image.png

方法三：hive -i盔夜，執(zhí)行配置參數(shù)，但會(huì)進(jìn)入hive窗口堤魁。

frog3.conf中的語(yǔ)句如下

執(zhí)行conf文件

二喂链、sql語(yǔ)句

1、創(chuàng)建數(shù)據(jù)庫(kù)

create database frog_db;
drop database frog_db;
drop database frog_db cascade; #強(qiáng)制刪除非空數(shù)據(jù)庫(kù)妥泉，慎用Ｍ治ⅰ！

注意一定執(zhí)行了語(yǔ)句才算創(chuàng)建了數(shù)據(jù)庫(kù)盲链。

2蝇率、創(chuàng)建數(shù)據(jù)表

create table student(
id int comment 'id',
name string comment '姓名',
score decimal(30,6) comment '成績(jī)') ,
stored as textfile; 

• comment表示注釋內(nèi)容
• 最后一行指定表數(shù)據(jù)的存儲(chǔ)格式為textfile,存儲(chǔ)到hdfs路徑里面去。默認(rèn)的存儲(chǔ)方式也是textfile刽沾，這一行不寫也可以本慕。

3、查看hive數(shù)據(jù)表

查看表結(jié)構(gòu)

查看創(chuàng)建表的語(yǔ)句

這一行表示hdfs存儲(chǔ)路徑侧漓，hdfs路徑下的文件需要hadoop命令才看得到锅尘，和linux并不是一回事

• 自己指定建表格式：方便導(dǎo)入數(shù)據(jù)，用逗號(hào)分隔符分隔數(shù)據(jù)

use frog_db;
drop table student;
create table student(
  id int comment '識(shí)別碼',
  name string comment '姓名',
  score string comment '成績(jī)')
row format delimited fields terminated by ','
lines terminated by '\n' 
stored as textfile;

image.png

4布蔗、導(dǎo)入數(shù)據(jù)(從本地)：

image.png

load data local inpath '/home/froghd/student.txt' into table student;
load data local inpath '/home/froghd/student.txt' overwrite into table student;

第二行的意思是覆蓋掉student表中原有數(shù)據(jù)藤违。

• 注意（非常重要易混淆）：從本地系統(tǒng)導(dǎo)入數(shù)據(jù)和從hdfs文件系統(tǒng)導(dǎo)入數(shù)據(jù)用的命令都是load data，但是從本地系統(tǒng)導(dǎo)入數(shù)據(jù)要加local關(guān)鍵字何鸡，如果不加則是從hdfs文件系統(tǒng)導(dǎo)入數(shù)據(jù)纺弊。

參考教程： 一起學(xué)Hive——詳解四種導(dǎo)入數(shù)據(jù)的方式

注意：寫數(shù)據(jù)前要在linux中查看數(shù)據(jù)路徑。

ls和pwd分別確定目標(biāo)文件和所在的目錄

最好在查看一下目標(biāo)文件中是否有數(shù)據(jù)

5骡男、查看hdfs中的文件

• linux中使用hadoop語(yǔ)句

  
  
  在查看student表的創(chuàng)建語(yǔ)句中可以看到一個(gè)hfds路徑，我們可以在linux中使用hadoop語(yǔ)句查看

hadoop語(yǔ)句：'hadoop fs -ls hdfs具體路徑 '

可以看到傍睹，/user/下游五個(gè)子目錄

可以在 /user/tmo 目錄下面創(chuàng)建文件或文件夾

• hive中使用dfs語(yǔ)句
  在hive中查看hdfs文件：
  
  image.png
  
  hive中的命令執(zhí)行語(yǔ)句：
  dfs -ls hdfs:具體路徑

6隔盛、在linux中下載hdfs路徑下的文件

hdfs fs -get

7、在linux中刪除hdfs路徑下的文件

hadoop fs -rm

以此類推拾稳，可以查看hdfs下面的student.txt文件

hadoop fs -cat student.txt hdfs路徑

注意：使用hadoop命令刪除（-rm）和上傳（-put）的過(guò)程中吮炕，hive不需要重新在使用下列代碼，即可恢復(fù)hive數(shù)據(jù)庫(kù)中的數(shù)據(jù)访得。

load data local inpath '/home/froghd/student.txt' into table student;
load data local inpath '/home/froghd/student.txt' overwrite into table student;

8龙亲、內(nèi)部表

如果我們不指定 location 那么創(chuàng)建的表就是內(nèi)部表陕凹，如果指定了 location呢，那就是外部表鳄炉。其實(shí)就是把數(shù)據(jù)從 linux 上放到了 hdfs 路徑上

use frog_db;
drop table student;
create external table student(
  id int comment '識(shí)別碼',
  name string comment '姓名')
row format delimited fields terminated by ','
lines terminated by '\n' 
stored as textfile
location '/tmp/student';

刪除表時(shí)杜耙，內(nèi)部表中的數(shù)據(jù)和元數(shù)據(jù)將會(huì)被同時(shí)刪除，而外部表只刪除元數(shù)據(jù)拂盯，不刪除數(shù)據(jù)佑女。

9.分區(qū)表

在表目錄中為數(shù)據(jù)文件創(chuàng)建分區(qū)子目錄，以便于在查詢時(shí)谈竿，MR 程序可以針對(duì)分區(qū)子目錄中的數(shù)據(jù)進(jìn)行處理团驱，縮減讀取數(shù)據(jù)的范圍（不然就要全部讀取）空凸。

比如：網(wǎng)站每天產(chǎn)生的瀏覽記錄嚎花，瀏覽記錄應(yīng)該建一個(gè)表來(lái)存放，但是呀洲，有時(shí)候贩幻，我們可能只需要對(duì)某一天的瀏覽記錄進(jìn)行分析，這時(shí)两嘴，就可以將這個(gè)表建為分區(qū)表丛楚，每天的數(shù)據(jù)導(dǎo)入其中的一個(gè)分區(qū)，當(dāng)然憔辫，每日的分區(qū)目錄趣些，應(yīng)該有一個(gè)目錄名（分區(qū)字段）。

# partitioned by（day string）就是分區(qū)的依據(jù)
use frog_db;
drop table pv_log;
create table pv_log(
  ip string,
  url string,
  visit_time string)
partitioned by(day string)
row format delimited fields terminated by ',';
#建表語(yǔ)句贰您，只會(huì)建表目錄坏平，分區(qū)的目錄是在放數(shù)據(jù)的時(shí)候建立，先建表再放數(shù)據(jù)

數(shù)據(jù)

27.38.32.58,http://www.baidu.com.cn,2006-12-13 12:34:16
27.38.32.59,http://www.baidu.com.cn,2011-08-13 08:37:16
27.38.32.60,http://www.baidu.com.cn,2006-12-13 12:24:16
27.38.32.61,http://www.baidu.com.cn,2016-06-13 06:34:16
27.38.32.54,http://www.baidu.com.cn,2012-12-15 12:34:16
27.38.32.55,http://www.baidu.com.cn,2009-08-13 09:24:16
27.38.32.57,http://www.baidu.com.cn,2005-12-13 12:14:16
27.38.32.50,http://www.baidu.com.cn,2003-07-16 10:04:16
27.38.32.52,http://www.baidu.com.cn,2007-12-13 12:34:16

建立分區(qū)表

查看分區(qū)表字典

load data local path '/home/frogdata005/lee1/pv.log' into table pv_log partition (day=20150120)
#此處day=是隨便取名的锦亦，根據(jù)取消為分區(qū)表取名

在hive中導(dǎo)入數(shù)據(jù)

查看分區(qū)表數(shù)據(jù)

• 顯示分區(qū)數(shù)據(jù)

show partitions pv_log;

顯示分區(qū)數(shù)據(jù)

10舶替、cats建表語(yǔ)法

# 創(chuàng)建一個(gè)和 table1 一樣字段的 table2 表
create table table2 like table1

image.png

創(chuàng)建一個(gè)表類似與已有表，不僅字段一樣而且還帶有數(shù)據(jù)（不會(huì)有分區(qū)）杠园，查出來(lái)是什么字段名新表就是什么字段名

create table pv_log2
as 
select * from pv_log where visit_time>'2006-12-13';

這種查詢的方式創(chuàng)建表顾瞪，并不會(huì)形成分區(qū)

三、hive的常用函數(shù)

1抛蚁、case when

和mysql中的用法一致陈醒，根據(jù)when后面的邏輯判斷，給記錄打上then后面的標(biāo)簽瞧甩。

select 
  id, 
  name,
  case when score<=60 then '不及格'
       when score>60 and score <=80 then '良好'
       else '優(yōu)秀' end as grade
from student;

2钉跷、if 語(yǔ)句，類似一個(gè)三元表達(dá)式

同樣也是類似一個(gè)打標(biāo)簽的操作肚逸，根據(jù)if第一個(gè)參數(shù)的邏輯判斷爷辙，如果是就返回第二個(gè)位置參數(shù)'pass'彬坏，否則返回第三個(gè)位置參數(shù)'fail'。

select id膝晾，if (score>=60,'pass','fail') from student;
# 如果分?jǐn)?shù)大于60栓始，就返回 pass，不然就返回 fail

3玷犹、nvl函數(shù)：空值轉(zhuǎn)換函數(shù)

函數(shù)形式：nvl（expr1混滔，expr2）
適用于數(shù)字型、字符型和日期型歹颓，但是expr1和expr2的數(shù)據(jù)類型必須為相同類型坯屿。
作用：將查詢?yōu)镹ull值轉(zhuǎn)換為指定值

# 查詢插入一條 null 值數(shù)據(jù)
insert into student 
select
  5,
  'frog',
  null;

或者常規(guī)寫法：

insert into table student values(5,'lisi','null');

使用nvl（expr1，expr2）

# 如果 score 字段為空巍扛，就返回  0 领跛，不為空就是 score 本身
select score,nvl(score,0) from student;

更多函數(shù)

四、窗口函數(shù)

1撤奸、row_number() over()
HIVESQL中ROW_NUMBER() OVER語(yǔ)法以及示例

row_number() OVER (PARTITION BY COL1 ORDERBY COL2)表示:
根據(jù)COL1分組吠昭，在分組內(nèi)部根據(jù)COL2排序，而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號(hào)（該編號(hào)在組內(nèi)是連續(xù)并且唯一的)胧瓜。

eg：有如下數(shù)據(jù)矢棚，要查出每種性別中年齡最大的2條數(shù)據(jù)

在linux中創(chuàng)建數(shù)據(jù)文件（.txt）

1,18,a,male
2,19,a,male
3,22,a,female
4,16,b,female
5,30,b,male
6,26,b,female

創(chuàng)建表(hive)

use frog_db;
drop table userinfo;
create table userinfo(
  id string,
  age int,
  title string,
  sex string)
row format delimited fields terminated by ','
lines terminated by '\n' 
stored as textfile;

導(dǎo)入數(shù)據(jù)(hive)

load data local inpath '/home/xxx/xxx.txt' into table userinfo;

select
  id,
  age,
  title,
  sex,
  row_number() over(partition by sex order by age desc) as rn
from userinfo;

image.png

select * 
from(select
  id,
  age,
  title,
  sex,
  row_number() over(partition by sex order by age desc) as rn
from userinfo) as a
where a.rn<3;

控制排序組的數(shù)量，前兩個(gè)數(shù)據(jù)輸出

linux中使用vi命令存儲(chǔ)數(shù)據(jù)到.txtw文件中

A,2012-01,1000
A,2012-02,2030
A,2012-03,3600
A,2012-04,6008
A,2012-05,3000
B,2012-01,2000
B,2012-02,2300
B,2012-03,1800
B,2012-04,2000
B,2012-05,1300
B,2012-06,1600
B,2012-07,5000
C,2012-01,1020
C,2012-02,2000
C,2012-03,3200
C,2012-04,6000
C,2012-05,5300
C,2012-06,8800
C,2012-07,9000

hive中

use frog_db;
drop table saleinfo;
create table saleinfo(
  product_name string,
  month string,
  money string)
row format delimited fields terminated by ','
lines terminated by '\n' 
stored as textfile;

在hive中導(dǎo)入linux中的本地?cái)?shù)據(jù)

load data local inpath '/home/frog005/lee1/purchase_order.txt' overwrite into table saleinfo;

select
  product_name,
  month,
  money,
  sum(money) over(partition by product_name order by month) as all_money
from saleinfo;

輸出結(jié)果

最后對(duì)比下分組求和的情況：

select product_name,sum(money) as sum_money from saleinfo group by product_name;

輸出結(jié)果：

可見只是得到了分組的綜合，并沒有的得到函數(shù)的累加值钝满。如果在mysql中實(shí)現(xiàn)累加需要借助局部變量