Geekly articles weekly

Cómo recibir datos de Google Analytics usando R en Microsoft SQL Server

En este artículo, quiero mostrar en detalle cómo puede usar R en Microsoft SQL Server para obtener datos de Google Analytics (y generalmente de cualquier API).


La tarea: tenemos un servidor MS SQL y queremos recibir datos en DWH por API


Utilizaremos el paquete googleAnalyticsR para conectarnos a Google Analytics (GA).


Este paquete se elige como ejemplo debido a su popularidad. Puede usar otro paquete, por ejemplo: RGoogleAnalytic .
Los enfoques para la resolución de problemas serán los mismos.


Instalar R en MS SQL Server


Esto se realiza a través de la interfaz estándar para instalar componentes MS SQL.





  1. Este es R con el que SQL Server interactuará directamente (llamado en consultas SQL).
  2. Puede trabajar con la copia del cliente R de R Studio sin temor a romper algo en el servidor de la base de datos.

Acepte el acuerdo de licencia y preste atención a que no se instalará R ordinario sino Microsoft R Open



Brevemente, qué es:
Microsoft toma R Open, lo mejora con sus paquetes y lo distribuye de forma gratuita.
En consecuencia, los paquetes de esta versión R están disponibles para descargar no en CRAN , sino en MRAN .


Hay más por venir. De hecho, al instalar MS SQL, no obtenemos un MRAN limpio, sino algo más: Microsoft ML Server .


Esto significa para nosotros que habrá paquetes adicionales en el conjunto de bibliotecas R: RevoScaleR .


RevoScaleR está diseñado para procesar grandes datos y construir modelos de aprendizaje automático en grandes conjuntos de datos.


Esta información debe tenerse en cuenta ya que existe una alta probabilidad de preguntas relacionadas con diferentes versiones de R.


Después de instalar los componentes, obtenemos la interacción de Microsoft R.



Esta consola no es muy conveniente de usar, así que descargue e instale inmediatamente la versión gratuita RStudio .


Configurar el servidor SQL para interactuar con R


Ejecute los siguientes scripts en SSMS:


Permitir que los scripts se ejecuten en el servidor SQL


sp_configure 'external scripts enabled', 1; RECONFIGURE WITH OVERRIDE;

Servidor sql reiniciar


Asegúrese de que los scripts R se ejecuten


 EXECUTE sp_execute_external_script @language =N'R', @script=N'print(version)';

Encuentre la ubicación de los paquetes R que utiliza el 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));


En mi caso, la ruta a los paquetes R MS SQL:
C: / Archivos de programa / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library


Ejecute RStudio.


Existe una buena posibilidad de que haya varias versiones R instaladas en la computadora, por lo que debe asegurarse de que estamos trabajando con la versión del servidor SQL.




La configuración se aplicará después de reiniciar RStudio.


Instale el paquete googleAnalyticsR


Para RStudio usando el comando


 library()

Descubra la ruta a la biblioteca de paquetes de la versión del cliente R (con la que funciona RStudio)


En mi caso, este camino:
C: / Archivos de programa / Microsoft SQL Server / 140 / R_SERVER / library


Instale el paquete googleAnalyticsR a través de RStudio




Aquí hay algunos matices sombreados:
No puede simplemente agregar lo que desee a las carpetas del sistema MS SQL. Los paquetes se guardarán en un directorio temporal como archivos ZIP.



Vaya a la carpeta temporal y descomprima todos los paquetes en Explorer.



Los paquetes descomprimidos deben copiarse en el directorio de la biblioteca de R Services (con el que trabaja el servidor MS SQL).


En mi ejemplo esta es la carpeta
C: / Archivos de programa / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library

Los paquetes descomprimidos también deben copiarse en la versión del cliente R (con la que RStudio funciona)


En mi ejemplo esta es la carpeta
C: / Archivos de programa / Microsoft SQL Server / 140 / R_SERVER / library

(aprendimos estos caminos de scripts ejecutados previamente)


Antes de copiar a la carpeta R Services, es mejor guardar una copia de la carpeta de la biblioteca. La experiencia ha demostrado que existen diferentes situaciones y es mejor poder volver a los paquetes existentes.


Al copiar, reemplace todos los paquetes existentes.


Para consolidar la habilidad, repita el ejercicio.
Solo que ahora no instalamos paquetes, sino que actualizamos todos los existentes.
(esto no es necesario para conectarse a GA, pero es mejor tener las últimas versiones de todos los paquetes)


Verifique si hay nuevos paquetes en RStudio



Los paquetes se descargarán a una carpeta temporal.
Realice las mismas acciones que cuando instala nuevos paquetes.


Comprobación del acceso a Internet 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

Dado que SQL Server no tiene acceso a Internet de forma predeterminada, es probable que la secuencia de comandos anterior provoque el siguiente error.



Proporcione acceso a Internet a los scripts R desde SQL.


SQL 2017



SQL 2019



En ssms


 --      create database Demo go use Demo go --  ,       Google Analytics create schema GA go --        GA drop table if exists [GA].[token] create table [GA].[token]( [id] varchar(200) not null, [value] varbinary(max) constraint unique_id unique (id))

Obtenga el token de Google Analytics


Ejecute el siguiente código en RStudio:
Esto abrirá la ventana de autenticación de servicios de Google en su navegador. Deberá iniciar sesión y dar permiso para acceder a Google Analytics.


 #       Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #   ga_auth() PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- readBin(PathTokenFile, "raw", file.info(PathTokenFile)$size) #     conStr <- 'Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Uid=Sa;Pwd=SaSql123' ds <- RxOdbcData(table="ga.token", connectionString=conStr) #     rxWriteObject(ds, "ga_TokenFile", TokenFile)

En SSMS, asegúrese de que el token de Google se reciba y se registre en la base de datos.


 Select * from [GA].[token]

Compruebe la conexión a GA a través de RStudio


 #    RStudio Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- 'Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Uid=Sa;Pwd=SaSql123' #      ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="2019-01-01", end="2019-08-01", metrics = "sessions", dimensions = "date") OutputDataSet

Si todo salió bien, agregue el script R a SQL y ejecute la consulta.


 drop table if exists #GA_session create table #GA_session ( [date] date, [sessions] int ) declare @Rscript nvarchar(max) set @Rscript = N' Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Uid=Sa;Pwd=SaSql123'' #      ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="2019-01-01", end="2019-08-01", metrics = "sessions", dimensions = "date") OutputDataSet$date <- as.character(OutputDataSet$date) ' -- print @Rscript insert into #GA_session ([date],[sessions]) EXECUTE sp_execute_external_script @language = N'R', @script = @Rscript Select * from #GA_session order by [date] asc

Tenga en cuenta que el script usa un Nombre de usuario y Contraseña, lo cual es algo bueno.
Por lo tanto, cambiamos la cadena de conexión a la autenticación de Windows.


 conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Trusted_Connection=true'' #   <<<===     –

Después de cambiar el método de autenticación, deberá agregar derechos de acceso a la base de datos al servicio que llama a R.



(Por supuesto, es mejor trabajar con grupos de usuarios. Simplifiqué la solución como parte de la demostración)


Ejecutamos la consulta SQL como un procedimiento.


 Create procedure Ga.Get_session @Date_start date ='2019-01-01', @Date_End date ='2019-08-01' as drop table if exists #GA_session create table #GA_session ( [date] date, [sessions] int ) declare @Rscript nvarchar(max) set @Rscript =CONCAT( N' #    RStudio Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Trusted_Connection=true'' #   <<<===     -    ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="' , @Date_start ,N'", end="' , @Date_End ,N'", metrics = "sessions", dimensions = "date") OutputDataSet$date <- as.character(OutputDataSet$date) ' ) -- print @Rscript insert into #GA_session ([date],[sessions]) EXECUTE sp_execute_external_script @language = N'R', @script = @Rscript Select * from #GA_session order by [date] asc

Verifique la operación del procedimiento


 -- Default options exec Ga.Get_session -- Get sessions for a given period exec Ga.Get_session @Date_start ='2019-08-01', @Date_End ='2019-09-01'

El script R no es complicado, siempre se puede copiar a R Studio. Modificar y guardar en el procedimiento SQL.
Por ejemplo, solo cambié el parámetro de dimensiones y ahora puedo cargar la página de destino por fechas.


 Create procedure [GA].[Get_landingPage_session] @Date_start date ='2019-01-01', @Date_End date ='2019-08-01' as drop table if exists #GA_session create table #GA_session ( [date] date, landingPagePath nvarchar(max), [sessions] int ) declare @Rscript nvarchar(max) set @Rscript =CONCAT( N' #    RStudio Sys.setenv(TZ="Europe/Berlin") library(googleAnalyticsR) #     conStr <- ''Driver={SQL Server};Server=EC2AMAZ-68OQ7JV;Database=Demo;Trusted_Connection=true'' #   <<<===     -    ds <- RxOdbcData(table="ga.token", connectionString=conStr) PathTokenFile <- paste ( getwd(),"/", ".httr-oauth" , sep="") TokenFile <- rxReadObject(ds, "ga_TokenFile") write.filename = file(PathTokenFile, "wb") writeBin(TokenFile, write.filename) close(write.filename) Sys.setenv("GA_AUTH_FILE" = PathTokenFile) #   ga_auth() #  ga_id account_list <- ga_account_list() ga_id <- account_list$viewId #    OutputDataSet <-google_analytics(ga_id, start="' , @Date_start ,N'", end="' , @Date_End ,N'", metrics = "sessions", dimensions = c("date" ,"landingPagePath")) OutputDataSet$date <- as.character(OutputDataSet$date) ' ) -- print @Rscript insert into #GA_session ([date],landingPagePath,[sessions]) EXECUTE sp_execute_external_script @language = N'R', @script = @Rscript Select * from #GA_session order by [date] asc

comprobando


 exec [GA].[Get_landingPage_session]

Básicamente, eso es todo.


Me gustaría señalar que usando R a través de SQL, puede obtener datos de cualquier API.
Por ejemplo: recibir tasas de cambio.


 -- https://www.cbr-xml-daily.ru Declare @script nvarchar(max) set @script = N' encoding = "utf-8" Sys.setlocale("LC_CTYPE", "russian") Sys.setenv(TZ="Europe/Berlin") library(httr) url <- "https://www.cbr-xml-daily.ru/daily_json.js" resp <- GET(url) library(jsonlite) Response <- fromJSON(content(resp, as = "text")) OutputDataSet <- data.frame(matrix(unlist(Response$Valute$USD), nrow=1, byrow=T),stringsAsFactors=FALSE) OutputDataSet <- rbind(OutputDataSet,data.frame(matrix(unlist(Response$Valute$EUR), nrow=1, byrow=T),stringsAsFactors=FALSE)) ' EXEC sp_execute_external_script @language = N'R' , @script = @script with result SETS UNDEFINED

u obtener datos de la primera API disponible, algunas granjas en Australia ...


 -- https://dev.socrata.com/ Declare @script nvarchar(max) set @script = N' library(httr) url <- "https://data.ct.gov/resource/y6p2-px98.json?category=Fruit&item=Peaches" resp <- GET(url) library(jsonlite) Response <- fromJSON(content(resp, as = "text")) OutputDataSet <- as.data.frame(Response) OutputDataSet <- OutputDataSet [, c("category" , "item" , "farmer_id" , "zipcode" , "business" , "l" , "location_1_location", "location_1_city" , "location_1_state" , "farm_name", "phone1" , "website", "suite")] ' EXEC sp_execute_external_script @language = N'R' , @script = @script with result SETS UNDEFINED

En total:


● las contraseñas de conexión no se almacenan en ningún lugar
● los derechos se distribuyen centralmente a través de cuentas de directorio activo
● no hay archivos de configuración adicionales
● sin violines de Python que contengan contraseñas para la base de datos
● todo el código se almacena en los procedimientos y se guarda cuando se realiza una copia de seguridad de la base de datos


La copia de seguridad de la base de datos MS SQL 2017 con código completo está disponible aquí
(para la reproducción, debe instalar paquetes, distribuir los derechos, especificar el nombre de su servidor)

Source: https://habr.com/ru/post/466589/

More articles:


All Articles