Hive高级编程-weibo-大数据文档资料

GlobalSortHiveHiveQL-Joind1d121dX=dd121dd121d>valuevalue>>>HiveHiveQL-GroupBypageid_age_pageid_age_sumd112211dd1212.INSERTINTOTABLEpageid_age_sum.SELECTpageid,age,count(1).FROMpv_users-GROUPBYpageid,age;d12d12111111p111pdcountdcount_distinct_us1221dd1212-SELECTpageid,COUNT(DISTINCTuserid)-FROMpage_viewGROUPBYEfficientexecutionofSQGlobal-MergeSequentialMapReduceJobsAB1ABC1AB1ABC11C11-FROM(ajoinbona.key=b.key)joincona.key=c.keySELECT…-ShareCommonReadOperationsd12d12d112111.FROMpv_users.INSERTINTOTABLEpv_pageid_sum..SELECTpageid,count(1)..GROUPBYpageid.INSERTINTOTABLEpv_age_sum.SELECTage,count(1).GROUPBYage;-MapJoin-NoreducerneededHiveQL-MapJointime121femalevalue21value21-Hash-basedaggregates-90%speedimprovementonQuery少的聚合效果）（map端聚合最多能使用的内存）Attention：不能太多gendercountgendercountM13F1111gendercountgendercountM24F22gendergenderM1M2M1M1F2F1gendercountgendercountM11F111111-skewindata优化-用法-LoadBalanceProblempageid_age_sumpageid_age_partial_pageid_age_sumpageid_age_partial_sumde11121dd1421e122112-倾斜的原因？-驱动表•使用大表做驱动表，避免内存溢出•Join中最右边的表是驱动表•MapJoin无视Join顺序，使用大表做驱动表-特征•每个Map有一个输出文件，输入数据量大，Map数很多导致输-缺点-改进前wherept='20111123000000'ANDdateCompare(gmt_created,'2011-11--改进后wherept='20111123000000'ANDdateCompare(gmt_created,'2–优先官方UDF•/confluence/display/Hive/LanguageManual+UDF–耗时的操作，耗cpu•编解码，url的encode/d•SimpleDateFormat复用，构造函数中的操作耗时••date_formatyyyy-MM-ddHH:mmpublicclassUDFDateextendsUDF{publicUDFDate(){}publicTextevaluate(TextdateString){if(dateString==null){returnnull;}try{SimpleDateFormatformatter=newSimpleDateFormat("yyyy-MM-dd");Datedate=formatter.parse(dateString.toString());t.set(formatter.format(date));returnt;}catch(ParseExceptione){returnnull; }•date_formatyyyy-MM-dpublicclassUDFDateextendsUDF{privatefinalSimpleDateFormatformatter=newSimpleDateFormat("yyyy-MM-dd");publicUDFDate(){}publicTextevaluate(TextdateString){if(dateString==null){returnnull;}try{Datedate=formatter.parse(dateString.toString());t.set(formatter.format(date));returnt;}catch(ParseExceptione){returnnull; };insertOVERWRITETABLEr_mid_alipay_playpartition(pt)selectday_id,buyer_nick,buyer_id,cat1,name1,from(selectptasday_id,buyer_nick,buyer_id,cat1,name1,concat(pt,'000000')asptfromr_gmv_alipayawherept>='20110101'andalipay='alipay'groupbypt,buyer_nick,buyer_id,cat1,name1,concat(pt,'000000'))distributebypt;/index.php/Hive_sql_%E7%9B%B8%E5%85%B3%E7%94%A8%E6%B3%95#.E5.8A.A8.E6.80.81.E5.88.86.E5.8C.BA.E4.BA.A7.E7.94.9F.E5.A4.A7.E9.87.8F.E6.96.87.E4.BB.B6.EF.BC.8C.E5.AF.BC.E8.87.B4namenode_load.E5.BE.88.E9.AB.98•快：fromr_auction_auctions_20110107_pfromr_auction_auctions_20110107_plimit100;/index.php/Hive_sql_%E7%9B%B8%E5%85%B3%E7%94%A8%E6%B3%95#.E5.A6.82.E4.BD.95.E5.BF.AB.E9.80.9F.E5.8F.96.E5.8