Carregamento de dados semiestruturados no Amazon Redshift

Use o tipo de dados SUPER para persistir e consultar dados hierárquicos e genéricos no Amazon Redshift. O Amazon Redshift apresenta a função json_parse para analisar dados no formato JSON e convertê-los na representação SUPER. O Amazon Redshift também oferece suporte ao carregamento de colunas SUPER no comando COPY Os formatos de arquivo compatíveis são JSON, Avro, texto, formato CSV (valores separados por vírgula), Parquet e ORC.

Para obter informações sobre as tabelas usadas nos exemplos a seguir, consulte Conjunto de dados de amostra SUPER.

Para obter mais informações sobre a função json_parse, consulte Função JSON_PARSE.

A codificação padrão para o tipo de dados SUPER é ZSTD.

Analisar documentos JSON para colunas SUPER

Você pode inserir ou atualizar dados JSON em uma coluna SUPER usando a função json_parse. A função analisa dados no formato JSON e converte no tipo de dados SUPER, que você pode usar em instruções INSERT ou UPDATE.

O exemplo a seguir insere dados JSON em uma coluna SUPER. Se a função json_parse estiver ausente na consulta, o Amazon Redshift trata o valor como uma única string em vez de uma string formatada em JSON que deve ser analisada.

Se você atualizar uma coluna de dados SUPER, o Amazon Redshift exigirá que o documento completo seja passado para valores de coluna. O Amazon Redshift não oferece suporte a atualização parcial.

INSERT INTO region_nations VALUES(0,
   'lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to',
   'AFRICA',
   JSON_PARSE('{"r_nations":[
      {"n_comment":" haggle. carefully final deposits detect slyly agai",
         "n_nationkey":0,
         "n_name":"ALGERIA"
      },
      {"n_comment":"ven packages wake quickly. regu",
         "n_nationkey":5,
         "n_name":"ETHIOPIA"
      },
      {"n_comment":" pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t",
         "n_nationkey":14,
         "n_name":"KENYA"
      },
      {"n_comment":"rns. blithely bold courts among the closely regular packages use furiously bold platelets?",
         "n_nationkey":15,
         "n_name":"MOROCCO"
      },
      {"n_comment":"s. ironic, unusual asymptotes wake blithely r",
         "n_nationkey":16,
         "n_name":"MOZAMBIQUE"
      }
   ]
}'));

Usar COPY para carregar colunas SUPER no Amazon Redshift

Nas seções a seguir, você pode aprender sobre diferentes maneiras de usar o comando COPY para carregar dados JSON no Amazon Redshift.

Copiar dados de JSON e Avro

Usando o suporte a dados semiestruturados no Amazon Redshift, você pode carregar um documento JSON sem fragmentar os atributos de suas estruturas JSON em várias colunas.

O Amazon Redshift fornece dois métodos para ingerir documentos JSON usando COPY, mesmo com uma estrutura JSON totalmente ou parcialmente desconhecida:

Você pode usar essas opções com os formatos JSON ou Avro.

O tamanho máximo de um objeto JSON antes da destruição é 4 MB.

Copiar um documento JSON em uma única coluna de dados SUPER

Para copiar um documento JSON em uma única coluna de dados SUPER, crie uma tabela com uma única coluna de dados SUPER.

CREATE TABLE region_nations_noshred (rdata SUPER);

Copie os dados do Amazon S3 na única coluna de dados SUPER. Para ingerir os dados de origem JSON em uma única coluna de dados SUPER, especifique a propriedade noshred na cláusula FORMAT JSON.

COPY region_nations_noshred FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'noshred';

Depois que COPY ingeriu com êxito o JSON, sua tabela tem uma coluna rdata de dados SUPER que contém os dados de todo o objeto JSON. Os dados ingeridos mantêm todas as propriedades da hierarquia JSON. No entanto, as folhas são convertidas para tipos escalares do Amazon Redshift para processamento eficiente de consultas.

Use a consulta a seguir para recuperar a string JSON original.

SELECT rdata FROM region_nations_noshred;

Quando o Amazon Redshift gera uma coluna de dados SUPER, ela se torna acessível usando JDBC como uma string por meio da serialização JSON. Para ter mais informações, consulte Serializar JSON aninhado complexo.

Copiar um documento JSON em múltiplas colunas de dados SUPER

Você pode fragmentar um documento JSON em várias colunas que podem ser colunas de dados SUPER ou tipos escalares do Amazon Redshift. O Amazon Redshift espalha diferentes partes do objeto JSON em colunas diferentes.

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

Para copiar os dados do exemplo anterior para a tabela, especifique a opção AUTO na cláusula FORMAT JSON para dividir o valor JSON em várias colunas. COPY corresponde aos atributos JSON de nível superior com nomes de coluna e permite que valores aninhados sejam ingeridos como valores SUPER, como matrizes e objetos JSON.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

Quando os nomes de atributo JSON estiverem em maiúsculas e minúsculas mistas, especifique a propriedade auto ignorecase na cláusula FORMAT JSON. Para obter mais informações sobre o uso do comando COPY, consulte Carregar de dados JSON usando a opção "auto ignorecase".

Em alguns casos, há uma incompatibilidade entre nomes de coluna e atributos JSON ou o atributo a ser carregado é aninhado mais do que um nível profundo. Em caso afirmativo, use um arquivo jsonpaths para mapear manualmente atributos JSON para colunas do Amazon Redshift.

CREATE TABLE nations
(
 regionkey smallint
 ,name varchar
 ,comment super
 ,nations super
 ); 

Suponha que você deseja carregar dados em uma tabela onde os nomes de coluna não correspondam aos atributos JSON. No exemplo a seguir, a tabela nations é tal tabela. Você pode criar um arquivo jsonpaths que mapeia os caminhos dos atributos para as colunas da tabela por sua posição no array jsonpaths.

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$.r_comment",
       "$.r_nations
    ]
}

O local do arquivo jsonpaths é usado como argumento para FORMAT JSON.

COPY nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_jsonpaths.json';

Use a consulta a seguir para acessar a tabela que mostra a dispersão de dados para várias colunas. As colunas de dados SUPER são impressas usando o formato JSON.

SELECT r_regionkey,r_name,r_comment,r_nations[0].n_nationkey FROM region_nations ORDER BY 1,2,3 LIMIT 1;

Arquivos Jsonpaths mapeiam campos no documento JSON para colunas de tabela. É possível extrair outras colunas, como chaves de distribuição e classificação, enquanto ainda carrega o documento completo como uma coluna SUPER. A consulta a seguir carrega o documento completo na coluna de nações. A coluna name é a chave de classificação, e a coluna regionkey é a chave de distribuição.

CREATE TABLE nations_sorted (
    regionkey smallint,
    name varchar,
    nations super
) DISTKEY(regionkey) SORTKEY(name);

O jsonpath raiz “$” mapeia para a raiz do documento da seguinte maneira:

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$"
    ]
}

O local do arquivo jsonpaths é usado como argumento para FORMAT JSON.

COPY nations_sorted FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_sorted_jsonpaths.json';

Copiar dados de texto e CSV

O Amazon Redshift representa colunas SUPER em formatos de texto e CSV como JSON serializado. A formatação JSON válida é necessária para que as colunas SUPER sejam carregadas com as informações do tipo correto. Tire aspas de que objetos, matrizes, números, boolianos e valores nulos. Quebre valores de strings entre aspas duplas. As colunas SUPER usam regras de escape padrão para formatos de texto e CSV. Para CSV, os delimitadores usam caracteres de escape de acordo com o padrão CSV. Para texto, quando o delimitador escolhido também pode aparecer em um campo SUPER, use a opção ESCAPE durante COPY e UNLOAD.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/csv/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT CSV;

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/text/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
DELIMITER ','
ESCAPE;

Copiar dados de Parquet e ORC em formato de coluna

Se seus dados semiestruturados ou aninhados já estiverem disponíveis no formato Apache Parquet ou Apache ORC, você pode usar o comando COPY para ingerir dados no Amazon Redshift.

A estrutura da tabela do Amazon Redshift deve corresponder ao número de colunas e aos tipos de dados da coluna dos arquivos Parquet ou ORC. Especificando SERIALIZETOJSON no comando COPY, você pode carregar qualquer tipo de coluna no arquivo que se alinha com uma coluna SUPER na tabela como SUPER. Isso inclui tipos de estrutura e array.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/parquet/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT PARQUET SERIALIZETOJSON;

O exemplo a seguir usa um formato ORC.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/orc/region_nation'
IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT ORC SERIALIZETOJSON;

Quando os atributos dos tipos de dados de data ou hora estão no ORC, o Amazon Redshift os converte em varchar ao codificá-los em SUPER.