day20hive以及拉链表

一.数据采集 Sqoop介绍：Hadoop生态体系和RDBMS（mysql，oracle，db2）体系之间传送数据的一种工具。import数据导入，export数据导出sqoop安装：

2.1 tar -xvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /export/server/

2.2 mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.7

2.3 修改配置文件：

mv sqoop-env-template.sh sqoop-env.sh
vim sqoop-env.sh

2.4加入mysql的jdbc驱动包和hive的执行包
cp mysql-connector-java-5.1.41-bin.jar /export/server/sqoop-1.4.7/lib/
cp hive-exec-2.1.0.jar /export/server/sqoop-1.4.7/lib/
2.5测试 /export/server/sqoop-1.4.7/bin/sqoop list-databases --connect jdbc:mysql://node3:3306/ --username root --password 123456

二.拉链表 缓慢变化维：数据仓库中的数据模型设计过程中会遇到：表中部分字段会uodate，需要查看某一个时间点或历史快照，变化的比例和频率不是很大，可以使用拉链表解决缓慢变化维问题拉链表：start_date 起始有效时间 end_date，结束时间（当结束时为最新）创建表
3.1在mysql创建表并添加数据：

一、Sqoop框架 --1、验证sqoopcd /export/server/sqoop-1.4.7bin/sqoop list-databases --connect jdbc:mysql://node1:3306/ --username root --password 123456二、拉链表-Demo-- 1、MySQL创建商品表-- 创建数据库 CREATE DATAbase demo; -- 创建商品表 create table if not exists demo.product( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态 createtime varchar(50), -- 商品创建时间 modifytime varchar(50) -- 商品修改时间 ) ; --2、Hive-ODS层建表-- 创建表create database if not exists demo;-- 创建ods层表create table if not exists demo.ods_product( goods_id string, -- 商品编号 goods_status string, -- 商品状态 createtime string, -- 商品创建时间 modifytime string -- 商品修改时间)partitioned by (dt string) --按照天分区row format delimited fields terminated by 't';--3、Hive dw层创建拉链表-- 创建拉链表create table if not exists demo.dw_product( goods_id string, -- 商品编号 goods_status string, -- 商品状态 createtime string, -- 商品创建时间 modifytime string, -- 商品修改时间 dw_start_date string, -- 生效日期 dw_end_date string -- 失效日期)row format delimited fields terminated by 't';--4、MySQL数据库导入12月20日数据（4条数据）insert into demo.product(goods_id, goods_status, createtime, modifytime) values('001', '待审核', '2020-12-18', '2020-12-20'),('002', '待售', '2020-12-19', '2020-12-20'),('003', '在售', '2020-12-20', '2020-12-20'),('004', '已删除', '2020-12-15', '2020-12-20');--5、使用sqoop进行全量同步MySQL数据到Hive ods层表，创建Hive分区,并导入mysql的数据-- 创建分区alter table demo.ods_product add partition (dt='2020-12-20');--将mysql数据导入Hive/export/server/sqoop-1.4.7/bin/sqoop import --connect jdbc:mysql://node1:3306/demo --username root --password 123456 --table product --m 1 --delete-target-dir --fields-terminated-by 't' --target-dir /user/hive/warehouse/demo.db/ods_product/dt=2020-12-20--6 编写SQL从ods导入dw当天最新的数据-- 从ods层导入dw当天最新数据insert overwrite table demo.dw_productselect goods_id, -- 商品编号 goods_status, -- 商品状态 createtime, -- 商品创建时间 modifytime, -- 商品修改时间 modifytime as dw_start_date, -- 生效日期 '9999-12-31' as dw_end_date -- 失效日期from demo.ods_productwhere dt = '2020-12-20';--7、增量导入2019年12月21日数据- MySQL数据库导入12月21日数据（6条数据）--模拟更新数据UPDATe demo.product SET goods_status = '待售', modifytime = '2020-12-21' WHERe goods_id = '001';INSERT INTO demo.product(goods_id, goods_status, createtime, modifytime) VALUES('005', '待审核', '2020-12-21', '2020-12-21'),('006', '待审核', '2020-12-21', '2020-12-21');--8、使用sqoop开发增量同步MySQL数据到Hive ods层表--Hive创建分区alter table demo.ods_product add partition (dt='2020-12-21');--增量导入/export/server/sqoop-1.4.7/bin/sqoop import --connect jdbc:mysql://node1:3306/demo --username root --password 123456 --target-dir /user/hive/warehouse/demo.db/ods_product/dt=2020-12-21 --query "select * from product where modifytime = '2020-12-21' and $CONDITIONS" --delete-target-dir --fields-terminated-by 't' --m 1 --8、编写SQL处理dw层历史数据，重新计算之前的dw_end_date app 层 dw 层 （2020-12-20） dw_product ods 层 （2020-12-20 、 2020-12-21）-- 重新计算dw层拉链表中的失效时间select t1.goods_id, -- 商品编号 t1.goods_status, -- 商品状态 t1.createtime, -- 商品创建时间 t1.modifytime, -- 商品修改时间 t1.dw_start_date, -- 生效日期（生效日期无需重新计算） case when (t2.goods_id is not null and t1.dw_end_date = '9999-12-31') then '2020-12-21' else t1.dw_end_date end as dw_end_date -- 更新生效日期（需要重新计算）from demo.dw_product t1 left join (select * from demo.ods_product where dt='2020-12-21') t2 on t1.goods_id = t2.goods_id--9、合并当天最新的数据和历史数据insert overwrite table demo.dw_productselect t1.goods_id, -- 商品编号 t1.goods_status, -- 商品状态 t1.createtime, -- 商品创建时间 t1.modifytime, -- 商品修改时间 t1.dw_start_date, -- 生效日期（生效日期无需重新计算） case when (t2.goods_id is not null and t1.dw_end_date = '9999-12-31') then '2020-12-21' else t1.dw_end_date end as dw_end_date -- 更新生效日期（需要重新计算）from demo.dw_product t1 left join (select * from demo.ods_product where dt='2020-12-21') t2 on t1.goods_id = t2.goods_id union allselect goods_id, -- 商品编号 goods_status, -- 商品状态 createtime, -- 商品创建时间 modifytime, -- 商品修改时间 modifytime as dw_start_date, -- 生效日期 '9999-12-31' as dw_end_date -- 失效日期from demo.ods_product where dt='2020-12-21'order by dw_start_date, goods_id;--10、拉链表查询-- 查询2020-12-20数据 select * from dw_product where dw_start_date = '2020-12-20' order by goods_id;-- 查询当前订单的最新状态 select * from dw_product where dw_end_date = '9999-12-31' order by goods_id ;