1. 数据仓库ODS层基础认知在数据仓库架构中ODSOperational Data Store层作为原始数据的蓄水池承担着数据抽取、暂存和历史保留的关键职能。与传统的数据库不同ODS层保持业务系统数据的原始状态不做过多清洗转换为后续的数据加工提供原材料。典型企业数据流向示例业务系统 → ODS层 → DWD层 → DWS层 → ADS层其中ODS层的数据特点包括数据粒度与源系统完全一致更新频率通常按T1增量同步存储周期一般保留3-6个月原始数据数据结构保留源表字段不做裁剪2. ODS层表设计规范2.1 命名规则建议采用[层级标识]_[业务域]_[数据主题]_[分表标识]的命名结构层级标识固定为ods业务域如crm(客户关系)、oms(订单)数据主题如user(用户)、order(订单)分表标识daily(日表)/full(全量表)/inc(增量表)示例-- 用户日全量表 CREATE TABLE ods_crm_user_daily ( ... ) PARTITIONED BY (dt string) STORED AS PARQUET;2.2 字段设计原则原始字段完整保留不删除源系统任何字段字段类型与源系统保持一致增加src_update_time记录源系统更新时间技术字段添加dw_create_time timestamp COMMENT 数据仓库创建时间, dw_update_time timestamp COMMENT 数据仓库更新时间, dw_batch_number string COMMENT 数据同步批次号, dw_status smallint COMMENT 数据状态标记分区设计-- 按日期分区是最常见做法 PARTITIONED BY ( dt string COMMENT 数据日期,yyyy-MM-dd格式 ) -- 对于超大表可考虑二级分区 PARTITIONED BY ( dt string, hour string )3. 存储格式选型对比3.1 Parquet vs ORC vs TextFile格式压缩率查询性能Schema演进适用场景Parquet高优支持分析型查询ORC极高最优有限支持Hive生态场景TextFile低差无临时数据/接口文件3.2 压缩算法推荐采用ZSTD压缩的Parquet格式是目前最佳实践SET parquet.compressionZSTD; SET hive.exec.compress.outputtrue;实测对比1GB原始数据ZSTD → 压缩率35% 查询耗时12s SNAPPY → 压缩率45% 查询耗时15s GZIP → 压缩率30% 查询耗时18s4. 完整DDL模板示例4.1 增量表模板CREATE TABLE IF NOT EXISTS ods_oms_order_inc ( order_id bigint COMMENT 订单ID, user_id bigint COMMENT 用户ID, order_amount decimal(18,2) COMMENT 订单金额, order_status tinyint COMMENT 订单状态, create_time timestamp COMMENT 创建时间, update_time timestamp COMMENT 更新时间, -- 技术字段 src_update_time timestamp COMMENT 源系统更新时间, dw_create_time timestamp COMMENT 数据仓库创建时间, dw_update_time timestamp COMMENT 数据仓库更新时间, dw_batch_number string COMMENT 数据批次号, dw_status smallint COMMENT 数据状态1新增 2修改 3删除 ) COMMENT 订单业务增量数据表 PARTITIONED BY (dt string COMMENT 数据日期) STORED AS PARQUET LOCATION /data/warehouse/ods/oms/order_inc TBLPROPERTIES ( parquet.compressionZSTD, transient_lastDdlTimeunix_timestamp() );4.2 全量表模板CREATE TABLE IF NOT EXISTS ods_crm_user_full ( user_id bigint COMMENT 用户ID, user_name string COMMENT 用户名, gender tinyint COMMENT 性别, birthday date COMMENT 生日, mobile string COMMENT 手机号, id_card string COMMENT 身份证号, address string COMMENT 地址, -- 技术字段 dw_create_time timestamp COMMENT 数据仓库创建时间, dw_update_time timestamp COMMENT 数据仓库更新时间, dw_batch_number string COMMENT 数据批次号 ) COMMENT 用户信息全量表 PARTITIONED BY (dt string COMMENT 数据日期) STORED AS PARQUET LOCATION /data/warehouse/ods/crm/user_full TBLPROPERTIES ( parquet.compressionZSTD, auto.purgetrue );5. 数据加载策略5.1 增量同步方案-- Sqoop增量抽取示例 sqoop import \ --connect jdbc:mysql://mysql-server:3306/source_db \ --username etl_user \ --password 123456 \ --table orders \ --target-dir /data/warehouse/ods/oms/order_inc/dt${dt} \ --incremental lastmodified \ --check-column update_time \ --last-value ${last_import_time} \ --merge-key order_id \ --fields-terminated-by \001 \ --compress \ --compression-codec org.apache.hadoop.io.compress.SnappyCodec5.2 全量刷新方案#!/bin/bash # 全量数据加载脚本 current_dt$(date %Y-%m-%d) hive -e SET hive.exec.dynamic.partitiontrue; SET hive.exec.dynamic.partition.modenonstrict; INSERT OVERWRITE TABLE ods_crm_user_full PARTITION(dt${current_dt}) SELECT user_id, user_name, gender, birthday, mobile, id_card, address, current_timestamp() AS dw_create_time, current_timestamp() AS dw_update_time, batch_${current_dt} AS dw_batch_number FROM source_crm.user_info; 6. 元数据管理实践6.1 注释规范-- 表注释示例 COMMENT 订单事实表 - 存储从OMS系统同步的订单主数据包含订单基本信息、金额、状态等核心字段每日增量同步 -- 字段注释示例 order_status tinyint COMMENT 订单状态1-待支付 2-已支付 3-已发货 4-已完成 5-已取消6.2 血缘关系追踪-- 在Hive中记录数据血缘 ALTER TABLE ods_oms_order_inc SET TBLPROPERTIES ( upstream.systemOMS, upstream.tablet_order, etl.ownerbi_team, etl.processsqoop_import_order.sh );7. 常见问题解决方案7.1 数据漂移处理当源系统时间戳不准确导致数据同步遗漏时-- 设置时间缓冲区间建议1-2小时 WHERE update_time ${last_import_time} AND update_time date_add(${current_dt}, 1)7.2 小文件合并-- 使用Hive合并小文件 SET hive.merge.mapfilestrue; SET hive.merge.mapredfilestrue; SET hive.merge.size.per.task256000000; SET hive.merge.smallfiles.avgsize128000000; INSERT OVERWRITE TABLE ods_crm_user_full PARTITION(dt${dt}) SELECT * FROM ods_crm_user_full WHERE dt${dt};7.3 数据类型转换处理源系统与ODS层类型差异-- MySQL的datetime转Hive timestamp CAST(from_unixtime(unix_timestamp(mysql_datetime, yyyy-MM-dd HH:mm:ss)) AS timestamp) -- 字符串转decimal防止精度丢失 CAST(amount_str AS DECIMAL(18,2)) 数据仓库ODS层设计与最佳实践指南
1. 数据仓库ODS层基础认知在数据仓库架构中ODSOperational Data Store层作为原始数据的蓄水池承担着数据抽取、暂存和历史保留的关键职能。与传统的数据库不同ODS层保持业务系统数据的原始状态不做过多清洗转换为后续的数据加工提供原材料。典型企业数据流向示例业务系统 → ODS层 → DWD层 → DWS层 → ADS层其中ODS层的数据特点包括数据粒度与源系统完全一致更新频率通常按T1增量同步存储周期一般保留3-6个月原始数据数据结构保留源表字段不做裁剪2. ODS层表设计规范2.1 命名规则建议采用[层级标识]_[业务域]_[数据主题]_[分表标识]的命名结构层级标识固定为ods业务域如crm(客户关系)、oms(订单)数据主题如user(用户)、order(订单)分表标识daily(日表)/full(全量表)/inc(增量表)示例-- 用户日全量表 CREATE TABLE ods_crm_user_daily ( ... ) PARTITIONED BY (dt string) STORED AS PARQUET;2.2 字段设计原则原始字段完整保留不删除源系统任何字段字段类型与源系统保持一致增加src_update_time记录源系统更新时间技术字段添加dw_create_time timestamp COMMENT 数据仓库创建时间, dw_update_time timestamp COMMENT 数据仓库更新时间, dw_batch_number string COMMENT 数据同步批次号, dw_status smallint COMMENT 数据状态标记分区设计-- 按日期分区是最常见做法 PARTITIONED BY ( dt string COMMENT 数据日期,yyyy-MM-dd格式 ) -- 对于超大表可考虑二级分区 PARTITIONED BY ( dt string, hour string )3. 存储格式选型对比3.1 Parquet vs ORC vs TextFile格式压缩率查询性能Schema演进适用场景Parquet高优支持分析型查询ORC极高最优有限支持Hive生态场景TextFile低差无临时数据/接口文件3.2 压缩算法推荐采用ZSTD压缩的Parquet格式是目前最佳实践SET parquet.compressionZSTD; SET hive.exec.compress.outputtrue;实测对比1GB原始数据ZSTD → 压缩率35% 查询耗时12s SNAPPY → 压缩率45% 查询耗时15s GZIP → 压缩率30% 查询耗时18s4. 完整DDL模板示例4.1 增量表模板CREATE TABLE IF NOT EXISTS ods_oms_order_inc ( order_id bigint COMMENT 订单ID, user_id bigint COMMENT 用户ID, order_amount decimal(18,2) COMMENT 订单金额, order_status tinyint COMMENT 订单状态, create_time timestamp COMMENT 创建时间, update_time timestamp COMMENT 更新时间, -- 技术字段 src_update_time timestamp COMMENT 源系统更新时间, dw_create_time timestamp COMMENT 数据仓库创建时间, dw_update_time timestamp COMMENT 数据仓库更新时间, dw_batch_number string COMMENT 数据批次号, dw_status smallint COMMENT 数据状态1新增 2修改 3删除 ) COMMENT 订单业务增量数据表 PARTITIONED BY (dt string COMMENT 数据日期) STORED AS PARQUET LOCATION /data/warehouse/ods/oms/order_inc TBLPROPERTIES ( parquet.compressionZSTD, transient_lastDdlTimeunix_timestamp() );4.2 全量表模板CREATE TABLE IF NOT EXISTS ods_crm_user_full ( user_id bigint COMMENT 用户ID, user_name string COMMENT 用户名, gender tinyint COMMENT 性别, birthday date COMMENT 生日, mobile string COMMENT 手机号, id_card string COMMENT 身份证号, address string COMMENT 地址, -- 技术字段 dw_create_time timestamp COMMENT 数据仓库创建时间, dw_update_time timestamp COMMENT 数据仓库更新时间, dw_batch_number string COMMENT 数据批次号 ) COMMENT 用户信息全量表 PARTITIONED BY (dt string COMMENT 数据日期) STORED AS PARQUET LOCATION /data/warehouse/ods/crm/user_full TBLPROPERTIES ( parquet.compressionZSTD, auto.purgetrue );5. 数据加载策略5.1 增量同步方案-- Sqoop增量抽取示例 sqoop import \ --connect jdbc:mysql://mysql-server:3306/source_db \ --username etl_user \ --password 123456 \ --table orders \ --target-dir /data/warehouse/ods/oms/order_inc/dt${dt} \ --incremental lastmodified \ --check-column update_time \ --last-value ${last_import_time} \ --merge-key order_id \ --fields-terminated-by \001 \ --compress \ --compression-codec org.apache.hadoop.io.compress.SnappyCodec5.2 全量刷新方案#!/bin/bash # 全量数据加载脚本 current_dt$(date %Y-%m-%d) hive -e SET hive.exec.dynamic.partitiontrue; SET hive.exec.dynamic.partition.modenonstrict; INSERT OVERWRITE TABLE ods_crm_user_full PARTITION(dt${current_dt}) SELECT user_id, user_name, gender, birthday, mobile, id_card, address, current_timestamp() AS dw_create_time, current_timestamp() AS dw_update_time, batch_${current_dt} AS dw_batch_number FROM source_crm.user_info; 6. 元数据管理实践6.1 注释规范-- 表注释示例 COMMENT 订单事实表 - 存储从OMS系统同步的订单主数据包含订单基本信息、金额、状态等核心字段每日增量同步 -- 字段注释示例 order_status tinyint COMMENT 订单状态1-待支付 2-已支付 3-已发货 4-已完成 5-已取消6.2 血缘关系追踪-- 在Hive中记录数据血缘 ALTER TABLE ods_oms_order_inc SET TBLPROPERTIES ( upstream.systemOMS, upstream.tablet_order, etl.ownerbi_team, etl.processsqoop_import_order.sh );7. 常见问题解决方案7.1 数据漂移处理当源系统时间戳不准确导致数据同步遗漏时-- 设置时间缓冲区间建议1-2小时 WHERE update_time ${last_import_time} AND update_time date_add(${current_dt}, 1)7.2 小文件合并-- 使用Hive合并小文件 SET hive.merge.mapfilestrue; SET hive.merge.mapredfilestrue; SET hive.merge.size.per.task256000000; SET hive.merge.smallfiles.avgsize128000000; INSERT OVERWRITE TABLE ods_crm_user_full PARTITION(dt${dt}) SELECT * FROM ods_crm_user_full WHERE dt${dt};7.3 数据类型转换处理源系统与ODS层类型差异-- MySQL的datetime转Hive timestamp CAST(from_unixtime(unix_timestamp(mysql_datetime, yyyy-MM-dd HH:mm:ss)) AS timestamp) -- 字符串转decimal防止精度丢失 CAST(amount_str AS DECIMAL(18,2))