Rotina de Carga Incremental Simples

De Qknow
Ir para: navegação, pesquisa

Introdução

IncrementalLoad.jpg

Carga incremental no QlikView refere-se a habilidade de ler somente os dados que não foram previamente carregados, permitindo que as cargas recorrentes possam ler apenas uma parcela dos dados necessários a completar o que já foi obtido. Para aqueles que estão atuando mais recentemente com a plataforma Qlik, é importante destacar que o processo de carga dos dados para a memória refere-se a ação de conectar/ler todas as fontes de dados definidas, mesmo que os dados já estejam em memória a partir de cargas anteriores.

Por padrão, sempre que o processo de reload é acionado, todos os dados são recuperados das fontes definidas e a versão anterior é descarregada da memória. Ou seja, qualquer que seja a quantidade de dados previamente carregada, toda a informação em memória é dispensada e lida das bases originais. Dependendo do volume de dados este processo pode levar minutos ou horas. O desempenho do processo de recarga depende muito das condições de infraestrutura e da qualidade dos dados.

Por diversos motivos o procedimento de recarga dos dados pode ser personalizado para ler somente o que não foi carregado anteriormente, como registros novos, modificados ou mesmo excluídos. Ao invés de dispensar todo o volume previamente lido, é possível construir uma lógica que obtenha apenas uma parcela destes dados, evitando um maior esforço dos sistemas de banco de dados e uma redução acentuada no tempo total de processamento.

Para que seja possível utilizar o processo de carga incremental é necessário utilizar arquivos Qlikview no formato .QVD. Apenas para conceituar de maneira rápida e simples, arquivos .QVD respondem pelas tabelas carregadas e necessárias a um determinado painel. Porém, devido as características da tecnologia Qlik, estes arquivos são extremamente compactos e rápidos. A leitura de um arquivo .QVD para a memória ocorre, geralmente, entre 10 e 100 vezes mais rápido do que a leitura de qualquer outra fonte de dados. Cada arquivo .QVD armazena uma estrutura equivalente a uma tabela. Ou seja, são conjuntos de colunas e linhas provenientes de uma instrução SELECT ou carga de dados. O modelo de carga incremental varia de acordo com os objetivos de negócio.

Carga Incremental para Novos Registros

Este modelo aplica-se a carga dos dados que ainda não foram carregados anteriormente tendo sido inseridos nas bases de dados após a última carga. Claro que a primeira ocorrência de carga deve gerar o arquivo .QVD para hospedagem dos dados. Ou seja, na primeira operação de reload não existe uma carga anterior, logo todos os registros devem ser lidos. Para realizar a construção passo-a-passo do processo de carga incremental, faça o download do arquivo em formato Excel aqui. O arquivo contém as seguintes colunas para simular uma tabela de dados tradicional:

  • CustomerID
  • CompanyName
  • CustomerName
  • Address
  • City
  • State
  • ZipCode
  • Country
  • Phone
  • Fax
  • InsertDate
  • ChangeDate
  Nota: Baixe o arquivo em formato Excel para simular a inserção de dados.

Para o processo de carga incremental visando novos registros inseridos nas bases é preciso que a tabela de origem tenha um valor que possa ser comparado com os dados previamente carregados afim de determinar se as linhas já foram lidas anteriormente. No exemplo, é possível utilizar os campos CustomerID ou InsertDate. Para fins de teste iremos considerar que o campo CustomerID é incremental, geralmente o que ocorre em campos do tipo IDENTITY (SQL Server) ou SEQUENCE (Oracle). Já o campo InsertDate assumirá a data de inserção de cada novo registro da tabela. O diagrama a seguir explica o procedimento que será adotado para a carga incremental proposta por esta seção.

IncrementalLoad1.png

1. Quando o processo de reload é acionado o QlikView executa o script de carga definido no arquivo .QVW. Geralmente este script é gravado em um .QVW separado da interface do painel, para o qual chamamos de Extrator de Dados ou Transformador de Dados. Mas nada impede que seja definido no próprio arquivo do painel, como neste exemplo.

2. A primeira tarefa do script é avaliar se houve uma carga anterior (prévia) tendo sido gerado um arquivo .QVD. Este teste afere se a carga deve ser completa (full) ou incremental. Caso o arquivo .QVD não exista a carga total (não incremental) é realizada obtendo todos os dados da base original. Em seguida, o arquivo .QVD é salvo.

3. Na hipótese do arquivo .QVDexistir, então o script considera que a carga será do tipo incremental, buscando na tabela de dados apenas os registros que já não existam no arquivo .QVD. Nesta ação o script deve gravar o momento em que a carga foi iniciada para que no futuro essa informação sirva de critério para a busca de novos registros.

4. Seguindo o fluxo de uma carga incremental, o script faz a leitura da data/hora da última execução de carga para utilizar como critério de pesquisa para a nova carga. Neste cenário esta informação é fundamental para comparar com a data de criação dos novos registros na base de dados. Esta informação não seria necessária se o campo de critério fosse incremental.

5. De posse da informação de data/hora da última operação de carga, o script monta uma nova instrução que utilizará o critério de tempo para realizar a leitura da base de dados. Em caso de conexões a SGBDs (bancos relacionais), esta operação ocorrerá após a conexão com a base, obviamente. Neste exemplo, consideramos a base o arquivo Excel, motivo pelo qual a conexão está invertida no diagrama.

6. Uma vez com a estrutura de consulta montada, a conexão à fonte de dados é estabelecida, executando a carga apenas dos registros mais novos sem a leitura da base completa. Ou seja, somente os registros com data mais recente do que aquela referente a última carga são efetivamente lidos. Os demais já existem no arquivo .QVD gravado pela operação anterior.

7. Com os novos dados obtidos da base, o arquivo .QVD é lido e mesclado (reunido) com os dados obtidos da tabela recém carregada. O processo de merge junta os novos dados aos dados anteriores. O resultado é persistido (gravado) de volta ao arquivo .QVD utilizado como base para apresentação dos dados em um ou mais painéis de tomada de decisão.


Para montar a lógica apresentada, abra o QlikView Desktop e crie um novo documento. Após salvar o arquivo com o nome desejado, abra o editor de scripts (CTRL + E) para iniciar a criação da lógica de carga incremental segundo o diagrama acima.

LET vStart = Now();
LET vFileExists = IF(FileSize('Qknow - Customers - v1.0.QVD') > 0, -1, 0);
LET vCriterio = IF(IsNull(vCriterio), 0, vCriterio);

A variável vStart armazena a data/hora em que a operação do script iniciou. Essa informação é importante para obter os dados que foram inseridos antes do script iniciar, mas só será útil nas cargas seguintes. Já a variável vFileExists utiliza a função FileSize do QlikView para testar o tamanho do arquivo Qknow - Customers - v1.0.QVD, que no primeiro momento não existe. Por isso, a combinação com a função IF retorna 0, ou false. Para uma simplificação do script evitando novas condições com a função IF, a variável vCriterio é utilizada para definir quais são os registros que devem ser obtidos da base de dados em referência a última carga realizada. No entanto, no primeiro momento, esta variável é preenchida com zero por não ter ocorrido uma carga anterior. Ocorre então a carga da tabela destinada ao incremento utilizando o critério estabelecido no campo InsertDate. Logicamente esta carga leva em consideração que a tabela registre o momento em que cada registro foi inserido gravado neste campo.

SourceTable:
LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
     FROM [qknow_customers_v1.0.xls] (biff, embedded labels, table is Clientes$)
          WHERE InsertDate > '$(vCriterio)';
  Nota: Para mais informações sobre definição de variáveis, consulte o artigo sobre uso de variáveis em expressões repetidas.

Na primeira execução do script todos os registros são obtidos da tabela, independente da fonte ser um arquivo ou um banco de dados. Por isso, é preciso avaliar que tipo de carga foi realizada e gravar o arquivo .QVD com os registros obtidos. Utilizando a variável vFileExists é possível determinar se o arquivo existe. Caso não seja encontrado, trata-se de uma carga geral.

IF vFileExists = -1 THEN
       NewTable:
       Concatenate (SourceTable)
       LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
       FROM [Qknow - Customers Qvd File.Qvd] (qvd);
END IF

Apenas quando o arquivo existir este será carregado para a memória em uma nova tabela denominada NewTable. Note que a instrução Concatenate (SourceTable) indica ao QlikView que as tabelas devem ser unidas (merge), reunindo os dados que foram carregados em cargas anteriores residentes no arquivo .QVD com os dados lidos da base de dados (tabela/arquivo). Após união dos dados o arquivo .QVD é gravado contendo o conteúdo completo.

STORE SourceTable INTO [Qknow - Customers Qvd File.Qvd] (qvd);

LET vCriterio = vStart;

Neste exemplo o critério de obtenção de novos dados é a data de inserção dos registros testado no campo InsertDate. Por isso, o critério é atualizado com a data de início do processamento. Isso indica que nenhum registro inserido após o script ter iniciado será carregado. Após concluir o script, salve o projeto e execute a carga através do botão Reload ou por meio do atalho CTRL + R. Será possível notar a carga de todos os registros disponíveis na tabela por tratar-se da primeira operação de reload.

ReloadProcedure1.PNG


Note que os dados carregados foram todos os disponíveis na tabela. Ou seja, 92 registros. Se a operação for executada novamente, a mensagem será que nenhum registro foi lido, pois não há novas inserções entre as cargas.

Para testar a implementação, insira novas linhas na tabela (arquivo Excel) tal como no exemplo abaixo, rodando o processo de reload em seguida. Será possível observar que somente 2 registros serão lidos da tabela e mesclados com aqueles presentes no arquivo .QVD.

93 | Qknow | Franco Galati   | QE 34 | Guara II | DF | 72000 | Brazil | (61) 30333334 | 30/09/2015 14:20 | 30/09/2015 14:20
94 | Qknow | Nilton Barcelos | SAS 2 | Asa Sul  | DF | 72000 | Brazil | (61) 30331234 | 30/09/2015 14:20 | 30/09/2015 14:20

Após executar a rotina é possível constatar os registros lidos da tabela. Certifique-se de inserir a data/hora superior a data/hora da última execução, doutra forma, os registros não serão considerados como novos. No exemplo, 30/09/2015 14:20 é superior a data/hora da última execução registrada na variável vCriterio. O resultado é exibo na figura a seguir, onde dois novos registros são lidos da tabela e mesclados via CONCATENATE com os 92 lidos anteriormente.


IncrementalLoad2.png


Carga Incremental com Campo Sequencial

Outra técnica pode ser utilizada em tabelas que não possuem um campo para determinar a data/hora de inserção do registro, como visto na seção anterior. No entanto, se há um campo sequencial disponível, tal como em chaves primárias do tipo IDENTITY (Ex.: SQL Server) ou SEQUENCE (Ex.: Oracle), é possível gravar o último registro recuperado do campo sequencial visando compará-lo com novos registros no futuro. Considerando este cenário, o script de carga incremental seria ajustado da seção anterior para o exemplo a seguir:

LET vFileExists = IF(FileSize('Qknow - Customers Qvd File.Qvd') > 0, -1, 0);
LET vCriterio = IF(IsNull(vCriterio), 0, vCriterio);

SourceTable:
LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
     FROM [qknow_customers_v1.0.xls] (biff, embedded labels, table is Clientes$)
            Where CustomerID > $(vCriterio);

IF vFileExists = -1 THEN
       NewTable:
       Concatenate (SourceTable)
       LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
            FROM [Qknow - Customers Qvd File.Qvd] (qvd);
END IF

STORE SourceTable INTO [Qknow - Customers Qvd File.Qvd] (qvd);

LET vCriterio = Peek('CustomerID');
LET vFileExists = ;

No cenário acima o campo auto numerado é utilizado para definir quais registros já foram lidos para o arquivo .QVD. A diferença entre esta versão do script e o apresentado na seção anterior é o critério de seleção dos novos registros, que deixa de ser feito pelo campo InsertDate e passa a ser o campo CustomerID. Para testar o funcionamento deste script basta incluir novos registros incrementando o campo CustomerID, tal como no exemplo abaixo.

95 | Qknow | Geilson Junior   | SAS 2 | Asa Sul | DF | 72000 | Brazil | (61) 30331234 | 30/09/2015 15:21 | 30/09/2015 15:21

O resultado é a inserção de uma nova linha aos dados do .QVD., buscando apenas a diferença.

IncrementalLoad3.png


Neste cenário é possível resumir o procedimento da seguinte maneira:

  • A instrução LET vFileExists = IF(FileSize('Qknow - Customers Qvd File.Qvd') > 0, -1, 0); avalia se o arquivo .QVD existe.
  • Caso o arquivo exista indica que alguma carga anterior foi realizada. Doutra forma, trata-se da primeira operação de reload.
  • A instrução LET vCriterio = IF(IsNull(vCriterio), 0, vCriterio); é preenchida com o critério a ser usado na cláusula Where.
  • Na primeira execução (primeira carga) o critério será definido como zero.
  • O comando LOAD...Where CustomerID > $(vCriterio); fará a leitura dos registros que forem maiores que o critério.
  • O critério da primeira execução instrui o comando a carregar todos os registros (maior que zero).
  • Na linha IF vFileExists = -1 THEN há o teste de existência do arquivo .QVD criado depois da primeira execução.
  • Independente do resultado, STORE grava o arquivo .QVD todas as vezes em que o reload for executado.
  • A variável de critério é redefinida com o valor mais alto do campo sequencia, por meio da instrução LET vCriterio = Peek('CustomerID');.

O resultado é a leitura dos registros novos e a gravação de um novo arquivo .QVD que pode ser utilizado por vários painéis.


Idea 1.jpg

Precisa de mais informações sobre a gravação de arquivos .QVD?

Veja aqui!

Carga Incremental para Registros Atualizados

O modelo visto na seção anterior cobriu um formato de carga para novos registros. Porém, é possível que registros sofram alterações sem que tenham sido inseridos desde a última carga. Por isso, o script pode prever que o registro foi modificado e atualizar o arquivo .QVD tanto com os novos dados quanto com os atualizados. Ou seja, registros novos e atualizados são lidos a partir da base de dados enquanto os demais, inalterados, retornam para o arquivo .QVD sem necessidade de leitura da tabela.

O script para carga incremental de registros novos ou atualizados é muito semelhante ao script original utilizado apenas para novos dados. Porém, ao invés de utilizar o campo InsertDate, ChangeDate é utilizado em seu lugar. Assume-se que o campo ChangeDate é atualizado sempre que há um novo registro inserido e, também, quando há atualizações de dados em linhas já existentes na tabela. O script final prevendo cargas incrementais para atualizações e inserções de novos dados será semelhante ao apresentado abaixo.

LET vStart = Now();
LET vFileExists = IF(FileSize('Qknow - Customers Qvd File.Qvd') > 0, -1, 0);
LET vCriterio = IF(IsNull(vCriterio), 0, vCriterio);

SourceTable:
LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
     FROM [qknow_customers_v1.0.xls] (biff, embedded labels, table is Clientes$)
          WHERE ChangeDate > '$(vCriterio)';

IF vFileExists = -1 THEN
	NewTable:
	Concatenate (SourceTable)
	LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
	     FROM [Qknow - Customers Qvd File.Qvd] (qvd) Where Not Exists (CustomerID);
END IF

STORE SourceTable INTO [Qknow - Customers Qvd File.Qvd] (qvd);

LET vCriterio = vStart;
LET vFileExists = ;

Em relação ao script de carga incremental destinado apenas a novos registros, este modelo está atualizado para incluir um critério ao processo de concatenação utilizando a instrução WHERE para carregar do arquivo .QVD apenas os registros que não foram carregados da tabela original: Where Not Exists (CustomerID).

Assumindo o arquivo Excel em uso, note a alteração realizada em três dos registros da tabela. Quando o processo de carga é executado pela segunda vez somente os registros que sofreram alterações após o último reload são lidos. A cláusula WHERE instrui o comando LOAD a ler do arquivo .QVD somente os registros que não sofreram alterações. O campo CustomerID é a chave primária que determina essa condição. Ou seja, ler do .QVD todos os registros que não foram lidos da tabela original. Assim, são unidos (merge) o .QVD e a tabela original.

84 | Pour l´homme   | Henry Renault  | 59 rue de l'Abbaye  | Reims   | NA | 51100 | France | 26.47.15.10     | 12.34.56.78 | 26/03/2014 09:15 | 02/10/2015 17:05
88 | Nirvana Stores | Alice Chain    | "305 - 14th Ave. S. | Seattle | WA |98128  | USA    | (206) 555-4112  | 12.34.56.78 | 30/03/2014 09:15 | 02/10/2015 17:05
95 | Qknow          | Geilson Junior | SAS Quadra 2        | Asa Sul | GO | 72000 | Brazil | (61) 30331234   | 12.34.56.78 | 30/09/2015 15:21 | 02/10/2015 17:05

IncrementalLoad5.png


Como visto, apenas os três registros que possuem a data de modificação maior do que a data da última carga são lidos da tabela (arquivo) no Excel, enquanto todos os demais registros, a exceção dos três, são lidos do arquivo .QVD.

Carga Incremental para Registros Atualizados e Excluídos

Este último cenário exemplifica um script capaz de ler os dados inseridos e modificados a partir da tabela original enquanto remove do arquivo .QVD eventuais registros que não mais existam. Embora considerado uma carga incremental, na prática ao menos uma coluna (PK) da tabela de origem precisa ter todos os registros lidos. Ainda assim este modelo supera a carga completa de todas as colunas, pois apenas a chave primária da tabela em questão é trafegada entre a fonte e o QlikView.

O script é essencialmente o mesmo da seção anterior acrescido da leitura da tabela original para comparar as chaves primárias existentes entre as duas versões (tabela e .QVD). Portanto, apenas a seguinte linha é adicionada:

INNER JOIN LOAD CustomerID FROM [qknow_customers_v1.0.xls] (biff, embedded labels, table is Clientes$);

A instrução completa pode ser observada com destaque a mudança no exemplo abaixo:

LET vStart = Now();
LET vFileExists = IF(FileSize('Qknow - Customers Qvd File.Qvd') > 0, -1, 0);
LET vCriterio = IF(IsNull(vCriterio), 0, vCriterio);

SourceTable:
LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
     FROM [qknow_customers_v1.0.xls] (biff, embedded labels, table is Clientes$)
          WHERE ChangeDate > '$(vCriterio)';

IF vFileExists = -1 THEN
	NewTable:
	Concatenate (SourceTable)
	LOAD CustomerID, CompanyName, CustomerName, Address, City, State, ZipCode, Country, Phone, Fax, InsertDate, ChangeDate
	FROM [Qknow - Customers Qvd File.Qvd] (qvd) Where Not Exists (CustomerID);
	INNER JOIN LOAD CustomerID FROM [qknow_customers_v1.0.xls] (biff, embedded labels, table is Clientes$);
END IF

STORE SourceTable INTO [Qknow - Customers Qvd File.Qvd] (qvd);

LET vCriterio = vStart;
LET vFileExists = ;


  Nota: Assegure-se de alterar o caminho dos arquivos no editor de scripts se realizar o download abaixo.


Faça o download destes exemplos nos links a seguir:


Idea 1.jpg

Aprenda a criar rotinas para mostrar dados adicionais durante o processo de recarga.

Veja aqui!


Envelope01.jpg
Procurando Algo? Fale Conosco!

Voltar | Página Principal