大數(shù)據(jù)存儲(chǔ)-廣電用戶數(shù)據(jù)存儲(chǔ)與分析

廣電用戶數(shù)據(jù)存儲(chǔ)與分析廣播電視行業(yè)是指專業(yè)從事廣電設(shè)備的生產(chǎn)、研究、銷售的單位，主要包括攝、錄、監(jiān)、采、編、播、管、存等方面。伴隨互聯(lián)網(wǎng)和移動(dòng)互聯(lián)網(wǎng)的快速發(fā)展，各種網(wǎng)絡(luò)電視和視頻應(yīng)用（如愛奇藝、騰訊視頻、芒果TV等）遍地開花，人們的電視觀看行為正發(fā)生變化，由之前的傳統(tǒng)電視媒介向電腦、手機(jī)、平板端的網(wǎng)絡(luò)電視轉(zhuǎn)化。目前某廣播電視網(wǎng)絡(luò)運(yùn)營集團(tuán)已建成完整覆蓋各區(qū)（縣級(jí)市）的有線傳輸與無線傳輸互為延伸、互為補(bǔ)充的廣電寬帶信息網(wǎng)絡(luò)，實(shí)現(xiàn)了城區(qū)全程全網(wǎng)的雙向覆蓋，為廣大市民提供有線數(shù)字電視、互聯(lián)網(wǎng)接入服務(wù)、高清互動(dòng)電視、移動(dòng)數(shù)字電視、手機(jī)電視、信息內(nèi)容集成等多樣化、跨平臺(tái)的信息服務(wù)。該集團(tuán)的數(shù)據(jù)來源多種多樣，需要對(duì)這些數(shù)據(jù)進(jìn)行存儲(chǔ)和分析。項(xiàng)目描述項(xiàng)目背景本項(xiàng)目將實(shí)現(xiàn)廣電用戶數(shù)據(jù)的存儲(chǔ)、處理和分析，先將CSV的數(shù)據(jù)導(dǎo)入至ElasticSearch中，再將ElasticSearch數(shù)據(jù)導(dǎo)入到Hive進(jìn)行數(shù)據(jù)探索、清洗和分析，最后將清洗分析的結(jié)果導(dǎo)入MySQL和HBase中。項(xiàng)目描述項(xiàng)目目標(biāo)根據(jù)該廣電公司的業(yè)務(wù)需求進(jìn)行具體分析，確定存儲(chǔ)與分析的技術(shù)選型，并設(shè)計(jì)存儲(chǔ)與分析架構(gòu)。為模擬真實(shí)生產(chǎn)環(huán)境，先將CSV的數(shù)據(jù)導(dǎo)入至ElasticSearch中，再將ElasticSearch數(shù)據(jù)導(dǎo)入至Hive中。對(duì)廣電用戶數(shù)據(jù)進(jìn)行基礎(chǔ)探索分析和業(yè)務(wù)探索分析，統(tǒng)計(jì)出不同類型的用戶記錄數(shù)、用戶收視時(shí)長分布和用戶的機(jī)頂盒待機(jī)記錄。再根據(jù)數(shù)據(jù)探索總結(jié)出數(shù)據(jù)清洗規(guī)則，處理廣電用戶數(shù)據(jù)中的無效數(shù)據(jù)，包括無效的用戶、收視、賬單、訂單數(shù)據(jù)。根據(jù)廣電用戶的月均消費(fèi)金額，計(jì)算用戶的電視消費(fèi)水平和寬帶消費(fèi)水平，作為廣電用戶的消費(fèi)水平標(biāo)簽。最終將處理后的結(jié)果保存至MySQL數(shù)據(jù)庫中。項(xiàng)目描述項(xiàng)目分析1導(dǎo)入CSV格式數(shù)據(jù)到ElasticSearch目錄分析需求與架構(gòu)2轉(zhuǎn)移ElasticSearch數(shù)據(jù)到Hive3統(tǒng)計(jì)各表宣傳和政企用戶記錄數(shù)據(jù)4用戶收視行為數(shù)據(jù)：用戶收視行為數(shù)據(jù)由網(wǎng)管系統(tǒng)實(shí)時(shí)采集。用戶消費(fèi)水平標(biāo)簽閾值探索：利用大數(shù)據(jù)技術(shù)進(jìn)行用戶畫像，將用戶標(biāo)簽化，以用戶為中心，串聯(lián)起用戶所有的歷史行為，然后根據(jù)歷史行為建立用戶畫像，細(xì)分出用戶的各種特征，由此能夠從整體上深入了解每一個(gè)用戶。用戶入網(wǎng)程度標(biāo)簽閾值探索：利用用戶基本信息表可以給用戶貼上用戶入網(wǎng)程度標(biāo)簽，即電視入網(wǎng)程度標(biāo)簽（子標(biāo)簽包含老用戶、中等用戶和新用戶），寬帶入網(wǎng)程度標(biāo)簽（子標(biāo)簽包含老用戶、中等用戶和新用戶）。

業(yè)務(wù)需求分析業(yè)務(wù)數(shù)據(jù)存儲(chǔ)使用ElasticSearch。使用Hive外部表關(guān)聯(lián)ElasticSearch。使用Sqoop實(shí)現(xiàn)將存儲(chǔ)在Hive的數(shù)據(jù)結(jié)果傳輸?shù)組ySQL中。

選擇存儲(chǔ)與分析技術(shù)將業(yè)務(wù)數(shù)據(jù)導(dǎo)入ElasticSearch。使用Hive外部表關(guān)聯(lián)ElasticSearch。利用Hive實(shí)現(xiàn)業(yè)務(wù)需求。利用Sqoop將畫像結(jié)果傳輸?shù)組ySQL中。設(shè)計(jì)存儲(chǔ)與分析架構(gòu)目錄轉(zhuǎn)移ElasticSearch數(shù)據(jù)到Hive3統(tǒng)計(jì)各表宣傳和政企用戶記錄數(shù)據(jù)41導(dǎo)入CSV格式數(shù)據(jù)到ElasticSearch分析需求與架構(gòu)2用戶基本信息表：用戶基本信息表記錄的是用戶最新狀態(tài)信息。

了解數(shù)據(jù)用戶狀態(tài)信息變更表：用戶狀態(tài)信息變更表用于記錄用戶所有時(shí)段的狀態(tài)信息。

了解數(shù)據(jù)賬單信息表：賬單信息表記錄用戶每月的賬單信息。

了解數(shù)據(jù)用戶收視行為信息表：用戶收視行為信息表記錄了用戶觀看電視的收視信息，其中觀看方式可分為直播、點(diǎn)播和回看，用戶每切換一個(gè)頻道都會(huì)生成一條新的記錄。

了解數(shù)據(jù)數(shù)據(jù)導(dǎo)入：利用ElaticSearch的Bulk

API，批量地將數(shù)據(jù)導(dǎo)入ElasticSearch中。查詢各表數(shù)據(jù)記錄數(shù)：利用Head插件提交查詢請(qǐng)求，查詢各表數(shù)據(jù)記錄數(shù)。導(dǎo)入數(shù)據(jù)到ElasticSearch目錄統(tǒng)計(jì)各表宣傳和政企用戶記錄數(shù)據(jù)41分析方法與過程背景與挖掘目標(biāo)2轉(zhuǎn)移ElasticSearch數(shù)據(jù)到Hive3分析需求與架構(gòu)導(dǎo)入CSV格式數(shù)據(jù)到ElasticSearch在Hive中用戶基本信息表：用戶基本信息表記錄的是用戶最新狀態(tài)信息。分別將各個(gè)表數(shù)據(jù)從ElasticSearch導(dǎo)入Hive。導(dǎo)入數(shù)據(jù)后，檢查導(dǎo)入數(shù)據(jù)是否完整。在Hive中建立并導(dǎo)入數(shù)據(jù)管理表在Hive中查詢表中的數(shù)據(jù)使用select語句。其基本的語法如下。

查看Hive中的表數(shù)據(jù)SELECT[ALL|DITINCT]select_expr,select_expr,...FROMtable_reference[WHEREwhere_condition][GROUPBYcol_list[HAVINGcondition][CLUSTERBYcol_list|[DISTRIBUTEBYcol_list][SORTBY|ORDERBYcol_list]][LIMITnumber]目錄統(tǒng)計(jì)各表宣傳和政企用戶記錄數(shù)據(jù)41分析方法與過程背景與挖掘目標(biāo)2分析需求與架構(gòu)導(dǎo)入CSV格式數(shù)據(jù)到ElasticSearch結(jié)果分析3轉(zhuǎn)移ElasticSearch數(shù)據(jù)到Hive根據(jù)廣電集團(tuán)提供的資料，在各個(gè)表中owner_code為02、09、10的數(shù)據(jù)就是宣傳數(shù)據(jù)。使用“where

owner_codein("02","09","10")”篩選出宣傳數(shù)據(jù)，再用count(*)進(jìn)行統(tǒng)計(jì)。

統(tǒng)計(jì)各表宣傳數(shù)據(jù)記錄數(shù)在各個(gè)表中owner_name=“EA”或“EB”或“EC”或“ED”或“EE”的數(shù)據(jù)即為政企用戶記錄。使用“whereowner_namein("EA級(jí)","EB級(jí)","EC級(jí)","ED級(jí)","EE級(jí)")”命令篩選出政企用戶數(shù)據(jù)，再用count(*)進(jìn)行統(tǒng)計(jì)。

統(tǒng)計(jì)各表政企用戶數(shù)據(jù)記錄數(shù)統(tǒng)計(jì)各無效收視數(shù)據(jù)目錄6統(tǒng)計(jì)各表無效數(shù)據(jù)7計(jì)算用戶電視消費(fèi)水平和寬帶消費(fèi)水平5統(tǒng)計(jì)出用戶收視時(shí)長的分布情況，統(tǒng)計(jì)用戶收視時(shí)長分布。用戶收視時(shí)長是根據(jù)表media_index的duration字段計(jì)算的。統(tǒng)計(jì)時(shí)長時(shí)需要注意，數(shù)據(jù)庫中存放的數(shù)據(jù)時(shí)以毫秒為單位，需要在程序中進(jìn)行換算。統(tǒng)計(jì)用戶收視時(shí)長分布在統(tǒng)計(jì)中，若同一個(gè)節(jié)目觀看時(shí)長超過5個(gè)小時(shí)，分析模型則認(rèn)為是機(jī)頂盒待機(jī)狀態(tài)。為了得到更準(zhǔn)確的數(shù)據(jù)分析結(jié)果，統(tǒng)計(jì)機(jī)頂盒待機(jī)的記錄數(shù)

統(tǒng)計(jì)機(jī)頂盒待機(jī)記錄數(shù)統(tǒng)計(jì)各無效收視數(shù)據(jù)目錄6統(tǒng)計(jì)各表無效數(shù)據(jù)7計(jì)算用戶電視消費(fèi)水平和寬帶消費(fèi)水平5處理無效用戶信息：無效用戶包括用戶狀態(tài)為被動(dòng)銷戶、創(chuàng)建、沖正、銷號(hào)的用戶，以及宣傳和政企用戶，對(duì)這些字段進(jìn)行清洗。處理無效收視信息：無效收視數(shù)據(jù)判定規(guī)則是觀看時(shí)長小于20秒或者觀看時(shí)長大于5小時(shí)的數(shù)據(jù)，清洗無效收視數(shù)據(jù)。處理無效賬單信息：無效賬單是指賬單數(shù)據(jù)mmconsume_billevents的should_pay字段數(shù)值小于0的記錄。查詢無效賬單的數(shù)量，對(duì)其進(jìn)行清洗，并將清洗結(jié)果存入mmconsume_billevents_preprocessed表中。處理無效訂單信息：無效訂單數(shù)據(jù)是指訂單數(shù)據(jù)order_index的cost字段數(shù)為空的記錄。查詢無效訂單的數(shù)量，對(duì)其進(jìn)行清洗，并將清洗結(jié)果存入order_index_preprocessed表中

處理各表無效數(shù)據(jù)統(tǒng)計(jì)各無效收視數(shù)據(jù)目錄6統(tǒng)計(jì)各表無效數(shù)據(jù)5計(jì)算用戶電視消費(fèi)水平和寬帶消費(fèi)水平7統(tǒng)計(jì)電視消費(fèi)水平主要是對(duì)mmconsume_billevents_preprocessed表中電視用戶的應(yīng)付金額數(shù)據(jù)進(jìn)行分析。用戶實(shí)際消費(fèi)金額應(yīng)等于應(yīng)付金額減去優(yōu)惠金額。由于數(shù)據(jù)是3個(gè)月的數(shù)據(jù)，所以還需要再除以3才能得到用戶的月均消費(fèi)金額。計(jì)算電視消費(fèi)水平和寬帶消費(fèi)水平1.電視消費(fèi)水平根據(jù)電視用戶的月均消費(fèi)金額，制定電視消費(fèi)水平標(biāo)簽的子標(biāo)簽的判斷閾值。對(duì)mmconsume_billevents_preprocessed表中should_pay字段的消費(fèi)數(shù)據(jù)進(jìn)行分類，得到電視超低消費(fèi)、電視低消費(fèi)、電視中等消費(fèi)和電視高消費(fèi)。計(jì)算電視消費(fèi)水平和寬帶消費(fèi)水平月消費(fèi)金額消費(fèi)水平>-26.5and<26.5電視超低消費(fèi)>=26.5and<46.5電視低消費(fèi)>=46.5and<66.5電視中等消費(fèi)>=66.5電視高消費(fèi)統(tǒng)計(jì)寬帶消費(fèi)水平主要是對(duì)mmconsume_billevents_preprocessed表中寬帶用戶的應(yīng)付金額數(shù)據(jù)進(jìn)行分析。用戶實(shí)際消費(fèi)金額應(yīng)等于應(yīng)付金額減去優(yōu)惠金額。由于數(shù)據(jù)總共3個(gè)月的數(shù)據(jù)，所以還需要再除以3才能得到用戶的月均消費(fèi)金額。計(jì)算電視消費(fèi)水平和寬帶消費(fèi)水平2.寬帶消費(fèi)水平根據(jù)用戶的月均消費(fèi)金額，制定寬帶消費(fèi)水平標(biāo)簽的子標(biāo)簽的判斷閾值。對(duì)mmconsume_billevents_preprocessed表中should_pay字段的消費(fèi)數(shù)據(jù)進(jìn)行分類，得到寬帶低消費(fèi)、寬帶中消費(fèi)和寬帶高消費(fèi)。計(jì)算電視消費(fèi)水平和寬帶消費(fèi)水平月消費(fèi)金額消費(fèi)水平<=25低消費(fèi)>25and<=45中等消費(fèi)>45高消費(fèi)將數(shù)據(jù)傳輸?shù)組ySQL首先需要先將Hive中的數(shù)據(jù)的表結(jié)構(gòu)復(fù)制到MySQL中。從關(guān)系數(shù)據(jù)庫導(dǎo)入文件到MySQL中。將數(shù)據(jù)傳輸?shù)紿Base