1、Apache KylinOLAP on Hadoop第1頁http:/kylin.ioAgendaWhats Apache Kylin?Tech HighlightsPerformanceRoadmapQ&A第2頁Extreme OLAP Engine for Big DataKylin is an open source Distributed Analytics Engine from eBay thatprovides SQL interface and multi-dimensional analysis (OLAP) onHadoop supporting extremely lar

2、ge datasetsWhats Kylinkylin / kiln / 麒麟-n. (in Chinese art) a mythical animal of composite form Open Sourced on Oct 1st, Be accepted as Apache Incubator Project on Nov 25th, 第3頁Big Data EraMore and more data becoming available on HadoopLimitations in existing Business Intelligence (BI) ToolsLimited

3、support for HadoopData size growing exponentiallyHigh latency of interactive queriesScale-Up architectureChallenges to adopt Hadoop as interactive analysis systemMajority of analyst groups are SQL savvyNo mature SQL interface on HadoopOLAP capability on Hadoop ecosystem not ready yet第4頁5Why notBuild

4、 an engine from scratch?第5頁Extreme Scale OLAP EngineKylin is designed to query 10+ billions of rows on HadoopANSI SQL Interface on HadoopKylin offers ANSI SQL on Hadoop and supports most ANSI SQL query functionsSeamless Integration with BI ToolsKylin currently offers integration capability with BI T

5、ools like Tableau.Interactive Query CapabilityUsers can interact with Hive tables at sub-second latencyMOLAP CubeDefine a data model from Hive tables and pre-build in KylinScale Out ArchitectureQuery server cluster supports thousands concurrent users and provide high availabilityFeatures Highlights第

6、6頁Compression and Encoding SupportIncremental Refresh of CubesApproximate Query Capability for distinct count (HyperLogLog)Leverage HBase Coprocessor for query latencyJob Management and MonitoringEasy Web interface to manage, build, monitor and query cubesSecurity capability to set ACL at Cube/Proje

7、ct LevelSupport LDAP IntegrationFeatures Highlights第7頁Cube Designer第8頁Job Management第9頁Query and Visualization第10頁Tableau Integration第11頁CaseCubeSizeRawRecordsUserSessionAnalysis26TB28+billionrowsClassifiedTrafficAnalysis21TB20+billionrowsGeoXBehaviorAnalysis560GB1.2+billionrowseBay90% query 5 secon

8、dsBaiduBaidu Map internal analysisMany other Proof of ConceptsBloomberg Law, British GAS, JD, Microsoft, StubHub, Tableau Who are using Kylin第12頁http:/kylin.ioAgendaWhats Apache Kylin?Tech HighlightsPerformanceRoadmapQ&A第13頁OLAPCubeKylin Architecture Overview15SQL-Based Tool(BI Tools: Tableau)JDBC/O

9、DBCSQL Online AnalysisData Flow Offline Data Flow Clients/Users interactive withKylin via SQL OLAP Cube is transparent tousersMid Latency - MinutesHadoopHiveStar Schema DataLow Latency -SecondsDataCube(HBase)Key Value Data3rd Party App(Web App, Mobile)REST APISQLREST ServerQuery EngineRoutingMetadat

10、aCube Build Engine(MapReduce)第14頁Cube: Fact Table: Dimensions: Measures: Storage(HBase): DimDimDimFactSourceStar SchemaColumn FamilyRow Keyrow Arow Brow CColumnVal 1Val 2Val 3TargetHBase StorageMappingCube MetadataData ModelingEnd UserCube ModelerAdmin第15頁time, itemtime, item, locationtime, item, lo

11、cation, suppliertimeitemlocationsuppliertime, locationTime, supplieritem, locationitem, supplierlocation, suppliertime, item, suppliertime, location, supplieritem, location, supplier1-D cuboids2-D cuboids3-D cuboids4-D(base) cuboidBase vs. aggregate cells; ancestor vs. descendant cells; parent vs. c

12、hild cells.5.(9/15, milk, Urbana, Dairy_land) - (9/15, milk, Urbana, *) - (*, milk, Urbana, *) - (*, milk, Chicago, *) - (*, milk, *, *) - OLAP Cube Balance between Space and TimeCuboid = one combination of dimensionsCube = all combination of dimensions (all cuboids)0-D(apex) cuboid第16頁Cube B

13、uild Job Flow第17頁How To Store Cube? HBase Schema第18頁Dynamic data management framework.Formerly known as Optiq, Calcite is an Apache incubator project, used byApache Drill and Apache Hive, among others.How to Query Cube?Query Engine Calcite第19頁Metadata SPI Provide table schema from Kylin metadataOpti

14、mize Rule Translate the logic operator into Kylin operatorRelational Operator Find right cube Translate SQL into storage engine API call Generate physical execute plan by linq4j java implementationResult Enumerator Translate storage engine result into java implementation result.SQL Function Add Hype

15、rLogLog for distinct count Implement date time related functions (i.e. Quarter)How to Query Cube?Kylin Extensions on Calcite第20頁Query Engine Kylin Explain PlanSELECT test_cal_dt.week_beg_dt,test_category.category_name, test_category.lvl2_name, test_category.lvl3_name,test_kylin_fact.lstg_format_name

16、, test_sites.site_name, SUM(test_kylin_fact.price) AS GMV, COUNT(*) AS TRANS_CNTFROM test_kylin_factLEFT JOIN test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dtLEFT JOIN test_category ON test_kylin_fact.leaf_categ_id = test_category.leaf_categ_id AND test_kylin_fact.lstg_site_id =test_catego

17、ry.site_idLEFT JOIN test_sites ON test_kylin_fact.lstg_site_id = test_sites.site_idWHERE test_kylin_fact.seller_id = 123456OR test_kylin_fact.lstg_format_name = NewGROUP BY test_cal_dt.week_beg_dt, test_category.category_name, test_category.lvl2_name, test_category.lvl3_name,test_kylin_fact.lstg_for

18、mat_name,test_sites.site_nameOLAPToEnumerableConverterOLAPProjectRel(WEEK_BEG_DT=$0, category_name=$1, CATEG_LVL2_NAME=$2,CATEG_LVL3_NAME=$3,LSTG_FORMAT_NAME=$4, SITE_NAME=$5, GMV=CASE(=($7, 0), null, $6), TRANS_CNT=$8)OLAPAggregateRel(group=0, 1, 2, 3, 4, 5, agg#0=$SUM0($6), agg#1=COUNT($6), TRANS_

19、CNT=COUNT()OLAPProjectRel(WEEK_BEG_DT=$13, category_name=$21, CATEG_LVL2_NAME=$15, CATEG_LVL3_NAME=$14,LSTG_FORMAT_NAME=$5, SITE_NAME=$23, PRICE=$0)OLAPFilterRel(condition=OR(=($3, 123456), =($5, New)OLAPJoinRel(condition=($2, $25), joinType=left)OLAPJoinRel(condition=AND(=($6, $22), =($2, $17), joi

20、nType=left)OLAPJoinRel(condition=($4,$12), joinType=left)OLAPTableScan(table=DEFAULT, TEST_KYLIN_FACT, fields=0,1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)OLAPTableScan(table=DEFAULT, TEST_CAL_DT, fields=0,1)OLAPTableScan(table=DEFAULT, test_category, fields=0,1, 2, 3, 4, 5, 6, 7, 8)OLAPTableScan(table=DEFAU

21、LT, TEST_SITES, fields=0,1, 2)第21頁Plugin-able storage engineCommon iterator interface for storage engineIsolate query engine from underline storageTranslate cube query into HBase table scanColumns, Groups Cuboid IDFilters - Scan Range (Row Key)Aggregations - Measure Columns (Row Values)Scan HBase ta

22、ble and translate HBase result into cube resultHBase Result (key + value) - Cube Result (dimensions + measures)How to Query Cube?Storage Engine第22頁Curse of dimensionality: N dimension cube has 2N cuboidFull Cube vs. Partial CubeHugh data volumeDictionary EncodingIncremental BuildingHow to Optimize C

23、ube?Cube Optimization第23頁Full CubePre-aggregate all dimension combinations“Curse of dimensionality”: N dimension cube has 2N cuboid.Partial CubeTo avoid dimension explosion, we divide the dimensions intodifferent aggregation groups2N+M+L 2N + 2M + 2LFor cube with 30 dimensions, if we divide these di

24、mensions into 3group, the cuboid number will reduce from 1 Billion to 3 Thousands230 210 + 210 + 210Tradeoff between online aggregation and offline pre-aggregationHow to Optimize Cube?Full Cube vs. Partial Cube第24頁How to Optimize Cube?Partial Cube第25頁Data cube has lost of duplicated dimension values

25、Dictionary maps dimension values into IDs that will reduce the memory and storagefootprint.Dictionary is based on TrieHow to Optimize Cube?Dictionary Encoding第26頁How to Optimize Cube?Incremental Build第27頁CubeInvertedIndexStorageformatPre-aggregatedcuboidsSharding,columnarstorage,withinvertedindexonr

26、owblocksQuerymethodCuboidscanningMassiveparallelprocessingStrengthPre-aggregatehugehistoricdatatosmallsummariesSwiftresponsetoreal-timedataWeaknessTaketimetobuildSlowatscanninglargedatavolumeStreaming, ongoing effortCube is great, butSometimes we want to drill down to row level informationCube takes

27、 time to build, how about real-time analysis?Streaming with inverted index第28頁streamingKarfkahourly/dailybatchminutes batchInvertedIndexReal-time StoreKylin 0.8, Lambda ArchitectureSQL QueryHybrid StorageInterfaceCubeHistoric Store第29頁http:/kylin.ioAgendaWhats Apache Kylin?Tech HighlightsPerformance

28、RoadmapQ&A第30頁Kylin vs. Hive#QueryTypeReturn DatasetQueryOn Kylin (s)QueryOn Hive (s)Comments1High LevelAggregation40.129157.4371,217 times23Analysis QueryDrill Down toDetail22,669325,0291.61512.058109.206113.12368 times9 times4Drill Down toDetail524,78022.426383.21278 times5Data Dump972,00249.054N/A100500200150SQL #1SQL #2SQL #3HiveKylinHighLevelAggregationAnalysisQueryDrillDownto DetailLow LevelAggregationTransaction LevelBased on 12+B records case第31頁Performance - ConcurrencyLinear scale out with more nodes第32頁Performance - Query L