Ejemplos
En esta sección se incluyen ejemplos del uso de HyperLogLog con Amazon Redshift.
Temas
- Ejemplo: devolver la cardinalidad en una subconsulta
- Ejemplo: devolver un tipo HLLSKETCH a partir de bocetos combinados en una subconsulta
- Ejemplo: devolver un boceto de HyperLogLog a partir de la combinación de varios bocetos
- Ejemplo: generar bocetos de HyperLogLog a partir de datos de S3 mediante tablas externas
Ejemplo: devolver la cardinalidad en una subconsulta
En el siguiente ejemplo, se devuelve la cardinalidad de cada boceto en una subconsulta para una tabla denominada Ventas.
CREATE TABLE Sales (customer VARCHAR, country VARCHAR, amount BIGINT);
INSERT INTO Sales VALUES ('David Joe', 'Greece', 14.5), ('David Joe', 'Greece', 19.95), ('John Doe', 'USA', 29.95), ('John Doe', 'USA', 19.95), ('George Spanos', 'Greece', 9.95), ('George Spanos', 'Greece', 2.95);
La siguiente consulta genera un boceto HLL para los clientes de cada país y extrae la cardinalidad. Esto muestra clientes únicos de cada país.
SELECT hll_cardinality(sketch), country
FROM (SELECT hll_create_sketch(customer) AS sketch, country
FROM Sales
GROUP BY country) AS hll_subquery;
hll_cardinality | country
----------------+---------
1 | USA
2 | Greece
...
Ejemplo: devolver un tipo HLLSKETCH a partir de bocetos combinados en una subconsulta
En el siguiente ejemplo, se devuelve un único tipo HLLSKETCH que representa la combinación de bocetos individuales a partir de una subconsulta. Los bocetos se combinan mediante la función de agrupación HLL_COMBINE.
SELECT hll_combine(sketch)
FROM (SELECT hll_create_sketch(customer) AS sketch
FROM Sales
GROUP BY country) AS hll_subquery
hll_combine
--------------------------------------------------------------------------------------------
{"version":1,"logm":15,"sparse":{"indices":[29808639,35021072,47612452],"values":[1,1,1]}}
(1 row)
Ejemplo: devolver un boceto de HyperLogLog a partir de la combinación de varios bocetos
Para el siguiente ejemplo, suponga que la tabla page-users
almacena bocetos agrupados previamente para cada página que los usuarios visitaron en un sitio web determinado. Cada fila de esta tabla contiene un boceto de HyperLogLog que representa todos los ID de usuario que muestran las páginas visitadas.
page_users
-- +----------------+-------------+--------------+
-- | _PARTITIONTIME | page | sketch |
-- +----------------+-------------+--------------+
-- | 2019-07-28 | homepage | CHAQkAQYA... |
-- | 2019-07-28 | Product A | CHAQxPnYB... |
-- +----------------+-------------+--------------+
En el siguiente ejemplo, se unen los bocetos agrupados previamente y se genera un solo boceto. Este boceto encapsula la cardinalidad colectiva que abarca cada boceto.
SELECT hll_combine(sketch) as sketch
FROM page_users
El resultado tiene un aspecto similar al siguiente.
-- +-----------------------------------------+
-- | sketch |
-- +-----------------------------------------+
-- | CHAQ3sGoCxgCIAuCB4iAIBgTIBgqgIAgAwY.... |
-- +-----------------------------------------+
Cuando se crea un nuevo boceto, puede utilizar la función HLL_CARDINALITY para obtener los valores distintos colectivos, como se muestra a continuación.
SELECT hll_cardinality(sketch)
FROM (
SELECT
hll_combine(sketch) as sketch
FROM page_users
) AS hll_subquery
El resultado tiene un aspecto similar al siguiente.
-- +-------+
-- | count |
-- +-------+
-- | 54356 |
-- +-------+
Ejemplo: generar bocetos de HyperLogLog a partir de datos de S3 mediante tablas externas
Los siguientes ejemplos almacenan en caché los bocetos de HyperLogLog para evitar el acceso directo a Amazon S3 para la estimación de la cardinalidad.
Puede agrupar previamente y almacenar en caché bocetos de HyperLogLog en tablas externas definidas para contener datos de Amazon S3. Al hacer esto, puede extraer estimaciones de cardinalidad sin acceder a los datos base subyacentes.
Por ejemplo, suponga que ha llevado un conjunto de archivos de texto delimitado por tabulaciones en Amazon S3. Ejecuta la siguiente consulta para definir una tabla externa denominada sales
en el esquema externo de Amazon Redshift denominado spectrum
. El bucket de Amazon S3 para este ejemplo está en la Región de AWS Este de EE. UU. (Norte de Virginia).
create external table spectrum.sales(
salesid integer,
listid integer,
sellerid smallint,
buyerid smallint,
eventid integer,
dateid integer,
qtysold integer,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
row format delimited
fields terminated by '\t' stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales/';
Supongamos que desea calcular la cantidad de compradores distintos que adquirieron un elemento en fechas arbitrarias. Para ello, en el siguiente ejemplo, se generan bocetos para los ID de comprador para cada día del año y se almacena el resultado en la tabla hll_sales
de Amazon Redshift.
CREATE TABLE hll_sales AS
SELECT saletime, hll_create_sketch(buyerid) AS sketch
FROM spectrum.sales
GROUP BY saletime;
SELECT TOP 5 * FROM hll_sales;
El resultado tiene un aspecto similar al siguiente.
-- hll_sales
-- | saletime | sketch |
-- +-----------------+---------------------------------------------------------------------+
-- | 7/22/2008 8:30 | {"version":1,"logm":15,"sparse":{"indices":[9281416],"values":[1]}}
-- | 2/19/2008 0:38 | {"version":1,"logm":15,"sparse":{"indices":[48735497],"values":[3]}}
-- | 11/5/2008 4:49 | {"version":1,"logm":15,"sparse":{"indices":[27858661],"values":[1]}}
-- | 10/27/2008 4:08 | {"version":1,"logm":15,"sparse":{"indices":[65295430],"values":[2]}}
-- | 2/16/2008 9:37 | {"version":1,"logm":15,"sparse":{"indices":[56869618],"values":[2]}}
-- +---------------- +---------------------------------------------------------------------+
La siguiente consulta muestra el número estimado de compradores distintos que adquirieron un elemento durante el viernes después de Acción de Gracias en 2008.
SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers
FROM hll_sales
WHERE trunc(saletime) = '2008-11-28';
El resultado tiene un aspecto similar al siguiente.
distinct_buyers
---------------
386
Suponga que desea conocer el número de usuarios distintos que compraron un elemento en un cierto intervalo de fechas. Un ejemplo podría ser desde el viernes después de Acción de Gracias hasta el lunes siguiente. Para obtener esto, la siguiente consulta utiliza la función de agrupación hll_combine
. Esta función le permite evitar el recuento doble de los compradores que adquirieron un elemento en más de un día del rango seleccionado.
SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers
FROM hll_sales
WHERE saletime BETWEEN '2008-11-28' AND '2008-12-01';
El resultado tiene un aspecto similar al siguiente.
distinct_buyers
---------------
1166
Para mantener la tabla hll_sales
actualizada, ejecute la siguiente consulta al final de cada día. Al hacerlo, se genera un boceto de HyperLogLog en función de los ID de los compradores que adquirieron un elemento hoy y lo agregan a la tabla hll_sales
.
INSERT INTO hll_sales
SELECT saletime, hll_create_sketch(buyerid)
FROM spectrum.sales
WHERE TRUNC(saletime) = to_char(GETDATE(), 'YYYY-MM-DD')
GROUP BY saletime;