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

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 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. 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 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<given:varchar(20), family:varchar(20)>,
  phones array<varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
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 ter mais informações, consulte CREATE EXTERNAL TABLE.

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<struct<nestedarray:
         array<struct<morenestedarray: 
         array<string>>>>>

Você também pode aninhar os tipos struct como exibido na coluna x no exemplo a seguir.

x struct<a: string,
         b: struct<c: integer,
                   d: struct<e: string>
                  >
        >

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<a: string,
         b: struct<c: integer, 
                      d: struct<e: string>
                  >
        >

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 

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<given:varchar(20), family:varchar(20)>,
  phones map<varchar(20), varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
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<given:varchar(20), family:varchar(20)>,
  phones array<struct<key:varchar(20), value:varchar(20)>>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
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';