hive查询数据创建另一张表_数据的另一种展示形式，Hive 拉链表实践

最新推荐文章于 2023-05-03 13:04:17 发布
最新推荐文章于 2023-05-03 13:04:17 发布
阅读量254 收藏
点赞数
文章标签： hive查询数据创建另一张表
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_32841641/article/details/113707439
版权

背景

拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的； 顾名思义，所谓拉链表，就是记录历史。记录一个事务从开始一直到当前状态的所有变化的信息。

拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据(SCD2)的一种常见方式。

应用场景

现假设有如下场景：一个企业拥有5000万会员信息，每天有20万会员资料变更，需要在数仓中记录会员表的历史变化以备分析使用，即每天都要保留一个快照供查询，反映历史数据的情况。

在此场景中，需要反映5000万会员的历史变化，如果保留快照，存储两年就需要2X365X5000W条数据存储空间，数据量为365亿，如果存储更长时间，则无法估计需要的存储空间。而利用拉链算法存储，每日只向历史表中添加新增和变化的数据，每日不过20万条，存储4年也只需要3亿存储空间。

实现步骤

在拉链表中，每一条数据都有一个生效日期(effective_date)和失效日期(expire_date)。假设在一个用户表中，在2019年11月8日新增了两个用户，如下表所示，则这两条记录的生效时间为当天，由于到2019年11月8日为止,这两条就还没有被修改过，所以失效时间为一个给定的比较大的值，比如：3000-12-31  

member_idphonenocreate_timeupdate_time
10001133000000012019-11-083000-12-31
10002135000000022019-11-083000-12-31

第二天(2019-11-09)，用户10001被删除了，用户10002的电话号码被修改成13600000002.为了保留历史状态，用户10001的失效时间被修改为2019-11-09，用户10002则变成了两条记录，如下表所示： 

member_idphonenocreate_timeupdate_time
10001133000000012019-11-082019-11-09
10002135000000022019-11-082019-11-09
10002136000000022019-11-093000-12-31

第三天(2019-11-10),又新增了用户10003，则用户表数据如小表所示： 

member_idphonenocreate_timeupdate_time
10001133000000012019-11-082019-11-09
10002135000000022019-11-082019-11-09
10002136000000022019-11-093000-12-31
10003133000000062019-11-103000-12-31

如果要查询最新的数据，那么只要查询失效时间为3000-12-31的数据即可，如果要查11月8号的历史数据，则筛选生效时间<= 2019-11-08并且失效时间>2019-11-08的数据即可。如果查询11月9号的数据，那么筛选条件则是生效时间<=2019-11-09并且失效时间>2019-11-09

表结构

  • MySQL源member表

CREATE TABLE member(
            member_id VARCHAR ( 64 ),
            phoneno VARCHAR ( 20 ),
            create_time datetime,
            update_time datetime );

  • ODS层增量表member_delta,每天一个分区

CREATE TABLE member_delta
          (member_id string,
            phoneno string,
            create_time string,
            update_time string)
PARTITIONED BY (DAY string);

  • 临时表

CREATE TABLE member_his_tmp
          (member_id string,
            phoneno string,
            effective_date date,
            expire_date date
            );

  • DW层历史拉链表

CREATE TABLE member_his
          (member_id string,
            phoneno string,
            effective_date date,
            expire_date date);

Demo数据准备

2019-11-08的数据为： 

member_idphonenocreate_timeupdate_time
10001135000000012019-11-08 14:47:552019-11-08 14:47:55
10002135000000022019-11-08 14:48:332019-11-08 14:48:33
10003135000000032019-11-08 14:48:532019-11-08 14:48:53
10004135000000042019-11-08 14:49:022019-11-08 14:49:02

2019-11-09的数据为：其中蓝色代表新增数据，红色代表修改的数据

member_idphonenocreate_timeupdate_time
10001135000000012019-11-08 14:47:552019-11-08 14:47:55
10002136000000022019-11-08 14:48:332019-11-09 14:48:33
10003135000000032019-11-08 14:48:532019-11-08 14:48:53
10004135000000042019-11-08 14:49:022019-11-08 14:49:02
10005135000000052019-11-09 08:54:032019-11-09 08:54:03
10006135000000062019-11-09 09:54:252019-11-09 09:54:25

2019-11-10的数据：其中蓝色代表新增数据，红色代表修改的数据  

member_idphonenocreate_timeupdate_time
10001135000000012019-11-08 14:47:552019-11-08 14:47:55
10002136000000022019-11-08 14:48:332019-11-09 14:48:33
10003135000000032019-11-08 14:48:532019-11-08 14:48:53
10004136000000042019-11-08 14:49:022019-11-10 14:49:02
10005135000000052019-11-09 08:54:032019-11-09 08:54:03
10006135000000062019-11-09 09:54:252019-11-09 09:54:25
10007135000000072019-11-10 17:41:492019-11-10 17:41:49

全量初始装载

在启用拉链表时，先对其进行初始装载，比如以2019-11-08为开始时间，那么将MySQL源表全量抽取到ODS层member_delta表的2018-11-08的分区中，然后初始装载DW层的拉链表member_his

INSERT overwrite TABLE member_his
SELECT
  member_id,
  phoneno,
  to_date ( create_time ) AS effective_date,
 '3000-12-31'
FROM
member_delta
WHERE
DAY = '2019-11-08'

查询初始的历史拉链表数据

1df8bd399009bcd7abbd203eb05d45bf.png

增量抽取数据

每天，从源系统member表中，将前一天的增量数据抽取到ODS层的增量数据表member_delta对应的分区中。这里的增量需要通过member表中的创建时间和修改时间来确定，或者使用sqoop job监控update时间来进行增联抽取。比如，本案例中2019-11-09和2019-11-10为两个分区，分别存储了2019-11-09和2019-11-10日的增量数据。2019-11-09分区的数据为:

f3660fb7c816c2f0b0e94b1b22876ec4.png

2019-11-10分区的数据为：

fead289d84fb706737b314d2ed0cbe14.png

增量刷新历史拉链数据

  • 2019-11-09增量刷新历史拉链表将数据放进临时表

INSERT overwrite TABLE member_his_tmp
SELECT *
FROM
  (
-- 2019-11-09增量数据，代表最新的状态，该数据的生效时间是2019-11-09，过期时间为3000-12-31
-- 这些增量的数据需要被全部加载到历史拉链表中
SELECT member_id,
       phoneno,
       '2019-11-09' effective_date,
                    '3000-12-31' expire_date
   FROM member_delta
   WHERE DAY='2019-11-09'
   UNION ALL 
-- 用当前为生效状态的拉链数据，去left join 增量数据，
-- 如果匹配得上，则表示该数据已发生了更新，
-- 此时，需要将发生更新的数据的过期时间更改为当前时间.
-- 如果匹配不上，则表明该数据没有发生更新，此时过期时间不变
SELECT a.member_id,
       a.phoneno,
       a.effective_date,
       if(b.member_id IS NULL, to_date(a.expire_date), to_date(b.day)) expire_date
   FROM
     (SELECT *
      FROM member_his
      WHERE expire_date='3000-12-31') a
   LEFT JOIN
     (SELECT *
      FROM member_delta
      WHERE DAY='2019-11-09') b ON a.member_id=b.member_id)his

将数据覆盖到历史拉链表

INSERT overwrite TABLE member_his
SELECT *
FROM member_his_tmp

查看历史拉链表

75e1b58fcff9c3a0b7c062ee1e197500.png

  • 2019-11-10增量刷新历史拉链表

将数据放进临时表

INSERT overwrite TABLE member_his_tmp
SELECT *
FROM
(
-- 2019-11-10增量数据，代表最新的状态，该数据的生效时间是2019-11-10，过期时间为3000-12-31
-- 这些增量的数据需要被全部加载到历史拉链表中
SELECT member_id,
      phoneno,
      '2019-11-10' effective_date,
                   '3000-12-31' expire_date
  FROM member_delta
  WHERE DAY='2019-11-10'
  UNION ALL
-- 用当前为生效状态的拉链数据，去left join 增量数据，
-- 如果匹配得上，则表示该数据已发生了更新，
-- 此时，需要将发生更新的数据的过期时间更改为当前时间.
-- 如果匹配不上，则表明该数据没有发生更新，此时过期时间不变
SELECT a.member_id,
      a.phoneno,
      a.effective_date,
      if(b.member_id IS NULL, to_date(a.expire_date), to_date(b.day)) expire_date
  FROM
    (SELECT *
    FROM member_his
    WHERE expire_date='3000-12-31') a
  LEFT JOIN
    (SELECT *
    FROM member_delta
    WHERE DAY='2019-11-10') b ON a.member_id=b.member_id)his

查看历史拉链表

dbe1983960ecfe369abc87e26fe887bf.png

将以上脚本封装成shell调度的脚本

#!/bin/bash

#如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="

INSERT overwrite TABLE member_his_tmp
SELECT *
FROM
  (
-- 2019-11-10增量数据，代表最新的状态，该数据的生效时间是2019-11-10，过期时间为3000-12-31
-- 这些增量的数据需要被全部加载到历史拉链表中
SELECT member_id,
       phoneno,
       '$do_date' effective_date,
       '3000-12-31' expire_date
   FROM member_delta
   WHERE DAY='$do_date'
   UNION ALL
-- 用当前为生效状态的拉链数据，去left join 增量数据，
-- 如果匹配得上，则表示该数据已发生了更新，
-- 此时，需要将发生更新的数据的过期时间更改为当前时间.
-- 如果匹配不上，则表明该数据没有发生更新，此时过期时间不变
SELECT a.member_id,
       a.phoneno,
       a.effective_date,
       if(b.member_id IS NULL, to_date(a.expire_date), to_date(b.day)) expire_date
   FROM
     (SELECT *
      FROM member_his
      WHERE expire_date='3000-12-31') a
   LEFT JOIN
     (SELECT *
      FROM member_delta
      WHERE DAY='$do_date') b ON a.member_id=b.member_id)his;
"

$hive -e "$sql"
5dd082f5677cdceb8a12cb676a54174e.png— END—

如果感觉文章有用，记得分享给你的朋友哦

(在看也是鼓励！)

3ed4bb88e570354799569494a6ed987a.png

景 风
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
hive 新建表，写入查询结果
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
04-27 4491
hive> create table sales_info_new_new > as > select sku_id,sku_name,category_id3,price,sales_count,dt > from sales_info where dt = '2019-04-26'; 原表DDL: CREATE TABLE `sale...
Hive大表的测试数据
07-31
Hive大表的测试数据
hive内部表和外部表的区别_Hive 拉链表实践
weixin_39760650的博客
11-24 116
点击“蓝字”关注我们吧相关推荐：系列 | 数仓实践第三篇NO.3『拉链表』导读：之前我们已经分享过在Greenplum上实现拉链表实践，采用过程函数式的方式insert/update实现。今天主要分享在Hive上实现拉链表代码实践。☞ 关注公众号『数据仓库与Python大数据』，获取更多优质资源与干货文章。背景拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的；顾名思...
4.HiveSQL查询语句方法大全
最新发布
m0_63953077的博客
05-03 1155
HiveSQL查询语句方法归纳与实战
数据仓库分层
wzj_wp的博客
01-10 764
数据仓库&数据集市 数据仓库 数据仓库，英文名称为Data Warehouse，可简写为DW或DWH。数据仓库，是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合。它是单个数据存储，出于分析性报告和决策支持目的而创建。 为需要业务智能的企业，提供指导业务流程改进、监视时间、成本、质量以及控制 简单来说就是一堆数据集合（log型和db型）的整合处理，，挖掘有效价值，提供决策支...
HIVE的三种建表方式
热门推荐
weixin_44988131的博客
11-01 2万+
目录一、直接建表二、as （直接使用查询结果插入到一张新表）三、like（复制表结构） 一、直接建表 中括号里面的均为可选项 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 　　[(col_name data_type [COMMENT col_comment], ...)] 　　[COMMENT table_comment] 　　[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...
Hive（3）
m0_56660665的博客
09-05 2019
hive3
Hive小表的测试数据
07-31
Hive小表的测试数据
python导出hive数据表的schema实例代码
12-24
想必他们对表结构不甚了解，还需要为之提供一个表结构说明，于是编写了一个脚本，从hive数据库中将每张表的字段即类型查询出来，代码如下： #coding=utf-8 import pyhs2 from xlwt import * hiveconn = pyhs2....
Mysql元数据如何生成Hive建表语句注释脚本详解
01-19
最近在将数据从Mysql 等其他关系型数据库 抽取到Hive 表中时，需要同步mysql表中的注释，以下脚本可以生成hive表字段注释修改语句。 注：其他关系型数据库如：oracle 可以通过相同的思路，读取元数据，修改脚本语法...
达观数据分析平台架构和Hive实践
02-26
Hadoop于2006年1月28日诞生，至今已有10年，它改变了企业...面对当今互联网产生的巨大的TB甚至PB级原始数据，利用基于Hadoop的数据仓库解决方案Hive早已是Hadoop的热点应用之一。达观数据团队长期致力于研究和积累Hadoo
HiveSQL建表-查询
Vulpes corsac
08-19 2725
Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构 化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需 要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。而mapreduce开发人员可以把 己写的mapper 和reducer 作为插件来支持
Hive创建表
zhaoxiaoba123的博客
09-01 4083
建表语法 字段解释说明 (1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出 异常;用户可以用 IFNOTEXISTS 选项来忽略这个异常。 (2)EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际 数据的路径(LOCATION)，Hive 创建内部表时，会将数据移动到数据仓库指向的路径,若创建外部表仅记录数据所在的路径不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据不删除数据。 (3
hive根据已有表创建新表_Hive 架构
weixin_30117665的博客
12-24 621
前言下图展示了 Hive 的组成以及 Hive 和 Hadoop 的交互。Hive 组件UI： 用户提交查询语句以及其它管理操作的入口 ( command line )Driver： 接收用户提交的查询语句，与 Compiler 创建会话连接。Compiler： 对查询语句进行语义分析并生成执行计划Metastore: 元数据库，存储表的结构信息，分区信息以及与 HDFS 文件的关联信息。Exec...
hive根据已有表创建新表_数据库知识总结—(八)数据库&表的CRUD
weixin_39669638的博客
12-28 581
一.创建表本节用到的表结构如下：customers表vendors表orderitems表一般有两种创建表的方法：使用具有交互式创建和管理表的工具；或者直接用MySQL语句操纵。（1）表创建基础 为利用create table创建表，必须给出下列信息：（一）新表的名字，在create table之后给出。（二）表列的名字和定义，用逗号隔开。//创建顾客信息表 Create table custo...
hive根据已有表创建新表_Hive中新建表
weixin_39598941的博客
12-29 1119
最基本的建表语句：create table student(id string comment '学号',name string comment '姓名',sex string comment '性别',age string comment '年龄') comment '学生表';show create table student;CREATE TABLE `student`(`id` strin...
大数据技术基础实验十：Hive实验——新建Hive表
北天的博客
11-05 1858
大数据基础技术实验十，学习掌握有关Hive表的创建查看删除等基础操作。
Hive将查询结果保存到另一张表中
vincent_duan的专栏
11-14 9218
例如使用查询语句select province,count(1) from track_info where day='2013-07-21' group by province ;查询出的结果如下： 我们想要将这个结果自动存储到另一张表中。 创建一张表： create table track_info_province_stat ( province string, cnt bigint ) ...
dataframe pandas 取得当前时间段的后一个时间点数据
a5601564的博客
09-28 2146
数据从星期一到星期五的日线数据形成周线数据，然后想定位到所对应的周线数据的情况，就可以通过 backfill参数实现。 data_day_df.index.get_loc(current_kline.open_time, method='backfill') open_time为当前的日线级数据。运算后得到周线数据。 ...
HIVE中，如何将DESC TABLE_NAME返回的数据保存为一张临时表
02-17
在Hive中，您可以使用以下步骤将DESC TABLE_NAME返回的数据保存为一张临时表： 1. 使用DESCRIBE FORMATTED TABLE_NAME命令获取表的元数据信息。 2. 通过在命令末尾添加“INTO TABLE temp_table”来将结果保存到临时...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值