簡(jiǎn)介

項(xiàng)目介紹： Adventure Works Cycles 是一家銷售自行車及相關(guān)零件的公司，通過(guò)現(xiàn)有數(shù)據(jù)監(jiān)控銷售情況，獲取最新商品銷售趨勢(shì)及區(qū)域分布情況進(jìn)行可視化展示周循，為業(yè)務(wù)決策提供洞察舅踪，實(shí)現(xiàn)高效經(jīng)營(yíng)。
負(fù)責(zé)內(nèi)容：1. 根據(jù)實(shí)際業(yè)務(wù)需要肮韧，使用python+SQL語(yǔ)句對(duì)ODS基礎(chǔ)層數(shù)據(jù)聚合加工，提取關(guān)鍵分析指標(biāo)旺订，并加載到DW匯總層弄企，最終形成三張聚合表（訂單日期聚合表、同比數(shù)據(jù)表和時(shí)間地區(qū)產(chǎn)品聚合表）区拳。

2. 在Linux環(huán)境下使用shell命令后臺(tái)運(yùn)行腳本拘领，實(shí)現(xiàn)BI看板所需三張聚合數(shù)據(jù)表按邏輯順序的每日自動(dòng)更新。
3. 使用PowerBI連接MySQL數(shù)據(jù)庫(kù)實(shí)現(xiàn)Sales Dashboard樱调，包括銷售情況约素、時(shí)間趨勢(shì)、區(qū)域分布指標(biāo)的可視化監(jiān)控笆凌。主要從時(shí)間圣猎、地區(qū)和產(chǎn)品維度，展示訂單量乞而、銷售額送悔、客單價(jià)、銷售產(chǎn)品結(jié)構(gòu)和銷售預(yù)期達(dá)成率等關(guān)鍵指標(biāo)爪模。

可視化看板截圖

image.png

image.png

image.png

一欠啤、背景介紹

Adventure Works Cycles是基于微軟SQL Server中AdventureWorks 示例數(shù)據(jù)庫(kù)所構(gòu)建的虛擬公司，它是一家大型跨國(guó)制造公司屋灌。該公司向北美洁段，歐洲和亞洲商業(yè)市場(chǎng)生產(chǎn)并銷售金屬和復(fù)合材料自行車。盡管其基本業(yè)務(wù)位于華盛頓州的博塞爾市共郭，擁有290名員工祠丝，但幾個(gè)區(qū)域銷售團(tuán)隊(duì)遍布整個(gè)市場(chǎng)疾呻。

二、分析目的：

為滿足業(yè)務(wù)團(tuán)隊(duì)自主分析纽疟，并基于銷售情況快速做出決策的需求罐韩。要求數(shù)據(jù)部門和業(yè)務(wù)部門溝通需求的數(shù)據(jù)指標(biāo)，考慮實(shí)現(xiàn)數(shù)據(jù)自動(dòng)聚合更新并輸出可視化看板污朽。

三散吵、分析框架

image.png

四、分析過(guò)程

1. 數(shù)據(jù)指標(biāo)梳理與理解

目的：探索現(xiàn)有數(shù)據(jù)庫(kù)包含哪些表和字段蟆肆，結(jié)合業(yè)務(wù)需求矾睦，梳理可分析的指標(biāo)。

(1) 數(shù)據(jù)庫(kù)探索

數(shù)據(jù)庫(kù)共有6張表炎功，且主要可以分成三類枚冗，一類是ods開(kāi)頭的明細(xì)表（訂單明細(xì)表、每日新增用戶表）蛇损，一類是dw開(kāi)頭的聚合表（時(shí)間地區(qū)產(chǎn)品聚合表赁温、每日環(huán)比表和當(dāng)日維度表），還有一種dim開(kāi)頭的維度表（日期維度表）淤齐。

(2) 數(shù)據(jù)梳理

根據(jù)業(yè)務(wù)理解并與相關(guān)同事溝通了解股囊，對(duì)項(xiàng)目會(huì)用到的數(shù)據(jù)表和字段進(jìn)行梳理，形成數(shù)據(jù)字典更啄。具體字段內(nèi)容如下：

adventure數(shù)據(jù)字典.png

(3) 指標(biāo)體系

分析所需指標(biāo)：

1. 時(shí)間維度——年稚疹、季度、月祭务、昨日内狗、今日
2. 地區(qū)維度——區(qū)域、省份义锥、城市
3. 訂單產(chǎn)品維度——訂單日期柳沙、客戶編號(hào)、產(chǎn)品類別拌倍、產(chǎn)品子類赂鲤、產(chǎn)品名、產(chǎn)品單價(jià)贰拿、產(chǎn)品銷售數(shù)量
4. 各類同比（如消費(fèi)金額、定單量熄云、單均價(jià)等）及占比值（如產(chǎn)品類別）

2. 利用python進(jìn)行底層數(shù)據(jù)聚合

根據(jù)業(yè)務(wù)需要膨更，接下來(lái)對(duì)基礎(chǔ)層數(shù)據(jù)聚合加工，提取關(guān)鍵分析指標(biāo)缴允，形成三張聚合表（訂單日期聚合表荚守、同比數(shù)據(jù)表和新的時(shí)間地區(qū)產(chǎn)品聚合表）珍德。具體處理邏輯梳理如下：

Adventure項(xiàng)目.jpg

(1) 訂單日期聚合表

從Mysql數(shù)據(jù)庫(kù)中獲取ods_sales_orders訂單明細(xì)表并按日期分組聚合，計(jì)算每日總金額矗漾、訂單量和客單價(jià)锈候。讀取dim_date_df日期維度表，與上表結(jié)合敞贡，得到dw_order_by_day訂單日期聚合表泵琳。 部分代碼如圖：

image.png

(2) 同比數(shù)據(jù)表

從Mysql數(shù)據(jù)庫(kù)中獲取上一步生成的dw_order_by_day訂單日期聚合表，對(duì)當(dāng)日/昨天/當(dāng)月/當(dāng)季/當(dāng)年標(biāo)簽進(jìn)行判斷誊役，計(jì)算各時(shí)間維度下與去年同期的總金額获列、訂單量和客單價(jià)同比數(shù)據(jù)。得到dw_amount_diff當(dāng)日維度表蛔垢，部分代碼如圖：

image.png

image.png

(3) 當(dāng)日時(shí)間地區(qū)產(chǎn)品聚合表

讀取并聚合當(dāng)日的ods_sales_orders訂單明細(xì)表击孩、ods_customer每日新增用戶表和dim_date_df日期維度表，形成dw_customer_order當(dāng)日時(shí)間地區(qū)產(chǎn)品聚合表鹏漆。部分代碼如圖：

image.png

3. 在Linux服務(wù)器上部署代碼巩梢，讓其每日自動(dòng)更新

這里使用python的schedule模塊實(shí)現(xiàn)數(shù)據(jù)自動(dòng)更新，部分代碼實(shí)現(xiàn)如下：

image.png

接著艺玲，將寫好的定時(shí)任務(wù)腳本傳到Linux服務(wù)器中括蝠，并掛到后臺(tái)運(yùn)行。

nohup python3 -u schedule_job_test.py > schedule_job_test.log 2>&1 &

此時(shí)定時(shí)文件被運(yùn)行板驳，會(huì)返回一個(gè)任務(wù)ID又跛。

image.png

使用以下語(yǔ)句查看新建的任務(wù)ID，確定其是否成功在后臺(tái)運(yùn)行若治。

ps aux| grep schedule_job_test.py 

image.png

查看任務(wù)日志慨蓝。

cat schedule_job_test.log

image.png

說(shuō)明該任務(wù)在后臺(tái)自動(dòng)運(yùn)行，并每十秒輸出一段日志端幼。
完成以上操作后只要云服務(wù)器保持開(kāi)啟狀態(tài)礼烈，后臺(tái)就會(huì)每日自動(dòng)更新所需表。需要中止后臺(tái)任務(wù)可使用命令——kill 任務(wù)ID婆跑。

4. 連接Power bi 部署展示

使用Power BI鏈接新生成的三張聚合表此熬，并通過(guò)web發(fā)布形成鏈接。
可視化看板鏈接

image.png

image.png

image.png

5. 實(shí)現(xiàn)hive數(shù)據(jù)倉(cāng)庫(kù)以及處理日常需求

隨著數(shù)據(jù)量增大滑进，使用python進(jìn)行聚合變的困難起來(lái)犀忱，為解決大數(shù)據(jù)場(chǎng)景問(wèn)題，以下引入hive代替python進(jìn)行數(shù)據(jù)聚合扶关。主要流程為使用sqoop抽取數(shù)據(jù)進(jìn)入hive數(shù)據(jù)庫(kù)阴汇，在hive中進(jìn)行數(shù)據(jù)聚合，Linux服務(wù)器部署自動(dòng)更新节槐，使用sqoop將更新后數(shù)據(jù)傳輸回Mysql搀庶。

Sqoop：SQL-to-Hadoop

把關(guān)系型數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入到 Hadoop 系統(tǒng) ( 如 HDFS拐纱、HBase 和 Hive) 中；把數(shù)據(jù)從 Hadoop 系統(tǒng)里抽取并導(dǎo)出到關(guān)系型數(shù)據(jù)庫(kù)里哥倔。利用MapReduce 加快數(shù)據(jù)傳輸速度秸架。

(1) Sqoop抽取mysql數(shù)據(jù)到hive

import sqoop
步驟1：Sqoop與數(shù)據(jù)庫(kù)Server通信，獲取數(shù)據(jù)庫(kù)表的元數(shù)據(jù)信息咆蒿；
步驟2：Sqoop啟動(dòng)一個(gè)Map-Only的MR作業(yè)东抹，利用元數(shù)據(jù)信息并行將數(shù)據(jù)寫入Hadoop。

把dim_date_df日期維度表蜡秽、ods_customer每日新增用戶表和ods_sales_orders訂單明細(xì)表數(shù)據(jù)通過(guò)sqoop工具遷移到Hive的ods庫(kù)中府阀。以dim_date_df日期維度表為例編寫shell腳本，其他兩張表同理：

hive -e "drop table if exists ods.dim_date_df" # 刪除hive原有的舊表
sqoop import \ 
--hive-import \ 
--connect jdbc:mysql:///主機(jī)名:端口/目錄名 \  
--driver com.mysql.jdbc.Driver \ # Hadoop根目錄
--username xxxxx \   #用戶名
--password xxxxx \      #密碼
--query \  ## 構(gòu)建表達(dá)式<sql語(yǔ)句 >執(zhí)行
"select * from dim_date_df where "'$CONDITIONS'" " \
--fetch-size 50000 \  ## 一次從數(shù)據(jù)庫(kù)讀取 n 個(gè)實(shí)例芽突，即n條數(shù)據(jù)
--hive-table ods.dim_date_df \ ## 創(chuàng)建dim_date_df表
--hive-drop-import-delims \  ## 在導(dǎo)入數(shù)據(jù)到hive時(shí)试浙，去掉數(shù)據(jù)中的\r\n\013\010這樣的字符
--delete-target-dir \  ## 如果目標(biāo)文件已存在就把它刪除
--target-dir /user/hadoop/sqoop/dim_date_df \  ## 數(shù)據(jù)文件被放在了hive的默認(rèn)/user/hadoop/sqoop/dim_date_df下面
-m 1  ## 遷移過(guò)程使用1個(gè)map（開(kāi)啟一個(gè)線程）

image.png

(2) 建立數(shù)據(jù)倉(cāng)庫(kù)，做聚合數(shù)據(jù)處理

編寫Hive SQL寞蚌，放到shell腳本中運(yùn)行田巴，這里以聚合生成dw_order_by_day每日環(huán)比表為例，同理生成其他兩張表挟秤。

hive -e "drop table if exists ods.dw_order_by_day"
hive -e "
CREATE TABLE ods.dw_order_by_day(
  create_date string,
  is_current_year bigint,
  is_last_year bigint,
  is_yesterday bigint,
  is_today bigint,
  is_current_month bigint,
  is_current_quarter bigint,
  sum_amount double,
  order_count bigint)
"
hive -e "
with dim_date as
(select create_date,
            is_current_year,
            is_last_year,
            is_yesterday,
            is_today,
            is_current_month,
            is_current_quarter
            from ods.dim_date_df),
sum_day as
(select create_date,
        sum(unit_price) as sum_amount,
        count(customer_key) as order_count
        from ods.ods_sales_orders
        group by create_date)
insert into ods.dw_order_by_day
    select b.create_date,
    b.is_current_year,
    b.is_last_year,
    b.is_yesterday,
    b.is_today,
    b.is_current_month,
    b.is_current_quarter,
    a.sum_amount,
    a.order_count
    from sum_day as a
    inner join dim_date as b
    on a.create_date=b.create_date
"

(3) Sqoop從hive導(dǎo)出數(shù)據(jù)到mysql

export sqoop
將數(shù)據(jù)從Hadoop 導(dǎo)入關(guān)系型數(shù)據(jù)庫(kù)導(dǎo)中壹哺。
步驟1：Sqoop與數(shù)據(jù)庫(kù)Server通信，獲取數(shù)據(jù)庫(kù)表的元數(shù)據(jù)信息艘刚；
步驟2：并行導(dǎo)入數(shù)據(jù) ：將Hadoop 上文件劃分成若干個(gè)split.每個(gè)split 由一個(gè)Map Task 進(jìn)行數(shù)據(jù)導(dǎo)入管宵。

編寫Hive SQL，放到shell腳本中運(yùn)行攀甚，這里將dw_order_by_day每日環(huán)比表的數(shù)據(jù)抽取回mysql數(shù)據(jù)庫(kù)為例箩朴，同理生成其他兩張表。

sqoop export \
--connect "jdbc:mysql://主機(jī)名:端口/目錄名" \
--username xxxxx \ ##數(shù)據(jù)庫(kù)賬號(hào) 
--password xxxxx \ ##數(shù)據(jù)庫(kù)密碼
--table dw_order_by_day \
--export-dir /user/hive/warehouse/ods.db/dw_order_by_day \
--input-null-string "\\\\N" \
--input-null-non-string "\\\\N"  \
--input-fields-terminated-by "\001"  \
--input-lines-terminated-by "\\n"  \
-m 1

(4) Linux服務(wù)器定時(shí)更新部署

linux的定時(shí)任務(wù)使用crontab文件來(lái)實(shí)現(xiàn)

1. 編寫job_shedule.sh文件秋度，按執(zhí)行順序添加文件

sh /home/xxx/sqoop_dim_date_df.sh
sh /home/xxx/sqoop_ods_customer.sh
sh /home/xxx/sqoop_ods_sales_order.sh
sh /home/xxx/create_dw_order_by_day.sh
sh /home/xxx/create_dw_amount_diff.sh
sh /home/xxx/create_dw_customer_order.sh
sh /home/xxx/create_dw_order_by_day.sh
sh /home/xxx/create_dw_amount_diff.sh
sh /home/xxx/create_dw_customer_order.sh

2. 添加定時(shí)任務(wù)炸庞，設(shè)定每天早上6點(diǎn)執(zhí)行

編輯crontab 文件

vi /etc/crontab

添加定時(shí)任務(wù)：

0 6 * * * sh home/xxx/job_schedule.sh > /dev/null 2>&1  ##避免當(dāng)程序在指定的時(shí)間執(zhí)行后，系統(tǒng)發(fā)一封郵件給當(dāng)前的用戶荚斯，顯示該程序執(zhí)行的內(nèi)容