Modelos de dados compatíveis com o ApsaraDB for SelectDB - ApsaraDB for SelectDB: Central de Documentação do Alibaba Cloud

Contexto

No ApsaraDB for SelectDB, as tabelas são compostas por linhas e colunas. As colunas dividem-se em duas categorias:

Colunas de chave: correspondem às colunas de dimensão. Defina-as após as palavras-chave DUPLICATE KEY, AGGREGATE KEY ou UNIQUE KEY em uma instrução CREATE TABLE.
Colunas de valor: correspondem às colunas de métrica. Incluem todas as colunas não listadas como colunas de chave.

Essas duas categorias mapeiam diretamente os três modelos de dados.

Modelo de chave de agregação

O modelo de chave de agregação pré-agrega os dados no momento da gravação. Ao importar linhas com valores idênticos em todas as colunas de chave, o SelectDB as consolida em uma única linha. Cada coluna de valor é agregada conforme a função especificada na instrução CREATE TABLE.

Use este modelo para consultas de relatórios com padrões de agregação fixos, como usuários ativos diários ou receita por região. Se suas consultas variarem na lógica de agregação ou se você precisar de alta performance no COUNT(*), prefira o modelo de chave duplicada ou o modelo de chave única com MoW. Consulte Limitações do modelo de chave de agregação para mais detalhes.

Tipos de agregação

Tipo de agregação	Descrição
`SUM`	Calcula a soma entre linhas. Aplicável a valores numéricos.
`MIN`	Mantém o valor mínimo. Aplicável a valores numéricos.
`MAX`	Mantém o valor máximo. Aplicável a valores numéricos.
`REPLACE`	Substitui o valor anterior pelo novo valor importado. Para linhas com as mesmas colunas de chave, a substituição segue a ordem de importação.
`REPLACE_IF_NOT_NULL`	Funciona como `REPLACE`, mas ignora valores nulos. Defina `null` (não uma string vazia) como padrão da coluna; caso contrário, strings vazias sobrescreverão os dados.
`HLL_UNION`	Agrega colunas do tipo HyperLogLog (HLL) usando o algoritmo HLL.
`BITMAP_UNION`	Agrega colunas BITMAP por meio de agregação de união.

Exemplo 1: Agregação básica na importação

A tabela example_tbl1 registra dados de visitas de usuários. As colunas de chave (user_id, date, city, age, sex) identificam registros únicos, enquanto as colunas de valor armazenam métricas.

Nome da coluna	Tipo	Tipo de agregação	Comentário
user_id	LARGEINT	N/A	O ID do usuário.
date	DATE	N/A	A data em que os dados são gravados na tabela.
city	VARCHAR(20)	N/A	A cidade onde o usuário reside.
age	SMALLINT	N/A	A idade do usuário.
sex	TINYINT	N/A	O gênero do usuário.
last_visit_date	DATETIME	REPLACE	A última vez que o usuário fez uma visita.
cost	BIGINT	SUM	O valor gasto pelo usuário.
max_dwell_time	INT	MAX	O tempo máximo de permanência do usuário.
min_dwell_time	INT	MIN	O tempo mínimo de permanência do usuário.

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;

Insira as seguintes linhas:

user_id	date	city	age	sex	last_visit_date	cost	max_dwell_time	min_dwell_time
10.000	2017-10-01	Beijing	20	0	2017-10-01 06:00:00	20	10	10
10.000	2017-10-01	Beijing	20	0	2017-10-01 07:00:00	15	2	2
10.001	2017-10-01	Beijing	30	1	2017-10-01 17:05:45	2	22	22
10.002	2017-10-02	Shanghai	20	1	2017-10-02 12:59:12	200	5	5
10.003	2017-10-02	Guangzhou	32	0	2017-10-02 11:20:00	30	11	11
10.004	2017-10-01	Shenzhen	35	0	2017-10-01 10:00:15	100	3	3
10.004	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);

Após a importação, o SelectDB armazena uma única linha agregada para o usuário 10.000, já que as linhas 1 e 2 compartilham as mesmas colunas de chave. Os demais usuários possuem chaves únicas, portanto suas linhas permanecem inalteradas:

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

Detalhamento da agregação da linha do usuário 10.000:

last_visit_date (REPLACE): 2017-10-01 06:00:00 é substituído por 2017-10-01 07:00:00.

Quando o tipo REPLACE é aplicado no mesmo lote de importação, a ordem de substituição não é garantida. O valor armazenado pode ser qualquer um dos timestamps. Entre lotes diferentes, o lote posterior sempre prevalece.
cost (SUM): 20 + 15 = 35.
max_dwell_time (MAX): max(10, 2) = 10.
min_dwell_time (MIN): min(10, 2) = 2.

Após a agregação, as linhas brutas originais deixam de existir no armazenamento.

Exemplo 2: Agregação de novos dados com dados existentes

Este exemplo demonstra como o SelectDB agrega um segundo lote de importação sobre dados já armazenados.

Crie a tabela example_tbl2 (mesmo esquema da 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 o 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 o lote 2 (adiciona novos dados para o usuário 10.004 e introduz o usuário 10.005):

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 armazenado:

user_id	date	city	age	sex	last_visit_date	cost	max_dwell_time	min_dwell_time
10.000	2017-10-01	Beijing	20	0	2017-10-01 07:00:00	35	10	2
10.001	2017-10-01	Beijing	30	1	2017-10-01 17:05:45	2	22	22
10.002	2017-10-02	Shanghai	20	1	2017-10-02 12:59:12	200	5	5
10.003	2017-10-02	Guangzhou	32	0	2017-10-02 11:20:00	30	11	11
10.004	2017-10-01	Shenzhen	35	0	2017-10-01 10:00:15	100	3	3
10.004	2017-10-03	Shenzhen	35	0	2017-10-03 11:22:00	55	19	6
10.005	2017-10-03	Changsha	29	1	2017-10-03 18:11:02	3	1	1

A linha do usuário 10.004 referente a 2017-10-03 foi agregada entre os dois lotes (cost: 11 + 44 = 55; max_dwell_time: max(6, 19) = 19; min_dwell_time: min(6, 19) = 6). Como o usuário 10.005 é novo, sua linha foi inserida sem alterações.

A agregação no ApsaraDB for SelectDB ocorre em três etapas:

Etapa de ETL (extração, transformação e carga): cada lote de importação é agregado internamente antes da gravação.
Etapa de compactação de dados: o cluster de computação mescla dados de diferentes lotes de importação em segundo plano.
Etapa de consulta: quaisquer dados restantes não agregados são consolidados durante a execução da consulta.

O nível de agregação em qualquer momento é transparente. Considere sempre que os dados consultados estão totalmente agregados.

Exemplo 3: Retenção de dados detalhados com o modelo de chave de agregação

Adicionar uma coluna de alta cardinalidade às colunas de chave impede que as linhas compartilhem a mesma chave. Isso retém efetivamente todos os dados brutos, mantendo o uso do modelo de chave de agregação.

Inclua uma coluna timestamp (com precisão de segundos) na chave:

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 linha possui um timestamp exclusivo, nenhuma linha compartilha as mesmas colunas de chave, evitando qualquer agregação. Todos os dados brutos são preservados.

Limitações do modelo de chave de agregação

Como o modelo de chave de agregação apresenta sempre dados totalmente consolidados, algumas consultas podem gerar resultados inesperados ou ter custos elevados.

Sobrecarga do COUNT(*)

Considere uma tabela example_tbl8 com o seguinte esquema:

Nome da coluna	Tipo	Tipo de agregação	Comentário
user_id	LARGEINT	N/A	O ID do usuário.
date	DATE	N/A	A data em que os dados são gravados na tabela.
cost	BIGINT	SUM	O valor gasto pelo usuário.

Importe dois lotes:

Lote 1:

user_id	date	cost
10.001	2017-11-20	50
10.002	2017-11-21	39

Lote 2:

user_id	date	cost
10.001	2017-11-20	1
10.001	2017-11-21	5
10.003	2017-11-22	22

As cinco linhas originais ainda podem existir no armazenamento subjacente antes da compactação. Ao executar COUNT(*):

SELECT COUNT(*) FROM example_tbl8;

Resultado esperado: 4 (quatro combinações de chave únicas). No entanto, se o SelectDB verificar apenas a coluna user_id e ignorar a agregação, o retorno será 3. Se contar todas as linhas não compactadas, retornará 5. Ambos os resultados estão incorretos.

Para obter o resultado correto, o mecanismo de consulta precisa examinar todas as colunas de chave (user_id e date) e agregá-las, realizando uma varredura completa das chaves a cada chamada de COUNT(*).

Solução alternativa: Adicione uma coluna count com valor fixo de 1 e tipo de agregação SUM:

Nome da coluna	Tipo	Tipo de agregação	Comentário
user_id	BIGINT	N/A	O ID do usuário.
date	DATE	N/A	A data em que os dados são gravados na tabela.
cost	BIGINT	SUM	O valor gasto pelo usuário.
count	BIGINT	SUM	Contador de linhas.

A consulta SELECT SUM(count) FROM table; equivale a SELECT COUNT(*) FROM table; e executa muito mais rápido. Note que essa abordagem não suporta reimportação de linhas com as mesmas colunas de chave, pois isso inflaria o contador.

Alternativamente, defina o tipo de agregação da coluna count como REPLACE com valor fixo de 1. Isso produz o mesmo resultado que COUNT(*) e permite a reimportação de chaves duplicadas.

Semântica de consultas agregadas

Cada coluna de valor fica vinculada a um tipo de agregação no momento da criação da tabela. Executar uma função de agregação diferente em uma coluna de valor pode retornar resultados semanticamente incorretos. Projete o esquema considerando os tipos de agregação necessários.

Modelo de chave única

O modelo de chave única difere do modelo de chave de agregação por impor unicidade na chave primária em vez de agregar valores. Quando novas linhas chegam com as mesmas colunas de chave de linhas existentes, apenas a linha importada mais recentemente é mantida.

Use este modelo para dados relacionais com requisitos de unicidade, como pedidos ou perfis de usuário. Ele não oferece suporte à pré-agregação ROLLUP.

O ApsaraDB for SelectDB fornece dois métodos de implementação:

Merge on Write (MoW): remove duplicatas durante a etapa de importação. Recomendado para melhor desempenho de consulta.
Merge on Read (MoR): aplica a deduplicação no momento da consulta.

MoW (recomendado)

O MoW marca as linhas sobrescritas como excluídas durante a importação e grava os dados mais recentes diretamente. Durante a consulta, as linhas excluídas são filtradas sem necessidade de operação de mesclagem. Isso elimina a sobrecarga de agregação do MoR e habilita o pushdown de predicados na maioria dos cenários, resultando em consultas agregadas mais rápidas.

O MoW vem desativado por padrão no ApsaraDB for SelectDB V3.0. Para ativá-lo, defina "enable_unique_key_merge_on_write" = "true" ao criar a tabela.

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"
);

Não é possível atualizar perfeitamente do MoR para o MoW devido à diferença na organização dos dados. Para migrar, utilize INSERT INTO unique-mow-table SELECT * FROM source_table para importar os dados existentes para uma nova tabela MoW.

O sinal de exclusão única e a coluna de sequência continuam funcionando mesmo com o MoW ativado.

MoR

O MoR aplica a lógica de deduplicação durante a consulta, mesclando os dados na leitura. Equivale ao modelo de chave de agregação com todas as colunas de valor definidas como 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;

O modelo de chave única MoR acima é internamente idêntico ao seguinte modelo de chave de agregação, onde todas as colunas de valor usam 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 chave duplicada

O modelo de chave duplicada difere dos outros dois por armazenar todas as linhas exatamente como foram importadas, sem agregação e sem imposição de unicidade. Múltiplas linhas com os mesmos valores nas colunas de chave coexistem sem interferir umas nas outras.

Use este modelo para consultas ad hoc em dados brutos, como logs, quando você precisa de todos os detalhes e não requer deduplicação ou pré-agregação.

As colunas de chave especificadas na instrução CREATE TABLE atuam apenas como chaves de ordenação, não como identificadores únicos. Selecione as duas a quatro primeiras colunas como chave 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;

Coluna	Tipo	Chave de ordenação	Comentário
timestamp	DATETIME	Sim	O momento em que o log foi gerado.
type	INT	Sim	O tipo do log.
error_code	INT	Sim	O código de erro.
error_msg	VARCHAR(1024)	Não	A mensagem de erro.
op_id	BIGINT	Não	O ID do proprietário.
op_time	DATETIME	Não	O momento em que o erro foi tratado.

Colunas de chave entre modelos

As colunas de chave desempenham funções diferentes dependendo do modelo:

Modelo	Função das colunas de chave
Modelo de chave duplicada	Apenas chaves de ordenação — não são identificadores únicos
Modelo de chave de agregação	Chaves de ordenação e identificadores únicos
Modelo de chave única	Chaves de ordenação e identificadores únicos

Escolha um modelo de dados

Cenário	Modelo recomendado
Relatórios de agregação com padrões fixos (por exemplo, usuários ativos diários, receita por região)	Modelo de chave de agregação
Dados relacionais com restrições de chave primária (por exemplo, pedidos, perfis de usuário)	Modelo de chave única (MoW)
Consultas agregadas de alto desempenho em dados com chave primária	Modelo de chave única com MoW ativado
Análise de dados brutos, consultas de logs, exploração ad hoc	Modelo de chave duplicada
Atualizações parciais de colunas	Modelo de chave única — consulte Atualização Parcial

Quando evitar o modelo de chave de agregação: Não utilize este modelo se suas consultas COUNT(*) precisarem ser rápidas ou se for necessário executar funções de agregação diferentes dos tipos definidos na criação da tabela. O modelo de chave de agregação só é eficiente quando há uma consolidação significativa de linhas. Se a maioria das linhas possuir chaves únicas, a sobrecarga da pré-agregação supera os benefícios.

Como o MoW melhora o desempenho do COUNT(*)

O MoW utiliza um bitmap de exclusão para rastrear linhas sobrescritas, em vez de agregar durante a consulta. Usando o mesmo cenário de dois lotes descrito na limitação do modelo de chave de agregação acima:

Após o lote 1:

user_id	date	cost	bit de exclusão
10.001	2017-11-20	50	false
10.002	2017-11-21	39	false

Após o lote 2 (a linha duplicada do lote 1 é marcada como excluída):

user_id	date	cost	bit de exclusão
10.001	2017-11-20	50	true
10.002	2017-11-21	39	false

user_id	date	cost	bit de exclusão
10.001	2017-11-20	1	false
10.001	2017-11-21	5	false
10.003	2017-11-22	22	false

O comando COUNT(*) ignora linhas com delete bit = true e verifica apenas uma coluna, retornando 4 corretamente com sobrecarga mínima. Em ambiente de teste, as consultas COUNT(*) que utilizam o método de implementação MoW do modelo de chave única apresentam desempenho 10 vezes superior em comparação ao modelo de chave de agregação.

Modelo	Mais indicado para	Limitação principal
Modelo de chave de agregação	Consultas de relatórios com padrões fixos e métricas pré-agregadas	`COUNT(*)` tem alto custo; cada coluna de valor está vinculada a um único tipo de agregação
Modelo de chave única	Dados relacionais que exigem unicidade da chave primária (pedidos, perfis de usuário)	Não permite pré-agregação ROLLUP
Modelo de chave duplicada	Consultas ad hoc, análise de logs e armazenamento de dados brutos	Sem pré-agregação; sem deduplicação automática