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.