從 JSON 格式 COPY - Amazon Redshift

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

從 JSON 格式 COPY

JSON 資料結構由一組物件或陣列組成。JSON對象開頭和結尾是大括號,並且包含一組無順序的名稱/值對。每一個名稱和值以冒號分隔,而配對以逗號分隔。名稱是以雙引號括住的字串。引號字元必須是簡單引號 (0x22),而不是斜向或「智慧型」引號。

JSON 陣列 的開頭和結尾是方括號,且包含一組已排序的值 (以逗號分隔)。值可以是以雙引號括住的字串、數字、布林值 true 或 false、null、JSON 物件或陣列。

JSON 物件和陣列可以是巢狀,以支援階層資料結構。下列範例顯示具有兩個有效物件的 JSON 資料結構。

{ "id": 1006410, "title": "Amazon Redshift Database Developer Guide" } { "id": 100540, "name": "Amazon Simple Storage Service User Guide" }

下面以兩個 JSON 陣列顯示同樣的資料。

[ 1006410, "Amazon Redshift Database Developer Guide" ] [ 100540, "Amazon Simple Storage Service User Guide" ]

JSON 的複製選項

將 COPY 與 JSON 格式數據一起使用時,可以指定以下選項:

  • 'auto' — COPY 自動從 JSON 文件加載字段。

  • 'auto ignorecase'— COPY 自動從 JSON 文件加載字段,同時忽略字段名稱的大小寫。

  • s3://jsonpaths_file— COPY 使用 JSONPath 檔案來解析 JSON 源資料。JSONPaths 檔案是包含單一 JSON 物件的文字檔案,此物件的名稱為 "jsonpaths",並與 JSONPath 表達式陣列搭配成對。如果名稱是 "jsonpaths" 以外的任何字串,COPY 會使用 'auto' 引數,而不使用 JSONPaths 檔案。

有關演示如何使用'auto''auto ignorecase'或 JSONPaths 檔案,並使用 JSON 物件或陣列,請參從 JSON 複製的範例

JSONPath 選項

在 Amazon Redshift COPY 語法中,JSONPath 表達式會使用方括號或點符號來指向 JSON 階層資料結構中的單一名稱元素。Amazon Redshift 不支援可能形成不明確路徑或多個名稱元素的任何 JSONPath 元素,例如萬用字元或篩選表達式。因此,Amazon Redshift 無法分析複雜、多層級的資料結構。

以下是 JSONPaths 檔案的範例,其中的 JSONPath 表達式使用方括號標記法。貨幣符號 ($) 代表根層級結構。

{ "jsonpaths": [ "$['id']", "$['store']['book']['title']", "$['location'][0]" ] }

在上述範例中,$['location'][0] 參考陣列的第一個元素。JSON 採用以零為基礎的陣列索引。陣列索引必須是正整數 (大於或等於零)。

下列範例使用點標記法來示範前一個 JSONPaths 檔案。

{ "jsonpaths": [ "$.id", "$.store.book.title", "$.location[0]" ] }

您不能混用方括號標記法和點標記法jsonpaths陣列。方括號標記法和點標記法中都可以使用方括號來參考陣列元素。

使用點標記法時,JSONPath 表達式不能包含下列字元:

  • 一般單引號 ( ' )

  • 句點,或點 ( . )

  • 方括號 ( [ ] ),除非用來參考陣列元素

如果 JSONPath 表達式所參考的名稱-值對中的值是物件或資組,則會將整個物件或陣列載入為字串,包括大括號或方括號。例如,假設 JSON 資料包含下列物件。

{ "id": 0, "guid": "84512477-fa49-456b-b407-581d0d851c3c", "isActive": true, "tags": [ "nisi", "culpa", "ad", "amet", "voluptate", "reprehenderit", "veniam" ], "friends": [ { "id": 0, "name": "Martha Rivera" }, { "id": 1, "name": "Renaldo" } ] }

JSONPath 表達式 $['tags'] 會傳回下列值。

"["nisi","culpa","ad","amet","voluptate","reprehenderit","veniam"]"

JSONPath 表達式 $['friends'][1] 會傳回下列值。

"{"id": 1,"name": "Renaldo"}"

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

關於如何使用 'auto' 引數或 JSONPaths 檔案及使用 JSON 物件或陣列來載入資料的範例,請參閱從 JSON 複製的範例

如需如何複製多個 JSON 檔案的相關資訊,請參使用資訊清單指定資料檔案

JSON 中的逸出字元

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

例如,假設您在 escape.json 儲存貯體的一個名為 s3://mybucket/json/ 檔案中有下列 JSON。

{ "backslash": "This is a backslash: \\", "newline": "This sentence\n is on two lines.", "tab": "This sentence \t contains a tab." }

請執行下列命令來建立 ESCAPES 資料表並載入 JSON。

create table escapes (backslash varchar(25), newline varchar(35), tab varchar(35)); copy escapes from 's3://mybucket/json/escape.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format as json 'auto';

查詢 ESCAPES 資料表來檢視結果。

select * from escapes; backslash | newline | tab ------------------------+-------------------+---------------------------------- This is a backslash: \ | This sentence | This sentence contains a tab. : is on two lines. (1 row)

遺失數值精確度

從 JSON 格式的資料檔案中將數字載入已定義為數值資料類型的欄時,可能會遺失精確度。電腦系統中無法精確表示某些浮點值。因此,從 JSON 檔案複製的資料可能不會如預期般四捨五入。為了避免遺失精確度,建議您使用下列其中一個替代方法:

  • 以字串來表示數字,用雙引號括住值。

  • 使用 ROUNDEC 將數字四捨五入而非截斷。

  • 使用 CSV、字元分隔或固定寬度的文字檔案,而不要使用 JSON 或 Avro 檔案。