Introdução às tabelas temporais na Base de Dados SQL do Azure e na Instância Gerida SQL do Azure

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

As tabelas temporais são um recurso de programação do Banco de Dados SQL do Azure e da Instância Gerenciada 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 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 é muito 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.

Felizmente, você não precisa colocar 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), o Azure Data Studio ou qualquer outra ferramenta de desenvolvimento Transact-SQL.

Criar nova tabela

Use o item de menu de contexto "New System-Versioned Table" no Pesquisador 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:

No 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 facilmente o layout da tabela:

Você também pode criar uma tabela temporal especificando as instruções Transact-SQL diretamente, conforme mostrado no exemplo abaixo. Observe que os elementos obrigatórios de cada tabela temporal 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 históricas:

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 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 compactação e desempenho muito bons para consultas analíticas. As tabelas temporais oferecem a flexibilidade de configurar índices nas tabelas atuais e temporais de forma completamente 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 a identificação, enquanto sua tabela de histórico é exibida como um nó filho.

Alterar tabela existente para temporal

Vamos abordar o cenário alternativo em que a tabela WebsiteUserInfo já existe, mas 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, 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 aproveitar 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.

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

Esquema de tabela evolutivo

Normalmente, você precisará alterar o esquema da tabela temporal enquanto estiver desenvolvendo aplicativos. Para isso, basta executar instruções ALTER TABLE regulares 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).

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

• Particionamento de tabelas
• Script de limpeza personalizado

Próximos passos

• Para obter mais informações sobre tabelas temporais, consulte Tabelas temporais.