Separando sua aplicação do Banco de Dados com instâncias cloud

Instância cloud

Imagem ilustrativa: Separando o banco de dados e aplicação em instâncias cloud  – Freepik

Há algum tempo atrás nós trouxemos para você uma forma avançada de se separar responsabilidades do sistema através do Cluster de banco de dados. Em resumo, esta arquitetura separava seu projeto em instâncias cloud. A ideia é criar um grupo de banco de dados com responsabilidades semelhantes (leitura e escrita) ou não (Só leitura, só escrita) e através de um load balancer podemos balancear a carga de processos em cada servidor.

Hoje explicarei de uma forma mais prática, uma arquitetura que pode encaixar melhor em projetos médios. São comuns exemplos, os mais diversos blogs e lojas virtuais que, apesar de não serem uma americanas, um submarino ou um globo.com, começaram a enfrentar problemas pelo alto fluxo de consultas nos bancos de dados. Estes por sua vez estão trazendo demora ou instabilidade na aplicação web.

Quando fazer a separação em instâncias cloud

Reuni algumas perguntas que você deve se fazer antes de sair contratando ou separando novas instâncias cloud. Afinal, a ideia é otimizar seu sistema mas, não sair separando tudo por mero luxo certo.

  1. Sua aplicação está mais lenta que o normal?
  2. Ela está sofrendo com picos de instabilidade ou até mesmo saindo do ar em determinados momentos do dia?
  3. Durante estes picos e estas demoras, os processos envolvidos são relacionados a uma consulta no banco, ou em salvar algum dado neste?
  4. Ao executar o comando top em seu bash, as estatísticas de uso de recursos estão bem altas e o processo relacionado ao banco de dados está entre os maiores?

Você deve ter percebido que se seu projeto está nesta situação é melhor tomar uma atitude. Então tendo tudo analisado vamos a parte prática.

Mão na massa! Colocando meu Banco de dados em outra instância cloud

Para isto, utilizarei o painel do DialCloud+. Com ele você conseguirá configurar cada instância com os recursos necessários a cada projeto.

Para ilustração deixo este esquema de como funcionará este exemplo de arquitetura. Basicamente estamos deixando seu banco dados dedicado em uma instância com seus recursos próprios.

Banco de dados dedicado em uma instância cloud

Arquitetura com banco de dados dedicado

Com esta imagem em mente, podemos seguir ;).

Abaixo podemos ver que toda a aplicação se encontra em apenas uma instância. Nesta instância temos instalados o SO, o apache, o MySQL, PHP e entre diversas outras aplicações. O caso é que, como citado acima, eu identifiquei que meu banco de dados começou a consumir grande parte da minha memória e acaba por utilizar minhas 4 vCPUs disponíveis o que deixa todo o meu sistema em cheque.

 

instâncias cloud

Tela de instâncias do DialCloud +

É claro que neste exemplo terei de optar por aumentar um pouco meus recursos. Mas, simplesmente aumentá-los não seria suficiente já que isto não evita que meu banco de dados, em um momento de desespero, acabe por utilizar 6vCPUs. Por isto, a ideia aqui é pegar este banco de dados e migrar ele para outro servidor.

Para que seu sistema não fique fora durante todo o tempo da migração e para minimizar todo e qualquer impacto, vamos pegar os novos recursos e montar uma instância de banco de dados mínima. Esta estrutura servirá de base para configurarmos o que virá a ser nosso servidor final.

Nova instância

Tela de nova instância do DialCloud +

Como pode ver montei uma pequena instância com os recursos mínimos para que eu pudesse configurar meu novo servidor de banco de dados.

Migrando meu banco de dados

E ai vem a grande dica! Como agora tenho um servidor que será responsável apenas por receber e responder consultas de banco, posso simplesmente ignorar a instalação de vários módulos e aplicações de servidor que acabam por consumir um pouco do meu processamento e instalar apenas por exemplo o MySQL. Menos recurso para aplicações, mais recurso para o banco.
Ao terminar a instalação da nova instância eu preciso exportar o banco que está em produção e então importar no servidor novo. Apesar de rápido, este é o momento que exige maior atenção, pois terei que colocar meu serviço em manutenção momentânea para que não perca nenhum dado no meio do processo. Afinal enquanto faço isto, meus usuários estariam utilizando meu app.

Outra dica é não fazer isto em um dia ou horário de pico de acesso, assim você terá mais tranquilidade para fazer o processo.

Agora eu preciso fazer minha aplicação comunicar com este novo banco de dados, mas não se preocupe. Com a criação da nova instância minha tela de network mostra uma nova rede interna que foi criada entre estas 2 instâncias cloud. Basta eu pegar o ip interno referente ao servidor de banco de dados e colocar no host das configurações de banco de dados da minha aplicação.

Tela de rede interna no DialCloud +

Tela de rede interna no DialCloud +

Para garantir mais proteção aos meus dados, vou configurar o acesso do ip externo apenas para a instância de aplicação. Faço isso configurando o Port forwarding e no caso do serviço do DialCloud+ libero o acesso externo no firewall (Esta é uma proteção a mais disponibilizada na DialHost gratuitamente) através do Outbound Firewall.

Tela de Port Forwarding no DialCloud +

Tela de Port Forwarding no DialCloud +

Realocando recursos

Tá mas, meu processo que exigia mais recursos tem bem menos recursos. Bem, como eu disse antes, esse era um servidor base para a migração. Agora é só ir nas configurações das instâncias e escalonar os recursos. Assim, você poderá destinar mais para seu banco de dados e menos para sua aplicação.

Concluindo

Esta arquitetura possui uma distribuição mais simples se comparada com a de cluster de dados. Por ser mais simples, ela pode ser mais comum para o dia a dia de diversos projetos. Ainda assim, ela é uma ótima opção pois propicia:

  • Menor custo com recursos de máquina.
  • Melhor distribuição das responsabilidades do servidor o que falicita seu escalonamento.
  • Maior segurança dos dados, já que seu banco de dados não terá acesso por outro lugar que não sua aplicação.
Tenha controle e flexibilidade nos seus recursos com DialCloud +. Servidores em cloud com load balancing, VPN e todo o controle de rede que você precisa. Saiba Mais.

 

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.

Cluster de banco de dados – Eficiência em projetos de grande tráfego

Cluster de banco de dados

imagem ilustrativa

No mês de fevereiro abordamos sobre a mudança de uma hospedagem compartilhada para uma hospedagem cloud e quais fatores devem ser levados em conta para esta decisão. Agora vamos para um assunto mais avançado e focado no desempenho de consultas e escritas em banco de dados. A criação de cluster de banco de dados.

A arquitetura de cluster de banco de dados é formada pela redundância do seu banco de dados em 2 instâncias ou mais instâncias. Estas instâncias, então, são balanceadas de forma a dividir com eficiência a quantidade de requisições ao banco. Mas, vamos por partes. Neste artigo pretendemos mostrar como funciona esta arquitetura.

Cluster de banco de dados

Exemplo 1 – Arquitetura simples de cluster com configuração Master-Master

Camada de Aplicação

A camada de aplicação é a parte que terá contato com o usuário e ela deverá ser inteligente para disparar requisições de escrita e leitura para as instâncias responsáveis por cada ação.

No diagrama exemplificado acima temos uma estrutura mais simples, onde todo o cluster faz leitura e escrita (configuração master-master). Neste caso apenas a camada de load balancing será o suficiente para balancear a carga.  Mas em um projeto de alto fluxo isto ainda pode gerar gargalos de requisição e assim é melhor dividir as responsabilidades ainda mais.

No exemplo abaixo podemos ver uma arquitetura que divide o banco de dados em 4 instâncias, sendo 2 responsáveis por leituras e 2 responsáveis por escrita.

Cluster de banco de dados

Exemplo 2 – Arquitetura dividida de cluster com Load Balancer para banco de leitura e escrita.

Camada Load Balancer

A camada Load Balancer será responsável por receber a requisição da aplicação e balancear a carga de forma efetiva para seus respectivos bancos de dados. No caso do exemplo 1, a aplicação vai simplesmente mandar a requisição e o load balancer vai dividir cada requisição para cada instância de banco de dados sem nenhuma interferência da aplicação.

No exemplo 2, a camada de aplicação irá trabalhar em conjunto com 2 load balancer. Sendo, um dividindo as cargas com os bancos de leitura e o outro com os bancos de escrita.

Assim, a camada Load Balancer será responsável por nunca deixar uma instância de banco de dados sobrecarregada enquanto a outra estiver ociosa. Com isto, seus serviço mantém maior disponibilidade para um momento de alto fluxo de processos.

Aqui na Dialhost, por exemplo, você consegue fazer esta configuração do Load Balancer através do painel de controle do DialCloud +

Camada do cluster de banco de dados

Esta é a camada principal da arquitetura e se não for bem planejada tudo pode ir por água abaixo. A primeira coisa a se pensar é qual o nível de gargalo seu sistema se encontra hoje. Caso seja um gargalo geral de requisições porque seu banco de dados encontra-se todo em apenas uma instância o primeiro exemplo pode ser o suficiente para você. Mesmo tendo uma divisão mais básica onde todas as instâncias leem e escrevem, o fato de ter uma nova instância para dividir a carga de processos já irá equilibrar o uso de recursos.

Vale aqui, analisar o nível de complexidade do sistema em questão. Para um portal com muito conteúdo, como por exemplo globo.com, esta arquitetura do exemplo 1 pode ser ineficiente, dada a quantidade de dados que lidos e inseridos a todo momento.

Após escolher entre o exemplo 1 ou o exemplo 2 é necessário configurar todas as instâncias para que elas mantenham sincronismo de dados. Qualquer falha de sincronismo aqui pode acabar com a estabilidade do seu sistema. Isto porque, um dado inserido no banco da instância X não conseguirá ser lido na instância Z ou Y. A falta de sincronismo na verdade, é um dos grandes contras desta arquitetura.

Finalizando

O cluster de dados se bem montado otimiza bastante as requisições ao seu banco de dados. Mas, como podemos ver ele possui prós e contras em sua utilização.

Prós

  • Melhora a performance de escrita no banco de dados, já que elas podem ser espalhadas em infinitos bancos de dados;
  • Permite que seu sistema escalone em seu cloud sem limitações;
  • Pode ser ainda mais otimizado, disponibilizando bancos somente para leitura e outros para escrita;

Contras

  • A aplicação deve ser inteligente para saber balancear a carga entre as instâncias;
  • Se a sincronização de dados for assíncrona, é possível que os dados estejam fora de sincronia por questões de segundos;
  • Se o banco de dados master (central) falhar, é preciso arrumar-lo para que o cluster volte a funcionar;
Tenha controle e flexibilidade nos seus recursos com DialCloud +. Servidores em cloud com load balancing, VPN e todo o controle de rede que você precisa. Saiba Mais.

 

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.

Não faça regras de negócio dentro do SGDB

banco de dados

Imagem ilustrativa

Já se passaram muitos anos desde a década de 90 e ainda assim eu continuo ouvindo e vendo muitas corporações colocando regra de negócio das soluções dentro de um banco de dados. Hoje o cenário de soluções corporativas é outro completamente mais complexo e a arquitetura em duas camadas não oferece características que sustente sua evolução. Resumidamente, soluções construídas em duas camadas não cumprem requisitos como escalabilidade, extensibilidade, manutenibilidade, segurança, perfomance e disponibilidade. Dessa forma, hoje usamos arquitetura n-camadas que é a única que, se bem projetada, pode cumprir todos os requisitos não-funcionais característicos das soluções atuais. Segue um resumo básico dos velhos e já batidos motivos documentados que justificam o não uso de regras dentro de um banco de dados:

1. Acomplamento

Regra dentro do banco de dados viola o princípio SOC, que define que cada pedaço de um sistema precisa estar localizado em um lugar único e exclusivo promovendo isolamento, manutenção, reutilização e futura substituição, sem impacto nas outras partes.

2. Falta de portabilidade

Regras escritas dentro do banco não oferecem portabilidade entre os diferentes produtos concorrentes da mesma filosofia de banco de dados adotado. Na década de 90 já era um transtorno e só tínhamos os famosos SGDB. Hoje, então, a coisa piorou com o surgimento do NoSQL e suas várias opções estruturais.

3. Péssima manutenção e código inflexível

Regras no banco de dados são, na maioria das vezes, escritas usando store procedures  (filosofia procedural da década de 60) que são desprovidos de recursos e as diretrizes da OOP como encapsulamento, agregação, composição, associação, herança e polimorfismo e da mesma forma não conseguem usufruir de nenhum tipo de padrões (patterns) arquiteturais, projetos e programação.

4. Know-how especializado

Regras no banco de dados precisam ser manutenidas por profissionais que detenham conhecimento especializado para aquele produto específico e filosofia de banco de dados adotado, sendo difícil eqou caro de encontrar mercado ou de formá-lo internamente.

5. Problemas de performance

Soluções de grande porte, dotadas de um grande e crescente número de acessos simultâneos e com a execução de regras pesadas vão degradando gradativamente a performance da solução, podendo até (que é o acontece na maioria dos casos) derrubar o serviço, uma vez que os banco de dados são desprovidos de devidos gerenciamentos de recursos encontrados normalmente em MIDDLEWARE relacionados com técnicas de otimizações, tunning, comunicação assíncronas, mensageira (MOM), escalabilidade vertical e horizontal apropriadas aplicadas especificamente na execução das regras de negócios.

6. Ausência de recursos

Regras de negócio normalmente englobam o uso de recursos como lógica binária, manipulação de arquivos PDF, DOC, XLS, XML, JSON. Comunicação com sistemas externos como LDAP, SMTP, FTP, Mensageira, SOAP, REST etc. Os SGDB normalmente não possuem API’s disponíveis para estes fins e muitos outros recursos, salvo em casos raros que alguns provedores de SGDB fornece alguma coisa bem limitada e proprietária para tratar um ou outro. A coisa piora por que normalmente não existe abertura para se acrescentar uma API de terceiros para dentro do banco.

Conclusão

Martin Folwer, no livro Patterns of Enterprise Application Architecture – capitulo 8, escreveu:

“Por todas estas questões, muitas pessoas evitam implementar regras de negócio dentro de um banco de dados. Eu tento me alinhar com esta visão, a menos que haja um grande ganho de desempenho a ser obtido – o que, para ser sincero, frequentemente ocorre. Nesse caso, pego um método de negócio da camada de domínio e alegremente  o transformo em um procedure dentro de um banco de dados. Faço isso apenas em áreas com claros problemas de desempenho, tratando-o como um abordagem de otimização e não como um principio arquitetural”.

Joshua Bloch, no livro Java Effective, escreveu no item 55 (que eu resumi):

“A história das décadas passadas nos mostram que otimização prematura na verdade é mais propenso a causar danos do que benefícios. O caminho da otimização precoce pode levá-lo a uma solução que ainda não seja rápida, arquiteturalmente ruim e, pior de tudo, inflexível e de difícil evolução. Portanto, não tente criar programas rápidos! Na verdade, foque em criar bons programas, usando todos os conceitos, princípios e abordagem necessários. Se um programa bem arquiteturado não for rápido suficiente, a boa arquitetura já estabelecida permitirá que ele seja facilmente otimizado. Não pense em problemas de desempenho enquanto estiver projetando uma solução. Quando terminar a codificação, avalie seu desempenho. Se ele for suficientemente rápido, tudo estará resolvido. Caso contrário, localize a fonte de gargalo usando uma ferramenta de profile e trabalhe nas partes relevantes. Repita esse processo conforme necessário, avaliando o desempenho após cada alteração até apresentar um tempo satisfatório”.

Acredito que todos estes fatos já fornecem base suficiente para que você tenha condições de fazer a sua tomada de decisão.

Até a próxima!

—–

Artigo de Fernando Franzini, publicado no iMasters.

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.

Importando múltiplos arquivos texto

multiplos arquivos

Imagem ilustrativa

Faz alguns meses que publiquei aqui nesta coluna um artigo descrevendo alguns problemas comuns com arquivos texto. Separadores de colunas, identificadores de linhas, caracteres “proibidos” etc. Desta vez eu apresento uma solução simples para importação de múltiplos arquivos texto no SQL Server. De quebra, eu uso também um truque muito interessante apresentado por Grzegorz Oledzki para conversão de texto no formato ‘DD-MM-YYYY’ em datas reconhecidas pelo SGBD.

Alternativas de importação

Existem vários comandos e ferramentas que auxiliam na importação de dados. Alguns são simples e práticos, outros mais complexos e robustos. Basta pensar que, em última análise, um comando BULK INSERT, uma operação de DATA FLOW do SSIS e o utilitário BCP fazem a mesma coisa: importam dados.

Eu sou fã da simplicidade do comando BULK INSERT, apesar dele ter limitações importantes. Talvez a mais séria destas limitações seja não suportar arquivos CSV, que são muito comuns quando se trata de importação de dados.

Por outro lado, o comando BULK INSERT oferece uma série de benefícios interessantes. Alguns deles são:

  1. Reforça a validação de tipos de dados;
  2. Valida restrições do modelo (como chaves estrangeiras);
  3. Permite configurar delimitadores de colunas e marcadores de final de linha;
  4. Permite definir a página de códigos, para trabalhar com caracteres diferentes do ASCII;
  5. Controla o fluxo de importação, comitando transações a cada N registros importados;
  6. Permite desabilitar gatilhos que seriam disparados em consequência da inserção dos novos registros;
  7. Permite definição de layouts de importação, essencial nos casos em que o arquivo importado tem número diferente de colunas em relação à tabela de destino;
  8. Permite definir as linhas inicial e final do arquivo de dados, desprezando eventuais cabeçalhos ou rodapés no arquivo importado;
  9. Controla o tipo de “lock” sobre a tabela;
  10. Possibilita cargas de dados em paralelo, aumentando dramaticamente a velocidade do processo;
  11. Dispensa log da operação de carga de dados e, portanto, não tem impacto sobre o “transaction log” da base;
  12. Especifica um arquivo de output para registrar erros da importação.

 

Estudo de caso

Considere que você recebeu uma solicitação especial de um diretor da sua empresa. Ele lhe passou um conjunto de 50 arquivos texto e pediu para importá-los num banco de dados para fazer algumas análises.

Ele fez questão de passar detalhes do seu pedido: todos os arquivos tem o mesmo layout, exatamente a mesma sequência de campos. Usam o mesmo separador de colunas (caractere de tabulação “\t”) e o mesmo identificador de final do registro (caractere de retorno de linha “\n”). Os 50 arquivos recebem nomes sequenciais, que vão de “Arquivo01.txt” a “Arquivo50.txt”.

A primeira linha dos arquivos é sempre um cabeçalho informando o nome dos campos. Porém os cabeçalhos apresentam rótulos diferentes em cada arquivo. E a quantidade de linhas de dados varia de arquivo para arquivo.

As tabelas usadas

O leitor mais atento já percebeu a descrição acima traz detalhes importantes misturados com informações irrelevantes para a sua tarefa. Mas a vida real é assim mesmo. Mesmo quando lida com um usuário com bastante conhecimento técnico, é comum que aconteçam alguns deslizes e ele suponha que alguns detalhes tenham muito mais importância do que de fato tem.

O primeiro detalhe irrelevante é sobre a variação da quantidade de registros entre os arquivos. Isso não nos afeta, até porque as ferramentas de carga de dados já estão preparadas para isso (importação roda até encontrar o marcador de final de arquivo).

O segundo é sobre os cabeçalhos variáveis. O que interessa no processo importação é o número de linhas que serão desprezadas no início de cada arquivo e a sequência das colunas. Se elas são descritas de forma diferente é irrelevante. Como eu disse, estas linhas de cabeçalho são desprezadas.

Não há nenhuma informação sobre campos que devam ser desprezados. Então eu assumo que devo importar todas as colunas do arquivo. Também não há comentário sobre existência de alguma chave nestes dados. Mas como serão feitas análises, é fundamental que seja criado um índice clusterizado nesta tabela.

Tenha em mente que o SQL Server nunca oferecerá boas performances em consultas se não existir um índice clusterizado na tabela. Isso é tão importante que o SQL Server cria automaticamente um índice clusterizado assim que se define a chave primária da tabela.

Portanto a tabela de destino deverá ter uma chave primária. Para isso, eu adiciono um campo IDENTITY nesta tabela. Veja Listagem 1:

Listagem 1: tabela de destino


1 CREATE TABLE dbo.tbDestino(
2     DestinoID       int identity(1,1) NOT NULL,
3     TipoPessoa      char(2) NOT NULL,
4     NomeCompleto    varchar(200) NOT NULL,
5     Titulo          varchar(8) NULL,
6     Prenome         varchar(50) NOT NULL,
7     NomeDoMeio      varchar(50) NULL,
8     Sobrenome       varchar(50) NOT NULL,
9     Sufixo          varchar(50) NULL,
10     EmailPromo      int NOT NULL,
11     DataAtualizacao datetime NOT NULL,
12  CONSTRAINT PK_Destino PRIMARY KEY CLUSTERED (DestinoID)
13 ) ON PRIMARY
14 GO

Importante notar que agora temos uma tabela com um campo a mais do que os arquivos. É o campo [DestinoID], chave primária da tabela.

Neste ponto, temos que escolher entre dois caminhos. A primeira opção é criar um arquivo de formatação, especificando o mapeamento dos campos entre os arquivos de origem e a tabela de destino. A segunda é criar uma tabela de carga de dados, normalmente chamada de tabela de staging. Esta tabela teria o mesmo número de campos da fonte de dados e a mesma sequência de campos. Todos os campos da tabela de staging usam o tipo VARCHAR, evitando conversões durante o processo de importação.

Como eu disse, meu objetivo é criar um processo de carga de múltiplos arquivos que seja o mais simples possível e menos suscetível a falha. Por conta disso eu escolho usar a tabela de staging. O script de criação desta tabela é apresentado na Listagem 2.

Listagem 2: tabela de staging


1 CREATE SCHEMA staging
2 GO
3
4 CREATE TABLE staging.tbDestino(
5     TipoPessoa      varchar(2)   NULL,
6     NomeCompleto    varchar(200) NULL,
7     Titulo          varchar(8)   NULL,
8     Prenome         varchar(50)  NULL,
9     NomeDoMeio      varchar(50)  NULL,
10     Sobrenome       varchar(50)  NULL,
11     Sufixo          varchar(50)  NULL,
12     EmailPromo      varchar(50)  NULL,
13     DataAtualizacao varchar(50)  NULL
14 ) ON PRIMARY
15 GO

O script de carga de dados

Agora é necessário tratar do comando BULK INSERT. Um detalhe que sempre causa confusão é que ao usarmos esse comando, devemos especificar um caminho de arquivo conforme ele é especificado no servidor onde roda a instância SQL.

No caso atual, os arquivos estão gravados no diretório ‘C:\TEMP’ do servidor. A  Listagem 3 mostra o comando necessário.

Listagem 3: comando BULK INSERT


1 BULK INSERT staging.tbDestino
2 FROM 'C:\Temp\Arquivo01.txt'
3 WITH (
4     FIELDTERMINATOR ='\t',
5     ROWTERMINATOR = '\n',
6     FIRSTROW = 12
7 )
8 GO

Temos 50 arquivos para importar e naturalmente seria possível executar esta tarefa repetindo esta instrução para cada um dos arquivos. Porém, o legal de fazer scripts genéricos é que eles podem ser reutilizados numa gama muito grande de situações. “Reusabilidade” é algo muito importante e você deve estar atento a isso quando cria seus scripts.

Este script de importação de múltiplos arquivos é fácil de criar, mas ainda assim pode ser reaproveitado para qualquer importação de arquivos que tenham nomes sequenciais. Basta criar as variáveis adequadas. No caso, eu uso os seguintes parâmetros:

  • Nome do arquivo (@vchArquivo)
  • Extensão do arquivo (@vchArquivoExtensao)
  • Diretório de origem (@vchDiretorioOrigem)
  • Nome completo da tabela de destino (@vchTabelaDestino)
  • Quantidade de arquivos a importar (@intQTDArquivos)
  • Número de linhas de cabeçalho (@intLinhasCabecalho)
  • Separador de colunas (@vchColuna)
  • Identificador de linha (@vchLinha)

 

A instrução inteira precisa ser montada e guardada numa outra variável que então será executada com a instrução EXEC(). A Listagem 4 mostra este script parametrizado de carga de dados.

Listagem 4: script de carga

 

1 declare @vchArquivo as varchar(50)
2 declare @vchArquivoExtensao as varchar(50)
3 declare @vchDiretorioOrigem as varchar(4000)
4 declare @vchTabelaDestino as varchar(200)
5 declare @intQTDArquivos as integer
6 declare @intLinhasCabecalho as integer
7 declare @vchColuna as varchar(2)
8 declare @vchLinha as varchar(2)
9
10 --insercao dos valores iniciais
11 set @vchArquivo = 'Arquivo'
12 set @vchArquivoExtensao = '.txt'
13 set @vchDiretorioOrigem = 'C:\TEMP\'
14 set @vchTabelaDestino = 'staging.tbDestino'
15 set @intQTDArquivos = 50
16 set @intLinhasCabecalho = 1
17 set @vchColuna = '\t'
18 set @vchLinha = '\n'
19
20 -- declaracao de variaveis de controle
21 declare @intContador as integer
22 declare @vchSQL as varchar(500)
23 declare @vchLinhaInicial as varchar(5)
24
25 set @intContador = 1
26 set @vchLinhaInicial = convert(varchar(5), (@intLinhasCabecalho + 1 ))
27
28
29 --limpa tabela de destino
30 set @vchSQL = 'truncate table ' + @vchTabelaDestino
31 exec(@vchSQL)
32
33 while @intContador <= @intQTDArquivos
34     begin
35
36     --define caminho completo para o arquivo de importação
37     set @vchSQL = @vchDiretorioOrigem + @vchArquivo
38         + convert(varchar(3), @intContador) + @vchArquivoExtensao
39
40     --define a instrucao completa do BULK INSERT   
41     set @vchSQL =  'BULK INSERT ' + @vchTabelaDestino
42         + ' FROM ''' + @vchSQL
43         + ''' WITH (FIELDTERMINATOR =''' + @vchColuna
44         + ''', ROWTERMINATOR = ''' + @vchLinha
45         + ''', FIRSTROW = ' + @vchLinhaInicial + ')'
46
47     -- importa o arquivo
48     exec(@vchSQL)
49
50     --identifica o novo arquivo
51     set @intContador += 1
52     end

O script de conversão de tipos de dados

O passo final é a conversão dos dados para os tipos de dados adequados. Eu costumo tratar esta tarefa em separado da carga de dados, porque é praticamente impossível generalizar qualquer tipo de tratamento. A conversão depende essencialmente do layout do arquivo de dados e, portanto, não há como generalizá-la sem restringir o layout considerado.

Neste estudo, o layout envolve campos VARCHAR na maioria dos casos. Existem apenas três exceções:

  • Campo TipoPessoa, usando CHAR(2)
  • Campo EmailPromo, que tem valores tipo INTEGER
  • Campo DataAtualizacao, com tipo DATE (entenda-se mm/dd/yy)

 

Na tabela de staging todos os campos usam VARCHAR. Mas as conversões de VARCHAR para CHAR ou para INTEGER são automáticas. É preciso tratar apenas da conversão das datas. Elas estão registradas no formato “dd.mm.yyyy”.

Observe que a conversão seria automática se o formato da coluna data fosse “mm/dd/yyyy”.  Mas no nosso caso é necessário haver um tratamento. É aqui que entra um truque que eu encontrei tempos atrás num fórum. É uma sugestão de Grzegorz Oledzki (veja Referências) e é um recurso que eu mantenho na minha “caixa de ferramentas”.

Convertemos as datas usando a função CONVERT(),  especificando o novo tipo de dados como DATE e o parâmetro de formatação 103, que representa o formato “dd/mm/yyyy”.

A Listagem 5 mostra a declaração INSERT com a conversão necessária:

Listagem 5: populando a tabela de destino


1 insert into dbo.tbDestino (
2      TipoPessoa
3     ,NomeCompleto
4     ,Titulo
5     ,Prenome
6     ,NomeDoMeio
7     ,Sobrenome
8     ,Sufixo
9     ,EmailPromo
10     ,DataAtualizacao   
11         )
12 select
13      TipoPessoa
14     ,NomeCompleto
15     ,Titulo
16     ,Prenome
17     ,NomeDoMeio
18     ,Sobrenome
19     ,Sufixo
20     ,EmailPromo
21     ,convert(datetime, DataAtualizacao,103) as DataAtualizacao 
22 from staging.tbDestino
23 GO

Conclusão

Carga de dados é um tópico muito importante para qualquer sistema novo. Neste aspecto, dispor de um script simples e versátil como aquele apresentado neste artigo pode ser de grande utilidade para desenvolvedores e DBAs.

Em resumo, o processo apresentado aqui envolve cinco etapas:

  1. Criação de uma tabela de staging com a mesma sequência de colunas dos arquivos de dados, usando sempre campos VARCHAR();
  2. Criação de uma tabela de pesquisa que use os tipos de dados adequados para cada coluna e também inclua uma chave primária;
  3. Adaptação (se necessário) dos nomes de arquivos de texto para que sejam sequenciais;
  4. Identificação dos parâmetros do processo de importação;
  5. Definição das transformações necessárias entre a tabela de staging e a de pesquisa.

 

Esta é uma solução bastante versátil e de implementação simples. E espero que lhe seja útil assim como tem sido para mim.

Referências

MICROSOFT. BULK INSERT (Transact-SQL). MSDN. MICROSOFT CORP
OLEDZKI, Grzegorz. How to convert a “dd/mm/yyyy” string to datetime in SQL Server? STACKOVERFLOW. Maio/2010
MICROSOFT. CAST and CONVERT (Transact-SQL). MSDN. MICROSOFT CORP

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.

A simplicidade e a importância do Round Robin como técnica de balanceamento

banco de dados

Imagem ilustrativa

Na era da Big Data somos obrigados a criar sistemas cada vez mais robustos que saibam lidar com grande variação de demanda. Serviços na internet, por exemplo, possuem uma variação tão grande de uso, que gera dificuldades para controlar os acessos nos momentos de pico. Os sistemas que precisam ter controle de demanda, mas não o fazem, podem ter inúmeros problemas como, por exemplo, gerar um tempo de resposta inaceitável para seus usuários ou até mesmo fazer com que o sistema fique fora do ar.

Para tentar amenizar o problema de variação de demanda, muitas empresas acabam criando sistemas altamente complexos que realizam a monitoração através de métricas, como medições de CPU e memória para distribuir a carga de trabalho. Com estas métricas, serviços podem ser capazes de mudar seu comportamento caso precisem lidar com muitos acessos, repassando trabalho de uma máquina para outra, caso atinja um limite de CPU ou até mesmo recusando novas requisições para evitar a degradação do sistema, para citar alguns casos. Existem ainda sistemas que trabalham com arquiteturas de prioridade, tratando primeiramente dos processos com maior demanda, tentando amenizar o grande número de acessos.

Existem, contudo, desafios para os sistemas listados acima: testar estas medições é uma tarefa complexa e, por isso, muitas vezes acabam sendo testadas apenas com simulações básicas. Além disso, exigirem manutenções delicadas quase que constantemente. Será que realmente é necessário adicionar tanta complexidade para distribuir trabalho em nosso sistema?

A resposta é: nem sempre é preciso. Uma alternativa é utilizar uma técnica chamada de Round Robin. Esta alternativa foi classificada como uma das mais simples e robustas entre as atuais técnicas utilizadas para problemas de distribuição de carga. Em palavras simples, Round Robin é um sistema que consiste em alguns elementos, sendo cada elemento representado por um processo ou até mesmo uma máquina de um cluster, formando uma fila circular.


No exemplo da figura acima, vamos supor que tenhamos um cluster com 4 máquinas. Cada nova requisição recebida em nosso cluster seria atendida por um destes nós. A primeira seria atendida pelo nó 1, a subsequente pelo nó 2, a seguinte pelo nó 3 e daí em diante até voltar ao nó 1 e reiniciar o processo. Com esta técnica é possível distribuir o trabalho de forma equilibrada para cada máquina disponível sem precisar calcular métricas de balanceamento. Existem inúmeras variações do algoritmo de Round Robin, uma delas, por exemplo, é trabalhar com filas circulares dando uma fatia de tempo de CPU para cada processo.

Neste artigo só estamos discutindo sobre a ideia mais simples, que já é bastante eficaz para a construção de API’s que precisam, por exemplo, de distribuição de carga. Esta técnica remove a necessidade de criar sistemas para monitoração dinâmica e são obviamente construídas de forma muito mais rápida e prática das que fazem balanceamento através de medições de recursos. Esta técnica foi criada antes mesmo de existirem computadores e é até hoje utilizada em larga escala por inúmeros sistemas com diferentes propósitos.

—–

Artigo de Breno Riba, publicado originalmente no iMasters.

DialHost
DialHost

Contamos com nosso DataCenter no Brasil de alta qualidade, estabilidade e confiança para hospedar seu site. Utilize poderosas ferramentas para otimizar o uso do seu serviço e seja sempre atendido de forma rápida e clara pelo nosso atendimento 24h.