Geekly articles weekly

كيفية تلقي البيانات من Google Analytics باستخدام R في Microsoft SQL Server

في هذه المقالة ، أريد أن أوضح بالتفصيل كيف يمكنك استخدام R في Microsoft SQL Server للحصول على بيانات من Google Analytics (وبشكل عام من أي واجهة برمجة تطبيقات).


المهمة - لدينا خادم MS SQL ونريد استلام البيانات في DWH بواسطة API


سنستخدم حزمة googleAnalyticsR للاتصال بـ Google Analytics (GA).


تم اختيار هذه الحزمة كمثال بسبب شعبيتها. يمكنك استخدام حزمة أخرى ، على سبيل المثال: RGoogleAnalytic .
مقاربات حل المشكلات ستكون هي نفسها.


تثبيت R على MS SQL Server


يتم ذلك عبر الواجهة القياسية لتثبيت مكونات MS SQL.





  1. هذا هو R أن SQL Server سيتفاعل معها مباشرة (تسمى في استعلامات SQL).
  2. يمكنك العمل مع نسخة عميل R من R Studio دون خوف من كسر شيء ما على خادم قاعدة البيانات.

اقبل اتفاقية الترخيص وانتبه أنه لن يتم تثبيت R العادية ولكن Microsoft R Open



باختصار ، ما هو:
تأخذ Microsoft R Open ، وتقوم بتحسينه باستخدام حزمه وتوزيعه مجانًا.
وفقًا لذلك ، تتوفر حزم هذا الإصدار R للتنزيل ليس في CRAN ، ولكن في MRAN .


هناك المزيد في المستقبل. في الواقع ، عند تثبيت MS SQL ، لا نحصل على MRAN نظيفة ، ولكن شيء أكثر - Microsoft ML Server .


هذا يعني لنا أنه سيكون هناك حزم إضافية في مجموعة مكتبات R - RevoScaleR .


تم تصميم RevoScaleR لمعالجة البيانات الضخمة وبناء نماذج تعلم الآلة على مجموعات البيانات الكبيرة.


يجب وضع هذه المعلومات في الاعتبار نظرًا لوجود احتمال كبير للأسئلة المتعلقة بإصدارات R المختلفة.


بعد تثبيت المكونات ، نحصل على تفاعل Microsoft R.



وحدة التحكم هذه ليست مريحة للغاية للاستخدام ، لذلك قم على الفور بتنزيل الإصدار المجاني RStudio وتثبيته .


تكوين خادم SQL للتفاعل مع R


تنفيذ البرامج النصية التالية في SSMS:


السماح بتشغيل البرامج النصية على خادم SQL


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

خادم مزود إعادة تشغيل


تأكد من تنفيذ البرامج النصية R


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

ابحث عن موقع حزم R التي يتم استخدامها بواسطة خادم 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));


في حالتي ، فإن الطريق إلى حزم R MS SQL:
C: / ملفات البرنامج / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library


تشغيل RStudio.


هناك فرصة جيدة أن يكون هناك العديد من إصدارات R مثبتة على جهاز الكمبيوتر ، لذلك تحتاج إلى التأكد من أننا نعمل مع إصدار خادم SQL.




سيتم تطبيق الإعدادات بعد إعادة تشغيل RStudio.


قم بتثبيت حزمة googleAnalyticsR


ل RStudio باستخدام الأمر


 library()

تعرف على المسار إلى مكتبة الحزم الخاصة بإصدار عميل R (الذي يعمل مع RStudio)


في حالتي ، هذا المسار:
C: / ملفات البرنامج / Microsoft SQL Server / 140 / R_SERVER / library


قم بتثبيت حزمة googleAnalyticsR عبر RStudio




هنا بعض الفروق الدقيقة المظللة:
لا يمكنك فقط إضافة أي شيء تريده إلى مجلدات نظام MS SQL. سيتم حفظ الحزم في دليل مؤقت كأرشيفات ZIP.



انتقل إلى المجلد المؤقت وقم بفك ضغط كل الحزم في Explorer.



يجب نسخ الحزم غير المضغوطة إلى دليل مكتبة R Services (الذي يعمل معه خادم MS SQL).


في المثال الخاص بي هذا هو المجلد
C: / ملفات البرنامج / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / library

يجب أيضًا نسخ الحزم غير المضغوطة إلى إصدار عميل R (الذي يعمل RStudio معه)


في المثال الخاص بي هذا هو المجلد
C: / ملفات البرنامج / Microsoft SQL Server / 140 / R_SERVER / library

(لقد تعلمنا هذه المسارات من البرامج النصية التي تم تنفيذها مسبقًا)


قبل النسخ إلى مجلد R Services ، من الأفضل حفظ نسخة من مجلد المكتبة. لقد أثبتت التجربة أن هناك مواقف مختلفة ومن الأفضل أن تكون قادرًا على العودة إلى الحزم الموجودة.


عند النسخ ، استبدل كل الحزم الموجودة.


لتعزيز المهارة ، كرر التمرين.
الآن فقط لا نقوم بتثبيت الحزم ، ولكن نقوم بتحديث كل الحزم الموجودة.
(هذا ليس ضروريًا للاتصال بـ GA ، ولكن من الأفضل الحصول على أحدث الإصدارات من جميع الحزم)


تحقق من وجود حزم جديدة في 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 Server ليس لديه إمكانية الوصول إلى الإنترنت بشكل افتراضي ، فمن المحتمل أن يتسبب البرنامج النصي السابق في حدوث الخطأ التالي.



توفير الوصول إلى الإنترنت إلى البرامج النصية R من SQL.


SQL 2017



SQL 2019



في الرسائل القصيرة


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

الحصول على رمزية تحليلات جوجل


قم بتنفيذ الكود التالي في 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]

تحقق من اتصال GA عبر 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

إذا سارت الأمور على ما يرام ، فأضف R script إلى 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

انتبه إلى أن البرنامج النصي يستخدم اسم المستخدم وكلمة المرور ، وهو أمر جيد.
لذلك ، نقوم بتغيير سلسلة الاتصال إلى مصادقة Windows.


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

بعد تغيير طريقة المصادقة ، ستحتاج إلى إضافة حقوق الوصول إلى قاعدة البيانات إلى الخدمة التي تتصل بـ R.



(بالطبع ، من الأفضل العمل مع مجموعات المستخدمين. قمت بتبسيط الحل كجزء من العرض التوضيحي)


نقوم بتنفيذ استعلام 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'

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 برمز كامل هنا
(للتشغيل ، تحتاج إلى تثبيت الحزم ، وتوزيع الحقوق ، وتحديد اسم خادمك)

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

More articles:


All Articles