接著上一篇文章：遷移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后綴）：
- csh_v3：創建表
- lsh_v3：加載數據到表
- 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。
