Neste artigo, quero mostrar em detalhes como você pode usar o R no Microsoft SQL Server para implementar a recuperação de dados do Google Analytics (e geralmente de qualquer API).
Agradecimentos:
Como nunca fui um profissional de marketing, precisava da ajuda de um especialista. A sala de teste e o acesso ao Google Analytics (GA) foram organizados por Alexei Seleznev e também deram conselhos práticos.
Ele é analista profissional de marketing. E, como gratidão pela ajuda, o canal de telegrama de Aleksey é mencionado aqui, onde ele conduz sua atividade.
A tarefa - temos um servidor MS SQL e queremos receber dados em DWH pela API
Para se conectar ao Google Analytics (GA), usaremos o pacote googleAnalyticsR .
Este pacote é selecionado, por exemplo, devido à sua popularidade. Você pode usar outro pacote, por exemplo: RGoogleAnalytic .
As abordagens para resolver o problema serão as mesmas.
Instale o R no MS SQL Server
isso é feito através da interface padrão para instalar componentes do MS SQL.
- Este é o R com o qual o SQL Server trabalhará diretamente (chamado nas consultas SQL).
- Uma cópia do cliente do R pode trabalhar com ele no RStudio sem medo de quebrar algo no servidor de banco de dados.
Concordamos com a licença e prestamos atenção em que R comum não será instalado, mas o Microsoft R Open
Em poucas palavras, o que é:
A Microsoft leva o R Open o enobrece com seus pacotes e também o distribui de graça.
Portanto, os pacotes desta versão do R estão disponíveis para download não no CRAN, mas no MRAN .
Mas isso não é tudo. De fato, ao instalar o MS SQL, não temos um MRAN puro, mas algo mais - o Microsoft ML Server .
Para nós, isso significa que haverá pacotes adicionais no conjunto de bibliotecas R - RevoScaleR .
O RevoScaleR foi projetado para processar big data e criar modelos de aprendizado de máquina em conjuntos de dados significativos.
Essas informações devem ser lembradas, pois existe uma alta probabilidade de perguntas relacionadas a diferentes versões dos pacotes R.
Após a instalação dos componentes, obtemos a interface padrão para interagir com o R da Microsoft.
Como esse console não é o mais conveniente que você pode usar, faça o download e instale imediatamente a versão gratuita do RStudio .
Configuramos o SQL Server para trabalhar com R
No SSMS, executamos os seguintes scripts:
Permitimos executar scripts no servidor SQL
sp_configure 'external scripts enabled', 1; RECONFIGURE WITH OVERRIDE;
Reinicie o SQL do servidor
Verifique se os scripts R scripts são executados
EXECUTE sp_execute_external_script @language =N'R', @script=N'print(version)';
Encontre o local dos pacotes R que são usados pelo servidor SQL
declare @Rscript nvarchar(max) set @Rscript = N' InstaledLibrary <- library() InstaledLibrary <- as.data.frame(InstaledLibrary$results ) OutputDataSet <- InstaledLibrary ' EXECUTE sp_execute_external_script @language = N'R' , @script = @Rscript WITH RESULT SETS (([Package] varchar(255) NOT NULL, [LibPath] varchar(255) NOT NULL, [Title] varchar(255) NOT NULL));
No meu caso, o caminho para os pacotes R MS SQL:
C: / Arquivos de Programas / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library
Começamos o RStudio.
É possível que várias versões do R sejam instaladas no computador e você precisa ter certeza de que estamos trabalhando com uma versão do SQL Server.
As configurações serão aplicadas após reiniciar o RStudio.
Instale o pacote googleAnalyticsR
Na equipe do RStudio
library()
descubra o caminho para a biblioteca de pacotes da versão cliente do R (com a qual o RStudio funciona)
No meu caso, desta maneira:
C: / Arquivos de Programas / Microsoft SQL Server / 140 / R_SERVER / library
Instale o pacote googleAnalyticsR através do RStudio
Aqui há uma nuance não óbvia:
Você não pode simplesmente escrever algo nas pastas do sistema MS SQL. Os pacotes serão salvos em um diretório temporário como arquivos ZIP.
No Explorer, vá para a pasta temporária e descompacte todos os pacotes.
Pacotes descompactados devem ser copiados para o diretório de biblioteca dos Serviços R (com o qual o servidor MS SQL trabalha).
No meu exemplo, esta é uma pasta
C: / Arquivos de Programas / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library
Além disso, os pacotes descompactados devem ser copiados para a versão cliente do R (com a qual o RStudio trabalha)
No meu exemplo, esta é uma pasta
C: / Arquivos de Programas / Microsoft SQL Server / 140 / R_SERVER / library
(aprendemos esses caminhos com scripts executados anteriormente)
Antes de copiar para a pasta R Services, é melhor salvar uma cópia da pasta da biblioteca, como mostra a prática, há casos diferentes e é melhor poder retornar aos pacotes existentes.
Ao copiar, substitua todos os pacotes disponíveis.
Para consolidar a habilidade adquirida, repita o exercício.
Somente agora não instalamos pacotes, mas atualizamos todos os disponíveis.
(não é necessário conectar-se ao GA, mas é melhor ter as versões mais recentes de todos os pacotes)
No RStudio, verificamos novos pacotes
Os pacotes serão baixados para uma pasta temporária.
Realizamos as mesmas ações com eles que ao instalar novos pacotes.
Verificando o acesso à Internet do MS SQL
declare @Rscript nvarchar(max) set @Rscript = N' library(httr) HEAD("https://www.yandex.ru", verbose()) ' EXECUTE sp_execute_external_script @language = N'R' , @script = @Rscript
Como o SQL Server não tem acesso à Internet por padrão, provavelmente o script anterior causará o seguinte erro.
Abrimos o acesso à Internet para scripts R a partir do SQL.
SQL 2017
SQL2019
No SSMS
Obtenha o token do Google Analytics
No RStudio, executamos o seguinte código:
Ao mesmo tempo, a janela de autenticação nos serviços do Google será aberta no navegador. Você precisará fazer login e dar permissão para acessar o Google Analytics.
No SSMS, verifique se o token do Google é recebido e registrado no banco de dados
Select * from [GA].[token]
Verifique a conexão com o GA via RStudio
Se tudo der certo, adicione o script R ao SQL e execute a consulta.
drop table if exists
Observe que o script usa Login e Senha - isso não é muito bom.
Portanto, alteramos a cadeia de conexão para autenticação do Windows.
conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Trusted_Connection=true''
Após alterar o método de autenticação, será necessário adicionar o acesso ao banco de dados na R. chamada.
(É claro que é melhor usar grupos de usuários, como parte da demonstração, simplifiquei a solução)
Fazemos a consulta SQL como um procedimento
Create procedure Ga.Get_session @Date_start date ='2019-01-01', @Date_End date ='2019-08-01' as drop table if exists
Verifique o funcionamento do procedimento
O script R não é complicado, mas sempre pode ser copiado para o R Studio. Modifique e salve no procedimento SQL.
Por exemplo, mudei apenas o parâmetro dimensiones e já posso carregar a landingPage por data.
Create procedure [GA].[Get_landingPage_session] @Date_start date ='2019-01-01', @Date_End date ='2019-08-01' as drop table if exists
estão verificando
exec [GA].[Get_landingPage_session]
Em princípio, tudo está pronto.
Gostaria de observar que, usando R via SQL, você pode obter dados de qualquer API
Por exemplo: obtendo a taxa de câmbio
ou obtendo dados da primeira API que você obtém, em alguns farms na Austrália ...
Total:
- senhas de conexão não são armazenadas em nenhum lugar
- direitos são distribuídos centralmente através de contas do Active Directory
- nenhum arquivo de configuração adicional
- não há arquivos python com violinos contendo senhas no banco de dados
- todo o código está em procedimentos e é salvo quando o backup do banco de dados é feito
O banco de dados de backup MS SQL 2017 com todo o código está disponível aqui
(para reprodução, você precisa instalar pacotes, conceder direitos, especificar o nome do seu servidor)