Como uso o tipo de dados SUPER no Amazon Redshift para processar e consultar dados JSON?

Breve descrição

Use o tipo de dados SUPER para armazenar dados semiestruturados em seus data warehouses do Amazon Redshift. É possível consultar o tipo de dados SUPER com a linguagem PartiQL.

Também é possível usar o Amazon Redshift Spectrum para suporte de dados semiestruturados.

Resolução

É possível usar o comando COPY ou o comando INSERT com a função JSON_PARSE para carregar JSON em dados SUPER. Quando você carrega dados grandes, é uma prática recomendada usar o comando COPY.

Usar o comando COPY

Use o comando COPY para carregar JSON a partir de arquivos de dados no Amazon Simple Storage Service (Amazon S3). Para mais informações, consulte Carregar dados semiestruturados no Amazon Redshift.

Para carregar JSON em uma única coluna de dados SUPER, use a opção noshred.

Para carregar JSON em várias colunas, use a opção auto ou especifique o arquivo jsonpaths. Quando você usa a opção auto, COPY combina os atributos JSON de nível superior com os nomes das colunas e permite carregar valores aninhados como valores SUPER. Os exemplos a seguir demonstram como usar a opção auto.

Exemplo de arquivo de dados 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"}]}

Exemplo de comando 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)

Carregue os dados JSON em um arquivo de texto ou .csv para SUPER. Certifique-se de que esteja em um formato JSON válido. O Amazon Redshift usa regras de escape padrão para arquivos.csv.

Exemplo de arquivo.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""}]"

Exemplo de comando 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)

Usar o comando INSERT e a função JSON_PARSE

A função JSON_PARSE analisa os dados no JSON e os converte no tipo de dados SUPER que é possível usar com o comando INSERT.

Exemplo de comando:

# 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)

Consultar dados SUPER com PartiQL

O Amazon Redshift usa a linguagem PartiQL para acesso compatível com SQL a dados relacionais, semiestruturados e aninhados. Para mais informações, consulte Consultar dados semiestruturados.

Consulta de exemplo:

# 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)

Descarregar dados SUPER em um arquivo JSON

É possível usar o comando UNLOAD para extrair dados do tipo de dados SUPER e armazená-los no S3 como um documento JSON.

Exemplo de comando:

# 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;

Exemplo de documento 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"}]}