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

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

धन्यवाद:

चूंकि मैं कभी बाज़ारिया नहीं रहा, इसलिए मुझे एक विशेषज्ञ की मदद की ज़रूरत थी। Google Analytics (GA) के लिए परीक्षण कक्ष और पहुंच का आयोजन अलेक्सई सेलेज़नेव द्वारा किया गया था, और व्यावहारिक सलाह भी दी।
वह विपणन में एक पेशेवर विश्लेषक है। और मदद के लिए आभार के रूप में, अलेक्सी के टेलीग्राम चैनल का उल्लेख यहां किया गया है, जहां वह अपनी गतिविधि संचालित करता है।

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

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

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

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

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

1. यह वह R है जो SQL Server सीधे काम करेगा (जिसे SQL क्वेरीज़ कहा जाता है)।
2. R का एक क्लाइंट कॉपी डेटाबेस सर्वर पर कुछ तोड़ने के डर के बिना RStudio से इसके साथ काम कर सकता है।

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

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

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

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

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

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

घटकों को स्थापित करने के बाद, हमें माइक्रोसॉफ्ट से आर के साथ बातचीत करने के लिए डिफ़ॉल्ट इंटरफ़ेस मिलता है।

यह कंसोल सबसे सुविधाजनक नहीं है जिसे आप उपयोग कर सकते हैं, इसलिए 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() 

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

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

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

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

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

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

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

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

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

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

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

कॉपी करते समय, सभी उपलब्ध पैकेजों को बदलें।

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

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 सर्वर में डिफ़ॉल्ट रूप से इंटरनेट का उपयोग नहीं है, इसलिए संभवत: आपकी पिछली स्क्रिप्ट निम्न त्रुटि का कारण होगी।

हम SQL से R स्क्रिप्ट के लिए इंटरनेट एक्सेस खोलते हैं।

एसक्यूएल 2017

SQL2019

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

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

 --    exec Ga.Get_session --      exec Ga.Get_session @Date_start ='2019-08-01', @Date_End ='2019-09-01' 

R स्क्रिप्ट जटिल नहीं है, इसे हमेशा R Studio में कॉपी किया जा सकता है। 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 यहां उपलब्ध है
(प्लेबैक के लिए, आपको पैकेज स्थापित करने, अधिकार देने, अपने सर्वर का नाम निर्दिष्ट करने की आवश्यकता है)