Consulta de datos semiestructurados

Con Amazon Redshift, puede consultar y analizar datos semiestructurados, como JSON, Avro o Ion, junto con los datos estructurados. Los datos semiestructurados se refieren a los datos que tienen un esquema flexible, lo que permite estructuras jerárquicas o anidadas. En las siguientes secciones, se muestra la consulta de datos semiestructurados mediante el uso del soporte de Amazon Redshift para formatos de datos abiertos, lo que le permite obtener información valiosa de estructuras de datos complejas.

Amazon Redshift utiliza el lenguaje PartiQL para ofrecer acceso compatible con SQL a datos relacionales, semiestructurados y anidados.

PartiQL funciona con tipos dinámicos. Este enfoque permite filtrar, unir y agrupar de forma intuitiva la combinación de conjuntos de datos estructurados, semiestructurados y anidados. La sintaxis PartiQL utiliza notación con puntos y subíndice de matriz para la navegación de rutas cuando se accede a datos anidados. También habilita los elementos de la cláusula FROM para iterar sobre matrices y utilizarlos para las operaciones de eliminación de anidamiento. A continuación, se describen los diferentes patrones de consulta que combinan el uso del tipo de datos SUPER con la navegación, la anulación de dinamización, la eliminación de anidamiento o las uniones de las rutas y las matrices.

Para obtener información acerca de las tablas utilizadas en los ejemplos siguientes, consulte Conjunto de datos de muestra SUPER.

Navegación

Amazon Redshift utiliza PartiQL para permitir la navegación hacia matrices y estructuras mediante la notación […] con corchetes y puntos respectivamente. Además, puede mezclar la navegación en estructuras mediante la notación con puntos y matrices con la notación con corchetes. Por ejemplo, en el siguiente ejemplo, se supone que la columna de datos SUPER c_orders es una matriz con una estructura y que un atributo se denomina o_orderkey.

Para capturar datos en la tabla customer_orders_lineitem, ejecute el siguiente comando. Reemplace el rol de IAM con sus propias credenciales.

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;

Amazon Redshift también utiliza un alias de tablas como prefijo para la notación. En el siguiente ejemplo, se presenta la misma consulta que en el ejemplo anterior.

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

Puede utilizar las notaciones con puntos y corchetes en todos los tipos de consultas, como las de filtrado, combinación y agregación. También puede utilizar estas notaciones en una consulta en la que por lo general hay referencias de columnas. En el siguiente ejemplo, se utiliza una instrucción SELECT que filtra los resultados.

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

En el siguiente ejemplo, se utiliza la navegación con corchetes y puntos tanto en las cláusulas GROUP BY como 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;

Consultas de eliminación del anidamiento

Para eliminar el anidamiento de las consultas, Amazon Redshift utiliza la sintaxis de PartiQL para iterar sobre las matrices SUPER. Para ello, navega por la matriz con la cláusula FROM de una consulta. Continuando con el ejemplo anterior, el siguiente ejemplo itera los valores de atributo de c_orders.

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

La sintaxis de desanidamiento es una extensión de la cláusula FROM. En SQL estándar, la cláusula FROM x (AS) y significa que y itera cada tupla en relación con x. En este caso, x hace referencia a una relación y y hace referencia a un alias de relación x. Del mismo modo, la sintaxis de PartiQL de eliminación de anidamiento con el elemento de cláusula FROM x (AS) y significa que y itera sobre cada valor (SUPER) en la expresión de matriz (SUPER) x. En este caso, x es una expresión SUPER y y es un alias de x.

El operando izquierdo también puede utilizar la notación con puntos y corchetes para la navegación normal. En el ejemplo anterior, customer_orders_lineitem c es la iteración sobre la tabla base customer_order_lineitem y c.c_orders o es la iteración sobre la matriz c.c_orders. Para iterar sobre el atributo o_lineitems, que es una matriz dentro de otra matriz, debe agregar varias cláusulas.

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

Amazon Redshift también admite un índice de matrices cuando se itera sobre la matriz usando la palabra clave AT. La cláusula x AS y AT z itera sobre la matriz x y genera el campo z,, que es el índice de la matriz. En el siguiente ejemplo, se muestra cómo funciona un índice de matrices.

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)

En el siguiente ejemplo se itera una 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)

En el siguiente ejemplo se itera una matriz de varios niveles. En el ejemplo se utilizan varias cláusulas no anidadas para iterar en las matrices más internas. La matriz AS f.multi_level_array itera sobre multi_level_array. El elemento AS de la matriz representa la iteración sobre las matrices 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 obtener más información acerca de la cláusula FROM, consulte Cláusula FROM.

Anulación de la dinamización de los objetos

Para anular la dinamización del objeto, Amazon Redshift utiliza la sintaxis de PartiQL para iterar sobre objetos SUPER. Para ello, utiliza la cláusula FROM de una consulta con la palabra clave UNPIVOT. En este caso, la expresión es el objeto c.c_orders[0]. La consulta de ejemplo itera sobre cada atributo devuelto por el 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)

Al igual que para eliminar el anidamiento, la sintaxis para anular la dinamización también es una extensión de la cláusula FROM. La diferencia es que la sintaxis para anular la dinamización utiliza la palabra clave UNPIVOT para indicar que está iterando sobre un objeto en lugar de una matriz. Utiliza AS value_alias para iterar sobre todos los valores dentro de un objeto y utiliza AT attribute_alias para iterar sobre todos los atributos. Considere el siguiente fragmento de sintaxis:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift admite la anulación de la dinamización de objetos y la eliminación del anidamiento de matrices en una sola cláusula FROM de la siguiente manera:

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;

Cuando se utiliza la anulación de la dinamización de objetos, Amazon Redshift no admite la anulación correlacionada. En concreto, supongamos que tiene un caso en el que hay varios ejemplos de anulación de la dinamización en diferentes niveles de consulta y la anulación de la dinamización interna hace referencia al exterior. Amazon Redshift no admite este tipo de anulación de la dinamización múltiple.

Para obtener más información acerca de la cláusula FROM, consulte Cláusula FROM. Para ver ejemplos que muestran cómo realizar consultas de datos estructurados con PIVOT y UNPIVOT, consulte Ejemplos de PIVOT y UNPIVOT.

Escritura dinámica

La escritura dinámica no requiere la conversión explícita de los datos que se extraen de las rutas con puntos y corchetes. Amazon Redshift utiliza la escritura dinámica para procesar datos SUPER sin esquema sin necesidad de declarar los tipos de datos antes de utilizarlos en la consulta. La escritura dinámica utiliza los resultados de navegar por columnas de datos SUPER sin tener que convertirlos explícitamente en tipos de Amazon Redshift. La escritura dinámica es más útil en las uniones y las cláusulas GROUP BY. En el siguiente ejemplo, se utiliza una instrucción SELECT que no requiere ninguna conversión explícita de las expresiones con puntos y corchetes en los tipos habituales de Amazon Redshift. Para obtener más información acerca de la compatibilidad y la conversión de tipos, consulte Conversión y compatibilidad de tipos.

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

El signo de igualdad en esta consulta se evalúa como true cuando c_orders[0].o_orderstatus es la cadena “P”. En todos los demás casos, el signo de igualdad se evalúa como false, incluidos los casos en los que los argumentos de la igualdad son tipos diferentes.

Escritura dinámica y estática

Sin usar la escritura dinámica, no se puede determinar si c_orders[0].o_orderstatus es una cadena, un entero o una estructura. Solo puede determinar que c_orders[0].o_orderstatus es un tipo de datos SUPER, que puede ser un escalar, una matriz o una estructura de Amazon Redshift. El tipo estático de c_orders[0].o_orderstatus es un tipo de datos SUPER. De forma convencional, un tipo es implícitamente un tipo estático en SQL.

Amazon Redshift utiliza la escritura dinámica para el procesamiento de datos sin esquema. Cuando la consulta evalúa los datos, c_orders[0].o_orderstatus resulta ser un tipo específico. Por ejemplo, evaluar c_orders[0].o_orderstatus en el primer registro de customer_orders_lineitem puede resultar en un entero. La evaluación en el segundo registro puede resultar en una cadena. Estos son los tipos dinámicos de la expresión.

Cuando se utiliza un operador o una función SQL con expresiones con puntos y corchetes que tienen tipos dinámicos, Amazon Redshift produce resultados similares a utilizar el operador o la función SQL estándar con los respectivos tipos estáticos. En este ejemplo, cuando el tipo dinámico de la expresión de ruta es una cadena, la comparación con la cadena “P” es significativa. Siempre que el tipo dinámico de c_orders[0].o_orderstatus sea cualquier otro tipo de datos, excepto que sea una cadena, la igualdad devolverá el resultado false. Otras funciones devuelven valores nulos cuando se usan argumentos mal escritos.

En el siguiente ejemplo, se la consulta anterior se escribe con escritura 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;

Tenga en cuenta la siguiente distinción entre predicados de igualdad y predicados de comparación. En el ejemplo anterior, si el predicado de igualdad se reemplaza con un predicado de menor que o igual, la semántica produce un valor nulo en vez de uno false.

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

En este ejemplo, si c_orders[0].o_orderstatus es una cadena, Amazon Redshift devuelve el valor true si es alfabéticamente igual o menor que “P”. Amazon Redshift devuelve un valor false si es alfabéticamente mayor que “P”. Sin embargo, si c_orders[0].o_orderstatus no es una cadena, Amazon Redshift devuelve un valor “null” (nulo) debido a que Amazon Redshift no puede comparar valores de diferentes tipos, como se muestra en la consulta siguiente:

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;

La escritura dinámica no efectúa exclusiones de las comparaciones de tipos que son mínimamente comparables. Por ejemplo, puede convertir los tipos escalares CHAR y VARCHAR de Amazon Redshift en SUPER. Estos tipos son comparables como cadenas, lo que incluye ignorar los caracteres de espacio en blanco finales similares a los tipos CHAR y VARCHAR de Amazon Redshift. De manera similar, los valores enteros, decimales y de coma flotante son comparables como valores SUPER. Específicamente para las columnas decimales, cada valor también puede tener una escala diferente. Amazon Redshift sigue considerándolos como tipos dinámicos.

Amazon Redshift también admite la igualdad en los objetos y las matrices que se evalúan como de igualdad profunda, tales como la evaluación profunda de objetos o matrices y la comparación de todos los atributos. Utilice la igualdad profunda con precaución, ya que el proceso de realizar la igualdad profunda puede llevar mucho tiempo.

Uso de la escritura dinámica para las uniones

Para las uniones, la escritura dinámica combina automáticamente los valores con diferentes tipos dinámicos, sin tener que realizar un largo análisis de CASE WHEN para averiguar qué tipos de datos pueden aparecer. Por ejemplo, suponga que, con el tiempo, su organización cambió el formato que utilizaba para las claves de partes.

Las claves iniciales de partes de enteros emitidas se reemplazan por claves de partes de cadenas, como “A55”, y, luego, se reemplazan de nuevo por claves de partes de matrices, como [‘X’, 10], que combina una cadena y un número. Amazon Redshift no tiene que realizar un análisis prolongado de casos sobre las claves de partes y puede utilizar uniones, como se muestra en el siguiente ejemplo.

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;

En el siguiente ejemplo, se muestra cuán compleja e ineficiente puede ser la misma consulta si no se usa la escritura 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 laxa

De manera predeterminada, las operaciones de navegación en los valores SUPER devuelven valores nulos en lugar de devolver un error cuando la navegación no es válida. La navegación por objetos no es válida si el valor SUPER no es un objeto o si el valor SUPER es un objeto, pero no contiene el nombre del atributo utilizado en la consulta. Por ejemplo, la siguiente consulta tiene acceso a un nombre de atributo no válido de la columna de datos SUPER cdata:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

La navegación por matrices devuelve el valor nulo si el valor SUPER no es una matriz o si el índice de matriz está fuera de los límites. La siguiente consulta devuelve el valor nulo porque c_orders[1][1] está fuera de los límites.

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

La semántica laxa es especialmente útil cuando se utiliza la escritura dinámica para convertir un valor SUPER. Convertir un valor SUPER en un tipo incorrecto devuelve el valor nulo en lugar de un error si la conversión no es válida. Por ejemplo, la siguiente consulta devuelve el valor nulo porque no puede convertir el valor de cadena “Good” del atributo de objeto o_orderstatus en INTEGER. Amazon Redshift devuelve un error para una conversión de VARCHAR en INTEGER, pero no para una conversión SUPER.

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

Tipos de introspección

Las columnas de datos SUPER admiten funciones de inspección que devuelven el tipo dinámico y otra información del tipo relativa al valor SUPER. El ejemplo más común es la función escalar JSON_TYPEOF que devuelve un VARCHAR con valores booleanos, de números, de cadenas, de objetos, de matrices o nulos, dependiendo del tipo dinámico del valor SUPER. Amazon Redshift admite las siguientes funciones booleanas para las columnas de datos SUPER:

Todas estas funciones devuelven un valor false si el valor de entrada es nulo. IS_SCALAR, IS_OBJECT e IS_ARRAY son mutuamente excluyentes y cubren todos los valores posibles, excepto los nulos.

Para inferir los tipos correspondientes a los datos, Amazon Redshift utiliza la función JSON_TYPEOF, que devuelve el tipo (el nivel superior) del valor SUPER, como se muestra en el siguiente ejemplo:

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

Amazon Redshift ve esto como una única cadena larga, similar a insertar este valor en una columna VARCHAR en lugar de una SUPER. Como la columna es SUPER, la cadena única sigue siendo un valor SUPER válido y la diferencia se observa en 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;

Ordenar por

Amazon Redshift no define comparaciones SUPER entre valores con diferentes tipos dinámicos. El valor SUPER que es una cadena no es más pequeño ni más grande que un valor SUPER que es un número. Para utilizar cláusulas ORDER BY con columnas SUPER, Amazon Redshift define un orden total entre los diferentes tipos que se deben observar cuando Amazon Redshift clasifica los valores SUPER mediante cláusulas ORDER BY. El orden entre los tipos dinámicos es booleano, de número, de cadena, de matriz, de objeto. En el siguiente ejemplo, se muestran los órdenes de los 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 obtener más información acerca de la cláusula ORDER BY, consulte Cláusula ORDER BY.