Python, Power BI e Google Sheets: Integrando ferramentas para visualizar dados em tempo real
Hello, world!
Uma das necessidades de um profissional que trabalhe com pipelines de dados é possuir conhecimento sobre a integração entre as diferentes ferramentas e softwares utilizados na empresa. Muitas vezes, é por meio desse tipo de integração que se consegue otimizar e automatizar o desempenho de um processo. Desta forma, o objetivo deste tutorial é ensinar a integrar Google Sheets (como base de dados), Python (como ferramenta de integração), e Power BI (como ferramenta de visualização) dentro de um processo de extração, leitura e visualização de dados em tempo real — e tudo isso a custo zero.
Ingredientes necessários
Imagine que você está organizando um evento e deseja saber maiores informações sobre o perfil das pessoas que estão se inscrevendo nele. As inscrições estão acontecendo em tempo real, e você precisa ter certeza de que elas estão acontecendo conforme a estratégia de atração e conversão da sua organização e de acordo com as metas definidas no início do planejamento do evento. O que você faria?
Quando estive diante dessa situação, criar um Dashboard para monitoramento de informações em tempo real serviu como uma ótima solução — e é exatamente isso que iremos aprender neste tutorial.
Para isso, os ingredientes que precisaremos são:
- Planilha do Google: representando uma base de dados com informações relacionadas a inscritos em um determinado evento - alimentada em tempo real;
- Python: forma como os dados da planilha serão extraídos, tratados e e enviados ao Power BI;
- Power BI (App): aplicativo utilizado para gerar as visualizações sobre os dados do evento e hospedá-las em um site público.
Observação: os únicos pré-requisitos deste tutorial são possuir conhecimento básico em Python e na criação de gráficos e visualizações no Power BI.
Etapas do projeto
- Criar um conjunto de dados Streaming no Power BI;
- Criar as credenciais de acesso do Google Drive;
- Extrair os dados de uma planilha do Google;
- Tratar os dados coletados;
- Enviar as informações ao serviço do Power BI;
- Hospedar as visualizações geradas em um site público (opcional);
Disclaimer: ainda que os passos 1 e 2 sejam necessários para o projeto, eles não representam necessariamente o foco deste tutorial. Além disso, tendo em vista a extensão do documento, optou-se por demonstrá-los por meio de conteúdos externos (2 videos que totalizam 4 minutos de visualização — é rapidinho, viu?).
… E mãos à obra.
Criando um Streaming Dataset no Power BI
O primeiro passo é criar um conjunto de dados streaming no Power BI App (https://app.powerbi.com) e configurá-lo para representar as mesmas colunas da sua planilha de dados. Caso você não saiba criá-lo, basta seguir os passos deste vídeo até a duração de 2:20 minutos.
Ponto de atenção: não esqueça de colocar o nome das colunas e o tipo dos dados de entrada referentes à composição da sua planilha. (e não referentes ao exemplo citado no vídeo).
Como dito anteriormente, neste tutorial coletaremos dados sobre o processo de inscrição em dois eventos distintos, chamados “Evento A” e “Evento B”. Tais informações estão dentro da mesma planilha, onde os dados de cada evento encontram-se separados em abas diferentes. A primeira aba (referente ao Evento A), contém as colunas: data de inscrição, e-mail, idade e estado. A segunda aba (referente ao evento B), contém as colunas data de inscrição, e-mail, empresa e cargo. Desta forma, seria necessário criar dois conjuntos de dados streaming, um para cada evento, com as suas respectivas colunas e tipo de dados. Caso você tenha apenas uma base de registros, crie um único conjunto de dados streaming (streaming dataset).
Por fim, habilite o botão ‘Historic Data Analysis’, salve a(s) Push URL gerada(s) para uso posterior e crie um Relatório (Report) a partir do streaming dataset que você acabou de criar clicando no botão em vermelho, conforme ilustrado na figura abaixo. Vale lembrar que este tutorial não irá aprofundar as partes de criação e design de Dashboards no Power BI.
Criando as credenciais de uso do Google
Para permitir que o Python tenha acesso à planilha de dados (ler e editar), é preciso criar as credenciais de acesso do Google. Caso você não saiba criá-las, basta seguir os passos deste vídeo até a duração de 1:35 minutos.
Ponto de atenção: ao compartilhar a planilha com o seu
client_email, não esqueça de selecionar a opção que permitirá ler e editar o documento.
Extraindo os dados
Enfim, ao código! Por aqui, apenas importe as bibliotecas descritas abaixo.
O bloco a seguir será o responsável por fornecer ao Python o acesso necessário para coletar as informações da planilha de dados do Google. Para isso, definimos a variável scope com os scopes de atuação do projeto e autorizamos a leitura do arquivo que contém as nossas credenciais.
Lembra daquele arquivo .DAT gerado quando você criou as credenciais do Google? Coloque-o na mesma pasta em que estiver executando o seu projeto e e escreva o nome do arquivo no argumento da variável credentials, como ilustrado na figura abaixo.
Neste exemplo, o nome do meu arquivo era “Teste-2403a88196e3”.
Estrutura da planilha de dados
A próxima etapa consiste em criar um cenário que promova certa escalabilidade ao projeto. Para evitar a necessidade de gerar um arquivo de código para cada visualização criada, iremos criar uma aba chamada “Mãe”, que atuará como um painel de controle na estrutura da planilha a qual extrairemos os dados.
Onde:
- A coluna
Sheet Namedeve comportar o nome exato das abas que contém as inscrições de cada evento. Neste exemplo, Evento A e Evento B; - A coluna
Countrepresenta o número de registros contidos nas respectivas abas, e é obtido por meio uma fórmula simples na própria célula do Google Sheets=CONT.VALORES('Evento A'!A2:A)'); - A coluna
URL Pushdeverá conter a(s) URL gerada(s) anteriormente no Power BI; - Por fim, a coluna
Auxrepresenta a quantidade de registros lidos pelo código na última vez em que ele foi executado. Isto é, sempre que executado, o código irá comparar os valores nas colunasCounteAux, e atualizar a colunaAuxcom o número de registros atuais em cada aba. CasoCount > Aux, existem novas inscrições em determinado Evento — o que servirá de gatilho para a execução da parte do código que envia as informações ao Power BI.
Essa comparação é necessária pois a API do Power BI App (até a data deste tutorial) não reconhece se os registros x, y, z já foram lidos em iterações passadas. Assim, se o código não apresentar uma etapa de verificação que garanta que apenas os novos registros de cada iteração sejam enviados ao Power BI, o Dashboard final gerado estará representando informações duplicadas, e portanto, falsas. Assim:
É de extrema importância que apenas os novos registros de cada iteração sejam enviados ao serviço do Power BI.
A seguir, explicaremos a função principal e a verificação que garante que estaremos enviando apenas os novos registros de cada iteração ao Power BI.
Enfim, a função principal!
Agora que vimos a estrutura da planilha de dados, é hora de definir suas variáveis no código. A primeira variável é a spreadsheet_key, e deve conter o ID da sua planilha de dados do Google, encontrado no próprio link da planilha:https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=1
A seguir, na variável control_worksheet, o argumento deve conter o nome dado à aba que representa o painel de controle da sua planilha — neste exemplo, o nome dado foi “Mãe”.
Lembrete: na aba que representa o Painel de Controle, cada linha a partir do cabeçalho contém informações sobre um Dashboard específico.
Desta forma, a função principal (ilustrada no bloco abaixo) inicia-se pela leitura de tais informações por meio do: for i in range (2, rows+1), onde a variável rows armazena o número de linhas totais da aba Mãe — neste exemplo, rows = 3.
Ao realizar a leitura das informações sobre cada Dashboard, entra a etapa de verificação citada anteriormente: caso a variável counttab seja maior do que a variável aux — isto é, o número total de registros atuais no Dashboard seja maior do que o número de registros lidos na última execução do código — apenas os novos registros, representados pela variável diff, serão enviados ao Power BI.
Dica de ouro: configure a sua planilha do Google para ordenar os seus registros de forma descendente pela data de entrada! Desta forma, ao executar o código, será necessário percorrer apenas os primeiros registros da planilha ao invés de ter que percorrer todos os registros até chegar aos desejados. Na prática, imagine uma base com milhões de registros: você prefere que o código tenha sempre que percorrer todos os milhões de registros até encontrar o que é desejado, ou apenas as primeiras dezenas/centenas de registros? (:
- Disponibilizei no meu GitHub (link no final da página) um script que realiza essa função de maneira automática para qualquer planilha do Google.
Em seguida, as variáveis worksheet e table são responsáveis por coletar os dados referentes às inscrições nos Eventos A e B. Como elas estão dentro do for, para cada iteração, o código estará trabalhando com as informações sobre a aba respectiva àquela iteração (representadas pela variável tabname). Além disso, como tais variáveis encontram-se também dentro do if, os dados de cada evento só serão coletados se forem aprovados pela etapa de verificação anterior — economizando tempo e processamento.
Desta forma, com uma única execução manual, o código irá realizar as seguintes tarefas:
- Ler as informações relativas à cada dashboard na aba “Mãe”;
- Verificar, para cada Dashboard, a existência de novos registros em relação ao valor lido na última execução do código;
- Em caso positivo, ler os novos registros na abas respectivas;
- Enviar os novos registros à interface do Power BI.
Antes de enviados ao Power BI, os dados devem ser transformados em um pandas Dataframe e convertidos em formato JSON para que a REST API do Power BI esteja apta a reconhecê-los.
Assim, a variável data_df transforma em DataFrame apenas com os novos registros, que conforme citado anteriormente, são ordenados de forma descendente, isto é, os últimos registros entram sempre no topo da tabela. Desta forma, define-se o range do DataFrame como sendo da linha 1 (à partir do cabeçalho) até a linha representada pela variável diff, cujo valor representa a quantidade de novos registros.
data_df = pd.DataFrame(table[1:diff+1], columns=HEADER)Por fim, o comando control_worksheet.update_cell(i, 4, counttab) atualiza o valor da coluna Aux na aba de Painel de Controle com o número atual de registros lidos pelo código.
O bloco abaixo mostra um exemplo de resposta do código quando executado duas vezes, onde: na primeira execução, não foram encontrados novos registros; na segunda execução, foram encontrados novas inscrições para os dois eventos.
(Execução 1)Evento A
Número de registros atuais: 47
Número de registros na última execução: 47Evento B
Número de registros atuais: 21
Número de registros na última execução: 21(Execução 2)Evento A
Número de registros atuais: 51
Número de registros na última execução: 47
Existem novos registros na aba: Evento A
<Response [200]>Evento B
Número de registros atuais: 23
Número de registros na última execução: 21
Existem novos registros na aba: Evento B
<Response [200]>
Caso você receba erro de resposta 401 ou 404, certifique-se de que:
- a URL Push fornecida pelo Power BI e lida pelo código é a mesma;
- o nome das colunas criadas no streaming dataset e o nome das colunas na sua aba de dados (não do painel de controle) são idênticas;
- não existem dados perdidos na aba, isto é, fora do range das colunas elencadas na criação do streaming dataset;
Tá quase, prometo!
O último bloco é responsável por executar a função principal de maneira automática a partir da primeira execução manual. Para isso, o primeiro argumento de scheduler.add_job() deve conter o nome da função a ser executada. O intervalo de tempo de execução deve ser definido no terceiro argumento (neste exemplo, em 25 segundos).
Hospedar as visualizações geradas dentro de um website
Para gerar o embed-link responsável por hospedar os Dashboards dentro do seu website, basta entrar na tela do seu Report e clicar em File > Publish to Web. Assim, todas as pessoas que tenham acesso à pagina onde o conteúdo foi hospedado estarão aptas a visualizar os Dashboards criados.
Glossário
- Dashboard: conjunto de gráficos e visualizações informativas;
- Dataframe: estrutura de dados em formato de tabela;
- Embed-link: endereço para incorporar um conteúdo em uma página web;
- Pipeline: fluxo pelo qual percorrem os dados de uma organização;
- Push URL: endereço pelo qual envia-se dados até o serviço do Power BI;
- Report: funcionalidade do Power BI para criação de Dashboards;
- REST API: tipo de arquitetura web para receber e enviar requisições;
- Streaming Dataset: funcionalidade do Power BI para criação de conjuntos de dados que permite comunicação em tempo real com o seu serviço.
That’s all, folks!
Obrigado por ter chegado até aqui!
Este artigo foi a minha primeira contribuição Open-Source e publicação no Medium. Comecei a programar há 3 meses e desde então sempre tive vontade de contribuir e escrever para os blogs de dados que acompanho.
O foco deste tutorial foi, acima de tudo, trazer uma solução simples em termos técnicos e de custo zero. Além disso, procurei trazer algumas pequenas dicas sobre como melhorar o desempenho e tempo de processamento do código — coisas que como iniciante em programação, me ajudaram bastante a entender um pouco melhor da mentalidade necessária para um programador.
Tenho certeza de que ainda há várias maneiras de melhorar o processo que descrevi, então será um prazer ouvir suas críticas e sugestões.
Vamos conversar?
Fique à vontade para entrar em contato através de um dos links abaixo:
LinkedIn
https://www.linkedin.com/in/tiagomanke/
Instagram
https://www.instagram.com/tiagomanke/
GitHub:
https://github.com/tgom94
