Consultar dados semiestruturados

Com o Amazon Redshift, é possível consultar e analisar dados semiestruturados, como JSON, Avro ou Ion, bem como dados estruturados. Dados semiestruturados referem-se a dados que têm um esquema flexível que permite estruturas hierárquicas ou agrupadas. As seções a seguir demonstram a consulta de dados semiestruturados usando o suporte do Amazon Redshift a formatos de dados abertos, o que permite que você extraia informações valiosas de estruturas de dados complexas.

O Amazon Redshift usa a linguagem PartiQL para oferecer acesso compatível com SQL a dados relacionais, semiestruturados e aninhados.

O PartiQL opera com tipos dinâmicos. Esse método permite filtragem intuitiva, junção e agregação na combinação de conjuntos de dados estruturados, semiestruturados e aninhados. A sintaxe do PartiQL usa notação pontilhada e array subscript para navegação de caminho ao acessar dados aninhados. Ele também permite que os itens da cláusula FROM para iterar sobre arrays e usar para operações unnest. As seções a seguir descrevem os diferentes padrões de consulta que combinam o uso do tipo de dados SUPER com a navegação de caminho e matriz, desfazer aninhamento, transformar colunas em linhas ou junções.

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

Navegação

O Amazon Redshift usa o PartiQL para habilitar a navegação em arrays e estruturas usando o colchete [...] e a notação de pontos, respectivamente. Além disso, você pode misturar navegação em estruturas usando a notação de pontos e arrays usando a notação de colchetes. Por exemplo, o exemplo a seguir assume que a coluna de dados SUPER c_orders é uma matriz com uma estrutura, e um atributo é chamado o_orderkey.

Para ingerir dados na tabela customer_orders_lineitem, execute o comando a seguir. Substitua a função do IAM por suas próprias credenciais.

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

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

O Amazon Redshift também usa um alias de tabela como um prefixo para a notação. O exemplo a seguir é a mesma consulta do exemplo anterior.

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

Você pode usar as notações de ponto e colchetes em todos os tipos de consultas, como filtragem, junção e agregação. Você pode usar essas notações em uma consulta na qual normalmente há referências de coluna. O exemplo a seguir usa uma instrução SELECT que filtra resultados.

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;

O exemplo a seguir usa a navegação entre colchetes e pontos nas cláusulas GROUP BY e ORDER BY.

SELECT c_orders[0].o_orderdate,
       c_orders[0].o_orderstatus,
       count(*)
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderkey IS NOT NULL
GROUP BY c_orders[0].o_orderstatus,
         c_orders[0].o_orderdate
ORDER BY c_orders[0].o_orderdate;

Desaninhar consultas

Para desaninhar consultas, o Amazon Redshift usa a sintaxe PartiQL para iterar sobre matrizes SUPER. Ele faz isso navegando pela matriz usando a cláusula FROM de uma consulta. Com o exemplo anterior, o exemplo a seguir itera sobre os valores do atributo para c_orders.

SELECT c.*, o FROM customer_orders_lineitem c, c.c_orders o;

A sintaxe de desaninhamento é uma extensão da cláusula FROM. No SQL padrão, a cláusula FROM x (AS) y significa que y itera sobre cada tupla em relação a x. Nesse caso, x refere-se a uma relação, e y refere-se a um alias para a relação x. Da mesma forma, a sintaxe PartiQL de desaninhamento usando o item da cláusula FROM x (AS) y significa que y itera sobre cada valor (SUPER) na expressão de matriz x. Nesse caso, x é uma expressão SUPER, e y é um alias para x.

O operando esquerdo também pode usar a notação de pontos e colchetes para navegação regular. No exemplo anterior, customer_orders_lineitem c é a iteração sobre a tabela customer_order_lineitem de base, e c.c_orders o é a iteração sobre a matriz c.c_orders. Para iterar sobre atributo o_lineitems, que é uma matriz dentro de uma matriz, é necessário adicionar várias cláusulas.

SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

O Amazon Redshift também oferece suporte ao índice de matriz ao iterar sobre a matriz usando a palavra-chave AT. A cláusula x AS y AT z itera sobre a matriz x e gera o campo z,, que é o índice da matriz. O exemplo a seguir mostra como o índice da matriz funciona.

SELECT c_name,
       orders.o_orderkey AS orderkey,
       index AS orderkey_index
FROM customer_orders_lineitem c, c.c_orders AS orders AT index 
ORDER BY orderkey_index;

c_name             | orderkey | orderkey_index
-------------------+----------+----------------
Customer#000008251 | 3020007  |        0
Customer#000009452 | 4043971  |        0
  (2 rows)

O exemplo a seguir itera sobre uma matriz escalar.

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index;

 index | element
-------+----------
     0 | 1
     1 | 2.3
     2 | 45000000
(3 rows)

O exemplo a seguir itera sobre uma matriz de vários níveis. O exemplo usa várias cláusulas unnest para iterar nas matrizes mais internas. O AS da matriz f.multi_level_array itera sobre multi_level_array. O elemento AS da matriz é a iteração sobre as matrizes dentro de multi_level_array.

CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array;

SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;

   array   | element
-----------+---------
 [1.1,1.2] | 1.1
 [1.1,1.2] | 1.2
 [2.1,2.2] | 2.1
 [2.1,2.2] | 2.2
 [3.1,3.2] | 3.1
 [3.1,3.2] | 3.2
(6 rows)

Para obter mais informações sobre a cláusula FROM, consulte Cláusula FROM.

Transformar colunas em linhas de objetos

Para transformar colunas em linhas de objetos, o Amazon Redshift usa a sintaxe PartiQL para iterar sobre objetos SUPER. Ele faz isso usando a cláusula FROM de uma consulta com a palavra-chave UNPIVOT. Nesse caso, a expressão é o objeto c.c_orders[0]. A consulta de exemplo itera sobre cada atributo exibido pelo objeto.

SELECT attr as attribute_name, json_typeof(val) as value_type 
FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr 
WHERE c_custkey = 9451;

 attribute_name  | value_type
-----------------+------------
 o_orderstatus   | string
 o_clerk         | string
 o_lineitems     | array
 o_orderdate     | string
 o_shippriority  | number
 o_totalprice    | number
 o_orderkey      | number
 o_comment       | string
 o_orderpriority | string
(9 rows)

Assim como no desaninhamento, a sintaxe da transformação de coluna em linhas é uma extensão da cláusula FROM. A diferença é que a sintaxe da transformação de colunas em linhas usa a palavra-chave UNPIVOT para indicar que está iterando sobre um objeto em vez de uma matriz. Ele usa AS value_alias para iteração sobre todos os valores dentro de um objeto e usa o AT attribute_alias para iterar sobre todos os atributos. Pense no seguinte fragmento de sintaxe:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

O Amazon Redshift aceita o uso de desagregação de objetos e desaninhamento de matriz em uma única cláusula FROM da seguinte forma:

SELECT attr as attribute_name, val as object_value
FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr 
WHERE c_custkey = 9451;

Ao usar a transformação de coluna em linha de objetos, o Amazon Redshift não oferece suporte a transformação de coluna em linha correlacionada. Especificamente, suponha que você tenha um caso em que há vários exemplos de transformação de coluna em linha em diferentes níveis de consulta e transformação de coluna em linha interna faz referência à externa. O Amazon Redshift não oferece suporte a este tipo de transformações múltiplas de coluna em linha.

Para obter mais informações sobre a cláusula FROM, consulte Cláusula FROM. Para obter exemplos que mostrem como consultar dados estruturados com PIVOT e UNPIVOT, consulte Exemplos de PIVOT e UNPIVOT.

Digitação dinâmica

A digitação dinâmica não requer fundição explícita de dados que são extraídos dos caminhos de ponto e colchetes. O Amazon Redshift usa a digitação dinâmica para processar dados SUPER sem esquema sem a necessidade de declarar os tipos de dados antes de usá-los em sua consulta. A digitação dinâmica usa os resultados da navegação em colunas de dados SUPER sem ter que convertê-las explicitamente nos tipos do Amazon Redshift. A digitação dinâmica é mais útil em junções e cláusulas GROUP BY. O exemplo a seguir usa uma instrução SELECT que não requer conversão explícita das expressões de ponto e colchetes para os tipos habituais do Amazon Redshift. Para obter informações sobre a compatibilidade e conversão de tipos, consulte Compatibilidade e conversão dos tipos.

SELECT c_orders[0].o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus = 'P';

O sinal de igualdade nesta consulta é avaliado como true quando c_orders [0] .o_orderstatus é a string 'P'. Em todos os demais casos, o sinal de igualdade é avaliado como false, incluindo os casos em que os argumentos da igualdade são diferentes tipos.

Digitação dinâmica e estática

Sem usar a digitação dinâmica, você não pode determinar se c_orders [0] .o_orderstatus é uma string, um inteiro ou uma estrutura. Você só pode determinar que c_orders [0] .o_orderstatus é um tipo de dados SUPER, que pode ser um escalar, um array ou uma estrutura do Amazon Redshift. O tipo estático de c_orders [0] .o_orderstatus é um tipo de dados SUPER. Convencionalmente, um tipo é implicitamente um tipo estático no SQL.

O Amazon Redshift usa a digitação dinâmica para o processamento de dados sem esquema. Quando a consulta avalia os dados, c_orders [0] .o_orderstatus acaba por ser um tipo específico. Por exemplo, avaliar c_orders [0] .o_orderstatus no primeiro registro de customer_orders_lineitem pode resultar em um inteiro. A avaliação no segundo registro pode resultar em uma string. Estes são os tipos dinâmicos da expressão.

Ao usar um operador ou função SQL com expressões de ponto e colchetes que têm tipos dinâmicos, o Amazon Redshift produz resultados semelhantes ao uso do operador ou função SQL padrão com os respectivos tipos estáticos. Neste exemplo, quando o tipo dinâmico da expressão de caminho é uma string, a comparação com a string 'P' é significativa. Sempre que o tipo dinâmico de c_orders [0] .o_orderstatus é qualquer outro tipo de dados exceto ser uma string, a igualdade retorna false. Outras funções retornam null quando argumentos digitados incorretamente são usados.

O seguinte exemplo grava a consulta anterior com digitação estática:

SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR = 'P'
           ELSE FALSE END;

Observe a seguinte distinção entre predicados de igualdade e predicados de comparação. No exemplo anterior, se você substituir o predicado de igualdade por um predicado menor que ou igual, a semântica produzirá null em vez de false.

SELECT c_orders[0]. o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus <= 'P';

Neste exemplo, se c_orders [0] .o_orderstatus for uma string, o Amazon Redshift retornará true se for alfabeticamente igual ou menor que 'P'. O Amazon Redshift retornará false se for alfabeticamente maior que 'P'. No entanto, se c_orders [0] .o_orderstatus não for uma string, o Amazon Redshift retornará null, pois o Amazon Redshift não pode comparar valores de tipos diferentes, conforme mostrado na consulta a seguir:

SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR <= 'P'
           ELSE NULL END;

A digitação dinâmica não exclui de comparações de tipos que são minimamente comparáveis. Por exemplo, você pode converter os tipos escalares CHAR e VARCHAR do Amazon Redshift para SUPER. Eles são comparáveis como strings, inclusive ignorando caracteres de espaço em branco à direita semelhantes aos tipos CHAR e VARCHAR do Amazon Redshift. Da mesma forma, números inteiros, decimais e valores de ponto flutuante são comparáveis como valores SUPER. Especificamente para colunas decimais, cada valor também pode ter uma escala diferente. O Amazon Redshift ainda os considera como tipos dinâmicos.

O Amazon Redshift também oferece suporte à igualdade em objetos e arrays que são avaliados como profundos iguais, como avaliar profundamente em objetos ou arrays e comparar todos os atributos. Use profundo igual com cautela, porque o processo de realização de igual profundo pode ser demorado.

Usar a digitação dinâmica para junções

Para junções, a digitação dinâmica corresponde automaticamente aos valores com diferentes tipos dinâmicos sem executar uma longa análise CASE WHEN para descobrir quais tipos de dados podem aparecer. Por exemplo, suponha que sua organização alterou o formato que estava usando para chaves de peça ao longo do tempo.

As chaves iniciais de parte inteira emitidas são substituídas por chaves de partes de string, como 'A55', e posteriormente substituídas por chaves de partes de matriz, como ['X', 10] combinando uma string e um número. O Amazon Redshift não precisa executar uma longa análise de caso sobre chaves de peça e pode usar joins como mostrado no exemplo a seguir.

SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE l.l_partkey = ps.ps_partkey
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

O exemplo a seguir mostra quão complexa e ineficiente a mesma consulta pode ser sem usar a digitação dinâmica:

SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey)
           THEN l.l_partkey::integer = ps.ps_partkey::integer
           WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey)
           THEN l.l_partkey::varchar = ps.ps_partkey::varchar
           WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey)
                AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0])
                AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1])
           THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar
                AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer
           ELSE FALSE END
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

Semântica lax

Por padrão, as operações de navegação em valores SUPER retornam null em vez de retornar um erro quando a navegação é inválida. A navegação do objeto é inválida se o valor SUPER não for um objeto ou se o valor SUPER for um objeto, mas não contiver o nome do atributo usado na consulta. Por exemplo, a consulta a seguir acessa um nome de atributo inválido na coluna de dados SUPER cdata:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

Navegação de array retorna null se o valor SUPER não é um array ou o índice de array está fora dos limites. A consulta a seguir retorna null porque c_orders [1] [1] está fora dos limites.

SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

A semântica lax é especialmente útil quando se usa a digitação dinâmica para lançar um valor SUPER. Transferir um valor SUPER para o tipo errado retorna null em vez de um erro se a conversão for inválida. Por exemplo, a consulta a seguir retorna null porque não pode converter o valor de sequência de caracteres 'Good' do atributo o_orderstatus de objeto para INTEGER. O Amazon Redshift retorna um erro para uma transmissão VARCHAR para INTEGER, mas não para uma transmissão SUPER.

SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

Tipos de introspecção

As colunas de dados SUPER suportam funções de inspeção que retornam o tipo dinâmico e outras informações de tipo sobre o valor SUPER. A função escalar JSON_TYPEOF retorna um VARCHAR com valores booleanos, number, string, object, array ou null, dependendo do tipo dinâmico do valor SUPER. O Amazon Redshift oferece suporte às seguintes funções booleanas para colunas de dados SUPER:

Todas essas funções retornam false se o valor de entrada for nulo. IS_SCALAR, IS_OBJECT e IS_ARRAY são mutuamente exclusivos e cobrem todos os valores possíveis, exceto nulo.

Para inferir os tipos correspondentes aos dados, o Amazon Redshift usa a função JSON_TYPEOF que retorna o tipo de (o nível superior de) o valor SUPER, conforme mostrado no exemplo a seguir:

SELECT JSON_TYPEOF(r_nations) FROM region_nations;
 json_typeof
 -------------
 array
(1 row)

SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations;
 json_typeof
 -------------
 number

O Amazon Redshift vê isso como uma única string longa, semelhante à inserção desse valor em uma coluna VARCHAR em vez de uma SUPER. Como a coluna é SUPER, a string única ainda é um valor SUPER válido e a diferença é observada em JSON_TYPEOF:

SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations;
 is_varchar
-------------
 true
(1 row)

SELECT r_nations[4].n_name FROM region_nations
WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) 
           THEN r_nations[4].n_nationkey::INTEGER = 15 
           ELSE false END;

Order by (Ordenar por)

O Amazon Redshift não define comparações SUPER entre valores com diferentes tipos dinâmicos. Um valor SUPER que é uma cadeia não é menor nem maior do que um valor SUPER que é um número. Para usar as cláusulas ORDER BY com colunas SUPER, o Amazon Redshift define um pedido total entre diferentes tipos a serem observados quando o Amazon Redshift classifica os valores SUPER usando as cláusulas ORDER BY. A ordem entre tipos dinâmicos é booleano, número, string, array, objeto. O seguinte exemplo mostra as ordens de diferentes tipos:

INSERT INTO region_nations VALUES 
(100,'name1','comment1','AWS'), 
(200,'name2','comment2',1),
(300,'name3','comment3',ARRAY(1, 'abc', null)),
(400,'name4','comment4',-2.5),
(500,'name5','comment5','Amazon');

SELECT r_nations FROM region_nations order by r_nations;

r_nations
----------------
 -2.5
 1
 "Amazon"
 "AWS"
 [1,"abc",null]
(5 rows)

Para obter mais informações sobre a cláusula ORDER BY, consulte Cláusula ORDER BY.