Apache Hive LOGO

Hive實戰－求北上廣深等地知名商場的日均到訪

# 輸入數據

有商場數據如下：

id name

1 北京-華聯商廈

2 北京-朝陽大悅城

3 武漢-武商摩爾城

4 武漢-光谷世界城

5 青島-海信廣場

6 重慶-龍湖天街

7 上海-晶品購物中心

8 深圳-龍華九方購物中心

9 廣州-中港皮具城

......

# 數據傳到能執行hdfs命令所在服務器

scp id_name.txt zyl@hive_svc:/home/zyl/data

# 選擇Hive所使用庫

use zyl_product;

# 創建外部分區表,添加分區

create external table tmp_id_name_tab (mallid string, mallname string)

PARTITIONED BY(dt String)

row format delimited fields terminated by '\t'

stored as textfile location '/user/zyl/tmp_id_name_tab/init_meta';

ALTER TABLE tmp_id_name_tab

ADD PARTITION (dt='zsy_20171018_01')

location '/user/zyl/tmp_id_name_tab/zsy_20171018/01';

# 數據傳到Hive外部表分區zsy_20171018_01所在的HDFS上

hdfs dfs -put id_name.txt /user/zyl/tmp_id_name_tab/zsy_20171018/01

# 查看是否有數據(要確保有數據)

select * from tmp_id_name_tab a where a.dt = 'zsy_20171018_01' limit 10;

# 商場日均平均數

select mallid, mallname, ceil(avg(cnt)) avg_ceil from

(

select mallid, mallname, day, count(1) cnt from

(

select /*+mapjoin(b)*/mallid, mallname, day, personid from zyl_product.mall_probe_data a

join

(select mallid, mallname from zyl_product.tmp_id_name_tab a where a.dt = 'zsy_20171018_01') b

on (a.buildingid = b.id)

) a group by id, mallname, day

) a

group by id, mallname

代碼截圖

# 簡單說明

1.表簡要介紹

mall_probe_data表是基礎數據,里面有商場id、persoinid、日期等

2.SQL思路介紹

先用mapjoin在內存中裝載id映射表(zyl_product庫tmp_id_name_tab表),然后求商場每天的去重到訪數,最后求商場日均到訪數并向上取整.

難道沒人發現bug嗎?

需要去重按天對mac去重,加上group by mallid, mallname, day, personid

修復版如下：

# 商場日均平均數

select mallid, mallname, ceil(avg(cnt)) avg_ceil from

(

select mallid, mallname, day, count(1) cnt from

(

select /*+mapjoin(b)*/mallid, mallname, day, personid from zyl_product.mall_probe_data a

join

(select mallid, mallname from zyl_product.tmp_id_name_tab a where a.dt = 'zsy_20171018_01') b

on (a.buildingid = b.id)

group by mallid, mallname, day, personid

) a group by id, mallname, day

) a

group by id, mallname