数据处理思路
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) ISSELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALEFROM DBA_TAB_COLUMNS WHERE OWNER = 'ODS' AND TABLE_NAME = P_TABLE ORDER BY COLUMN_ID;-- 获取所有的字段注释CURSOR C_COL_COMMENTS (P_TABLE VARCHAR2) ISSELECT COLUMN_NAME,COMMENTSFROM 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); -- 构建 添加字段注释的脚本
BEGINFOR X IN C_TABLES LOOPBEGIN -- 如果表存在则删除EXECUTE IMMEDIATE 'DROP TABLE DWD.'||X.TABLE_NAME ||' PURGE';EXCEPTION WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLERRM);END;-- 构建创建表的语句V_SQL := 'CREATE TABLE DWD.'||X.TABLE_NAME||'(';-- 遍历 列 游标FOR Y IN C_COLUMNS(X.TABLE_NAME) LOOPV_DATA_TYPE := Y.DATA_TYPE;-- 处理精度和小数（number）IF Y.DATA_PRECISION IS NOT NULL THENV_DATA_TYPE := V_DATA_TYPE||'('||Y.DATA_PRECISION;IF Y.DATA_SCALE IS NOT NULL THENV_DATA_TYPE := V_DATA_TYPE ||','||Y.DATA_SCALE;END IF;V_DATA_TYPE := V_DATA_TYPE||')';ELSEV_DATA_TYPE := V_DATA_TYPE||'('||Y.DATA_LENGTH||')';IF Y.DATA_TYPE = 'CHAR' THENV_DATA_TYPE := 'VARCHAR2('||Y.DATA_LENGTH||')';--DBMS_OUTPUT.PUT_LINE('VARCHAR2('||Y.DATA_LENGTH||')');END IF;IF Y.DATA_TYPE IN ('DATE','TIMESTAMP') THENV_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) LOOPV_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) ISSELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALEFROM 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); ---------用于存储建表sqlBEGIN----外循环获取ODS的表名FOR X IN C_TABLES LOOPCOL_LIST := '';IS_FIRST_COL := TRUE;----内循环遍历当前表的所有字段且进行数据类型及日期格式的转换FOR Y IN C_COLUMNS(X.TABLE_NAME) LOOPIF Y.DATA_TYPE = 'CHAR' THEN CHAR_COL := 'TRIM('||Y.COLUMN_NAME||')'; ELSIF Y.DATA_TYPE IN ('DATE','TIMESTAMP') THENCHAR_COL := 'TO_CHAR('||Y.COLUMN_NAME||','||''''||'YYYYMMDD'||''''||')';ELSECHAR_COL := Y.COLUMN_NAME;END IF;----定义COL_LIST字段的拼接条件IF IS_FIRST_COL THENCOL_LIST := COL_LIST||CHAR_COL;IS_FIRST_COL := FALSE;---------不重新赋值无法进入else子语句ELSECOL_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_SQLCOMMIT; ----提交事务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层就对数据进行数据清洗操作
建表----数据类型转换
DECLAREv_sql VARCHAR2(4000);v_comment_sql VARCHAR2(4000);v_data_type VARCHAR2(100);-- 获取DB用户下的所有表CURSOR c_tables ISSELECT table_nameFROM dba_tablesWHERE owner = 'DB';-- 获取指定表的列信息CURSOR c_columns (p_table_name VARCHAR2) ISSELECT column_name,data_type,data_length,data_precision,data_scale,nullableFROM dba_tab_columnsWHERE owner = 'DB'AND table_name = p_table_nameORDER BY column_id;-- 获取列注释CURSOR c_col_comments (p_table_name VARCHAR2) ISSELECT column_name, commentsFROM dba_col_commentsWHERE owner = 'DB'AND table_name = p_table_name;BEGINFOR t IN c_tables LOOP-- 删除ODS用户下的表（如果存在）BEGINEXECUTE IMMEDIATE 'DROP TABLE ODS.' || t.table_name || ' PURGE';EXCEPTIONWHEN OTHERS THENIF SQLCODE != -942 THENRAISE;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' THENv_data_type := 'VARCHAR2(' || c.data_length || ')';ELSIF c.data_type = 'TIMESTAMP' THENv_data_type := 'DATE';ELSEv_data_type := c.data_type;-- 处理精度和小数位（如NUMBER）IF c.data_precision IS NOT NULL THENv_data_type := v_data_type || '(' || c.data_precision;IF c.data_scale IS NOT NULL THENv_data_type := v_data_type || ',' || c.data_scale;END IF;v_data_type := v_data_type || ')';ELSIF c.data_type IN ('VARCHAR2', 'NVARCHAR2', 'RAW') THENv_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' THENv_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) LOOPv_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 ISSELECT table_nameFROM dba_tablesWHERE owner = 'DB';-- 获取每张表中的字段名和数据类型CURSOR c_columns (p_table_name VARCHAR2) ISSELECT column_name,data_typeFROM dba_tab_columnsWHERE owner = 'DB'AND table_name = p_table_nameORDER 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) LOOPIF col.data_type = 'CHAR' THEN -- 类型为char则为字段添加trim函数char_col := 'TRIM('||col.column_name||')';ELSEchar_col := col.column_name;END IF;IF first_column THENcol_list := char_col; -- 首次拼接不添加逗号first_column := FALSE;ELSEcol_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;
