HANA实训指导书

第 1 页 共 41 页 数据仓库数据仓库 HANA 实训指导书实训指导书 1 连接 SAP HANA 数据库 3 2 加载数据 6 2 1 图形界面方式 6 2 2 命令行方式 10 3 数据清洗 11 4 数据建模分析 12 4 1 基础数据创建 12 4 2 范式分解 12 4 3 建模 14 4 3 1 课程信息建模 15 4 3 2 学生信息建模 17 4 3 3 选课信息建模 20 4 3 4 成绩绩点建模 21 5 HANA 数据挖掘 25 5 1 HANA 内嵌数据挖掘平台 PAL 25 5 2 SAP HANA PAL 中的数据挖掘算法及功能清单 26 5 3 关联规则 Apriori 算法简介 27 5 4 数据挖掘实例 课程关联规则分析 31 5 4 1 定义原始待分析数据存储过程 31 5 4 2 定义算法执行最终结果过程 31 5 4 3 定义元数据 32 5 4 4 生成 APRIORI 规则 32 5 4 5 原始数据准备 32 5 4 6 定义临时表 33 5 4 7 定义执行结果表 35 5 4 8 执行 APRIORI 算法 36 5 4 9 建模分析执行结果 36 6 实验总结 41 第 2 页 共 41 页 1 连接 连接 SAP HANA 数据库数据库 安装 HANA 客户端工具和建模工具 SAP HANA Studio 1 打开 SAP HANA Studio 弹出如下界面 2 单击 SAP HANA modeler 建模 左边栏鼠标右键 选择 Add System 弹出如下对话框 第 3 页 共 41 页 3 分别填入如下内容 1 Host Name 2 Instance Number HANA 实例标示 00 代表第 1 个 最大 99 3 表述信息根据自己喜好填写 然后单击 Next 进入下面对话框 第 4 页 共 41 页 4 分别输入 HANA 数据库管理员分配的账户和密码 单击完成 连接 HANA 服务器成功后 进入如下图所示界面 第 5 页 共 41 页 2 加载数据 加载数据 安装 HANA 客户端工具和建模工具后 需要将数据加载到 HANA 实例 中 以开始执行示例应用程序 数据加载可能非常复杂 SAP 为不同的数据加 载业务情景提供了多个解决方案 2 1 图形界面方式图形界面方式 这里将使用由 SAP HANA 建模工具提供的基本文本数据文件加载功能 它可以直接将本地文件加载到 HANA 系统中 操作如下 1 打开 HANA 建模工具 单击 文件 File 菜单并选择 导入 项 Import 第 6 页 共 41 页 2 HANA 建模工具将显示 导入 Import 向导 请选择 从本地文件导 入数据 Data from Local File 然后单击 下一步 Next 按钮 第 7 页 共 41 页 3 选择在之前步骤中创建的 HANA 实例 COURSE 然后单击 下一步 Next 按钮 4 单击源文件选项中的 浏览器 Browser 按钮 选择示例包中提供的 教务学生数据 new csv 文件 选择 标题行存在 Header row exists 选项 将模式设置为 USER01 并将表名设置为 COURSE 第 8 页 共 41 页 然后单击 下一步 Next 按钮 5 在 管理表定义和数据映射 Manage Table Definition and Data Mappings 图表中定义表结构 选择复选框将 CID 设置为关键字段 并设置为非空 将 BIRTHDAY 的 数据类型从 NVARCHAR 更改为 DATE 第 9 页 共 41 页 单击 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 0 第 10 页 共 41 页 3 数据清洗 数据清洗 从 教务学生数据 new csv 可以看到原始数据所包含字段 HANA 是分析 型数据库 所以其默认表模式都是列存储 如下图所示 原始数据表模式结构 经过数据清洗 例如 删除成绩为 0 即选修课后 没有参加考试的记录 删除课程代码为空的记录等 结果如下表所示 表表 3 1 选课数据表选课数据表 序号数据数据量备注 1总数据量2 058 430 2数据清洗后1 839 335 3选课代码为空8 108 4没有成绩 成绩 0 分和为空 199 320 5入校学年为空97 6生日为空937 7籍贯为空 无18 431 第 11 页 共 41 页 4 数据建模分析 数据建模分析 4 1 基础数据基础数据创建创建 我们将导入的数据经过数据清洗后得到原始数据表如下图 4 1 所示 图 4 1 HANA 建模工具显示原始数据表 4 2 范式分解范式分解 根据数据库范式分解理论 这里将原数据表分解为符合第三范式 3NF 第 12 页 共 41 页 的三个表 学生信息表 STUDENT 课程信息表 COURSEDATA 选课情 况信息表 SC 数据库关系图如下图 4 2 所示 图 4 2 HANA 建模工具显示数据库关系图 在 HANA 中 我们使用创建计算视图方式 构建以上三个表 表 4 2 数据库模式信息表 表名 学生信息表 CAC S 课程信息表 CAC C 选课情况信息表 CAC SC 数据量29 9673 7361 840 273 课程信息表 第 13 页 共 41 页 学生信息表 选课情况信息表 4 3 建模建模 打开左边导航栏的 Content 内容目录下 Package 右键单击属性视图 分析视图和计算视图 第 14 页 共 41 页 完成后 单击下图右方的绿色按钮 进行验证 验证通过单击按 钮发布之后 就可以单击 进行数据预览和建模分析 4 3 14 3 1 课程信息建模课程信息建模 对课程信息创建了计算视图 CAC C 其脚本如下图所示 此外 在右边输 出窗口定义 输出列分别为 COURSECODE 和 COURSENAME 下面其它视图 都需要建立输出列 并在语义层定义维度与度量 第 15 页 共 41 页 1 课程名相同 课程代码不同的课程 第 16 页 共 41 页 可以看出排名前 10 位的课程主要是每个学院都开设的实习类课程 通过 Row Count 数值可以很容易得到全校课程数目为 3 736 4 3 24 3 2 学生信息建模学生信息建模 对课程信息我们创建了计算视图 CAC S 其中利用出生日期 建立计算列 从而获得每个学生的星座 其计算公式如下图所示 第 17 页 共 41 页 1 全校按照生源地学生分布 通过 Row Count 数值可以很容易得到 2004 年以来全校学生数目为 29 967 人 排名前 5 位的是上海 江苏 浙江 湖南 山东 2 全校按照入学时间的入校学生分布 在分析视图 选择 入学时间 这个维度 度量选择安装 学号 计算 发 现 2004 2005 年记录缺少 2006 年至今的 8 年每年入校学生入学数基本持平 第 18 页 共 41 页 选择 Table 页面可以看到每年的具体数据如下图 3 全校学生按照星座的分布 在分析视图 选择 星座 这个维度 度量选择安装 学号 计算 排名前 5 位的是天枰座 天蝎座 狮子座 处女座 水瓶座 第 19 页 共 41 页 4 3 34 3 3 选课信息建模选课信息建模 对选课信息我们创建了图形化计算视图 CAC SC 其计算公式如下图所示 第 20 页 共 41 页 4 3 44 3 4 成绩绩点建模成绩绩点建模 对成绩信息我们创建了计算视图 CAC S 其中利用成绩 建立计算列 成 绩绩点 其计算公式如下图所示 其得到的数据如下图所示 第 21 页 共 41 页 1 全校学生生源地的平均成绩分布 在分析视图 选择 生源地 这个维度 度量选择安装 平均成绩 计算 排名前 5 位的是浙江 河北 河南 福建 山东 选择筛选器 filter 可以对数据进行筛选 例如 只查看留学生 台湾 香港 澳门学生的数据 第 22 页 共 41 页 2 全校学生星座的平均成绩分布 在分析视图 选择 星座 这个维度 度量选择安装 平均成绩 计算 排名前 5 位的是天枰座 天蝎座 狮子座 处女座 水瓶座 3 全校学生生源地和绩点的平均成绩分布 第 23 页 共 41 页 在分析视图 选择 生源地 和 绩点 这个维度 度量选择安装 平均成 绩 计算 选择筛选器 filter 可以对数据进行筛选 例如 查看等级为差的学生 的生源地分布数据 排名后 5 位的是北京 河南 广东 陕西 云南 第 24 页 共 41 页 4 全校学生星座和绩点的平均成绩分布 在分析视图 选择 星座 和 绩点 这个维度 度量选择安装 平均成绩 计算 选择筛选器 filter 可以对数据进行筛选 例如 查看优秀学生的星座 分布数据 排名前 5 位的是处女座 水瓶座 天枰座 双鱼座 射手座 第 25 页 共 41 页 5 HANA 数据挖掘数据挖掘 5 1 HANA 内嵌数据挖掘平台内嵌数据挖掘平台 PAL 在传统数据模型中 数据库只是作为存取数据一个工具 对于类似下图所示的 应用 客户端从 Database 获取数据 然后计算 最后再把结果写回 Database 如果 数据量过大 数据传输的开销过大 并且如果客户端的内存不够 计算分析的过 程也将非常缓慢 借助于大内存的优势 SAP HANA 的数据挖掘解决方案是把数据敏感的相 关计算逻辑都移动到 SAP HANA 内 从而省去了数据传输的开销 这个方案就 是 SAP HANA PAL 预测分析库 第 26 页 共 41 页 5 2 SAP HANA PAL 中的数据挖掘算法及功能清单中的数据挖掘算法及功能清单 表 5 1 PAL 算法清单 分类分类PAL 支持算法支持算法内嵌函数名内嵌函数名 Anomaly Detection ANOMALYDETECTION K means KMEANS VALIDATEKMEANS 聚类聚类 Clustering Self Organizing Maps SELFORGMAP Bi Variate Geometric Regression GEOREGRESSION FORECASTWITHGEOR Bi Variate Natural Logarithmic Regression LNREGRESSION FORECASTWITHLNR C4 5 Decision Tree CREATEDT PREDICTWITHDT CHAID Decision Tree CREATEDTWITHCHAID PREDICTWITHDT Exponential Regression EXPREGRESSION FORECASTWITHEXPR KNN KNN Support Vector MachineSVMTRAIN Multiple Linear Regression LRREGRESSION FORECASTWITHLR Polynomial Regression POLYNOMIALREGRESSION FORECASTWITHPOLYNOMIALR 分类分类 Classification Logistic Regression LOGISTICREGRESSION FORECASTWITHLOGISTICR 关联规则关联规则 Association Apriori APRIORIRULE LITEAPRIORIRULE Binning BINNING Inter Quartile Range Test IQRTEST Sampling SAMPLING Scaling Range SCALINGRANGE 预处理预处理 Preprocessing Variance Test VARIANCETEST Single Exponential SmoothingSINGLESMOOTH Double Exponential SmoothinDOUBLESMOOTH 指数平滑算法指数平滑算法 Time Series Triple Exponential SmoothingTRIPLESMOOTH 社会网络分析社会网络分析 Social Network Analysis Link PredictionLINK PREDICTION 第 27 页 共 41 页 Analysis ABC Analysis ABC 其他其他 Miscellaneous 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 形如 如果 那么 If Then 包含两个部分 前者为条件 算法称为 PRERULE 后者为结果 算法称为 POSTRULE 如下图所示 举例 一个顾客 如果买了可乐 那么他也会购买果汁 If 可乐 Then 果汁 第 28 页 共 41 页 图 5 1 规则示例 5 3 4 支持度支持度 Support 与置信度与置信度 Confidence 如何来度量一个规则是否够好 算法通过置信度 Confidence 和支持度 Support 假设有如下顾客购物信息 TIDItems T1orangejuice coke T2milk orange juice window cleaner T3orangejuice detergent T4orangejuice detergent coke T5windowcleaner 设条件的项的集合 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 第 29 页 共 41 页 提升度描述的是相对于不用这些规则 使用规则可以提高多少 有用的规 则的提升度数值为大于 1 计算方式为 Lift A B Confidence A B Support B Support A B Support A Support B 项项支持度支持度 A0 45 B0 42 C0 4 A and B0 25 A and C0 2 B and C0 15 A and B and C0 05 在上例中 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 再从备选集中 将不符合最小支持度或置信度的项删去 例如 规则规则置信度置信度 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 第 30 页 共 41 页 图 5 1 频繁项集查找算法示意图 5 4 数据挖掘实例数据挖掘实例 课程关联规则分析课程关联规则分析 本实验使用 HANA 数据挖掘平台 PAL 里的 Apriori 算法 对学生选课做简 第 31 页 共 41 页 单的关联规则分析 本建模分析是对选课信息 特别是选课组合 进行关联规 则分析 注意 先决条件 输入的数据中不包含空值 每个事务没有任何重复的项目 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 第 32 页 共 41 页 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 所示 第 33 页 共 41 页 表5 2 原始数据表结构 PAL APRIORI DATA T 序号字段数据类型描述实验 1 事务 Integer varchar 事务ID学号 2 项目item Integer 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 字符串参数 第 34 页 共 41 页 表5 4 算法控制参数说明表 参数名称数据类型说明 MIN SUPPORTDouble 用户定义的最小支持度 MIN CONFIDENCEDouble 用户定义的最小置信度 MIN LIFTDouble 用户定义的最小提升度 默认值 0 0 MAX CONSEQUENTInteger 最大频繁项数目 默认 100 THREAD NUMBERInteger 并发线程数 MAXITEMLENGTHInteger 先导项目和依赖项目的输出总长度 默认值是5 PMML EXPORTInteger 0 默认 不导出Apriori算法模型PMML 1 单行导出Apriori算法模型PMML中 2 多行导出Apriori算法模型PMML 每一行的最小 长度为5000个字符 为了减少关联规则 提高了最小支持度和置信度 本实验设定的参数如下 表 5 5 本实验设定的参数 序号序号参数名参数名整型参数数值整型参数数值双精度参数数值双精度参数数值字符型参数数值字符型参数数值 1THREAD NUMBER2 2MIN SUPPORT0 5 3MIN CONFIDENCE0 6 4MAXITEMLENGTH6 SQL 语句如下 第 35 页 共 41 页 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 项目ID 3SUPPORTDOUBLE 支持度 4CONFIDENCEDOUBLE 置信度 5LIFTDOUBLE 提升度 SQL 语句如下 DROP TABLE PAL APRIORI RESULT TBL CREATE COLUMN TABLE PAL APRIORI RESULT TBL PRERULE VARCHAR 5000 POSTRULE VARCHAR 5000 SUPPORT Double CONFIDENCE Double LIFT DOUBLE 表 5 7 执行结果参数数据表结构 PAL APRIORI PMMLMODEL T 序号字段数据类型描述 1IDINT 频繁项 2PMMLMODELVARCHAR 5000 项目ID 第 36 页 共 41 页 DROP 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 TBL PAL C