偶数科技CEO 常雷:新一代数据仓库

登学林

2017/11/14 发布于 技术 分类

数据仓库的演进分为三代,第一代数据仓库采用共享存储架构(比如Oracle Exadata),可扩性差,使用专有硬件,价格高。第二代数据仓库采用MPP架构,采用无共享架构(比如Teradata),使用普通X86服务器,可扩展至几十节点,但很难满足大数据需求,架构不够灵活,比如难于实现秒级扩容。Apache HAWQ属于第三代数据仓库,使用存储与计算分离架构,可扩展至数千节点,架构灵活。Oushu Database是HAWQ的增强企业版,拥有极速执行引擎,可以轻松取代传统数仓和Hadoop SQL引擎。

文字内容
1. 新偶常数雷一科博代技士C数EO据, Ap仓ach库e HS(AANWCeQ创Cw始2D人01a7ta Warehouse) www.oushu.io
2. 关于本人 --常雷博士 • 偶数科技创始人兼CEO • Apache HAWQ创始人 7• 前EMC/Pivotal研发部总监,高级研究员 01• 博士毕业于北京大学计算机系数据库专业 C2• 在国内外顶级学术会议(比如SIGMOD)和期刊发表数篇论文 SAC• 美国著名财经杂志《快公司》“中国商业最具创意人物100” • 中国大数据产业生态联盟专家 • 中国新一代IT产业推进联盟的技术专家 @Copyright 2017. All rights reserved
3. 目录 • 数据仓库生态 SACC2017• 数据仓库演进 • 新一代数据仓库 (NewDW - New Data Warehouse) • NewDW:Oushu Database的前世今生 @Copyright 2017. All rights reserved
4. 数据生态系统 12000 10000 8000 6000 4000 2000 0 920 2014 10270 70.00% 52.50% 49.04% 57.91% 53.54%7278 5070 43.55% 60.00% 50.00% 41.114%0.00% 1403 2091 3302 30.00% 20.00% 10.00% 0.00% 2015 2016 2017 2018 2019 2020 15000 10000 5000 0 26.47% 1038 2014 63.01% 1692 2015 46.87% 2485 2016 41.53% 3517 2017 67.96% 5907 2018 58.69% 9347 13626 45.36% 2019 2020 80% 60% 40% 20% 0% 全球数据仓库市场规模2016年达数百亿美 金 应用 用户行为分析、反欺诈、用户画像、信用模型 BI 分析挖掘/机器学习/AI Qlik, Power BI SAS, SPSS,Tensorflow 数 据 安 全 ETL I nformatic a Talend Kettle OLTP 关系数据库, NoSQL, NewSQL OLAP 数据仓库(Data Warehouse) MPP, SQL-on-Hadoop, New Data Warehouse 数 据 治 理 Cloud (公有云和私有云) @Copyright 2017. All rights reserved
5. 数据仓库的演进 传统数仓 s hare-s torage硬件/软件架构 DB实例1 DB实例2 DB实例3 DB实例4 共享存储 硬件配置 适用场景 架构 可扩展性 数仓代表 大多专有硬件平台 面向传统的BI分析 缺乏弹性 不易调整 十几个节点 Oracle, DB2 MPP s hare-nothi ng硬件/软件架构 DB 实例1 DB 实例2 DB 实例3 DB 实例4 磁盘 磁盘 磁盘 磁盘 大多工业标准的x 86服务器 复杂的计算需 求 缺乏弹性 面向传统BI分析 不易调整 几十个节点 Teradata, Vertica, Greenplum, Redshift 新一代数仓 (New Data Warehouse) share-nothing硬件架构 + 软件实现distributed shared-storage DB 实例1 DB 实例2 分布式文件系统 DB 实例3 磁盘 磁盘 磁盘 工业标准的x 86服务器 面向大数据和人工智 能 弹性伸缩,支持CaaS平 台 支持数据湖 灵活配置 上千个节点 Hive, HAWQ, SparkSQL, Snowflake @Copyright 2017. All rights reserved
6. 数据仓库引擎比较 高性能及 SQL兼容性 私有软件 & 闭源 & 非线性可扩展 开源&开放 & 线性可 扩展 受限的性能 及SQL兼容性 SQLAmazon Athena @Copyright 2017. All rights reserved
7. NewDW的细分类别 • SQL on Hadoop – SparkSQL, Hive, HAWQ 2.x, Presto • SQL on Object Store – Snowflake (on S3), Amazon Athena (on S3) • Hybrid: 有自己的存储,对外部存储可插拔 – HAWQ 3.x, Oushu Database – Impala @Copyright 2017. All rights reserved
8. Features 性能 可扩展性 Update/Delete 索引 SQL兼容性 高并发查询 NewDW特性比较 SQL on Hadoop SQL on Object Store Hybrid Hive SparkSQL Presto low middle low high high high bad N/A N/A bad N/A N/A middle middle bad no no no Snowflake low high weak N/A middle no Athena low high N/A N/A bad no HAWQ high high N/A N/A good no Oushu Impala top middle high high Good weak Yes weak good middle yes no @Copyright 2017. All rights reserved
9. Oushu Database的前世今生 @Copyright 2017. All rights reserved
10. HAWQ主要发展历程 Apache HAWQ 发展历程 • 2011年 - 常雷博士在EMC/Pivotal提出创意,HAWQ项目启动。 • 2013年 - HAWQ 1.0发布,性能是Hive的数百倍。 • 2014年 - HAWQ SIGMOD论文发表,得到国际数据库界认可。 • 2014年 - HAWQ为全球多家大型企业客户采用。 • 2015年 - HAWQ开源成为Apache项目。 • 2016年 - 常雷博士及HAWQ核心团队创立偶数科技。 • 2017年 - 偶数得到国际顶级VC投资,致力于HAWQ的发展。 • 2017年 - Oushu Database 3.0 企业版本发布,全新执行器, 10倍世界上最快的数据仓库 性能提升 @Copyright 2017. All rights reserved
11. Greenplum database (2003) Master host Interconnec t Primary Segmen t M i rror Segmen t Primary Segmen t M i rror Segmen t Segment host Primary Segmen t M i rror Segmen t Primary Segmen t M i rror Segmen t Segment host Data/Catalog replication replication Data/Catalog M i rror Segmen t Primary Segmen t M i rror Segmen t Primary Segmen t Segment host Degree of Parallelism = 8 #Segment Per Node = 4 Primary Segmen t M i rror Segmen t Primary Segmen t M i rror Segmen t Segment host @Copyright 2017. All rights reserved
12. HAWQ Alpha: Greenplum Database on HDFS (2011) Primary Segmen t M i rror Segmen t Primary Segmen t M i rror Segmen t Master host Primary Segmen t M i rror Segmen t Primary Segmen t M i rror Segmen t Catalog replication replication Catalog Interconnec t M i rror Segmen t Primary Segmen t M i rror Segmen t Primary Segmen t Primary Segmen t M i rror Segmen t Primary Segmen t M i rror Segmen t Datanode Datanode Data replication Datanode B Datanode Segment host Rack1Segment host • Degree of Parallelism = 8 • #Segment Per Node = 4 Meta Ops Segment host Rack2 Segment host Namenode Issues: • Recovery complexity • Expansion complexity • Management complexity (many segments pernode) • Fixed Degree of Parallelism @Copyright 2017. All rights reserved
13. HAWQ 1.0 GA Architecture (2013) Master host Catalog Interconnec t Stateless Segment Segment Segment Segment Segment Segment Segment Segment Datanode Datanode Data replication Datanode Datanode Segment host Rack1Segment host • Degree of Parallelism = 8 • #Segment Per Node = 2 Segment host Meta Ops Rack2 Segment host Namenode Issues: • Recovery complexity • Expansion complexity • Management complexity (many segments pernode) • Fixed Degree of Parallelism @Copyright 2017. All rights reserved
14. HAWQ 2.0: Architecture Change (2016 Q2) Master host Catalog Resourc e Manager Interconnec t Stateless vseg vseg vseg vseg Segmen t Datanode vseg vseg vseg vseg Segmen t Datanode Data replication vseg vseg vseg vseg Segmen t vseg vseg vseg vseg Segmen t Datanode Datanode Segment host Rack1Segment host • Degree of Parallelism = Any (#vseg) • #Segment Per Node = 1 Segment host Meta Ops Rack2 Segment host Namenode Issues: • Recovery complexity • Expansion complexity • Management complexity (many segments pernode) • Fixed Degree of Parallelism @Copyright 2017. All rights reserved
15. Oushu Database 3.0: Hornet Execution Engine (2017 Q3) Master host Catalog Resourc e Manager Interconnec t Stateless vseg vseg Hornet Segmen t Datanode vseg vseg Hornet Segmen t Datanode Data replication vseg vseg Hornet Segmen t vseg vseg Hornet Segmen t Datanode Datanode Segment host Rack1Segment host Segment host Meta Ops Namenode Rack2 Segment host 10Hornet Execution Engine:SIMD/New hardware times faster The Fastest Engine in the World @Copyright 2017. All rights reserved
16. Oushu Database 3.0 vs SparkSQL 2.2 单位(毫秒ms) select count(*) from lineitem; select count(*) from lineitem; AVERAGE Oushu Spark ratio 21.28 2555 120.06 22.77 2440 107.16 22.03 2497.50 113.61 @Copyright 2017. All rights reserved
17. count 不同数据类型的列 单位(毫秒ms) select count(l_orderkey) from lineitem; select count(l_partkey) from lineitem; select count(l_suppkey) from lineitem; select count(l_linenumber) from lineitem; select count(l_quantity) from lineitem; select count(l_extendedprice) from lineitem; select count(l_discount) from lineitem; select count(l_tax) from lineitem; select count(l_returnflag) from lineitem; select count(l_linestatus) from lineitem; select count(l_shipdate) from lineitem; select count(l_commitdate) from lineitem; select count(l_receiptdate) from lineitem; select count(l_shipinstruct) from lineitem; select count(l_shipmode) from lineitem; select count(l_comment) from lineitem; AVERAGE Oushu 306.70 274.35 244.77 133.67 110.12 112.05 108.64 115.14 70.41 73.01 127.12 135.43 134.36 236.63 177.66 344.94 169.06 Spark 3925 3674 3466 3265 3689 3627 3886 3723 4591 4208 4218 4506 4193 4311 4173 5885 4083.75 Ratio 12.80 13.39 14.16 24.43 33.50 32.37 35.77 32.33 65.20 57.64 33.18 33.27 31.21 18.22 23.49 17.06 29.88 @Copyright 2017. All rights reserved
18. sum/avg 不同数据类型的列 单位(毫秒ms) select sum(l_orderkey) from lineitem; select sum(l_partkey) from lineitem; select sum(l_suppkey) from lineitem; select sum(l_linenumber) from lineitem; select sum(l_quantity) from lineitem; select sum(l_extendedprice) from lineitem; select sum(l_discount) from lineitem; select sum(l_tax) from lineitem; select avg(l_orderkey) from lineitem; select avg(l_partkey) from lineitem; select avg(l_suppkey) from lineitem; select avg(l_linenumber) from lineitem; select avg(l_quantity) from lineitem; select avg(l_extendedprice) from lineitem; select avg(l_discount) from lineitem; select avg(l_tax) from lineitem; AVERAGE Oushu 323.16 298.30 263.69 154.20 128.39 138.48 141.68 143.07 327.68 303.51 269.36 161.41 131.92 138.48 134.01 137.92 199.70 Spark 3414 3321 3243 3193 4004 4042 3500 3536 3511 3583 3331 3196 3614 3554 3618 3549 3513.06 Ratio 10.56 11.13 12.30 20.71 31.19 29.19 24.70 24.72 10.71 11.81 12.37 19.80 27.40 25.66 27.00 25.73 20.31 @Copyright 2017. All rights reserved
19. @Copyright 2017. All rights reserved group by (某一列) 取count 单位(毫秒ms) select l_orderkey, count(*) from lineitem group by l_orderkey; select l_partkey, count(*) from lineitem group by l_partkey; select l_suppkey, count(*) from lineitem group by l_suppkey; select l_linenumber, count(*) from lineitem group by l_linenumber; select l_quantity, count(*) from lineitem group by l_quantity; select l_extendedprice, count(*) from lineitem group by l_extendedpric e; select l_discount, count(*) from lineitem group by l_disc ount; select l_tax, count(*) from lineitem group by l_tax; select l_returnflag, count(*) from lineitem group by l_returnflag; select l_linestatus, count(*) from lineitem group by l_linestatus; select l_shipdate, count(*) from lineitem group by l_shipdate; select l_commitdate, count(*) from lineitem group by l_c ommitdate; select l_receiptdate, count(*) from lineitem group by l_rec eiptdate; select l_shipinstruct, count(*) from lineitem group by l_shipinstruc t; select l_shipmode, count(*) from lineitem group by l_shipmode; select l_comment, count(*) from lineitem group by l_c omment; A V ERA GE(除去spark OOM 语句) Oushu Spark 14314. 14 OOM Ratio NAN 4127. 98 29299 7. 10 1142. 61 363. 51 370. 15 4929. 78 392. 41 352. 99 545. 86 329. 30 638. 51 642. 31 647. 12 823. 09 630. 63 39032. 16 1138. 30 18181 9570 11367 29736 10371 10371 11346 11217 16077 16161 15649 11539 11371 OOM 15161. 07 15. 91 26. 33 30. 71 6. 03 26. 43 29. 38 20. 79 34. 06 25. 18 25. 16 24. 18 14. 02 18. 03 NAN 21. 66
20. group by 不同数据类型的列,取其sum和avg 单位(毫秒ms) select l_partkey, sum(l_partkey), avg(l_partkey) from lineitem group by l_partkey; select l_suppkey, sum(l_suppkey), avg(l_suppkey) from lineitem group by l_suppkey; select l_linenumber, sum(l_linenumber), avg(l_linenumber) from lineitem group by l_linenumber; select l_quantity, sum(l_quantity), avg(l_quantity) from lineitem group by l_quantity; Oushu 8333.37 1527.32 416.03 390.82 select l_extendedprice, sum(l_extendedprice), avg(l_extendedprice) from lineitem group by l_extendedprice; 9148.20 select l_discount, sum(l_discount), avg(l_discount) from lineitem group by l_discount; select l_tax, sum(l_tax), avg(l_tax) from lineitem group by l_tax; 418.81 357.99 AVERAGE 2941.79 Spark 54470 19505 9914 11949 Ratio 6.54 12.77 23.83 30.57 32005 3.50 10757 25.68 10733 21333.29 29.98 18.98 @Copyright 2017. All rights reserved
21. @Copyright 2017. All rights reserved Group by 多列 单位(毫秒ms) select l_partkey, l_suppkey, count(*) from lineitem group by l_partkey, l_suppkey; Oushu 13074.79 select l_partkey, l_linenumber, count(*) from lineitem group by l_partkey, l_linenumber; select l_suppkey,l_extendedprice, count(*) from lineitem group by l_suppkey,l_extendedprice; select l_partkey, l_shipmode, count(*) from lineitem group by l_partkey, l_shipmode; 18091.03 145543.51 21298.14 select l_partkey, l_shipdate, count(*) from lineitem group by l_partkey, l_shipdate; 71890.82 select l_suppkey, l_tax, count(*) from lineitem group by l_suppkey, l_tax; 3994.25 select l_shipdate,l_commitdate, count(*) from lineitem group by l_shipdate,l_commitdate; 3159.43 select count(l_orderkey) from lineitem group by l_linenumber , l_quantity , l_tax; 1179.85 AVERAGE 2777.84 Spark OOM Ratio NAN OOM NAN OOM NAN OOM NAN OOM 28334 32811 NAN 7.09 10.39 18080 15.32 26408.33 10.93
22. Group by 表达式 单位(毫秒ms) Oushu Spark Ratio select l_partkey + l_suppkey, count(*) from lineitem group by l_partkey + l_suppkey; 4050.55 31601 7.80 select l_partkey + 1000 from lineitem group by l_partkey + 1000; 2869.51 27083 9.44 select l_tax * 100 from lineitem group by l_tax*100; 426.14 10005 23.48 AVERAGE group by 表达式 2448.73 22896.33 13.57 @Copyright 2017. All rights reserved
23. @Copyright 2017. All rights reserved 多个聚集函数 单位(毫秒ms) select l_partkey, count(*), c ount(l_orderkey), sum(l_orderkey), avg(l_orderkey) from lineitem group by l_partkey; select l_suppkey,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_suppkey; select l_linenumber,count(*),count(l_orderkey) ,sum(l_orderkey),avg(l_orderkey) from lineitem group by l_linenumber; select l_quantity,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_quantity; select l_discount,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_discount; select l_tax,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_tax; select l_returnflag,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_returnflag; select l_linestatus,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_linestatus; select l_shipdate,count(*),count(l_orderkey), sum(l_orderkey),avg(l_orderkey) from lineitem group by l_shipdate; select l_shipmode,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_shipmode; select l_comment,count(*),count(l_orderkey) , sum(l_orderkey),avg(l_orderkey) from lineitem group by l_comment; A V ERA GE Oushu Spark Ratio 11878.22 OOM NAN 2399. 98 23745 9. 89 698. 18 10943 15. 67 702. 60 13496 19. 21 741. 17 12668 17. 09 670. 63 12046 17. 96 913. 23 12812 14. 03 675. 94 12444 18. 41 1025. 86 … 17846 17. 40 …… 117636. 74 1722. 58 OOM 17189. 46 NAN 14. 97
24. 单位(毫秒ms) TPCHQ1 TPCHQ1 TPCHQ1 AVERAGE @Copyright 2017. All rights reserved TPCH Query Oushu Spark Ratio 1175.99 18626 15.84 1140.01 18060 15.84 1161.93 18096 15.57 1159.31 18260.67 15.75 TPCH Q1 selec t l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem_1gorc _none where l_shipdate <= '1998-08-20' group by l_returnflag, l_linestatus;
25. Oushu Database 4.0: Global Scale (2017 H1) H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et H orn et Global Scale: No master, P2P,Geo-replication,mixed workload @Copyright 2017. All rights reserved
26. HAWQ全球用户(部分) @Copyright 2017. All rights reserved
27. 某大型制造企业案例 背景 l 大量传感器数据无法及时处理 l 故障无法及时检测带来很大损失 l 传统解决方案过于昂贵 实现目标 l 搭建大数据平台,提高其处理处理能力 l 200+节点分析平台集群 l PB级数据存储 l 实现实时故障预测等应用 @Copyright 2017. All rights reserved
28. @Copyright 2017. All rights reserved 某大型证券交易所 • 挑战 – 为了应对每天增长的交易量,替换现有 Oracle EDW平台 – 为了合规需要保存最细力度的交易数据 – 经济有效的方式保证每天处理TB级别增量 数据 • 解决方案 – 把所有交易数据放入Hadoop和HAWQ – 把12亿条记录放到HAWQ里面进行查询分 析,获得更好的性能
29. 偶数科技简介 • EMC/Pivotal HAWQ创始人及HAWQ核心团队成员创立 • 偶数两大数据仓库/AI产品 – Oushu Database (HAWQ++) – Apache HAWQ • 成员大多为Apache Committer & PMC成员,来自各大云计算 和大数据公司: EMC/Pivotal, Oracle, IBM, Teradata等 • 毕业于国内外顶级学府,多个ACM程序设计大赛奖牌获得者 • 团队研究成果发布在国际顶级数据管理会议上(比如SIGMOD 等),并拥有多项国际专利 • 成立初始就获得硅谷VC投资 @Copyright 2017. All rights reserved
30. 偶数科技 常雷