HANA实训指导书.doc

数据仓库HANA实训指导书1、连接 SAP HANA 数据库32、加载数据62.1 图形界面方式62.2 命令行方式103、数据清洗114、数据建模分析124.1基础数据创建124.2 范式分解124.3 建模144.3.1 课程信息建模154.3.2 学生信息建模174.3.3 选课信息建模204.3.4 成绩绩点建模215、HANA数据挖掘255.1 HANA内嵌数据挖掘平台PAL255.2 SAP HANA PAL中的数据挖掘算法及功能清单265.3 关联规则Apriori算法简介275.4 数据挖掘实例课程关联规则分析315.4.1 定义原始待分析数据存储过程315.4.2 定义算法执行最终结果过程315.4.3 定义元数据325.4.4 生成APRIORI规则325.4.5 原始数据准备325.4.6 定义临时表335.4.7 定义执行结果表355.4.8 执行APRIORI算法365.4.9 建模分析执行结果366、实验总结411、连接 SAP HANA 数据库安装 HANA 客户端工具和建模工具SAP HANA Studio，1） 打开SAP HANA Studio , 弹出如下界面：2）单击SAP HANA modeler(建模)左边栏鼠标右键，选择Add System，弹出如下对话框：3）分别填入如下内容：1、Host Name：2、Instance Number: (HANA实例标示，00代表第1个，最大99)3、表述信息根据自己喜好填写。然后单击Next。进入下面对话框4）分别输入HANA数据库管理员分配的账户和密码。单击完成。连接HANA服务器成功后。进入如下图所示界面：2、加载数据安装 HANA 客户端工具和建模工具后，需要将数据加载到 HANA 实例中，以开始执行示例应用程序。数据加载可能非常复杂，SAP 为不同的数据加载业务情景提供了多个解决方案。2.1 图形界面方式这里将使用由 SAP HANA 建模工具提供的基本文本数据文件加载功能。它可以直接将本地文件加载到 HANA 系统中。操作如下：1. 打开 HANA 建模工具，单击“文件”(File) 菜单并选择“导入”项(Import)。2. HANA 建模工具将显示“导入”(Import) 向导，请选择“从本地文件导入数据”(Data from Local File)。然后单击“下一步”(Next) 按钮。3. 选择在之前步骤中创建的 HANA 实例COURSE，然后单击“下一步”(Next) 按钮。4. 单击源文件选项中的“浏览器”(Browser) 按钮。选择示例包中提供的“教务学生数据new.csv”文件。选择“标题行存在”(Header row exists) 选项；将模式设置为“USER01”，并将表名设置为“COURSE”。然后单击“下一步”(Next) 按钮。5. 在“管理表定义和数据映射”(Manage Table Definition and Data Mappings)图表中定义表结构：选择复选框将 CID 设置为关键字段，并设置为非空，将 BIRTHDAY的数据类型从 NVARCHAR 更改为 DATE。单击“Finish”按钮，开始导入2.2 命令行方式前面图形界面导入比较直观，但是导入速度很慢，而命令行方式，因为可以设置增加线程数，导入速度很快。打开HANA Studio ，进入SQL 控制台，导入数据文件”教务学生数据new.csv”到数据库SQL语句示例如下： IMPORT FROM CSV FILE /data/data.csv INTO COURSE.COURSE WITH THREADS 20 BATCH 10000 LOCK TABLE -提高列表初始装载性能 RECORD DELIMITED BY n FIELD DELIMITED BY , DATA FORMAT YYYY-MM-DD ERROR LOG /data/DATA.log执行结果：Statement IMPORT FROM CSV FILE /tmp/JWDATA.CSV INTO RAWDATA WITH THREADS 20 BATCH 10000 RECORD DELIMITED . successfully executed in 52.592 seconds (server processing time: 52.584 seconds) - Rows Affected: 03、数据清洗从“教务学生数据new.csv”可以看到原始数据所包含字段，HANA是分析型数据库，所以其默认表模式都是列存储。如下图所示：原始数据表模式结构经过数据清洗，例如，删除成绩为0，即选修课后，没有参加考试的记录，删除课程代码为空的记录等。结果如下表所示：表3.1 选课数据表序号数据数据量备注1总数据量2,058,4302数据清洗后1,839,3353选课代码为空8,1084没有成绩（成绩0分和为空）199,3205入校学年为空976生日为空9377籍贯为空,无18,431 4、数据建模分析4.1基础数据创建我们将导入的数据经过数据清洗后得到原始数据表如下图4.1 所示：图4.1 HANA 建模工具显示原始数据表4.2 范式分解根据数据库范式分解理论，这里将原数据表分解为符合第三范式（3NF）的三个表：学生信息表（STUDENT）,课程信息表（COURSEDATA）,选课情况信息表（SC）。数据库关系图如下图4.2所示：图4.2 HANA 建模工具显示数据库关系图在HANA中，我们使用创建计算视图方式，构建以上三个表：表4.2 数据库模式信息表表名学生信息表（CAC_S）课程信息表（CAC_C）选课情况信息表（CAC_SC）数据量29,9673,7361,840,273课程信息表学生信息表选课情况信息表4.3 建模打开左边导航栏的“Content”内容目录下Package，右键单击属性视图、分析视图和计算视图。完成后，单击下图右方的绿色按钮，进行验证，验证通过单击按钮发布之后，就可以单击，进行数据预览和建模分析4.3.1 课程信息建模 对课程信息创建了计算视图CAC_C，其脚本如下图所示，此外，在右边输出窗口定义，输出列分别为COURSECODE和COURSENAME。下面其它视图都需要建立输出列，并在语义层定义维度与度量。1）课程名相同，课程代码不同的课程： 可以看出排名前10位的课程主要是每个学院都开设的实习类课程。通过Row Count数值可以很容易得到全校课程数目为3,7364.3.2 学生信息建模对课程信息我们创建了计算视图CAC_S，其中利用出生日期，建立计算列，从而获得每个学生的星座。其计算公式如下图所示：1） 全校按照生源地学生分布：通过Row Count数值可以很容易得到2004年以来全校学生数目为29,967人，排名前5位的是上海、江苏、浙江、湖南、山东2） 全校按照入学时间的入校学生分布：在分析视图，选择“入学时间”这个维度，度量选择安装“学号”计算。发现2004，2005年记录缺少。2006年至今的8年每年入校学生入学数基本持平。选择”Table”页面可以看到每年的具体数据如下图： 3） 全校学生按照星座的分布：在分析视图，选择“星座”这个维度，度量选择安装“学号”计算。排名前5位的是天枰座、天蝎座、狮子座、处女座、水瓶座。4.3.3 选课信息建模对选课信息我们创建了图形化计算视图CAC_SC，其计算公式如下图所示：4.3.4 成绩绩点建模对成绩信息我们创建了计算视图CAC_S，其中利用成绩，建立计算列：成绩绩点。其计算公式如下图所示：其得到的数据如下图所示：1） 全校学生生源地的平均成绩分布：在分析视图，选择“生源地”这个维度，度量选择安装“平均成绩”计算。排名前5位的是浙江、河北、河南、福建、山东。选择筛选器（filter）, 可以对数据进行筛选，例如，只查看留学生，台湾、香港、澳门学生的数据：2） 全校学生星座的平均成绩分布：在分析视图，选择“星座”这个维度，度量选择安装“平均成绩”计算。排名前5位的是天枰座、天蝎座、狮子座、处女座、水瓶座。3） 全校学生生源地和绩点的平均成绩分布：在分析视图，选择“生源地”和“绩点”这个维度，度量选择安装“平均成绩”计算。选择筛选器（filter）, 可以对数据进行筛选，例如，查看等级为差的学生的生源地分布数据：排名后5位的是北京、河南、广东、陕西、云南。4） 全校学生星座和绩点的平均成绩分布：在分析视图，选择“星座”和“绩点”这个维度，度量选择安装“平均成绩”计算。选择筛选器（filter）, 可以对数据进行筛选，例如，查看优秀学生的星座分布数据：排名前5位的是处女座、水瓶座、天枰座、双鱼座、射手座。5、HANA数据挖掘5.1 HANA内嵌数据挖掘平台PAL在传统数据模型中,数据库只是作为存取数据一个工具,对于类似下图所示的应用, 客户端从Database获取数据,然后计算,最后再把结果写回Database, 如果数据量过大, 数据传输的开销过大,并且如果客户端的内存不够, 计算分析的过程也将非常缓慢。借助于大内存的优势, SAP HANA的数据挖掘解决方案是把数据敏感的相关计算逻辑都移动到SAP HANA内, 从而省去了数据传输的开销。这个方案就是SAP HANA PAL (预测分析库)。5.2 SAP HANA PAL中的数据挖掘算法及功能清单表5.1 PAL算法清单分类PAL支持算法内嵌函数名 聚类ClusteringAnomaly Detection ANOMALYDETECTIONK- means KMEANS VALIDATEKMEANS Self - Organizing Maps SELFORGMAP分类ClassificationBi - Variate Geometric Regression GEOREGRESSION FORECASTWITHGEOR Bi - Variate Natural Logarithmic RegressionLNREGRESSION FORECASTWITHLNRC4.5 Decision Tree CREATEDT PREDICTWITHDT CHAID Decision Tree CREATEDTWITHCHAID PREDICTWITHDT Exponential Regression EXPREGRESSION FORECASTWITHEXPR KNN KNNSupport Vector MachineSVMTRAINMultiple Linear Regression LRREGRESSION FORECASTWITHLR Polynomial Regression POLYNOMIALREGRESSION FORECASTWITHPOLYNOMIALR Logistic Regression LOGISTICREGRESSION FORECASTWITHLOGISTICR 关联规则AssociationApriori APRIORIRULE LITEAPRIORIRULE 预处理PreprocessingBinning BINNING Inter - Quartile Range Test IQRTEST Sampling SAMPLING Scaling Range SCALINGRANGE Variance Test VARIANCETEST 指数平滑算法Time Series Single Exponential SmoothingSINGLESMOOTH Double Exponential SmoothinDOUBLESMOOTH Triple Exponential SmoothingTRIPLESMOOTH 社会网络分析Social Network AnalysisAnalysis Link PredictionLINK PREDICTION其他MiscellaneousABC Analysis ABC Weighted Score Table WEIGHTEDTABLE 5.3 关联规则Apriori算法简介为了对后续实验所用Apriori算法及其各个参数设定进行分析，本小节对算法做简单的介绍。Apriori是关联规则里一项基本算法。由IBM阿尔马登研究中心Rakesh Agrawal 在 1994年VLDB上提出的（详细的介绍请下载Fast Algorithms for Mining Association Rules这篇文章）。5.3.1 关联规则的目的对于在一个数据集中找出项目之间的关系，也称之为购物篮分析 (market basket analysis)。例如，购买鞋的顾客，有10%的可能也会买袜子，60%的买面包的顾客，也会买牛奶。这其中最有名的例子就是尿布和啤酒的故事。5.3.2 关联规则的应用场合 在商业销售上，关联规则可用于交叉销售，以得到更大的收入； 在医疗方面，可找出可能的治疗组合； 在银行方面，对顾客进行分析，可以推荐感兴趣的服务； 在保险业务方面，如果出现了不常见的索赔要求组合，则可能为欺诈，需要作进一步的调查。5.3.3 规则规则（RULE）形如如果那么(IfThen), 包含两个部分：前者为条件，算法称为PRERULE，后者为结果,算法称为POSTRULE。如下图所示：举例：一个顾客，如果买了可乐，那么他也会购买果汁。If 可乐 Then 果汁图5.1规则示例5.3.4 支持度(Support) 与置信度(Confidence)如何来度量一个规则是否够好？算法通过置信度(Confidence)和支持度(Support)。假设有如下顾客购物信息：TIDItemsT1orangejuice, cokeT2milk, orange juice, window cleanerT3orangejuice, detergentT4orangejuice, detergent, cokeT5windowcleaner设条件的项的集合PRERULE为A，结果POSTRULE的集合为B。支持度计算在所有的交易集中，既有A又有B的概率。P(AB)例如在5条记录中，既有Orange又有Coke的记录有2条。则此条规则的支持度为2/5=0.4。现在这条规则可表述为，如果一个顾客即买了Orange又再买Coke会有40%的可能发生。置信度表示了这条规则有多大程度上值得可信。置信度计算在A中，同时也含有B的概率。即Confidence(A=B)=P(B|A)。例如：计算如果Orange则Coke的置信度。由于在含有Orange的4条交易中，仅有2条交易含有Coke。其置信度为0.5。5.3.5 提升度(Lift)提升度描述的是相对于不用这些规则，使用规则可以提高多少。有用的规则的提升度数值为大于1。计算方式为Lift(A=B)=Confidence(A=B)/Support(B)=Support(A=B)/(Support(A)*Support(B)。项支持度A0.45B0.42C0.4A and B0.25A and C0.2B and C0.15A and B and C0.05规则置信度If B and C then A0.05/0.15*100%=33.33%If A and C then B0.05/0.20*100%=25%If A and B then C0.05/0.25*100%=20%在上例中，Lift(If B and C The A)=0.05/(0.15*0.45)=0.74。而Lift(If A then B)=0.25/(0.45*0.42)=1.32。也就是说对买了A的人进行推荐B，购买概率是随机推荐B的1.32倍。Lift (A-B) = P (AB)/(P(A)P(B)5.3.6 频繁项集如何产生规则呢。可以分两步走。首先找出频繁项集(frequent itemset)。所谓频繁项集指满足最小支持度或置信度的集合。其次从频繁项集中找出强规则(strong rules)。强规则指不仅满足最小支持度而且满足最小置信度的规则。这其中有一个定理。即频繁项集的子集也一定是频繁项集。比如，如果A,B,C是一个3项的频繁项集，则其子集A,B,B,C,A,C也一定是2项的频繁项集。为方便，可以把含有k项的集合称之为k-itemsets.下面以迭代的方式找出频繁项集。首先找出1-itemsets的频繁项集，然后使用这个1-itemsets，进行组合，找出2-itemsets的频繁项集。如此下去，直到不再满足最小支持度或置信度的条件为止。这其中重要的两步骤分别是连接(join)和剪枝(prune).即从(k-1)-itemsets中的项进行组合，产生备选集(Candidate itemsets)。再从备选集中，将不符合最小支持度或置信度的项删去。例如：图5.1 频繁项集查找算法示意图5.4 数据挖掘实例课程关联规则分析本实验使用HANA数据挖掘平台PAL里的Apriori算法，对学生选课做简单的关联规则分析。本建模分析是对选课信息，特别是选课组合，进行关联规则分析。 注意：先决条件 输入的数据中不包含空值。 每个事务没有任何重复的项目。5.4.1 定义原始待分析数据存储过程为了将所有中间计算结果进行临时存储，对应后面的实际数据表，新建如下Table Type存储过程，如图所示：本实验所用SQL语句如下：SET SCHEMA COURSE;DROP TYPE PAL_APRIORI_DATA_T;CREATE TYPE PAL_APRIORI_DATA_T AS TABLE (XHWID VARCHAR (100), COURSENAME VARCHAR (200);5.4.2 定义算法执行最终结果过程SQL语句如下：DROP TYPE PAL_APRIORI_RESULT_T;CREATE TYPE PAL_APRIORI_RESULT_T AS TABLE( PRERULE VARCHAR(500), POSTRULE VARCHAR(500), SUPPORT DOUBLE, CONFIDENCE DOUBLE, LIFT DOUBLE);DROP TYPE PAL_APRIORI_PMMLMODEL_T;CREATE TYPE PAL_APRIORI_PMMLMODEL_T AS TABLE( ID INT, PMMLMODEL VARCHAR(5000);DROP TYPE PAL_CONTROL_T;CREATE TYPE PAL_CONTROL_T AS TABLE( NAME VARCHAR (50), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR (100);5.4.3 定义元数据将算法所用每个存储过程的属性作用，放到元数据表PAL_APRIORI_PDATA_TBL中：SQL语句如下：DROP TABLE PAL_APRIORI_PDATA_TBL;CREATE COLUMN TABLE PAL_APRIORI_PDATA_TBL( ID INT, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100) );INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (1, COURSE.PAL_APRIORI_DATA_T, in);INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (2, COURSE.PAL_CONTROL_T, in);INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (3, COURSE.PAL_APRIORI_RESULT_T, out);INSERT INTO PAL_APRIORI_PDATA_TBL VALUES (4, COURSE.PAL_APRIORI_PMMLMODEL_T, out);并将读取权限赋予超级用户:GRANT SELECT ON PAL_APRIORI_PDATA_TBL to SYSTEM;5.4.4 生成APRIORI规则调用PAL生成规则库的存储过程（afl_wrapper_generator），来生成PAL_APRIORI_RULE算法规则。CALL SYSTEM.afl_wrapper_eraser(PAL_APRIORI_RULE);CALL SYSTEM.afl_wrapper_generator(PAL_APRIORI_RULE, AFLPAL, APRIORIRULE, PAL_APRIORI_PDATA_TBL);5.4.5 原始数据准备定义原始数据表，如表5.2 所示：表5.2 原始数据表结构(PAL_APRIORI_DATA_T)序号字段数据类型描述实验1事务Integer , varchar事务ID学号2项目itemInteger , varchar项目ID课程名称利用HANA表创建过程，将选课信息表中的学号和课程名称字段数据导入到该原始数据表。本实验所用SQL语句如下：DROP TABLE PAL_APRIORI_TRANS_TBL;CREATE COLUMN TABLE PAL_APRIORI_TRANS_TBL AS (SELECT XHWID, COURSENAME FROM COURSE) WITH DATA;注意：此处必须为列存储数据表。5.4.6 定义临时表创建用户自定义的支持度、置信度参数表，这里使用HANA临时表，每次计算，都需要重新创建并插入参数数据。表5.3 算法控制参数表结构(#PAL_CONTROL_TBL)序号字段数据类型描述1NAMEVARCHAR(50)参数名称2INTARGSINTEGER整型参数3DOUBLEARGSDOUBLE双精度参数4STRINGARGSVARCHAR (100)字符串参数表5.4 算法控制参数说明表参数名称数据类型说明MIN_SUPPORTDouble用户定义的最小支持度 MIN_CONFIDENCEDouble用户定义的最小置信度MIN_LIFTDouble用户定义的最小提升度。默认值 0.0.MAX_CONSEQUENTInteger最大频繁项数目。 默认 100.THREAD_NUMBERInteger并发线程数MAXITEMLENGTHInteger先导项目和依赖项目的输出总长度。默认值是5PMML_EXPORTInteger0（默认）：不导出Apriori算法模型PMML。1：单行导出Apriori算法模型PMML中。2：多行导出Apriori算法模型PMML，每一行的最小长度为5000个字符。为了减少关联规则，提高了最小支持度和置信度。本实验设定的参数如下：表5.5 本实验设定的参数序号参数名整型参数数值双精度参数数值字符型参数数值1THREAD_NUMBER22MIN_SUPPORT0.53MIN_CONFIDENCE0.64MAXITEMLENGTH6SQL语句如下：DROP TABLE #PAL_CONTROL_TBL;CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL( NAME VARCHAR (50), INTARGS INTEGER, DOUBLEARGS DOUBLE,STRINGARGS VARCHAR (100);INSERT INTO #PAL_CONTROL_TBL VALUES (THREAD_NUMBER, 2, null, null);INSERT INTO #PAL_CONTROL_TBL VALUES (MIN_SUPPORT, null, 0.2, null);INSERT INTO #PAL_CONTROL_TBL VALUES (MIN_CONFIDENCE, null, 0.4, null);INSERT INTO #PAL_CONTROL_TBL VALUES (MAXITEMLENGTH, 6, null, null);5.4.7 定义执行结果表算法执行结果放在两个输出表里PAL_APRIORI_RESULT_T和参数表PAL_APRIORI_PMMLMODEL_T表5.6 执行结果数据表结构(PAL_APRIORI_RESULT_TBL)序号字段数据类型描述1PRERULEVARCHAR(500)频繁项2POSTRULEVARCHAR(500)项目ID3SUPPORTDOUBLE支持度4CONFIDENCEDOUBLE置信度5LIFTDOUBLE提升度SQL语句如下：DROP TABLE PAL_APRIORI_RESULT_TBL;CREATE COLUMN TABLE PAL_APRIORI_RESULT_TBL( PRERULE VARCHAR(5000), POSTRULEVARCHAR(5000), SUPPORT Double, CONFIDENCE Double, LIFT DOUBLE );表5.7 执行结果参数数据表结构(PAL_APRIORI_PMMLMODEL_T)序号字段数据类型描述1IDINT频繁项2PMMLMODELVARCHAR(5000)项目IDDROP TABLE PAL_APRIORI_PMMLMODEL_TBL;CREATE COLUMN TABLE PAL_APRIORI_PMMLMODEL_TBL( ID INT, PMMLMODEL VARCHAR(5000);最终得到如下图所示四个表：5.4.8 执行APRIORI算法执行PAL的APRIORI关联规则算法进行数据挖掘：CALL _SYS_AFL.PAL_APRIORI_RULE(PAL_APRIORI_TRANS_