O Amazon Redshift não permitirá mais a criação de UDFs do Python a partir do Patch 198. As UDFs do Python existentes continuarão a funcionar normalmente até 30 de junho de 2026. Para ter mais informações, consulte a [publicação de blog ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Tutorial: Consultar dados aninhados com o Amazon Redshift Spectrum Este tutorial demonstra como consultar dados aninhados com o Redshift Spectrum. Dados aninhados são dados que contêm campos aninhados. Campos aninhados são campos unidos como uma única entidade (por exemple, matrizes, structs ou objetos). **Topics** + [Visão geral](#tutorial-nested-data-overview) + [Etapa 1: Crie uma tabela externa que contém dados aninhados](#tutorial-nested-data-create-table) + [Etapa 2: Consultar os dados aninhados no Amazon S3 com extensões SQL](#tutorial-query-nested-data-sqlextensions) + [Casos de uso de dados aninhados](nested-data-use-cases.md) + [Limitações de dados aninhados (visualização)](nested-data-restrictions.md) + [Serializar JSON aninhado complexo](serializing-complex-JSON.md) ## Visão geral O Amazon Redshift Spectrum oferece suporte a consulta de dados aninhados em formatos de arquivo Parquet, ORC, JSON e Ion. O Redshift Spectrum acessa dados usando tabelas externas. Você pode criar tabelas externas usando os tipos de dados complexos `struct`, `array` e `map`. Por exemplo, suponha que o arquivo de dados contém os seguintes dados no Amazon S3 em uma pasta nomeada `customers`. Embora não haja um elemento raiz único, cada objeto JSON nestes dados de exemplo representa uma linha em uma tabela. ``` {"id": 1, "name": {"given": "John", "family": "Smith"}, "phones": ["123-457789"], "orders": [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50}, {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}] } {"id": 2, "name": {"given": "Jenny", "family": "Doe"}, "phones": ["858-8675309", "415-9876543"], "orders": [] } {"id": 3, "name": {"given": "Andy", "family": "Jones"}, "phones": [], "orders": [{"shipdate": "2018-03-02T08:02:15.000Z", "price": 13.50}] } ``` Você pode usar o Amazon Redshift Spectrum para consultar dados aninhados em arquivos. O tutorial a seguir mostra como fazer isso com dados do Apache Parquet. ### Pré-requisitos Se você ainda não estiver usando o Redshift Spectrum, acompanhe as etapas no [Conceitos básicos do Amazon Redshift Spectrum](c-getting-started-using-spectrum.md) antes de continuar. Para criar um esquema externo, substitua o ARN do perfil do IAM no comando a seguir pelo ARN do perfil que você criou em [Criar um perfil do IAM](c-getting-started-using-spectrum.md#c-getting-started-using-spectrum-create-role). Execute o comando no cliente SQL. ``` create external schema spectrum from data catalog database 'myspectrum_db' iam_role 'arn:aws:iam::123456789012:role/myspectrum_role' create external database if not exists; ``` ## Etapa 1: Crie uma tabela externa que contém dados aninhados Você pode visualizar a [fonte de dados](https://s3.amazonaws.com/redshift-downloads/tickit/spectrum/customers/customer_file1) baixando-a do Amazon S3. Para criar a tabela externa para este tutorial, execute o seguinte comando. ``` CREATE EXTERNAL TABLE spectrum.customers ( id int, name struct, phones array, orders array> ) STORED AS PARQUET LOCATION 's3://redshift-downloads/tickit/spectrum/customers/'; ``` No exemplo que precede, a tabela externa `spectrum.customers` usa os tipos de dados `struct` e `array` para definir colunas com dados aninhados. O Amazon Redshift Spectrum oferece suporte a consulta de dados aninhados em formatos de arquivo Parquet, ORC, JSON e Ion. O parâmetro `STORED AS` é `PARQUET` para arquivos do Apache Parquet. O parâmetro `LOCATION` deve se referir à pasta do Amazon S3 que contém os dados ou arquivos aninhados. Para obter mais informações, consulte [CREATE EXTERNAL TABLE](r_CREATE_EXTERNAL_TABLE.md). Você pode aninhar os tipos `array` e `struct` em qualquer nível. Por exemplo, você pode definir uma coluna nomeada `toparray` conforme exibido no seguinte exemplo. ``` toparray array>>>> ``` Você também pode aninhar os tipos `struct` como exibido na coluna `x` no exemplo a seguir. ``` x struct > > ``` ## Etapa 2: Consultar os dados aninhados no Amazon S3 com extensões SQL O Redshift Spectrum oferece suporte a tipos complexos de `array`, `map` e `struct` por meio das extensões de sintaxe SQL do Amazon Redshift SQL. ### Extensão 1: Acesso a colunas de estruturas Você pode extrair dados das colunas `struct` usando uma notação de ponto que concatene nomes de campo em caminhos. Por exemplo, a consulta a seguir retorna nomes e nomes de família de clientes. O nome é acessado pelo caminho longo `c.name.given`. O nome de família é acessado pelo caminho longo `c.name.family`. ``` SELECT c.id, c.name.given, c.name.family FROM spectrum.customers c; ``` A consulta anterior retorna os dados a seguir. ``` id | given | family ---|-------|------- 1 | John | Smith 2 | Jenny | Doe 3 | Andy | Jones (3 rows) ``` Uma `struct` pode ser uma coluna de outro `struct`, que pode ser uma coluna de outro `struct`, em qualquer nível. Os caminhos que acessa as colunas em `struct` aninhados de forma profunda podem ser arbitrariamente longos. Por exemplo, consulte a definição da coluna `x` no seguinte exemplo. ``` x struct > > ``` Você pode acessar os dados em `e` como `x.b.d.e`. ### Extensão 2: Variação sobre matriz em uma cláusula FROM Você pode extrair dados das colunas `array` (e, por extensão, colunas `map`) especificando as colunas `array` em uma cláusula `FROM` no lugar de nomes de tabela. A extensão aplica-se à cláusula `FROM` da consulta principal, e também as cláusulas `FROM` das subconsultas. Você pode referenciar elementos `array` por posição, como `c.orders[0]`. (visualização) Ao combinar a variação do `arrays` com junções, você pode alcançar vários tipos de não-aninhamento, como explicado os seguintes casos de uso. #### Não aninhamento usando junções internas A consulta a seguir selecione IDs de cliente e datas de envio de pedido de clientes que têm pedidos. A extensão do SQL na cláusula FROM `c.orders o` depende do alias `c`. ``` SELECT c.id, o.shipdate FROM spectrum.customers c, c.orders o ``` Para cada cliente `c` que possui pedidos, a cláusula `FROM` retorna uma linha para cada pedido `o` do cliente `c`. Essa linha combina a linha do cliente `c` e a linha de pedidos `o`. Em seguida, a cláusula `SELECT` mantém somente `c.id` e `o.shipdate`. O resultado é o seguinte. ``` id| shipdate --|---------------------- 1 |2018-03-01 11:59:59 1 |2018-03-01 09:10:00 3 |2018-03-02 08:02:15 (3 rows) ``` O alias `c` fornece acesso aos campos de clientes, e o alias `o` fornece acesso aos campos de pedidos. A semântica é semelhante ao SQL padrão. Você pode pensar na cláusula `FROM` como executando o loop aninhado a seguir, que é acompanhado por `SELECT` escolhendo os campos para saída. ``` for each customer c in spectrum.customers for each order o in c.orders output c.id and o.shipdate ``` Portanto, se um cliente não tiver um pedido, o cliente não aparece no resultado. Você também pode considerá-lo como cláusula `FROM` da execução `JOIN` com a tabela `customers` e o array `orders`. Na verdade, você também pode escrever a consulta, conforme mostrado no exemplo a seguir. ``` SELECT c.id, o.shipdate FROM spectrum.customers c INNER JOIN c.orders o ON true ``` **nota** Se um esquema nomeado `c` existe com uma tabela nomeada `orders`, então `c.orders` refere-se a tabela `orders`, e não a coluna de matriz de `customers`. #### Não aninhamento usando junções à esquerda A consulta a seguir exibe todos os nomes de clientes e seus pedidos. Se um cliente não fez um pedido, o nome do cliente ainda é retornado. Contudo, nesse caso, as colunas de pedido são NULL, conforme mostrado no exemplo a seguir para Jenny Doe. ``` SELECT c.id, c.name.given, c.name.family, o.shipdate, o.price FROM spectrum.customers c LEFT JOIN c.orders o ON true ``` A consulta anterior retorna os dados a seguir. ``` id | given | family | shipdate | price ----|---------|---------|----------------------|-------- 1 | John | Smith | 2018-03-01 11:59:59 | 100.5 1 | John | Smith | 2018-03-01 09:10:00 | 99.12 2 | Jenny | Doe | | 3 | Andy | Jones | 2018-03-02 08:02:15 | 13.5 (4 rows) ``` ### Extensão 3: Acessar uma matriz de escalares usando diretamente um alias Quando um alias `p` em intervalos de uma cláusula `FROM` percorre uma matriz de escalares, a consulta se refere aos valores de `p` como `p`. Por exemplo, a consulta a seguir produz pares de nomes de clientes e de números de telefone. ``` SELECT c.name.given, c.name.family, p AS phone FROM spectrum.customers c LEFT JOIN c.phones p ON true ``` A consulta anterior retorna os dados a seguir. ``` given | family | phone -------|----------|----------- John | Smith | 123-4577891 Jenny | Doe | 858-8675309 Jenny | Doe | 415-9876543 Andy | Jones | (4 rows) ``` ### Extensão 4: Acessar elementos de mapas Redshift Spectrum trata o tipo de dados `map` como um tipo `array` que contém tipos `struct` com uma coluna `key` e uma coluna `value`. O `key` deve ser um `scalar`; o valor pode ser qualquer tipo de dados. Por exemplo, o seguinte código criar uma tabela externa com um `map` para armazenar números de telefone. ``` CREATE EXTERNAL TABLE spectrum.customers2 ( id int, name struct, phones map, orders array> ) STORED AS PARQUET LOCATION 's3://redshift-downloads/tickit/spectrum/customers/'; ``` Como um tipo `map` se comporta como um tipo `array` com colunas `key` e `value`, você pode entender os esquemas anteriores como os seguintes. ``` CREATE EXTERNAL TABLE spectrum.customers3 ( id int, name struct, phones array>, orders array> ) STORED AS PARQUET LOCATION 's3://redshift-downloads/tickit/spectrum/customers/'; ``` A consulta a seguir retorna os nomes de clientes com um número de celular e retorna a quantidade para cada nome. A consulta de mapeamento é tratada como o equivalente de consulta dos tipos `array` de `struct` aninhados. A consulta a seguir retorna somente dados se você tiver criado a tabela externa como descrito previamente. ``` SELECT c.name.given, c.name.family, p.value FROM spectrum.customers c, c.phones p WHERE p.key = 'mobile'; ``` **nota** O `key` de um `map` é um `string` para tipos de arquivo Ion e JSON. # Casos de uso de dados aninhados Este tópico descreve casos de uso para dados aninhados. Dados aninhados são dados que contêm campos aninhados. Campos aninhados são campos unidos como uma única entidade (por exemple, matrizes, structs ou objetos). Você pode combinar as extensões descritas anteriormente com as características usuais SQL. Os casos de uso a seguir ilustram algumas combinações comuns. Estes exemplos ajudam a demonstrar como você pode usar dados aninhados. Eles não fazem parte do tutorial. **Topics** + [Ingerir dados aninhados](#ingesting-nested-data) + [Agregar dados aninhados com subconsultas](#aggregating-with-subquery) + [Unir o Amazon Redshift e dados aninhados](#joining-redshift-data) ## Ingerir dados aninhados Você pode usar uma declaração `CREATE TABLE AS` para ingerir dados de uma tabela externa que contenha tipos de dados complexos. A consulta a seguir extrai todos os clientes e seus números de telefone da tabela externa, usando `LEFT JOIN` e os armazena na tabela `CustomerPhones`. do Amazon Redshift. ``` CREATE TABLE CustomerPhones AS SELECT c.name.given, c.name.family, p AS phone FROM spectrum.customers c LEFT JOIN c.phones p ON true; ``` ## Agregar dados aninhados com subconsultas Você pode usar uma subconsulta para agregar dados aninhados. O exemplo a seguir ilustra essa abordagem. ``` SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount FROM spectrum.customers c; ``` Os dados a seguir são retornados. ``` given | family | ordercount --------|----------|-------------- Jenny | Doe | 0 John | Smith | 2 Andy | Jones | 1 (3 rows) ``` **nota** Quando você agrega dados aninhados agrupando os pela linha pai, a forma mais eficiente é a exibida no exemplo anterior. Nesse caso, as linhas aninhadas de `c.orders` são agrupadas em sua linha pai `c`. Ou, se você sabe que `id` é exclusivo para cada `customer` e `o.shipdate` e nunca nulo, você pode agregar conforme exibido no seguinte exemplo. Contudo, esta abordagem não é geralmente mais eficiente que o exemplo anterior. ``` SELECT c.name.given, c.name.family, COUNT(o.shipdate) AS ordercount FROM spectrum.customers c LEFT JOIN c.orders o ON true GROUP BY c.id, c.name.given, c.name.family; ``` Você também pode gravar a consulta usando uma subconsulta na cláusula `FROM` que refere-se a um alias (`c`) da consulta antecessora e extrai dados da matriz. O exemplo a seguir demonstra essa abordagem. ``` SELECT c.name.given, c.name.family, s.count AS ordercount FROM spectrum.customers c, (SELECT count(*) AS count FROM c.orders o) s; ``` ## Unir o Amazon Redshift e dados aninhados Você também pode adicionar dados do Amazon Redshift com dados aninhados em uma tabela externa. Por exemplo, digamos que você tenha os dados a seguir aninhados no Amazon S3. ``` CREATE EXTERNAL TABLE spectrum.customers2 ( id int, name struct, phones array, orders array> ); ``` Digamos também que possui a tabela a seguir no Amazon Redshift. ``` CREATE TABLE prices ( id int, price double precision ); ``` A consulta a seguir encontra o número total e a quantidade de compras de cada cliente com base em precedência. O exemplo a seguir é apenas uma ilustração. Só retorna dados se você tiver criado as tabelas descritas anteriormente. ``` SELECT c.name.given, c.name.family, COUNT(o.date) AS ordercount, SUM(p.price) AS ordersum FROM spectrum.customers2 c, c.orders o, prices p ON o.item = p.id GROUP BY c.id, c.name.given, c.name.family; ``` # Limitações de dados aninhados (visualização) Este tópico descreve as limitações referentes à leitura de dados aninhados com o Redshift Spectrum. Dados aninhados são dados que contêm campos aninhados. Campos aninhados são campos unidos como uma única entidade (por exemple, matrizes, structs ou objetos). **nota** As limitações marcadas (visualização prévia) na lista a seguir só se aplicam a clusters de visualização prévia criados nas regiões a seguir. Leste dos EUA (Ohio) (us-east-2) Leste dos EUA (Norte da Virgínia) (us-east-1) Oeste dos EUA (Norte da Califórnia) (us-west-1) Ásia Pacific (Tóquio) (ap-northeast-1) Europa (Irlanda) (eu-west-1) UE (Estocolmo) (eu-north-1) Para obter informações sobre como configurar clusters de visualização, consulte [Creating a preview cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-clusters-console.html#cluster-preview) no *Guia de gerenciamento do Amazon Redshift*. As limitações a seguir se aplicam à dados aninhados: + Um tipo `map` ou `array` pode conter outros tipos `map` ou `array`, desde que as consultas em `arrays` ou `maps` aninhados não retornem valores `scalar`. (visualização) + O Amazon Redshift Spectrum só dá suporte a tipos de dados complexos como tabelas externas. + As colunas de resultado da subconsulta devem estar no nível superior. (visualização) + Se uma expressão `OUTER JOIN` refere-se a uma tabela aninhada, pode consultar somente a tabela e seus arrays aninhados (e mapas). Se uma expressão `OUTER JOIN` não refere-se a uma tabela aninhada, pode consultar qualquer número de tabelas não aninhadas. + Se uma cláusula `FROM` em uma subconsulta refere-se a uma tabela aninhada, não pode consultar de outra tabela. + Se uma subconsulta depende de uma tabela aninhada referente a uma tabela pai, você só pode usar a tabela pai na cláusula `FROM`. Você não pode usar o pai em nenhuma outra cláusula, como a cláusula `SELECT` ou `WHERE`. Por exemplo, a consulta a seguir não é executada porque a cláusula `SELECT` da subconsulta se refere à tabela pai `c`. ``` SELECT c.name.given FROM spectrum.customers c WHERE (SELECT COUNT(c.id) FROM c.phones p WHERE p LIKE '858%') > 1; ``` A consulta a seguir funciona porque o pai `c` é usado apenas na cláusula `FROM` de uma subconsulta. ``` SELECT c.name.given FROM spectrum.customers c WHERE (SELECT COUNT(*) FROM c.phones p WHERE p LIKE '858%') > 1; ``` + Uma subconsulta que acessa dados aninhados em qualquer lugar, diferente da cláusula `FROM` deve retornar um valor único. As únicas exceções são os operadores `(NOT) EXISTS` em uma cláusula `WHERE`. + `(NOT) IN`Não há suporte ao . + A profundidade máxima de assentamento para todos os tipos aninhados é de 100. Essa restrição aplica-se a todos os formatos de arquivo (Parquet, ORC, Ion e JSON). + Subconsultas de agregação que acessam dados aninhadas podem se referir somente a `arrays` e `maps` em sua cláusula `FROM`, e não a uma tabela externa. + Não há suporte para consultar as pseudocolunas de dados aninhados em uma tabela do Redshift Spectrum. Para obter mais informações, consulte [Pseudocolunas](c-spectrum-external-tables.md#c-spectrum-external-tables-pseudocolumns). + Ao extrair dados das colunas de matriz ou mapa especificando-os em uma cláusula `FROM`, você só poderá selecionar valores dessas colunas se os valores forem `scalar`. Por exemplo, ambas as consultas a seguir tentam elementos `SELECT` por dentro de uma matriz. A consulta que seleciona `arr.a` funciona porque `arr.a` é um valor `scalar`. A segunda consulta não funciona porque `array` é uma matriz extraída de `s3.nested table` na cláusula `FROM`. (visualização) ``` SELECT array_column FROM s3.nested_table; array_column ----------------- [{"a":1},{"b":2}] SELECT arr.a FROM s3.nested_table t, t.array_column arr; arr.a ----- 1 --This query fails to run. SELECT array FROM s3.nested_table tab, tab.array_column array; ``` Você não pode usar uma matriz ou um mapa na cláusula `FROM` que acompanha outra matriz ou mapa. Para selecionar matrizes ou outras estruturas complexas que estejam aninhadas dentro de outras matrizes, considere usar índices na instrução `SELECT`. # Serializar JSON aninhado complexo Este tópico demonstra como serializar dados aninhados no formato JSON. Dados aninhados são dados que contêm campos aninhados. Campos aninhados são campos unidos como uma única entidade (por exemple, matrizes, structs ou objetos). Uma alternativa aos métodos demonstrados neste tutorial é consultar colunas de coleção aninhadas de nível superior como JSON serializado. Você pode usar a serialização para inspecionar, converter e ingerir dados aninhados como JSON com Redshift Spectrum. Este método é compatível com os formatos ORC, JSON, Ion e Parquet. Usar o parâmetro de configuração da sessão `json_serialization_enable` para configurar o comportamento de serialização. Quando definido, tipos de dados JSON complexos são serializados para VARCHAR (65535). O JSON aninhado pode ser acessado com [Funções JSON](json-functions.md). Para obter mais informações, consulte [json\$1serialization\$1enable](r_json_serialization_enable.md). Por exemplo, sem definir `json_serialization_enable`, as consultas a seguir que acessam colunas aninhadas falham diretamente. ``` SELECT * FROM spectrum.customers LIMIT 1; => ERROR: Nested tables do not support '*' in the SELECT clause. SELECT name FROM spectrum.customers LIMIT 1; => ERROR: column "name" does not exist in customers ``` Configurar `json_serialization_enable` permite consultar coleções de nível superior diretamente. ``` SET json_serialization_enable TO true; SELECT * FROM spectrum.customers order by id LIMIT 1; id | name | phones | orders ---+--------------------------------------+----------------+---------------------------------------------------------------------------------------------------------------------- 1 | {"given": "John", "family": "Smith"} | ["123-457789"] | [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50}, {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}] SELECT name FROM spectrum.customers order by id LIMIT 1; name --------- {"given": "John", "family": "Smith"} ``` Considere os seguintes itens ao serializar JSON aninhado. + Quando colunas de coleção são serializadas como VARCHAR (65535), seus subcampos aninhados não podem ser acessados diretamente como parte da sintaxe de consulta (por exemplo, na cláusula de filtro). No entanto, as funções JSON podem ser usadas para acessar JSON aninhado. + As seguintes representações especializadas não são compatíveis: + Junções ORC + Mapas ORC com chaves de tipo complexas + Datagramas Ion + SEXP Ion + Os timestamps são retornados como strings serializadas ISO. + Chaves de mapa primitivas são promovidas para string (por exemplo, `1` para `"1"`). + Valores nulos de nível superior são serializados como NULLs. + Se a serialização ultrapassa o tamanho máximo VARCHAR de 65535, a célula é definida como NULL. ## Serializar tipos complexos contendo strings JSON Por padrão, os valores de string contidos em coleções aninhadas são serializados como strings JSON escapadas. Escapar pode ser indesejável quando as strings são JSON válidos. Em vez disso, você pode querer gravar subelementos aninhados ou campos que são VARCHAR diretamente como JSON. Habilite esse comportamento com a configuração no nível da sessão `json_serialization_parse_nested_strings`. Quando ambos `json_serialization_enable` e `json_serialization_parse_nested_strings` são definidos, valores JSON válidos são serializados inline sem caracteres de escape. Quando o valor não é um JSON válido, ele é escapado como se a propriedade `json_serialization_parse_nested_strings` não foi definido. Para obter mais informações, consulte [json\$1serialization\$1parse\$1nested\$1strings](r_json_serialization_parse_nested_strings.md). Por exemplo, suponha que os dados do exemplo anterior continha JSON como um tipo complexo `structs` no campo `name`VARCHAR(20): ``` name --------- {"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"} ``` Quando `json_serialization_parse_nested_strings` estiver definido, a propriedade `name` é serializada da seguinte forma: ``` SET json_serialization_enable TO true; SET json_serialization_parse_nested_strings TO true; SELECT name FROM spectrum.customers order by id LIMIT 1; name --------- {"given": {"first":"John","middle":"James"}, "family": "Smith"} ``` Em vez de ser escapado assim: ``` SET json_serialization_enable TO true; SELECT name FROM spectrum.customers order by id LIMIT 1; name --------- {"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"} ```