Geekly articles weekly

Cara menerima data dari Google Analytics menggunakan R di Microsoft SQL Server

Pada artikel ini saya ingin menunjukkan secara rinci bagaimana Anda dapat menggunakan R di Microsoft SQL Server untuk mendapatkan data dari Google Analytics (dan umumnya dari API apa pun).


Tugas - kami memiliki server MS SQL dan kami ingin menerima data dalam DWH oleh API


Kami akan menggunakan paket googleAnalyticsR untuk terhubung ke Google Analytics (GA).


Paket ini dipilih sebagai contoh karena popularitasnya. Anda dapat menggunakan paket lain, misalnya: RGoogleAnalytic .
Pendekatan untuk pemecahan masalah akan sama.


Instal R pada MS SQL Server


ini dilakukan melalui antarmuka standar untuk menginstal komponen MS SQL.





  1. Ini adalah R yang akan berinteraksi dengan SQL Server secara langsung (disebut dalam query SQL).
  2. Anda dapat bekerja dengan salinan klien R dari R Studio tanpa takut merusak sesuatu di server database.

Terima perjanjian lisensi dan perhatikan bahwa bukan R biasa yang akan diinstal tetapi Microsoft R Open



Secara singkat, apa itu:
Microsoft mengambil R Open, memperbaikinya dengan paket-paketnya dan mendistribusikan secara gratis.
Dengan demikian, paket versi R ini tersedia untuk diunduh bukan dalam CRAN , tetapi dalam MRAN .


Masih ada lagi yang akan datang. Bahkan, ketika menginstal MS SQL, kita tidak mendapatkan MRAN yang bersih, tetapi sesuatu yang lebih - Microsoft ML Server .


Ini berarti bagi kami bahwa akan ada paket tambahan di set pustaka R - RevoScaleR .


RevoScaleR dirancang untuk memproses data besar dan membangun model pembelajaran mesin pada dataset besar.


Informasi ini harus diingat karena ada kemungkinan tinggi pertanyaan terkait dengan versi R.


Setelah menginstal komponen, kami mendapatkan interaksi Microsoft R.



Konsol ini sangat tidak nyaman untuk digunakan, jadi segera unduh dan instal RStudio versi gratis.


Konfigurasikan server SQL untuk berinteraksi dengan R


Jalankan skrip berikut di SSMS:


Izinkan skrip dijalankan di SQL server


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

Server sql restart


Pastikan skrip R dieksekusi


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

Temukan lokasi paket R yang digunakan oleh SQL server


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


Dalam kasus saya, path ke paket R MS SQL:
C: / Program Files / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / pustaka


Jalankan RStudio.


Ada kemungkinan besar bahwa akan ada beberapa versi R yang diinstal pada komputer, jadi Anda perlu memastikan bahwa kami bekerja dengan versi SQL server.




Pengaturan akan diterapkan setelah RStudio restart.


Instal paket googleAnalyticsR


Untuk RStudio menggunakan perintah


 library()

Cari tahu jalur ke pustaka paket versi R client (yang digunakan RStudio)


Dalam kasus saya, jalur ini:
C: / Program Files / Microsoft SQL Server / 140 / R_SERVER / library


Instal paket googleAnalyticsR melalui RStudio




Berikut ini beberapa nuansa teduh:
Anda tidak bisa hanya menambahkan apa pun yang Anda inginkan ke folder sistem MS SQL. Paket akan disimpan dalam direktori sementara sebagai arsip ZIP.



Buka folder sementara dan unzip semua paket di Explorer.



Paket yang tidak di-zip harus disalin ke direktori pustaka Layanan R (yang bekerja dengan MS SQL server).


Dalam contoh saya ini adalah folder
C: / Program Files / Microsoft SQL Server / MSSQL14.MSSQLSERVER / R_SERVICES / pustaka

Paket yang tidak di-zip juga harus disalin ke versi R client (yang bekerja dengan RStudio)


Dalam contoh saya ini adalah folder
C: / Program Files / Microsoft SQL Server / 140 / R_SERVER / library

(kami mempelajari jalur ini dari skrip yang dieksekusi sebelumnya)


Sebelum menyalin ke folder R Services, lebih baik menyimpan salinan folder library. Pengalaman telah membuktikan bahwa ada situasi yang berbeda dan lebih baik untuk dapat kembali ke paket yang ada.


Saat menyalin, ganti semua paket yang ada.


Untuk mengkonsolidasikan keterampilan, ulangi latihan.
Hanya sekarang kami tidak menginstal paket, tetapi memperbarui semua yang ada.
(ini tidak perlu untuk terhubung ke GA, tetapi lebih baik memiliki versi terbaru dari semua paket)


Periksa paket baru di RStudio



Paket akan diunduh ke folder sementara.
Lakukan tindakan yang sama seperti ketika menginstal paket baru.


Memeriksa akses Internet 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

Karena SQL Server tidak memiliki akses Internet secara default, kemungkinan skrip sebelumnya akan menyebabkan kesalahan berikut.



Menyediakan akses Internet ke skrip R dari SQL.


SQL 2017



SQL 2019



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

Dapatkan token Google Analytics


Jalankan kode berikut di RStudio:
Ini akan membuka jendela otentikasi layanan Google di browser Anda. Anda harus masuk dan memberikan izin untuk mengakses 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)

Di SSMS, pastikan token dari Google diterima dan dicatat dalam database


 Select * from [GA].[token]

Periksa koneksi ke GA melalui 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

Jika semuanya berjalan dengan baik, tambahkan skrip R ke SQL dan jalankan kueri.


 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

Perhatikan bahwa skrip menggunakan Nama Pengguna dan Kata Sandi, yang merupakan hal yang baik.
Oleh karena itu, kami mengubah string koneksi ke otentikasi Windows.


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

Setelah mengubah metode otentikasi, Anda perlu menambahkan hak akses basis data ke layanan yang memanggil R.



(Tentu saja, lebih baik bekerja dengan kelompok pengguna. Saya menyederhanakan solusi sebagai bagian dari demonstrasi)


Kami menjalankan query SQL sebagai prosedur.


 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

Periksa operasi prosedur


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

Script R tidak rumit, selalu dapat disalin ke R Studio. Ubah dan simpan dalam prosedur SQL.
Misalnya, saya hanya mengubah parameter dimensi dan sekarang dapat memuat Laman Landas menurut tanggal.


 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

memeriksa


 exec [GA].[Get_landingPage_session]

Pada dasarnya itu saja.


Saya ingin mencatat bahwa menggunakan R via SQL, Anda bisa mendapatkan data dari API apa pun.
Misalnya: menerima nilai tukar.


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

atau mendapatkan data dari API pertama yang tersedia, beberapa peternakan di Australia ...


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

Total:


● kata sandi koneksi tidak disimpan di mana pun
● hak didistribusikan secara terpusat melalui akun direktori aktif
● tidak ada file konfigurasi tambahan
● tidak ada biola Python yang berisi kata sandi ke basis data
● semua kode disimpan dalam prosedur dan disimpan ketika database dicadangkan


Cadangan basis data MS SQL 2017 dengan kode lengkap tersedia di sini
(untuk pemutaran, Anda perlu menginstal paket, mendistribusikan hak, menentukan nama server Anda)

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

More articles:


All Articles