2018大數據競賽試題答案

HadoopA.hadoopip地址,ip3臺虛名機分別執(zhí)行編輯命令,然后唯一的區(qū)別就是名字,hadoop1,C.3hosts文件命令為:vi/etc/hostsE:關閉,虛擬機環(huán)境已集主節(jié)點(hadoop1):配置為時間服務器,yumntp.從節(jié)點(hadoop2,hadoop3)hadoop1的配置(虛擬機環(huán)境已集成hadoophadoophadoopHive安裝mysqlHive元數mysql-uroot-最后一步就是新建hive數據庫,用于hive元數將mysql連接驅動包放到hive安裝路徑的 下,mysql包在hadoop-install錄啟動hive之前,先進行hive元數據初始化:schematooldbTypemysqlHive數據createdatabaseraw_test;usehiveshop1,shop2shop1,shop2數據源字段屬性從左往右分別是:id,商品名稱,商品額,商品數量,商品時createtabletable_name(idint,goods_namestring,biz_pricedouble,count rowformatdelimitedfieldsterminatedby';'storedastextfile;txtterminatedbytable_nameshop1,shop2,分兩次執(zhí)行.loaddatalocalinpath'/root/file_name.txt'intotable這里的'/root/file_name.txttxtfile_nameshop1,shop2,執(zhí)行兩次,table_nameraw_testshop1,shop2shop1_1,shop1_2,shop2_1createtabletable_name(idint,goods_namestring,biz_pricedouble,count rowformatdelimitedfieldsterminatedby';'storedastextfile;table_nameshop1_1的要求為過濾金額為負數或者數量為負數的記錄,語句如下:insertoverwritetableshop1_1selectrow_number()over(orderbyunix_timestamp(biz_date)asc)asid,fromwherebiz_price>=0orcount>=selectcount(*)fromshop1_2的要求為過濾掉屬性有空的記錄,語句如下:insertoverwritetableshop1_2selectrow_number()over(orderbyunix_timestamp(biz_date)asc)asid,fromwhereidisnotnullandgoods_name!=andbiz_priceisnotnullandcountisnotnullandbiz_date!='';selectcount(*)frominsertoverwritetableselectrow_number()over(orderbyunix_timestamp(biz_date)asc)asfromshop2groupbyselectcount(*)fromSpark操(進入spark安 ，/opt/spark-2.3.1/bin輸入spark-sql進入交互環(huán)境Spark-sqlmovie庫,moviecreatedatabasemovie;usemovie;movie庫創(chuàng)建movie_top250,并將桌面top250_f1.csv數據源導入到movie_top250中string,genrestring,rating_num mentstring,urlstring)rowformatfieldsterminatedby'\t'storedastextfile;loaddatalocalinpath'/root/top250_f1.txt'intotableselect*frommovie_top250limit5部selectnum, frommovie_top250orderbycomment_numdesclimit5;統(tǒng)計2013年以后產量最多的5個地selecta.area,count(a.num)asmovie_count selectnum,area,init_yearfrommovie_top250whereinit_year>=groupbyareaorderbymovie_countdesclimit統(tǒng)計評分和評論系數最高,且類別為愛情和劇情的前10部selecttitle,a ment_num,(rating_num*comment_num)asflagfrommovie_top250wheregenrelike劇情%愛情orgenrelike愛情%劇情%'orderbyflagdesclimit10;weather庫,weathercreatedatabaseweather;useweather;weather表createtableweather(happen_datestring,weather_conditionstring,highest_temperatureint,minimum_temperatureint,wind_directionstring)rowformatfieldsterminatedbystoredasweatherweather20189月份每天的最高溫度selecthappen_date,highest_temperature,minimum_temperaturefromweatherwhereyear(happen_date)=2018andmonth(happen_date)=9Echarts實例代碼(3).2016年,selectcount(*),weather_conditionaswcfromweatherwhereyear(happen_date)=2016groupbyweathe