ApacheKylin在大数据系统中应用课件.ppt

1、Apache KylinOLAP on Hadoophttp:/kylin.ioAgendaWhats Apache Kylin?Tech HighlightsPerformanceRoadmapQ&AExtreme 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 large datas

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

3、 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 yet5Why notBuild an engine fro

4、m scratch?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 Tools like Tableau.

5、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 HighlightsCompression and Enc

6、oding 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/Project LevelSupport LDAP Int

7、egrationFeatures HighlightsCube DesignerJob ManagementQuery and VisualizationTableau IntegrationCaseCubeSizeRawRecordsUserSessionAnalysis26TB28+billionrowsClassifiedTrafficAnalysis21TB20+billionrowsGeoXBehaviorAnalysis560GB1.2+billionrowseBay90%query 5 secondsBaiduBaidu Map internal analysisMany oth

8、er Proof of ConceptsBloomberg Law,British GAS,JD,Microsoft,StubHub,Tableau Who are using Kylinhttp:/kylin.ioAgendaWhats Apache Kylin?Tech HighlightsPerformanceRoadmapQ&AOLAPCubeKylin Architecture Overview15SQL-Based Tool(BI Tools:Tableau)JDBC/ODBCSQL Online AnalysisData Flow Offline Data Flow Client

9、s/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 EngineRoutingMetadataCube Build Engine(MapReduce)Cube:Fact Table:Dimensions:Meas

10、ures:Storage(HBase):DimDimDimFactSourceStar SchemaColumn FamilyRow Keyrow Arow Brow CColumnVal 1Val 2Val 3TargetHBase StorageMappingCube MetadataData ModelingEnd UserCube ModelerAdmintime,itemtime,item,locationtime,item,location,suppliertimeitemlocationsuppliertime,locationTime,supplieritem,location

11、item,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.child cells1.2.3.4.5.(9/15,milk,Urbana,Dairy_land)-(9/15,milk,Urbana,*)-(*,milk,Urbana,*)-(*,milk

12、,Chicago,*)-(*,milk,*,*)-OLAP Cube Balance between Space and TimeCuboid=one combination of dimensionsCube=all combination of dimensions (all cuboids)0-D(apex)cuboidCube Build Job FlowHow To Store Cube?HBase SchemaDynamic data management framework.Formerly known as Optiq,Calcite is an Apache incubato

13、r project,used byApache Drill and Apache Hive,among others.http:/optiq.incubator.apache.orgHow to Query Cube?Query Engine CalciteMetadata SPI Provide table schema from Kylin metadataOptimize Rule Translate the logic operator into Kylin operatorRelational Operator Find right cube Translate SQL into s

14、torage engine API call Generate physical execute plan by linq4j java implementationResult Enumerator Translate storage engine result into java implementation result.SQL Function Add HyperLogLog for distinct count Implement date time related functions(i.e.Quarter)How to Query Cube?Kylin Extensions on

15、 CalciteQuery 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,test_sites.site_name,SUM(test_kylin_fact.price)AS GMV,COUNT(*)AS TRANS_CNTFROM test_kylin_factLEFT JOIN test_cal_dt ON te

16、st_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_category.site_idLEFT JOIN test_sites ON test_kylin_fact.lstg_site_id=test_sites.site_idWHERE test_kylin_fact.seller_id=123456OR test_kylin

17、_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_format_name,test_sites.site_nameOLAPToEnumerableConverterOLAPProjectRel(WEEK_BEG_DT=$0,category_name=$1,CATEG_LVL2_NAME=$2,CATEG_LVL3_NAME=$3,LS

18、TG_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_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(

19、condition=OR(=($3,123456),=($5,New)OLAPJoinRel(condition=($2,$25),joinType=left)OLAPJoinRel(condition=AND(=($6,$22),=($2,$17),joinType=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_D

20、T,fields=0,1)OLAPTableScan(table=DEFAULT,test_category,fields=0,1,2,3,4,5,6,7,8)OLAPTableScan(table=DEFAULT,TEST_SITES,fields=0,1,2)Plugin-able storage engineCommon iterator interface for storage engineIsolate query engine from underline storageTranslate cube query into HBase table scanColumns,Group

21、s Cuboid IDFilters-Scan Range(Row Key)Aggregations-Measure Columns(Row Values)Scan HBase table and translate HBase result into cube resultHBase Result(key+value)-Cube Result(dimensions+measures)How to Query Cube?Storage EngineCurse of dimensionality:N dimension cube has 2N cuboidFull Cube vs.Partial

22、 CubeHugh data volumeDictionary EncodingIncremental BuildingHow to Optimize Cube?Cube OptimizationFull 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 grou

23、ps2N+M+L 2N+2M+2LFor cube with 30 dimensions,if we divide these dimensions 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 CubeHow to Optimize Cube?Partial C

24、ubeData cube has lost of duplicated dimension valuesDictionary maps dimension values into IDs that will reduce the memory and storagefootprint.Dictionary is based on TrieHow to Optimize Cube?Dictionary EncodingHow to Optimize Cube?Incremental BuildCubeInvertedIndexStorageformatPre-aggregatedcuboidsS

25、harding,columnarstorage,withinvertedindexonrowblocksQuerymethodCuboidscanningMassiveparallelprocessingStrengthPre-aggregatehugehistoricdatatosmallsummariesSwiftresponsetoreal-timedataWeaknessTaketimetobuildSlowatscanninglargedatavolumeStreaming,ongoing effortCube is great,butSometimes we want to dri

26、ll down to row level informationCube takes time to build,how about real-time analysis?Streaming with inverted indexstreamingKarfkahourly/dailybatchminutes batchInvertedIndexReal-time StoreKylin 0.8,Lambda ArchitectureSQL QueryHybrid StorageInterfaceCubeHistoric Storehttp:/kylin.ioAgendaWhats Apache

27、Kylin?Tech HighlightsPerformanceRoadmapQ&AKylin 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 times

28、5Data Dump972,00249.054N/A100500200150SQL#1SQL#2SQL#3HiveKylinHighLevelAggregationAnalysisQueryDrillDownto DetailLow LevelAggregationTransaction LevelBased on 12+B records casePerformance-ConcurrencyLinear scale out with more nodesPerformance-Query Latency90%queries 5sGreen Line:90%tile queriesGray

29、Line:95%tile querieshttp:/kylin.ioAgendaWhats Apache Kylin?Tech HighlightsPerformanceRoadmapQ&AKylin Evolution Roadmap201520142013InitialPrototypefor MOLAP Basic end to endPOCMOLAP IncrementalRefresh ANSI SQL ODBC Driver Web GUI ACL Open SourceHOLAPNext Gen LambdaArch AutomationStreaming OLAP JDBC D

30、riverNew GUI Excel Support moreCapacityManagementIn-MemoryAnalysis(TBD)Spark(TBD)moreTBDFutureSep,2013Jan,2014Sep,2014H1,2015Kylin Core Fundamental framework ofKylin OLAP EngineExtension Plugins to supportforadditionalfunctionsandfeaturesIntegration Lifecycle Management Supportto integrate with othe

31、rapplicationsInterface Allows for thirdparty users tobuild more features via user-interface atop Kylin coreDriver ODBC and JDBC DriversKylin OLAPCoreExtension Security Redis Storage Spark Engine DockerInterface Web Console Customized BI Ambari/Hue PluginIntegration ODBC Driver ETL Drill SparkSQLKylin Ecosystem