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
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
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 |
+----------------+-------------------+