Geekly articles weekly

Bagaimana di Microsoft SQL Server untuk mendapatkan data dari Google Analytics menggunakan R

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


Ucapan Terima Kasih:


Karena saya belum pernah menjadi pemasar, saya membutuhkan bantuan seorang spesialis. Ruang uji dan akses ke Google Analytics (GA) diselenggarakan oleh Alexei Seleznev , dan juga memberikan saran praktis.
Dia adalah analis profesional dalam pemasaran. Dan sebagai ucapan terima kasih atas bantuannya, saluran telegram Aleksey disebutkan di sini, di mana ia melakukan aktivitasnya.


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


Untuk terhubung ke Google Analytics (GA) kami akan menggunakan paket googlealnalyticsR .


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


Instal R pada MS SQL Server


ini dilakukan melalui antarmuka standar untuk menginstal komponen MS SQL.





  1. Ini adalah R yang SQL Server akan langsung bekerja dengannya (dipanggil dalam query SQL).
  2. Salinan klien dari R dapat digunakan dengannya dari RStudio tanpa takut merusak sesuatu di server database.

Kami setuju dengan lisensi dan memperhatikan bahwa R biasa tidak akan diinstal, tetapi Microsoft R Open



Singkatnya, apa itu:
Microsoft mengambil R Open untuk memuliakannya dengan paket-paketnya dan juga mendistribusikannya secara gratis.
Dengan demikian, paket versi R ini tersedia untuk diunduh bukan di CRAN tetapi di MRAN .


Tapi itu belum semuanya. Bahkan, ketika menginstal MS SQL, kita tidak mendapatkan MRAN murni, tetapi sesuatu yang lebih - Microsoft ML Server .


Bagi kami, ini berarti bahwa akan ada paket tambahan dalam set pustaka R - RevoScaleR .


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


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


Setelah menginstal komponen, kami mendapatkan antarmuka default untuk berinteraksi dengan R dari Microsoft.



Konsol ini bukan yang paling nyaman yang dapat Anda gunakan, jadi segera unduh dan instal versi RStudio gratis .


Kami mengkonfigurasi SQL server untuk bekerja dengan R


Di SSMS kami menjalankan skrip berikut:


Kami mengizinkan untuk mengeksekusi skrip di server SQL


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

Mulai ulang Server SQL


Pastikan skrip R skrip dieksekusi


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

Temukan lokasi paket R yang digunakan oleh server 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));


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


Kami memulai RStudio.


Ada kemungkinan bahwa beberapa versi R akan diinstal pada komputer dan Anda perlu memastikan bahwa kami bekerja dengan versi SQL server.




Pengaturan akan diterapkan setelah memulai ulang RStudio.


Instal paket googleAnalyticsR


Di tim RStudio


 library()

cari tahu jalan ke pustaka paket versi klien R (dengan mana RStudio bekerja)



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


Instal paket googleAnalyticsR melalui RStudio




Di sini ada nuansa yang tidak terlihat:
Anda tidak bisa hanya menulis sesuatu ke folder sistem MS SQL. Paket akan disimpan dalam direktori sementara sebagai arsip ZIP.



Di Explorer, buka folder sementara dan unzip semua paket.



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


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


Juga, paket yang tidak di-zip harus disalin ke versi klien R (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, seperti yang ditunjukkan oleh praktik, ada beberapa kasus yang berbeda dan lebih baik untuk dapat kembali ke paket yang ada.


Saat menyalin, ganti semua paket yang tersedia.


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


Di RStudio kami memeriksa paket baru



Paket akan diunduh ke folder sementara.
Kami melakukan tindakan yang sama dengan mereka 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 besar skrip Anda sebelumnya akan menyebabkan kesalahan berikut.



Kami membuka akses Internet untuk skrip R dari SQL.


SQL 2017



SQL2019



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


Di RStudio, kami menjalankan kode berikut:
Pada saat yang sama, jendela otentikasi di layanan Google akan terbuka di browser, 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' #    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

Harap dicatat bahwa skrip menggunakan Login dan Kata Sandi - ini tidak terlalu 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, perlu untuk menambahkan akses ke database ke panggilan R.



(Tentu saja, lebih baik menggunakan grup pengguna, sebagai bagian dari demo saya menyederhanakan solusi)


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


 --    exec Ga.Get_session --      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 sudah dapat memuat landingPage berdasarkan 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

sedang memeriksa


 exec [GA].[Get_landingPage_session]

Pada prinsipnya, semuanya sudah siap.


Saya ingin mencatat bahwa menggunakan R via SQL, Anda bisa mendapatkan data dari API apa pun
Misalnya: mendapatkan 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 Anda dapatkan, 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 file python dengan biola yang berisi kata sandi ke database
  • semua kode dalam prosedur dan disimpan ketika database dicadangkan

Database cadangan MS SQL 2017 dengan semua kode tersedia di sini
(untuk pemutaran, Anda perlu menginstal paket, memberikan hak, menentukan nama server Anda)

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

More articles:


All Articles