Introdução às tabelas temporais no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure

Aplica-se a:Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Tabelas temporais são um novo recurso de programação do Banco de Dados SQL do Azure e da Instância Gerenciada de SQL do Azure que permite controlar 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 relacionados ao contexto temporal, de modo que os fatos armazenados possam ser interpretados como válidos apenas no período específico. Essa propriedade das tabelas temporais permite uma análise eficiente baseada em tempo e a obtenção de informações da evolução dos dados.

Cenário temporal

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

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

Felizmente, você não precisa fazer qualquer esforço em seu aplicativo para manter essas informações de atividade. Com as tabelas temporais, esse processo é automatizado, oferecendo flexibilidade total durante o design do site e mais tempo para se concentrar na análise de dados. Só é necessário garantir que a tabela WebSiteInfo esteja configurada como versão temporal do sistema. As etapas exatas para utilizar as tabelas temporais neste cenário são descritas abaixo.

Etapa 1: configurar tabelas como temporais

Dependendo se você estiver iniciando um novo desenvolvimento ou atualizando um aplicativo existente, você criará tabelas temporais ou modificará as existentes ao adicionar atributos temporais. Em geral, seu cenário pode ser uma combinação dessas duas opções. Execute estas ações usando o SQL Server Management Studio (SSMS), o SQL Server Data Tools (SSDT), Azure Data Studio ou qualquer outra ferramenta de desenvolvimento do Transact-SQL.

Criar nova tabela

Use o item de menu de atalho "nova tabela de versão do sistema" no pesquisador de objetos SSMS para abrir o editor de consultas com um script de modelo de tabela temporal e, em seguida, use "especificar valores de parâmetros de modelo" (Ctrl + Shift + M) para preencher o modelo:

Em SSDT, escolha o modelo "tabela temporal (versão do sistema)" ao adicionar novos itens ao projeto de banco de dados. Isso abrirá o designer de tabela e permitirá que você especifique o layout da tabela com facilidade:

Também é possível criar uma tabela temporal especificando diretamente as instruções Transact-SQL, conforme mostrado no exemplo a seguir. Observe que os elementos obrigatórios de todas as tabelas temporais são a definição PERIOD e a cláusula SYSTEM_VERSIONING com uma referência a outra tabela de usuário que armazenará versões de linha de histórico:

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

Quando você cria a tabela temporal versionada pelo sistema, a tabela de histórico que acompanha este artigo 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 as tabelas temporais são usadas, especialmente para a auditoria de dados.

Neste caso em particular, temos o objetivo de realizar análises de tendências baseadas em tempo ao longo de um histórico de dados maior e com conjuntos de dados maiores, de forma que a opção de armazenamento para a tabela de histórico seja um índice columnstore clusterizado. Um columnstore clusterizado oferece compactação e desempenho ótimos para consultas analíticas. As tabelas temporais oferecem a flexibilidade de configurar índices nas tabelas atuais e temporais de forma totalmente independente.

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 Pesquisador de Objetos com o ícone específico para facilitar sua identificação, enquanto sua tabela de histórico é exibida como um nó filho.

Alterar uma tabela existente para temporal

Vamos abordar o cenário alternativo no qual a tabela WebsiteUserInfo já existe, mas que não foi projetada para manter um histórico de alterações. Nesse caso, você pode simplesmente estender a tabela existente para se tornar temporal, conforme 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: executar sua carga de trabalho regularmente

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

Para aproveitar o rastreio automático das alterações deste cenário em particular, vamos atualizar apenas a coluna PagesVisited sempre que um usuário finalizar sua sessão no site:

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

É importante observar que na consulta de atualizações não é necessário 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 aspectos são manipulados automaticamente pelo Banco de Dados SQL do Azure e pela Instância Gerenciada de SQL do Azure. O diagrama a seguir ilustra como os dados históricos estão sendo gerados em cada atualização.

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

Agora, quando o versionamento pelo sistema temporal estiver habilitado, a análise dos dados históricos estará a apenas uma consulta de distância de você. Neste artigo, mostraremos alguns exemplos que tratam dos cenários comuns da análise. Para obter todos os detalhes, explore as diversas opções introduzidas com a cláusula FOR SYSTEM_TIME.

Para ver os 10 principais usuários ordenados pelo número de páginas da Web visitadas na última 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 modificar facilmente essa consulta para analisar as visitas ao site no dia anterior, no mês anterior ou em qualquer ponto no passado desejado.

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

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 StDevViistedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

Para procurar atividades de um usuário específico, em 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 porque você pode mostrar com muita facilidade tendências e padrões de uso de uma maneira intuitiva:

Aprimoramento do esquema de tabela

Normalmente, você precisará alterar o esquema de tabela temporal enquanto estiver fazendo o desenvolvimento de aplicativos. Para isso, bastará executar as instruções ALTER TABLE regulares e o Banco de Dados SQL do Azure ou a Instância Gerenciada de SQL do Azure propagará adequadamente as alterações na tabela de histórico. O script a seguir mostra como você pode adicionar outros atributos ao controle:

/*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 de 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;

Por fim, você pode remover uma coluna que já não precisa.

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

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

Controle de retenção de dados históricos

Com as tabelas temporais versionadas pelo sistema, a tabela de histórico pode aumentar o tamanho do banco de dados mais do que as tabelas normais. Uma tabela de histórico grande e crescente pode se tornar um problema, tanto devido a custos de armazenamento puro, como por impor um desempenho imposto sobre consultas temporais. Portanto, o desenvolvimento de uma política de retenção de dados para o gerenciamento de dados na tabela de histórico é um aspecto importante do planejamento e do gerenciamento do ciclo de vida de cada tabela temporal. Com o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure, você tem as seguintes abordagens para o gerenciamento dos dados históricos na tabela temporal:

• Particionamento de Tabela
• Script de Limpeza Personalizada

Próximas etapas

• Para obter mais informações sobre tabelas tabela temporais, confira Tabelas Temporais.