当前位置: 首页 > news >正文

迁移Oracle SH 示例 schema 到 PostgreSQL

接着上一篇文章:迁移Oracle HR 示例 schema 到 PostgreSQL中,本文做Oracle SH(Sales History)示例 schema的迁移,SH schema比HR schema更大更复杂,本次迁移的重点是:

  • 分区表
  • 外部数据加载

使用的是Oracle 19c的示例 schema,下载命令如下:

git clone --depth 1 --branch v19c https://github.com/oracle-samples/db-sample-schemas.git

SH schema的安装脚本为sales_history/sh_main.sql,其主要构成按序为(以下省略.sql后缀):

  1. csh_v3:创建表
  2. lsh_v3:加载数据到表
  3. psh_v3:加载后的操作

我们也按以上顺序来迁移。依次形成了以下脚本:

  • csh_v3.sql:创建表
  • lsh_v3.sql:加载数据
  • cons_v3.sql:创建约束
  • idx_v3.sql:创建索引
  • views_v3.sql:创建视图,物化视图
  • cmnts_v3.sql:创建注释

csh_v3:创建表

这部分比较容易,分区的语法对应上就好,另外建立约束的部分放在数据加载后来做。

还有PG并没有Oracle的OLTP表压缩功能。

Oracle的分区语法丰富,普适性较强。例如范围分区支持VALUES LESS THAN。PG只支持FOR VALUES FROM … TO。不过问题不大。

看一个sales表的示例。

Oracle语法:

CREATE TABLE sales (prod_id             NUMBER          NOT NULL,cust_id             NUMBER          NOT NULL,time_id             DATE            NOT NULL,channel_id          NUMBER          NOT NULL,promo_id            NUMBER          NOT NULL,quantity_sold       NUMBER(10,2)    NOT NULL,amount_sold         NUMBER(10,2)    NOT NULL)PARTITION BY RANGE (time_id)( partition sales_1995 VALUES LESS THAN(TO_DATE('1996-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,partition sales_1996 VALUES LESS THAN(TO_DATE('1997-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
...

PostgreSQL的语法:

CREATE TABLE sales (prod_id             NUMERIC                 NOT NULL,cust_id             NUMERIC                 NOT NULL,time_id             DATE            NOT NULL,channel_id          NUMERIC         NOT NULL,promo_id            NUMERIC                 NOT NULL,quantity_sold       NUMERIC(10,2)   NOT NULL,amount_sold         NUMERIC(10,2)   NOT NULL)PARTITION BY RANGE (time_id);CREATE TABLE sales_1995 PARTITION OF salesFOR VALUES FROM (MINVALUE) TO ('1996-01-01');CREATE TABLE sales_1996 PARTITION OF salesFOR VALUES FROM ('1996-01-01') TO ('1997-01-01');...

lsh_v3:加载数据到表

这部分消耗时间最多,主要在数据文件的格式转换。Oracle是用SQL Loader,PostgreSQL则用COPY。

💡 先厘清一个概念。对于DATE数据类型,PG的精度是到天,而Oracle的精度是到秒。

本部分处理的主要问题:

  • 1:Oracle示例表中用DATE定义的列,实际只需要到天就可以了,但数据文件中的值却是1998-12-27-00-00-00,而非1998-12-27。所以我们需要去掉尾部的00-00-00
  • 2:多余的分隔符。按说3个字段只需要2个分隔符,但Oracle也支持尾部再多放一个分隔符。而PG不认,我们只需要去掉行末的分隔符即可。
  • 3:数据文件的字段比表的字段多
  • 4:建立外部表

问题1的处理较简单,例如对于times表:

\copy times from program 'sed "s/-00-00-00//g" time_v3.dat'WITH (FORMAT csv,DELIMITER '|'
);

用元命令而非SQL命令的原因在于要使用相对路径。

问题2的处理也是用sed,例如对于countries表:

\copy countries from program 'sed "s/|$//g" coun_v3.dat'WITH (FORMAT csv,DELIMITER '|'
);

有些表同时出现了问题1和2,例如customers表和products表:

\copy customers from program 'sed "s/-00-00-00//g;s/|$//g" cust1v3.dat'WITH (FORMAT csv,DELIMITER '|'
);

问题3的处理稍微不同,出于性能考虑,预处理生成了中间文件,而非之前的即时处理。例如对于sales表,他只有7个字段,而数据文件有9个字段。

即时处理如下,但结果1小时后也没出来,所以放弃了:

\copy sales from program 'sed "s/-*[0-9]\+\(\.[0-9]\+\)\?|$//g"|sed "s/|-*[0-9]\+\(\.[0-9]\+\)\?|$//g" sale1v3.dat'WITH (FORMAT csv,DELIMITER '|'
);

预处理方式如下:

sampledb=> \timing
Timing is on.sampledb=> \! time sed "s/-*[0-9]\+\(\.[0-9]\+\)\?|$//g" sale1v3.dat > 1real    0m34.503s
user    0m33.504s
sys     0m0.271s
sampledb=> \! time sed "s/|-*[0-9]\+\(\.[0-9]\+\)\?|$//g" 1 > 2real    0m19.119s
user    0m18.517s
sys     0m0.226s
sampledb=> \! mv 2 sale1v3_pg.dat
sampledb=> \copy sales from sale1v3_pg.datWITH (FORMAT csv,DELIMITER '|'
);
COPY 916039
Time: 9422.693 ms (00:09.423)

可以看到,预处理用了近54秒,导入用了9秒。

问题4的例子是costs表。他其实用到了之前9个字段的数据文件。

外部表的建立用了file_fdw扩展,这是PG原生的扩展,详见这里。

CREATE FOREIGN TABLE sales_transactions_ext
( PROD_ID               NUMERIC,CUST_ID               NUMERIC,TIME_ID               DATE,CHANNEL_ID    NUMERIC,PROMO_ID              NUMERIC,QUANTITY_SOLD   NUMERIC,AMOUNT_SOLD   NUMERIC(10,2),UNIT_COST     NUMERIC(10,2),UNIT_PRICE    NUMERIC(10,2)
) SERVER file_server
OPTIONS
(
format 'csv', filename 'sale1v3_fdw.dat', delimiter '|'
);

需要特别说明,我用的是相对路径,因此需要把数据文件拷贝到PG服务器可访问的目录,如$PGDATA。不过还是建议用绝对路径。

psh_v3:加载后的操作

我没有psh_v3.sql,而是用idx_v3.sql,views_v3.sql和cmnts_v3.sql对应。

最后

所有的脚本都在Github上了,下一篇我们迁Customer Orders 示例 schema。

http://www.lryc.cn/news/583242.html

相关文章:

  • 亚马逊广告进阶指南:长尾词应如何去挖掘
  • RapidRAW RAW 图像编辑器
  • 游戏开发学习记录
  • 码云创建分支
  • 分库分表之实战-sharding-JDBC绑定表配置实战
  • 掌握PDF转CAD技巧,提升工程设计效率
  • 模型内部进行特征提取时,除了“减法”之外,还有哪些技术
  • Android ttyS2无法打开该如何配置 + ttyS0和ttyS1可以
  • BEV感知算法:自动驾驶的“上帝视角“革命
  • c语言学习_函数递归2
  • 深度学习模型在C++平台的部署
  • Spring Boot微服务中集成gRPC实践经验分享
  • 1️⃣理解大语言模型
  • 百度文心一言开源ERNIE-4.5深度测评报告:技术架构解读与性能对比
  • Shell 脚本0基础教学(一)
  • 【计算机组成原理——知识点总结】-(总线与输入输出设备)-学习笔记总结-复习用
  • Energy-Based Transformers:实现通用系统2思维的新范式
  • HOOPS Communicator 2025.5.0版本更新速览:性能、测量与UI全面优化
  • C++入门基础篇(一)
  • 《【第五篇】图片处理自动化:让你的视觉内容更专业!:图片处理基础与批量裁剪》
  • Unity Demo-3DFarm详解-其二
  • 极简相册管理ios app Tech Support
  • 无人机报警器频段模块设计与运行要点
  • Excel 常用高级用法
  • 使用LLaMA-Factory微调Qwen2.5-VL-3B 的目标检测任务-使用LLaMA-Factory webui进行训练
  • 学习日志08 python
  • 基于svga+uniapp的微信小程序动画组件开发指南
  • 前端进阶之路-从传统前端到VUE-JS(第四期-VUE-JS页面布局与动态内容实现)(Element Plus方式)
  • IntelliJ IDEA 2025.1.3创建不了java8的项目
  • 【PTA数据结构 | C语言版】大整数相加运算