👩🏻‍🎨 ⌨️ 🤴🏼 Microsoft SQL सर्वर में R का उपयोग करके Google Analytics से डेटा कैसे प्राप्त करें 🌴 🚯 ⚜️

इस लेख में मैं विस्तार से बताना चाहता हूं कि आप Google एनालिटिक्स से डेटा प्राप्त करने के लिए Microsoft SQL सर्वर में R का उपयोग कैसे कर सकते हैं (और आमतौर पर किसी भी एपीआई से)।

कार्य - हमारे पास MS SQL सर्वर है और हम API द्वारा DWH में डेटा प्राप्त करना चाहते हैं

हम Google Analytics (GA) से कनेक्ट करने के लिए googleAnalyticsR पैकेज का उपयोग करेंगे।

इस पैकेज को इसकी लोकप्रियता के कारण एक उदाहरण के रूप में चुना गया है। आप दूसरे पैकेज का उपयोग कर सकते हैं, उदाहरण के लिए: RGoogleAnalytic ।
समस्या सुलझाने के दृष्टिकोण समान होंगे।

MS SQL सर्वर पर R स्थापित करें

यह MS SQL घटकों को स्थापित करने के लिए मानक इंटरफ़ेस के माध्यम से किया जाता है।

यह वह आर है जो एसक्यूएल सर्वर से सीधे बातचीत करेगा (जिसे एसक्यूएल क्वेश्चन कहा जाता है)।
आप डेटाबेस सर्वर पर कुछ तोड़ने के डर के बिना आर स्टूडियो से आर क्लाइंट कॉपी के साथ काम कर सकते हैं।

लाइसेंस समझौते को स्वीकार करें और ध्यान दें कि साधारण आर स्थापित नहीं होगा लेकिन माइक्रोसॉफ्ट आर ओपन

संक्षेप में, यह क्या है:
Microsoft आर ओपन लेता है, इसे अपने पैकेजों में सुधार करता है और मुफ्त में वितरित करता है।
तदनुसार, इस आर संस्करण के पैकेज सीआरएएन में नहीं, बल्कि एमआरएएन में डाउनलोड के लिए उपलब्ध हैं।

और भी बहुत कुछ है। वास्तव में, एमएस एसक्यूएल स्थापित करते समय, हमें एक साफ एमआरएएन नहीं मिलता है, लेकिन कुछ और - माइक्रोसॉफ्ट एमएल सर्वर ।

इसका मतलब यह है कि आर पुस्तकालयों के सेट में अतिरिक्त पैकेज होंगे - RevoScaleR ।

RevoScaleR बड़े डेटासेट बनाने और बड़े डेटासेट पर मशीन लर्निंग मॉडल के प्रसंस्करण के लिए डिज़ाइन किया गया है।

इस जानकारी को ध्यान में रखा जाना चाहिए क्योंकि विभिन्न आर संस्करणों से संबंधित प्रश्नों की उच्च संभावना है।

घटकों को स्थापित करने के बाद, हमें Microsoft R इंटरैक्शन मिलता है।

यह कंसोल उपयोग करने के लिए बहुत सुविधाजनक नहीं है, इसलिए तुरंत मुफ्त संस्करण RStudio को डाउनलोड और इंस्टॉल करें।

R के साथ सहभागिता करने के लिए SQL सर्वर कॉन्फ़िगर करें

SSMS में निम्न स्क्रिप्ट निष्पादित करें:

SQL सर्वर पर स्क्रिप्ट चलाने की अनुमति दें

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

सर्वर sql पुनः आरंभ

सुनिश्चित करें कि R स्क्रिप्ट निष्पादित हैं

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

SQL सर्वर द्वारा उपयोग किए जाने वाले R संकुल का स्थान ज्ञात करें

 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));

मेरे मामले में, R MS SQL पैकेज का पथ:
C: / प्रोग्राम फ़ाइलें / Microsoft SQL सर्वर / MSSQL14.MSSQLSERVER / R_SERVICES / पुस्तकालय

RStudio चलाएं।

एक अच्छा मौका है कि कंप्यूटर पर कई आर संस्करण स्थापित किए जाएंगे, इसलिए आपको यह सुनिश्चित करने की आवश्यकता है कि हम SQL सर्वर के संस्करण के साथ काम कर रहे हैं।

RStudio पुनरारंभ के बाद सेटिंग्स लागू की जाएंगी।

GoogleAnalyticsR पैकेज स्थापित करें

RStudio को कमांड का उपयोग करके

 library()

R क्लाइंट वर्जन (जिसके साथ RStudio काम करता है) के पैकेज लाइब्रेरी का रास्ता खोजें

मेरे मामले में, यह रास्ता:
C: / प्रोग्राम फ़ाइलें / Microsoft SQL सर्वर / 140 / R_SERVER / पुस्तकालय

RStudio के माध्यम से googleAnalyticsR पैकेज स्थापित करें

यहाँ कुछ छायांकित बारीकियों है:
आप एमएस एसक्यूएल सिस्टम फोल्डर में कुछ भी नहीं जोड़ सकते। संकुल को अभिलेखागार के रूप में एक अस्थायी निर्देशिका में सहेजा जाएगा।

अस्थायी फ़ोल्डर पर जाएं और एक्सप्लोरर के सभी पैकेजों को अनज़िप करें।

अनज़िप किए गए पैकेजों को आर सर्विसेज लाइब्रेरी डायरेक्टरी (जो MS SQL सर्वर के साथ काम करता है) में कॉपी किया जाना चाहिए।

मेरे उदाहरण में यह फ़ोल्डर है
C: / प्रोग्राम फ़ाइलें / Microsoft SQL सर्वर / MSSQL14.MSSQLSERVER / R_SERVICES / पुस्तकालय

अनजिप किए गए पैकेज को R क्लाइंट वर्जन (जो RStudio के साथ काम करता है) में भी कॉपी किया जाना चाहिए

मेरे उदाहरण में यह फ़ोल्डर है
C: / प्रोग्राम फ़ाइलें / Microsoft SQL सर्वर / 140 / R_SERVER / पुस्तकालय

(हमने पहले निष्पादित स्क्रिप्ट्स से इन रास्तों को सीखा)

आर सेवा फ़ोल्डर में कॉपी करने से पहले, लाइब्रेरी फ़ोल्डर की एक प्रति सहेजना बेहतर होता है। अनुभव ने साबित किया है कि अलग-अलग स्थितियां हैं और मौजूदा पैकेजों में वापस आने में सक्षम होना बेहतर है।

कॉपी करते समय, सभी मौजूदा पैकेजों को बदलें।

कौशल को मजबूत करने के लिए, व्यायाम को दोहराएं।
केवल अब हम पैकेज स्थापित नहीं करते हैं, लेकिन सभी मौजूदा अपडेट करते हैं।
(यह जीए से कनेक्ट करने के लिए आवश्यक नहीं है, लेकिन सभी पैकेजों के नवीनतम संस्करणों के लिए बेहतर है)

RStudio में नए पैकेजों की जाँच करें

पैकेज एक अस्थायी फ़ोल्डर में डाउनलोड किए जाएंगे।
नए पैकेज इंस्टॉल करते समय उसी क्रिया को करें।

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

चूंकि SQL सर्वर में डिफ़ॉल्ट रूप से इंटरनेट का उपयोग नहीं है, इसलिए संभावना है कि पिछली स्क्रिप्ट निम्न त्रुटि का कारण होगी।

एसक्यूएल से आर स्क्रिप्ट के लिए इंटरनेट का उपयोग प्रदान करें।

एसक्यूएल 2017

एसक्यूएल 2019

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))

Google Analytics टोकन प्राप्त करें

RStudio में निम्न कोड निष्पादित करें:
यह आपके ब्राउज़र में Google सेवाओं के प्रमाणीकरण विंडो को खोलेगा। आपको 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)

SSMS में, सुनिश्चित करें कि Google का टोकन डेटाबेस में प्राप्त और रिकॉर्ड किया गया है

 Select * from [GA].[token]

RStudio के माध्यम से GA के लिए कनेक्शन की जाँच करें

 #    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

यदि सब कुछ ठीक हो गया, तो R स्क्रिप्ट को SQL में जोड़ें और क्वेरी को निष्पादित करें।

 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

ध्यान दें कि स्क्रिप्ट उपयोगकर्ता नाम और पासवर्ड का उपयोग करती है, जो एक अच्छी बात है।
इसलिए, हम कनेक्शन स्ट्रिंग को विंडोज प्रमाणीकरण में बदलते हैं।

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

प्रमाणीकरण विधि को बदलने के बाद, आपको सेवा कॉलिंग आर के लिए डेटाबेस एक्सेस अधिकार जोड़ना होगा।

(बेशक, उपयोगकर्ता समूहों के साथ काम करना बेहतर है। मैंने प्रदर्शन के भाग के रूप में समाधान को सरल बनाया)

हम SQL क्वेरी को एक प्रक्रिया के रूप में निष्पादित करते हैं।

 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

प्रक्रिया संचालन की जाँच करें

 -- 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'

आर स्क्रिप्ट जटिल नहीं है, इसे हमेशा आर स्टूडियो में कॉपी किया जा सकता है। SQL प्रक्रिया में संशोधित करें और सहेजें।
उदाहरण के लिए, मैंने केवल आयाम पैरामीटर को बदल दिया है और अब दिनांक द्वारा लैंडिंग पृष्ठ को लोड कर सकता है।

 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

चेकिंग

 exec [GA].[Get_landingPage_session]

असल में, यह बात है।

मैं यह नोट करना चाहूंगा कि R का उपयोग SQL के माध्यम से कर सकते हैं, आप किसी भी API से डेटा प्राप्त कर सकते हैं।
उदाहरण के लिए: विनिमय दर प्राप्त करना।

 -- 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

या पहले उपलब्ध एपीआई, ऑस्ट्रेलिया के कुछ खेतों से डेटा प्राप्त कर रहा है ...

 -- 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

कुल में:

● कनेक्शन पासवर्ड कहीं भी संग्रहीत नहीं हैं
● अधिकार सक्रिय निर्देशिका खातों के माध्यम से केंद्र में वितरित किए जाते हैं
● कोई अतिरिक्त विन्यास फाइल नहीं
डेटाबेस के लिए पासवर्ड युक्त ● कोई पायथन पहेलियों
● सभी कोड प्रक्रियाओं में संग्रहीत होते हैं और डेटाबेस के बैकअप होने पर सहेजे जाते हैं

MS SQL 2017 डेटाबेस बैकअप पूर्ण कोड के साथ यहां उपलब्ध है
(प्लेबैक के लिए, आपको पैकेज स्थापित करने, अधिकार वितरित करने, अपने सर्वर का नाम निर्दिष्ट करने की आवश्यकता है)

Microsoft SQL सर्वर में R का उपयोग करके Google Analytics से डेटा कैसे प्राप्त करें