Hive：用SQL對資料進行操作，匯入資料、清洗臟資料、統計資料訂單等等

① Hive 資料管理、內外表、安裝模式操作

② Hive：用SQL對資料進行操作，匯入資料、清洗臟資料、統計資料訂單

③ Hive：多種方式建表，需求操作

④ Hive：磁區原因、創建磁區、靜態磁區 、動態磁區

⑤ Hive：分桶的簡介、原理、應用、創建

⑥ Hive：優化 Reduce，查詢程序；判斷資料傾斜，MAPJOIN

1、準備資料

百度網盤鏈接：https://pan.baidu.com/s/1QDlf7SoGPWliagV2ettMOQ
提取碼：3jcn

2、了解資料

head -10 orders.csv

order_id:訂單號
user_id:用戶id
eval_set:訂單的行為（歷史產生的或者訓練所需要的）
order_number：用戶購買訂單的先后順序
order_dow：order day of week ,訂單在星期幾進行購買的（0-6）
order_hour_of_day：訂單在哪個小時段產生的（0-23）
days_since_prior_order：表示后一個訂單距離前一個訂單的相隔天數

head -10 order_products__train.csv

order_id:訂單號
product_id:商品ID
add_to_cart_order：加入購物車的位置
reordered：這個訂單是否重復購買（1 表示是 0 表示否）

head -10 products.csv （資料倉庫定位：商品維度表）

product_id：商品ID
product_name：商品名稱
aisle_id：貨架id
department_id:該商品資料屬于哪個品類，日用品，或者生活用品等

departments.csv（品類維度表）
department_id：部門id, 品類id
department: 品類名稱

department_id,department
1,frozen
2,other
3,bakery

order_products__prior.csv（用戶歷史行為資料）

order_id,product_id,add_to_cart_order,reordered
2,33120,1,1
2,28985,2,1
2,9327,3,0

3、將資料匯入hive

對于orders.csv，我們指導他是以‘,’ 作為欄位分割符，行與行之間資料是‘\n’是分割，

在hive資料庫創建orders表

create table badou.orders(
order_id string
,user_id string
,eval_set string
,order_number string
,order_dow string
,order_hour_of_day string
,days_since_prior_order string
)
row format delimited fields terminated by ','
lines terminated by '\n';

可以得知，創建orders表成功；接下來我們要把orders.csv資料加載到orders表中，

加載資料到hive，有兩種方式：

• 加載本地資料到Hive，overwrite 覆寫， into 追加

load data local inpath 'day3/orders.csv' overwrite into table orders;

• HDFS資料加載到Hive (沒有 local，要保證HDFS有資料)

load data inpath 'day3/orders.csv' 
overwrite into table orders;

我們這里選擇從本地加載資料，

load data local inpath 'day3/orders.csv' overwrite into table orders;

select * from orders limit 10;

可以發現，第一行資料是臟資料，

我們要自動顯示下每個資料的欄位名稱，
進入 hive-site.xml，在(master)進行配置：

vi hive-site.xml
<!--Hive第一行顯示列名稱-->
<property>
  <name>hive.cli.print.header</name>
   <value>true</value>
  <description>顯示列名稱</description>
</property>

exit; 退出hive，再重新進入hive，

use badou;
select * from orders limit 10;

創建trains表，加載order_products__train.csv也是如此操作，

4、如何清洗第一行的臟資料？

4.1 方式一：shell命令

思想：在load加載資料之前，針對例外資料進行處理，用 sed '1d' orders.csv，

測驗：
head -10 orders.csv > tmp.csv
cat tmp.csv
sed '1d' tmp.csv > tmp_res.csv
cat tmp_res.csv

4.2 方式二：HQL (hive sql)

insert overwrite table orders
select * from orders where order_id !='order_id';

4.3 方式三：更新表，過濾首行(個人建議用這個SQL命令)

alter table trains set tblproperties('skip.header.line.count'='1'); 

5、每個用戶有多少個訂單？ (分組)

我們知道，user_id為用戶ID，order_id為訂單ID，count(order_id) 統計訂單數，

select user_id, count(distinct order_id) as order_cnt
from orders group by user_id
limit 20;

distinct ：表示去重
as：把count的結果作為order_cnt

對order_cnt進行從大到小排序的話，

select user_id, count(distinct order_id) as order_cnt from orders
 group by user_id
 order by order_cnt desc
limit 10;

6、每個用戶一個訂單平均是多少商品？

先創建priors表，加載order_products__prior.csv資料，

create table priors(
order_id string,
product_id string,
add_to_cart_order string,
reordered string
)
row format delimited fields terminated by ','
lines terminated by '\n';

load data local inpath 'day3/order_products__prior.csv' 
overwrite into table priors;

過濾臟資料
alter table priors set tblproperties('skip.header.line.count'='1'); 

6.1 一個訂單有多少個商品？

在priors 表對訂單order_id分組，我們選擇訂單ID和產品數量，產品數量as pro_cnt，

select order_id,count(distinct product_id) as pro_cnt from priors 
group by order_id 
limit 10;

6.2 一個用戶有多少商品？

在orders表有用戶ID，priors表有商品ID，這兩個表同時有 訂單ID order_id，可以把表連接起來，把7.1的結果 as t，在t表我們就有 order_id 和 pro_cnt 兩個欄位，

(select order_id, count(distinct product_id) 
as pro_cnt from priors
    group by order_id
    limit 10000
) as t

select user_id,pro_cnt from orders as od
inner join t 
on od.order_id=t.order_id
limit 10;

完整SQL陳述句：

select od.user_id, t.pro_cnt from orders as od
inner join (
    select 
    order_id, count(distinct product_id) as pro_cnt
    from priors
    group by order_id
    limit 10000
) as t
on od.order_id=t.order_id
limit 10;

as 是可以省略的；
結果應該有用戶ID與商品數量 od.user_id, t.pro_cnt，

6.3 針對步驟6.2，進行用戶對應的商品數量 sum求和，

對商品數量t.pro_cnt進行求和，即sum(t.pro_cnt)，

【注意】: 使用聚合函式（count、sum、avg、max、min ）的時候要結合group by 進行使用，
從 7.2代碼進行修改：

select od.user_id, sum(t.pro_cnt) as sum_prods from orders od
inner join (
    select order_id, count(distinct product_id) as pro_cnt
    from priors
    group by order_id
    limit 10000
) as t
on od.order_id=t.order_id
group by od.user_id
limit 10;

我們，修改 sum(t.pro_cnt) as sum_prods 后，需要進行group by操作，即group by od.user_id，顯示結果有 od.user_id sum_prods，

6.4 一個用戶平均一個訂單有多少個商品？

即 用戶的商品數量 / 用戶的訂單數量 == sum(t.pro_cnt)/count(t.order_id) as sc_prod,
也可以用平均產品數量avg，avg(pro_cnt) as avg_prod

select od.user_id
,sum(t.pro_cnt)/count(t.order_id) as sc_prod
,avg(pro_cnt) as avg_prod 
from orders od inner join (
    select 
    order_id, count(distinct product_id) as pro_cnt
    from priors
    group by order_id
    limit 10000
) t
on od.order_id=t.order_id
group by od.user_id
limit 10;

6.5 每個用戶在一周中的購買訂單的分布?

在 orders表中，的列order_dow，代表購買訂單是在一周的星期幾，0-6 代表周一到周日，

查詢:
head -30 orders.csv

select * from orders limit 30;

思路： 要輸出的是用戶id，與星期幾一天的總訂單量，即需要sum(星期幾訂單)，判斷if order_dow='0'/ '1'/ '2'/ '3'/ '4'/ '5'/ '6' --> true 為1, false 為0，把結果sum，則得出：星期幾一天的總訂單量，例如：

user_id order_dow  
	1   	 0   sum=0+1=1
	1   	 0   sum=1+1=2
	1   	 1   sum=0+1=1
	2   	 1	 sum=0+1=1
由上面資料可知，用戶id為1，星期一(0),有2個訂單;星期二(1),有1個訂單;
用戶id為2，星期二(1),有1個訂單;

【注意】： 實際開發中，一定是最先開始使用小批量資料進行驗證，驗證代碼邏輯的正確性，然后全量跑！！，提高作業效率，

方式一：用case when 陳述句

select user_id
, sum(case when order_dow='0' then 1 else 0 end) dow0
, sum(case when order_dow='1' then 1 else 0 end) dow1
, sum(case when order_dow='2' then 1 else 0 end) dow2
, sum(case when order_dow='3' then 1 else 0 end) dow3
, sum(case when order_dow='4' then 1 else 0 end) dow4
, sum(case when order_dow='5' then 1 else 0 end) dow5
, sum(case when order_dow='6' then 1 else 0 end) dow6
from orders
where user_id in ('1','2','3')
group by user_id;

方式二：用 if 陳述句

select user_id
, sum(if(order_dow='0',1,0)) as dow0
, sum(if(order_dow='1',1,0)) dow1
, sum(if(order_dow='2',1,0)) dow2
, sum(if(order_dow='3',1,0)) dow3
, sum(if(order_dow='4',1,0)) dow4
, sum(if(order_dow='5',1,0)) dow5
, sum(if(order_dow='6',1,0)) dow6
from orders
where user_id in ('1','2','3')
group by user_id;

我們驗證資料：從上下圖可知: 用戶id為1: 星期一沒有訂單，星期二有3個訂單，從下圖可知驗證正確，，小批量驗證完成，我們可以取消 where陳述句，進行全部量操作，

6.6 查看 在12點時間段每個用戶購買了哪些商品？

分析：需要 用戶ID:user_id, 商品ID:product_id, 時間：order_hour_of_day;
orders表有 : order_id, user_id;
trains表有：order_id, product_id;
把兩個表連接起來 inner join，

SQL陳述句：

select od.user_id, tr.product_id from 
	orders od inner join trains tr
	on od.order_id=tr.order_id
	where order_hour_of_day = '12'
	limit 10;

由圖可知，在12點，‘21’ 用戶 購買了 ‘25740、12683、44632、10957、32645、16615’ 等商品，