資料格式參數 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

資料格式參數

根據預設,COPY 命令會預期來源資料是字元分隔的 UTF-8 文字。預設分隔符號是縱線字元 ( | )。如果來源資料是其他格式,請使用下列參數來指定資料格式:

從 Amazon S3 時,除了標準資料格式,COPY 還支援下列單欄資料格式:

支援從單欄格式進行 COPY,但有某些限制。如需詳細資訊,請參閱 從單欄資料格式 COPY

資料格式參數

FORMAT [AS]

(選用) 識別資料格式關鍵字。FORMAT 引數描述如下。

CSV [ QUOTE [AS] 'quote_character' ]

在輸入資料中啟用 CSV 格式。若要自動逸出分隔符號、新行字元及換行字元,請以 QUOTE 參數指定的字元來括住欄位。預設引號字元為雙引號 (")。在欄位內使用引號字元時,請多加一個引號字元來逸出此字元。例如,如果引號字元是雙引號,若要插入字串A "quoted" word輸入檔案應該包含字串"A ""quoted"" word"。使用 CSV 參數時,預設分隔字元為逗號 ( , )。您可以使用 DELIMITER 參數來指定不同的分隔字元。

以引號括住欄位時,分隔字元和引號字元之間的空格會被忽略。如果分隔字元是空格字元 (例如 Tab 字元),則不會將分隔字元視為空格。

CSV 不能與 FIXEDWIDTH、REMOVEQUOTES 或 ESCAPE 一起使用。

QUOTE [AS] 'quote_character'

選用。指定字元做為使用 CSV 參數時的引號字元。預設值為雙引號 ( " )。如果使用 QUOTE 參數來定義雙引號以外的引號字元,則不需要在欄位內逸出雙引號。QUOTE 參數只能與 CSV 參數一起使用。AS 關鍵字為選用。

DELIMITER [AS] ['delimiter_char']

指定單一 ASCII 字元,用以分隔輸入檔案中的欄位,例如縱線字元 ( | )、逗號 ( , ) 或 Tab 字元 ( \t )。支援非列印 ASCII 字元。ASCII 字元也可以使用八進位表示,格式為 '\ ddd',其中' d '是八進位數字 (0—7)。預設分隔字元為縱線字元 ( | ),除非使用 CSV 參數 (在此情況下,預設分隔字元為逗號 ( , ))。AS 關鍵字為選用。DELIMITER 不能與 FIXEDWIDTH 一起使用。

FIXEDWIDTH 'fixedwidth_spec'

從每個欄寬是固定長度 (而不是以分隔字元隔開的欄) 的檔案載入資料。fixedwidth_spec 是字串,指定使用者定義的欄標籤和欄寬。欄標籤可以是文字字串或整數 (視使用者的選擇而定)。欄標籤與欄名稱無關。標籤/寬度配對的順序必須完全符合資料表欄的順序。FIXEDWIDTH 不能與 CSV 或 DELIMITER 一起使用。在 Amazon Redshift 中,CHAR 和 VARCHAR 欄的長度以字節表示,因此在準備要載入的檔案時,請確保您指定的欄寬可容納多位元組字元的二進位長度。如需詳細資訊,請參閱 字元類型

fixedwidth_spec 的格式如下所示:

'colLabel1:colWidth1,colLabel:colWidth2, ...'
塑形文件 [簡化 [自動] ['容忍']]

在輸入資料中啟用 SHAPEFILE 格式。默認情況下,shapefile 的第一欄是GEOMETRY或者IDENTITY列出來。所有後續列都遵循 shapefile 中指定的順序。

您不能使用 Shapefile 與固定寬度、刪除報價或 ESCAPE 一起使用。

使用GEOGRAPHY物件金有COPY FROM SHAPEFILE,首先攝入到GEOMETRY列,然後將對象轉換為GEOGRAPHY物件。

簡化 [容忍]

(可選)使用拉默-道格拉斯-皮克算法和給定容差在攝入過程中簡化所有幾何形狀。

簡化自動 [容忍]

(可選)僅簡化大於最大幾何尺寸的幾何。此簡化使用拉默-道格拉斯-佩克算法和自動計算的容差(如果不超過指定容差)。此算法計算在指定容差範圍內存儲對象的大小。所以此容忍值是選用的。

有關加載 shapefile 的示例,請參閲將形狀文件加載到 Amazon Redshift

AVRO [AS] 'avro_option'

指定來源資料是 Avro 格式。

從這些服務和通訊協定進行 COPY 時,支援 Avro 格式:

  • Simple Storage Service (Amazon S3)

  • Amazon EMR

  • 遠端主機 (SSH)

從 DynamoDB 進行 COPY 時不支援 Avro。

Avro 是資料序列化通訊協定。Avro 來源檔案包含結構描述來定義資料的結構。Avro 結構描述類型必須是 record。COPY 接受使用預設未壓縮解碼器和deflatesnappy壓縮解碼器。如需 Avro 的詳細資訊,請前往 Apache Avro

avro_option 的有效值如下所示:

  • 'auto'

  • 'auto ignorecase'

  • 's3://jsonpaths_file'

預設為 'auto'

COPY 會自動將 Avro 來源資料中的資料元素映射至目標資料表的欄。為此,請將 Avro 結構描述中的欄位名稱與目標資料表中的欄名稱進行比對。比對區分大小寫之分'auto'並且不區分大小寫之分'auto ignorecase'

Amazon Redshift 資料表中的欄名稱一律為小寫,因此當您使用'auto'選項時,匹配的欄位名稱也必須是小寫。如果字段名稱不是全部小寫,則可以使用'auto ignorecase'選項。使用預設值'auto'參數時,COPY 僅識別第一級字段,或外部字段,在結構中。

要顯式將列名映射到 Avro 字段名稱,您可以使用JSONPaths 檔案

根據預設,COPY 會嘗試將目標資料表中的所有欄與 Avro 欄位名稱進行比對。若要載入欄子集,您可以選擇性指定欄清單。如果從欄清單中省略目標資料表的某一欄,COPY 會載入目標欄的DEFAULT表達式。如果目標欄沒有預設值,COPY 會嘗試載入 NULL。如果某一欄出現在欄清單中,且 COPY 在 Avro 資料中找不到相符欄位,COPY 會嘗試將 NULL 載入此欄。

如果 COPY 嘗試將 NULL 指派給定義為 NOT NULL 的欄,COPY 命令會失敗。

Avro 結構描述

Avro 來源資料檔案包含結構描述來定義資料的結構。COPY 會讀取 Avro 來源資料檔案中的結構描述,以便將資料元素映射至目標資料表欄。下列範例顯示 Avro 結構描述。

{ "name": "person", "type": "record", "fields": [ {"name": "id", "type": "int"}, {"name": "guid", "type": "string"}, {"name": "name", "type": "string"}, {"name": "address", "type": "string"}] }

Avro 結構描述定義方式是 JSON 格式。頂層 JSON 物件包含三個名稱/值對,按鍵"name""type",以及"fields"

內含物件陣列的 "fields" 金鑰對會定義資料結構中每個欄位的名稱和資料類型。根據預設,COPY 會自動將欄位名稱與欄名稱進行比對。欄名稱始終是小寫,因此相符的欄位名稱也必須是小寫,除非您指定‘auto ignorecase’選項。不符合欄名稱的任何欄位名稱會被忽略。順序並不重要。在上述範例中,COPY 映射至欄名稱 idguidnameaddress

使用預設的 'auto' 引數時,COPY 只會將第一層物件與欄進行比對。若要映射至結構描述中更深的層級,或欄位名稱和欄名稱不相符,請使用 JSONPaths 檔案來定義映射。如需詳細資訊,請參閱 JSONPaths 檔案

如果與金鑰相關聯的值是複合 Avro 資料類型 (例如位元組、陣列、記錄、映射或連結),COPY 會以字串形式載入此值。此處,字串是資料的 JSON 表示法。COPY 會以字串形式載入 Avro 列舉資料類型,其中內容是類型的名稱。如需範例,請參閱 從 JSON 格式 COPY

Avro 檔案標頭 (包括結構描述和檔案中繼資料) 的大小上限為 1 MB。 

單一 Avro 資料區塊的大小上限為 4 MB。這不同於資料列大小上限。如果超過單一 Avro 資料區塊的大小上限,即使產生的資料列小於 4 MB 資料列大小限制,COPY 命令也會失敗。

在計算列大小時,Amazon Redshift 會在內部將管道字元 (|) 計算兩次。如果輸入資料包含非常多的縱線字元,即使資料區塊小於 4 MB,資料列大小仍可能超過 4 MB。

JSON [AS] 'json_option'

來源資料是 JSON 格式。

從這些服務和通訊協定 COPY 時,支援 JSON 格式:

  • Simple Storage Service (Amazon S3)

  • 從 Amazon EMR 中複製

  • 從 SSH COPY

從 DynamoDB 進行 COPY 時不支援 JSON。

json_option 的有效值如下所示:

  • 'auto'

  • 'auto ignorecase'

  • 's3://jsonpaths_file'

  • 'noshred'

預設為 'auto'。在加載 JSON 文檔時,Amazon Redshift 不會將 JSON 結構的屬性分解為多列。

根據預設,COPY 會嘗試將目標資料表中的所有欄與 JSON 欄位名稱金鑰配對。若要載入欄子集,您可以選擇性指定欄清單。如果 JSON 欄位名稱金鑰不是全部小寫,您可以使用'auto ignorecase'選項或JSONPaths 檔案,明確地將欄名稱映射至 JSON 欄位名稱金鑰。

如果從欄清單中省略目標資料表的某一欄,COPY 會載入目標欄的 DEFAULT 表達式。如果目標欄沒有預設值,COPY 會嘗試載入 NULL。如果某一欄出現在欄清單中,且 COPY 在 JSON 資料中找不到相符欄位,COPY 會嘗試將 NULL 載入此欄。

如果 COPY 嘗試將 NULL 指派給定義為 NOT NULL 的欄,COPY 命令會失敗。

COPY 會將 JSON 源資料中的資料元素映射至目標資料表中的欄。它通過匹配物件金鑰(或名稱) 設置為目標資料表中的欄名稱。

請參下列有關JSON 選項值:

'auto'

使用此選項時,匹配區分大小寫。Amazon Redshift 資料表中的欄名稱一律為小寫,因此當您使用'auto'選項時,匹配的 JSON 欄位名稱也必須是小寫。

'自動忽略'

使用此選項時,匹配時不區分大小寫。Amazon Redshift 資料表中的欄名稱一律為小寫,因此當您使用'auto ignorecase'選項,相應的 JSON 字段名稱可以是小寫、大寫或混合大小寫。

's3://jsonpaths_file'

如果使用此選項,COPY 會使用指名的 JSONPaths 檔案,將 JSON 來源資料中的資料元素映射至目標資料表的欄。所以此s3://jsonpaths_file引數必須是明確參考單一檔案的 Amazon S3 物件金鑰。範例為's3://mybucket/jsonpaths.txt'。參數不能是金 key prefix。如需使用 JSONPaths 檔案的詳細資訊,請參閱 JSONPaths 檔案

在某些情況下,jsonpaths_file具有相同的前綴與copy_from_s3_objectpath中的資料檔案。如果是這樣,COPY 將 JSONPath 文件作為數據文件讀取並返回錯誤。例如,假設您的資料檔案使用s3://mybucket/my_data.json,並且您的 JSON 路徑文件是s3://mybucket/my_data.jsonpaths。在這種情況下,COPY 會嘗試加載my_data.jsonpaths作為資料檔案。

'沒有'

使用此選項,Amazon Redshift 不會在加載 JSON 文檔時將 JSON 結構的屬性分解為多列。

JSON 資料檔案

JSON 資料檔案包含一組物件或陣列。COPY 會將每個 JSON 物件或陣列載入目標資料表中的每一列。對應到一列的每個物件或陣列必須是獨立的根層級結構;亦即,不能是另一個 JSON 結構的成員。

JSON對象以大括號 ({}) 開頭和結尾,且包含一組未排序的名稱-值對。每一對名稱和值以冒號分隔,而配對以逗號分隔。在預設情況下,物件金鑰(或名稱) 必須符合資料表中相應欄的名稱。Amazon Redshift 資料表中的欄名稱一律為小寫,因此 JSON 欄位名稱金鑰也必須是小寫。如果欄名稱與 JSON 金鑰不符,請使用 JSONPaths 檔案 明確地將欄映射至金鑰。

JSON 物件中的順序並不重要。不符合欄名稱的任何名稱會被忽略。以下顯示簡易 JSON 物件的結構。

{ "column1": "value1", "column2": value2, "notacolumn" : "ignore this value" }

JSON 陣列 的開頭和結尾是方括號 ( [ ] ),且包含一組已排序的值 (以逗號分隔)。如果資料檔案使用陣列,您必須指定 JSONPaths 檔案將值與欄配對。以下顯示簡易 JSON 陣列的結構。

["value1", value2]

JSON 必須格式正確。例如,不能以逗號或其他任何字元 (空格除外) 來分隔物件或陣列。必須以雙引號字元括住字串。引號字元必須是簡單引號 (0x22),而不是斜向或「智慧型」引號。

單一 JSON 物件或陣列的大小上限 (包括大括號或方括號) 為 4 MB。這不同於資料列大小上限。如果超過單一 JSON 物件或陣列的大小上限,即使產生的資料列小於 4 MB 資料列大小限制,COPY 命令也會失敗。

在計算資料行大小時,Amazon Redshift 會在內部將管道字元 (|) 計算兩次。如果輸入資料包含非常多的縱線字元,即使物件小於 4 MB,資料列大小仍可能超過 4 MB。

COPY 會載入 \n 做為新行字元,也會載入 \t 做為 Tab 字元。若要載入反斜線,請加上反斜線 ( \\ ) 來逸出。

COPY 會在指定的 JSON 來源中搜尋格式正確、有效的 JSON 物件或陣列。如果 COPY 在查找可用的 JSON 結構之前遇到任何非空格字符,或者在有效的 JSON 對象或數組之間遇到任何非空格字符,COPY 會為每個實例返回錯誤。這些錯誤都計入 MAXERROR 錯誤計數內。當錯誤計數等於或超過 MAXERROR 時,COPY 會失敗。

對於每個錯誤,Amazon Redshift 會在 STL_LOAD_ERRORS 系統資料表中記錄一列。LINE_NUMBER 欄會記錄造成錯誤之 JSON 物件的最後一行。

如果指定 IGNOREHEADER,COPY 會在 JSON 資料中忽略指定的行數。IGNOREHEADER 計算時一律計數 JSON 資料中的新行字元。

根據預設,COPY 會將空字串載入為空欄位。如果指定 EMPTYASNULL,COPY 會將 CHAR 和 VARCHAR 欄位的空字串載入為 NULL。一律會將其他資料類型 (例如 INT) 的空字串載入為 NULL。

JSON 不支援下列選項:

  • CSV

  • DELIMITER

  • ESCAPE

  • FILLRECORD

  • FIXEDWIDTH

  • IGNOREBLANKLINES

  • NULL AS

  • READRATIO

  • REMOVEQUOTES

如需詳細資訊,請參閱 從 JSON 格式 COPY。如需 JSON 資料結構的詳細資訊,請前往 www.json.org

JSONPaths 檔案

如果您從 JSON 格式或 Avro 來源資料載入,根據預設,COPY 會將來源資料中的第一層資料元素映射至目標資料表的欄。此操作方法是將名稱-值對中的每個名稱 (或物件金鑰) 與目標資料表中的欄名稱配對。

如果欄名稱和物件金鑰不符,或若要映射至資料階層中更深的層級,您可以使用 JSONPaths 檔案,以明確地將 JSON 或 Avro 資料元素映射至欄。JSONPaths 檔案會比對目標資料表或欄清單中的欄順序,以便將 JSON 資料元素映射至欄。

JSONPaths 檔案只能包含單一 JSON 物件 (不是陣列)。JSON 物件是名稱-值對。所以此物件金鑰(名稱-值對中的名稱)必須是"jsonpaths"。所以此在名稱-值對中,是JSONPath 表達式。每個 JSONPath 表達式都參考 JSON 資料階層或 Avro 結構描述中的單一元素,類似於 XPath 表達式參考 XML 文件中元素的方式。如需詳細資訊,請參閱 JSONPath 表達式

若要使用 JSONPaths 檔案,請將 JSON 或 AVRO 關鍵字新增至 COPY 命令。使用下列格式指定 JSONPaths 檔案的 S3 儲存貯體名稱和物件路徑。

COPY tablename FROM 'data_source' CREDENTIALS 'credentials-args' FORMAT AS { AVRO | JSON } 's3://jsonpaths_file';

所以此s3://jsonpaths_file值必須是明確參考單一檔案 (例如) 的 Amazon S3 物件金鑰,例如's3://mybucket/jsonpaths.txt'。不能成為金 key prefix。

在某些情況下,如果您從 Amazon S3 加載jsonpaths_file具有相同的前綴與copy_from_s3_objectpath中的資料檔案。如果是這樣,COPY 將 JSONPath 文件作為數據文件讀取並返回錯誤。例如,假設您的資料檔案使用s3://mybucket/my_data.json,並且您的 JSON 路徑文件是s3://mybucket/my_data.jsonpaths。在這種情況下,COPY 會嘗試加載my_data.jsonpaths作為資料檔案。

如果金鑰名稱是 "jsonpaths" 以外的任何字串,COPY 命令不會傳回錯誤,但會忽略 jsonpaths_file,並改用 'auto' 引數。

如果發生下列任何情形,COPY 命令會失敗:

  • JSON 格式不正確。

  • 有多個 JSON 物件。

  • 物件外面存在空格以外的任何字元。

  • 陣列元素是空字串或不是字串。

MAXERROR 不會套用至 JSONPaths 檔案。

即使指定 ENCRYPTED 選項,也不可對 JSONPaths 檔案進行加密。

如需詳細資訊,請參閱 從 JSON 格式 COPY

JSONPath 表達式

JSONPaths 檔案使用 JSONPath 表達式將資料欄位映射至目標欄。每個 JSONPath 表達式都對應至 Amazon Redshift 目標資料表中的一欄。JSONPath 陣列元素的順序必須符合目標資料表或欄清單 (如果使用欄清單) 中的欄順序。

如上所示,欄位名稱和值都需要雙引號字元。 引號字元必須是簡單引號 (0x22),而不是斜向或「智慧型」引號。

如果在 JSON 資料中找不到 JSONPath 表達式所參考的物件元素,COPY 會嘗試載入 NULL 值。如果參考的物件格式不正確,COPY 會傳回載入錯誤。

如果在 JSON 或 Avro 資料中找不到 JSONPath 表達式所參考的陣列元素,COPY 會失敗並傳回下列錯誤:Invalid JSONPath format: Not an array or index out of range.請從 JSONPaths 中移除任何不存在於來源資料中的陣列元素,並確認來源資料中的陣列格式正確。 

JSONPath 表達式可以使用方括號標記法或點標記法,但您不能混用標記法。下列範例示範使用方括號標記法的 JSONPath 表達式。

{ "jsonpaths": [ "$['venuename']", "$['venuecity']", "$['venuestate']", "$['venueseats']" ] }

下列範例示範使用點標記法的 JSONPath 表達式。

{ "jsonpaths": [ "$.venuename", "$.venuecity", "$.venuestate", "$.venueseats" ] }

在 Amazon Redshift COPY 語法方面,JSONPath 表達式必須指定明確路徑來指向 JSON 或 Avro 階層資料結構中的單一名稱元素。Amazon Redshift 不支援可能形成不明確路徑或多個名稱元素的任何 JSONPath 元素,例如萬用字元或篩選表達式。

如需詳細資訊,請參閱 從 JSON 格式 COPY

使用 JSONPaths 處理 Avro 資料

下列範例示範多個層級的 Avro 結構描述。

{ "name": "person", "type": "record", "fields": [ {"name": "id", "type": "int"}, {"name": "guid", "type": "string"}, {"name": "isActive", "type": "boolean"}, {"name": "age", "type": "int"}, {"name": "name", "type": "string"}, {"name": "address", "type": "string"}, {"name": "latitude", "type": "double"}, {"name": "longitude", "type": "double"}, { "name": "tags", "type": { "type" : "array", "name" : "inner_tags", "items" : "string" } }, { "name": "friends", "type": { "type" : "array", "name" : "inner_friends", "items" : { "name" : "friends_record", "type" : "record", "fields" : [ {"name" : "id", "type" : "int"}, {"name" : "name", "type" : "string"} ] } } }, {"name": "randomArrayItem", "type": "string"} ] }

下列範例顯示一個 JSONPath 檔案使用 AvroPath 表達式引用之前的結構描述。

{ "jsonpaths": [ "$.id", "$.guid", "$.address", "$.friends[0].id" ] }

JSONPaths 範例包含下列元素:

jsonpaths

JSON 物件的名稱,其中包含 AvroPath 表達式。

[ … ]

方括號括住含有路徑元素的 JSON 陣列。

$

貨幣符號表示 Avro 結構描述中的根元素,即 "fields" 陣列。

"$.id",

的目標 AvroPath 表達式。在此例子中,目標是 "fields" 陣列中名稱為 "id" 的元素。表達式以逗號分隔。

"$.friends[0].id"

方括號表示陣列索引。JSONPath 表達式採用以零為基礎的索引,所以此表達式是參考 "friends" 陣列中名稱為 "id" 的第一個元素。

Avro 結構描述語法需要使用內部欄位來定義記錄和陣列資料類型的結構。內部欄位會被 AvroPath 表達式。例如,欄位 "friends" 定義名為 "inner_friends" 的陣列,此陣列接著定義名為 "friends_record" 的記錄。所以此 AvroPath 表達式來引用字段"id"可以忽略額外欄位,以直接參考目標欄位。如下所示 AvroPath 表達式參考兩個屬於"friends"陣列。

"$.friends[0].id" "$.friends[0].name"

單欄資料格式參數

從 Amazon S3 時,除了標準資料格式,COPY 還支援下列單欄資料格式。支援從單欄格式 COPY,但有某些限制。如需詳細資訊,請參閱 從單欄資料格式 COPY

ORC

從採用「最佳化資料列單欄式 (ORC)」檔案格式的檔案載入資料。

PARQUET

從採用 Parquet 檔案格式的檔案載入資料。

RCFILE

從採用 RCFile 格式的檔案載入資料。

SEQUENCEFILE

從使用 SequenceFile 檔案格式。