数仓搭建实操(传统数仓oracle):[构建数仓层次|ODS贴源层]

ops/2025/2/24 6:40:55/

构建数仓层次/导入数据

创建五个用户用来分层，并直接赋予DBA角色，方便后期使用

查看权限是否赋予成功

SELECT * FROM DBA_ROLE_PRIVS WHERE granted_role = 'DBA';

将数据文件导入数据源(DB)

将数据导入oracle的命令(在电脑的cmd,即命令提示符中输入命令): 

imp 用户名/密码@ip地址:端口号/库名 file=dmp文件路径/文件名 full=y

ODS贴源层

建表

（字段类型+注释信息）

DECLARE-- 获取DB用户的表名CURSOR C_TABLES IS  SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'DB';-- 获取DB用户下所有表的字段CURSOR C_COLUMNS (P_TABLE VARCHAR2) ISSELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALEFROM DBA_TAB_COLUMNS WHERE OWNER = 'DB' AND TABLE_NAME = P_TABLE;-- 获取所有的字段注释CURSOR C_COL_COMMENTS (P_TABLE VARCHAR2) ISSELECT COLUMN_NAME,COMMENTSFROM DBA_COL_COMMENTS WHERE OWNER = 'DB' AND TABLE_NAME = P_TABLE;V_SQL VARCHAR2(2000); -- 构建sql语句V_DATA_TYPE VARCHAR2(2000); -- 构建 数据类型V_COMMENT_SQL VARCHAR2(2000); -- 构建 添加字段注释的脚本
BEGINFOR X IN C_TABLES LOOPBEGIN -- 如果表存在则删除EXECUTE IMMEDIATE 'DROP TABLE ODS.'||X.TABLE_NAME ||' PURGE';EXCEPTION WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLERRM);END;-- 构建创建表的语句V_SQL := 'CREATE TABLE ODS.'||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 IN ('DATE','TIMESTAMP') THENV_DATA_TYPE := Y.DATA_TYPE;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 ODS.'||X.TABLE_NAME||'.'||V.COLUMN_NAME||' IS' ||''''||V.COMMENTS||'''';EXECUTE IMMEDIATE V_COMMENT_SQL;END LOOP;END LOOP;END;

 数据导入

从DB用户(数据源)导入到ODS(贴源层) 

DECLARE-- 获取DB用户的表名CURSOR C_TABLES IS  SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'DB';V_SQL VARCHAR2(2000);
BEGINFOR V IN C_TABLES LOOPV_SQL := 'INSERT INTO ODS.'||V.TABLE_NAME ||' SELECT * FROM'||' DB.'||V.TABLE_NAME;EXECUTE IMMEDIATE V_SQL;END LOOP;END;

分析建表的PLSQL

获取DB用户的所有表>>dba_table视图

SELECT table_name
FROM dba_tables
WHERE owner = 'DB';

 获取表的字段>>DBA_TAB_COLUMNS 视图

SELECT * FROM DBA_TAB_COLUMNS 
WHERE OWNER = 'DB' AND TABLE_NAME = 'CI_CIE_CORP_CUST_INFO';

获取表字段的注释>> DBA_COL_COMMENTS视图

SELECT * FROM DBA_COL_COMMENTS 
WHERE OWNER = 'DB' AND TABLE_NAME = 'CI_CIE_CORP_CUST_INFO';

建表数量多>>使用PLSQL批量创建表结构

PLSQL的结构

DECLARE--变量声明
BEGIN--执行语句
EXCEPTION--异常处理
END;

declare部分

 先在DECLARE部分声明变量>>变量: 表名; 表的字段; 字段的注释>>需要用游标遍历>>声明3个游标

声明游标

字段的游标需要一个参数把表名传递进去; (P_TABLE VARCHAR2) 

注释的游标需要一个参数把字段传递进去; (P_TABLE VARCHAR2) 

注: 虽然参数名称相同,但是属于两个不同的游标,不会发生冲突

注意数据库之间的区别: 高斯数据库带参数的游标用不了;oracle可以

表名游标只需要获取表名, 过滤条件where  OWNER = 'DB'

字段游标需要获取的信息是:

       TABLE_NAME(字段所属的表)
      ,COLUMN_NAME(字段名称)
      ,DATA_TYPE(数据类型)
      ,DATA_LENGTH(数据长度)
      ,DATA_PRECISION(精度,即number数据类型的最大存储长度)
      ,DATA_SCALE(number数据类型的小数点保留位数)

过滤条件: WHERE OWNER = 'DB' AND TABLE_NAME = P_TABLE

注释游标需要获取的信息: 

      COLUMN_NAME(注释名称)
     ,COMMENTS(注释)

过滤条件: WHERE OWNER = 'DB' AND TABLE_NAME = P_TABLE

声明变量:

    V_SQL VARCHAR2(2000); --变量: 建表sql
    V_DATA_TYPE VARCHAR2(2000); -- 变量: 字段类型
    V_COMMENT_SQL VARCHAR2(2000); -- 变量: 构建 添加字段注释的脚本

begin部分

遍历表游标 
FOR X IN C_TABLES LOOPBEGIN -- 如果表存在则删除EXECUTE IMMEDIATE 'DROP TABLE ODS.'||X.TABLE_NAME ||' PURGE';EXCEPTION WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLERRM);END;-- 构建创建表的语句V_SQL := 'CREATE TABLE ODS.'||X.TABLE_NAME||'(';

用 for 循环遍历表名游标>> 游标值传递给变量X

处理如果表存在的情况>>嵌套一个PLSQL,用来删除表

EXECUTE IMMEDIATE 用来执行动态sql

删除表的sql 语句: DROP TABLE  表名

在表名之前加用户名ODS, 以防止脚本被非ODS用户执行时误删该用户的表

||  字符串连接符, 可以将变量值与静态文本一起连接，以构建完整的 SQL 语句

purge关键字的作用: 

指示数据库立即从数据库的回收站（Recycle Bin）中删除表，而不是将表放入回收站以供后续恢复。

>>避免回滚错误表数据

WHEN OTHERS THEN：异常处理子句

DBMS_OUTPUT.PUT_LINE 是oracle的输出语句

SQLERRM 是一个预定义的变量，它包含了最近一次 SQL 错误的错误消息。

DBMS_OUTPUT.PUT_LINE(SQLERRM);可以让开发者在 SQL Developer等数据库客户端工具中看到被捕获的错误信息

给建表变量赋值 '建表语句' V_SQL := 'CREATE TABLE ODS.'||X.TABLE_NAME||'(';

建表sql示例(不是建表PLSQL里的): 

CREATE TABLE employees (emp_id NUMBER(6) name VARCHAR2(50),    phone_number CHAR(15),        hire_date DATE,            salary NUMBER(8, 2),    );
 遍历字段游标
FOR Y IN C_COLUMNS(X.TABLE_NAME) LOOP

用for循环遍历字段游标, 用参数 X.TABLE_NAME 传递表名,把游标值传递给变量Y

上一个游标(表游标)的遍历后赋值给变量X, 而TABLE_NAME是X的数据域

 V_DATA_TYPE := Y.DATA_TYPE;

把Y变量的DATA_TYPE的这个数据域赋值给变量  V_DATA_TYPE

即把字段游标的DATA_TYPE数据赋值给变量 V_DATA_TYPE

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 IN ('DATE','TIMESTAMP') THENV_DATA_TYPE := Y.DATA_TYPE;END IF;
END IF;

用if 语句处理数据类型的精度(最大存储长度)和保留小数点数, 即 DATA_PRECISION 和 Y.DATA_SCALE

IF Y.DATA_PRECISION IS NOT NULL 为 ture 

即 数据类型是number类型  

格式: number(最大存储长度)或 number(最大存储长度, 保留小数位数)

>>需要在数据类型后面拼接存储长度(Y.DATA_PRECISION)

IF Y.DATA_SCALE IS NOT NULL 即进一步判断number类型有没有被定义保留小数位的位数,

ture >>重新给变量V_DATA_TYPE 赋值, 即在原来已经拼接了精度的基础上拼接保留小数位的位数(Y.DATA_SCALE)

注意: number数据类型的精度和保留小数位之间是用逗号隔开, 需要拼接逗号

最后拼接右括号)

IF Y.DATA_PRECISION IS NOT NULL 为 false

即 数据类型是不是 number类型, 即属于 char(n) 和  varcahr(n) 和  date 等

给变量数据类型 拼接数据长度变量>>V_DATA_TYPE||'('||Y.DATA_LENGTH||')';

用 if 进一步判断变量数据类型是否属于'DATE','TIMESTAMP'这两个数据类型>>这两个类型不需要定义长度

V_SQL := V_SQL||Y.COLUMN_NAME||' '||V_DATA_TYPE;V_SQL := V_SQL ||',';

给建表变量(V_SQL)重新赋值>>在原基础上拼接了字段名变量和字段类型变量和逗号

变量V_SQL的第一次赋值如下

V_SQL := 'CREATE TABLE ODS.'||X.TABLE_NAME||'(';

现在加上拼接后

V_SQL := 'CREATE TABLE ODS.'||X.TABLE_NAME||'('||Y.COLUMN_NAME||' '||V_DATA_TYPE||','

即create tabel ODS.表名(字段名称 数据类型,

END LOOP;
V_SQL := SUBSTR(V_SQL,1,LENGTH(V_SQL)-1);V_SQL := V_SQL||')';

 END LOOP;是字段游标循环的结束

建表语句中,最后一个字段的数据类型后面不需要逗号, 使用substr函数去掉最后一个逗号,在给变量 V_SQL拼接右括号

最后建表语句完成

create tabel ODS.表名(字段名称  数据类型, ......)

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

用oracle的输出语句打印出建表变量V_SQL的实际拼接结果, 以检查是否正确>>验证后注释掉

动态执行建表变量V_SQL

遍历注释游标
  -- 给字段添加注释FOR V IN C_COL_COMMENTS(X.TABLE_NAME) LOOPV_COMMENT_SQL := 'COMMENT ON COLUMN ODS.'||X.TABLE_NAME||'.'||V.COLUMN_NAME||' IS' ||''''||V.COMMENTS||'''';EXECUTE IMMEDIATE V_COMMENT_SQL;END LOOP;END LOOP;END;

用for循环遍历注释游标C_COL_COMMENTS, 给游标传递的参数是表名X.TABLE_NAME

在循环里面给注释变量V_COMMENT_SQL赋值,赋值内容是添加注释的sql

oracle给表中的列添加注释的语法是

表名, 列名是变量

注释内容需要单引号, 单引号需要转义, 转义符号 '  >> 4个单引号, 最外层的一对单引号是引用字符串的单引号, 里面的一对单引号,一个是要拼接的单引号,一个是转义字符

第一个结束的循环是注释游标循环的结束, 第二个结束的循环是表游标循环的结束

end 是PLSQL的结束标志


http://www.ppmy.cn/ops/160908.html

相关文章

根据音频中的不同讲述人声音进行分离音频 | 基于ai的说话人声音分离项目

根据音频中的不同讲述人声音进行分离音频 | 基于ai的说话人声音分离项目

0.研究背景 在实际的开发中可能会遇到这样的问题，老板让你把音频中的每个讲话人的声音分离成不同的音频片段。你可以使用au等专业的音频处理软件手动分离。但是这样效率太慢了，现在ai这么发达，我们能否借助ai之力来分离一条音频中的不同的说…
阅读更多...
【MyBatis】#{} 与 ${} 的区别（常见面试题）

【MyBatis】#{} 与 ${} 的区别（常见面试题）

目录 前言 预编译SQL和即时SQL 什么是预编译SQL？ 什么是即时SQL？ 区别 #{} 与 ${}的使用 防止SQL注入 什么是SQL注入？ 原理 排序功能 模糊查询 总结#{}和${}的区别 前言 在前面的学习中，我们已经知道了如果SQL语句想…
阅读更多...
小游戏-记忆卡牌

小游戏-记忆卡牌

1、游戏开始4张卡牌&#xff0c;每次过关后新增两张&#xff0c;总共64张卡&#xff0c;可以修改数组EMOJIS&#xff0c;添加表情&#xff0c;增加卡牌数量 2、新建txt文件&#xff0c;将代码粘贴进去&#xff0c;保存后&#xff0c;将txt修改后缀名为html的格式 <!DOCTYPE…
阅读更多...
#渗透测试#批量漏洞挖掘#Progress Software Flowmon命令执行漏洞(CVE-2024-2389)

#渗透测试#批量漏洞挖掘#Progress Software Flowmon命令执行漏洞(CVE-2024-2389)

免责声明 本教程仅为合法的教学目的而准备&#xff0c;严禁用于任何形式的违法犯罪活动及其他商业行为&#xff0c;在使用本教程前&#xff0c;您应确保该行为符合当地的法律法规&#xff0c;继续阅读即表示您需自行承担所有操作的后果&#xff0c;如有异议&#xff0c;请立即停…
阅读更多...
Linux权限（一）

Linux权限（一）

文章目录 基本指令sudo权限chmod修改目标属性修改角色 修改权限属性目录权限缺省权限 基本指令 sudo 1. sudo是对指令的短暂提权的 2. 比如安装软件&#xff0c;安装到系统中&#xff0c;需要管理员root权限&#xff0c;这些命令其实只安装了一份&#xff0c;普通用户和超级用…
阅读更多...
【Spring】Spring配置文件

【Spring】Spring配置文件

目录 ​什么是配置文件&#xff1f; 配置文件的作用 SpringBoot配置文件 配置文件格式 配置文件的优先级 properties配置文件说明 properties基本语法 读取配置文件 properties缺点 yml配置文件说明 yml基本语法 使用yml连接数据库 yml配置不同数据类型及null 注意…
阅读更多...
网络安全 linux学习计划 linux网络安全精要

网络安全 linux学习计划 linux网络安全精要

&#x1f345; 点击文末小卡片 &#xff0c;免费获取网络安全全套资料&#xff0c;资料在手&#xff0c;涨薪更快 2.使用命令行 文件系统层次标准&#xff08;FHS&#xff09;是一个文件和目录在Unix和Linux操作系统上面应该如何存储的定义。 /bin 重要的二进制可执行程序/bo…
阅读更多...
μEMU部署测试（论文复现）

μEMU部署测试（论文复现）

1. 论文参考&#xff08;先敬大佬&#xff09; Zhou W, Guan L, Liu P, et al. Automatic firmware emulation through invalidity-guided knowledge inference[C]//30th USENIX Security Symposium (USENIX Security 21). 2021: 2007-2024. Automatic Firmware Emulation th…
阅读更多...
最新文章

Copyright @ 2022~2023