廣電用戶數(shù)據清洗及數(shù)據導出任務背景大數(shù)據分析結果的有效性在很大程度上依賴于所處理數(shù)據的質量，使用合理的方法分析高質量的數(shù)據將得到準確的結果。數(shù)據的質量對任何依賴于該數(shù)據的應用所獲得結果有重要影響。數(shù)據的不完整性、不一致性、重復性和無效性等是低質量數(shù)據的重要特征。例如，使用歐洲的用戶畫像衡量中國的用戶畫像，因為數(shù)據描述的對象并不對應，所以這些數(shù)據是無效的、不準確的。因此，一般在數(shù)據分析、數(shù)據挖掘之前，需要進行數(shù)據探索，即探索數(shù)據的完整性、一致性、重復性和合理性等，若發(fā)現(xiàn)無效數(shù)據，則應進行數(shù)據清洗，為后續(xù)的數(shù)據分析處理工作提供高質量的數(shù)據。任務背景在前面章節(jié)中，使用的是廣電用戶的原始數(shù)據，通過數(shù)據查詢發(fā)現(xiàn)其中存在許多缺失和異常的數(shù)據，如大量數(shù)據字段中包含NULL值。在統(tǒng)計字段中的類型數(shù)量時也會對NULL值進行計算，造成數(shù)據分析結果的不準確。因此，需要對廣電用戶數(shù)據中的不符合案例分析要求的數(shù)據，即無效數(shù)據，進行清洗并將清洗后的數(shù)據進行保存。任務背景本章將對廣電用戶數(shù)據進行探索，尋找出各表中的無效數(shù)據，進行數(shù)據清洗并將清洗后的數(shù)據進行保存。本章將先探索無效的用戶數(shù)據，如統(tǒng)計重復的用戶數(shù)和探索特殊線路用戶數(shù)據等，其次探索無效的收視行為數(shù)據，分析用戶收視行為特征，篩選有效數(shù)據，接著探索無效賬單和訂單數(shù)據，最后將清洗好的數(shù)據進行保存。數(shù)據清洗的過程是比較煩瑣的，但需細致入微、踔厲奮發(fā)、勇毅前行，為實現(xiàn)任務而努力、堅持。清洗無效用戶數(shù)據清洗無效收視行為數(shù)據清洗無效賬單和訂單數(shù)據導出處理結果至Linux本地和HDFS清洗無效用戶數(shù)據在進行廣電用戶數(shù)據分析時，需要研究大眾用戶的行為特征。一般而言，政企用戶、內部通信用戶、測試用戶和辦理了銷號的用戶等都是無效用戶，因此，需要對無效用戶數(shù)據進行清洗。本任務探索廣電用戶數(shù)據中用戶編號phone_no、用戶等級編號owner_code、用戶等級名稱owner_name、品牌名稱sm_name和狀態(tài)名稱run_name這些字段中的無效用戶數(shù)據并進行清洗。探索無效用戶數(shù)據在廣電用戶數(shù)據中，存在大量的無效用戶數(shù)據，需要進行數(shù)據探索，查找無效用戶數(shù)據，然后刪除無效用戶數(shù)據，實現(xiàn)數(shù)據清洗。探索過程如下。1．統(tǒng)計重復的用戶數(shù)探索用戶基本數(shù)據表中是否存在重復記錄的用戶先統(tǒng)計用戶基本數(shù)據表中每個用戶記錄數(shù)，結合統(tǒng)計出的結果，觀察是否存在重復記錄的用戶再分組統(tǒng)計每個用戶編號phone_no的記錄數(shù)，并按記錄數(shù)降序排列，取前10條數(shù)據，發(fā)現(xiàn)用戶基本數(shù)據表中不存在記錄數(shù)大于1的用戶，且phone_no分組統(tǒng)計記錄數(shù)，將結果按降序排列，得到結果中所有的phone_no都唯一。探索無效用戶數(shù)據2．探索特殊線路用戶數(shù)據根據業(yè)務人員提供的數(shù)據，用戶等級編號owner_code字段含有多個取值，其中值為2、9或10的記錄是特殊路線的用戶的數(shù)據，特殊線路是用于用戶測試、產品檢驗的。保存廣電用戶數(shù)據的5個表中都存在owner_code字段，對這5個表中是否存在特殊線路的用戶及其數(shù)量進行分析，按照owner_code字段分組后，再統(tǒng)計該字段各值的記錄數(shù)。探索無效用戶數(shù)據以統(tǒng)計用戶基本數(shù)據表的owner_code字段值的結果為例，如圖所示，發(fā)現(xiàn)owner_code存在字段值為2的數(shù)據，且所占的比例較小。此外owner_code字段還存在空值（NULL），經過與業(yè)務人員溝通確認owner_code字段存在空值是正常的。探索無效用戶數(shù)據對其余4個表統(tǒng)計owner_code字段值，發(fā)現(xiàn)存在2、9或10，因此各表都需要清洗owner_code字段值為2、9或10的記錄，各表的owner_code字段值如表所示。數(shù)據表owner_code字段值用戶基本數(shù)據表0、15、2、5、6、7、8、NULL用戶狀態(tài)變更數(shù)據表0、15、2、5、6、8、NULL賬單數(shù)據表0、1、15、2、30、31、4、5、6、7、8、9、NULL訂單數(shù)據表0、1、10、15、2、30、31、4、5、6、7、8、9、NULL用戶收視行為數(shù)據表0、8、15、1、5、2、6、31、7、10、NULL探索無效用戶數(shù)據3．探索政企用戶數(shù)據由于廣電公司的用戶主要是家庭用戶，所以政企用戶不納入分析范圍。根據業(yè)務人員提供的數(shù)據，政企用戶的標識是用戶等級名稱owner_name字段值為EA級、EB級、EC級、ED級或EE級。根據第3章的數(shù)據說明所提供的數(shù)據，保存廣電用戶數(shù)據的5個表中都存在owner_name字段，需要探索這些表中是否存在政企用戶以及存在的數(shù)量。按照owner_name字段進行分組，再統(tǒng)計該字段各值的記錄數(shù)。探索無效用戶數(shù)據執(zhí)行代碼中的代碼可得出每個表的owner_name字段值的記錄數(shù)，每個表存在的owner_name字段值都不一致。以用戶基本數(shù)據表的owner_name字段值為例，存在EA級、EB級和EE級。探索無效用戶數(shù)據且owner_name字段值為HC級的記錄數(shù)最多，而政企用戶的數(shù)量較少，這也印證了廣電公司的用戶主要是家庭用戶。用戶基本信息表owner_name字段數(shù)據統(tǒng)計情況。數(shù)據表owner_name字段值用戶基本數(shù)據表EA級、EB級、EE級、HA級、HB級、HC級、HE級用戶狀態(tài)變更數(shù)據表EA級、EB級、EE級、HA級、HB級、HC級賬單數(shù)據表EA級、EB級、EE級、HA級、HB級、HC級、HE級訂單數(shù)據表EA級、EB級、EE級、HA級、HB級、HC級、HE級、NULL用戶收視行為數(shù)據表EA級、EE級、HA級、HB級、HC級、HE級探索無效用戶數(shù)據從3.1.4小節(jié)可知，本書使用的數(shù)據是一段時間內的廣電業(yè)務數(shù)據，而在實際的業(yè)務數(shù)據庫中，各信息表中可能會出現(xiàn)owner_name字段值為EC級或ED級的政企用戶記錄。因此在進行數(shù)據預處理時，需要清洗owner_name字段值為EA級、EB級、EC級、ED級和EE級的政企用戶。探索無效用戶數(shù)據4．統(tǒng)計sm_name字段值廣電公司目前的業(yè)務類型主要是數(shù)字電視、互動電視、珠江寬頻、模擬有線電視和甜果電視這5種，品牌名稱可以通過sm_name字段進行標識。除了用戶狀態(tài)變更數(shù)據表，其余4個表都含有sm_name字段。下面以統(tǒng)計用戶基本數(shù)據表中的所有業(yè)務類型、每種類型的用戶數(shù)以及每種類型的用戶數(shù)占比為例，實現(xiàn)sm_name字段數(shù)據探索，操作步驟如下。探索無效用戶數(shù)據首先統(tǒng)計用戶基本數(shù)據表的總記錄數(shù)，將其作為后續(xù)統(tǒng)計sm_name字段值數(shù)量占比的分母。接著按sm_name字段分組統(tǒng)計該字段各值的數(shù)量及其占比，統(tǒng)計用戶基本數(shù)據表的sm_name字段各值的數(shù)量及其占比，統(tǒng)計用戶基本數(shù)據表中sm_name字段各值的數(shù)量及其占比，結果顯示，sm_name字段的值一共有5種，且模擬有線電視的用戶最多，約占總數(shù)的49%，其次數(shù)字電視的用戶約占30%?，F(xiàn)在的主要業(yè)務是互動電視、數(shù)字電視、甜果電視、珠江寬頻，這四者約占總數(shù)的50%，需要保留這4種業(yè)務類型的用戶，刪除其他業(yè)務類型的用戶。探索無效用戶數(shù)據5．篩選正常、欠費暫停、主動暫停和主動銷戶的用戶數(shù)據根據業(yè)務要求，除了需要篩選指定品牌名稱的用戶外，還需要對狀態(tài)名稱進行過濾，只保留狀態(tài)名稱為正常、欠費暫停、主動暫停和主動銷戶的用戶，其余的狀態(tài)名稱不需要進行分析處理。狀態(tài)名稱的字段標識為run_name，只有用戶狀態(tài)變更數(shù)據表、訂單數(shù)據表與用戶基本數(shù)據表含有run_name字段。以對用戶基本數(shù)據中的用戶狀態(tài)進行探索為例，實現(xiàn)run_name字段數(shù)據探索，按照run_name字段分組后，再統(tǒng)計該字段各值的記錄數(shù)。探索無效用戶數(shù)據代碼得到的統(tǒng)計結果顯示run_name字段的值一共有8種。其中只保留正常、欠費暫停、主動暫停和主動銷戶的用戶，其余的值不需要進行分析處理。

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

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

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

探索無效訂單數(shù)據無效訂單數(shù)據是指訂單數(shù)據表order_index的訂購產品價格cost字段值為空或小于0的數(shù)據。若在統(tǒng)計用戶訂單金額時將無效訂購產品價格算入，將造成錯誤的收入預算，會對公司資產調用和計劃支出造成重大影響。查詢無效訂單數(shù)據的數(shù)量。執(zhí)行代碼中的代碼得出無效訂單數(shù)據的數(shù)量為0，即不存在訂購產品價格cost字段值為空或小于0的數(shù)據，因此無須清洗，如圖所示。刪除無效賬單和無效訂單數(shù)據本小節(jié)任務是刪除無效賬單和無效訂單數(shù)據，經探索分析得用戶訂單數(shù)據表中不存在無效訂單數(shù)據，而用戶賬單數(shù)據表中存在無效賬單數(shù)據，因此需刪除無效賬單數(shù)據即可。先創(chuàng)建mmconsume_billevents_clean表，再刪除賬單數(shù)據表中的無效賬單數(shù)據，最后將清洗好的數(shù)據導入mmconsume_billevents_clean表中，如圖。可參考代碼統(tǒng)計mmconsume_billevents_clean中應收金額小于0的數(shù)據量，驗證是否刪除了無效賬單數(shù)據，若統(tǒng)計值為0，則完成了刪除無效賬單數(shù)據，如圖。清洗無效用戶數(shù)據清洗無效收視行為數(shù)據清洗無效賬單和訂單數(shù)據導出處理結果至Linux本地和HDFS導出處理結果至Linux本地和HDFS隨著移動互聯(lián)網和物聯(lián)網的出現(xiàn)和快速發(fā)展，數(shù)據量也在爆發(fā)式增長，對應的數(shù)據存儲量也在增大，因此誕生了分布式存儲框架，用于解決大數(shù)據量的存儲問題。廣電公司在清洗完數(shù)百萬條數(shù)據后，需要將數(shù)據進行保存。本任務將清洗后的數(shù)據保存在Linux本地目錄和HDFS目錄下。使用INSERTOVERWRITE語句將數(shù)據導出至文件系統(tǒng)在HiveCLI中實現(xiàn)Hive表數(shù)據的導出，可以使用INSERTOVERWRITE語句編寫選擇導出的文件系統(tǒng)目錄和表內容。使用INSERTOVERWRITE語句將數(shù)據導出至文件系統(tǒng)目錄的語法如下。參數(shù)說明DIRECTORY在參數(shù)后指定數(shù)據導出的目錄LOCAL若使用LOCAL參數(shù)，則Hive會將數(shù)據寫入Linux本地目錄下。若不使用LOCAL參數(shù)，則將數(shù)據寫入HDFS目錄下ROWFORMATHive0.11.0開始允許指定字段間的分隔符，默認按^A字符(\001)分隔STOREDAS用于指定文件存儲格式，如TXT文檔或ORC優(yōu)化行柱狀文件等SELECT...FROM...用于選擇存儲內容

使用INSERTOVERWRITE語句將數(shù)據導出至文件系統(tǒng)使用INSERTOVERWRITE語句和LOCALDIRECTORY參數(shù)將用代碼所創(chuàng)建的賬單數(shù)據清洗表mmconsume_billevents_clean中的數(shù)據導出至Linux本地目錄/opt/zjsm_clean/mmconsume_billevents_clean（目錄需先手動創(chuàng)建）下，如圖。執(zhí)行代碼，在Linux本地目錄/opt/zjsm_clean/mmconsume_billevents_clean下生成一個名為000000_0的文件，文件記錄了mmconsume_billevents_clean表中的數(shù)據。在Linux終端查看000000_0文件的前10條數(shù)據，結果如圖。保存處理結果至Linux本地和HDFS本章對廣電用戶數(shù)據的5個表進行了無效數(shù)據探索并清洗，其中數(shù)據清洗規(guī)則如圖。數(shù)據表數(shù)據清洗規(guī)則賬單數(shù)據表訂單數(shù)據表用戶基本數(shù)據表用戶收視行為數(shù)據表刪除owner_name字段值為EA級、EB級、EC級、ED級、EE級的數(shù)據。刪除owner_code字段值為2、9、10的數(shù)據。保留sm_name字段值為珠江寬頻、數(shù)字電視、互動電視、甜果電視的數(shù)據。用戶狀態(tài)變更數(shù)據表訂單數(shù)據表用戶基本數(shù)據表保留run_name字段值為正常、主動暫停、欠費暫停、主動銷戶的數(shù)據。用戶基本數(shù)據表用戶編號phone_no數(shù)據去重賬單數(shù)據表刪除應收金額should_pay字段值小于0的記錄用戶收視行為數(shù)據表收視時長duration字段值大于等于20秒且duration字段值小于等于5小時的數(shù)據。刪除用戶收視行為數(shù)據表中res_type字段值為0時，origin_time和end_time中為00的數(shù)據（以秒為單位）用戶狀態(tài)變更數(shù)據表刪除owner_name字段值為EA級、EB級、EC級、ED級、EE級。刪除owner_code字段值為2、9、10的數(shù)據。保存處理結果至Linux本地和HDFSLinux本地文件系統(tǒng)的安全性較好，且傳輸數(shù)據文件較為方便，如將文件傳至Windows系統(tǒng)時，無須啟動Hadoop。HDFS能夠解決大數(shù)據量的存儲問題，且存儲效率較高。因此本小節(jié)將實現(xiàn)保存Hive清洗后的數(shù)據表至Linux本地目錄下和HDFS目錄下。以保存清洗后的用戶基本數(shù)據表mediamatch_usermsg_clean為例，使用INSERTOVERWRITE語句實現(xiàn)保存至Linux的/opt/zjsm_clean/mediamatch_usermsg_clean目錄下和HDFS的/opt/zjsm_clean/mediamatch_usermsg_clean目錄下。保存處理結果至Linux本地和HDFS小結本章的目標是先探索廣電用戶無效數(shù)據，再進行數(shù)據清洗與保存。首先探索了無效用戶數(shù)據，如探索重復的用戶數(shù)、特殊線路用戶數(shù)據和政企用戶數(shù)據等。其次探索了無效收視行為數(shù)據，主要探索用戶觀看時長。接著探索了無效賬單和訂單數(shù)據，探索用戶應付金額should_pay字段和訂購產品價格cost字段是否存在小于0的數(shù)據，再按照探索結果進行數(shù)據清洗。最終將清洗結果保存至Linux本地目錄和HDFS目錄。廣電用戶數(shù)據存儲與

處理的程序開發(fā)任務背景Hive與一般數(shù)據倉庫使用傳統(tǒng)關系數(shù)據庫作為基礎平臺不同，Hive基于Hadoop平臺構建，這使得Hive天然具備大數(shù)據處理能力。在之前的學習和實踐中，使用Hive都是通過CLI的方式，該方式僅允許使用HQL執(zhí)行查詢等操作，并且該方式比較笨拙、單一。其實Hive也提供了輕客戶端的實現(xiàn)，通過HiveServer或HiveServer2，客戶端可以在不啟動CLI的情況下對Hive中的數(shù)據進行操作，兩者都允許遠程客戶端使用多種編程語言如Java、Python向Hive提交請求，返回結果。學習多種方式操作Hive中的數(shù)據，落實科教興國戰(zhàn)略。任務背景本章主要介紹如何配置Hive遠程服務，搭建Hive遠程開發(fā)環(huán)境，通過案例實現(xiàn)廣電數(shù)據的存儲和廣電用戶數(shù)據的查詢與處理，并在此過程中學習通過IDEA編程軟件進行程序運行、調試的主要過程和方法。配置Hive遠程服務搭建Hive遠程連接環(huán)境編寫程序實現(xiàn)廣電數(shù)據的存儲編寫程序實現(xiàn)廣電數(shù)據的查詢與處理任務描述在HiveCLI中，一次只能運行一條HQL語句，對于單詞、語句編寫失誤造成的運行失敗也無法立刻修改，只能重新編寫語句，因此效率偏低。在編程類的軟件中進行HQL語句的編寫、運行，可以避免上述問題的出現(xiàn)。為了實現(xiàn)Hive程序調用，需要提前配置和啟動Hive遠程服務。本任務配置Hive遠程服務HiveServer2。配置Hive遠程服務Hive0.10.0版本具有一個可選的組件HiveServer，用于為Hive提供一種允許客戶端遠程訪問的服務。HiveServer基于Thrift協(xié)議，故也稱HiveServer為ThriftServer。HiveServer支持跨平臺、跨編程語言對Hive進行訪問；由于HiveServer受Thrift接口限制，所以HiveServer不能處理多于一個客戶端的并發(fā)請求。為實現(xiàn)多用戶并發(fā)訪問，Hive0.11.0版本重寫HiveServer代碼得到了HiveServer2。HiveServer2支持多客戶端的并發(fā)和認證，用于為客戶端通過API（例如JDBC、ODBC等）訪問Hive提供更好的支持。配置Hive遠程服務配置并啟動Hive遠程服務的操作如下。配置Hive遠程連接屬性。在master主節(jié)點執(zhí)行命令“vim/usr/local/hadoop-3.1.4/etc/hadoop/core-site.xml”，打開core-site.xml文件，然后進入編輯模式，添加Hive遠程服務的屬性，Hive遠程服務的屬性內容，修改好后按Esc鍵，輸入“:wq”并按Enter鍵保存退出。<property> <name>xyuser.root.hosts</name> <value>*</value></property><property> <name>xyuser.root.groups</name> <value>*</value></property>配置Hive遠程服務分發(fā)配置文件至各子節(jié)點。將core-site.xml文件使用scp命令發(fā)送給各子節(jié)點。啟動Hive遠程服務。啟動Hadoop集群和MySQL服務后，啟動Hive元數(shù)據庫服務、HiveServer2遠程服務，運行結果如下圖。配置Hive遠程服務啟動遠程服務之前，要啟動Hive元數(shù)據服務，否則在后續(xù)連接Hive，執(zhí)行一系列數(shù)據操作時，編程軟件會提示“Connectionrefused:connect”的錯誤。配置Hive遠程服務搭建Hive遠程連接環(huán)境編寫程序實現(xiàn)廣電數(shù)據的存儲編寫程序實現(xiàn)廣電數(shù)據的查詢與處理任務描述IntelliJIDEA簡稱IDEA，是Java等編程語言開發(fā)集成環(huán)境之一，該集成環(huán)境在智能代碼助手、代碼自動提示、重構、JavaEE支持、各類版本工具（Git、SVN等）、JUnit、CVS整合、代碼分析、創(chuàng)新的GUI設計等方面較為優(yōu)秀。本任務在IDEA上搭建Hive開發(fā)環(huán)境，并實現(xiàn)Hive遠程連接測試。創(chuàng)建IDEA開發(fā)項目讀者可到JetBrains公司的官方網站自行下載相關版本的IDEA編程軟件安裝包。對于個人用戶，可下載Community版（社區(qū)版）的IDEA編程軟件安裝包。下載后，請按照指示進行安裝。本書統(tǒng)一使用“IntelliJIDEACommunityEdition2021.3.3”版本。IDEA默認選用英文界面運行，讀者也可根據個人需求在插件管理中下載中文語言包。為了描述方便，本書統(tǒng)一以英文界面進行講解。創(chuàng)建IDEA開發(fā)項目在安裝好編程軟件后，即可創(chuàng)建IDEA開發(fā)項目，創(chuàng)建流程如下。打開編程軟件IDEA，彈出歡迎界面，如下圖，單擊“NewProject”按鈕。創(chuàng)建IDEA開發(fā)項目在“NewProject”界面中選擇“Maven”選項，在“ProjectSDK”下拉列表框中選擇1.8版本的JDK，如下圖，單擊“Next”按鈕。創(chuàng)建IDEA開發(fā)項目將項目命名為“HiveJavaAPI”，并將該項目放置在D盤根目錄下，如下圖，單擊“Finish”按鈕。創(chuàng)建IDEA開發(fā)項目項目創(chuàng)建完成后，會自動生成項目框架，如下圖。創(chuàng)建IDEA開發(fā)項目項目初始框架包含的項目元素如下表。項目元素說明項目根目錄項目存儲的本地目錄，項目HiveJavaAPI的安裝目錄為D:\HiveJavaAPI.idea節(jié)點主要用于保存IDEA項目的相關信息src節(jié)點用于保存源代碼（main目錄）和測試代碼（test目錄）ExternalLibraries節(jié)點用于保存使用到的外部庫文件鏈接ScratchesandConsoles可提供Scratchfiles和Scratchbuffers這兩種臨時的編輯環(huán)境，在臨時的編輯環(huán)境中，讀者可以通過編寫一些文本內容或一些代碼片段，實現(xiàn)IDEA功能測試創(chuàng)建IDEA開發(fā)項目項目具體結構如右圖。添加依賴創(chuàng)建好項目后，需要在項目的pom.xml文件中添加Hive相關依賴。在pom.xml文檔界面中，單擊鼠標右鍵選擇“Maven”命令，再選擇“Reloadproject”命令，如右圖，可立即加載依賴。加載完成后，可在左邊Project工具欄中單擊“ExternalLibraries”查看。添加依賴加載完成后，相關的依賴包會默認保存到系統(tǒng)用戶目錄下的.m2/repository子目錄下，如下圖。手動加載MySQL驅動在加載MySQL驅動前，需要在MySQLJAR包下載官網中提前下載驅動JAR包“mysql-connector-java-8.0.20.jar”，并將其復制到本地磁盤的“HiveJavaAPI連接驅動”目錄下。在創(chuàng)建好的IDEA項目里加載MySQL驅動的流程如下。單擊菜單“File”，選擇“ProjectStructure”選項，在彈出的“ProjectStructure”界面中選擇“Libraries”選項，單擊加號“+”按鈕，選擇“Java”選項，如下圖。手動加載MySQL驅動選擇存放MySQL驅動JAR包的本地目錄，定位到連接驅動的位置并選中，如下圖，單擊“OK”按鈕。手動加載MySQL驅動在彈出的界面“ChooseModules”中選擇“HiveJavaAPI”選項，如左圖，然后單擊“OK”按鈕，返回到“ProjectStructure”界面后，如右圖，單擊“Apply”按鈕，即可完成驅動加載。手動加載MySQL驅動添加驅動后的項目視圖會顯示加載的新驅動列表，如下圖。JDBC及其主要接口JDBC是Java數(shù)據庫連接（JavaDataBaseConnectivity）的縮寫，它是一套用于執(zhí)行SQL語句的JavaAPI。應用程序可通過JDBC連接到關系數(shù)據庫，并通過使用SQL語句完成數(shù)據庫中數(shù)據的查詢、新增、更新和刪除等操作。不同數(shù)據庫處理數(shù)據的方式并不太相同，如果直接使用數(shù)據庫廠商提供的訪問接口操作數(shù)據庫，那么應用程序的可移植性會變得較差。有了JDBC后，便可解決應用程序可移植性差的問題，因為JDBC要求各個數(shù)據庫廠商按照統(tǒng)一的規(guī)范來提供數(shù)據庫驅動。在應用程序中由JDBC和具體的數(shù)據庫驅動聯(lián)系，所以讀者不必直接與底層的數(shù)據庫交互，使得代碼的通用性更強。JDBC在應用程序與數(shù)據庫之間起到橋梁作用，當應用程序使用JDBC訪問特定的數(shù)據庫時，需要通過不同的數(shù)據庫驅動與不同的數(shù)據庫進行連接，連接后即可對該數(shù)據庫進行相應的操作。JDBC及其主要接口Hive-JDBC驅動是專用于Hive的JDBC驅動，客戶端可通過JDBC訪問Hive。8.2.3小節(jié)中已在pom.xml文件中添加了對JDBC驅動的依賴。在開發(fā)JDBC驅動前，需要了解JDBC常用的API。JDBCAPI主要位于java.sql包中，java.sql包定義了一系列訪問數(shù)據庫的接口和類，常用接口包括Driver、DriverManager、Connection、Statement、PreparedStatement、ResultSet等。JDBC及其主要接口1．Driver接口Driver接口是所有JDBC驅動必須實現(xiàn)的接口，該接口專門提供給數(shù)據庫廠商使用。需要注意的是，在編寫JDBC驅動時，必須將所使用的數(shù)據庫驅動或類庫加載至項目的Classpath中。在進行Java開發(fā)時，程序員只需要根據程序使用的驅動類型，針對對應的Driver接口裝載即可，Driver接口的方法如下表。方法名稱功能描述class.forName("sun.jdbc.odbc.jdbcOdbcDriver")通過ODBC-JDBC驅動程序裝載JDBC驅動class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")通過MSSQL2000數(shù)據庫的JDBC驅動程序裝載JDBC驅動JDBC及其主要接口方法名稱功能描述class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")通過MSSQL2005數(shù)據庫的JDBC驅動程序裝載JDBC驅動class.forName("com.mysql.cj.jdbc.Driver")通過MySQL數(shù)據庫的JDBC驅動程序裝載JDBC驅動class.forName("oracle.jdbc.driver.OracleDriver")通過Oracle數(shù)據庫的JDBC驅動程序裝載JDBC驅動JDBC及其主要接口2．DriverManager接口DriverManager接口是JDBC提供的工具類，用于加載JDBC驅動、建立與數(shù)據庫之間的連接。DriverManager類中的方法都是靜態(tài)方法，因此在程序中無須對DriverManager類中的方法進行實例化，直接通過類名即可調用。DriverManager接口的方法如下表。方法名稱功能描述staticvoidregisterDriver(Driverdriver)該方法用于向DriverManager注冊給定的JDBC驅動staticConnectiongetConnection(Stringurl,Stringuser,Stringpwd)該方法用于建立與數(shù)據庫之間的連接，并返回表示連接的Connection對象JDBC及其主要接口3．Connection接口Connection接口的主要作用是與特定數(shù)據庫進行連接，在連接上下文中執(zhí)行SQL語句并返回結果。Connection接口的主要方法如下表。方法名稱功能描述publicjava.sql.DatabaseMetaDatagetMetaData()該方法用于返回表示數(shù)據庫的元數(shù)據的DatabaseMeta對象StatementcreateStatement()該方法用于創(chuàng)建將SQL語句發(fā)送至數(shù)據庫的Statement對象PreparedStatementprepareStatement(Stringsql)該方法用于創(chuàng)建將參數(shù)化的SQL語句發(fā)送至數(shù)據庫的PreparedStatement對象CallableStatementprepareCall(Stringsql)該方法用于創(chuàng)建調用數(shù)據庫存儲過程的CallableStatement對象JDBC及其主要接口4．Statement接口Statement接口用于執(zhí)行靜態(tài)SQL語句并返回所生成結果的對象，Statement接口對象可以通過Connection接口實例的createStatement()方法獲得。Statement接口的主要方法如下表。方法名稱功能描述booleanexecute(Stringsql)該方法用于執(zhí)行各種SQL語句，并返回BOOLEAN類型的值，如果值為true，那么表示所執(zhí)行的SQL語句有查詢結果，可以通過Statement接口的getResultSet()方法獲得查詢結果intexecuteUpdate(Stringsql)該方法用于執(zhí)行SQL中的INSERT、UPDATE和DELETE語句，并返回INT類型的值，表示數(shù)據庫中受該SQL語句影響的記錄條數(shù)ResultSetexecuteQuery(Stringsql)該方法用于執(zhí)行SQL中的SELECT語句，并返回表示查詢結果的ResultSet對象JDBC及其主要接口5．PreparedStatement接口PreparedStatement接口是Statement接口的子接口，表示預編譯的SQL語句的對象。該接口擴展了帶有參數(shù)SQL語句的執(zhí)行操作，應用該接口中的SQL語句可以使用占位符“?”代替參數(shù)，然后通過setXxx()方法為SQL語句的參數(shù)賦值。PreparedStatement接口的主要方法如表所示。方法名稱功能描述intexecuteUpdate()該方法用于執(zhí)行SQL語句，SQL語句必須是DML語句或是無返回內容的SQL語句，如DDL語句ResultSetexecuteQuery()該方法用于執(zhí)行SQL查詢，并返回ResultSet對象voidsetInt(int

parameterIndex,int

x)該方法用于將指定參數(shù)設置成給定的INT值voidsetString(intparameterIndex,Stringx)該方法用于將指定參數(shù)設置成給定的STRING值JDBC及其主要接口6．ResultSet接口ResultSet接口表示數(shù)據庫查詢的結果集，通常通過執(zhí)行查詢數(shù)據庫的語句生成，主要用于保存JDBC執(zhí)行查詢時返回的結果。該結果集被封裝在一個邏輯表格中，在ResultSet接口內部有一個指向表格數(shù)據行的游標。ResultSet接口初始化時，游標默認指向第一行之前，可調用next()方法移動游標到下一行，直至下一行為空則返回FALSE。ResultSet接口的主要方法如下表。方法名稱功能描述StringgetString(intcolumnIndex)該方法用于獲取指定字段的STRING類型的值，參數(shù)columnIndex代表字段的索引StringgetString(StringcolumnName)該方法用于獲取指定字段的STRING類型的值，參數(shù)columnName代表字段的名稱JDBC及其主要接口續(xù)上表方法名稱功能描述intgetInt(intcolumnIndex)該方法用于獲取指定字段的INT類型的值，參數(shù)columnIndex代表字段的索引intgetInt(StringcolumnName)該方法用于獲取指定字段的INT類型的值，參數(shù)columnName代表字段的名稱booleannext()該方法用于將游標從當前位置向下移一行創(chuàng)建連接測試程序使用JDBC連接數(shù)據庫時，通常需要提供如下4個必要參數(shù)。驅動類名：“org.apache.hive.jdbc.HiveDriver”。連接地址和端口號：“jdbc:hive2://master:10000”。用戶名：使用默認用戶root。密碼：使用默認密碼123456。創(chuàng)建連接測試程序在IDEA中創(chuàng)建連接測試程序，實現(xiàn)連接Hive數(shù)據庫并創(chuàng)建數(shù)據庫test，操作如下。創(chuàng)建Java類。選擇main節(jié)點下面的java文件夾，單擊鼠標右鍵選擇“New”命令，選擇“JavaClass”命令，如下圖。在彈出的界面輸入“Connection”，按Enter鍵，創(chuàng)建新的Java類“Connection.java”。創(chuàng)建連接測試程序編輯代碼。在新建的Java類Connection.java中進行與Hive默認數(shù)據庫DEFAULT的連接，并創(chuàng)建新的數(shù)據庫test。運行程序。在對應代碼文件中，單擊鼠標右鍵選擇“Run'Connection.main()'”命令運行代碼，在下方工具欄“Run”運行結果出現(xiàn)“Processfinishedwithexitcode0”表示運行無誤，如下圖。創(chuàng)建連接測試程序驗證。在HiveCLI，使用“SHOWDATABASES;”命令查詢數(shù)據庫，可以看到剛創(chuàng)建的數(shù)據庫test，如下圖。至此，Hive的開發(fā)環(huán)境搭建成功。配置Hive遠程服務搭建Hive遠程連接環(huán)境編寫程序實現(xiàn)廣電數(shù)據的存儲編寫程序實現(xiàn)廣電數(shù)據的查詢與處理任務描述在實現(xiàn)Hive遠程服務配置和Hive遠程服務調用的基礎上，通過在IDEA中編寫程序實現(xiàn)廣電大案例的5個數(shù)據表創(chuàng)建和數(shù)據裝載的代碼封裝。本任務將介紹如何在IDEA開發(fā)環(huán)境中調試程序，通過程序調用的方式，將廣電數(shù)據存儲至Hive。創(chuàng)建開發(fā)項目打開編程軟件IDEA，選擇“File”→“New”，創(chuàng)建項目ZJSM，并將該項目放置在本地目錄（如“D:\Hive\ZJSM”）。創(chuàng)建的具體操作可參考上小節(jié)中的內容，ZJSM項目視圖如下圖。在項目的pom.xml文件中添加Hive相關依賴，操作參照上小節(jié)中的內容。創(chuàng)建HiveHelper類和連接Hive在src/main/java目錄下創(chuàng)建新的Java類HiveHelper，用于進行數(shù)據庫相關操作。在HiveHelper類中創(chuàng)建一個新方法getConn()，用于通過使用JDBC連接Hive數(shù)據庫。由于在連接過程中可能出現(xiàn)驅動無法加載或訪問數(shù)據錯誤等異常，故此處使用異常處理或拋出異常。創(chuàng)建測試類在src/test/java目錄下創(chuàng)建新的測試類HiveTest，用于調用HiveHelper類，完成相關的數(shù)據庫操作。創(chuàng)建Hive數(shù)據庫在HiveHelper類中創(chuàng)建一個新方法createDatabase(StringdbName)，用于創(chuàng)建系統(tǒng)數(shù)據庫，需要使用異常處理。創(chuàng)建Hive表在HiveHelper類中創(chuàng)建一個新方法createTable2(StringdbName)，用于創(chuàng)建系統(tǒng)數(shù)據庫，需要使用異常處理。由于數(shù)據表較多，僅以其中一份表，即用戶狀態(tài)變更數(shù)