Блог

Я пытаюсь запустить r-скрипт через пакетный файл, чтобы я мог запланировать запуск R-скрипта на регулярной основе. Скрипт R извлекает данные из Google Analytics и отправляет их в базу данных SQL Server.

R-скрипт отлично работает в R Studio, но когда я пытаюсь выполнить тот же R-скрипт через пакетный файл, он выдает ошибку —

«Ошибка в body_config(тело, составная часть): перенаправление объекта.uri не найден Вызывает rga.open … modify_config -> setdiff -> as.vector -> выполнение body_config остановлено «

Мой пакетный файл выглядит так: «C:Program FilesRR-3.0.2binRscript.exe » «C:UsersscDocumentsR Источники SocialMedia ExtractBlogStats.r» пауза

Мой Rscript выглядит так:

 library(devtools)

#install_github("rga", "skardhamar")
library(rga)

#install.packages("RODBC")
library(RODBC)

myconn <- odbcDriverConnect('driver={SQL Server};server=localhost;database=SocialMedia;trusted_connection=true')


options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))

#rga.open(instance="ga")


rga.open(instance="ga", where="~/ga.rga")


id <- "XXXXXXXX" # Valid ID in actual script file

end <- Sys.Date() - 1
start <- end - 1

by.practice <- ga$getData(id, start.date = start, end.date= end, 
                        metrics = "ga:sessions,ga:totalEvents",
                        dimensions = "ga:eventAction",
                        sort = "",
                        filters = "",
                        segment = "",
                        start = 1,
                        max = 1000)
  

Шаги: a) Выполняется rga.open (экземпляр = «ga») внутри RStudio

б) Который привел меня к URL-адресу и сгенерировал код

c) Вставил код в консоль RStudio

d) Сохраненный объект ga в локальной файловой системе — сохранить (ga, file=»C:/Users/sc/Documents/R Источники/SocialMedia /ga.rga»)

e) Удален вызов rga.open из функции — extract.blog.stats.by.practice()

 extract.blog.stats.by.practice = function(id = "XXXXXXX") {
library(devtools)
library(rga)
library(RODBC)
myconn <- odbcDriverConnect('driver={SQL Server};server=localhost;database=SocialMediaSink;trusted_connection=true')

options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))

#rga.open(instance="ga")
setwd("C:/Users/sc/Documents/R Sources/SocialMedia")

end <- Sys.Date() - 1
start <- end - 1

by.practice <- NULL

by.practice <- ga$getData(id, start.date = start, end.date= end, 
                        metrics = "ga:sessions,ga:totalEvents",
                        dimensions = "ga:eventAction",
                        sort = "",
                        filters = "",
                        segment = "",
                        start = 1,
                        max = 1000)


# Convert Numeric To Integer
practice <- by.practice[,1]
sessions <- as.integer(by.practice[,2])
totalEvents <- as.integer(by.practice[,3])

# Create a dataframe that mimics the structure of the database table - BlogPracticeStats
practice.stats <- data.frame(practice, end, sessions, totalEvents, stringsAsFactors = FALSE)

# Assign columnnames that match the table's column name in the database
colnames(practice.stats) <- c("Practice", "Date", "Sessions", "TotalEvents")

# Insert rows in the table
sqlSave(myconn, practice.stats, "BlogPracticeStats", safer = FALSE, append = TRUE, rownames = FALSE)
odbcClose(myconn)

 }
  

f) Включена загрузка «ga» из локально сохраненной копии в RunFunction.r

 source("C:/Users/sc/Documents/R Sources/SocialMedia/ExtractBlogPracticeStats.R")
load("C:/Users/sc/Documents/R Sources/SocialMedia/ga.rga")
extract.blog.stats.by.practice()
  

g) Выполненный пакетный файл

 "C:Program FilesRR-3.0.2binRscript.exe" "C:UsersscDocumentsR SourcesSocialMediaRunFunction.r"
pause
  

Я хотел опубликовать все решение —

Шаг 1 — Создание начальных объектов аутентификации

 #Initial Objects
# dev tools is needed because we need to use it's function install_github

# install.packages("devtools")
library(devtools)

# install_github("rga", "skardhamar")
library(rga)

library(RODBC)

config.folder.location <- "C:\Users\sc\Documents\R Sources\SocialMedia\Config"

working.directory <- config.folder.location

rga.open(instance="ga")

save(ga, file="C:/Users/sc/Documents/R Sources/SocialMedia/config/ga.rga")


connection.string <- 'driver={SQL Server};server=localhost;database=SocialMedia;trusted_connection=true'

save(connection.string,  file = paste0(config.folder.location,'\connection.string '))


# Google Analytics ID for ur site
google.analytics.id <- "XXXXXX"

save(google.analytics.id, file="google.analytics.id")
  

Шаг 2 — Запрос Google Analaytics

 # dev tools is needed because we need to use it's function install_github
# install.packages("devtools")
library(devtools)

# install_github("rga", "skardhamar")
library(rga)

# install.packages("RODBC")
library(RODBC)


#*********************************************************************************************************************
# Read arguments and store them in variables -- START
#*********************************************************************************************************************
args <- commandArgs(trailingOnly = TRUE)

# First argument is read as working directory (location where config folder resides)
working.directory <- as.character(args[1])

if (length(args) >= 2){
  # Second argument is used as start date in GA Query
  start <- as.Date(args[2])
}else{
 start <- Sys.Date() - 1
}

if (length(args) == 3){
# Third argument is used as end date in GA Query  
 end <- as.Date(args[3]) 
}else{
 end <- start
}

#*********************************************************************************************************************
# Read arguments and store them in variables -- END
#*********************************************************************************************************************

#*********************************************************************************************************************
# Use arguments values to setup the environment and load initial objects -- START
#*********************************************************************************************************************

# Set working directory to the passed value
setwd(working.directory)

# load Google Analytics ID from Config folder
load("google.analytics.id")

# Load SQL Connection String
load("connection.string")

options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))

# load pre-authentcated Google Analytics object "ga"
load("ga.rga")

# Check if the ga token has expired. If it has then refersh token.
if (ga$isTokenExpired()){
  ga$refreshToken()
}

#*********************************************************************************************************************
# Use arguments values to setup the environment and load initial objects -- END
#*********************************************************************************************************************

# Runs Google Analytics query for the provided id and limits data by date (i.e., start.date = date and end.date = date)
# Wrapped in try/catch to handle scenarios when GA does not return any rows



blog.stats = tryCatch({
 ga$getData(google.analytics.id, 
            start.date = start, 
            end.date = end, 
            metrics = "ga:sessions, ga:users, ga:newUsers, ga:sessionDuration, ga:timeOnPage, ga:pageviews",
            dimensions = "ga:date, ga:pageTitle, ga:medium, ga:hasSocialSourceReferral, ga:source, ga:referralPath",
            sort = "",
            filters = "",
            segment = "",
            start = 1,
            max = 10000)           
 }, warning = function(w) { 
    # print("warning")
    return(NULL)
 }, error = function(e) {
    # print("error")
    return(NULL)
 }, finally = {  
    # print("inside Finally")
}
)

if(length(blog.stats)>0 ){
# Convert Numeric To Integer
  blog.stats[,"sessions"] <- as.integer(blog.stats[,"sessions"])
  blog.stats[,"users"] <- as.integer(blog.stats[,"users"])
  blog.stats[,"newUsers"] <- as.integer(blog.stats[,"newUsers"])
  blog.stats[,"sessionDuration"] <- as.integer(blog.stats[,"sessionDuration"])
  blog.stats[,"timeOnPage"] <- as.integer(blog.stats[,"timeOnPage"])
  blog.stats[,"pageviews"] <- as.integer(blog.stats[,"pageviews"])


# Assign columnnames that match the table's column name in the database
colnames(blog.stats) <- c("Date", "PageTitle", "Medium", "HasSocialSourceReferral", "TrafficSource", "ReferralPath", "Sessions", "Users", "NewUsers", "SessionDurationSeconds", "TimeOnPageSeconds", "PageViews" )

# Open a Connection
myconn <- odbcDriverConnect(connection.string)

# Prepare Delete Query
delete.query <- paste0("DELETE  FROM  GoogleAnalyticsBlogStats WHERE Date >='",start,"' AND Date <='",end,"'")

# Execute Delete Query
sqlQuery(myconn,delete.query,  errors= FALSE)


# Insert rows in the table
sqlSave(myconn, blog.stats, "GoogleAnalyticsBlogStats", safer = FALSE, append = TRUE, rownames = FALSE)

# Close the connection to the database
odbcClose(myconn)
}
  

Шаг 3 — Выполнить Rscript из пакетного файла

Пакетный файл выглядит следующим образом —

 "C:Program FilesRR-3.1.0binRscript.exe" "D:Social Media AnalyticsR ScriptsExtractGoogleAnalyticsBlogStats.r" "D:\Social Media Analytics\R Scripts\Config"