Data models supported by ApsaraDB for SelectDB - ApsaraDB for SelectDB

ApsaraDB for SelectDB admite tres modelos de datos. Elija el adecuado antes de crear una tabla, ya que el modelo no se puede modificar después.

Modelo	Ideal para	Limitación principal
Modelo de clave de agregación	Consultas de reportes con patrones fijos y métricas preagregadas	`COUNT(*)` es costoso; cada columna de valor está vinculada a un tipo de agregación
Modelo de clave única	Datos relacionales que requieren unicidad de clave primaria (pedidos, perfiles de usuario)	No permite la preagregación ROLLUP
Modelo de clave duplicada	Consultas ad hoc, análisis de registros y almacenamiento de datos sin procesar	Sin preagregación ni deduplicación automática

Contexto

En ApsaraDB for SelectDB, las tablas se componen de filas y columnas. Las columnas se dividen en dos categorías:

Columnas de clave: corresponden a las columnas de dimensión. Se definen después de las palabras clave DUPLICATE KEY, AGGREGATE KEY o UNIQUE KEY en una instrucción CREATE TABLE.
Columnas de valor: corresponden a las columnas de métricas. Incluyen todas las columnas que no aparecen como columnas de clave.

Estas dos categorías se relacionan directamente con los tres modelos de datos.

Modelo de clave de agregación

El modelo de clave de agregación preagrega los datos durante la escritura. Cuando importa filas con valores idénticos en todas las columnas de clave, SelectDB las fusiona en una sola fila. Cada columna de valor se agrega mediante la función que especifique en la instrucción CREATE TABLE.

Utilice este modelo para consultas de reportes con patrones de agregación fijos, por ejemplo, usuarios activos diarios o ingresos por región. Si la lógica de agregación varía entre sus consultas, o si necesita que COUNT(*) sea rápido, use el modelo de clave duplicada o el modelo de clave única con MoW. Consulte Limitaciones del modelo de clave de agregación para más detalles.

Tipos de agregación

Tipo de agregación	Descripción
`SUM`	Calcula la suma de las filas. Aplicable a valores numéricos.
`MIN`	Conserva el valor mínimo. Aplicable a valores numéricos.
`MAX`	Conserva el valor máximo. Aplicable a valores numéricos.
`REPLACE`	Sustituye el valor anterior por el nuevo valor importado. En filas con las mismas columnas de clave, los valores se reemplazan según el orden de importación.
`REPLACE_IF_NOT_NULL`	Funciona igual que `REPLACE`, pero omite los valores nulos. Especifique `null` (no una cadena vacía) como valor predeterminado de la columna; de lo contrario, se sobrescribirán las cadenas vacías.
`HLL_UNION`	Agrega columnas del tipo HyperLogLog (HLL) mediante el algoritmo HLL.
`BITMAP_UNION`	Agrega columnas BITMAP mediante la agregación de unión.

Ejemplo 1: agregación básica durante la importación

La tabla example_tbl1 registra datos de visitas de usuarios. Las columnas de clave (user_id, date, city, age, sex) identifican registros únicos, mientras que las columnas de valor almacenan métricas.

Nombre de la columna	Tipo	Tipo de agregación	Comentario
user_id	LARGEINT	N/A	El ID del usuario.
date	DATE	N/A	La fecha en la que se escriben los datos en la tabla.
city	VARCHAR(20)	N/A	La ciudad donde reside el usuario.
age	SMALLINT	N/A	La edad del usuario.
sex	TINYINT	N/A	El género del usuario.
last_visit_date	DATETIME	REPLACE	La última vez que el usuario realizó una visita.
cost	BIGINT	SUM	La cantidad de dinero que gasta el usuario.
max_dwell_time	INT	MAX	El tiempo máximo de permanencia del usuario.
min_dwell_time	INT	MIN	El tiempo mínimo de permanencia del usuario.

CREATE TABLE IF NOT EXISTS test.example_tbl1
(
    `user_id` LARGEINT NOT NULL COMMENT "The user ID",
    `date` DATE NOT NULL COMMENT "The date on which data is written to the table",
    `city` VARCHAR(20) COMMENT "The city in which the user resides",
    `age` SMALLINT COMMENT "The age of the user",
    `sex` TINYINT COMMENT "The gender of the user",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "The last time when the user paid a visit",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "The amount of money that the user spends",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "The maximum dwell time of the user",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "The minimum dwell time of the user"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

Inserte a continuación las siguientes filas:

user_id	date	city	age	sex	last_visit_date	cost	max_dwell_time	min_dwell_time
10000	2017-10-01	Beijing	20	0	2017-10-01 06:00:00	20	10	10
10000	2017-10-01	Beijing	20	0	2017-10-01 07:00:00	15	2	2
10001	2017-10-01	Beijing	30	1	2017-10-01 17:05:45	2	22	22
10002	2017-10-02	Shanghai	20	1	2017-10-02 12:59:12	200	5	5
10003	2017-10-02	Guangzhou	32	0	2017-10-02 11:20:00	30	11	11
10004	2017-10-01	Shenzhen	35	0	2017-10-01 10:00:15	100	3	3
10004	2017-10-03	Shenzhen	35	0	2017-10-03 10:20:22	11	6	6

INSERT INTO example_db.example_tbl_agg1 VALUES
(10000,"2017-10-01","Beijing",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","Beijing",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","Beijing",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","Shanghai",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","Guangzhou",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","Shenzhen",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","Shenzhen",35,0,"2017-10-03 10:20:22",11,6,6);

Después de la importación, SelectDB almacena una sola fila agregada para el usuario 10000, ya que las filas 1 y 2 comparten las mismas columnas de clave. Los demás usuarios tenían claves únicas, por lo que sus filas se guardan sin cambios:

user_id	date	city	age	sex	last_visit_date	cost	max_dwell_time	min_dwell_time
10000	2017-10-01	Beijing	20	0	2017-10-01 07:00:00	35	10	2
10001	2017-10-01	Beijing	30	1	2017-10-01 17:05:45	2	22	22
10002	2017-10-02	Shanghai	20	1	2017-10-02 12:59:12	200	5	5
10003	2017-10-02	Guangzhou	32	0	2017-10-02 11:20:00	30	11	11
10004	2017-10-01	Shenzhen	35	0	2017-10-01 10:00:15	100	3	3
10004	2017-10-03	Shenzhen	35	0	2017-10-03 10:20:22	11	6	6

Así se agregó la fila del usuario 10000:

last_visit_date (REPLACE): 2017-10-01 06:00:00 se sustituye por 2017-10-01 07:00:00.

Cuando se aplica el tipo REPLACE dentro del mismo lote de importación, no se garantiza el orden de sustitución. El valor almacenado podría ser cualquiera de las dos marcas de tiempo. Entre lotes distintos, siempre prevalece el más reciente.
cost (SUM): 20 + 15 = 35.
max_dwell_time (MAX): max(10, 2) = 10.
min_dwell_time (MIN): min(10, 2) = 2.

Tras la agregación, las filas originales sin procesar dejan de existir en el almacenamiento.

Ejemplo 2: agregar datos nuevos con datos existentes

Este ejemplo muestra cómo SelectDB agrega un segundo lote de importación sobre datos ya almacenados.

Cree example_tbl2 (con el mismo esquema que example_tbl1):

CREATE TABLE IF NOT EXISTS test.example_tbl2
(
    `user_id` LARGEINT NOT NULL COMMENT "The user ID",
    `date` DATE NOT NULL COMMENT "The date on which data is written to the table",
    `city` VARCHAR(20) COMMENT "The city in which the user resides",
    `age` SMALLINT COMMENT "The age of the user",
    `sex` TINYINT COMMENT "The gender of the user",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "The last time when the user paid a visit",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "The amount of money that the user spends",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "The maximum dwell time of the user",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "The minimum dwell time of the user"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

Importe el lote 1:

INSERT INTO test.example_tbl2 VALUES
(10000,"2017-10-01","Beijing",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","Beijing",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","Beijing",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","Shanghai",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","Guangzhou",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","Shenzhen",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","Shenzhen",35,0,"2017-10-03 10:20:22",11,6,6);

Importe el lote 2 (agrega datos nuevos para el usuario 10004 e introduce al usuario 10005):

INSERT INTO test.example_tbl2 VALUES
(10004,"2017-10-03","Shenzhen",35,0,"2017-10-03 11:22:00",44,19,19),
(10005,"2017-10-03","Changsha",29,1,"2017-10-03 18:11:02",3,1,1);

Estado final almacenado:

user_id	date	city	age	sex	last_visit_date	cost	max_dwell_time	min_dwell_time
10000	2017-10-01	Beijing	20	0	2017-10-01 07:00:00	35	10	2
10001	2017-10-01	Beijing	30	1	2017-10-01 17:05:45	2	22	22
10002	2017-10-02	Shanghai	20	1	2017-10-02 12:59:12	200	5	5
10003	2017-10-02	Guangzhou	32	0	2017-10-02 11:20:00	30	11	11
10004	2017-10-01	Shenzhen	35	0	2017-10-01 10:00:15	100	3	3
10004	2017-10-03	Shenzhen	35	0	2017-10-03 11:22:00	55	19	6
10005	2017-10-03	Changsha	29	1	2017-10-03 18:11:02	3	1	1

La fila del usuario 10004 correspondiente a 2017-10-03 se agrega con ambos lotes (cost: 11 + 44 = 55; max_dwell_time: max(6, 19) = 19; min_dwell_time: min(6, 19) = 6). Como el usuario 10005 es nuevo, su fila se inserta tal cual.

La agregación en ApsaraDB for SelectDB ocurre en tres etapas:

Etapa ETL (extracción, transformación y carga): cada lote de importación se agrega internamente antes de escribirse.
Etapa de compactación de datos: el clúster de cómputo combina los datos de distintos lotes de importación en segundo plano.
Etapa de consulta: los datos que aún no se han agregado se procesan durante la ejecución de la consulta.

El grado de agregación en cualquier momento es transparente. Considere siempre que los datos consultados están completamente agregados.

Ejemplo 3: conservar datos detallados con el modelo de clave de agregación

Si añade una columna de alta cardinalidad a las columnas de clave, evita que las filas compartan la misma clave. De esta forma conserva todos los datos sin procesar y aprovecha el modelo de clave de agregación.

Agregue una columna timestamp (con precisión de segundos) a la clave:

CREATE TABLE IF NOT EXISTS test.example_tbl3
(
    `user_id` LARGEINT NOT NULL COMMENT "The user ID",
    `date` DATE NOT NULL COMMENT "The date on which data is written to the table",
    `timestamp` DATETIME NOT NULL COMMENT "The time when data is written to the table, which is accurate to seconds",
    `city` VARCHAR(20) COMMENT "The city in which the user resides",
    `age` SMALLINT COMMENT "The age of the user",
    `sex` TINYINT COMMENT "The gender of the user",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "The last time when the user paid a visit",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "The amount of money that the user spends",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "The maximum dwell time of the user",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "The minimum dwell time of the user"
)
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

Como cada fila tiene un timestamp único, ninguna comparte las mismas columnas de clave, por lo que no se realiza ninguna agregación. Todos los datos sin procesar se conservan intactos.

Limitaciones del modelo de clave de agregación

Dado que el modelo de clave de agregación siempre presenta datos totalmente agregados, algunas consultas generan resultados inesperados o implican un costo elevado.

Sobrecarga de COUNT(*)

Considere la tabla example_tbl8 con el siguiente esquema:

Nombre de la columna	Tipo	Tipo de agregación	Comentario
user_id	LARGEINT	N/A	El ID del usuario.
date	DATE	N/A	La fecha en la que se escriben los datos en la tabla.
cost	BIGINT	SUM	La cantidad de dinero que gasta el usuario.

Importe dos lotes:

Lote 1:

user_id	date	cost
10001	2017-11-20	50
10002	2017-11-21	39

Lote 2:

user_id	date	cost
10001	2017-11-20	1
10001	2017-11-21	5
10003	2017-11-22	22

Antes de la compactación, las cinco filas originales podrían seguir existiendo en el almacenamiento subyacente. Al ejecutar COUNT(*):

SELECT COUNT(*) FROM example_tbl8;

El resultado esperado es 4 (cuatro combinaciones únicas de clave). Sin embargo, si SelectDB solo examina la columna user_id y omite la agregación, devuelve 3. Si cuenta todas las filas sin compactar, devuelve 5. Ambos resultados son incorrectos.

Para obtener el resultado correcto, el motor de consultas debe examinar todas las columnas de clave (user_id y date) y agregarlas. Esto implica un escaneo completo de claves en cada llamada a COUNT(*).

Solución alternativa: añada una columna count con un valor fijo de 1 y el tipo de agregación SUM:

Nombre de la columna	Tipo	Tipo de agregación	Comentario
user_id	BIGINT	N/A	El ID del usuario.
date	DATE	N/A	La fecha en la que se escriben los datos en la tabla.
cost	BIGINT	SUM	La cantidad de dinero que gasta el usuario.
count	BIGINT	SUM	Contador de filas.

La consulta SELECT SUM(count) FROM table; equivale a SELECT COUNT(*) FROM table; y se ejecuta mucho más rápido. Tenga en cuenta que este método no permite volver a importar filas con las mismas columnas de clave, ya que inflaría el contador.

Como alternativa, establezca el tipo de agregación de la columna count en REPLACE con un valor fijo de 1. Esto produce el mismo resultado que COUNT(*) y permite volver a importar claves duplicadas.

Semántica de las consultas de agregación

Cada columna de valor se vincula a un tipo de agregación cuando se crea la tabla. Si ejecuta una función de agregación distinta sobre esa columna, podría obtener resultados semánticamente incorrectos. Diseñe el esquema con base en los tipos de agregación que necesite.

Modelo de clave única

A diferencia del modelo de clave de agregación, el modelo de clave única impone la unicidad de la clave primaria en lugar de agregar valores. Cuando llegan filas nuevas con las mismas columnas de clave que filas existentes, solo se conserva la última fila importada.

Utilice este método para datos relacionales con requisitos de unicidad, como pedidos o perfiles de usuario. No es compatible con la preagregación ROLLUP.

ApsaraDB for SelectDB ofrece dos métodos de implementación:

Merge on Write (MoW): elimina duplicados durante la etapa de importación. Recomendado para obtener el mejor rendimiento en consultas.
Merge on Read (MoR): aplica la deduplicación durante la consulta.

MoW (recomendado)

MoW marca como eliminadas las filas sobrescritas durante la importación y escribe directamente los datos más recientes. Al consultar, las filas eliminadas se filtran sin ninguna operación de combinación. Esto elimina la sobrecarga de agregación de MoR y habilita la inserción de predicados (predicate pushdown) en la mayoría de los escenarios, lo que acelera las consultas de agregación.

MoW está deshabilitado de forma predeterminada en ApsaraDB for SelectDB V3.0. Habilítelo al configurar "enable_unique_key_merge_on_write" = "true" cuando cree la tabla.

CREATE TABLE IF NOT EXISTS test.example_tbl6
(
    `user_id` LARGEINT NOT NULL COMMENT "The user ID",
    `username` VARCHAR(50) NOT NULL COMMENT "The nickname of the user",
    `city` VARCHAR(20) COMMENT "The city in which the user resides",
    `age` SMALLINT COMMENT "The age of the user",
    `sex` TINYINT COMMENT "The gender of the user",
    `phone` LARGEINT COMMENT "The phone number of the user",
    `address` VARCHAR(500) COMMENT "The address of the user",
    `register_time` DATETIME COMMENT "The time when the user is registered"
)
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true"
);

MoR no se puede actualizar a MoW de forma transparente porque organizan los datos de manera distinta. Para migrar, utilice INSERT INTO unique-mow-table SELECT * FROM source_table para importar los datos existentes a una nueva tabla MoW.

La firma de eliminación única y la columna de secuencia siguen funcionando aunque MoW esté habilitado.

MoR

MoR aplica la lógica de deduplicación durante la consulta al combinar los datos en la lectura. Equivale al modelo de clave de agregación con todas las columnas de valor configuradas en REPLACE.

CREATE TABLE IF NOT EXISTS test.example_tbl4
(
    `user_id` LARGEINT NOT NULL COMMENT "The user ID",
    `username` VARCHAR(50) NOT NULL COMMENT "The nickname of the user",
    `city` VARCHAR(20) COMMENT "The city in which the user resides",
    `age` SMALLINT COMMENT "The age of the user",
    `sex` TINYINT COMMENT "The gender of the user",
    `phone` LARGEINT COMMENT "The phone number of the user",
    `address` VARCHAR(500) COMMENT "The address of the user",
    `register_time` DATETIME COMMENT "The time when the user is registered"
)
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

El modelo de clave única MoR anterior es internamente idéntico al siguiente modelo de clave de agregación, donde todas las columnas de valor usan REPLACE:

CREATE TABLE IF NOT EXISTS test.example_tbl5
(
    `user_id` LARGEINT NOT NULL COMMENT "The user ID",
    `username` VARCHAR(50) NOT NULL COMMENT "The nickname of the user",
    `city` VARCHAR(20) REPLACE COMMENT "The city in which the user resides",
    `age` SMALLINT REPLACE COMMENT "The age of the user",
    `sex` TINYINT REPLACE COMMENT "The gender of the user",
    `phone` LARGEINT REPLACE COMMENT "The phone number of the user",
    `address` VARCHAR(500) REPLACE COMMENT "The address of the user",
    `register_time` DATETIME REPLACE COMMENT "The time when the user is registered"
)
AGGREGATE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

Modelo de clave duplicada

El modelo de clave duplicada se distingue de los otros dos porque almacena todas las filas exactamente como se importan, sin agregación ni validación de unicidad. Varias filas con los mismos valores en las columnas de clave coexisten sin afectarse entre sí.

Recomendado para escenarios de consultas ad hoc sobre datos sin procesar, como registros, donde requiere todo el detalle y no necesita deduplicación ni preagregación.

Las columnas de clave especificadas en la instrucción CREATE TABLE actúan únicamente como claves de ordenamiento, no como identificadores únicos. Le recomendamos seleccionar entre las dos y las cuatro primeras columnas como clave duplicada.

CREATE TABLE IF NOT EXISTS test.example_tbl7
(
    `timestamp` DATETIME NOT NULL COMMENT "The time when the log was generated",
    `type` INT NOT NULL COMMENT "The type of the log",
    `error_code` INT COMMENT "The error code",
    `error_msg` VARCHAR(1024) COMMENT "The error message",
    `op_id` BIGINT COMMENT "The owner ID",
    `op_time` DATETIME COMMENT "The time when the error was handled"
)
DUPLICATE KEY(`timestamp`, `type`, `error_code`)
DISTRIBUTED BY HASH(`type`) BUCKETS 16;

Columna	Tipo	Clave de ordenamiento	Comentario
timestamp	DATETIME	Sí	La hora en la que se generó el registro.
type	INT	Sí	El tipo de registro.
error_code	INT	Sí	El código de error.
error_msg	VARCHAR(1024)	No	El mensaje de error.
op_id	BIGINT	No	El ID del propietario.
op_time	DATETIME	No	La hora en la que se gestionó el error.

Columnas de clave en los distintos modelos

Las columnas de clave cumplen funciones distintas según el modelo:

Modelo	Función de las columnas de clave
Modelo de clave duplicada	Solo claves de ordenamiento, no identificadores únicos
Modelo de clave de agregación	Claves de ordenamiento e identificadores únicos
Modelo de clave única	Claves de ordenamiento e identificadores únicos

Elegir un modelo de datos

Escenario	Modelo recomendado
Reportes de agregación con patrones fijos (por ejemplo, usuarios activos diarios o ingresos por región)	Modelo de clave de agregación
Datos relacionales con restricciones de clave primaria (por ejemplo, pedidos o perfiles de usuario)	Modelo de clave única (MoW)
Consultas de agregación de alto rendimiento sobre datos con clave primaria	Modelo de clave única con MoW habilitado
Análisis de datos sin procesar, consultas de registros y exploración ad hoc	Modelo de clave duplicada
Actualizaciones parciales de columnas	Modelo de clave única; consulte Partial Update

Cuándo evitar el modelo de clave de agregación: evítelo si sus consultas COUNT(*) deben ser rápidas, o si necesita ejecutar funciones de agregación distintas a los tipos definidos al crear la tabla. Este modelo solo resulta eficiente cuando las filas se reducen de manera significativa. Si la mayoría de las filas tienen claves únicas, la sobrecarga de la preagregación supera cualquier ventaja.

Cómo MoW mejora el rendimiento de COUNT(*)

MoW utiliza un mapa de bits de eliminación (delete bitmap) para rastrear las filas sobrescritas en lugar de agregar durante la consulta. Con el mismo escenario de dos lotes descrito en la limitación del modelo de clave de agregación:

Después del lote 1:

user_id	date	cost	bit de eliminación
10001	2017-11-20	50	false
10002	2017-11-21	39	false

Después del lote 2 (la fila duplicada del lote 1 se marca como eliminada):

user_id	date	cost	bit de eliminación
10001	2017-11-20	50	true
10002	2017-11-21	39	false

user_id	date	cost	bit de eliminación
10001	2017-11-20	1	false
10001	2017-11-21	5	false
10003	2017-11-22	22	false

COUNT(*) omite las filas con delete bit = true y examina una sola columna, por lo que devuelve 4 correctamente y con una sobrecarga mínima. En el entorno de pruebas, las consultas COUNT(*) que utilizan el método de implementación MoW del modelo de clave única ofrecen un rendimiento 10 veces mayor que las del modelo de clave de agregación.