Geekly articles weekly

كيف في Microsoft SQL Server للحصول على بيانات من Google Analytics باستخدام R

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


الشكر:


بما أنني لم أكن مسوقًا على الإطلاق ، فقد كنت بحاجة إلى مساعدة أحد المتخصصين. تم تنظيم غرفة الاختبار والوصول إلى Google Analytics (GA) بواسطة Alexei Seleznev ، كما قدم نصائح عملية.
وهو محلل محترف في التسويق. وكامتنان للمساعدة ، تم ذكر قناة برقية أليكسي هنا ، حيث يدير نشاطه.


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


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


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


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


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





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

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



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


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


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


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


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


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



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


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


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


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


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

أعد تشغيل SQL Server


تأكد من أن يتم تنفيذ البرامج النصية 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



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]

تحقق من اتصال 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 إلى 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

يرجى ملاحظة أن البرنامج النصي يستخدم تسجيل الدخول وكلمة المرور - هذا ليس جيدًا جدًا.
لذلك ، نقوم بتغيير سلسلة الاتصال إلى مصادقة 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

تحقق من تشغيل الإجراء


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

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

More articles:


All Articles