Greenplum write out with copy command and read with external table

筆者: 歐立威 Hank


Greenplum 可以透過 Postgresql 相同的 copy 指令來匯出所需的資料文字檔，
又或是資料文字檔式從其他的系統上(EX: Oracle)匯出得來，
再介由 Greenplum External Table 的方式匯入資料至 Greenplum，
這樣的操作看似簡單平常，
卻是常常遇到資料檔中有不符合預期的字元而導致資料匯入失敗。

這邊我整理了一些最常遇見問題及大家可能搞混的部分做說明，
便可大大減少在資料匯出匯入時所遇到的問題，
而不需要每次都花時間在解這些煩人的字元剖析問題。



狀況一:

copy 指令的功能與語法相當直觀且單純，如下:
copy (select * from test) to '/tmp/test.txt';

若未給參數，則預設為 Text Format 且 delimiter 為 \t (Tab鍵)，
但這樣會遇到一個問題，如果有欄位裡的值也有 \t，
則會導致在匯入資料時有剖析錯誤，
所以筆者這邊建議大家資料匯出時必須特別選擇 delimiter，要是資料裡絕對不會出現字元。



狀況二:

通常我們匯出資料的源頭，我們根本不了解資料到底有哪些字元，
常常是花了數小時的資料匯出與匯入才會發現有不預期的字元，
所以我們還有一個方法能夠避免掉萬一有 delimiter 的字元出現在資料內的情形，
那就是 quote 這個參數，
筆者這邊習慣直接使用以下語法:
copy (select * from test) to '/tmp/test.txt' CSV;

CSV的格式預設便直接使用 delimiter ',' (逗號)及 quote '"' (雙引號)，
所以若資料內有逗號字元時，便可因為有雙引號的 quote 而剖析通過。



狀況三:

倘若有資料內容還是有與 quote 相同的字元呢!?
1. 可以把 quote 改為其他字元如: $, ^ 等不常見字元...
2. 若資料內容甚麼奇怪的字元都有，筆者看來這欄位的資料也是些非結構化的資料，
不差這些字元也是能看出欄位意義，所以可使用取代的指令都把它換掉吧
(:%s/word1/word2/g)
3. copy 指令有個參數是強迫在特定欄位加上 quote 的:
copy (select * from test) to '/tmp/test.txt' CSV FORCE QUOTE a, b, c;



補充:

在CSV格式下，null值和空字串是要分清楚的，因為這兩種值在DB裡是有很大的區別的:

1. 有個row有個三null欄位匯出後會長底下這樣:
,,

2. 有個row有三個空字串欄位匯出後會長底下這樣:
"","",""

請特別注意這兩種狀況，因為DB中很多的型態是可以接受null，
因為null只是代表還沒有值，
可是若是有值時則必須遵照該型態的格式，例如 timestamp: YYYY-MM-DD hh:mm:ss，
若此時的值是空字串則完全不符合格式，
會被拒絕寫入該欄位的。