工作室天下武功唯快不破saphana实战培训

SAPMatchInsightpoweredby“SAP的参与改变了教练、队员、球迷和媒体的足球体验。设想一下：十个球员用三个球进行有球训练，十分钟就能产生超过七百万个数据点，这个数字是多么庞大。然而，SAPHANA可以实时处理这些数据。在SAP的协助下，我们球队可以实时分析这些海量数据，从而制定培训计划并备战下场比赛。”SAPHANASAPHANASAPHANASAPHANASAPHANASQL实施SAPHANA

SAPHighPerformanceApplication(基于HANA和SAPUI5

SAP SAP

SAP BatchComputeBatchCompute擎 事件处SAPSAP

储储SAPReplicationServer,SAPBusinessObjectsData/

SAPHANA

SAP+

SAPHANA10TB|100SAP数据仓库查询:300ms-500ms 随机分析:SAP数据加载DataServices500GB/hr| 数据数据

SAPHANAStudio基于©2014SAPAG.Allrights SAPHANA

SAPHANA1

appsfor(&分析

on

on

SAPHANABOBJSAPSAPHANABOBJSAPHANARDSHANAFinance&BIon基于AmazonWebServicesbyHANAplanning&OneononSAPHANA作为OLAPSAPHANA1

ETL

行存

SAPSAPHANA1

内存容量：128GB~50TB~单机从128GB~单机从128GB~4TB100YOYSAPHANA析，9省集中MSS系统-BWonHANA900500SAPHANABIONHANA2011-6©2014SAPAG.Allrights SAPHANA&SAPHANA SAPHANASAPHANASAPHANASAPHANASAPHANASQL实施SAPHANAClients(planned,BI4Clients(planned,BI4MSBI4ERPSAPBusinessObjectsSBOforSBODiskDataLogPersistencePageRowCalcSQLSQLSessionOtherSource3rd©2014SAPAG.Allrights TheIMC MS Clients(planned,eg SessionSession

I SBOPRequestProcessing/ExecutionRequestProcessing/ExecutionCalcSQLSQLERP PersistencePagePersistencePage

RowRowfor ©2014SAPAG.Allrights LoadingDataintoSAP

MS

Clients

BI4BI4BI4RequestRequestProcessing/ExecutionControlSQLParser SQL Calc

SessionSessionBusinessObjectsBusinessObjects

for

RowPersistencePersistencePageDiskDataDiskDataLog ©2014SAPAG.Allrights Data

MS

BI4BI4BI4 SessionSessionERP BusinessBusinessObjects

SBOSBOfor

RowRowPagePersistencePage

DiskDataDiskDataLog3rd©2014SAPAG.Allrights MSClientsMSClients(planned,BI4BI4ERPDiskDataLogPersistencePageSessionSQLParser SQL CalcRow SBOSBOforBusinessObjects3rd©2014SAPAG.Allrights

MS

Clients(planned,

BI4BI4BI4 SessionSessionCalcSQLSQLBusinessObjects SBOSBOforOtherSource3rd©2014SAPAG.Allrights StorageSeparation(Main& EnableshighcompressionandhighwriteOnlyindeltastoragebecausewriteperformanceatthesame Theupdateisperformedbyinsertingaentryintothedelta

Main

DataCompressioninMainCompressionbycreating

ReadAlwayshavetoreadfrommain&deltastoragesandandapplyingfurthercompressionSpeedDataloadintoCPUEqualitycheck→

theEngineusesmultiversionconcurrencycontrol(MVCC)toensureconsistentreadoperations.©2014SAPAG.Allrights©2014SAPAG.Allrightsdeltamerge

DeltaMergeSeenextColumnStoreDeltaManagementDeltaMergeTomovechangesindeltastorageintothecompressedandreadoptimizedmainHappensEvenduringmergeoperationthecolumnartablewillbestillavailableforreadandwriteTofulfilthisrequirement,aseconddeltaandmainstorageareusedBefore AfterMergeMerge

©2014SAPAG.Allrights PurposeandWhyDoesAnIn-memoryDatabaseNeedAPersistenceMainMemoryisvolatile.WhathappensPower→Dataneedstobestoredinanon-volatileBackupandSAPin-memorycomputingengineoffersonepersistencelayerwhichisusedbyrowstoreandcolumnstoreRegular→fullpersistedimageofDBattimeofLogscapturingallDBtransactionssincelastsavepoint(redologsandundologs→restoreDBfromlatestsavepointAbilitytocreate→usedforSystemRestartandPopulationofIn-memoryActionsDuringSystemLastsavepointmustberestoredUndologsmustbereadforuncommittedtransactionssavedwithlastRedologsforcommittedtransactionssincelastsavepointCompletecontentofrowstoreisloadedintomemoryColumnstoretablesmaybemarkedforpreloadornotOnlytablesmarkedforpreloadareloadedintomemoryduringIftableismarkedforloadingondemand,therestoreprocedureisinvokedonfirstLookandInformationModelerDatabaseChoicetopublishandconsumeat4levelsofDataDataSourceschemas(metadata)–massandselectiveDataProvisioningforSAPBusinessApplications(bothinitialloadandreplication)AnalyticPrivileges/SecurityInformationModelerAttributes–descriptiveMeasures–datathatcanbequantifiedandAttributeViews–i.e.AnalyticViews–i.e.CalculationViews–similartovirtualproviderwithservicesconceptinLeveled–basedonmultipleParent-childAnalyticPrivilege–securityNavigatorView-HANAInstanceHANAServerNameandInstanceNumberUserDatabaseSchemaContent:ColumnViews,NavigatorView-ContentisorganizedinPackagesAttributeViews,AnalyticViews,CalculationViews,AnalyticPrivilegesorganisedinfolders©2014SAPAG.All©2014SAPAG.Allrights ©©2014SAPAG.Allrights SAPHANASAPHANASAPHANASAPHANASAPHANASQL实施SAPHANA

©©2014SAPAGoranSAPaffiliatecompany.Allrights SAPHANA123456…SAP

2345

SAPHANA数据表分区，是指在行方向上对一张完整的数据表按照一定的逻辑循序进行划分，分区的子表作为一个整体，对于大多数SQL查询和数据库操作语句(DML在大型多节点SAPHANA系统上，利用数据表的分区操作可以对巨大体积数据表的管理带来便利，均匀分布系统负载，优化应用运行性能…等等©2014SAPAGoranSAPaffiliatecompany.Allrights 并不仅仅是多节点SAPHANA数据库可以利用数据表分区，对单节点SAP123 >256…

<2<2<2<2456<26SAP©2014SAPAGoranSAPaffiliatecompany.Allrights 进行了表分区之后，子表共享metadata但是却拥有独立的mainindexdelta少deltamerge操作对整个系统资源的占用情况INSERTINTOTABLE… NodeNodeNodeNodeDelta 进行了表分区之后，子表共享metadata,但是却拥有独立的mainindex,deltadelta日志。因此当数据更新只是在个别子表上进行的时候，适当的对表进行分区，将减少deltaSELECTSELECTFROMWHERE国家=XXXNodeNodeNode时候，所有存储该表子表的服务器会并行执行Query，利用Scaleout的SAPSELECTSELECTFROMWHERE月份=1月NodeNode2NodeINSERTINTOTABLES… NodeNodeNodeNode SAPHANASAPHANA的数据表分区方案强烈的依赖如：系统负荷，数据表结构，SQL查询方式等等，利用HANA提供的系统数据视图(如:M_CS_TABLES)定期检查并预估数据表的体积，SAP建议数据表的分区至少在数单独从系统优化角度进行表的分区。例如利用数据表的分区减少DeltaMerge对系统资源占用，但是可能会大大降低应对SAPNetWeaverBusinessWarehouseonHANA的表进行分区。BWonHANA有自动表分区的机制.除非SAP支持的建议，CREATECOLUMNTABLEmytab(aINT,bINT,cINT,PRIMARYKEY(a,b))PARTITIONBYHASH(a,b)PARTITIONS4;COLUMNmytabINT,bINT,INT,PRIMARYPARTITIONHASHb)ALTERTABLEmytab_hashPARTITIONBYHASH(v_jldw)PARTITIONSSELECT*FROMmytab_HASHWHEREHANA系统会根据SQLQuery的Where语句里包含的过滤条件，对CREATECOLUMNTABLEmytab(aINT,bINT,cINT)PARTITIONBYROUNDROBINPARTITIONS4RangeRange分区操作要求对表统会报错。因而需要预定义一个被称为’RestPartition’的分区子表来接受这些记录 CREATECOLUMNTABLEmytab(aINT,bINT,cINT,PRIMARYKEY(a,b))PARTITIONBYRANGE(a)(PARTITION1<=VALUES<PARTITION5<=VALUES<20,PARTITIONVALUE=44,PARTITIONOTHERS)繁DeltaMergeHashRangeHashRangeRound-RobinRangeHashHashHashRange分 HashRange分区是最典型的一种多层分区方式，如：在第一层分区利用Hash分区的时间分区原则降低每个节点的DeltaMerge带来的资源消耗，如下图所示CREATECOLUMNTABLEmytab(aINT,bINT,cVARCHAR(10),PRIMARYKEY(a,b))PARTITIONBYHASH(a,b)PARTITIONS3,RANGE(c)(PARTITIONVALUE=‘JAN’,PARTITIONVALUE=‘FEB’,PARTITIONVALUE=‘MAR’,PARTITIONOTHERS)Round-RobinRange分区及HashHash分 Round-RobinRangeCREATECOLUMNTABLEmytab(aINT,bINT,cINT)PARTITIONBYROUNDROBINPARTITIONS4,RANGE(c)(PARTITION1<=VALUES<5,PARTITION5<=VALUES<HashHashCREATECOLUMNTABLEmytab(aINT,bINT,cINT,PRIMARYKEY(a,b))PARTITIONBYHASH(a,b)PARTITIONS4,HASH(c)PARTITIONS7对于表的分区的监控，SAPHANA一共提供了如下四张系统视图可以查询到目标表的分区方案明细SELECTSCHEMA_NAME,TABLE_NAME,PARTITION_SPEC,TABLE_OID,IS_COLUMN_TABLEFROMTABLESWHEREPARTITION_SPECISNOTNULLSELECTHOST,SCHEMA_NAME,TABLE_NAME,PART_ID,MEMORY_SIZE_IN_MAIN,MEMORY_SIZE_IN_DELTA,RECORD_COUNTFROMM_CS_TABLESORDERBYHOST,SCHEMA_NAME,TABLE_NAMEDESCSELECT*FROMM_TABLESWHERESELECTSCHEMA_NAME,TABLE_NAME,PARTITION,SUBPARTITION,RANGEFROMM_CS_PARTITIONSORDERBYSCHEMA_NAMEASC,TABLE_NAMEASC,PARTITIONASC,SUBPARTITIONASCClientSideStatement例如:从Hash(X)3到Hash(X)2例如从Hash(X)3到Hash(Y)拆分具体场景从分区子表数n到分区子表数m,且例如Hash(X)3到Hash(XALTERTABLEmytabPARTITIONBYHASH(a)PARTITIONSALTERTABLEmytabMERGE对数据表记录拥有长时间的锁（仅SELECT操作是允许的在操作开始之前，会先进行DeltaMergeHANA的StatisticsServer会保持对数据增长和表的体积的监控，参照分区的拆分与合并一节，进行分区表的合并与再分区可能需要对分区子表进行移动。割子表，对多层分区需要将整个分区组进行移动，建议在进行分区子表的移动之前，查看M_C_AITIOSABLE_LOTION了解当前分区子表或分区组所在位置信息ALTERTABLEmytabMOVEPARTITION2TOALTERTABLEmytabMOVETOCREATECOLUMNTABLEmytab(aINT,bINT)PARTITIONBYHASH(a)PARTITIONS2,RANGE(b)(PARTITION1<=values<5,PARTITIONOTHERS)ALTERTABLEmytabMOVEPARTITION2TOCREATEROWTABLEmytab(IINTPRIMARYKEY)ATALTERTABLEmytab5ADDREPLICAATLOCATIONClientSideStatement境，SQLQuery会被重新定向和发QueryPlan和连接线程的运行信息数据表所在节点发送SQLQuery执行请求，这也被称为ClientSideStatementRoutingClientSideStatementRouting在多节点系统上，表的分区和移动都会改变，因此系统会将queryplan重新进行编查询的优化，需要设置HANAServer段和Client端的相关参数***在对表进行分区针对应用进行优化之前，建议检查ClientSideStatementRouting机制是否正常，可以通过系统视图M_CONNECTIONS查询客户端和HANAServer的连接建立信息ClientSideStatementRoutingClientlibrary(SQLDBC,

EnabledNoclientdistributionis

Connectiondistributionis

Both Statementroutingis Statementroutingis Enabled Noclient Noclientdistributionis

Statementrouting Statementroutingis

ConnectiondistributionisBothClientSideStatementStringUSERNAME="username";StringPASSWORD=Propertiesprops=newProperties();props.put("user",USERNAME);…sqlConn=DriverManager.getConnection(DB_URL,propsmode参数仅提供测试，不建议在SAPHANASAPHANASAPHANASAPHANASAPHANASQL实施SAPHANARowStorevs.ColumnStoreWhentoUseWhichStoreModelingOnlyPossibleForColumnThisanswersthefrequentlyasked"WhereshouldIputatable–rowstoreorcolumnInformationModeleronlyworkswithcolumnReplicationservercreatestablesincolumnstoreperDataServicescreatestablesincolumnstoreperSQLtocreatecolumntable:"CREATECOLUMNTABLEStorecanbechangedwith"ALTERTABLESystemTablesAreCreatedWhereTheyFitAdministrativetablesinrowSchemaSYS→caches,administrativetablesofAdministrativetablesincolumnSchema_SYS_BI→metadataofcreatedviews+masterdataforSchema_SYS_BIC→somegeneratedtablesforSchema_SYS_REPO→e.g.listsofactive/modifiedversionsofModelingProcessPhysicaltablesarecreateddynamically(1:1ofsourcesystem

arecreatedAttributeAnalytic

ConsumewithBICS,SQL,Physicaltablesareloadedwith

ColumnviewsFrameworkforModelingwithin Tables/AttrTables/Attr Base Only

not

SchemaorCalcViewsCalcViews

TryCalc

notAttributeAttributeWhatisanAttributeAttributesaddcontexttoAttributesaremodeledusingAttributeCanberegardedasMasterDataCanbelinkedtofacttablesinAnalyticalAmeasuree.g.weightcanbedefinedasanTableJoinsandJoinfullOuter,textTableLanguageAnalyticalAnAnalyticalViewcanberegardedasaAnalyticalViewsdoesnotstoreanydata.ThedataisstoredincolumnstoreortableviewbasedontheAnalyticalViewStructure.AttributeandCancreateAttributeMusthaveatleastoneMusthaveatleastoneCancreateCalculatedCanrenameAttributeandMeasuresonthepropertytabAnalyticalView:DataTherearethreemainviewsonecanselectfromwhenpreviewingRawData–tableformatofDistinctValues–graphicalandtextformatidentifyinguniqueAnalysis–selectfields(attributesandmeasures)todisplayingraphicalNoSQLcodingUnion,Join,ProjectionnodesJoinColumnTables(Analytical SQLorSQLScriptrequiredtocreateScriptbasedCalculationWriteSQLSelectstatementsagainstexistingrawtablesorColumnStoresDefineoutputstructure,activationcreatescolumnstorebasedonScriptCalcEngineforTheeasiestwaytothinkofCalculationModelsistoseethemasdataflowgraphs,wherethemodelercandefinedatasourcesasinputsanddifferentoperations(join,aggregation,projection,…)ontopofthemfordataTheCalculationEnginewillbreakupamodel,forexamplesomeSQLScript,intooperationsthatcanbeprocessedinparallel(rulebasedmodeloptimizer).Thentheseoperationswillbepassedtothedatabaseoptimizerwhichwilldeterminethebestplanforaccessingroworcolumnstores(algebraictransformationsandcostbasedoptimizationsbasedondatabasestatistics).SQLScript/R/BFL(BusinessFunctionThreewaystoimplementin-memorydataminingandstatisticalSQLScriptisasetofSQLextensionswhichallowdeveloperstopushdata-intensivelogicintothedatabaseinordertoavoidmassivedatacopiestotheapplicationserverandtoleveragesophisticatedparallelexecutionstrategiesoftheRThroughtheRintegrationsolution,developerscanleverageopensourceR’s3000+externalpackagestoperformwide-rangedataminingandstatisticalBFL(BusinessFunctionBFListhecalculationlibraryfortheapplicationsbuiltontopoftheSAPHANAdatabase.ThebusinessfunctionsarewritteninC++andexecutedindatabasecalculationengine.BFLhasaroadmapfordataminingandstatisticalalgorithms.ThesetofSQLextensionsfortheSAPHANAdatabasewhichallowdeveloperstopushdataintensivelogicintothedatabaseiscalledSQLScript.TheseextensionsarekeystoavoidingmassivedatacopiestotheapplicationserverandtoleveragesophisticatedparallelexecutionstrategiesoftheSQLScriptV2supportsstoredprocedures,whichprovidesenhancedcontrolflowcapabilitiesandispositionedtobemoresuitableforpushingcomplexpartsofapplicationlogictothedatabase.Itcanmeetsomesimplerequirementforreporting,likejoin,aggregation,etc.Whenitcomestodataminingandstatisticanalysis,SQLScriptisnotsuitableforimplementingcomplexalgorithms.SAPHANASAPHANASAPHANASAPHANASAPHANASQL实施SAPHANA地利用SAPHANA的复杂并行执行机制,列存储,查询优化等特性.,SQLScript不适合用于复杂的算法实现,.SQLScript函数可以返回多个结果集但是SQL加了可读性.标准SQL虽然支持建立SQL视图,但是SQL视图不支持输入参数通过SQLScript可以用本地变量来保存临时结果而无需定义变量类型,SQLScript是SQL的扩展→Insert,Update,Delete,DDL,IF/Else,游标loopsbig_pub_ids=SELECTpublisherFROMbooks --QueryQ1GROUPBYpublisherHAVINGCOUNT(isbn)>big_pub_books=SELECTtitle,price --QueryQ2FROM:big_pub_ids,publishers,booksWHEREpub_id=pidANDpub_id=publisherANDcrcy=:currency;output_pubs=SELECTSUM(price) --QueryQ3FROM:big_pub_booksGROUPBYpublisher;output_year=SELECTSUM(price) --QueryQ4FROM:big_pub_booksGROUPBYyear;

BEGIN =BEGIN =*–p_out2*from–END;p_out3*fromSELECTcolumnout=SELECTA,B,CfromSELECTattributeout=SELECTA,B,Cfromout=SELECTA,B,C,SUM(D)from"ANALYTIC_VIEW"GROUPBYA,B,CWHEREout=SELECTA,B,C,SUM(D)from"ANALYTIC_VIEW"WHEREB='value'ANDC=col_tab=CE_COLUMN_TABLE("COLUMN_TABLE");out=CE_PROJECTION(col_tab,[A,B,C],'"B"=''value''AND"C"=GROUPout=SELECTA,B,C,SUM(D)FROM"COLUMN_TABLE"GROUPBYA,B,CCE_AGGREGATION((col_tab,SUM(D),[A,B,C]);INNERout=SELECTA,B,Y,SUM(D)from"COLTAB1"out=CE_JOIN("COLTAB1","COLTAB2",[KEY1,KEY2],[A,B,Y,LEFTOUTERout=SELECTA,B,Y,SUM(D)from"COLTAB1"LEFTOUTERJOIN"COLTAB2"WHERE"COLTAB1"."KEY1"="COLTAB2"."KEY1"AND"COLTAB1"."KEY2"="COLTAB2"."KEY2"KEY2],[A,B,Y,D])SQLout=SELECTA,B,C,SUBSTRING(D,2,5)FROMCE_PROJECTION(:proj_tab,["A","B","C",col_tab1=SELECTA,B,C,DFROM"COLUMN_TABLE1";col_tab1=SELECTA,B,C,DFROM"COLUMN_TABLE1";col_tab2=SELECTA,B,C,DFROM"COLUMN_TABLE2";out=SELECT*:col_tab1UNIONALLSELECT*FROMcol_tab1=管理窗口→追踪配置（选项卡）→全局(ERROR),警告(WARNING),信息(INFO)或SQL追踪管理窗口→追踪配置（选项卡管理窗口→追踪配置（选项卡）→句法:ylt_tab=select*fromsqlscript_trace;select*from<temp_table>;calltruncate_sqlscript_trace;编译调试信息ALTERPROCEDUREgetOutputRECOMPILEWITH理解数据流结构和变量的重命名select*fromSYS.PROCEDURE_DATAFLOWSasSYS.PROCEDURE_MAPPINGaswheredf.dataflow_name=cedure_name=‘GETOUTPUT‘orderCALLgetOutput(0,'EUR',NULL,NULL)WITHOVERVIEWINselect*fromselect*from使用工具，比如JSONViewer避免连接(JOIN)输入数据源中不带WHERE条件的视图,|constant’skey|intvalue|doublevalue|:SAPHANASAPHANASAPHANASAPHANASAPHANASQL实施SAPHANASAPHANA设备方法论（安装和升级）永久化存储备份及恢复（系统备份容灾能力期隐患/SAPHANASAPSAP

SAPSolutionmanagerSAPHANA作为设备提供了E2E的SAPHANA监控和管理©2014SAPAG.©2014SAPAG.Allrights SAPHANA技术合作伙 SAPHANA硬件合作伙SAPHANASAPHANAPlatformSAPHANAEnterpriseSAPHANAfor2x10WestmereEX2Intel处理器)128GB160GBPCIe-FlashSSD1TBSASSSD3x1GBn/wor1x10GBn/w总线),S2x10WestmereEX24Intel处理器256GB320GBPCIe-FlashSSD1TBSASSSD3x1GBn/wor1x10GBn/w总线),M4x10WestmereEX48Intel处理器512GB640GBPCIe-FlashSSD2TBSASSSD3x1GBn/wor1x10GBn/w总线),L8x10WestmereEX8Intel处理器1TB1.2TBPCIe-FlashSSD4TBSASSSD3x1GBn/wor1x10GBn/w总线),/pam(“HANA©2014SAPAG.Allrights SAPHANA“on-premise”

“SaaS设备运营的概念基础设施SAP

©2014SAPAG.©2014SAPAG.AllrightsSAPHANABI连接 SAPHANASPSAPSAPHANASAPHANAStudioSAPHANAServiceSMP(SPSAPHANAAllremoteconnectionsaredoneviasecurewebIT(ITSAPHANASAPSAPHANA设备内容的技术操作手册(/hana本手册会衍生出操作SAPHANA必须的定期职责SAPHANA设备方法论（安装和升级）容灾能力监控和管理SAPHANA SAPHANA（这里指redo日志）通过记录数据库事

1

323 SAPHANA设备方法论（安装和升级）备份及恢复（系统备份容灾能力SAPHANA服务 （外部备份

（外部备份

SAPHANAStudioSAPHANAStudio务务BackintforSymantecIBMHP(Data©©2014SAPAG.Allrights 这是计划的当前状态，SAP随时可能修改 从SAPHANASPS4SID数SAPHANA设备方法论（安装和升级）备份及恢复（系统备份监控和管理HANA

例如向外扩展（ScaleM统计服务（仅在一台激活的节点上XSAPHANASAPHANA

SAPHANA…管理HANA软件升级

SAPHANAStudio 数据 数据 数据 数据 数据 点HP,Fujitsu, 点不同的案例（HANASAPBW）对最小安装可能有不同的需求和建议。（比如BW在SAPnote1637145attachedPDF中有不同的定义）

SAPHANA设备方法论（安装和升级）©©2014SAPAG.Allrights 这是计划的当前状态，SAP随时可能修改 SAPHANASAPHA