如何使用 Amazon Redshift 中的 SUPER 資料類型來處理和查詢 JSON 資料？

簡短描述

使用 SUPER 資料類型將半結構化資料儲存在 Amazon Redshift 資料倉儲中。您可以使用 PartiQL 語言查詢 SUPER 資料類型。

您也可以使用 Amazon Redshift Spectrum 來支援半結構化資料。

解決方法

您可以使用 COPY 命令或帶有 JSON_PARSE 函數的 INSERT 命令，將 JSON 載入到 SUPER 資料中。載入大型資料時，最佳做法是使用 COPY 命令。

使用 COPY 命令

使用 COPY 命令從 Amazon Simple Storage Service (Amazon S3) 中的資料檔案載入 JSON。如需詳細資訊，請參閱將半結構化資料載入到 Amazon Redshift 中。

若要將 JSON 載入到單一 SUPER 資料欄中，請使用 noshred 選項。

若要將 JSON 載入到多個資料欄中，請使用自動選項，或指定 jsonpaths 檔案。當您使用自動選項時，COPY 會將頂層 JSON 屬性與資料欄名稱進行比對，並允許您將巢狀結構的值載入為 SUPER 值。以下範例說明如何使用自動選項。

JSON 資料檔範例：

{"r_regionkey":0,"r_name":"AFRICA","r_nations":[{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]}
 {"r_regionkey":1,"r_name":"AMERICA","r_nations":[{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]}
 {"r_regionkey":2,"r_name":"ASIA","r_nations":[{"n_nationkey":8,"n_name":"INDIA"}]}

COPY 命令範例：

# CREATE TABLE region_nations
 (
  r_regionkey smallint
  ,r_name varchar
  ,r_nations super
  );

# COPY region_nations
  FROM 's3://<S3 path to the JSON data file>'
  IAM_ROLE '<IAM role>'
  FORMAT JSON 'auto';

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

將文字或.csv 檔案中的 JSON 資料載入到 SUPER。請確認其採用有效的 JSON 格式。Amazon Redshift 會對 .csv 檔案使用標準轉義規則。

.csv 檔案範例：

r_regionkey,r_name,r_nations
0,AFRICA,"[{""n_nationkey"":11,""n_name"":""ALGERIA""},{""n_nationkey"":5,""n_name"":""ETHIOPIA""},{""n_nationkey"":14,""n_name"":""KENYA""}]"
1,AMERICA,"[{""n_nationkey"":1,""n_name"":""ARGENTINA""},{""n_nationkey"":2,""n_name"":""BRAZIL""}]"
2,ASIA,"[{""n_nationkey"":8,""n_name"":""INDIA""}]"

COPY 命令範例：

# CREATE TABLE region_nations
 (
  r_regionkey smallint
  ,r_name varchar
  ,r_nations super
  );

# COPY region_nations
   FROM 's3://<S3 path to the CSV file>'
   IAM_ROLE '<IAM role>'
   FORMAT CSV
   IGNOREHEADER 1;

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

使用 INSERT 命令和 JSON_PARSE 函數

JSON_PARSE 函數會解析 JSON 格式的資料，並將其轉換為可與 INSERT 命令一起使用的 SUPER 資料類型。

命令範例：

# CREATE TABLE region_nations
 (
  r_regionkey smallint
  ,r_name varchar
  ,r_nations super
  );

# INSERT INTO region_nations VALUES(0,'AFRICA',json_parse('[{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]'));
# INSERT INTO region_nations VALUES(1,'AMERICA',json_parse('[{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]'));
# INSERT INTO region_nations VALUES(2,'ASIA',json_parse('[{"n_nationkey":8,"n_name":"INDIA"}]'));

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

使用 PartiQL 查詢 SUPER 資料

Amazon Redshift 使用 PartiQL 語言以 SQL 相容方式存取關聯式資料、半結構化資料和巢狀資料。如需詳細資訊，請參閱查詢半結構化資料。

查詢範例：

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

# SELECT region_nations.r_nations[1].n_name FROM region_nations;
   n_name   
------------
 "ETHIOPIA"
 "BRAZIL"

(3 rows)

# SELECT rn.r_regionkey, rn.r_name, n FROM region_nations rn, rn.r_nations n;
 r_regionkey | r_name  |                   n                    
-------------+---------+----------------------------------------
           0 | AFRICA  | {"n_nationkey":11,"n_name":"ALGERIA"}
           0 | AFRICA  | {"n_nationkey":5,"n_name":"ETHIOPIA"}
           0 | AFRICA  | {"n_nationkey":14,"n_name":"KENYA"}
           1 | AMERICA | {"n_nationkey":1,"n_name":"ARGENTINA"}
           1 | AMERICA | {"n_nationkey":2,"n_name":"BRAZIL"}
           2 | ASIA    | {"n_nationkey":8,"n_name":"INDIA"}
(6 rows)

將 SUPER 資料卸載到 JSON 檔案

您可以使用 UNLOAD 命令從 SUPER 資料類型中擷取資料，並將其作為 JSON 文件儲存在 S3 中。

命令範例：

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

# UNLOAD ('SELECT * FROM region_nations')
  TO 's3://<S3 path>'
  IAM_ROLE '<IAM role>'
  FORMAT JSON;

JSON 文件範例：

{"r_regionkey":0,"r_name":"AFRICA","r_nations":[{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]}
{"r_regionkey":1,"r_name":"AMERICA","r_nations":[{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]}
{"r_regionkey":2,"r_name":"ASIA","r_nations":[{"n_nationkey":8,"n_name":"INDIA"}]}