BI
文章平均质量分 83
wzy0623
25年的数据库、数据仓库、大数据相关工作。《Hadoop构建数据仓库实践》、《HAWQ数据仓库与数据挖掘实战》、《SQL机器学习库——MADlib技术解析》、《MySQL高可用实践》、《Kettle构建Hadoop ETL》、《Greenplum构建实时数据仓库实践》作者。
展开
-
Data Vault初探(四) —— 初始装载
下面的SQL脚本用于完成Data Vault的初始装载过程。USE dv;SET AUTOCOMMIT = 0;-- 清空附属表、链接表、中心表SET FOREIGN_KEY_CHECKS=0;TRUNCATE sat_customer;TRUNCATE sat_order_product;TRUNCATE sat_product;TRUNCATE sat_product_c原创 2015-12-08 18:27:03 · 3235 阅读 · 0 评论 -
Data Vault初探(五) —— 定期装载_SQL
说明:1. 定期装载的周期为每天一次。2. 每天装载自上次装载后的变化数据3. 建立源数据库的过渡表用于CDC4. 建立cdc_time表用于基于时间戳的CDC5. 因为源库上只有订单销售表有时间属性,所以除了sales_order和sales_order_item拉取变化数据外,其它表都整体拉取到过渡区。实际环境中建议在源表设计上应该有created和last_updated原创 2015-12-11 17:06:07 · 2726 阅读 · 0 评论 -
Data Vault初探(六) —— 定期装载_Kettle_作业
总体作业:设置日期变量:清空过渡表:装载过渡表:原创 2015-12-11 17:15:19 · 2353 阅读 · 1 评论 -
Data Vault初探(八) —— 定期装载_Kettle_链接表
装载链接表作业:装载订单客户链接表:装载订单产品链接表:装载产品-产品分类链接表:原创 2015-12-11 17:35:11 · 1599 阅读 · 0 评论 -
Data Vault初探(九) —— 定期装载_Kettle_附属表
装载附属表作业:装载客户附属表:装载订单_产品附属表:装载产品附属表:装载产品分类附属表:原创 2015-12-11 17:51:03 · 2044 阅读 · 0 评论 -
Data Vault初探(七) —— 定期装载_Kettle_中心表
装载中心表作业:装载产品分类中心表:装载客户中心表:装载产品中心表:装载销售订单中心表:原创 2015-12-11 17:29:04 · 1901 阅读 · 0 评论 -
维度模型数据仓库(十二) —— 多路径和参差不齐的层次
(五)进阶技术 7. 多路径和参差不齐的层次 本篇讨论多路径层次,它是对单路径层次的扩展。上一篇里数据仓库的月维度只有一条层次路径,即年-季度-月这条路径。在本篇中加一个新的级别,推广期,并且加一个新的年-推广期-月的层次路径。这时月维度将有两条层次路径,因此具有多路径层次。本篇讨论的另一个主题是不完全层次,这种层次在它的一个或多个级别上没有数据。原创 2015-11-11 14:13:23 · 3851 阅读 · 3 评论 -
维度模型数据仓库(三) —— 准备数据仓库模拟环境
(二)准备数据仓库模拟环境 上一篇说了很多数据仓库和维度模型的理论,从本篇开始落地实操,用一个小而完整的示例说明维度模型及其相关的ETL技术。示例数据库和ETL的SQL实现是在《Dimensional Data Warehousing with MySQL: A Tutorial》基础上做了些修改,增加了Kettle实现的部分。本篇详细说明数据仓库模拟实验环境搭建过程。原创 2015-10-31 08:20:52 · 5901 阅读 · 5 评论 -
维度模型数据仓库(五) —— 定期装载
(四)定期装载 初始装载只在开始数据仓库使用前执行一次,然而,必须要按时调度定期执行装载源数据的过程。本篇说明执行定期装载的步骤,包括识别源数据与装载类型、使用SQL和Kettle两种方法开发和测试定期装载过程。 从源抽取数据导入数据仓库有两种方式,可以从源把数据抓取出来(拉),也可以请求源把数据发送(推)到数据仓库。影响选择数据抽取方式的一个重要因素是源数原创 2015-11-03 15:17:51 · 4430 阅读 · 4 评论 -
维度模型数据仓库(十) —— 快照
(五)进阶技术 5. 快照 前面实验说明了处理维度的扩展。本篇讨论两种事实表的扩展技术。 有些用户,尤其是管理者,经常会要看某个特定时间点的数据。也就是说,他们需要数据的快照。周期快照和累积快照是两种处理事实表扩展的技术。 周期快照是在一个给定的时间对事实表进行一段时期的总计。例如,一个月销售订单周期快照是每个月底时总的销售订原创 2015-11-10 09:19:46 · 7803 阅读 · 0 评论 -
维度模型数据仓库(二十) —— 累积的度量
(五)进阶技术 15. 累积的度量 本篇说明如何实现累积月底金额,并对数据仓库模式和初始装载、定期装载脚本做相应地修改。累积度量是半可加的,而且它的初始装载比前面做的要复杂的多。 可加、半可加、不可加事实 事实表中的数字度量可划分为三类。最灵活、最有用的度量是完全可加的,可加性度量可以按照与事实表关联的任意维度汇总。半可加原创 2015-11-24 16:03:42 · 2820 阅读 · 0 评论 -
维度模型数据仓库(二十一) —— 分段维度
(五)进阶技术 16. 分段维度 本篇说明分段维度的实现技术。分段维度包含连续值的分段。例如,年度销售订单分段维度可能包含有叫做“低”、“中”、“高”的三档;各档定义分别为0.01到15000、15000.01到30000.00、30000.01到99999999.99。如果一个客户的年度销售订单金额为10000,则被归为“低”档。分段维度可以存储多个分段集原创 2015-11-25 17:31:43 · 2823 阅读 · 0 评论 -
维度模型数据仓库(四) —— 初始装载
(三)初始装载 在数据仓库可以使用前,需要装载历史数据。这些历史数据是导入进数据仓库的第一个数据集合。首次装载被称为初始装载,一般是一次性工作。由最终用户来决定有多少历史数据进入数据仓库。例如,数据仓库使用的开始时间是2015年3月1日,而用户希望装载两年的历史数据,那么应该初始装载2013年3月1日到2015年2月28日之间的源数据。在2015年3月2日装载2015年3月1日的原创 2015-11-01 14:44:16 · 6961 阅读 · 16 评论 -
维度模型数据仓库(十六) —— 间接数据源
(五)进阶技术 11. 间接数据源 本篇讨论如何处理间接数据源。间接数据源与维度表具有不同的粒度,因此不能直接装载进数据仓库。在这里通过修改(五)进阶技术7. “多路径和参差不齐的层次”里的推广源数据说明怎样处理间接数据源。CAMPAIGN SESSION,MONTH,YEAR2014 First Campaign,1,20142014 First原创 2015-11-16 13:48:58 · 1953 阅读 · 0 评论 -
维度模型数据仓库(七) —— 按需装载
(五)进阶技术 2. 按需装载 前面已经做了“初始装载”和“定期装载”。还有一种需要熟悉的装载类型,按需装载。所谓“按需装载”指的是,在正常调度之外,当源数据有效时或者数据仓库需要时进行装载。例如,促销销售源数据只有在促销期内有效,而在其它时间是无效的。 在“准备数据仓库模拟环境”中讨论的“生成日期维度数据”可以看做是一种按需装载。数据仓库原创 2015-11-05 16:30:57 · 2578 阅读 · 0 评论 -
维度模型数据仓库(八) —— 维度子集
(五)进阶技术 3. 维度子集 有些需求不需要最细节的数据。例如更想要某个月而不是某天的记录。再比如相对于全部的销售数据,可能对某些特定状态的数据更感兴趣等。这些特定维度包含在从细节维度选择的行中,所以叫维度子集。维度子集比细节维度小,因此更易使用,查询也更快。 本篇中将准备两个特定维度,它们均取自现有的维度:月份维度(日期维度的子集),P原创 2015-11-06 11:56:53 · 3331 阅读 · 0 评论 -
维度模型数据仓库(十七) —— 无事实的事实表
(五)进阶技术 12. 无事实的事实表 本篇讨论一种技术,用来处理源数据中没有度量的需求。例如,产品源数据不包含产品数量信息,如果系统需要得到产品的数量,很显然不能简单地从数据仓库中直接得到。这时就要用到无事实的事实表技术。使用此技术可以通过持续跟踪产品的发布来计算产品的数量。可以创建一个只有产品(计什么数)和日期(什么时候计数)维度代理键的事实表。之所以叫做无原创 2015-11-17 14:26:43 · 7798 阅读 · 1 评论 -
维度模型数据仓库(十八) —— 迟到的事实
(五)进阶技术 13. 迟到的事实 装载日期在生效日期后的事实就是迟到的事实。晚于订单日期进入源数据的销售订单可以看做是一个迟到事实的例子。销售订单被装载进其事实表时,装载的日期晚于销售订单的订单日期,因此是一个迟到的事实。(因为定期装载的是前一天的数据,所以这里的晚于指的是晚2天及其以上。) 迟到事实影响周期快照事实表的装载,如(五)进阶原创 2015-11-19 10:09:58 · 3543 阅读 · 1 评论 -
维度模型数据仓库(一) —— 概述
最近看了三本关于数据仓库的书,很有收获,也很受启发。这三本书分别是《数据仓库工具箱(第三版)》、《Dimensional Data Warehousing with MySQL: A Tutorial》和《Pentaho Kettle解决方案》。在仔细研读了这三本书之后,感觉就像是一本书的三个层次。Ralph Kimball的经典著作数据仓库工具箱阐述的是维度建模方法论和按不同行业建模的示例。Di原创 2015-10-31 07:51:12 · 7950 阅读 · 6 评论 -
维度模型数据仓库(二) —— 维度模型基础
(一)维度模型基础 既然维度模型是数据仓库建设中的一种数据建模方法,那不妨先看一下几种主流的数据仓库架构。 1. Kimball的DW/BI架构图(一)- 1 2. Inmon企业信息工厂架构图(一)- 2 3. 混合型架构图(一)- 3 从图中可以看出,每原创 2015-10-31 08:00:08 · 8139 阅读 · 1 评论 -
维度模型数据仓库(六) —— 增加列
(五)进阶技术 1. 增加列 数据仓库最常碰到的扩展是给一个已经存在的维度表和事实表添加列。本篇先讨论如果需要增加列,模式会发生怎样的变化。然后进一步说明如何在客户维度和销售订单事实表上添加列,并在新列上应用SCD2。假设需要在客户维度中增加送货地址属性,并在销售订单事实表中增加数量度量值。 修改数据库模式 图(五)- 1-1原创 2015-11-04 21:24:30 · 4254 阅读 · 0 评论 -
维度模型数据仓库(十四) —— 杂项维度
(五)进阶技术 9. 杂项维度 本篇讨论杂项维度。简单地说,杂项维度就是一种包含的数据具有很少可能值的维度。例如销售订单,它可能有很多离散数据(yes-no这种类型的值),如verification_ind(如果订单已经被审核,值为yes)credit_check_flag(表示此订单的客户信用状态是否已经检查)new_customer_ind(如果这是原创 2015-11-13 13:45:43 · 4200 阅读 · 0 评论 -
维度模型数据仓库(九) —— 角色扮演维度
(五)进阶技术 4. 角色扮演维度 当一个事实表多次引用一个维度表时会用到角色扮演维度。例如,一个销售订单有一个是订单日期,还有一个交货日期,这时就需要引用日期维度表两次。 本篇将说明两类角色扮演维度的实现,分别是表别名和数据库视图。这两种都使用了MySQL的功能。表别名是在SQL语句里引用维度表多次,每次引用都赋予维度表一个别名。而数据库原创 2015-11-08 15:02:50 · 4429 阅读 · 0 评论 -
维度模型数据仓库(十一) —— 维度层次
(五)进阶技术 6. 维度层次 大多数维度都具有一个或多个层次。例如,日期维度就有一个四级层次:年、季度、月和日。这些级别用date_dim表里的列来表示。日期维度是一个单路径层次,因为除了年-季度-月-日这条路径外,它没有任何其它层次。除此之外,本篇还将讨论在维度的层次上进行分组和钻取查询。多路径层次在下一篇“多路径和参差不齐的层次”中讨论。原创 2015-11-10 17:50:43 · 10228 阅读 · 4 评论 -
维度模型数据仓库(十九) —— 维度合并
(五)进阶技术 14. 维度合并 随着数据仓库中维度的增加,会发现有些通用的数据存在于多个维度中。例如,客户维度的客户邮编相关信息、送货邮编相关信息和工厂维度里都有邮编、城市和州。本篇说明如何把三个维度里的邮编相关信息合并到一个新的邮编维度。 修改数据仓库模式 为了合并维度,需要改变数据仓库模式。图(五)- 14-1显示了修改后的模式。新增原创 2015-11-20 22:01:17 · 3908 阅读 · 0 评论 -
维度模型数据仓库(十三) —— 退化维度
(五)进阶技术 8. 退化维度 本篇讨论一种称为退化维度的技术。该技术减少维度的数量,简化维度数据仓库的模式。简单的模式比复杂的更容易理解,也有更好的查询性能。当一个维度没有数据仓库需要的任何数据时就可以退化此维度。需要把退化维度的相关数据迁移到事实表中,然后删除退化的维度。 退化订单维度 本节说明如何退化订单维度,包括对原创 2015-11-12 12:47:12 · 25631 阅读 · 0 评论 -
维度模型数据仓库(十五) —— 多重星型模式
(五)进阶技术 10. 多重星型模式 从(五)进阶技术1. “增加列”开始,已经通过增加列和表扩展了数据仓库,在(五)进阶技术5. “快照”里增加了第二个事实表,month_end_sales_order_fact表。这之后数据仓库模式就有了两个事实表(第一个是在开始建立数据仓库时创建的sales_order_fact表)。有了这两个事实表的数据仓库就是一个正原创 2015-11-13 18:08:40 · 4179 阅读 · 0 评论 -
Data Vault初探(十) —— 星型模型向Data Vault模型转化
源数据库模型(3NF)如下图:星型模型如下图:Data Vault模型如下图:说明:星型模型(star schema)的事实表采取了完全规范化的第三范式(3NF)模型,而维表采取了第二范式的设计模型。有时也会把维表的设计规范化,就成了所谓的雪花模型(snowflake schema)。星型模型向Data Vault模型转化:星型模趔的主要构成部分是维表与事实原创 2015-12-15 15:07:49 · 7862 阅读 · 1 评论 -
Data Vault初探(一) —— 基础
摘自:《Pentaho Kettle 解决方案: 使用PDI构建开源ETL解决方案》 Data Vault(DV)模型是用于企业级的数据仓库建模。由Dan Linstedt在20世纪90年代提出(http://www.danlinstedt.com)。在最近几年,Data Vault模型获得了很多关注,并在BI社区里拥有了一批追随者。 Dan Linsted转载 2015-12-08 17:56:29 · 12046 阅读 · 0 评论 -
Data Vault初探(二) —— 构建及参考原则
摘自:《下一代数据仓库模型Data Vault的研究及其应用》 Data Vault模型的构建 在Data Vault模型中,各个实体组件有着严格、通用的定义与准确、灵活的功能描述,这不但使得Data Vault模型能够最直观、最一般地反映数掘之间内含的业务规则,同时也为构建Data Vault模型提供了一致而普遍的方法。简单地讲,Data Vault模转载 2015-12-08 18:00:10 · 9542 阅读 · 0 评论 -
Data Vault初探(三) —— 建立Data Vault模型
本示例源数据库是一个订单销售的普通场景,共有省、市、客户、产品类型、产品、订单、订单明细7个表。ERD如下图所示。使用下面的脚本建立源数据库表:CREATE TABLE province ( province_id varchar(2) NOT NULL COMMENT '省份编码', province_name varchar(20) DEFAULT NULL COMMENT原创 2015-12-08 18:05:54 · 8749 阅读 · 2 评论 -
kettle 5.1.0 连接 Hadoop hive 2 (hive 1.2.1)
1. 配置HiveServer2,在hive-site.xml中添加如下的属性 hive.server2.thrift.bind.host 192.168.56.101 Bind host on which to run the HiveServer2 Thrift service. hive.server2.thrift.port 1原创 2016-03-16 10:53:55 · 13990 阅读 · 2 评论 -
参加BDTC 2016有感
经过三个多月的紧张忙碌,书稿基本完成,现在又有时间笔耕这块CSDN上的自留地了。昨天有幸参加了BDTC 2016中国大数据技术大会,虽然三天的大会只参加了最后一天,但是受益良多。感谢CSDN的增票,3500大元的门票价格不菲,要是自费参加是绝下不了这个决心的。(没有全程参与是个遗憾,在此稍微吐槽一下我司的考勤与年假制度,对于我这个老革命来说真的极为不爽。) 从早上9点到傍晚6点,除去午休的一原创 2016-12-11 20:37:50 · 1585 阅读 · 1 评论 -
使用Kettle连接动态分库
一、问题提出 在一个数据仓库应用中,每天新建一个MySQL数据库,以当天日期命名,如d_p20161201、d_p20161202等,并使用Kettle连接这些数据库做数据清洗和ETL工作。由于数据库是用脚本每天动态生成的,Kettle如何连接动态分库呢?二、解决方案1. 建立数据库连接,在数据库名称中引入变量。此时是无法连接到数据库的。2. 建立转换,用JavaScript步骤设置原创 2016-12-26 09:36:29 · 4190 阅读 · 0 评论 -
Kettle之“检查表是否存在”
想用Kettle实现一个非常简单的需求,从MySQL向Oracle导入一个表的数据,如果在oracle里表不存在,先建表再导入数据。这个功能看似非常简单,但对于刚开始接触Kettle的用户来说,可能会有些疑惑。Kettle的转化和作业中都有“检查表是否存在”步骤,但是如果要实现上述的需求,只能用作业,而不能使用转换。 在Kettle中,作业是串行执行的,只有前一个作业项执行成功才会开始下一个作业原创 2016-12-26 10:39:30 · 9645 阅读 · 1 评论 -
Kettle之“获取文件名”
收到的需求是这样的:有几百个文本文件,每个文件内容的格式相同,都是有固定分隔符的两列,每个文件有几千行记录。现在需要把这些文件的内容导入一个表,除了文件中的两列,还要存一列记录对应的文件名。 作为一个搞数据库的,导入数据本来是小事一桩,可这有几百个文件要手工逐个处理未免太麻烦了,于是想起了Kettle。Kettle的转换处理数据流,其中有一个“获取文件名”的输入对象,可以使用它在导入文件数据时添原创 2016-12-26 11:07:01 · 21090 阅读 · 0 评论 -
在Kettle里使用参照表进行数据校验(流查询实现)
参照表一个常见的用途就是做数据的查询和检验。提供一个输入字段,如果输入字段里的值没有匹配上,就给对应的数据行做一个错误标志。下面使用城市和邮政编码查询做个例子,演示如何使用计算器步骤和查询步骤来判断地址和邮政编码是否匹配。完整的转换如下图: 首先,需要一些输入数据,本例使用了“自定义常量数据”步骤,并添加一些测试数据作为输入,如下图: 第一个清洗步骤就是从邮政编码里提取数字,要使用计算器步骤。原创 2016-12-26 16:10:19 · 16514 阅读 · 1 评论 -
在Kettle里使用参照表进行数据校验(子转换实现)
有一种参照表叫数据确认主表。性别编码就是这种参照表的例子。有的系统使用字母M、F和U,分别代表男、女、未知;有的系统使用NULL来代表未知的性别;有的系统使用Male和Female代表男、女;而有的系统则使用完全不同的编码,如0(男)、1(女)或0(未知)、1(男)、2(女),等等。还有更复杂的情况,有的系统使用C代表儿童,使用F代表父亲,M代表母亲,各种变化和组合都有可能。要把从这些来源的数据整原创 2016-12-26 16:33:31 · 6575 阅读 · 0 评论 -
在Kettle里使用时间戳实现变化数据捕获(CDC)
1. 建立测试表,插入数据。use test; create table t_color ( id int unsigned not null auto_increment primary key, color varchar(10), create_date datetime, last_update timestamp ) eng原创 2016-12-26 14:03:21 · 17367 阅读 · 3 评论 -
ETL主流程(书摘备查)
原创 2016-12-27 09:54:21 · 1077 阅读 · 0 评论