数仓搭建实操(传统数仓oracle):DWD数据明细层

news/2025/2/25 0:09:57

数据处理思路

DWD层, 数据明细层>>数据清洗转换, 区分事实表,维度表

全是事实表,没有维度表>>不做处理

数据清洗>>数据类型varchar 变成varchar2, 日期格式统一(时间类型变成varchar2); 字符数据去空格

 知识补充:

varchar 存储定长字符类型 ; 存储的数据会根据定义的长度来占用空间，不足部分会用空格填充

varchar2 存储可变长度字符串 ; 只占用实际存储数据所需的空间加上一个额外的字节来记录长度

varchar2是oracle数据库特有的, varchar是大多数数据库通用的

把数据类型从varchar 变成varchar2也是为了数据存储时不占用过多的空间

查看表中数据的存储是否有空格占空间

示例

以公司客户信息表(CI_CIE_CORP_CUST_INFO)为例

查看结果>>原表中的数据空格占用了大量的空间

解决>>更改varchar的存储长度/把varchar变成varchar2

实操示例1

建表

批量建表>>使用PLSQL

建表的表结构和注释需和ODS层(用户)一致, 需要的信息是: 表名, 表字段, 字段注释>>定义3个游标从ODS层循环获取

建表时进行的数据清洗>>把char类型变成varchar2类型; 把时间类型变成varchar2类型

DECLARE
   -- 获取ODS用户的表名
   CURSOR C_TABLES IS  SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'ODS';

   -- 获取ODS用户下所有表的字段
   CURSOR C_COLUMNS (P_TABLE VARCHAR2) IS
   SELECT 
      TABLE_NAME
      ,COLUMN_NAME
      ,DATA_TYPE
      ,DATA_LENGTH
      ,DATA_PRECISION
      ,DATA_SCALE
    FROM DBA_TAB_COLUMNS 
    WHERE OWNER = 'ODS' AND TABLE_NAME = P_TABLE ORDER BY COLUMN_ID;
    
    -- 获取所有的字段注释
    CURSOR C_COL_COMMENTS (P_TABLE VARCHAR2) IS
    SELECT 
       COLUMN_NAME
       ,COMMENTS
    FROM DBA_COL_COMMENTS 
    WHERE OWNER = 'ODS' AND TABLE_NAME = P_TABLE;

    V_SQL VARCHAR2(3000); -- 构建sql语句
    V_DATA_TYPE VARCHAR2(2000); -- 构建 数据类型
    V_COMMENT_SQL VARCHAR2(2000); -- 构建 添加字段注释的脚本
BEGIN
  FOR X IN C_TABLES LOOP
    
    BEGIN -- 如果表存在则删除
      EXECUTE IMMEDIATE 'DROP TABLE DWD.'||X.TABLE_NAME ||' PURGE';
     EXCEPTION 
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    
    -- 构建创建表的语句
    V_SQL := 'CREATE TABLE DWD.'||X.TABLE_NAME||'(';
    
    -- 遍历 列 游标
    FOR Y IN C_COLUMNS(X.TABLE_NAME) LOOP
      V_DATA_TYPE := Y.DATA_TYPE;
      -- 处理精度和小数（number）
      IF Y.DATA_PRECISION IS NOT NULL THEN
        V_DATA_TYPE := V_DATA_TYPE||'('||Y.DATA_PRECISION;
        
        IF Y.DATA_SCALE IS NOT NULL THEN
          V_DATA_TYPE := V_DATA_TYPE ||','||Y.DATA_SCALE;
        END IF;
        
        V_DATA_TYPE := V_DATA_TYPE||')';
      
      ELSE
        V_DATA_TYPE := V_DATA_TYPE||'('||Y.DATA_LENGTH||')';
        
        IF Y.DATA_TYPE = 'CHAR' THEN
           V_DATA_TYPE := 'VARCHAR2('||Y.DATA_LENGTH||')';
           --DBMS_OUTPUT.PUT_LINE('VARCHAR2('||Y.DATA_LENGTH||')');
        END IF;
        
        IF Y.DATA_TYPE IN ('DATE','TIMESTAMP') THEN
            V_DATA_TYPE := 'VARCHAR2(200)';
        END IF;
      END IF;
      
      
      
      V_SQL := V_SQL||Y.COLUMN_NAME||' '||V_DATA_TYPE;
      V_SQL := V_SQL ||',';
      
    END LOOP;
    V_SQL := SUBSTR(V_SQL,1,LENGTH(V_SQL)-1);
     V_SQL := V_SQL||')';
     
     -- DBMS_OUTPUT.PUT_LINE(V_SQL);
     EXECUTE IMMEDIATE V_SQL;
     
 
       -- 给字段添加注释
    FOR V IN C_COL_COMMENTS(X.TABLE_NAME) LOOP
      V_COMMENT_SQL := 'COMMENT ON COLUMN DWD.'||X.TABLE_NAME||'.'||V.COLUMN_NAME||' IS' ||''''||V.COMMENTS||'''';
      EXECUTE IMMEDIATE V_COMMENT_SQL;
    END LOOP;
   END LOOP;
 
END;

注: PLSQL的分析参照ODS层的建表PLSQL

和ODS建表PLSQL的不同之处

1.用户名

2.多了一个if 语句来把char类型变成varchar2类型

3.多一个if 语句把时间类型变成varchar2字符串类型

插入数据

--  ODS 数据到 DWD 

DECLARE
    -- 获取ODS用户的表名
   CURSOR C_TABLES IS  SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'ODS';

   -- 获取ODS用户下所有表的字段
   CURSOR C_COLUMNS (P_TABLE VARCHAR2) IS
   SELECT 
      TABLE_NAME
      ,COLUMN_NAME
      ,DATA_TYPE
      ,DATA_LENGTH
      ,DATA_PRECISION
      ,DATA_SCALE
    FROM DBA_TAB_COLUMNS 
    WHERE OWNER = 'ODS' AND TABLE_NAME = P_TABLE ORDER BY COLUMN_ID;
    
    --定义变量
    COL_LIST VARCHAR2(4000);---用于存储字段列表
    CHAR_COL VARCHAR2(4000);---用于处理数据类型和格式的转换
    IS_FIRST_COL BOOLEAN := TRUE;  ---用于判断是否是第一个字段
    V_SQL VARCHAR2(4000);  ---------用于存储建表sql

BEGIN

  ----外循环获取ODS的表名
  FOR X IN C_TABLES LOOP
    COL_LIST := '';
    IS_FIRST_COL := TRUE;

  ----内循环遍历当前表的所有字段且进行数据类型及日期格式的转换
    FOR Y IN C_COLUMNS(X.TABLE_NAME) LOOP
      IF Y.DATA_TYPE = 'CHAR' THEN   
        CHAR_COL := 'TRIM('||Y.COLUMN_NAME||')';  
      ELSIF Y.DATA_TYPE IN ('DATE','TIMESTAMP') THEN
        CHAR_COL := 'TO_CHAR('||Y.COLUMN_NAME||','||''''||'YYYYMMDD'||''''||')';
      ELSE
        CHAR_COL := Y.COLUMN_NAME;
      END IF;
        

            ----定义COL_LIST字段的拼接条件
      IF IS_FIRST_COL THEN
        COL_LIST := COL_LIST||CHAR_COL;
        IS_FIRST_COL := FALSE;---------不重新赋值无法进入else子语句
      ELSE
        COL_LIST := COL_LIST||','||CHAR_COL;
      END IF;
    END LOOP;

    V_SQL := 'INSERT INTO DWD.'||X.TABLE_NAME||' SELECT '||COL_LIST||' FROM ODS.'||X.TABLE_NAME;
    -- DBMS_OUTPUT.PUT_LINE(V_SQL);  ----输出拼接的插入sql进行检查,检查后注释掉
    EXECUTE IMMEDIATE V_SQL;     ----动态执行V_SQL
    COMMIT; ----提交事务
  END LOOP;
    
END;

为什么需要重新赋值 IS_FIRST_COL := FALSE;

在PL/SQL中，布尔变量的值不会自动改变，必须通过显式的赋值操作来更新其状态。如果不写 IS_FIRST_COL := FALSE;，IS_FIRST_COL 的值将始终保持为初始值 TRUE，导致逻辑无法正确切换到后续字段的处理逻辑。

特殊的分区表和拉链表

如果是大量的分区表和拉链表>>修改建表PLSQL

如果是大量的普通表里面夹杂着一两个分区表和拉链表>>注释掉建表PLSQL的动态执行语句, 解除输出语句的注释, 批量输出建表语句, 复制到oracle的SQL执行区域, 找到要建分区表/拉链表的那张表>>修改建表语句>>执行

     DBMS_OUTPUT.PUT_LINE(V_SQL);
     ---EXECUTE IMMEDIATE V_SQL;

实操示例2

 因为数据源DB也在oracle数据库, 也可以选择在ODS层就对数据进行数据清洗操作

建表----数据类型转换

DECLARE
    v_sql           VARCHAR2(4000);
    v_comment_sql   VARCHAR2(4000);
    v_data_type     VARCHAR2(100);
    
    -- 获取DB用户下的所有表
    CURSOR c_tables IS
        SELECT table_name
        FROM dba_tables
        WHERE owner = 'DB';
    
    -- 获取指定表的列信息
    CURSOR c_columns (p_table_name VARCHAR2) IS
        SELECT column_name,
               data_type,
               data_length,
               data_precision,
               data_scale,
               nullable
        FROM dba_tab_columns
        WHERE owner = 'DB'
          AND table_name = p_table_name
        ORDER BY column_id;
    
    -- 获取列注释
    CURSOR c_col_comments (p_table_name VARCHAR2) IS
        SELECT column_name, comments
        FROM dba_col_comments
        WHERE owner = 'DB'
          AND table_name = p_table_name;

BEGIN
    FOR t IN c_tables LOOP
        -- 删除ODS用户下的表（如果存在）
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE ODS.' || t.table_name || ' PURGE';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;

        -- 构建CREATE TABLE语句
        v_sql := 'CREATE TABLE ODS.' || t.table_name || ' (';
        FOR c IN c_columns(t.table_name) LOOP
            -- 替换数据类型
            IF c.data_type = 'CHAR' THEN
                v_data_type := 'VARCHAR2(' || c.data_length || ')';
            ELSIF c.data_type = 'TIMESTAMP' THEN
                v_data_type := 'DATE';
            ELSE
                v_data_type := c.data_type;
                -- 处理精度和小数位（如NUMBER）
                IF c.data_precision IS NOT NULL THEN
                    v_data_type := v_data_type || '(' || c.data_precision;
                    IF c.data_scale IS NOT NULL THEN
                        v_data_type := v_data_type || ',' || c.data_scale;
                    END IF;
                    v_data_type := v_data_type || ')';
                ELSIF c.data_type IN ('VARCHAR2', 'NVARCHAR2', 'RAW') THEN
                    v_data_type := v_data_type || '(' || c.data_length || ')';
                END IF;
            END IF;

            -- 拼接列定义
            v_sql := v_sql || c.column_name || ' ' || v_data_type;
            
            -- 处理NOT NULL约束
            IF c.nullable = 'N' THEN
                v_sql := v_sql || ' NOT NULL';
            END IF;
            v_sql := v_sql || ', ';
        END LOOP;

        -- 完成CREATE TABLE语句
        v_sql := RTRIM(v_sql, ', ') || ')';
        EXECUTE IMMEDIATE v_sql;

        -- 添加字段注释
        FOR com IN c_col_comments(t.table_name) LOOP
            v_comment_sql := 'COMMENT ON COLUMN ODS.' || t.table_name || '.' || com.column_name ||
                            ' IS ''' || REPLACE(COALESCE(com.comments, '暂无注释'), '''', '''''') || '''';
            EXECUTE IMMEDIATE v_comment_sql;
        END LOOP;
    END LOOP;
END;

插入数据----去空格

DECLARE
    -- 获取DB用户所有的表
    CURSOR c_tables IS
        SELECT table_name
        FROM dba_tables
        WHERE owner = 'DB';
    -- 获取每张表中的字段名和数据类型
     CURSOR c_columns (p_table_name VARCHAR2) IS
        SELECT column_name,
               data_type
        FROM dba_tab_columns
        WHERE owner = 'DB'
          AND table_name = p_table_name
        ORDER BY column_id;
        
     col_list VARCHAR(2000); -- 存放字段   
     char_col VARCHAR2(2000); -- 存放char类型的字段 
     v_sql VARCHAR2(2000); -- 最后需要动态执行的sql语句
     first_column BOOLEAN := TRUE; -- 用于标记是否为第一个字段
BEGIN
  -- 遍历所有的DB表名
  FOR tab IN c_tables LOOP 
    --DBMS_OUTPUT.PUT_LINE(tab.table_name);
    -- 表名作为参数传进c_columns游标 进行遍历
    col_list := '';
    first_column := TRUE;
    FOR col IN c_columns(tab.table_name) LOOP
        IF col.data_type = 'CHAR' THEN -- 类型为char则为字段添加trim函数
          char_col := 'TRIM('||col.column_name||')';
        ELSE
          char_col := col.column_name;
        END IF;
        
        IF first_column THEN
            col_list := char_col; -- 首次拼接不添加逗号
            first_column := FALSE;
        ELSE
            col_list := col_list||','||char_col; -- 非首次拼接添加逗号
        END IF;
    END LOOP;
    v_sql := 'INSERT INTO ODS.'||tab.table_name||' SELECT '||col_list||' FROM DB.'||tab.table_name;
    -- DBMS_OUTPUT.PUT_LINE(v_sql);
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;


http://www.niftyadmin.cn/n/5864869.html

相关文章

jar、war、pom

jar、war、pom

1. <packaging>jar</packaging> 定义与用途 用途&#xff1a;默认打包类型&#xff0c;生成 JAR 文件&#xff08;Java Archive&#xff09;&#xff0c;适用于普通 Java 应用或库。 场景&#xff1a; 开发工具类库&#xff08;如 commons-lang.jar&#xff09;。…
阅读更多...
《AI赋能星际探索：机器人如何开启宇宙新征程！》

《AI赋能星际探索：机器人如何开启宇宙新征程！》

在人类对宇宙无尽的探索中&#xff0c;空间探索任务始终充满挑战。从遥远星球的探测&#xff0c;到空间站的维护&#xff0c;每一项任务都需要高精度、高可靠性的操作。人工智能&#xff08;AI&#xff09;的迅猛发展&#xff0c;为空间探索机器人带来了革命性的变革&#xff0…
阅读更多...
解决每次 Maven Rebuild 后 Java 编译器版本变为 1.5

解决每次 Maven Rebuild 后 Java 编译器版本变为 1.5

解决方法 明确指定 Java 编译版本 在 pom.xml 中添加 maven-compiler-plugin 配置&#xff0c;明确指定 Java 编译版本为 1.8。可以在 标签内添加以下内容&#xff1a; <build><plugins><plugin><groupId>org.apache.maven.plugins</groupId>&…
阅读更多...
deepseek AI写的对动态地址的linux执行文件的加壳

deepseek AI写的对动态地址的linux执行文件的加壳

我开始思考如何逐步完善程序中的各个部分。首先&#xff0c;在shell. c文件中&#xff0c;有一些未定义的部分&#xff0c;如TARGET入口地址、GOT表地址等。这些需要通过调试工具&#xff08;比如gdb&#xff09;获取&#xff0c;并在代码中标明。此外&#xff0c;shellcode数组…
阅读更多...
深度学习-7.超参数优化

深度学习-7.超参数优化

Deep Learning - Lecture 7 Hyperparameter Optimization 简介超参数搜索用于超参数选择的贝叶斯优化启发性示例贝叶斯优化 引用 本节目标&#xff1a; 解释并实现深度学习中使用的不同超参数优化方法&#xff0c;包括&#xff1a; 手动选择网格搜索随机搜索贝叶斯优化 简介 …
阅读更多...
DDD - 整洁架构

DDD - 整洁架构

DDD&#xff08;领域驱动设计&#xff09;的整洁架构&#xff08;Clean Architecture&#xff09;是一种通过分层和依赖规则&#xff0c;确保领域模型&#xff08;业务逻辑&#xff09;与技术实现解耦的架构模式。其核心目标是让业务逻辑成为系统的核心&#xff0c;而技术细节&…
阅读更多...
PH热榜 | 2025-02-23

PH热榜 | 2025-02-23

1. NYX 标语&#xff1a;你智能化的营销助手&#xff0c;助你提升业绩。 介绍&#xff1a;NYX的人工智能助手简化了从头到尾的广告活动管理&#xff0c;帮助你轻松创建高转化率的广告&#xff0c;启动多渠道营销活动&#xff0c;并通过实时分析来优化表现。它还可以整合主要的…
阅读更多...
【网络编程】广播和组播

【网络编程】广播和组播

数据包发送方式只有一个接受方&#xff0c;称为单播。如果同时发给局域网中的所有主机&#xff0c;称为广播。只有用户数据报(使用UDP协议)套接字才能广播&#xff1a; 广播地址以192.168.1.0 (255.255.255.0) 网段为例&#xff0c;最大的主机地址192.168.1.255代表该网段的广…
阅读更多...
最新文章

Copyright @ 2022~2023