Amazon Redshift dejará de admitir la creación de nuevas UDF de Python a partir del parche 198. Las UDF de Python existentes seguirán funcionando hasta el 30 de junio de 2026. Para obtener más información, consulte la [publicación del blog](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Tutorial: Consulta de datos anidados con Amazon Redshift Spectrum En este tutorial se muestra cómo consultar datos anidados con Redshift Spectrum. Los datos anidados son datos que contienen campos anidados. Los campos anidados son campos que se unen como una sola entidad, por ejemplo, matrices, estructuras u objetos. **Topics** + [Descripción general](#tutorial-nested-data-overview) + [Paso 1: Crear una tabla externa que contenga datos anidados](#tutorial-nested-data-create-table) + [Paso 2: Consultar los datos anidados en Amazon S3 con extensiones SQL](#tutorial-query-nested-data-sqlextensions) + [Casos de uso de datos anidados](nested-data-use-cases.md) + [Limitaciones de los datos anidados (versión preliminar)](nested-data-restrictions.md) + [Serialización de datos JSON anidados y complejos](serializing-complex-JSON.md) ## Descripción general Amazon Redshift Spectrum admite la consulta de datos anidados en los formatos de archivos Parquet, ORC, JSON e Ion. Redshift Spectrum obtiene acceso a los datos mediante el uso de tablas externas. Puede crear tablas externas que usen los tipos de datos complejos `struct`, `array` y `map`. Supongamos, por ejemplo, que su archivo de datos contiene los siguientes datos en Amazon S3 en una carpeta llamada `customers`. Aunque no hay un solo elemento raíz, cada objeto JSON de estos datos de ejemplo representa una fila en una tabla. ``` {"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}] } ``` Puede usar Amazon Redshift Spectrum para consultar datos anidados en archivos. El siguiente tutorial le muestra cómo hacerlo con los datos de Apache Parquet. ### Requisitos previos Si aún no usa Redshift Spectrum, siga los pasos en el tutorial de [Introducción a Amazon Redshift Spectrum](c-getting-started-using-spectrum.md) antes de continuar. Para crear un esquema externo, reemplace el ARN del rol de IAM en el siguiente comando por el ARN del rol que creó en [Creación de un rol de IAM](c-getting-started-using-spectrum.md#c-getting-started-using-spectrum-create-role). Luego, ejecute el comando en 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; ``` ## Paso 1: Crear una tabla externa que contenga datos anidados Puede ver el [origen de datos](https://s3.amazonaws.com/redshift-downloads/tickit/spectrum/customers/customer_file1) descargándolo de Amazon S3. Para crear la tabla externa para este tutorial, ejecute el siguiente comando. ``` CREATE EXTERNAL TABLE spectrum.customers ( id int, name struct, phones array, orders array> ) STORED AS PARQUET LOCATION 's3://redshift-downloads/tickit/spectrum/customers/'; ``` En el ejemplo anterior, la tabla externa `spectrum.customers` utiliza los tipos de datos `struct` y `array` para definir columnas con datos anidados. Amazon Redshift Spectrum admite la consulta de datos anidados en los formatos de archivos Parquet, ORC, JSON e Ion. El parámetro `STORED AS` es `PARQUET` para los archivos de Apache Parquet. El parámetro `LOCATION` tiene que hacer referencia a la carpeta de Amazon S3 que contiene los datos o los archivos anidados. Para obtener más información, consulte [CREATE EXTERNAL TABLE](r_CREATE_EXTERNAL_TABLE.md). Puede anidar tipos `array` y `struct` en cualquier nivel. Por ejemplo, puede definir una columna denominada `toparray` tal como se muestra en el siguiente ejemplo. ``` toparray array>>>> ``` También puede anidar los tipos `struct` tal como se muestra para la columna `x` en el siguiente ejemplo. ``` x struct > > ``` ## Paso 2: Consultar los datos anidados en Amazon S3 con extensiones SQL Redshift Spectrum permite consultar los tipos complejos `array`, `map` y `struct` mediante extensiones de la sintaxis SQL de Amazon Redshift. ### Extensión 1: Acceso a columnas de estructuras Puede extraer datos de columnas de `struct` mediante una notación de puntos que concatene nombres de campos para crear rutas. Por ejemplo, la siguiente consulta devuelve los nombres y los apellidos de los clientes. El acceso al nombre se consigue con la ruta larga `c.name.given`. El acceso al apellido se consigue con la ruta larga `c.name.family`. ``` SELECT c.id, c.name.given, c.name.family FROM spectrum.customers c; ``` La consulta anterior devuelve los siguientes datos. ``` id | given | family ---|-------|------- 1 | John | Smith 2 | Jenny | Doe 3 | Andy | Jones (3 rows) ``` Una `struct` puede ser una columna de otra `struct`, que a su vez puede ser una columna de otra `struct`, en cualquier nivel. Las rutas que ofrecen acceso a columnas en `struct` profundamente anidadas pueden ser arbitrariamente largas. Por ejemplo, consulte la definición de la columna `x` a continuación. ``` x struct > > ``` Puede obtener acceso a los datos en `e` como `x.b.d.e`. ### Extensión 2: Desplazamiento por matrices en una cláusula FROM Puede extraer datos de columnas `array` (y, por extensión, de columnas `map`) especificando las columnas `array` en una cláusula `FROM` en lugar de nombres de tablas. La extensión se aplica a la cláusula `FROM` de la consulta principal y también a las cláusulas `FROM` de las subconsultas. Puede hacer referencia a los elementos `array` por posición como, por ejemplo, `c.orders[0]`. (versión preliminar) Mediante la combinación del desplazamiento por `arrays` con las uniones, se pueden conseguir varios tipos de aplanamientos, tal como se explica en los siguientes casos de uso. #### Aplanamiento mediante combinaciones interiores La siguiente consulta selecciona los ID de cliente y las fechas de envío de los pedidos para los clientes que tienen pedidos. La extensión SQL en la cláusula FROM `c.orders o` depende del alias `c`. ``` SELECT c.id, o.shipdate FROM spectrum.customers c, c.orders o ``` Para cada cliente `c` que tiene pedidos, la cláusula `FROM` devuelve una fila para cada pedido `o` del cliente `c`. La fila combina la fila de cliente `c` y la fila de pedido `o`. A continuación, la cláusula `SELECT` solo conserva `c.id` y `o.shipdate`. El resultado es el siguiente. ``` 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) ``` El alias `c` proporciona acceso a los campos del cliente y el alias `o` proporciona acceso a los campos de los pedidos. Las semánticas son similares al SQL estándar. Puede considerar una cláusula `FROM` como si ejecutara el siguiente bucle anidado, seguido de `SELECT` para elegir los campos de la salida. ``` for each customer c in spectrum.customers for each order o in c.orders output c.id and o.shipdate ``` Por consiguiente, si un cliente no tuviera un pedido, no aparecería en el resultado. También puede considerar esto como la cláusula `FROM` que realiza una `JOIN` con la tabla `customers` y la matriz `orders`. De hecho, también puede escribir la consulta tal como se muestra en el siguiente ejemplo. ``` SELECT c.id, o.shipdate FROM spectrum.customers c INNER JOIN c.orders o ON true ``` **nota** Si existe un esquema denominado `c` con una tabla llamada `orders`, entonces `c.orders` hará referencia a la tabla `orders` y no a la columna matriz de `customers`. #### Aplanamiento mediante combinaciones izquierdas La siguiente consulta devuelve todos los nombres de los clientes y sus pedidos. Si un cliente no ha realizado ningún pedido, también se devolverá el nombre del cliente. No obstante, en este caso, las columnas de pedido son NULL, tal y como se muestra en el siguiente ejemplo 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 ``` La consulta anterior devuelve los siguientes datos. ``` 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) ``` ### Extensión 3: Acceso a una matriz de escalares directamente mediante un alias Cuando un alias `p` en una cláusula `FROM` toma valores de una matriz de escalares, la consulta hace referencia a los valores de `p` como `p`. Por ejemplo, la siguiente consulta devuelve pares de nombres de clientes y números de teléfono. ``` SELECT c.name.given, c.name.family, p AS phone FROM spectrum.customers c LEFT JOIN c.phones p ON true ``` La consulta anterior devuelve los siguientes datos. ``` given | family | phone -------|----------|----------- John | Smith | 123-4577891 Jenny | Doe | 858-8675309 Jenny | Doe | 415-9876543 Andy | Jones | (4 rows) ``` ### Extensión 4: Acceso a elementos de asignaciones Redshift Spectrum trata el tipo de dato `map` como un tipo `array` que contiene tipos `struct` con una columna `key` y una columna `value`. La columna `key` debe ser `scalar`; el valor puede ser cualquier tipo de dato. Por ejemplo, el siguiente código crea una tabla externa con `map` para almacenar números de teléfono. ``` CREATE EXTERNAL TABLE spectrum.customers2 ( id int, name struct, phones map, orders array> ) STORED AS PARQUET LOCATION 's3://redshift-downloads/tickit/spectrum/customers/'; ``` Como un tipo `map` se comporta igual que un tipo `array` con columnas `key` y `value`, puede considerar los anteriores esquemas como si fueran el siguiente. ``` CREATE EXTERNAL TABLE spectrum.customers3 ( id int, name struct, phones array>, orders array> ) STORED AS PARQUET LOCATION 's3://redshift-downloads/tickit/spectrum/customers/'; ``` La siguiente consulta devuelve los nombres de clientes con un número de teléfono móvil, y devuelve el número para cada nombre. La consulta de asignaciones se trata como si fuera el equivalente a consultar una `array` anidada de tipos `struct`. La siguiente consulta solo devolverá datos si creó la tabla externa tal y como se describió anteriormente. ``` SELECT c.name.given, c.name.family, p.value FROM spectrum.customers c, c.phones p WHERE p.key = 'mobile'; ``` **nota** La columna `key` para una `map` es una `string` para los tipos de archivo de Ion y JSON. # Casos de uso de datos anidados En este tema se describen casos de uso de datos anidados. Los datos anidados son datos que contienen campos anidados. Los campos anidados son campos que se unen como una sola entidad, por ejemplo, matrices, estructuras u objetos. Puede combinar las extensiones descritas anteriormente con las características SQL habituales. Los siguientes casos de uso ilustran algunas combinaciones comunes. Estos ejemplos muestran cómo puede usar los datos anidados. No forman parte del tutorial. **Topics** + [Obtención de datos anidados](#ingesting-nested-data) + [Agregación de datos anidados con subconsultas](#aggregating-with-subquery) + [Combinación de datos de Amazon Redshift y datos anidados](#joining-redshift-data) ## Obtención de datos anidados Puede usar una instrucción `CREATE TABLE AS` para obtener datos de una tabla externa que contiene tipos de datos complejos. La siguiente consulta extrae todos los clientes y sus números de teléfono de la tabla externa mediante la instrucción `LEFT JOIN` y los almacena en la tabla `CustomerPhones` de 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; ``` ## Agregación de datos anidados con subconsultas Puede usar una subconsulta para agregar datos anidados. El siguiente ejemplo ilustra este enfoque. ``` SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount FROM spectrum.customers c; ``` Se devuelven los siguientes datos. ``` given | family | ordercount --------|----------|-------------- Jenny | Doe | 0 John | Smith | 2 Andy | Jones | 1 (3 rows) ``` **nota** Cuando agrega datos anidados agrupándolos por la fila principal, la forma más eficiente es la que se muestra en el ejemplo anterior. En dicho ejemplo, las filas anidadas de `c.orders` están agrupadas por su fila principal `c`. De forma alternativa, si sabe que el `id` es único para cada `customer` y `o.shipdate` nunca es nulo, puede realizar la agregación tal y como se muestra en el siguiente ejemplo. Sin embargo, este enfoque no es en general tan eficiente como el del ejemplo 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; ``` También puede escribir la consulta usando una subconsulta en la cláusula `FROM` que haga referencia a un alias (`c`) de la consulta antecesora y que extraiga datos de matriz. El siguiente ejemplo demuestra esta estrategia. ``` SELECT c.name.given, c.name.family, s.count AS ordercount FROM spectrum.customers c, (SELECT count(*) AS count FROM c.orders o) s; ``` ## Combinación de datos de Amazon Redshift y datos anidados También puede combinar datos de Amazon Redshift con datos anidados en una tabla externa. Por ejemplo, suponga que tiene los siguientes datos anidados en Amazon S3. ``` CREATE EXTERNAL TABLE spectrum.customers2 ( id int, name struct, phones array, orders array> ); ``` Imagine también que tiene la siguiente tabla en Amazon Redshift. ``` CREATE TABLE prices ( id int, price double precision ); ``` La siguiente consulta encuentra el número total y la cantidad de las compras de cada cliente en base a lo anterior. El siguiente ejemplo es solo una muestra. Solo devolverá datos si creó las tablas tal y como se describió 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; ``` # Limitaciones de los datos anidados (versión preliminar) En este tema se describen las limitaciones de la lectura de datos anidados con Redshift Spectrum. Los datos anidados son datos que contienen campos anidados. Los campos anidados son campos que se unen como una sola entidad, por ejemplo, matrices, estructuras u objetos. **nota** Las limitaciones marcadas (versión preliminar) en la siguiente lista solo se aplican a la versión preliminar de los clústeres en las siguientes regiones. Este de EE. UU. (Ohio) (us-east-2) Este de EE. UU. (Norte de Virginia) (us-east-1) EE. UU. Oeste (Norte de California) (us-west-1) Asia-Pacífico (Tokio) (ap-northeast-1) Europa (Irlanda) (eu-west-1) Europa (Estocolmo) (eu-north-1) Para obtener información sobre la configuración de clústeres de versión preliminar, consulte [Crear un clúster de versión preliminar](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-clusters-console.html#cluster-preview) en la *Guía de administración de Amazon Redshift*. Las limitaciones siguientes son aplicables a los datos anidados: + Un tipo `array` o `map` puede contener otros tipos `array` o `map` siempre y cuando las consultas que estén en las `arrays` o `maps` anidadas no devuelvan valores `scalar`. (versión preliminar) + Amazon Redshift Spectrum solo admite tipos de datos complejos como tablas externas. + Las columnas de resultados de las subconsultas deben ser de nivel superior. (versión preliminar) + Si una expresión `OUTER JOIN` hace referencia a una tabla anidada, puede hacer referencia solo a esa tabla y a sus matrices anidadas (y asignaciones). Si una expresión `OUTER JOIN` no hace referencia a una tabla anidada, puede hacer referencia a cualquier número de tablas no anidadas. + Si una cláusula `FROM` en una subconsulta hace referencia a una tabla anidada, no puede hacer referencia a ninguna otra tabla. + Si una subconsulta depende de una tabla anidada que hace referencia a una tabla principal, la subconsulta solo puede usar la tabla principal en la cláusula `FROM`. No puede usar la principal en ninguna otra cláusula, como puede ser una cláusula `SELECT` o `WHERE`. Por ejemplo, la siguiente consulta no se ejecuta porque la `SELECT` cláusula de la subconsulta hace referencia a la tabla `c` principal. ``` SELECT c.name.given FROM spectrum.customers c WHERE (SELECT COUNT(c.id) FROM c.phones p WHERE p LIKE '858%') > 1; ``` La siguiente consulta funciona porque el elemento principal `c` solo se usa en la cláusula `FROM` de la subconsulta. ``` SELECT c.name.given FROM spectrum.customers c WHERE (SELECT COUNT(*) FROM c.phones p WHERE p LIKE '858%') > 1; ``` + Una subconsulta que obtiene acceso a datos anidados desde cualquier lugar que no sea la cláusula `FROM` debe devolver un único valor. Las únicas excepciones son los operadores `(NOT) EXISTS` de las cláusulas `WHERE`. + `(NOT) IN` no se admite. + La profundidad máxima de anidamiento para todos los tipos anidados es de 100. Esta restricción se aplica a todos los formatos de archivo (Parquet, ORC, Ion y JSON). + Las subconsultas de agregación que tienen acceso a los datos anidados solo pueden hacer referencia a `arrays` y `maps` en la cláusula `FROM`, y no a una tabla externa. + No es posible realizar consultas de las pseudocolumnas de datos anidados en una tabla de Redshift Spectrum. Para obtener más información, consulte [Pseudocolumnas](c-spectrum-external-tables.md#c-spectrum-external-tables-pseudocolumns). + Al extraer datos de columnas de matrices o mapas al especificarlos en una cláusula `FROM`, solo puede seleccionar valores de esas columnas si los valores son `scalar`. Por ejemplo, las siguientes consultas intentan `SELECT` elementos de dentro de una matriz. La consulta que selecciona `arr.a` funciona porque `arr.a` es un valor `scalar`. La segunda consulta no funciona porque `array` es una matriz extraída de `s3.nested table` de la cláusula `FROM`. (versión preliminar) ``` 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; ``` No puede utilizar una matriz o un mapa en la cláusula `FROM` que, a su vez, provenga de otra matriz o mapa. Para seleccionar matrices u otras estructuras complejas que estén anidadas dentro de otras matrices, considere la posibilidad de utilizar índices en la instrucción `SELECT`. # Serialización de datos JSON anidados y complejos En este tema se muestra cómo serializar datos anidados en formato JSON. Los datos anidados son datos que contienen campos anidados. Los campos anidados son campos que se unen como una sola entidad, por ejemplo, matrices, estructuras u objetos. Una alternativa a los métodos demostrados en este tutorial es consultar columnas de recopilación anidada de nivel superior como datos JSON serializados. Puede utilizar la serialización para inspeccionar, convertir y capturar datos anidados como JSON con Redshift Spectrum. Este método es compatible con los formatos ORC, JSON, Ion y Parquet. Utilice el parámetro de configuración de sesión `json_serialization_enable` para establecer el comportamiento de serialización. Una vez que se establece, los tipos de datos JSON complejos se serializan en VARCHAR(65535). Se puede acceder a los datos JSON anidados con [Funciones JSON](json-functions.md). Para obtener más información, consulte [json\$1serialization\$1enable](r_json_serialization_enable.md). Por ejemplo, sin establecer `json_serialization_enable`, las siguientes consultas que acceden directamente a columnas anidadas producen error. ``` 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 ``` La configuración de `json_serialization_enable` permite consultar directamente las recopilaciones de nivel superior. ``` 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"} ``` Tenga en cuenta los siguientes elementos a la hora de serializar los datos JSON anidados. + Cuando las columnas de recopilación se serializan como VARCHAR(65535), no se puede acceder directamente a sus subcampos anidados como parte de la sintaxis de la consulta (por ejemplo, en la cláusula de filtro). Sin embargo, las funciones JSON se pueden usar para acceder a datos JSON anidados. + No se admiten las siguientes representaciones especializadas: + uniones ORC + mapas ORC con claves de tipo complejo + datagramas Ion + Ion SEXP + Las marcas temporales se devuelven como cadenas serializadas ISO. + Las claves de mapa primitivas se promueven a la categoría de cadena (por ejemplo, de `1` a `"1"`). + Los valores nulos de nivel superior se serializan como NULL. + Si la serialización supera el tamaño máximo de VARCHAR de 65535, la celda se establece como NULL. ## Serialización de tipos complejos que contienen cadenas JSON De manera predeterminada, los valores de cadena contenidos en recopilaciones anidadas se serializan como cadenas JSON con escape. El escape puede ser no deseado cuando las cadenas son datos JSON válidos. En cambio, es posible que desee escribir directamente como JSON subelementos anidados o campos VARCHAR. Habilite este comportamiento con la configuración de `json_serialization_parse_nested_strings` a nivel de la sesión. Cuando se establecen `json_serialization_enable` y `json_serialization_parse_nested_strings`, los valores JSON válidos se serializan en línea sin caracteres de escape. Cuando el valor no tenga el formato JSON válido, se escapa como si el valor de configuración `json_serialization_parse_nested_strings` no se hubiera establecido. Para obtener más información, consulte [json\$1serialization\$1parse\$1nested\$1strings](r_json_serialization_parse_nested_strings.md). Por ejemplo, suponga que los datos del ejemplo anterior contenían JSON como un tipo complejo de `structs` en el campo `name` VARCHAR(20): ``` name --------- {"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"} ``` Cuando se haya establecido `json_serialization_parse_nested_strings`, la columna `name` se serializa de la siguiente manera: ``` 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"} ``` En lugar del siguiente escape: ``` SET json_serialization_enable TO true; SELECT name FROM spectrum.customers order by id LIMIT 1; name --------- {"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"} ```