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

数据处理思路

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;