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.
Temas
Informació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 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. 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
Para crear la tabla externa para este tutorial, ejecute el siguiente 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/';
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.
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<struct<nestedarray: array<struct<morenestedarray: array<string>>>>>
También puede anidar los tipos struct tal como se muestra para la columna x en el siguiente ejemplo.
x struct<a: string, b: struct<c: integer, d: struct<e: string> > >
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<a: string, b: struct<c: integer, d: struct<e: string> > >
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<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 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<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/';
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.
