大数据 常雷-新一代数据仓库：Apache HAWQ

新一代数据仓库:HAWQ偶数科技CEO,ApacheHAWQ创始人www.oushu.io@Copyright2016.Allrightsreserved公司简介HAWQ成功案例公司简介HAWQ成功案例@Copyright2016.Allrightsreserved 2014 2014201520162017201820192020亿美元全球大数据市场规模市场体量增长率%60%40%20%0%45.36%63.01%58.69%34746.87%41.53%90726.47%517000248520142015201920200市场体量增长率201620172018据市场规模67.96%@Copyright2016.AllrightsreservedQlikPowerBI数据安全QlikPowerBI数据安全PwaWarehouseInformaticaTalendKettleNoSQLNewSQL挖掘／机器学习/AISAS,SPSS,TensorflowCloud(公有云和私有云)全球数据仓库市场规模2016年达数百亿美金•Database:1962年出现–InvertedFileDatabaseSystemon•数据库的几个阶段–1960s:NavigationalDBMS(网状&层次模型)•IntegratedDataStore(IDS)•InformationManagementSystem(IMS)–1970s-1990s:SQL/RelationalDBMS•OLTP,Datawarehouse,MPP–2000s-Present:PostRelational•NoSQL(XML,KV,Graph,Tree),NewSQL,NewDW@Copyright2016.Allrightsreserved•查询优化和执行•索引与存储•事务处理@Copyright2016.Allrightsreserved1973TuringAward住在Harrison的所有客户@Copyright2016.Allrightsreserved1981TuringAwardSelectcustomer_nameFromcustomerWherecustomer_city=‘Harrison’;找出住在Harrison的所有客户elationalModelofDataforLargeSharedDataBanksJimGray1998TuringAwardr2014TuringAward@Copyright2016.AllrightsreservedalueCassandra:CQL–HBase:API•GraphModeleoj–Giraph/Pregel–XMLDatabaseB@Copyright2016.Allrightsreserved@Copyright2016.Allrightsreserved硬件配 适用场景架构share-nothing硬件/软件架构大多工业标准的x86服务器复杂的计算需求缺乏弹性面向传统BI分析不易调整几十个节点caGreenplumRedshift支持数据湖弹性伸缩，支持CaaS平台灵活配置硬件配 适用场景架构share-nothing硬件/软件架构大多工业标准的x86服务器复杂的计算需求缺乏弹性面向传统BI分析不易调整几十个节点caGreenplumRedshift支持数据湖弹性伸缩，支持CaaS平台灵活配置share-nothing硬件架构+软件实现distributedshared-storage盘磁盘缺乏弹性不易调整IOracle,DB2工业标准的x86服务器上千个节点owflake面向大数据和人工智能可扩展性表传统数仓share-storage硬件/软件架构@Copyright2016.AllrightsreservedL开L开源&开放&线性可AmazonAthenaSQL私有软件&闭源&非线性可扩展@Copyright2016.Allrightsreserved•SQLonHadoope–Snowflake(onS3),AmazonAthena(onS3)•Hybrid:有自己的存储，对外部存储可插拔–Impala@Copyright2016.Allrightsreserved@Copyright2016.AllrightsreservedadoopSQLonObjectStoreridStoragekSQLnowflakepalamiddlehtopmiddlehhhhhhhhoodmiddlemiddlemiddleododmiddle@Copyright2016.AllrightsreservedApacheHAWQ发展历程•2011年-常雷博士在EMC/Pivotal提出创意，HAWQ项目启动。•2013年-HAWQ1.0发布，性能是Hive的数百倍。•2014年-HAWQ为全球多家大型企业客户采用。•2015年-HAWQ开源成为Apache项目。•2016年-常雷博士及HAWQ核心团队创立偶数科技。•2017年-偶数得到国际顶级VC投资，致力于HAWQ的发展。•2017年-OushuDatabase3.0企业版本发布，全新执行器，世界上最快的数据仓库10倍性能提升@Copyright2016.AllrightsreservedInterconnectMirrorSegmentMirrorSegmentPrimarySegmentPrimarySegmentSegmenthostPrimarySegmentPrimarySegmentMirrorSegmentMirrorSegmentSegmenthosttInterconnectMirrorSegmentMirrorSegmentPrimarySegmentPrimarySegmentSegmenthostPrimarySegmentPrimarySegmentMirrorSegmentMirrorSegmentSegmenthosttaryegmentrorSegmentrrorSegmentSegmenthostreplicationreplicationMasterhost@Copyright2016.AllrightsreservedDegreeofParallelism=8#SegmentPerNode=4rimarySegmentMirrorSegmentrimarySegmentMirrorSegmentPrimarySegmentMirrorSegmentSegmenthostogreplicationPrimarySegmentMirrorSegmentPrimarySegmentMirrorSegmentDatanoderimarySegmentMirrorSegmentPrimarySegmentMirrorSegmenttrorSegmentaryegmentrrorSegmentreplicationogDatareplicationRack1Rack2•DegreeofParallelism=8•#SegmentPerNode=ogreplicationPrimarySegmentMirrorSegmentPrimarySegmentMirrorSegmentDatanoderimarySegmentMirrorSegmentPrimarySegmentMirrorSegmenttrorSegmentaryegmentrrorSegmentreplicationogDatareplicationRack1Rack2•DegreeofParallelism=8•#SegmentPerNode=4Issues:•Recoverycomplexity•Expansioncomplexity•Managementcomplexity(manysegmentspernode)•FixedDegreeofParallelism DatanodeDatanodeBNamenodeMasterhosttMetaOps@Copyright2016.AllrightsreservedSegmenthostSegmenthostSegmenthostSegmenthostMirrorMirrorSegmentMirrorMirrorSegmentrimaryrimarySegmentPrimaryPrimarySegmentDaDatanodeMasterhost SegmentSegmenttDatanodeStatelessmentSegmentegmentData DatanodereplicationSegmenthostSegmenthostSegmenthostRack2Rack1•DegreeofParallelism=8•#SegmentPerMasterhost SegmentSegmenttDatanodeStatelessmentSegmentegmentData DatanodereplicationSegmenthostSegmenthostSegmenthostRack2Rack1•DegreeofParallelism=8•#SegmentPerNode=2Issues:•RecoverycomplexityDatanodeHAWQ1.0GAArchitecture(2013)tCatalogSegmenthostMetaOps•Expansioncomplexity•Managementcomplexity(manysegmentspernode)•FixedDegreeofParallelism@Copyright2016.AllrightsreservedntDDatanodeNamenodeNamenode和PaaS/Docker云平台原生结合的并行SQL引擎MasterhostInterconnectvsegvsegvsegvsegvsegvsegtvsegvsegvsegvsegSegmentDatanodevsegvsegStatelessDatareplicationSegmenthostSegmenthostSegmenthost和PaaS/Docker云平台原生结合的并行SQL引擎MasterhostInterconnectvsegvsegvsegvsegvsegvsegtvsegvsegvsegvsegSegmentDatanodevsegvsegStatelessDatareplicationSegmenthostSegmenthostSegmenthostRack2Rack1•DegreeofParallelism=Any(#vseg)•#SegmentPerNode=1•Recoverycomplexity•Expansioncomplexity•FixedDegreeofParallelismDatanodeSegmentDatanodeHAWQ2.0:ArchitectureChange(2016Q2)界上第一个SegmenthostMetaOps@Copyright2016.AllrightsreservedCatalogResourceManagervsegvsegSegmentvsegvsegDatanodevsegvsegSegmentvsegvsegDatanodeNamenodeNamenodeMasterhosttvsegvsegHornetSegmentvsegHornetSegmentvsegDatanodeStatelessDatareplicationDatanodeDatanodeSegmentvsegvsegHornetDatanodeCatalogResourceManagerNamenodevsegvsegHornettHAWQ++3.0:HornetExecutionEngine(2017Q3)MasterhosttvsegvsegHornetSegmentvsegHornetSegmentvsegDatanodeStatelessDatareplicationDatanodeDatanodeSegmentvsegvsegHornetDatanodeCatalogResourceManagerNamenodevsegvsegHornettSegmenthostSegmentSegmenthostSegmenthost SegmenthostSegmenthostRack1MetaOpsHornetExecutionEngine:SIMD/Newhardwaretimesfaster@Copyright2016.Allrightsreserved@Copyright2016.Allrightsreserved单位(毫秒ms)rkselectcount(*)fromlineitem;selectcount(*)fromlineitem;AVERAGE@Copyright2016.Allrightsreserved单位(毫秒ms)rkRatioselectcount(l_orderkey)fromlineitem;selectcount(l_partkey)fromlineitem;selectcount(l_suppkey)fromlineitem;selectcount(l_linenumber)fromlineitem;selectcount(l_quantity)fromlineitem;selectcount(l_extendedprice)fromlineitem;selectcount(l_discount)fromlineitem;selectcount(l_tax)fromlineitem;selectcount(l_returnflag)fromlineitem;selectcount(l_linestatus)fromlineitem;selectcount(l_shipdate)fromlineitem;selectcount(l_commitdate)fromlineitem;selectcount(l_receiptdate)fromlineitem;selectcount(l_shipinstruct)fromlineitem;selectcount(l_shipmode)fromlineitem;selectcount(l_comment)fromlineitem;AVERAGE@Copyright2016.Allrightsreserved单位(毫秒ms)rkRatioselectsum(l_orderkey)fromlineitem;selectsum(l_partkey)fromlineitem;selectsum(l_suppkey)fromlineitem;selectsum(l_linenumber)fromlineitem;selectsum(l_quantity)fromlineitem;selectsum(l_extendedprice)fromlineitem;selectsum(l_discount)fromlineitem;selectsum(l_tax)fromlineitem;selectavg(l_orderkey)fromlineitem;selectavg(l_partkey)fromlineitem;selectavg(l_suppkey)fromlineitem;selectavg(l_linenumber)fromlineitem;selectavg(l_quantity)fromlineitem;selectavg(l_extendedprice)fromlineitem;selectavg(l_discount)fromlineitem;selectavg(l_tax)fromlineitem;AVERAGE@Copyright2016.Allrightsreserved单位(毫秒ms)ycountfromlineitemgroupby14countfromlineitemgroupbylpartkey;4127.987.10omlineitemgroupby6191363.5126.33roupby370.1530.71lineitemgroupby4929.786.03y392.4126.43groupbyl_tax;352.9929.38temgroupby545.8620.79329.3034.06638.5125.18tdatecountfromlineitemgroupby642.3125.16untfromlineitemgroupby647.1224.18823.0902630.6303omlineitemgroupby032.16AVERAGE(除去sparkOOM语句)300721.66@Copyright2016.Allrightsreserved单位(毫秒ms)rkRatioselectl_partkey,sum(l_partkey),avg(l_partkey)fromlineitemgroupbylpartkey;selectl_suppkey,sum(l_suppkey),avg(l_suppkey)fromlineitemgroupbyl_suppkey;selectl_linenumber,sum(l_linenumber),avg(l_linenumber)fromlineitemgroupbyllinenumber;selectl_quantity,sum(l_quantity),avg(l_quantity)fromlineitemgroupbyl_quantity;mlextendedpriceavg(l_extendedprice)fromlineitemgroupbyselectl_discount,sum(l_discount),avg(l_discount)fromlineitemgroupbyldiscount;selectl_tax,sum(l_tax),avg(l_tax)fromlineitemgroupbyltax;AVERAGE@Copyright2016.Allrightsreserved单位(毫秒ms)rkRatioselectl_partkey,l_suppkey,count(*)fromlineitemgroupbyl_partkey,l_suppkey;electlpartkeyllinenumbercountfromlineitemgroupbyl_partkey,l_linenumber;selectl_suppkey,l_extendedprice,count(*)fromlineitemgroupbylsuppkey,lextendedprice;selectl_partkey,l_shipmode,count(*)fromlineitemgroupbylpartkey,l_shipmode;selectl_partkey,l_shipdate,count(*)fromlineitemgroupbyl_partkey,l_shipdate;selectl_suppkey,l_tax,count(*)fromlineitemgroupbyl_suppkey,l_tax;selectl_shipdate,l_commitdate,count(*)fromlineitemgroupbylshipdatel_commitdate;selectcount(l_orderkey)fromlineitemgroupbyl_linenumber,l_quantity,l_tax;AVERAGE@Copyright2016.Allrightsreserved单位(毫秒ms)Oushuselectl_partkey+l_suppkey,count(*)fromlineitemgroupbyl_partkey+l_suppkey;4050.55316017.80selectl_partkey+1000fromlineitemgroupbyl_partkey+1000;2869.51270839.44selectl_tax*100fromlineitemgroupby426.141000523.48AVERAGEgroupby表达式2448.7322896.3313.57@@Copyright2016.Allrightsreserved单位(毫秒ms)selectl_partkey,count(*),groupbylpartkey;22selectl_suppkey,count(*),count(l_orderkey),sum(l_orderkey),avg(l_orderkey)fromlineitemgroupbylsuppkey;99.989.89selectl_linenumber,count(*),count(l_orderkey),sum(l_orderkey),avg(l_orderkey)fromlineitemgroupbyl_linenumber;698.1867selectl_quantity,count(*),count(l_orderkey),sum(l_orderkey),avg(l_orderkey)fromlineitemgroupbylquantity;702.6021selectl_discount,count(*),count(l_orderkey),sum(l_orderkey),avg(l_orderkey)fromlineitemgroupbyl_discount;741.1709selectl_tax,count(*),count(l_orderkey),sum(l_orderkey),avg(l_orderkey)fromlineitemgroupbyl_tax;670.6396selectl_returnflag,count(*),count(l_orderkey),sum(l_orderkey),avg(l_orderkey)fromlineitemgroupbylreturnflag;913.2303selectl_linestatus,count(*),count(l_orderkey),sum(l_orderkey),avg(l_orderkey)fromlineitemgroupbyl_linestatus;675.9441selectl_shipdate,count(*),count(l_ord