廣電用戶收視行為數(shù)據(jù)查詢優(yōu)化任務背景通過前面章節(jié)的學習，讀者對在HiveCLI中通過執(zhí)行HQL語句對Hive表進行查詢的方法有了一定的了解，也熟悉了各種Hive函數(shù)的使用場景，掌握了調(diào)用函數(shù)編寫HQL語句實現(xiàn)查詢的方法。然而，為了進一步提高查詢性能和效率，讀者該如何合理地、高效地選擇函數(shù)和編寫HQL語句，依舊是值得探究的主題。任務背景在數(shù)據(jù)量很小的場景下可以不考慮對HQL語句進行優(yōu)化，因為數(shù)據(jù)量很小，所以優(yōu)化效果可能不顯著。然而，在大數(shù)據(jù)場景下，語句優(yōu)化就尤為關(guān)鍵，在面對以百萬或以億計的數(shù)據(jù)量時，即使細微地優(yōu)化，都會在執(zhí)行效率上有質(zhì)的飛越。依“舊例”辦事固然很少出錯，但執(zhí)行速度會極慢，在高要求的背景下，大家需要做到“守正創(chuàng)新”，實現(xiàn)飛躍發(fā)展。廣電案例中的用戶收視行為數(shù)據(jù)表含有100多萬條數(shù)據(jù)，相應的查詢屬于大數(shù)據(jù)場景下的查詢，因此查詢語句的優(yōu)化是十分有必要的。任務背景本章將對廣電用戶收視行為數(shù)據(jù)表進行查詢優(yōu)化。首先介紹視圖的創(chuàng)建及其操作方法其次介紹Hive的設置優(yōu)化，如配置Fetch抓取、設置map和reduce任務數(shù)等再介紹Hive的語句優(yōu)化，如使用子查詢優(yōu)化查詢語句，通過介紹Hive查詢的優(yōu)化方法，并結(jié)合廣電用戶收視行為數(shù)據(jù)表，以實現(xiàn)各任務為目標，幫助讀者掌握Hive的優(yōu)化查詢方法。使用視圖統(tǒng)計不同節(jié)目的用戶觀看人數(shù)優(yōu)化統(tǒng)計直播頻道數(shù)使用子查詢統(tǒng)計節(jié)目類型為直播的頻道Top10任務描述隨著互聯(lián)網(wǎng)的發(fā)展，電視與互聯(lián)網(wǎng)相結(jié)合，已經(jīng)實現(xiàn)了節(jié)目點播或回放，人們可以隨時觀看自己喜歡的節(jié)目。為了解不同節(jié)目的用戶觀看人數(shù)，探索人們是觀看直播節(jié)目居多，還是因時間沖突，只能以點播或回放的方式觀看節(jié)目。本任務介紹使用Hive視圖降低查詢復雜度，優(yōu)化統(tǒng)計不同節(jié)目的用戶觀看人數(shù)查詢語句。創(chuàng)建視圖視圖是基于數(shù)據(jù)庫的基本表創(chuàng)建的一種偽表，因此數(shù)據(jù)庫只存儲視圖的定義，不存儲數(shù)據(jù)項，數(shù)據(jù)項仍然存在基本表中。視圖可作為抽象層，將數(shù)據(jù)發(fā)布給下游用戶。視圖只能用于查詢，但不能進行數(shù)據(jù)的插入和修改，提高了數(shù)據(jù)的安全性。在創(chuàng)建視圖時，視圖就已經(jīng)固定，因此對基本表的后續(xù)更改（如添加列等操作）將不會反映在視圖中。視圖允許從多個表中抽取字段組成可查詢的偽表。使用視圖可以降低查詢的復雜度，達到優(yōu)化查詢的目的。創(chuàng)建視圖在Hive中可以使用CREATEVIEW關(guān)鍵字創(chuàng)建視圖。創(chuàng)建視圖的語法如下。CREATEVIEW[IFNOTEXISTS][db_name.]view_name[(column_name[COMMENTcolumn_comment],...)][COMMENTview_comment][TBLPROPERTIES(property_name=property_value,...)]ASSELECT...;創(chuàng)建視圖創(chuàng)建Hive視圖的語法中的參數(shù)如下表所示。參數(shù)說明IFNOTEXISTS當創(chuàng)建視圖時，如果已經(jīng)存在同名的視圖，那么將引發(fā)錯誤。在創(chuàng)建視圖時可以使用IFNOTEXISTS判斷是否存在同名的視圖COMMENT注釋，不僅可以為選擇的字段添加注釋，而且也可以為視圖添加注釋TBLPROPERTIES用戶可在創(chuàng)建視圖時添加自定義或預定義的數(shù)據(jù)屬性，并設置數(shù)據(jù)屬性的賦值ASSELECT用戶可選擇所基于的基本表內(nèi)容創(chuàng)建視圖，定義視圖的結(jié)構(gòu)和數(shù)據(jù)。如果在創(chuàng)建視圖時未定義列名，那么視圖列的名稱將自動由定義的SELECT表達式派生（如果SELECT包含無別名的標量表達式，如“x+y”，那么視圖列名將以“_C0”“_C1”的形式生成）。重命名列時，還可以選擇提供列注釋（注釋不會自動繼承自基礎列）創(chuàng)建視圖廣電用戶收視行為數(shù)據(jù)表中，記錄了不同節(jié)目的收視情況。現(xiàn)通過從Hive的廣電用戶收視行為數(shù)據(jù)表media_index中選取用戶編號、直播頻道名稱、觀看行為開始時間和觀看行為結(jié)束時間，形成一個可供查詢的視圖media_index_time_view。查看與刪除視圖視圖是一個沒有關(guān)聯(lián)存儲的純邏輯對象。當查詢引用視圖時，會評估視圖的定義以生成一組行數(shù)據(jù)供進一步地查詢。視圖是一個概念性描述，實際上作為查詢優(yōu)化的一部分，Hive將視圖的定義與查詢的定義結(jié)合起來，如在查詢視圖內(nèi)容時，Hive根據(jù)視圖的定義選擇引用表中對應的字段內(nèi)容進行查詢。在上小節(jié)中已創(chuàng)建了media_index_time_view視圖，在ZJSM數(shù)據(jù)庫中使用“SHOWTABLES;”語句，可查看當前數(shù)據(jù)庫中的表和視圖，如下圖所示。查看與刪除視圖除了使用“SHOWTABLES;”語句可查看當前數(shù)據(jù)庫中的視圖外，Hive2.2.0及其后的版本開始支持使用“SHOWVIEWS;”語句查看當前數(shù)據(jù)庫中的視圖，如下圖所示。查看與刪除視圖從上圖中可看到，數(shù)據(jù)庫中已經(jīng)存在了media_index_time_view視圖，可使用“DESCmedia_index_time_view;”語句查看視圖結(jié)構(gòu)，如下圖所示。查看與刪除視圖視圖內(nèi)容查詢方法與表內(nèi)容查詢方法一致，查詢media_index_time_view視圖的前10行數(shù)據(jù)，結(jié)果下圖所示。查看與刪除視圖視圖是基于基本表創(chuàng)建的偽表，并沒有將真實數(shù)據(jù)存儲在Hive中，若刪除視圖關(guān)聯(lián)的基本表，則查詢視圖內(nèi)容時將會報錯。若刪除ZJSM數(shù)據(jù)庫中的media_index表，則再次執(zhí)行上面代碼所示命令后，查看media_index_time_view視圖內(nèi)容時將會報錯，如下圖所示。查看與刪除視圖刪除視圖可使用“DROPVIEWview_name;”語句。對視圖使用“DROPTABLE”語句是非法的，這不會刪除視圖。使用“DROPVIEWmedia_index_time_view;”語句刪除media_index_time_view視圖，如下圖所示。任務實現(xiàn)本任務的目標是實現(xiàn)使用視圖統(tǒng)計不同節(jié)目的用戶觀看人數(shù)。廣電用戶收視行為數(shù)據(jù)表的字段眾多，使用視圖篩選出相關(guān)字段可以降低查詢的復雜度，實現(xiàn)Hive查詢優(yōu)化。實現(xiàn)該任務的步驟如下。根據(jù)media_index表中的phone_no（用戶編號）和res_type（節(jié)目類型）字段創(chuàng)建視圖media_index_type_view。任務實現(xiàn)使用media_index_type_view視圖統(tǒng)計不同節(jié)目的用戶觀看人數(shù)。執(zhí)行上面代碼所示的命令可得到不同節(jié)目的用戶觀看人數(shù)的統(tǒng)計結(jié)果，如下圖所示，可得出觀看電視直播節(jié)目的用戶有6333人，觀看點播或回放節(jié)目的用戶有2448人。因此可得觀看電視直播節(jié)目的用戶人數(shù)約是觀看點播或回放節(jié)目的用戶人數(shù)的2.6倍。使用視圖統(tǒng)計不同節(jié)目的用戶觀看人數(shù)優(yōu)化統(tǒng)計直播頻道數(shù)使用子查詢統(tǒng)計節(jié)目類型為直播的頻道Top10任務描述隨著有線數(shù)字電視的發(fā)展與普及，用戶能夠觀看更多的直播頻道。用戶除了可以觀看基本的免費直播節(jié)目外，還可以通過付費的方式觀看更多的付費節(jié)目?，F(xiàn)今電視節(jié)目豐富多彩，直播頻道相對較多。本任務介紹優(yōu)化Hive配置并統(tǒng)計廣電直播頻道數(shù)，提高查詢效率。配置Fetch抓取Fetch抓取是指在Hive中對某些數(shù)據(jù)的查詢可以不必使用MapReduce計算，而是讀取存儲目錄下的文件，再輸出查詢結(jié)果到控制臺，如全局查詢、字段查詢和使用LIMIT語句查詢。在特殊的場景下配置Fetch抓取，可以提高查詢的效率。配置Fetch抓取Hive安裝目錄的conf目錄下存在一個hive-default.xml.template配置文件，該文件中存在一個hive.fetch.task.conversion參數(shù)，對該參數(shù)可以設置3種值，分別為none、minimal和more，3種參數(shù)值的含義解析如下表所示。參數(shù)值含義解析none表示所有查詢都會運行MapReduceminimal表示在查詢的開始階段、選擇某些分區(qū)的字段和使用LIMIT語句查詢時不會執(zhí)行MapReducemore表示在全局查詢、字段查詢和使用LIMIT查詢時都不會執(zhí)行MapReduce配置Fetch抓取將hive.fetch.task.conversion參數(shù)的值設置為more，即可實現(xiàn)Fetch抓取。Hive3.1.2中的hive.fetch.task.conversion參數(shù)的值默認設置為more。例如，在Hive3.1.2的CLI中簡單地查詢廣電用戶收視行為數(shù)據(jù)表的前5行數(shù)據(jù)，查詢時將不會執(zhí)行MapReduce，如下圖所示。配置Fetch抓取若將hive.fetch.task.conversion的值設置為none，再次查詢廣電用戶收視行為數(shù)據(jù)表的前5行數(shù)據(jù)，則查詢時將執(zhí)行MapReduce，執(zhí)行結(jié)果如下圖所示。配置Fetch抓取對比上兩張圖所示的執(zhí)行時間可得：當將hive.fetch.task.conversion的值設置為more時，查詢時間為0.381秒當將hive.fetch.task.conversion的值設置為none時，查詢時間為72.547秒因此配置Fetch抓取對執(zhí)行簡單查詢的效率有顯著的提升。合理設置map和reduce任務數(shù)當使用Hive進行聚合查詢時，將不通過Fetch抓取讀取存儲目錄文件，而是使用MapReduce作業(yè)執(zhí)行聚合查詢的過程。在通常情況下，MapReduce作業(yè)通過讀取數(shù)據(jù)文件將產(chǎn)生一個或多個map任務，產(chǎn)生的map任務數(shù)主要取決于讀取到的文件數(shù)和集群設置的文件塊大小。而在默認的情況下，reduce任務數(shù)則是通過Hive的內(nèi)置算法決定的。合理設置map和reduce任務數(shù)調(diào)整map任務數(shù)并不是map任務數(shù)越多，其執(zhí)行效率就越高。假設一個任務存在多個小文件，每一個小文件都會啟動一個map任務，當一個map任務啟動和初始化的時間遠遠大于邏輯處理的時間，會造成很大的資源浪費，并且可執(zhí)行的map任務數(shù)是有限的。因此，可以設置在map任務執(zhí)行前合并小文件以達到減少map任務數(shù)的目的。合理設置map和reduce任務數(shù)當然，map任務數(shù)也不是越少越好。假設處理的文件較大，任務邏輯復雜，map任務執(zhí)行較慢的時候，可考慮增加map任務數(shù)，降低每個map任務處理的數(shù)據(jù)量，從而提高任務的執(zhí)行效率。增加map任務數(shù)可以通過減小Hadoop塊實現(xiàn)。廣電用戶收視行為數(shù)據(jù)表大小約為780MB，使用“SETmapreduce.input.fileinputformat.split.maxsize;”語句查看最大切分數(shù)據(jù)塊，結(jié)果如下圖所示。本書Hive環(huán)境中默認最大切分數(shù)據(jù)塊的大小為256MB。合理設置map和reduce任務數(shù)使用count()聚合函數(shù)統(tǒng)計廣電用戶收視行為數(shù)據(jù)表的記錄數(shù)，結(jié)果如右圖所示，該查詢過程所需要的執(zhí)行時間為62.273秒。由右圖可得，處理廣電用戶收視行為數(shù)據(jù)表將會產(chǎn)生4個map任務，map任務數(shù)較少，因此每個map任務所需處理的數(shù)據(jù)量較大。合理設置map和reduce任務數(shù)將最大切分數(shù)據(jù)塊的大小設置為128MB，再統(tǒng)計廣電用戶收視行為數(shù)據(jù)表的記錄數(shù)，預計將產(chǎn)生7個map任務，執(zhí)行效率將會提升。執(zhí)行結(jié)果如下圖所示。下圖所示的結(jié)果與預測結(jié)果一致，將最大切分數(shù)據(jù)塊的大小設置為128MB后，MapReduce任務產(chǎn)生了7個map任務，執(zhí)行時間也從62.273秒降低為52.472秒，表明通過合理設置map任務數(shù)能夠有效地提高執(zhí)行效率。合理設置map和reduce任務數(shù)調(diào)整reduce任務數(shù)Hive3.1.2中每個reduce任務處理的數(shù)據(jù)塊大小默認是256MB，每個MapReduce任務的最大可執(zhí)行reduce任務數(shù)為1009個，結(jié)果如下圖所示。合理設置map和reduce任務數(shù)若不設置reduce任務數(shù)，則Hive將使用內(nèi)置算法確定reduce任務數(shù)，這將對執(zhí)行效率有很大的影響。在處理的數(shù)據(jù)量較大時，若reduce任務數(shù)較少，則會導致reduce任務執(zhí)行過慢，甚至會出現(xiàn)內(nèi)存過載的錯誤。若reduce任務數(shù)較多，則會產(chǎn)生大量的小文件，造成文件合并代價太高，NameNode的內(nèi)存占用也會增大。因此，合理地設置reduce任務數(shù)尤為關(guān)鍵。合理的reduce任務數(shù)等于讀取的文件大小除以每個reduce任務能夠處理的數(shù)據(jù)量大小。如廣電用戶收視行為數(shù)據(jù)表的大小約為780MB，合理的reduce任務數(shù)應該為4。配置并行執(zhí)行在Hive中執(zhí)行HQL語句查詢時，會將查詢轉(zhuǎn)化成一個或多個階段來完成查詢?nèi)蝿?，包括MapReduce階段、全局查詢階段、合并階段或使用LIMIT語句查詢階段等。在Hive的默認環(huán)境下，Hive執(zhí)行任務時將按照劃分好的階段逐步執(zhí)行，換而言之，Hive一次只會執(zhí)行一個階段。其實在Hive作業(yè)的眾多階段中，并非所有的階段都是完全相互依賴的，因此存在某些階段是允許并行執(zhí)行的。通過配置并行執(zhí)行，可以使得整個Hive作業(yè)的執(zhí)行時間縮短。Hive的并行執(zhí)行配置。配置并行執(zhí)行Hive的并行執(zhí)行默認狀態(tài)是false（即關(guān)閉），需要設置為true（開啟）。Hive中執(zhí)行同一條HQL語句所支持的并行度默認值為8，換而言之，Hive可以同時執(zhí)行8個互不相關(guān)的階段。讀者可以根據(jù)所執(zhí)行的HQL語句的復雜程度調(diào)整最大并行度。值得注意的是，在Hive集群當中，如果Hive作業(yè)中并行執(zhí)行階段增多，那么集群資源利用率也會增大。并行執(zhí)行會占用大量的集群資源以加速HQL語句的執(zhí)行，因此一定要清楚集群資源的總量與當前利用率，否則并行執(zhí)行將失敗。任務實現(xiàn)本任務的目標是實現(xiàn)優(yōu)化Hive配置，并且統(tǒng)計直播頻道數(shù)，任務實現(xiàn)步驟如下。創(chuàng)建media_index_station_view視圖，從廣電用戶收視行為數(shù)據(jù)表中篩選出直播頻道字段。將Hive中的最大切分數(shù)據(jù)塊的大小設置為128MB，增加map任務數(shù)。開啟任務并行執(zhí)行。去重統(tǒng)計直播頻道數(shù)。任務實現(xiàn)優(yōu)化統(tǒng)計直播頻道數(shù)，將生成1個MapReduce作業(yè)，且讀取media_index_station_view視圖數(shù)據(jù)時，生成7個map任務。因為使用COUNT(DISTINCT)語句去重，所以只會生成1個reduce任務，MapReduce作業(yè)執(zhí)行總用時為50.399s，結(jié)果如下圖所示。使用視圖統(tǒng)計不同節(jié)目的用戶觀看人數(shù)優(yōu)化統(tǒng)計直播頻道數(shù)使用子查詢統(tǒng)計節(jié)目類型為直播的頻道Top10任務描述現(xiàn)今廣電直播頻道數(shù)量眾多，電視節(jié)目日益全球化，任務6.2統(tǒng)計出廣電公司擁有147個直播頻道。廣電公司需要對每個直播頻道進行投資，因此了解用戶感興趣的節(jié)目，進行直播頻道熱度統(tǒng)計，對廣電公司進行節(jié)目投資十分重要。本任務介紹使用子查詢統(tǒng)計出節(jié)目類型為直播的頻道Top10。使用子查詢優(yōu)化查詢語句Hive作為分布式的數(shù)據(jù)倉庫，在執(zhí)行分布式計算和分布式存儲時，都會消耗大量的磁盤和網(wǎng)絡I/O（Input/Output，輸入輸出）資源，因此如何減少I/O資源消耗是一個優(yōu)化的焦點。Hive的查詢依賴于MapReduce計算框架，而每一個MapReduce作業(yè)的啟動需要消耗大量的I/O資源，原因是MapReduce存在Shuffle操作，中間結(jié)果將產(chǎn)生大量的磁盤落地。HQL語句的查詢?nèi)蝿諘D(zhuǎn)化為MapReduce程序來執(zhí)行，若存在多個作業(yè)，則作業(yè)與作業(yè)之間的中間結(jié)果會先溢寫到磁盤上。因此優(yōu)化HQL語句減少中間結(jié)果數(shù)據(jù)的產(chǎn)生，也能夠達到減少I/O資源消耗的效果。使用子查詢優(yōu)化查詢語句子查詢指在一個查詢語句中嵌套使用一個或多個查詢語句，子查詢放在FROM關(guān)鍵字之后，且因為FROM子句中的每個表都必須具有名稱，所以需要為子查詢指定表名稱。子查詢SELECT列表中的列（字段）名稱也必須唯一。從Hive0.13開始允許使用AS關(guān)鍵字進行子查詢表命名，同時也開始支持在WHERE之后使用關(guān)鍵字IN或NOTIN實現(xiàn)子查詢。使用子查詢優(yōu)化查詢語句在Hive中，盡量先多使用子查詢和使用WHERE語句降低表數(shù)據(jù)的復雜度，再使用JOIN連接查詢。如果先進行表連接，那么查詢將先進行全表掃描，最后才使用WHERE語句篩選，執(zhí)行效率將會降低。如果先使用子查詢，那么可利用WHERE語句過濾不相關(guān)字段，不但能增加map任務數(shù)，還能減小數(shù)據(jù)量。使用子查詢，查詢用戶數(shù)小于500的用戶等級名稱，查詢到EA級的用戶數(shù)小于500，結(jié)果如下圖所示。優(yōu)化配置GROUPBY語句在使用GROUPBY語句進行數(shù)據(jù)分組時，在默認情況下，Map階段相同的key數(shù)據(jù)將發(fā)送給同一個reduce任務處理。當某一個key數(shù)據(jù)過大時，將產(chǎn)生數(shù)據(jù)傾斜，導致某個reduce任務需要處理的數(shù)據(jù)量過大，使得該reduce任務執(zhí)行緩慢，甚至造成任務掛失。其實并非所有的聚合操作都需要在Reduce階段完成，許多聚合操作在不影響最終結(jié)果的情況下可以在Map階段進行預聚合（如求和以及求最值），最后在Reduce階段進行結(jié)果輸出即可。優(yōu)化配置GROUPBY語句在Hive中開啟Map階段預聚合的參數(shù)設置如下。設置允許在Map端進行聚合，聚合設置默認為true（開啟）。設置允許在Map端進行聚合操作的數(shù)據(jù)量時應對所需處理的數(shù)據(jù)量有一定的了解，若設置的可聚合數(shù)量過小，則會影響執(zhí)行效率。優(yōu)化配置GROUPBY語句設置允許在發(fā)生數(shù)據(jù)傾斜時進行負載均衡，負載均衡默認為false（關(guān)閉），需要將其設置為true（開啟）。當開啟負載均衡時，將生成兩個MapReduce作業(yè)。第一個MapReduce作業(yè)的Map端的輸出結(jié)果將被隨機分布到Reduce端，對每個reduce任務進行部分數(shù)據(jù)聚合操作，并輸出結(jié)果，目的是將相同的key分發(fā)到不同的reduce任務中，以達到負載均衡。第二個MapReduce作業(yè)根據(jù)第一個MapReduce作業(yè)處理好的結(jié)果按key分組聚合并發(fā)送到相同的reduce任務中。優(yōu)化配置GROUPBY語句以分組統(tǒng)計廣電用戶收視行為數(shù)據(jù)表中的用戶等級名稱為例，對比在優(yōu)化配置GROUPBY語句前后的任務執(zhí)行時間。使用Hive默認配置分組統(tǒng)計用戶等級名稱，將產(chǎn)生一個MapReduce作業(yè)，作業(yè)執(zhí)行時間為105.8秒，如右圖所示。優(yōu)化配置GROUPBY語句使用語句優(yōu)化配置GROUPBY語句后，再次分組統(tǒng)計用戶等級名稱，將產(chǎn)生兩個MapReduce作業(yè).第一個作業(yè)的作用是實現(xiàn)部分數(shù)據(jù)預聚合與reduce任務負載均衡。第二個作業(yè)的作用是實現(xiàn)全部數(shù)據(jù)分組聚合輸出。兩個作業(yè)的總執(zhí)行時間為82.268秒，相比于使用Hive默認配置分組統(tǒng)計用戶等級名稱的執(zhí)行時間105.8秒，執(zhí)行時間減少了大約23.5秒，如右圖所示。使用GROUPBY代替COUNT(DISTINCT)去重統(tǒng)計在數(shù)據(jù)量較小的場景下，使用COUNT(DISTINCT)去重統(tǒng)計與使用GROUPBY去重統(tǒng)計在執(zhí)行效率上區(qū)別不大。在數(shù)據(jù)量較大的場景下，不建議使用COUNT(DISTINCT)，因為COUNT(DISTINCT)只會啟動一個reduce任務，該reduce任務需要處理的數(shù)據(jù)量較大，將導致整個MapReduce作業(yè)難以完成?？煽紤]先使用GROUPBY分組后再使用COUNT函數(shù)統(tǒng)計的方式實現(xiàn)去重。使用GROUPBY代替COUNT(DISTINCT)去重統(tǒng)計使用COUNT(DISTINCT)去重統(tǒng)計用戶數(shù)，將啟用一個reduce任務執(zhí)行，執(zhí)行時間為51.115秒，結(jié)果如下圖所示。使用GROUPBY代替COUNT(DISTINCT)去重統(tǒng)計使用GROUPBY分組后再使用COUNT函數(shù)統(tǒng)計的方式實現(xiàn)去重統(tǒng)計用戶數(shù)，將會啟動兩個MapReduce作業(yè)，也就是對應的先分組后統(tǒng)計。啟動MapReduce作業(yè)后，將產(chǎn)生4個reduce任務，兩個MapReduce作業(yè)的總執(zhí)行時間為77.993秒，如右圖所示。相比于使用COUNT(DISTINCT)去重統(tǒng)計用戶數(shù)，采用GROUPBY分組后再使用COUNT函數(shù)統(tǒng)計的方式雖然會啟動兩個MapReduce作業(yè)，在執(zhí)行時間上稍慢，但是相比于只用一個reduce任務，采用4個reduce任務執(zhí)行更能保證數(shù)據(jù)的安全和作業(yè)的穩(wěn)定執(zhí)行。優(yōu)化配置LIMIT語句LIMIT語句用于限制查詢返回的行數(shù)。如果不優(yōu)化LIMIT語句，將會在全表查詢后返回限制的行數(shù)。在Hive中可開啟LIMIT語句優(yōu)化參數(shù)，優(yōu)化后將對數(shù)據(jù)抽樣返回。開啟LIMIT語句優(yōu)化參數(shù)，需要開啟對數(shù)據(jù)進行采樣的功能，且可以設置最小采樣容量和可抽樣的最大文件數(shù)。優(yōu)化配置LIMIT語句設置LIMIT抽樣返回之后存在一個缺點，即有用的數(shù)據(jù)可能永遠不會被抽到。Hive還可以通過設置嚴格模式，防止一些危險操作。例如，設置使用ORDERBY語句進行查詢時必須使用LIMIT語句。因為ORDERBY語句為了執(zhí)行排序會將所有的結(jié)果數(shù)據(jù)分發(fā)到同一個Reduce任務中做處理，所以強制要求用戶增加LIMIT語句可以防止Reducer階段執(zhí)行時間過長，該設置默認false（關(guān)閉）。任務實現(xiàn)因為需要使用子查詢統(tǒng)計節(jié)目類型為直播的頻道Top10，且需要去重用戶數(shù)，所以需要使用GROUPBY語句。因此，需要優(yōu)化配置GROUPBY語句，以提高執(zhí)行效率。任務實現(xiàn)步驟如下。創(chuàng)建media_index_Top10_view視圖，從廣電用戶收視行為數(shù)據(jù)表中篩選出用戶編號、直播頻道名稱和節(jié)目類型字段。將最大切分數(shù)據(jù)塊的大小設置為128MB，增加map任務數(shù)。任務實現(xiàn)因為廣電用戶收視行為數(shù)據(jù)表大小約為780MB，Hive中每個reduce任務處理的數(shù)據(jù)量大小默認是256MB，所以設置reduce任務數(shù)為4。優(yōu)化配置GROUPBY語句，設置允許在Map端進行聚合，且設置允許在Map端進行聚合操作的數(shù)據(jù)量為10000000。設置執(zhí)行GROUPBY語句時，允許在發(fā)生數(shù)據(jù)傾斜時進行負載均衡。任務實現(xiàn)開啟任務并行執(zhí)行。設置使用ORDERBY語句進行查詢時必須使用LIMIT語句。使用子查詢統(tǒng)計節(jié)目類型為直播的頻道Top10，過濾掉點播或回放的節(jié)目類型數(shù)據(jù)。任務實現(xiàn)通過優(yōu)化配置Hive執(zhí)行環(huán)境后，執(zhí)行HQL語句統(tǒng)計節(jié)目類型為直播的頻道Top10，將啟動3個MapReduce作業(yè)，執(zhí)行時間為136.519秒，結(jié)果下圖所示。小結(jié)本章先介紹了Hive視圖的創(chuàng)建、查看與刪除方法。其次介紹了如何配置Fetch抓取、設置map和reduce任務數(shù)以及配置并行執(zhí)行。然后介紹了使用子查詢的方法。最后介紹了優(yōu)化配置GROUPBY語句和LIMIT語句。本章通過優(yōu)化Hive配置與HQL語句，實現(xiàn)廣電用戶收視行為數(shù)據(jù)查詢優(yōu)化，幫助讀者掌握各種Hive優(yōu)化方法。廣電用戶數(shù)據(jù)清洗及數(shù)據(jù)導出任務背景大數(shù)據(jù)分析結(jié)果的有效性在很大程度上依賴于所處理數(shù)據(jù)的質(zhì)量，使用合理的方法分析高質(zhì)量的數(shù)據(jù)將得到準確的結(jié)果。數(shù)據(jù)的質(zhì)量對任何依賴于該數(shù)據(jù)的應用所獲得結(jié)果有重要影響。數(shù)據(jù)的不完整性、不一致性、重復性和無效性等是低質(zhì)量數(shù)據(jù)的重要特征。例如，使用歐洲的用戶畫像衡量中國的用戶畫像，因為數(shù)據(jù)描述的對象并不對應，所以這些數(shù)據(jù)是無效的、不準確的。因此，一般在數(shù)據(jù)分析、數(shù)據(jù)挖掘之前，需要進行數(shù)據(jù)探索，即探索數(shù)據(jù)的完整性、一致性、重復性和合理性等，若發(fā)現(xiàn)無效數(shù)據(jù)，則應進行數(shù)據(jù)清洗，為后續(xù)的數(shù)據(jù)分析處理工作提供高質(zhì)量的數(shù)據(jù)。任務背景在前面章節(jié)中，使用的是廣電用戶的原始數(shù)據(jù)，通過數(shù)據(jù)查詢發(fā)現(xiàn)其中存在許多缺失和異常的數(shù)據(jù)，如大量數(shù)據(jù)字段中包含NULL值。在統(tǒng)計字段中的類型數(shù)量時也會對NULL值進行計算，造成數(shù)據(jù)分析結(jié)果的不準確。因此，需要對廣電用戶數(shù)據(jù)中的不符合案例分析要求的數(shù)據(jù)，即無效數(shù)據(jù)，進行清洗并將清洗后的數(shù)據(jù)進行保存。任務背景本章將對廣電用戶數(shù)據(jù)進行探索，尋找出各表中的無效數(shù)據(jù)，進行數(shù)據(jù)清洗并將清洗后的數(shù)據(jù)進行保存。本章將先探索無效的用戶數(shù)據(jù)，如統(tǒng)計重復的用戶數(shù)和探索特殊線路用戶數(shù)據(jù)等，其次探索無效的收視行為數(shù)據(jù)，分析用戶收視行為特征，篩選有效數(shù)據(jù)，接著探索無效賬單和訂單數(shù)據(jù)，最后將清洗好的數(shù)據(jù)進行保存。數(shù)據(jù)清洗的過程是比較煩瑣的，但需細致入微、踔厲奮發(fā)、勇毅前行，為實現(xiàn)任務而努力、堅持。清洗無效用戶數(shù)據(jù)清洗無效收視行為數(shù)據(jù)清洗無效賬單和訂單數(shù)據(jù)導出處理結(jié)果至Linux本地和HDFS清洗無效用戶數(shù)據(jù)在進行廣電用戶數(shù)據(jù)分析時，需要研究大眾用戶的行為特征。一般而言，政企用戶、內(nèi)部通信用戶、測試用戶和辦理了銷號的用戶等都是無效用戶，因此，需要對無效用戶數(shù)據(jù)進行清洗。本任務探索廣電用戶數(shù)據(jù)中用戶編號phone_no、用戶等級編號owner_code、用戶等級名稱owner_name、品牌名稱sm_name和狀態(tài)名稱run_name這些字段中的無效用戶數(shù)據(jù)并進行清洗。探索無效用戶數(shù)據(jù)在廣電用戶數(shù)據(jù)中，存在大量的無效用戶數(shù)據(jù)，需要進行數(shù)據(jù)探索，查找無效用戶數(shù)據(jù)，然后刪除無效用戶數(shù)據(jù)，實現(xiàn)數(shù)據(jù)清洗。探索過程如下。1．統(tǒng)計重復的用戶數(shù)探索用戶基本數(shù)據(jù)表中是否存在重復記錄的用戶先統(tǒng)計用戶基本數(shù)據(jù)表中每個用戶記錄數(shù)，結(jié)合統(tǒng)計出的結(jié)果，觀察是否存在重復記錄的用戶再分組統(tǒng)計每個用戶編號phone_no的記錄數(shù)，并按記錄數(shù)降序排列，取前10條數(shù)據(jù)，發(fā)現(xiàn)用戶基本數(shù)據(jù)表中不存在記錄數(shù)大于1的用戶，且phone_no分組統(tǒng)計記錄數(shù)，將結(jié)果按降序排列，得到結(jié)果中所有的phone_no都唯一。探索無效用戶數(shù)據(jù)2．探索特殊線路用戶數(shù)據(jù)根據(jù)業(yè)務人員提供的數(shù)據(jù)，用戶等級編號owner_code字段含有多個取值，其中值為2、9或10的記錄是特殊路線的用戶的數(shù)據(jù)，特殊線路是用于用戶測試、產(chǎn)品檢驗的。保存廣電用戶數(shù)據(jù)的5個表中都存在owner_code字段，對這5個表中是否存在特殊線路的用戶及其數(shù)量進行分析，按照owner_code字段分組后，再統(tǒng)計該字段各值的記錄數(shù)。探索無效用戶數(shù)據(jù)以統(tǒng)計用戶基本數(shù)據(jù)表的owner_code字段值的結(jié)果為例，如圖所示，發(fā)現(xiàn)owner_code存在字段值為2的數(shù)據(jù)，且所占的比例較小。此外owner_code字段還存在空值（NULL），經(jīng)過與業(yè)務人員溝通確認owner_code字段存在空值是正常的。探索無效用戶數(shù)據(jù)對其余4個表統(tǒng)計owner_code字段值，發(fā)現(xiàn)存在2、9或10，因此各表都需要清洗owner_code字段值為2、9或10的記錄，各表的owner_code字段值如表所示。數(shù)據(jù)表owner_code字段值用戶基本數(shù)據(jù)表0、15、2、5、6、7、8、NULL用戶狀態(tài)變更數(shù)據(jù)表0、15、2、5、6、8、NULL賬單數(shù)據(jù)表0、1、15、2、30、31、4、5、6、7、8、9、NULL訂單數(shù)據(jù)表0、1、10、15、2、30、31、4、5、6、7、8、9、NULL用戶收視行為數(shù)據(jù)表0、8、15、1、5、2、6、31、7、10、NULL探索無效用戶數(shù)據(jù)3．探索政企用戶數(shù)據(jù)由于廣電公司的用戶主要是家庭用戶，所以政企用戶不納入分析范圍。根據(jù)業(yè)務人員提供的數(shù)據(jù)，政企用戶的標識是用戶等級名稱owner_name字段值為EA級、EB級、EC級、ED級或EE級。根據(jù)第3章的數(shù)據(jù)說明所提供的數(shù)據(jù)，保存廣電用戶數(shù)據(jù)的5個表中都存在owner_name字段，需要探索這些表中是否存在政企用戶以及存在的數(shù)量。按照owner_name字段進行分組，再統(tǒng)計該字段各值的記錄數(shù)。探索無效用戶數(shù)據(jù)執(zhí)行代碼中的代碼可得出每個表的owner_name字段值的記錄數(shù)，每個表存在的owner_name字段值都不一致。以用戶基本數(shù)據(jù)表的owner_name字段值為例，存在EA級、EB級和EE級。探索無效用戶數(shù)據(jù)且owner_name字段值為HC級的記錄數(shù)最多，而政企用戶的數(shù)量較少，這也印證了廣電公司的用戶主要是家庭用戶。用戶基本信息表owner_name字段數(shù)據(jù)統(tǒng)計情況。數(shù)據(jù)表owner_name字段值用戶基本數(shù)據(jù)表EA級、EB級、EE級、HA級、HB級、HC級、HE級用戶狀態(tài)變更數(shù)據(jù)表EA級、EB級、EE級、HA級、HB級、HC級賬單數(shù)據(jù)表EA級、EB級、EE級、HA級、HB級、HC級、HE級訂單數(shù)據(jù)表EA級、EB級、EE級、HA級、HB級、HC級、HE級、NULL用戶收視行為數(shù)據(jù)表EA級、EE級、HA級、HB級、HC級、HE級探索無效用戶數(shù)據(jù)從3.1.4小節(jié)可知，本書使用的數(shù)據(jù)是一段時間內(nèi)的廣電業(yè)務數(shù)據(jù)，而在實際的業(yè)務數(shù)據(jù)庫中，各信息表中可能會出現(xiàn)owner_name字段值為EC級或ED級的政企用戶記錄。因此在進行數(shù)據(jù)預處理時，需要清洗owner_name字段值為EA級、EB級、EC級、ED級和EE級的政企用戶。探索無效用戶數(shù)據(jù)4．統(tǒng)計sm_name字段值廣電公司目前的業(yè)務類型主要是數(shù)字電視、互動電視、珠江寬頻、模擬有線電視和甜果電視這5種，品牌名稱可以通過sm_name字段進行標識。除了用戶狀態(tài)變更數(shù)據(jù)表，其余4個表都含有sm_name字段。下面以統(tǒng)計用戶基本數(shù)據(jù)表中的所有業(yè)務類型、每種類型的用戶數(shù)以及每種類型的用戶數(shù)占比為例，實現(xiàn)sm_name字段數(shù)據(jù)探索，操作步驟如下。探索無效用戶數(shù)據(jù)首先統(tǒng)計用戶基本數(shù)據(jù)表的總記錄數(shù)，將其作為后續(xù)統(tǒng)計sm_name字段值數(shù)量占比的分母。接著按sm_name字段分組統(tǒng)計該字段各值的數(shù)量及其占比，統(tǒng)計用戶基本數(shù)據(jù)表的sm_name字段各值的數(shù)量及其占比，統(tǒng)計用戶基本數(shù)據(jù)表中sm_name字段各值的數(shù)量及其占比，結(jié)果顯示，sm_name字段的值一共有5種，且模擬有線電視的用戶最多，約占總數(shù)的49%，其次數(shù)字電視的用戶約占30%。現(xiàn)在的主要業(yè)務是互動電視、數(shù)字電視、甜果電視、珠江寬頻，這四者約占總數(shù)的50%，需要保留這4種業(yè)務類型的用戶，刪除其他業(yè)務類型的用戶。探索無效用戶數(shù)據(jù)5．篩選正常、欠費暫停、主動暫停和主動銷戶的用戶數(shù)據(jù)根據(jù)業(yè)務要求，除了需要篩選指定品牌名稱的用戶外，還需要對狀態(tài)名稱進行過濾，只保留狀態(tài)名稱為正常、欠費暫停、主動暫停和主動銷戶的用戶，其余的狀態(tài)名稱不需要進行分析處理。狀態(tài)名稱的字段標識為run_name，只有用戶狀態(tài)變更數(shù)據(jù)表、訂單數(shù)據(jù)表與用戶基本數(shù)據(jù)表含有run_name字段。以對用戶基本數(shù)據(jù)中的用戶狀態(tài)進行探索為例，實現(xiàn)run_name字段數(shù)據(jù)探索，按照run_name字段分組后，再統(tǒng)計該字段各值的記錄數(shù)。探索無效用戶數(shù)據(jù)代碼得到的統(tǒng)計結(jié)果顯示run_name字段的值一共有8種。其中只保留正常、欠費暫停、主動暫停和主動銷戶的用戶，其余的值不需要進行分析處理。

探索無效用戶數(shù)據(jù)在以用戶狀態(tài)變更數(shù)據(jù)表和訂單數(shù)據(jù)表統(tǒng)計run_name字段值的統(tǒng)計結(jié)果中，同樣存在多種類型，也只需保留正常、欠費暫停、主動暫停和主動銷戶的用戶數(shù)據(jù)。用戶狀態(tài)變更數(shù)據(jù)表、訂單數(shù)據(jù)表與用戶基本數(shù)據(jù)表run_name字段值，如下所示。數(shù)據(jù)表run_name字段值用戶基本數(shù)據(jù)表主動暫停、主動銷戶、沖正、創(chuàng)建、欠費暫停、正常、被動銷戶、銷號用戶狀態(tài)變更數(shù)據(jù)表主動暫停、主動銷戶、沖正、創(chuàng)建、欠費暫停、正常、被動銷戶訂單數(shù)據(jù)表主動暫停、主動銷戶、沖正、創(chuàng)建、欠費暫停、正常、被動銷戶、未激活、BG、BY、DB、DG、DI、GI、GY、NULL、Y、YB、YD、YG、YI、YN、YY、dd

刪除無效用戶數(shù)據(jù)通過對廣電用戶數(shù)據(jù)進行無效用戶數(shù)據(jù)的探索，查詢出許多無效的用戶數(shù)據(jù)。本小節(jié)的任務是清洗無效用戶數(shù)據(jù)，任務實現(xiàn)步驟如下。用戶去重。經(jīng)統(tǒng)計，并無重復的用戶記錄，無須處理。清洗特殊線路用戶數(shù)據(jù)，即清洗各表owner_code字段值為2、9或10的記錄。清洗政企用戶數(shù)據(jù)，即清洗各表中owner_name字段值為EA級、EB級、EC級、ED級或EE級的政企用戶數(shù)據(jù)。只保留用戶基本數(shù)據(jù)表、賬單數(shù)據(jù)表、訂單數(shù)據(jù)表和用戶收視行為數(shù)據(jù)表中sm_name字段值為數(shù)字電視、互動電視、珠江寬頻和甜果電視的數(shù)據(jù)。只保留用戶基本數(shù)據(jù)表、用戶狀態(tài)變更數(shù)據(jù)表、訂單數(shù)據(jù)表中run_name字段值為正常、欠費暫停、主動暫停和主動銷戶的用戶數(shù)據(jù)。刪除無效用戶數(shù)據(jù)本任務以清洗用戶基本數(shù)據(jù)表中的數(shù)據(jù)為例，實現(xiàn)以上數(shù)據(jù)清洗要求。因為所創(chuàng)建的5張廣電用戶數(shù)據(jù)Hive表均為普通的內(nèi)部表，所以將使用篩選有效數(shù)據(jù)導入另一個表的方式實現(xiàn)數(shù)據(jù)清洗。創(chuàng)建一個mediamatch_usermsg_clean表，將無效的數(shù)據(jù)剔除，將有效數(shù)據(jù)導入表中，實現(xiàn)數(shù)據(jù)清洗。如果需要驗證mediamatch_usermsg_clean表中的數(shù)據(jù)是否有效，可以通過分組查詢owner_code、owner_name、sm_name和run_name字段值驗證是否還存在無效數(shù)據(jù)。以驗證mediamatch_usermsg_clean表中sm_name字段值是否全為數(shù)字電視、互動電視、珠江寬頻或甜果電視為例。

清洗無效用戶數(shù)據(jù)清洗無效收視行為數(shù)據(jù)清洗無效賬單和訂單數(shù)據(jù)導出處理結(jié)果至Linux本地和HDFS清洗無效收視行為數(shù)據(jù)信息科技發(fā)展迅速，無論是硬件還是軟件每天都在更新，在計算機、手機上使用各種視頻軟件或App即可實現(xiàn)節(jié)目觀看，達到取代電視機觀看的結(jié)果。因此，新時代的人們使用電視機觀看節(jié)目的時間逐漸減少，甚至某些家庭已經(jīng)不安裝電視機了。對此，廣電公司急需研究用戶收視行為數(shù)據(jù)，分析用戶的興趣，提高電視機的使用率。在分析用戶行為數(shù)據(jù)之前，應先進行無效收視行為數(shù)據(jù)探索，以免造成分析結(jié)果出現(xiàn)嚴重錯誤。本任務探索用戶收視行為數(shù)據(jù)表中觀看時長duration、節(jié)目類型res_type、用戶觀看開始時間origin_time和用戶觀看結(jié)束時間end_time字段的無效收視行為數(shù)據(jù)并將其進行刪除。探索無效收視行為數(shù)據(jù)用戶在觀看電視節(jié)目時，常常為了找到喜歡的節(jié)目而頻繁地切換，這時將產(chǎn)生大量的觀看時長較短的數(shù)據(jù)，分析這樣的數(shù)據(jù)對研究用戶觀看興趣有較大的影響，因此需要探索并清除。此外，許多用戶只是關(guān)閉了顯示設備，轉(zhuǎn)而進行另外的個人活動，而連接終端還處于觀看狀態(tài)，因此也將產(chǎn)生大量的觀看時長過長的數(shù)據(jù)，同樣地，這樣的數(shù)據(jù)也屬于無效數(shù)據(jù)。探索無效收視行為數(shù)據(jù)為獲得更有分析價值的用戶收視行為數(shù)據(jù)，需要探索無效收視行為數(shù)據(jù)并將其清洗，探索過程如下。1．統(tǒng)計用戶收視行為記錄觀看時長的均值、最值和標準差為了掌握用戶收視行為記錄中的觀看時長的取值范圍，以便后續(xù)業(yè)務需求探索中為用戶收視行為無效數(shù)據(jù)的分析探索提供幫助，而且由于用戶收視行為數(shù)據(jù)表中的記錄數(shù)較多，所以有必要對用戶觀看時長進行基本的探索分析。使用AVG、MIN、MAX與STDDEV函數(shù)分別統(tǒng)計用戶觀看時長的均值、最小值、最大值和標準差，由于duration字段記錄的是用戶觀看時間（以秒位單位）乘以1000的值，所以duration字段的值需要除以1000以得到以秒為單位的用戶觀看時長。其中duration字段值需要使用CAST函數(shù)轉(zhuǎn)換成DOUBLE類型的值。探索無效收視行為數(shù)據(jù)從右圖所示的統(tǒng)計結(jié)果可以發(fā)現(xiàn)，用戶收視行為數(shù)據(jù)表中平均每條記錄的觀看時長約為1104秒（約為18分鐘）。記錄中觀看時長最小值約為0秒，觀看時長的最大值為17992秒（約為5小時），標準差約為1439秒（約為24分鐘）。統(tǒng)計結(jié)果說明了用戶觀看時長的范圍（0秒～5小時）是比較大的，觀看時長的離散程度較?。ㄓ^看時長的標準差約為24分鐘）。探索無效收視行為數(shù)據(jù)2．統(tǒng)計用戶收視時長分布用戶收視行為無效數(shù)據(jù)是指用戶觀看時長過短或過長的數(shù)據(jù)，這種數(shù)據(jù)出現(xiàn)的原因可能是用戶頻繁切換頻道或只關(guān)閉電視機而忘記關(guān)閉機頂盒。在用戶收視行為數(shù)據(jù)表中，duration字段記錄了用戶的每次觀看時長。由于各記錄的觀看時長差異較大，所以需要將觀看時長以每小時為區(qū)間進行劃分，統(tǒng)計各區(qū)間的記錄數(shù)。首先使用COUNT函數(shù)統(tǒng)計用戶收視行為數(shù)據(jù)表的總記錄數(shù)，可參考代碼實現(xiàn)，統(tǒng)計結(jié)果為4754442條。接著統(tǒng)計觀看時長以每小時為區(qū)間的記錄數(shù)，因為duration字段記錄的是用戶觀看時間（以秒為單位）乘以1000的值，所以將duration字段的值除以1000×60×60即可得到觀看時長以小時為單位的值，其中子查詢語句中的FLOOR函數(shù)的作用是向下取整，結(jié)果如圖所示。探索無效收視行為數(shù)據(jù)據(jù)圖所示的統(tǒng)計結(jié)果可知，絕大部分的觀看時長都小于1小時，約占總記錄數(shù)的94%，觀看時長大于等于1小時小于2小時的記錄數(shù)約占總數(shù)的5.9%。由于觀看時長小于1小時的記錄數(shù)占了絕大部分，所以將這部分記錄再按1分鐘為時間間隔進行劃分，分析落在每個區(qū)間的記錄數(shù)分布情況，結(jié)果如圖所示。探索無效收視行為數(shù)據(jù)由圖可得用戶觀看時長記錄數(shù)隨著時間間隔而大約呈現(xiàn)出指數(shù)遞減的趨勢，其中觀看時長小于1分鐘的數(shù)據(jù)最多，約占總記錄數(shù)的18%。為了進一步了解觀看時長小于1分鐘的秒級數(shù)據(jù)分布情況，再將這部分數(shù)據(jù)按秒進行劃分，結(jié)果如圖所示。探索無效收視行為數(shù)據(jù)左圖只展現(xiàn)了1～10s的記錄數(shù)，為了更直觀地展現(xiàn)1分鐘內(nèi)每秒的記錄數(shù)和分布情況，將統(tǒng)計結(jié)果保存至Linux本地系統(tǒng)并使用Python、Excel或MATLAB等工具制作折線圖，如右圖所示。由右圖可得，觀看時長在1～19秒的每個區(qū)間內(nèi)的觀看記錄數(shù)相差不大，從20秒開始每個區(qū)間的記錄數(shù)遠高于1～19秒每個區(qū)間的記錄數(shù)。綜合以上的分析統(tǒng)計結(jié)果以及結(jié)合業(yè)務的實際情況，將觀看時長小于20秒和大于5小時的數(shù)據(jù)視為無效數(shù)據(jù)，需要將這些數(shù)據(jù)刪除以便能夠更好地分析用戶的收視行為。探索無效收視行為數(shù)據(jù)3．查詢機頂盒自動返回的數(shù)據(jù)在用戶收視行為數(shù)據(jù)表中，還有一部分數(shù)據(jù)的節(jié)目類型為直播，即res_type字段值為0時，觀看行為開始時間origin_time和觀看行為結(jié)束時間end_time的秒時間單位為00結(jié)尾的記錄，這些記錄是機頂盒自動返回的數(shù)據(jù)，并不是用戶真實的觀看記錄。因此這一部分數(shù)據(jù)也是需要刪除的。探索分析由機頂盒自動返回的數(shù)據(jù)以及其數(shù)據(jù)量的大小，使用“LIKE'%00'”語句即可查詢某字段以00結(jié)尾的數(shù)據(jù)，結(jié)果如圖所示。探索無效收視行為數(shù)據(jù)從圖所示的統(tǒng)計分析結(jié)果來看，用戶收視行為數(shù)據(jù)表中res_type字段值為0時，origin_time和end_time的秒時間單位為00的記錄的確存在并且記錄數(shù)約為88萬，因此在進行數(shù)據(jù)預處理時需要清洗這部分無效的數(shù)據(jù)。刪除無效收視行為數(shù)據(jù)對廣電用戶收視行為數(shù)據(jù)表的探索，主要是探索用戶的觀看時長，將無效的收視數(shù)據(jù)進行清洗。本小節(jié)的任務是獲得更有分析價值的用戶收視行為數(shù)據(jù)，探索無效收視行為數(shù)據(jù)并將其進行清洗，任務實現(xiàn)步驟如下。統(tǒng)計用戶觀看時長的均值、最值和標準差，探索用戶觀看時長范圍。由圖所示的統(tǒng)計結(jié)果可得，用戶的觀看時長范圍為0～5小時。根據(jù)統(tǒng)計用戶觀看時長分布結(jié)果分析，刪除觀看時長小于20秒和觀看時長大于5小時的數(shù)據(jù)。刪除節(jié)目類型為直播，即res_type字段值為0時，觀看行為開始時間origin_time和觀看行為結(jié)束時間end_time為00的無效收視行為數(shù)據(jù)。刪除無效收視行為數(shù)據(jù)如果需要驗證media_index_clean表中的數(shù)據(jù)是否有效，可以通過查詢觀看時長duration字段的最大值、最小值，以及節(jié)目類型res_type字段是否還存在無效數(shù)據(jù)實現(xiàn)。以驗證media_index_clean表中數(shù)據(jù)是否有效為例，結(jié)果如圖所示，可知media_index_clean不存在直播的節(jié)目類型。清洗無效用戶數(shù)據(jù)清洗無效收視行為數(shù)據(jù)清洗無效賬單和訂單數(shù)據(jù)導出處理結(jié)果至Linux本地和HDFS清洗無效賬單和訂單數(shù)據(jù)賬單是指與消費者發(fā)生交易的商戶或公司向消費者提供的賬目發(fā)生明細單，也是商戶或公司記錄和統(tǒng)計營收的數(shù)據(jù)依據(jù)。訂單是訂購貨物的合同或單據(jù)。用戶在挑選商品后，在實體店前臺或在網(wǎng)上下單，這時需要打印訂單表。訂單表記錄了用戶的訂購產(chǎn)品詳細信息，一般包括用戶名稱、訂購貨物名稱、訂購金額和訂購數(shù)量等。清洗無效賬單和訂單數(shù)據(jù)廣電公司的用戶賬單數(shù)據(jù)表和訂單數(shù)據(jù)表分別記錄了各用戶的消費詳情和訂購產(chǎn)品信息。這兩個表中存在無效數(shù)據(jù)，在進行營收統(tǒng)計分析時，若采用了無效的訂單和賬單數(shù)據(jù)，則將導致統(tǒng)計錯誤，因此需要對用戶賬單數(shù)據(jù)表和訂單數(shù)據(jù)表進行數(shù)據(jù)清洗。本任務探索用戶賬單數(shù)據(jù)表中用戶應付金額should_pay字段和用戶訂單數(shù)據(jù)表中訂購產(chǎn)品價格cost字段的無效數(shù)據(jù)并進行清洗。探索無效賬單數(shù)據(jù)無效賬單數(shù)據(jù)是指賬單數(shù)據(jù)表mmconsume_billevents的用戶應付金額should_pay字段值小于0的數(shù)據(jù)。若在統(tǒng)計營收金額時將應收金額小于0的數(shù)據(jù)算入，則將造成營收統(tǒng)計錯誤。查詢無效賬單的數(shù)量，結(jié)果如圖所示。由圖所示的統(tǒng)計結(jié)果可得，賬單數(shù)據(jù)表中存在377條無效賬單數(shù)據(jù)，需要將這些無效賬單數(shù)據(jù)清洗。

探索無效訂單數(shù)據(jù)無效訂單數(shù)據(jù)是指訂單數(shù)據(jù)表order_index的訂購產(chǎn)品價格cost字段值為空或小于0的數(shù)據(jù)。若在統(tǒng)計用戶訂單金額時將無效訂購產(chǎn)品價格算入，將造成錯誤的收入預算，會對公司資產(chǎn)調(diào)用和計劃支出造成重大影響。查詢無效訂單數(shù)據(jù)的數(shù)量。執(zhí)行代碼中的代碼得出無效訂單數(shù)據(jù)的數(shù)量為0，即不存在訂購產(chǎn)品價格cost字段值為空或小于0的數(shù)據(jù)，因此無須清洗，如圖所示。刪除無效賬單和無效訂單數(shù)據(jù)本小節(jié)任務是刪除無效賬單和無效訂單數(shù)據(jù)，經(jīng)探索分析得用戶訂單數(shù)據(jù)表中不存在無效訂單數(shù)據(jù)，而用戶賬單數(shù)據(jù)表中存在無效賬單數(shù)據(jù)，因此需刪除無效賬單數(shù)據(jù)即可。先創(chuàng)建mmconsume_billevents_clean表，再刪除賬單數(shù)據(jù)表中的無效賬單數(shù)據(jù)，最后將清洗好的數(shù)據(jù)導入mmconsume_billevents_clean表中，如圖?？蓞⒖即a統(tǒng)計mmconsume_billevents_clean中應收金額小于0的數(shù)據(jù)量，驗證是否刪除了無效賬單數(shù)據(jù)，若統(tǒng)計值為0，則完成了刪