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

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

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

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

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