O que é ETL?
ETL é um processo que extrai dados de vários sistemas de origem, altera-os (através de cálculos, concatenações e assim por diante) e, em seguida, coloca-os no sistema Data Warehouse. ETL significa Extrair, Transformar e Carregar.
É fácil acreditar que construir um data warehouse é tão simples quanto extrair dados de várias fontes e alimentá-los em um banco de dados de data warehouse. Isso está longe de ser o caso e é necessário um procedimento ETL complicado. O processo ETL, que é tecnicamente complexo, envolve a participação ativa de uma variedade de partes interessadas, incluindo desenvolvedores, analistas, testadores e executivos seniores.
Para preservar seu valor como ferramenta de tomada de decisão, o sistema de data warehouse deve se desenvolver em sincronia com os desenvolvimentos de negócios. ETL é um processo regular (diário, semanal, mensal) de um sistema de data warehouse que deve ser ágil, automatizado e devidamente documentado.
Como o ETL funciona?
Aqui vamos aprender como funciona o processo de ETL passo a passo:
Passo 1) Extração
Os dados são extraídos do sistema de origem e colocados na área de preparação durante a extração. Caso alguma transformação seja necessária, ela é realizada na área de staging para que o desempenho do sistema de origem não seja prejudicado. A reversão será difícil se os dados danificados forem transferidos diretamente da fonte para o banco de dados do data warehouse. Antes de mover os dados extraídos para o Data warehouse, eles podem ser validados na área de preparação.
Os data warehouses podem combinar sistemas com diferentes hardwares, sistemas de gerenciamento de banco de dados, sistemas operacionais e protocolos de comunicação. Os armazéns de dados devem combinar sistemas com DBMS, hardware, sistemas operacionais e protocolos de comunicação diferentes. As fontes podem incluir programas legados, como mainframes, aplicativos personalizados, dispositivos de ponto de contato, como caixas eletrônicos e comutadores de chamada, arquivos de texto, planilhas, ERP, dados de fornecedores e parceiros e assim por diante.
Assim, antes de extrair dados e carregá-los fisicamente, é necessário um mapa lógico de dados. A conexão entre as fontes e os dados de destino é mostrada neste mapa de dados.
Três métodos de extração de dados:
- Extração Parcial – Se o sistema de origem o alertar quando um registro for modificado, essa é a maneira mais simples de obter os dados.
- Extração parcial (sem notificação de atualização) – Nem todos os sistemas podem fornecer uma notificação quando ocorre uma atualização; mas, eles podem indicar os registros que foram alterados e fornecer a extração desses registros.
- Extrato completo – Certos sistemas são incapazes de determinar quais dados foram alterados. Nesse cenário, a única forma de retirar os dados do sistema é realizar uma extração completa. Essa abordagem requer um backup da extração anterior no
mesmo formato em mãos para identificar as mudanças que foram feitas.
Independentemente do método adotado, a extração não deve impactar no desempenho ou no tempo de resposta dos sistemas de origem. Esses são bancos de dados de produção em tempo real. Qualquer desaceleração ou travamento pode ter impacto nos resultados da empresa.
Passo 2) Transformação
Os dados recuperados do servidor de origem são brutos e inutilizáveis em seu estado original. Como resultado, ele deve ser limpo, mapeado e transformado. Na realidade, esta é a etapa principal em que o processo ETL agrega valor e transforma dados para produzir relatórios de BI significativos.
É um conceito chave de ETL no qual você aplica uma coleção de funções aos dados extraídos. movimento direto or passar por issoa é o tipo de dados que não requer nenhuma transformação.
Você pode executar operações personalizadas em dados durante a etapa de transformação. Por exemplo, suponha que o cliente deseja uma soma de receita de vendas que não existe no banco de dados. ou se o nome e o sobrenome em uma tabela estiverem em colunas separadas. Antes de carregar, eles podem ser concatenados.
A seguir estão alguns exemplos de problemas de integridade de dados:
- Ortografias diferentes do mesmo indivíduo, como Prashant, Parshant e etc.
- Existem muitas maneiras de representar o nome de uma empresa, como Google, Google Inc.
- Vários nomes, como Cleaveland e Cleveland, são usados.
- É possível que vários números de conta sejam produzidos por diferentes aplicativos para o mesmo cliente.
- Alguns arquivos de dados necessários são deixados em branco.
Etapa 3) Carregando
O estágio final no processo ETL é carregar os dados no banco de dados de data warehouse de destino. Um grande volume de dados é carregado em um período de tempo relativamente curto em um data warehouse típico. Como resultado, o processo de carregamento deve ser otimizado para desempenho.
Na ocorrência de uma falha de carga, devem ser implementados procedimentos de recuperação para que as operações possam ser reiniciadas a partir do ponto de falha sem comprometer a integridade dos dados. Os administradores do Data Warehouse devem monitorar, continuar e interromper as cargas com base no desempenho do servidor.
Tipos de Carregamento:
- Carga inicial - preenchendo todos
as tabelas do Data Warehouse - Carga Incremental - implementação em curso
modificações conforme necessário em uma base regular - Atualização completa — limpando o conteúdo
de uma ou mais tabelas e recarregá-los com novos dados
Verificação de carga
- Verifique se os dados do campo-chave não estão ausentes ou nulos.
- As exibições de modelagem com base nas tabelas de destino devem ser testadas.
- Examine os valores combinados3 e as medidas calculadas.
- Verificações de dados nas tabelas de dimensão e histórico.
- Examine os relatórios de BI na tabela de dimensões e fatos carregados.
Configurando ETL usando PythonScript
Como resultado, você deve executar o Extract Transform Load (ETL) básico de vários bancos de dados para um data warehouse para fazer a agregação de dados para inteligência de negócios. Existem vários pacotes ETL disponíveis que você acredita serem excessivos para seu caso de uso básico.
Mostrarei como extrair dados do MySQL, SQL-server e firebird neste artigo. Usando o Python 3.6, transforme os dados e carregue-os no SQL-server (armazém de dados).
Primeiro de tudo, temos que criar um diretório para o nosso projeto:
python_etl |__main.py |__db_credentials.py |__variáveis.py |__sql_queries.py |__etl.py
Para configurar o ETL usando Python, você precisará gerar os seguintes arquivos no diretório do seu projeto.
- db_credenciais.py: Deve ter todas as informações necessárias para se conectar a todos os bancos de dados. como senha do banco de dados, número da porta, etc.
- sql_queries.py: Todas as consultas de banco de dados comumente usadas para extrair e carregar dados no formato String devem estar disponíveis.
- etl.py: Conecte-se ao banco de dados e realize as consultas necessárias executando todos os procedimentos necessários.
- principal.py: Responsável por gerenciar o fluxo de operações e executar as operações essenciais em uma ordem especificada.
Nesta seção de sql_queries.py, este é o local onde armazenaremos todas as nossas consultas sql para extração de bancos de dados de origem e importação para nosso banco de dados de destino (armazém de dados)
Configurar credenciais e variáveis do banco de dados
Em variables.py, crie uma variável para registrar o nome da base.
datawarehouse_name = 'seu_datawarehouse_name'
Configure todas as strings e credenciais de conexão do banco de dados de origem e destino em db_credentials.py conforme mostrado abaixo. Salve a configuração como uma lista para que possamos iterá-la sempre que necessário em muitos bancos de dados posteriormente.
from variables import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (target db, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', ' database': '{}'.format(datawarehouse_name), 'user': 'your_db_username', 'password': 'your_db_password', 'autocommit': True, } # sql-server (source db) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'your_sql_server', 'database': 'db1', 'user': 'your_db_username', 'password': 'your_db_password', ' autocommit': True, } ] # mysql (source db) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'database': 'db_1', } , { 'user': 'your_user_2', 'password': 'your_password_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (source db) fdb_db_config = [ { 'dsn' : "/seu/caminho/para/fonte.db", 'usuário': "seu_usuário", 'senha': "você r_senha", } ]
Consultas SQL
Nesta seção de sql_queries.py, este é o local onde armazenaremos todas as nossas consultas sql para extração de bancos de dados de origem e importação para nosso banco de dados de destino (armazém de dados).
Temos que implementar várias sintaxes para cada banco de dados porque estamos trabalhando com várias plataformas de dados. Podemos fazer isso separando as consultas com base no tipo de banco de dados.
# consultas de exemplo, serão diferentes em diferentes plataformas db firebird_extract = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO tabela (coluna_1, coluna_2, coluna_3) VALUES (?, ?, ?) ''') firebird_extract_2 = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (column_1, column_2, column_3) VALORES (?, ?, ? ) ''') sqlserver_extract = (''' SELECT sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 FROM sqlserver_table ''') sqlserver_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''' ) mysql_extract = (''' SELECT mysql_column_1, mysql_column_2, mysql_column_3 FROM mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') # exportando consultas class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # cre ate instances for SqlQuery class fbd_query = SqlQuery(firebird_extract, firebird_insert) fbd_query_2 = SqlQuery(firebird_extract_2, firebird_insert_2) sqlserver_query = SqlQuery(sqlserver_extract, sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # store as list for iteration fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Extrair carga de transformação
Para configurar o ETL usando Python para as fontes de dados mencionadas acima, você precisará dos seguintes módulos:
# módulos python import mysql.connector import pyodbc import fdb # variables from variables import datawarehouse_name
Podemos usar duas técnicas nisso: etl() e etl_process().
etl_process() é o procedimento para estabelecer uma conexão de fonte de banco de dados e chamar o método etl() com base na plataforma de banco de dados.
E no segundo método, que é o método etl(), ele executa a consulta de extração primeiro, depois armazena os dados SQL nos dados variáveis e os insere no banco de dados de destino, que é nosso data warehouse. A transformação de dados pode ser realizada alterando a variável de dados do tipo tupla.
def etl(query, source_cnx, target_cnx): # extrai dados do banco de dados de origem source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # carrega dados no banco de dados do warehouse if data: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('dados carregados no warehouse db') target_cursor.close() else : print('dados estão vazios') def etl_process(queries, target_cnx, source_db_config, db_platform): # estabelece conexão com o banco de dados if db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_config) elif db_platform == 'sqlserver': source_cnx = pyodbc.connect(**source_db_config) elif db_platform == 'firebird': source_cnx = fdb.connect(**source_db_config) else: return 'Erro! plataforma de banco de dados não reconhecida' # percorre consultas sql para consulta em consultas: etl(query, source_cnx, target_cnx) # fecha a conexão de banco de dados source_cnx.close()
Juntando tudo
Agora, na próxima etapa, podemos percorrer todas as credenciais em main.py e executar o etl para todos os bancos de dados.
Para isso, temos que importar todas as variáveis e métodos necessários:
# variáveis de db_credentials importam datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config de sql_queries importam fbd_queries, sqlserver_queries, mysql_queries de variáveis importam * # métodos de etl import etl_process
O código neste arquivo é responsável por iterar as credenciais para se conectar ao banco de dados e executar as operações ETL usando Python necessárias.
def main(): print('starting etl') # estabelece conexão com o banco de dados de destino (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # percorre credenciais # mysql para configuração em mysql_db_config: try: print("carregando db: " + config['database']) etl_process(mysql_queries, target_cnx, config, 'mysql') except Exception as error: print("etl for {} has error".format(config['database'])) print ('mensagem de erro: {}'.format(error)) continue # sql-server for config in sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') except Exception as error: print("etl for {} has error".format(config['database'])) print('error message: {}'.format(error)) continue # firebird for config in fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') except Exception as error: print("etl for {} has error".format(config ['banco de dados'])) print('mensagem de erro: {}'.fo rmat(error)) continue target_cnx.close() if __name__ == "__main__": main()
No seu terminal, digite python main.py e você acabou de criar um ETL usando um script python puro.
Ferramentas ETL
Existem várias ferramentas de Data Warehousing no mercado. Aqui estão alguns dos exemplos mais famosos:
1. MarkLogic:
MarkLogic é um sistema de armazenamento de dados que usa uma variedade de recursos de negócios para tornar a integração de dados mais fácil e rápida. Ele pode consultar muitos tipos de dados, como documentos, relacionamentos e metadados.
https://www.marklogic.com/product/getting-started/
2. Oráculo:
Oracle é o banco de dados mais popular do setor. Oferece uma ampla variedade de soluções de Data Warehouse para serviços locais e na nuvem. Ajuda na melhor experiência do cliente, aumentando a eficiência operacional.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift é uma solução de armazenamento de dados da Amazon. É uma solução simples e econômica para analisar vários tipos de dados com SQL padrão e ferramentas de inteligência de negócios existentes. Também permite a execução de consultas complexas em petabytes de dados estruturados.
https://aws.amazon.com/redshift/?nc2=h_m1
Conclusão
Este artigo forneceu uma compreensão profunda do que é ETL, bem como um tutorial passo a passo sobre como configurar seu ETL em Python. Ele também forneceu uma lista das melhores ferramentas que a maioria das organizações usa atualmente para criar seus pipelines de dados ETL.
A maioria das organizações hoje em dia, por outro lado, possui uma enorme quantidade de dados com uma estrutura altamente dinâmica. Criar um pipeline ETL do zero para esses dados é um procedimento difícil, pois as organizações terão que usar um grande número de recursos para criar esse pipeline e garantir que ele possa acompanhar o alto volume de dados e as alterações do Schema.
Sobre o autor
Prashant Sharma
Atualmente, estou cursando meu Bacharelado em Tecnologia (B.Tech) no Vellore Institute of Technology. Estou muito entusiasmado com a programação e suas aplicações reais, incluindo desenvolvimento de software, aprendizado de máquina, aprendizado profundo e ciência de dados.
Espero que goste do artigo. Se você quiser se conectar comigo, então você pode se conectar em:
ou para qualquer outra duvida voce pode enviar um e-mail para mim também
Relacionado
- '
- "
- Conta
- ativo
- Todos os Produtos
- Amazon
- analítica
- aplicações
- ÁREA
- artigo
- Automatizado
- backup
- impulsionar
- construir
- Prédio
- negócio
- inteligência de negócios
- chamada
- Cheques
- cleveland
- Na nuvem
- serviços na nuvem
- código
- Comunicação
- Empresa
- da conexão
- continuar
- Criar
- Credenciais
- dados,
- integração de dados
- ciência de dados
- data warehouse
- armazéns de dados
- banco de dados
- bases de dados
- deep learning
- desenvolver
- desenvolvedores
- Desenvolvimento
- Dispositivos/Instrumentos
- Dimensão
- INSTITUCIONAIS
- motorista
- eficiência
- etc.
- execução
- executivos
- Experiências
- Extração
- Extractos
- Falha
- Primeiro nome
- fluxo
- formato
- recentes
- cheio
- guia
- Hardware
- SUA PARTICIPAÇÃO FAZ A DIFERENÇA
- Alta
- história
- Como funciona o dobrador de carta de canal
- Como Negociar
- HTTPS
- identificar
- Impacto
- importador
- Incluindo
- INFORMAÇÕES
- integração
- Inteligência
- questões
- IT
- Chave
- grande
- APRENDER
- aprendizagem
- Line
- Lista
- carregar
- aprendizado de máquina
- de grupos
- mapa,
- mercado
- Mídia
- Mais populares
- mover
- nomes
- notificação
- números
- Oferece
- operando
- sistemas operacionais
- Operações
- oráculo
- ordem
- organizações
- Outros
- Parceiros
- Senha
- atuação
- plataforma
- Plataformas
- Popular
- Produzido
- Produção
- Programação
- Programas
- projeto
- puxando
- Python
- Cru
- em tempo real
- Realidade
- registros
- recuperação
- Relacionamentos
- Relatórios
- Recursos
- resposta
- receita
- Ciência
- Serviços
- conjunto
- Baixo
- simples
- So
- Software
- desenvolvimento de software
- Soluções
- SQL
- Etapa
- Estado
- loja
- lojas
- .
- sistemas
- Target
- tecnologia
- técnicas
- Equipar
- A fonte
- tempo
- Transformação
- tutorial
- Atualizar
- valor
- fornecedores
- volume
- Armazém
- Armazenagem
- semanal
- Atividades:
- trabalho