数仓1.4 |业务数仓搭建| 拉链表| Presto电商业务及数据结构SKU库存量单位,剩余多少;现在已经被引申为产品统一编号的简称,每种产品均对应有唯一的SKU号;每个商品都是一个SKU,每个SKU都有自己独立的库存数。也就是说每一个商品详情展示都是一个SKU(比如iPhone手机的SKU--银色、128G内存、支持联通网以这个为单位记录库存数)。 订单表:周期性状态变化(order_info) View Code 订单详情表:(order_detail) order_detail.order_id 是要一一对应 order_info.id;导入数仓时要关联下,不一致的舍去 View Code 商品表 sku_info View Code 用户表user_info View Code 商品一级分类表base_category1 View Code 商品二级分类表base_category2 View Code 商品三级分类表base_category3 View Code 支付流水表 payment_info View Code 数仓理论(重点)①实体表:用户表、商品表--->全量;一个个实实在在的个体; 一般是指一个现实存在的业务对象,比如用户,商品,商家,销售员等等。 (同步策略)实体表数据量比较小:通常可以做每日全量,就是每天存一份完整数据。即每日全量。 ②维度表(码表--编号的解释表):对应的业务状态;商品一级分类表、商品二级分类表、商品三级分类表.等都是;全量表 比如地区表,订单状态,支付方式,审批状态,商品分类等等 同步策略(维度表数据量比较小:通常可以做每日全量,就是每天存一份完整数据。即每日全量。 说明:1)针对可能会有变化的状态数据可以存储每日全量。2)没变化的客观世界的维度(比如性别,地区,民族,政治成分,鞋子尺码)可以就存一份固定值。) ③事务型事实表;一般指随着业务发生不断产生的数据。特点是一旦发生不会再变化;比如,交易流水,操作日志,出库入库记录等。 同步策略(因为数据不会变化,而且数据量巨大,所以每天只同步新增数据即可,所以可以做成每日增量表,即每日创建一个分区存储。) ④周期型事实表:随着业务的发生(时间)而变化 这类表从数据量的角度,存每日全量的话,数据量太大,冗余也太大。如果用每日增量的话无法反应数据变化。 每日新增及变化量可以用,包括了当日的新增和修改。一般来说这个表,足够计算大部分当日数据的。但是这种依然无法解决能够得到某一个历史时间点(时间切片)的切片数据。 所以要用利用每日新增和变化表,制作一张拉链表,以方便的取到某个时间切片的快照数据。所以我们需要得到每日新增及变化量。 拉链 同步策略数据同步策略的类型包括:全量表、增量表、新增及变化表、拉链表
范式理论关系型数据库设计时,遵照一定的规范要求,目的在于降低数据的冗余性,目前业界范式有:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。 范式的标准定义是:符合某一种级别的关系模式的集合,表示一个关系内部各属性之间的联系的合理化程度。通俗地讲,范式可以理解为一张数据表的表结构,符合的设计标准的级别。 使用范式的根本目的是: 1)减少数据冗余,尽量让每个数据只出现一次。 2)保证数据一致性 缺点是获取数据时,需要通过Join拼接出最后的数据。 1NF核心原则:属性不可切割 2NF核心原则: 不能存在部分函数依赖 3NF不能存在传递函数依赖 函数依赖完全函数依赖 部分函数依赖: 传递函数依赖 关系模型主要应用与OLTP系统(关系型数据库。尽量-遵循第三范式)中,为了保证数据的一致性以及避免冗余,所以大部分业务系统的表都是遵循第三范式的。 维度模型(主要有事实表和维度表,大规模复杂查询的响应性能,更直接面向业务,星形模型,特殊场景适用的雪花模型。) 主要应用于OLAP系统(联机分析处理OLAP(On-Line Analytical Processing)是数据仓库系统的主要应用)中; (可减小表的关联),因为关系模型虽然冗余少,但是在大规模数据,跨表分析统计查询过程中,会造成多表关联,这会大大降低执行效率。 维度模型数据来源于--->OLTP各种类型的分析计算; 雪花模型、星型模型和星座模型维度建模的基础上又分为三种模型:星型模型、雪花模型、星座模型。 星型模型:(快) 雪花模型:(灵活) 星座模型:(可能是雪花也可能是星型) 数据仓库由多个主题构成,包含多个事实表,而维表是公共的,可以共享,这种模式可以看做星型模式的汇集,因而称作星系模式或者事实星座模式。 生成业务数据并导入数仓生成业务数据函数说明 init_data ( do_date_string VARCHAR(20) , order_incr_num INT, user_incr_num INT , sku_num INT , if_truncate BOOLEAN ): 参数一:do_date_string生成数据日期 参数二:order_incr_num订单id个数 参数三:user_incr_num用户id个数 参数四:sku_num商品sku个数 参数五:if_truncate是否删除数据 需求:生成日期2019年2月10日数据、订单1000个、用户200个、商品sku300个、不删除数据。 CALL init_data('2019-02-10',1000,200,300,FALSE); 生成2019年2月11日数据 CALL init_data('2019-02-11',1000,200,300,FALSE); Sqoop安装 https://www.cnblogs.com/shengyang17/p/10512510.html Sqoop导入命令参数 ms /opt/module/sqoop/bin/sqoop import \ --connect \ --username \ --password \ --target-dir \ --delete-target-dir \ --num-mappers \ --fields-terminated-by \ --query "2"′andCONDITIONS;' Sqoop定时导入脚本利用Sqoop将mysql中的数据导入HDFS中 1)在/home/kris/bin目录下创建脚本sqoop_import.sh [kris@hadoop101 bin]$ vim sqoop_import.sh case 1是脚本的第一个参数,all是把所有的参数都导入进来;dbdate=2 是输入的第二个参数; import_data这个函数##1指这个函数的第一个输入参数,指表名;query"2" ##这个函数的第二个参数即sql查询语句; where 1=1是为了防止sql注入 View Code 增加脚本执行权限 kirs@hadoop101 bin]$ chmod 777 sqoop_import.sh执行脚本导入数据 kirs@hadoop101 bin]$ sqoop_import.sh all 2019-02-10kirs@hadoop101 bin]$ sqoop_import.sh all 2019-02-11 ODS层完全仿照业务数据库中的表字段,一模一样的创建ODS层对应表。 1 )创建订单表 View Code 2 )创建订单详情表 View Code 3 )创建商品表 View Code 4 )创建用户表 View Code 5 )创建商品一级分类表 View Code 6 )创建商品二级分类表 View Code 7 )创建商品三级分类表 View Code 8 )创建支付流水表 View Code ODS层数据导入脚本 View Code 查询导入数据 hive (gmall)> select * from ods_order_info where dt='2019-02-10' limit 1; select * from ods_order_info where dt='2019-02-11' limit 1; DWD层对ODS层数据进行判空过滤。对商品分类表进行维度退化(降维)。 1) 创建订单表 View Code 2) 创建订单详情表 View Code 3 )创建用户表 View Code 4) 创建支付流水表 View Code 5 )创建商品表(增加分类)
1)维度退化要付出什么代价?或者说会造成什么样的需求处理不了? 如果被退化的维度,还有其他业务表使用,退化后处理起来就麻烦些。 2)想想在实际业务中还有那些维度表可以退化 城市的三级分类(省、市、县)等 View Code DWD层数据导入脚本 View Code 执行脚本导入数据 [kris@hadoop101 bin]$ dwd_db.sh 2019-02-10 [kris@hadoop101 bin]$ dwd_db.sh 2019-02-11 查看导入数据 hive (gmall)> select * from dwd_sku_info where dt='2019-02-10' limit 2; select * from dwd_sku_info where dt='2019-02-11' limit 2; DWS层之用户行为宽表1)为什么要建宽表 需求目标,把每个用户单日的行为聚合起来组成一张多列宽表,以便之后关联用户维度信息后进行,不同角度的统计分析。 创建用户行为宽表 user_id 、order_count、order_amount、payment_count、payment_amount、comment_count View Code 向用户行为宽表导入数据 dwd_order_info、dwd_payment_info、dwd_comment_log、dws_user_action View Code DWS层用户行为数据宽边导入脚本 1)在/home/atguigu/bin目录下创建脚本dws_db_wide.sh [atguigu@hadoop102 bin]$ vim dws_db_wide.sh View Code 订单表拉链表导的是新增和变化(修改) 查看某些业务信息的某一个时间点当日信息; 数据量有一定规模,无法按照每日全量的的方式保存,比如1亿用户*365,每天一份用户信息。(无法做每日全量) 制作流程图 拉链表制作过程 步骤0:初始化拉链表(首次独立执行) 1)生成10条原始订单数据 [kris@hadoop101 bin]$ sqoop_import.sh all 2019-02-13[kris@hadoop101 bin]$ ods_db.sh 2019-02-13[kris@hadoop101 bin]$ dwd_db.sh 2019-02-132)初始化拉链表(首次独立执行) hive (gmall)>drop table if exists dwd_order_info_his; create table dwd_order_info_his( `id` string COMMENT '订单编号', `total_amount` decimal(10,2) COMMENT '订单金额', `order_status` string COMMENT '订单状态', `user_id` string COMMENT '用户id' , `payment_way` string COMMENT '支付方式', `out_trade_no` string COMMENT '支付流水号', `create_time` string COMMENT '创建时间', `operate_time` string COMMENT '操作时间' , `start_date` string COMMENT '有效开始日期', `end_date` string COMMENT '有效结束日期') COMMENT '订单拉链表'stored as parquet location '/warehouse/gmall/dwd/dwd_order_info_his/'tblproperties ("parquet.compression"="snappy"); 就在原来基础上添加两个字段:start_date, end_date3)初始化拉链表 hive (gmall)>insert overwrite table dwd_order_info_his select id, total_amount, order_status, user_id, payment_way, out_trade_no, create_time, operate_time, '2019-02-13', '9999-99-99'from ods_order_info oi where oi.dt='2019-02-13'; 查询拉链表中数据 hive (gmall)> select * from dwd_order_info_his limit 2; 2019-02-13hive (gmall)> select * from dwd_order_info_his limit 2;id total_amount order_status user_id payment_way out_trade_no create_time operate_time start_date end_date1 64 1 1 1 7824722278 2019-02-13 07:26:54.0 null 2019-02-13 9999-99-992 64 1 4 2 0028658702 2019-02-13 05:39:26.0 null 2019-02-13 9999-99-99 步骤1:制作当日变动数据(包括新增,修改)每日执行 步骤2:先合并变动信息,再追加新增信息,插入到临时表中 1)建立临时表 hive (gmall)>drop table if exists dwd_order_info_his_tmp; create external table dwd_order_info_his_tmp( `id` string COMMENT '订单编号', `total_amount` decimal(10,2) COMMENT '订单金额', `order_status` string COMMENT '订单状态', `user_id` string COMMENT '用户id' , `payment_way` string COMMENT '支付方式', `out_trade_no` string COMMENT '支付流水号', `create_time` string COMMENT '创建时间', `operate_time` string COMMENT '操作时间', `start_date` string COMMENT '有效开始日期', `end_date` string COMMENT '有效结束日期') COMMENT '订单拉链临时表'stored as parquet location '/warehouse/gmall/dwd/dwd_order_info_his_tmp/'tblproperties ("parquet.compression"="snappy");2)导入脚本 insert overwrite table dwd_order_info_his_tmpselect * from(select id, total_amount, order_status, user_id, payment_way, out_trade_no, create_time, operate_time, '2019-02-14' start_date, '2019-99-99' end_date from dwd_order_info where dt='2019-02-14'union allselect oh.id, oh.total_amount, oh.order_status, oh.user_id, oh.payment_way, oh.out_trade_no, oh.create_time, oh.operate_time, oh.start_date, if(oi.id is null, oh.end_date, date_add(oi.dt, -1)) end_date ##没匹配上即不为空就还是原来的 from dwd_order_info_his oh left join(select * from dwd_order_info where dt="2019-02-14")oi on oh.id = oi.id and oh.end_date="9999-99-99" ##要改的就是它 )his order by his.id, start_date; 把所有的表拿来,加上两个字段日期; 步骤3:把临时表覆盖给拉链表 overwrite回原来的状态; 一般1个月拉链1次; hive (gmall)> insert overwrite table dwd_order_info_his > select * from dwd_order_info_his_tmp; hive (gmall)> select * from dwd_order_info_his ;id total_amount order_status user_id payment_way out_trade_no create_time operate_time start_date end_date1 64 1 1 1 7824722278 2019-02-13 07:26:54.0 null 2019-02-13 2019-02-131 740 2 2 2 6172225040 2019-02-14 13:39:49.0 2019-02-14 14:17:13.0 2019-02-14 2019-99-99 ##修改之后变成02-14, 99-99记录的是变化10 823 1 1 1 6002223362 2019-02-13 17:12:32.0 null 2019-02-13 9999-99-992 64 1 4 2 0028658702 2019-02-13 05:39:26.0 null 2019-02-13 2019-02-13 ##把原来的2019-02-13 2019-99-99修改为了2019-02-13 2019-02-13(用02-14减1)2 369 2 2 1 3168474956 2019-02-14 06:15:57.0 2019-02-14 06:25:54.0 2019-02-14 2019-99-993 66 1 4 2 5999709451 2019-02-13 03:50:02.0 null 2019-02-13 9999-99-994 259 2 3 1 0789187336 2019-02-13 07:44:43.0 2019-02-13 08:30:01.0 2019-02-13 9999-99-995 684 2 1 2 8902377409 2019-02-13 10:46:09.0 2019-02-13 11:28:28.0 2019-02-13 9999-99-996 672 1 2 2 2398265682 2019-02-13 07:39:14.0 null 2019-02-13 9999-99-997 890 2 4 1 1480697931 2019-02-13 07:58:03.0 2019-02-13 08:10:22.0 2019-02-13 9999-99-998 130 1 2 2 8729918906 2019-02-13 09:45:16.0 null 2019-02-13 9999-99-999 171 2 2 2 3246623167 2019-02-13 15:33:50.0 2019-02-13 15:41:13.0 2019-02-13 9999-99-99Time taken: 0.042 seconds, Fetched: 12 row(s) OLAP分析工具之Presto下载安装 1)下载地址 https://repo1./maven2/com/facebook/presto/presto-server/0.196/presto-server-0.196.tar.gz Presto Server安装 2)将presto-server-0.196.tar.gz导入hadoop101的/opt/software目录下,并解压到/opt/module目录 [kris@hadoop101 software]$ tar -zxvf presto-server-0.196.tar.gz -C /opt/module/3)修改名称为presto [kris@hadoop101 module]$ mv presto-server-0.196/ presto4)进入到/opt/module/presto目录,并创建存储数据文件夹 [kirs@hadoop101 presto]$ mkdir data5)进入到/opt/module/presto目录,并创建存储配置文件文件夹 [kirs@hadoop101 presto]$ mkdir etc6)配置在/opt/module/presto/etc目录下添加jvm.config配置文件 [kirs@hadoop101 etc]$ vim jvm.config View Code 7)Presto可以支持多个数据源,在Presto里面叫catalog,这里我们配置支持Hive的数据源,配置一个Hive的catalog [kirs@hadoop101 etc]$ mkdir catalog 8)将hadoop101上的presto分发到hadoop102、hadoop103 [kirs@hadoop101 module]$ xsync presto9)分发之后,分别进入hadoop101、hadoop102、hadoop103三台主机的/opt/module/presto/etc的路径。配置node属性,node id每个节点都不一样。 [kirs@hadoop101 etc]$vim node.properties node.environment=production node.id=ffffffff-ffff-ffff-ffff-ffffffffffff node.data-dir=/opt/module/presto/data [kirs@hadoop102 etc]$vim node.properties node.environment=production node.id=ffffffff-ffff-ffff-ffff-fffffffffffe node.data-dir=/opt/module/presto/data [kirs@hadoop103 etc]$vim node.properties node.environment=production node.id=ffffffff-ffff-ffff-ffff-fffffffffffd node.data-dir=/opt/module/presto/data 10)Presto是由一个coordinator节点和多个worker节点组成。在hadoop101上配置成coordinator,在hadoop102、hadoop103上配置为worker。 [kirs@hadoop101 etc]$ vim config.properties coordinator=truenode-scheduler.include-coordinator=falsehttp-server.http.port=8881query.max-memory=50GB discovery-server.enabled=truediscovery.uri=http://hadoop101:8881 (2)hadoop102、hadoop103上配置worker节点 [kirs@hadoop102 etc]$ vim config.properties coordinator=falsehttp-server.http.port=8881query.max-memory=50GB discovery.uri=http://hadoop101:8881[kirs@hadoop103 etc]$ vim config.properties coordinator=falsehttp-server.http.port=8881query.max-memory=50GB discovery.uri=http://hadoop101:8881 启动 11)在/opt/module/hive目录下,启动Hive Metastore,用atguigu角色 nohup bin/hive --service metastore >/dev/null 2>&1 &12)分别在hadoop101、hadoop102、hadoop103上启动presto server (1)前台启动presto,控制台显示日志 [kirs@hadoop101 presto]$ bin/launcher run [kirs@hadoop102 presto]$ bin/launcher run [kirs@hadoop103 presto]$ bin/launcher run (2)后台启动presto [kirs@hadoop101 presto]$ bin/launcher start [kirs@hadoop102 presto]$ bin/launcher start [kirs@hadoop103 presto]$ bin/launcher start 日志查看路径/opt/module/presto/data/var/log Presto命令行Client安装--一般没人用 1)下载Presto的客户端 Presto可视化Client安装 1)将yanagishima-18.0.zip上传到hadoop101的/opt/module目录2)解压缩yanagishima [kirs@hadoop101 module]$ unzip yanagishima-18.0.zipcd yanagishima-18.03)进入到/opt/module/yanagishima-18.0/conf文件夹,编写yanagishima.properties配置 [kirs@hadoop101 conf]$ vim yanagishima.properties jetty.port=7080presto.datasources=kris-presto presto.coordinator.server.kris-presto=http://hadoop101:8881catalog.kris-presto=hive schema.kris-presto=default sql.query.engines=presto 4)在/opt/module/yanagishima-18.0路径下启动yanagishima [kirs@hadoop101 yanagishima-18.0]$ nohup bin/yanagishima-start.sh >y.log 2>&1 &5)启动web页面 http://hadoop101:7080 看到界面,进行查询了。 分类: BDwarehouse |
|
来自: jasonbetter > 《数据仓库》