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. 验证sqoop
cd /export/server/sqoop-1.4.7

bin/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_product
select
  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-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_product
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
   
union all

select 
  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 ;