Quando eu estava realizando um treinamento avançado de Pentaho em um cliente na cidade de Recife, aqui no Brasil, um dos alunos me solicitou uma dica de como acessar planilhas eletrônica que foram desenvolvidas no Google Docs e armazenadas no Drive, usando o PDI ( Pentaho Data Integration ) , de primeira mão já afirmei que poderia usar a API do Google e o Pentaho Data Integration para interagir , mas como sempre o projeto Kettle e o Software Livre me surpreendem, acessando o Marketplace lá estava um plugin prontinho para isso desenvolvido por Brian Zoetewey!
O mesmo tem alguns passos de acesso e segurança a serem configurados que o torna um pouco mais complicado que acessa uma planilha local ( faz sentido! ) , mas uma vez feito os demais são simples de usar.
Bom vamos lá a algumas dicas de como usa-lo!
Obs. Este post esta funcional para a versão 7.1 do PDI caso queira instalar manualmente para versões superiores adicionei o step em https://github.com/ambientelivre/pdi-google-spreadsheet-plugin/blob/master/step/PentahoGoogleSheetsPlugin-4.1-distribution.zip irei atualizar para versões novas quando possível.
- Primeiramente inicie seu Spoon/PDI, e no menu Tools -> Markteplace , procure por Google na pesquisa e clique em install no plugin Google SpreadSheet input/Output.
- Confirme a instalação do plugin e aguarde a instalação finalizar
- Uma mensagem será emitida após a instalação solicitando que reinicie o Pentaho Data Integration / Spoon
- Depois de Reiniciar crie uma nova transformação no PDI, e inclua um Google Spreadsheet Input e deve informar um Email de Serviço do Google ( não é sua conta de gmail deve vegar este id email de serviço) e deve importar uma Chave ou Private Key no formato p12 ( já iremos explicar como gerar o id e chave da API )
- Para gerar um Email de Serviço no Google acesse https://console.developers.google.com/projectselector/iam-admin/iam e será solicitado para criar um Projeto de API ou selecionar um preexistente em sua conta Google.
- Se já tiver criado alguma API simplesmente seleciona conforme a seguir caso contrário realize os passos de criação seguindo o wizard do Google APIs.
- Depois de selecionado acesse o Menu de opções “Contas de Serviço” e gere uma conta de serviço para a API , no momento de geração será ;he perguntado o formato de chave , selecione a opção p12e baixe o arquivo.
- Agora copie em seu Step no PDI e clique no botão Test Connection, se der sucesso podemos seguir e frente.
- Atenção!!! agora você deve acessar seu Driver e compartilhar o documento com o Pseudo usuário 999999999-xyxyxyxyxyxyxyxyxyxxy@developer.gserviceaccount.com para que a mesma fique disponível, após isso clique em Browser e ele ira listar todas as planilhas compartilhadas
- Selecione o arquivo de Planilha desejada e depois seleciona a Planilha do arquivo ( WorkSheet id ( que são as abas de trabalho )
- Depois de selecionar acesse a aba Fields e clique em Obter fields para ter as colunas de sua planilha no Stream de dados do PDI.
- Agora e dar continuidade , pode usar todos os recursos do Pentaho Data Integration , Preview e Todos infinitos Steps de tratamento de Dados.
- da mesma forma que fizemos a leitura podemos fazer a escrita de uma planilha com o step Google Spreadsheet Output sem maiores complicações.
- Outros tratamentos no Google docs podem ser realizados com um pouco mais de conhecimento da API do Google ( https://developers.google.com/drive/v3/reference ) e nosos recursos de REST/SOAP/Http e JavaScript do Pentaho Data Integration e depois adicionar estes dados extraídos (inclusive em tempo real ) á Dashboards, ou Cubos através de integrações bath em datawarehouses.
- Quem também quer ampliar as funcionalidades e criar algo similar o fonte deste plugin se encontra em https://github.com/GlobalTechnology/pdi-google-spreadsheet-plugin
Observação 1: Alguns colegas comentaram que foi necessário fazer conversão de Binary[String] para String conforme imagem:
Observação 2: também foram relatados limitação no número de linhas do arquivo
Referências :
Treinamento Avançado em Pentaho Data Integration :
https://www.ambientelivre.com.br/treinamento/pentaho/data-integration.html
GitHub GlobalTechnology
https://github.com/GlobalTechnology/pdi-google-spreadsheet-plugin
Fórum Internacional da Pentaho:
https://forums.pentaho.com/showthread.php?220537-Kettle-connection-to-Google-Drive
Google Developers
https://developers.google.com/
Does it work with community edition as well?
Thanks
Yes , this is community 🙂 #goopensource
Marcio por acaso é possivel passar o e-mail e a key como parâmetro pelo pan ?
tem sim https://wiki.pentaho.com/display/EAI/Kitchen+User+Documentation
Hmm, beleza, só achei estranho porque dentro das opções do spreadsheet ele não te da a opção de colocar um parâmetro na key, como é feito no google analytics por exemplo só selecionar o arquivo gerado pela api. Vou dar uma olhada melhor aqui, muito obrigado!!
Você esta certo ! não tem o $ no campo então não permite variavel tera de usar o Metadata Injection https://help.pentaho.com/Documentation/8.0/Products/Data_Integration/Transformation_Step_Reference/ETL_Metadata_Injection
Olá Márcio,
No meu caso tive que adicionar um step “select values” para alterar os tipos que estavam como [String] para String.
Exemplo => https://www.dropbox.com/s/t8qrzen23121431/Screen%20Shot%202018-05-16%20at%2015.16.37.png?dl=0
Tentei fazer isso, mas não deu certo:
https://www.dropbox.com/s/di0veqnc34o4v3e/Captura%20de%20Tela%20%2877%29.png?dl=0
https://www.dropbox.com/s/4taho7gq0q76apb/Captura%20de%20Tela%20%2878%29.png?dl=0
Acredito que haja uma limitação desse step para x linhas, pois para outro arquivo que estou tentando inserir com 1200 linhas está dando certo.
Obrigado Marcio vou comentar no post pode ser uma limitação sim!
Vou colocar uma observação no Post! Grato Fernando!
Meu xará, parabéns pelo post. O único que encontrei da forma mais fácil de ensinar e ainda em português!! Poderia me informar porque na opção de output a planilha, recebendo tanto de XLSX quanto de CSV ocorre um erro?
at java.security.AccessController.doPrivileged(Native Method)
at sun.net.www.protocol.http.HttpURLConnection$10.run(Unknown Source)
at java.net.HttpURLConnection.getResponseCode(Unknown Source)at sun.net.www.protocol.https.HttpsURLConnectionImpl.getResponseCode(Unknown Source)
1. Somente consegui inserir 1900 linhas, a partir de 2000 linhas deu erro.
2. Não consegui instalar ele no servidor, não aparece no marketplace do servidor.
Olá Odivon, o teste que realizei se bem me lembro foi no PDI 7.1 , como é um plugin comunitário pode não funcionar em versões mais recentes, deve ser testado, sobre a Limitação de linha teriamos de ver se na API do Drive tem alguma limitação. O Plugin tem código aberto e isso provavelmente somente será respondido numa analise mais aprofundada em https://github.com/GlobalTechnology/pdi-google-spreadsheet-plugin