hive数仓 ods层增量数据导入

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: 根据业务需求,当表数据量超过10万条时采用增量数据导入,否则全量导入。增量导入基于`create_date`和`modify_date`字段进行,并确保时间字段已建立索引以提升查询效率。避免在索引字段上执行函数操作。创建增量表和全量表,并按日期进行分区。首次导入全量数据,后续每日新增或变更数据保存在增量表中,通过全量表与增量表的合并保持数据一致性。

依据我们当前的业务。选择表数据量大于10W以上采用增量数据导入,10W以下采用全量数据导入。增量数据依据时间字段进行判断(create_date,modify_date)。

如果针对时间字段进行增量数据导入,需要先确定当前业务数据库中是否针对时间字段添加了索引(提高查询速度);其次,在写增量导入sql时不要对索引字段进行函数操作。比如:

date_format(create_date,'%Y%m%d')>=${bizdate} ×,这种写法会导致无法使用索引
create_date>=date_format(${bizdate},'%Y%m%d') √,推荐使用这种写法

首先创建两张表,分别为增量表和全量表

-- 增量表_delta
-- 存储了当日新增或变化数据
create table if not exists ods_s_mscmp_msc_b_spu_delta
(
spu_code                string  comment"商品代码"
,product_category_code   string  comment"产品目录代码"
,spu_name                string  comment"商品名称"
,spu_type                string  comment"商品类型"
,external_code           string  comment"第三方商品原始代码"
,external_id             string  comment"第三方系统商品ID"
,state                   string  comment"状态"
,is_delete               string  comment"逻辑删除标志位"
,create_date             string  comment"创建时间"
,create_by               string  comment"创建者"
,modify_date             string  comment"修改时间"
,modify_by               string  comment"修改者"
)PARTITIONED BY (dt STRING) -- 按照日期分区
-- 全量表
-- 存放历史全量数据
create table if not exists ods_s_mscmp_msc_b_spu
(
spu_code                string  comment"商品代码"
,product_category_code   string  comment"产品目录代码"
,spu_name                string  comment"商品名称"
,spu_type                string  comment"商品类型"
,external_code           string  comment"第三方商品原始代码"
,external_id             string  comment"第三方系统商品ID"
,state                   string  comment"状态"
,is_delete               string  comment"逻辑删除标志位"
,create_date             string  comment"创建时间"
,create_by               string  comment"创建者"
,modify_date             string  comment"修改时间"
,modify_by               string  comment"修改者"
)PARTITIONED BY (dt STRING) -- 按照日期分区

增量表和全量表字段完全一致,首次需要先对全量表ods_s_mscmp_msc_b_spu进行全量数据导入。后续每天的新增或者变化数据均保存到增量表ods_s_mscmp_msc_b_spu_delta中,然后将增量表和全量表进行合并,合并代码为:

-- 增量表数据主要分为三部分,分别为新增数据,变化数据,历史无变化数据。
-- 使用增量表 full join 全量表(采用主键id关联)将全量表和增量表进行合并。
-- 当full join结果中增量表di主键id不为空则说明新增或历史数据发生了变化,采用新增或更新后的字段值。
-- 当full join结果中增量表di主键为空则说明历史数据无变化,使用df原字段值。
-- 将拼接的最新全量数据写入到全量表T-1分区中。
insert into table ods_s_mscmp_msc_b_spu partition(ds = ${bizdate})
select 
  case when di.spu_code is not null then di.spu_code else df.spu_code    end as spu_code             
  case when di.spu_code is not null then di.product_category_code else df.product_category_code end as product_category_code
  case when di.spu_code is not null then di.spu_name else df.spu_name    end as spu_name             
  case when di.spu_code is not null then di.spu_type else df.spu_type    end as spu_type             
  case when di.spu_code is not null then di.external_code else df.external_code end as external_code        
  case when di.spu_code is not null then di.external_id else df.external_id end as external_id          
  case when di.spu_code is not null then di.state    else df.state end as state                
  case when di.spu_code is not null then di.is_delete else df.is_delete    end as is_delete            
  case when di.spu_code is not null then di.create_date else df.create_date end as create_date          
  case when di.spu_code is not null then di.create_by else df.create_by end as create_by            
  case when di.spu_code is not null then di.modify_date else df.modify_date end as modify_date          
  case when di.spu_code is not null then di.modify_by else df.modify_by end as modify_by            
from
  (select
      spu_code                
      ,product_category_code  
      ,spu_name               
      ,spu_type               
      ,external_code          
      ,external_id            
      ,state                  
      ,is_delete              
      ,create_date            
      ,create_by              
      ,modify_date            
      ,modify_by              
  from ods_s_mscmp_msc_b_spu
  where ds = ${bizdate} -- 昨天分区的日期T-1
  )di full join
  (select
      spu_code                
      ,product_category_code  
      ,spu_name               
      ,spu_type               
      ,external_code          
      ,external_id            
      ,state                  
      ,is_delete              
      ,create_date            
      ,create_by              
      ,modify_date            
      ,modify_by              
  from ods_s_mscmp_msc_b_spu
  where ds = date_sub(${bizdate}, 1) -- 前天分区的日期T-2
  )df on di.spu_code = df.spu_code
相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
2月前
|
分布式计算 关系型数据库 Serverless
实时数仓 Hologres产品使用合集之如何将ODPS视图表数据导入到Hologres内表
实时数仓Hologres是阿里云推出的一款高性能、实时分析的数据库服务,专为大数据分析和复杂查询场景设计。使用Hologres,企业能够打破传统数据仓库的延迟瓶颈,实现数据到决策的无缝衔接,加速业务创新和响应速度。以下是Hologres产品的一些典型使用场景合集。
|
2月前
|
SQL 分布式计算 数据安全/隐私保护
实时数仓 Hologres产品使用合集之重建表的索引后,如何将数据导入新表
实时数仓Hologres是阿里云推出的一款高性能、实时分析的数据库服务,专为大数据分析和复杂查询场景设计。使用Hologres,企业能够打破传统数据仓库的延迟瓶颈,实现数据到决策的无缝衔接,加速业务创新和响应速度。以下是Hologres产品的一些典型使用场景合集。
|
3月前
|
分布式计算 DataWorks 关系型数据库
阿里云数加-分析型数据库AnalyticDB数据导入的多样化策略
通过合理利用这些数据导入方法,用户可以充分发挥AnalyticDB的实时计算能力和高并发查询性能,为业务分析和决策提供强有力的数据支持。
|
4月前
|
SQL JSON DataX
离线数仓(六)【ODS 层开发】
离线数仓(六)【ODS 层开发】
|
4月前
|
消息中间件 存储 Kafka
Flink 实时数仓(二)【ODS 层开发】
Flink 实时数仓(二)【ODS 层开发】
|
5月前
|
SQL 数据采集 分布式计算
Hive 数仓及数仓设计方案
数仓整合企业数据,提供统一出口,用于数据治理。其特点包括面向主题集成和主要支持查询操作。数仓设计涉及需求分析(如咨询老板、运营人员和行业专家)、确定主题指标(如电商的转化率)、数据标准设定、规模与成本计算、技术选型(如Hadoop生态组件)以及数据采集和操作。设计流程涵盖从理解需求到实施SQL函数和存储过程的全过程。
92 3
|
5月前
|
分布式计算 关系型数据库 OLAP
阿里云AnalyticDB基于Flink CDC+Hudi实现多表全增量入湖实践
阿里云AnalyticDB基于Flink CDC+Hudi实现多表全增量入湖实践
161 0
|
5月前
|
数据格式
数仓学习---数仓开发之ODS层
数仓学习---数仓开发之ODS层
693 2
|
5月前
|
SQL 分布式计算 关系型数据库
Sqoop数据导入到Hive表的最佳实践
Sqoop数据导入到Hive表的最佳实践
|
5月前
|
存储 SQL 分布式计算
Hadoop(HDFS+MapReduce+Hive+数仓基础概念)学习笔记(自用)
Hadoop(HDFS+MapReduce+Hive+数仓基础概念)学习笔记(自用)
471 0

热门文章

最新文章