Creación de un ROW Cambio de los nombres de campos en matrices mediante CAST Filtro de matrices mediante la notación de .Filtro de matrices con valores anidados Filtro de matrices mediante UNNEST Búsqueda de palabras clave en matrices mediante regexp_like

Consultas de matrices con tipos complejos y estructuras anidadas

A menudo sus datos de origen contienen matrices con tipos de datos complejos y estructuras anidadas. En los ejemplos de esta sección se muestra cómo cambiar el tipo de datos de un elemento, ubicar elementos dentro de matrices y encontrar palabras clave con consultas de Athena.

Creación de un ROW
Cambio de los nombres de campos en matrices mediante CAST
Filtro de matrices mediante la notación de .
Filtro de matrices con valores anidados
Filtro de matrices mediante UNNEST
Búsqueda de palabras clave en matrices mediante regexp_like

Creación de un `ROW`

nota

En los ejemplos de esta sección se utiliza ROW como medio para crear datos de muestra con los que trabajar. Cuando consulta tablas en Athena, no es necesario que cree tipos de datos de ROW, puesto que ya se han creado a partir del origen de datos. Cuando se utiliza CREATE_TABLE, Athena define un STRUCT en él, lo rellena con datos y crea el tipo de datos ROW automáticamente para cada fila del conjunto de datos. El tipo de datos ROW subyacente consiste en campos con nombre de cualquier tipo de datos SQL compatible.


WITH dataset AS (
 SELECT
   ROW('Bob', 38) AS users
 )
SELECT * FROM dataset

Esta consulta devuelve:


+-------------------------+
| users                   |
+-------------------------+
| {field0=Bob, field1=38} |
+-------------------------+

Cambio de los nombres de campos en matrices mediante `CAST`

Para cambiar el nombre de campo en una matriz que contiene valores ROW, puede ejecutar CAST en la declaración ROW:


WITH dataset AS (
  SELECT
    CAST(
      ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)
    ) AS users
)
SELECT * FROM dataset

Esta consulta devuelve:


+--------------------+
| users              |
+--------------------+
| {NAME=Bob, AGE=38} |
+--------------------+

nota

En el ejemplo anterior declara name como VARCHAR porque este es su tipo en Presto. Si declara este STRUCT en una instrucción CREATE TABLE, use el tipo String porque Hive define este tipo de datos como String.

Filtro de matrices mediante la notación de `.`

En el siguiente ejemplo, seleccione el campo accountId de la columna userIdentity de una tabla de registros de AWS CloudTrail utilizando la notación de punto . Para obtener más información, consulte Consulta de registros de AWS CloudTrail.


SELECT
  CAST(useridentity.accountid AS bigint) as newid
FROM cloudtrail_logs
LIMIT 2;

Esta consulta devuelve:


+--------------+
| newid        |
+--------------+
| 112233445566 |
+--------------+
| 998877665544 |
+--------------+

Para consultar una matriz de valores, emita esta consulta:


WITH dataset AS (
  SELECT ARRAY[
    CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER))
  ] AS users
)
SELECT * FROM dataset

Devuelve este resultado:


+-----------------------------------------------------------------+
| users                                                           |
+-----------------------------------------------------------------+
| [{NAME=Bob, AGE=38}, {NAME=Alice, AGE=35}, {NAME=Jane, AGE=27}] |
+-----------------------------------------------------------------+

Filtro de matrices con valores anidados

Las matrices grandes suelen contener estructuras anidadas y usted debe poder filtrar o buscar valores dentro de ellas.

Para definir un conjunto de datos para una matriz de valores que contiene un valor BOOLEAN anidado, emita esta consulta:


WITH dataset AS (
  SELECT
    CAST(
      ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    ) AS sites
)
SELECT * FROM dataset

Devuelve este resultado:


+----------------------------------------------------------+
| sites                                                    |
+----------------------------------------------------------+
| {HOSTNAME=aws.amazon.com, FLAGGEDACTIVITY={ISNEW=true}}  |
+----------------------------------------------------------+

A continuación, para filtrar y obtener acceso al valor BOOLEAN de este elemento, siga utilizando la notación de punto .


WITH dataset AS (
  SELECT
    CAST(
      ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    ) AS sites
)
SELECT sites.hostname, sites.flaggedactivity.isnew
FROM dataset

Esta consulta selecciona los campos anidados y devuelve este resultado:


+------------------------+
| hostname       | isnew |
+------------------------+
| aws.amazon.com | true  |
+------------------------+

Filtro de matrices mediante `UNNEST`

Para filtrar una matriz que contiene una estructura anidada por uno de sus elementos secundarios, emita una consulta con un operador UNNEST. Para obtener más información acerca de UNNEST, consulte Aplane Matrices anidadas.

Por ejemplo, esta consulta encuentra nombres de host de sitios del conjunto de datos.


WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    ),
    CAST(
      ROW('news.cnn.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    ),
    CAST(
      ROW('netflix.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN))
    )
  ] as items
)
SELECT sites.hostname, sites.flaggedActivity.isNew
FROM dataset, UNNEST(items) t(sites)
WHERE sites.flaggedActivity.isNew = true

Devuelve:


+------------------------+
| hostname       | isnew |
+------------------------+
| aws.amazon.com | true  |
+------------------------+

Búsqueda de palabras clave en matrices mediante `regexp_like`

Los siguientes ejemplos ilustran cómo buscar en un conjunto de datos una palabra clave contenida en un elemento de una matriz mediante la función regexp_like. Toma como entrada un patrón de expresión regular para evaluar o una lista de términos separados por una barra vertical (|), evalúa el patrón y determina si la cadena especificada lo contiene.

El patrón de expresión regular debe estar incluido dentro de la cadena y no debe coincidir con él. Para que coincida la cadena completa, escriba el patrón con ^ al principio del mismo y, $ al final, como por ejemplo '^pattern$'.

Suponga que tiene una matriz de sitios que contienen su nombre de host y un elemento flaggedActivity. Este elemento incluye un elemento ARRAY que contiene a su vez varios elementos MAP, cada uno de ellos con una lista de diferentes palabras clave populares y su contaje de popularidad. Supongamos que desea encontrar una determinada palabra clave dentro de un elemento MAP de esta matriz.

Para buscar este conjunto de datos para sitios con una palabra clave específica, utilizamos regexp_like en lugar del operador LIKE de SQL similar, ya que la búsqueda de un gran número de palabras clave es más eficiente con regexp_like.

ejemplo Ejemplo 1: uso de `regexp_like`

La consulta en este ejemplo utiliza la función regexp_like para buscar términos 'politics|bigdata', que pueden encontrarse en valores dentro de matrices:


WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(ARRAY[
          MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
          MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
          MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
          MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   ),
   CAST(
     ROW('news.cnn.com', ROW(ARRAY[
       MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
       MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
       MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
       MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
     ])
     ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   ),
   CAST(
     ROW('netflix.com', ROW(ARRAY[
       MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
       MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
       MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
       MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
     ])
     ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
   )
 ] AS items
),
sites AS (
  SELECT sites.hostname, sites.flaggedactivity
  FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)

Esta consulta devuelve dos sitios:


+----------------+
| hostname       |
+----------------+
| aws.amazon.com |
+----------------+
| news.cnn.com   |
+----------------+

ejemplo Ejemplo 2: uso de `regexp_like`

En la consulta del siguiente ejemplo se añade la puntuación de popularidad total de los sitios que coinciden con sus términos de búsqueda a la función regexp_like y, a continuación, se ordenan de puntuación más alta a más baja.


WITH dataset AS (
  SELECT ARRAY[
    CAST(
      ROW('aws.amazon.com', ROW(ARRAY[
          MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']),
          MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']),
          MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']),
          MAP(ARRAY['term', 'count'], ARRAY['iot', '74'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    ),
    CAST(
      ROW('news.cnn.com', ROW(ARRAY[
        MAP(ARRAY['term', 'count'], ARRAY['politics', '241']),
        MAP(ARRAY['term', 'count'], ARRAY['technology', '211']),
        MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']),
        MAP(ARRAY['term', 'count'], ARRAY['iot', '170'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    ),
    CAST(
      ROW('netflix.com', ROW(ARRAY[
        MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']),
        MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']),
        MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']),
        MAP(ARRAY['term', 'count'], ARRAY['iot', '4'])
      ])
      ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) ))
    )
  ] AS items
),
sites AS (
  SELECT sites.hostname, sites.flaggedactivity
  FROM dataset, UNNEST(items) t(sites)
)
SELECT hostname, array_agg(flags['term']) AS terms, SUM(CAST(flags['count'] AS INTEGER)) AS total
FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags)
WHERE regexp_like(flags['term'], 'politics|bigdata')
GROUP BY (hostname)
ORDER BY total DESC

Esta consulta devuelve dos sitios:


+------------------------------------+
| hostname       | terms    | total  |
+----------------+-------------------+
| news.cnn.com   | politics |  241   |
+----------------+-------------------+
| aws.amazon.com | bigdata |  10    |
+----------------+-------------------+

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Uso de matrices para crear mapas

Consulta de datos geoespaciales

Consultas de matrices con tipos complejos y estructuras anidadas

Creación de un ROW

nota

Cambio de los nombres de campos en matrices mediante CAST

nota

Filtro de matrices mediante la notación de .

Filtro de matrices con valores anidados

Filtro de matrices mediante UNNEST

Búsqueda de palabras clave en matrices mediante regexp_like

ejemplo Ejemplo 1: uso de regexp_like

ejemplo Ejemplo 2: uso de regexp_like

Creación de un `ROW`

Cambio de los nombres de campos en matrices mediante `CAST`

Filtro de matrices mediante la notación de `.`

Filtro de matrices mediante `UNNEST`

Búsqueda de palabras clave en matrices mediante `regexp_like`

ejemplo Ejemplo 1: uso de `regexp_like`

ejemplo Ejemplo 2: uso de `regexp_like`