Implementar o Processo de ETL Passos (Importar do Google Analytics para o Armazém de Dados)

 

imagem30-04-2018-14-04-34Recentemente, publiquei um post cobrindo os primeiros passos na definição de um processo de ETL. Se você perdeu, você pode querer começar por aí para saber o porquê de armazéns de dados existentes, como criar um ETL infra-estrutura, e como determinar os requisitos de dados. Eu também começou a provocá-parte dois, fornecendo poucos detalhes sobre uma real e ETL exemplo, usando dados do Google Analytics. Neste post, eu vou continuar a carne que o exemplo e mostrar a você como criar o modelo de dados, banco de dados, desenvolver o ETL, e teste/implementar.

Criando o Modelo de Dados

Existem três tipos de tabelas que são centrais para a maioria dos data warehouses e fará parte da maioria dos modelos de dados:

Tabelas De Preparo

Uma boa prática com o ETL é trazer os dados de origem para o seu armazém de dados, sem qualquer transformações.

Os dados são colocados em tabelas de preparo e, em seguida, como transformações ocorrem os dados são movidos para tabelas de relatórios. IuIn disso, eu gosto de manter uma cópia dos dados originais em uma tabela de histórico para que se algum dia eu precisar de dados original para re-processo, que está prontamente disponível. (Espaço em disco é bastante barato e, portanto, a conveniência e a flexibilidade fornecida por ter o original de dados “brutos” é o custo vale a pena.)

Desde que nós estaremos utilizando a API do Google Analytics, só podemos puxar 7 dimensões e 10 métricas. Portanto, vamos ter quatro tabelas de preparo para os nossos dados “brutos”, e quatro tabelas de preparo que irá armazenar os dados históricos (“raw” dados históricos).

(clique na imagem para ampliar)

etl process staging tables

Observe o seguinte em cada tabela de teste:

Usuário Mantida Tabelas

Devido à transformação de requisitos, existem vários mantidas pelo usuário tabelas (tabelas de onde os dados adicionais fornecidos pelos usuários, como os dados não estão disponíveis em outros lugares). Estas tabelas mantidas pelo usuário irá proporcionar melhorias/adições aos dados.

(clique na imagem para ampliar)

screenshot of etl user maintained tables providing additions to the data

O Usuário Mantida Tabelas servem os seguintes fins, no nosso ETL exemplo:

O Usuário Mantida tabelas pode ser mantida através de várias opções, dependendo do número de registros que devem ser mantidos e a frequência de adições e alterações:

Cada um destes requer a aplicação de processos de negócio para garantir a integridade dos dados é mantida.
Clique & Tweet!

Cada um destes requer a aplicação de processos de negócios para garantir a integridade dos dados é mantida. Por exemplo, você não deseja excluir registos que se relacionam com outras tabelas ou que são usados como parte de ETL lógica para derivar campos.

Tabelas De Relatórios

O relatório de tabelas as tabelas usuários irão utilizar para sua análise e emissão de relatórios. Na maioria dos casos, os usuários não precisam de acesso a tabelas de preparo como as tabelas de relatórios e, em alguns casos, o Usuário Mantida tabelas têm a final limpo e transformado os dados necessários para a elaboração de relatórios.

(clique na imagem para ampliar)screenshot of etl process reporting tables

O ga_data tabela mostra que as quatro separado de dados do Google Analytics puxa (dados encontrados nos quatro tabelas de preparo): (1) staging_ga_metrics; (2) staging_ga_goals1; (3) staging_ga_goals2; (4) staging_ga_events) foram Unidos e agregados para a chave primária apropriado nível, e adiciona o novo Canal e Nome do Evento de campos. O ga_data tabela pode ser associada a umt_ga_account tabela para obter o nome da Conta associada com os dados, bem como os nomes intuitivos para os objectivos.

Como uma etapa posterior, você pode criar uma vista dinâmica , que terá o real usuário nomes amigáveis para todos os objetivos, como nomes de coluna. Do ponto de vista seria criado dinamicamente a cada dia, para capturar quaisquer alterações para o usuário amigável objetivo nomes que possam ter ocorrido. No entanto, eu não vou entrar em detalhes para esta solução.

O ga_data tabela não oomente tem o etl_pull_date para mostrar quando os dados foram retirados do sistema de origem, mas também um load_date. O load_date é a data em que os dados foram carregados para a mesa. Isso pode ajudar se você precisar de reprocessar dados históricos. Por exemplo, suponha que é de Março e determinar que a lógica para o Nome do Evento e o Canal deveria ter sido alterado em fevereiro. Fazer as alterações apropriadas para o Usuário Mantida tabelas para rever a lógica e, em seguida, você pode reprocessar os dados de fevereiro para a frente, utilizando os dados históricos no preparo de tabelas históricas. A carga data em ga_data iria mostrar que toda a fevereiro de registros foram re-carregados ou re-processado, em Março, eles teriam de Março de load_date). Tendo o load_date pode ser útil para a resolução de problemas e compreensão quando os dados foram processados e, portanto, as “regras” é usado para transformar os dados.

Criar Banco de dados, Esquemas, Tabelas e Restrições

Banco de dados

Como prática recomendada, o seu ambiente de banco de dados deve ter os seguintes bancos de dados:

Esquemas

Os esquemas são como pastas de arquivo dentro de um banco de dados, eles ajudam a agrupar logicamente os seus dados. Eu sugiro um teste de esquema, onde os dados brutos é carregado, e um relatório esquema onde as tabelas de relatórios estão localizados. Algumas pessoas criam um esquema para cada fonte de dados. Então, se você tem ETL para puxar os dados do Google Analytics e o google AdWords, você pode ter um ga_staging, ga_reporting, aw_staging, e aw_reporting esquemas.

Tudo depende de suas necessidades de negócios e como os dados serão utilizados.

Tabelas e Restrições

Usando o SQL, criar as tabelas e as restrições com base em seu modelo de dados. Muitas ferramentas de modelagem de banco de dados vai levar o modelo que você criou e fornecer-lhe com o SQL para criar as tabelas e restrições.

Lembre-se a recomendação de que seu preparo tabelas não contêm quaisquer restrições (não nulo campos, chaves primárias ou secundárias, etc.), como ele pode impedir que os dados de carregamento. Em vez disso, configure o seu ETL programas para implementar e garantir as restrições são impostas como os dados são transferidos para tabelas de relatórios.

ETL

extract, transform, load

ETL tem três etapas: (1) Extrair; (2) Transformação; (3) a Carga.

Extrato de

Você pode se conectar e extrair dados a partir de fontes através de:etl extract icon

No nosso exemplo, nós usamos o API do Google Analytics, e com Python criamos um programa para utilizar a API para acessar e extrair os dados identificados nos requisitos, extrair os dados para um arquivo CSV. No entanto, nós adicione um toque extra. Temos o umt_ga_account tabela que lista os vários ga_ids que precisamos para extrair dados. Cada ga_id necessita de uma recepção (como os dados do Google Analytics é segregado por ga_id). Então, vamos executar os seguintes passos com o nosso programa Python:

Carga

etl load icon

Ok, então nós não vamos na ordem sugerida pelo ETL (sim, nós fizemos um pouco de luz transformação adicionando o ga_id e o etl_pull_date para a fonte de dados, mas nós vamos fazer mais transformação posterior).

Em vez disso, uma vez que o nosso programa Python tem os dados do Google Analytics em um arquivo CSV podemos utilizar Python para acessar o banco de dados e truncar o adequado preparo da tabela onde os dados serão carregados (excluir todos os registros da tabela) e, em seguida, carregar os dados no nosso arquivo CSV para a tabela temporária.

Como estamos usando o Redshift, podemos utilizar Python para carregar o arquivo CSV resultante para o nosso AWS S3 (instância de armazenamento de disco) e usar uma cópia de comandos para carregá-lo a partir do S3 para o Redshift. Este é um Redshift melhor prática, como utilizar o S3 para carregar dados para o Redshift é significativamente mais rápido do que através de outras ferramentas.

Por exemplo, eu usei o Navicat para carregar 50.000 registros de um arquivo CSV para Redshift e demorou cerca de 20 minutos (depende do número de colunas que você tem e o tamanho dos campos). Os mesmos dados carregados a partir do S3 para o Redshift usando o comando copiar demorou menos de 10 segundos.

Além disso, ao carregar o arquivo(s) para o S3, agora você tem uma cópia de segurança dos dados brutos extraídos.

Nossa fonte de dados está agora na apropriado tabela de preparo no nosso armazém de dados.

Transformação

etl transform icon

Podemos ter o Python executar instruções SQL. A maioria dos nossos ETL pode ser feito rapidamente e eficientemente usando o SQL em vez do Python. No nosso exemplo, precisamos fazer o seguinte (usando staging_ga_metrics dados asan exemplo):

APAGAR
A PARTIR de preparo.staging_ga_metrics_historical
ONDE CAST(ga_id COMO VARCHAR) || CAST(data COMO VARCHAR)
(SELECT DISTINCT ELENCO(sgm.ga_id COMO VARCHAR) || CAST(sgm.data

COMO VARCHAR)

A PARTIR de preparo.staging_ga_metrics sgm);

INSERT INTO teste.staging_ga_metrics_historical
(SELECIONAR *
A PARTIR de preparo.staging_ga_metrics sgm
ONDE CAST(sgm.ga_id COMO VARCHAR) || CAST(sgm.data

VARCHAR) NÃO
(SELECT DISTINCT ELENCO(sgmh.ga_id COMO VARCHAR) ||

ELENCO(sgmh.data VARCHAR)

A PARTIR de preparo.staging_ga_metrics_historical sgmh));

APAGAR
A PARTIR do db.ga_data
ONDE CAST(ga_id COMO VARCHAR) || CAST(data COMO VARCHAR)
(SELECT distinct ELENCO(sgamv.ga_id COMO VARCHAR) ||

ELENCO(sgamv.data VARCHAR)

A PARTIR de preparo.staging_ga_all_metrics_v sgamv);

screenshot of the etl transformation process for reporting

Testar e Implementar

O ETL trabalho está feito, agora é hora de executar Testes de Aceitação do Usuário (UAT), onde os proprietários de negócios validar os dados no armazém de dados corresponde ao que está no Google Analytics, e atende a todos os requisitos. Uma vez que a EMPRESA é completa e assinada, você está pronto para mudar a sua solução para a produção.

Algumas considerações adicionais:

Depois de ter movido tudo em produção, o seu blog vai ser capaz de realizar uma análise mais profunda, relatório com maior confiança, criar visualização e painéis, todos, sem preocupações de obtenção de dados amostrados.

Não Há Mais Restrições!

No exemplo, usamos os dados do Google Analytics, mas você pode criar um ETL programa, para qualquer fonte de dados. ETL irá permitir que você para possuir seus dados, permitindo que você para transformá-lo, melhorá-lo, limpá-lo, tudo o que você precisa fazer a fim de:

De vez em seu próprio armazém de dados, você pode use a ferramenta de relatório de sua escolha para analisar, criar visualizações e painéis de controle, não sendo limitada pela ferramenta de relatório oferecido pela fonte de dados.
Clique & Tweet!

E de vez em seu próprio armazém de dados, você pode usar a ferramenta de relatório de sua escolha (por exemplo, Domo, Tableau, Dados do Google Studio, Alimentação BI, etc.), para analisar, criar efeitos visuais e painéis de controle, não sendo limitada pela ferramenta de relatório oferecido pela fonte de dados.

Crédito Extra – Ganhar Mais Dimensões

Eu mencionei que a API do Google Analytics tem restrições, o que limita o número de Dimensões e Métricas que você pode puxar através de API. ETL, permite superar o número de Métricas puxado, como mostrado pelo Google Analytics exemplo. Você ainda está limitado pelas Dimensões, porque quando você união de seus dados do Google Analytics, você precisa manter o mesmo nível de granularidade (i.e. mesmas dimensões), ou então você vai obter resultados incorretos.

Como encionado, existem um par de “truques” que você pode implementar para ganhar mais uns dimensões:

 

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Name *
Email *
Website

This site uses Akismet to reduce spam. Learn how your comment data is processed.