Melhores práticas de tabelas delta de armazenamento de coluna

Princípios de funcionamento

No GaussDB(DWS), os dados em uma tabela de armazenamento de coluna são armazenados por coluna. Por padrão, as 60.000 linhas em cada coluna são armazenadas em uma CU. Uma CU é a unidade mínima para armazenar dados em uma tabela de armazenamento de colunas. Depois que uma CU é gerada, os dados nela são fixos e não podem ser modificados. Não importa se um ou 60.000 registros de dados são inseridos em uma tabela de coluna de armazenamento, apenas uma CU é gerada. Quando uma pequena quantidade de dados é inserida em uma tabela de armazenamento de colunas várias vezes, ela não pode ser bem pressionada. Como resultado, ocorre o inchaço dos dados, o que afeta o desempenho da consulta e o uso do disco.

Os dados em um arquivo de CU não podem ser modificados e só podem ser anexados. Excluir os dados do arquivo de CU é marcar os dados anteriores como inválidos no dicionário. A atualização dos dados do arquivo de CU é marcar os dados antigos como inválidos e gravar um novo registro na nova CU. Se uma tabela de armazenamento de colunas for atualizada ou excluída várias vezes ou se apenas uma pequena quantidade de dados for inserida a cada vez, o espaço de tabela de armazenamento de colunas inchará e uma grande quantidade de espaço não poderá ser usada efetivamente.

As tabelas de armazenamento de colunas são projetadas para importar uma grande quantidade de dados e armazená-los por coluna para consulta. Para resolver os problemas anteriores, a tabela delta é introduzida, que é uma tabela de armazenamento de linha anexada a uma tabela de armazenamento de coluna. Depois que a tabela delta é habilitada, quando um único dado ou um pequeno lote de dados é importado, os dados são armazenados na tabela delta para evitar pequenas CUs. A adição, exclusão, modificação e consulta da tabela delta são as mesmas das tabelas de armazenamento de linha. Depois que a tabela delta é ativada, o desempenho da importação de tabelas de armazenamento de colunas é bastante aprimorado.

Casos de uso

A tabela delta de armazenamento de colunas é usada para armazenamento híbrido de linhas e colunas e é adequada para análise e estatísticas em tempo real. Ela resolve o problema de desempenho causado pela importação de pequenos lotes de dados e mescla periodicamente os dados à tabela primária para garantir o desempenho da análise e da consulta. Você precisa determinar se deve habilitar tabelas delta com base na situação real. Caso contrário, as vantagens das tabelas de armazenamento de colunas de GaussDB(DWS) não podem ser totalmente utilizadas, desperdiçando espaço e tempo extra.

Preparativos

• Você registrou uma conta do GaussDB(DWS) e verificou o status da conta antes de usar GaussDB(DWS). A conta não pode estar em atraso ou congelada.
• Você obteve o AK e SK da conta.
• Os dados de amostra foram carregados na pasta traffic-data em um bucket do OBS, e todas as contas da Huawei Cloud receberam a permissão somente leitura para acessar o bucket do OBS. Para mais detalhes, consulte Análise de veículos no ponto de verificação.

Procedimento

1. Use o DAS para se conectar a um cluster. Localize o cluster necessário na lista de clusters e clique em Log In na coluna Operation. Na página de DAS exibida, digite o nome do usuário, o nome do banco de dados e a senha e teste a conexão. Se a conexão for bem-sucedida, faça logon no cluster. Para obter detalhes, consulte Uso do DAS para se conectar a um cluster.

   

2. Execute a instrução a seguir para criar o banco de dados traffic:

   1	CREATE DATABASE traffic encoding 'utf8' template template0;

3. Execute as seguintes instruções para criar as tabelas de banco de dados GCJL e GCJL2 para armazenar informações do veículo do ponto de verificação: Por padrão, a tabela delta não está ativada para GCJL, mas para GCJL2.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   CREATE SCHEMA traffic_data; SET current_schema= traffic_data; DROP TABLE if exists GCJL; CREATE TABLE GCJL ( kkbh VARCHAR(20), hphm VARCHAR(20), gcsj DATE , cplx VARCHAR(8), cllx VARCHAR(8), csys VARCHAR(8) ) with (orientation = column, COMPRESSION=MIDDLE) distribute by hash(hphm); DROP TABLE if exists GCJL2; CREATE TABLE GCJL2 ( kkbh VARCHAR(20), hphm VARCHAR(20), gcsj DATE , cplx VARCHAR(8), cllx VARCHAR(8), csys VARCHAR(8) ) with (orientation = column, COMPRESSION=MIDDLE, ENABLE_DELTA = TRUE) distribute by hash(hphm);

   

   • As tabelas delta são desativadas por padrão. Para ativar tabelas delta, defina enable_delta como true ao criar tabelas de armazenamento de colunas.
   • Você também pode executar o seguinte comando para ativar tabelas delta:

     1	ALTER TABLE table_name SET (enable_delta=TRUE);

   • Se a tabela delta tiver sido ativada, você poderá executar o seguinte comando para desativá-la quando necessário:

     1	ALTER TABLE table_name SET (enable_delta=FALSE);

4. Crie uma tabela estrangeira, que é usada para identificar e associar os dados de origem no OBS.
   

   • <obs_bucket_name> indica o nome do bucket do OBS. Apenas algumas regiões são suportadas. Para obter detalhes sobre as regiões suportadas e os nomes dos bucket do OBS, consulte Regiões suportadas. Os clusters do GaussDB(DWS) não oferecem suporte ao acesso entre regiões aos dados do bucket do OBS.
   • Nesta prática, a região CN-Hong Kong é usada como exemplo. Digite dws-demo-ap-southeast-1 e substitua <Access_Key_Id> e <Secret_Access_Key> pelo valor atual.
   • Se a mensagem"ERROR: schema "xxx" does not exist Position" for exibida quando você criar uma tabela estrangeira, o esquema não existe. Execute a etapa anterior para criar um esquema.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   DROP FOREIGN table if exists GCJL_OBS; CREATE FOREIGN TABLE GCJL_OBS ( like traffic_data.GCJL ) SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://<obs_bucket_name>/traffic-data/gcxx', format 'text', delimiter ',', access_key '<Access_Key_Id>', secret_access_key '<Secret_Access_Key>', chunksize '64', IGNORE_EXTRA_DATA 'on' );

5. Execute a instrução a seguir para importar dados da tabela estrangeira para a tabela do banco de dados:

   1 2	INSERT INTO traffic_data.GCJL select * from GCJL_OBS; INSERT INTO traffic_data.GCJL2 select * from GCJL_OBS;

   Leva algum tempo para importar dados.

6. Execute a instrução a seguir para verificar o tamanho do espaço de armazenamento depois que a tabela do banco de dados é importada:

   1 2	SELECT pg_size_pretty(pg_total_relation_size('traffic_data.GCJL')); SELECT pg_size_pretty(pg_total_relation_size('traffic_data.GCJL2'));

   Depois que a tabela delta é habilitada, o uso do espaço de armazenamento é reduzido de 8953 MB para 6053 MB, melhorando significativamente o desempenho da importação.

   

7. Execute a instrução a seguir para consultar dados na tabela. A velocidade da consulta é melhorada depois que a tabela delta é habilitada.

   1 2	SELECT * FROM traffic_data.GCJL where hphm = 'YD38641'; SELECT * FROM traffic_data.GCJL2 where hphm = 'YD38641';

Impacto da ativação da tabela delta

• A ativação da função de tabela delta de uma tabela de armazenamento de colunas pode impedir que pequenas CUs sejam geradas quando um único dado ou uma pequena quantidade de dados é importada para a tabela, melhorando assim o desempenho. Por exemplo, se 100 pedaços de dados são importados cada vez em um cluster com 3 CNs e 6 DNs, o tempo de importação pode ser reduzido em 25%, o uso do espaço de armazenamento pode ser reduzido em 97%. Portanto, você precisa habilitar a tabela delta antes de inserir um pequeno lote de dados por várias vezes e desabilitar a tabela delta depois de confirmar que nenhum pequeno lote de dados precisa ser importado.
• Uma tabela delta é uma tabela de armazenamento de linha anexada a uma tabela de armazenamento de coluna. Depois que os dados são inseridos em uma tabela delta, a alta taxa de compactação da tabela de armazenamento de colunas é perdida. Em casos normais, tabelas de armazenamento de colunas são usadas para importar uma grande quantidade de dados. Portanto, a tabela delta é desativada por padrão, se a tabela delta é ativada quando uma grande quantidade de dados é importada, mais tempo e espaço são consumidos. Se a tabela delta for ativada quando os 10.000 registros de dados forem importados em um cluster com 3 DNs e 6 DNs, a velocidade de importação será quatro vezes mais lenta e mais de 10 vezes o espaço será consumido do que quando a tabela delta estiver desabilitada. Portanto, tenha cuidado ao ativar a tabela delta.