雲資料庫HybridDB for PostgreSQL支援多種資料匯入方法:
在資料匯入的過程中,經常因為存在特殊字元導致匯入失敗。本文介紹了預先處理匯入資料中的特殊字元的方法,從而消除特殊字元帶來的問題。
在上述匯入方法中,從MySQL匯入和從PostgreSQL匯入中使用的工具,會自動將特殊符號進行轉義和封裝,您可以直接使用而不需要過多的設定。這裡重點介紹在使用OSS高速並行匯入和通過COPY資料匯入處理程序中遇到特殊符號的處理方法。
OSS高速並行匯入
在資料匯入處理程序中,一般是將檔案的每行作為一個元組,通過在每行中規定分隔字元來分割每一列的資料。下文首先介紹分隔字元的使用方法和約束,然後介紹在每列中遇到特殊符號的處理方法。
分隔字元
在建立OSS外部表格文法中,您可以通過在FORMAT子句後面指定DELIMITER分隔字元,如下:
FORMAT 'TEXT' (DELIMITER ',')
- 如果
FORMAT 'TEXT'
,則DELIMITER
預設值為'\t'
。 - 如果
FORMAT 'CSV'
,則DELIMITER
預設值為','
。
您也可以自訂DELIMITER,但是建立外部表格文法中自訂的DELIMITER必須滿足以下約束:
- 必須是一個ASCII字元,不允許是漢字或者2個以及以上ASCII字元。
- 不支援
'\n'
和'\r'
。 - 支援除
'\n'
和'\r'
之外的其他逸出字元,使用時前面加E或者e。 - 支援前面不加E的逸出字元
'\t'
。 - 如果是TEXT模式,可以設定DELIMITER為OFF,支援單列外部表格。
為了能夠正常讀取資料,您提供的OSS檔案內容必須嚴格遵守設定的DELIMITER。
資料中的特殊符號
在資料匯入處理程序中,出現特殊符號的情境可以分為以下幾種:
-
列中存在和DELIMITER相同的字元。
-
如果您使用TEXT模式,則需要在每個DELIMITER字元前加ESCAPE符。ESCAPE符可以在建立外表時使用以下命令指定,預設值為反斜線(\)。
FORMAT 'TEXT' (ESCAPE '\' )
- 如果您使用的是CSV模式,則需要在每個DELIMITER字元前加雙引號(”)。
-
-
列中存在中文。OSS外表支援中文資料,但是為了保證顯示正確,您需要在建立外表時設定如下編碼格式:
ENCODING 'UTF8'
-
列中存在null。您可以設定null對應的匹配字元,在匯入資料時將對應的字元匹配為null。CSV模式下預設值為不帶引號的空值,TEXT模式下預設值為\N。以下命令將空格作為null的匹配字元,如果該列為空白格,則在使用OSS檔案匯入的資料中該列值為null。
FORMAT 'text' (null ' ' )
-
列中存在逸出字元。您可以在逸出字元前增加ESCAPE符。ESCAPE符在建立外表時指定,CSV模式預設值為雙引號(”),TEXT模式預設值為反斜線(\)。
-
您可以自訂ESCAPE為單個字元。例如,以下命令將ESCAPE設定為反斜線:
FORMAT 'csv' (ESCAPE '\' )
- 您也可以設定ESCAPE為OFF,避免所有字元被自動轉義。
-
-
列中存在單引號或者雙引號。
- 如果您使用TEXT模式,需要在單引號或者雙引號前面增加ESCAPE符,預設為反斜線(\)。
- 如果您使用CSV模式,需要在單引號或者雙引號前面增加ESCAPE符,預設為雙引號(”),同時在該列前後加雙引號(”),將整列括起來。
COPY資料匯入
您在使用\COPY語句匯入資料時,分隔字元的使用方法和OSS高速並行匯入時的使用方法一樣,而對資料中出現特殊符號的處理方法也和OSS高速並行匯入相類似。不同的是COPY語句和CREATE EXTERNAL TABLE
語句用法略有不同,COPY語句詳細用法見使用COPY命令匯入資料。