Partilhar via

Introdução às tabelas temporais

Aplica-se a:Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Fabric

As tabelas temporais são um recurso de programação que permite acompanhar e analisar o histórico completo de alterações em seus dados, sem a necessidade de codificação personalizada. As tabelas temporais mantêm os dados estreitamente relacionados com o contexto temporal para que os factos armazenados possam ser interpretados como válidos apenas dentro do período específico. Esta propriedade das tabelas temporais permite uma análise eficiente baseada no tempo e a obtenção de insights da evolução dos dados.

Cenário temporal

Este artigo ilustra as etapas para utilizar tabelas temporais em um cenário de aplicativo. Suponha que você queira rastrear a atividade do usuário em um novo site que está sendo desenvolvido do zero ou em um site existente que você deseja estender com a análise de atividade do usuário. Neste exemplo simplificado, assumimos que o número de páginas da Web visitadas durante um período de tempo é um indicador que precisa ser capturado e monitorado no banco de dados do site hospedado no Banco de Dados SQL do Azure ou na Instância Gerenciada SQL do Azure. O objetivo da análise histórica da atividade do usuário é obter entradas para redesenhar o site e fornecer uma melhor experiência para os visitantes.

O modelo de banco de dados para esse cenário é simples - a métrica de atividade do usuário é representada com um único campo inteiro, PageVisited, e é capturada junto com informações básicas sobre o perfil do usuário. Além disso, para análise baseada no tempo, você manteria uma série de linhas para cada usuário, onde cada linha representa o número de páginas que um usuário específico visitou dentro de um período de tempo específico.

Diagrama de um esquema de tabela para a tabela de exemplo WebSiteUserinfo.

Felizmente, você não precisa fazer nenhum esforço em seu aplicativo para manter essas informações de atividade. Com tabelas temporais, este processo é automatizado - dando-lhe total flexibilidade durante o design do site e mais tempo para se concentrar na análise de dados em si. A única coisa que você precisa fazer é garantir que WebSiteInfo a tabela seja configurada como com versão temporal do sistema. As etapas exatas para utilizar tabelas temporais neste cenário são descritas abaixo.

Etapa 1: Configurar tabelas como temporais

Dependendo se você está iniciando um novo desenvolvimento ou atualizando o aplicativo existente, você criará tabelas temporais ou modificará as existentes adicionando atributos temporais. Em geral, seu cenário pode ser uma mistura dessas duas opções. Execute essas ações usando o SQL Server Management Studio (SSMS), o SQL Server Data Tools (SSDT), a extensão mssql para Visual Studio Code ou qualquer outra ferramenta de desenvolvimento Transact-SQL.

Importante

É recomendável que você sempre use a versão mais recente do SQL Server Management Studio para permanecer sincronizado com as atualizações do Banco de Dados SQL do Azure e da Instância Gerenciada SQL do Azure. Atualize o SQL Server Management Studio.

Criar nova tabela

  • Use o item de menu de contexto Nova Tabela System-Versioned no Explorador de Objetos do SSMS para abrir o editor de consultas com um script de modelo de tabela temporal e, em seguida, use Especificar Valores para Parâmetros de Modelo (Ctrl+Shift+M) para preencher o modelo.

    Captura de tela do SSMS da opção Nova tabela versionada do sistema.

  • No SSDT, escolha o modelo "Tabela Temporal (System-Versioned)" ao adicionar novos itens ao projeto de banco de dados. Isso abrirá o designer de tabela e permitirá que você especifique facilmente o layout da tabela:

    Captura de ecrã do SSMS da caixa de diálogo Adicionar Novo Item e da opção Tabela Temporal, System-Versioned selecionada.

  • Você também pode criar uma tabela temporal especificando as instruções Transact-SQL diretamente, conforme mostrado no exemplo a seguir. Os elementos obrigatórios de cada tabela temporal são a PERIOD definição e a SYSTEM_VERSIONING cláusula com referência a outra tabela de usuário que armazenará versões históricas de linha:

    CREATE TABLE WebsiteUserInfo
(  
      [UserID] int NOT NULL PRIMARY KEY CLUSTERED
    , [UserName] nvarchar(100) NOT NULL
    , [PagesVisited] int NOT NULL
    , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
    , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
  )  
  WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));

Tabela de histórico padrão

Quando você cria uma tabela temporal com versão do sistema, a tabela de histórico que a acompanha com a configuração padrão é criada automaticamente. A tabela de histórico padrão contém um índice de árvore B clusterizado nas colunas de período (fim, início) com a compactação de página habilitada. Essa configuração é ideal para a maioria dos cenários em que tabelas temporais são usadas, especialmente para auditoria de dados.

Neste caso em particular, nosso objetivo é realizar uma análise de tendência baseada no tempo em um histórico de dados mais longo e com conjuntos de dados maiores, portanto, a opção de armazenamento para a tabela de histórico é um índice columnstore clusterizado. Um columnstore clusterizado fornece boa compactação e desempenho para consultas analíticas. As tabelas temporais oferecem a flexibilidade de configurar índices nas tabelas atuais e temporais de forma completamente independente.

Observação

Os índices Columnstore estão disponíveis nas camadas Business Critical, General Purpose e Premium e na camada Standard, S3 e superior.

O script a seguir mostra como o índice padrão na tabela de histórico pode ser alterado para o columnstore clusterizado:

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

As tabelas temporais são representadas no Explorador de Objetos com um ícone específico, facilitando a identificação, enquanto a sua tabela de histórico é exibida como um nó filho.

Captura de tela do SQL Server Management Studio mostrando o Pesquisador de Objetos e a tabela de histórico.

Alterar tabela existente para temporal

Vamos abordar o cenário alternativo em que a WebsiteUserInfo tabela já existe, mas não foi projetada para manter um histórico de mudanças. Nesse caso, você pode simplesmente estender a tabela existente para se tornar temporal, como mostrado no exemplo a seguir:

ALTER TABLE WebsiteUserInfo
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN  
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE WebsiteUserInfo  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Etapa 2: Execute sua carga de trabalho regularmente

A principal vantagem das tabelas temporais é que você não precisa alterar ou ajustar seu site de forma alguma para realizar o controle de alterações. Uma vez criadas, as tabelas temporais persistem de forma transparente as versões de linha anteriores sempre que você executa modificações em seus dados.

Para usar o controle automático de alterações para esse cenário específico, vamos apenas atualizar a coluna PagesVisited toda vez que um usuário terminar sua sessão no site:

UPDATE WebsiteUserInfo  SET [PagesVisited] = 5
WHERE [UserID] = 1;

É importante notar que a consulta de atualização não precisa saber a hora exata em que a operação real ocorreu nem como os dados históricos serão preservados para análise futura. Ambos os aspetos são tratados automaticamente pelo Banco de Dados SQL do Azure e pela Instância Gerenciada SQL do Azure. O diagrama a seguir ilustra como os dados do histórico estão sendo gerados em cada atualização.

Diagrama da arquitetura da tabela temporal.

Etapa 3: Executar a análise de dados históricos

Agora, quando o controle de versão temporal do sistema está habilitado, a análise de dados históricos está a apenas uma consulta de distância de você. Neste artigo, forneceremos alguns exemplos que abordam cenários de análise comuns - para aprender todos os detalhes, explore várias opções introduzidas com a cláusula FOR SYSTEM_TIME .

Para ver os 10 principais utilizadores ordenados pelo número de páginas Web visitadas há uma hora, execute esta consulta:

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT TOP 10 * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME AS OF @hourAgo
ORDER BY PagesVisited DESC

Você pode facilmente modificar esta consulta para analisar as visitas ao site a partir de um dia atrás, um mês atrás ou em qualquer momento no passado que desejar.

Para executar a análise estatística básica do dia anterior, use o seguinte exemplo:

DECLARE @twoDaysAgo datetime2 = DATEADD(DAY, -2, SYSUTCDATETIME());
DECLARE @aDayAgo datetime2 = DATEADD(DAY, -1, SYSUTCDATETIME());

SELECT UserID, SUM (PagesVisited) as TotalVisitedPages, AVG (PagesVisited) as AverageVisitedPages,
MAX (PagesVisited) AS MaxVisitedPages, MIN (PagesVisited) AS MinVisitedPages,
STDEV (PagesVisited) as StDevVisitedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

Para pesquisar atividades de um usuário específico, dentro de um período de tempo, use a cláusula CONTAINED IN:

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
DECLARE @twoHoursAgo datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME());
SELECT * FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME CONTAINED IN (@twoHoursAgo, @hourAgo)
WHERE [UserID] = 1;

A visualização gráfica é especialmente conveniente para consultas temporais, pois você pode mostrar tendências e padrões de uso de forma intuitiva com muita facilidade:

Gráfico das páginas visitadas ao longo do tempo, com base nos dados da tabela de histórico temporal.

Evoluir o esquema da tabela

Normalmente, você precisará alterar o esquema da tabela temporal enquanto estiver desenvolvendo aplicativos. Para isso, basta executar instruções regulares ALTER TABLE e o Banco de Dados SQL do Azure ou a Instância Gerenciada SQL do Azure propaga adequadamente as alterações na tabela de histórico.

O script a seguir mostra como você pode adicionar atributo adicional para rastreamento:

/*Add new column for tracking source IP address*/
ALTER TABLE dbo.WebsiteUserInfo
ADD  [IPAddress] varchar(128) NOT NULL CONSTRAINT DF_Address DEFAULT 'N/A';

Da mesma forma, você pode alterar a definição da coluna enquanto sua carga de trabalho está ativa:

/*Increase the length of name column*/
ALTER TABLE dbo.WebsiteUserInfo
    ALTER COLUMN  UserName nvarchar(256) NOT NULL;

Finalmente, você pode remover uma coluna que não precisa mais.

/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
    DROP COLUMN TemporaryColumn;

Como alternativa, use o SSDT mais recente para alterar o esquema da tabela temporal enquanto estiver conectado ao banco de dados (modo online) ou como parte do projeto de banco de dados (modo offline).

Controlar a retenção de dados históricos

Com tabelas temporais com versão do sistema, a tabela de histórico pode aumentar o tamanho do banco de dados mais do que as tabelas regulares. Uma tabela de histórico grande e em constante crescimento pode se tornar um problema tanto devido aos custos de armazenamento puros quanto à imposição de um imposto de desempenho sobre consultas temporais. Portanto, desenvolver uma política de retenção de dados para gerenciar dados na tabela de histórico é um aspeto importante do planejamento e gerenciamento do ciclo de vida de cada tabela temporal. Com o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure, você tem as seguintes abordagens para gerenciar dados históricos na tabela temporal:

Observações

No Banco de Dados SQL do Azure configurado para espelhamento para o Banco de Dados SQL do Fabric e no Banco de Dados SQL do Fabric, você pode criar tabelas temporais, mas as respetivas tabelas de histórico não são espelhadas no Fabric OneLake. Para obter detalhes específicos relativamente à definição do sinalizador SYSTEM_VERSIONING em tabelas temporais, consulte Criar uma tabela temporal versionada pelo sistema.

Conteúdo relacionado