R-ohjelmointi.org

Tilastotieteellistä ohjelmointia R-kielellä

SQL Server 2016 CTP3 ja SQL Server R Services

Microsoft:in SQL Server 2016:stä on tällä hetkellä jaossa testiversio CTP3. Se tulee sisältämään uutuutena muun muassa R-tuen. Testataanpa, miltä CTP3 näyttää!

Kokeiluversion asentaminen

Mainitun kokeiluversion voi ladata Microsoftin sivuilta. Asennuspaketti on koooltaan melko massiivinen, mutta mukana tulevan wizard:in avulla asennus onnistuu kokonaisuudessaan alle tunnissa.

Asensin tätä testiä varten SQL Server:stä paikallisen kopion omalle tietokoneelleni. Tällaisessa asennuksessa näyttää olevan oletuksena, että käyttäjän autentikointi tapahtuu Windows -autentikointia käyttäen. Tämä on näppärää, koska tällöin tietokantaan kirjautumiseen ei tarvita erillistä käyttäjätunnusta ja salasanaa.

Lisäksi kannattaa asentaa vaikkapa Microsoftin demotietokanta Adventure Works, jotta tietokannassa olisi jotakin millä testata. Adventure Works:n voi niin ikään ladata Microsoftin sivuilta. Asennus onnistuu SQL Server:in Restore a Database Backup -toiminnolla varsin suoraviivaisesti.

Asennusten jälkeen tietokantapalvelimen nimeksi tuli tässä esimerkissä ASPIRE, ja tietokannan nimi palvelimella on AdventureWorks2016CTP3.

Hakujen tekeminen

Management Studio

Tietokannasta voi tehdä hakuja usealla eri tavalla. Eräs perinteinen tapa on käyttää SQL Server Management Studio:ta. Onnistuneen asennuksen jälkeen Management Studio löytyy Windows:in Ohjelmat-valikosta SQL Server -ryhmän alta. Kun ohjelma avautuu, voi yläpalkista valita ”New Query”. Tämän jälkeen voi tehdä hakuja kirjoittamalla SQL-koodia Query -ikkunaan. Tehdyn koodin voi ajaa valitsemalla yläpalkista ”Run” (vihreä nuoli) tai painamalla näppäintä F5. Esimerkiksi kymmenen ensimmäisen rivin haku tietokannan osoitetaulusta tuottaa seuraavan tuloksen:

SQLServerQuery1

R

Haun tuloksen voi tallentaa tiedostoon, jonka voi sitten lukea esimerkiksi R:ään. Jos kuitenkin on tarkoituksena analysoida tietoja R:ssä, voi olla näppärämpää tehdä haku suoraan R:stä käsin. Yhteyden muodostaminen SQL Server:in tietokantaan onnistuu RODBC-paketin funktioilla. Yhteydenottoja varten pitää spesifioida ns. connection string, jossa esiintyvät mm. tietokantapalvelimen nimi (ASPIRE) ja tietokannan nimi, johon kytkeydytään (AdventureWorks2016CTP3). Tarvittaessa samassa yhteydessä voi määritellä myös käyttäjätunnuksen ja salasanan yhteyden muodostamista varten. Paikallisen asennuksen ollessa kyseessä on yhteydenotto helppoa, mutta yleensä työpaikoilla tarvittavat asetukset määrittelee tietohallinto tai muu vastaava toimija.

Yhteyden muodostamisen jälkeen haku tehdään määrittelemällä tarvittava SQL-lause funktion sqlQuery() argumentiksi. Hakujen jälkeen yhteys katkaistaan komennolla odbcClose().

library(RODBC)
con <- odbcDriverConnect('driver={SQL Server};server=ASPIRE;database=AdventureWorks2016CTP3;trusted_connection=true')
res <- sqlQuery(con, 'select * from HumanResources.Department')
odbcClose(con)

Tulos tulee R:ään data frame:na:

res
 
   DepartmentID                       Name                            GroupName ModifiedDate
1             1                Engineering             Research and Development   2008-04-30
2             2                Tool Design             Research and Development   2008-04-30
3             3                      Sales                  Sales and Marketing   2008-04-30
4             4                  Marketing                  Sales and Marketing   2008-04-30
5             5                 Purchasing                 Inventory Management   2008-04-30
6             6   Research and Development             Research and Development   2008-04-30
7             7                 Production                        Manufacturing   2008-04-30
8             8         Production Control                        Manufacturing   2008-04-30
9             9            Human Resources Executive General and Administration   2008-04-30
10           10                    Finance Executive General and Administration   2008-04-30
11           11       Information Services Executive General and Administration   2008-04-30
12           12           Document Control                    Quality Assurance   2008-04-30
13           13          Quality Assurance                    Quality Assurance   2008-04-30
14           14 Facilities and Maintenance Executive General and Administration   2008-04-30
15           15     Shipping and Receiving                 Inventory Management   2008-04-30
16           16                  Executive Executive General and Administration   2008-04-30

R-analyysit tietokannassa

Edellä kuvattu tapa tuoda dataa suoraan R:ään toimii nykyisin hyvin monien tietokantojen kanssa. SQL Server 2016:ssa on kuitenkin erityisominaisuutena mahdollisuus tehdä R:llä laskentaa suoraan tietokannassa (R Service).

Ominaisuuden hyötynä on tietysti se, että raskaampaa laskentaa pystyy tekemään suoraan palvelimen päässä vieläpä siten, ettei dataa tarvitse erikseen irrottaa tietokannasta ennen analyysien tekemistä. Tämäkin ominaisuus löytyy monista muistakin tuotteista (mm. Oracle), mutta tarkastellaanpa, miten se toimii SQL Server:ssä.

Asentaminen

Koko toiminnallisuuden asentamisen kuvaava ohje löytyy Microsoftin sivuilta. SQL Serverin asennuksen yhteydessä tulee ensinnäkin asentaa Advanced Analytics Extensions.

Sen lisäksi tulee asentaa Revolution R Open ja Revolution R Enterprise. Yllä mainittu ohje sisältää myös näiden tuotteiden asennusohjeet. Lataus on loppukäyttäjälle ilmainen (ainakin tällä hetkellä).

Asennusten jälkeen pitää vielä tehdä eräitä asennuksen jälkeisiä toimenpiteitä. Eräs keskeinen vaihe on sallia ulkoisten skriptien ajaminen seuraavalla T-SQL-lausekkeella:

sp_configure 'external scripts enabled', 1;
RECONFIGURE;

Vinkki konfiguraatioskriptin ajamista varten: Windows 10 -käyttöjärjestelmässä power user:lle suunnattu menu löytyy näppäinten Windows + x takaa. Sieltä löytyy viimeisessä vaiheessa tarvittava mm. admin command prompt suoraan.

Lopuksi voi olla hyvä ajatus ladata asennusohjeessa mainittu esimerkkipaketti SQLServer2016CTP3Samples.zip. Paketista löytyvä tiedosto iris_demo.sql sisältää esimerkin yhden R:n mukana tulevan aineiston lataamisesta tietokantaan ja muutamien tiedonlouhinta-algoritmien soveltamisesta kyseiseen aineistoon.

Käyttö

Tarkastellaanpa yllä mainittua iris_demo-skriptiä hieman tarkemmin. Skripti käsittelee R:n käyttöä stored procedure:n kautta. SQL Serverin R:ää voi käyttää myös ”suoraan”, kutsumalla sitä Revolution R:stä käsin. Andrie de Vries:in GitHub-repository antaa muutamia esimerkkejä näistä eri käyttötavoista.

Joka tapauksessa T-SQL -skripti alkaa taulujen tyhjentämisellä ja perustamisella. Nämä komennot siis suoritetaan vaikkapa SQL Server Management Studion query-ikkunassa:

drop table if exists iris_data;
drop table if exists iris_models;
go
-- Setup table for holding data:
create table iris_data (
		id int not null identity primary key
		, "Sepal.Length" float not null, "Sepal.Width" float not null
		, "Petal.Length" float not null, "Petal.Width" float not null
		, "Species" varchar(100) null
);
-- Setup table for holding model(s):
create table iris_models (
	model_name varchar(30) not null default('default model') primary key,
	model varbinary(max) not null
);
go
drop procedure if exists get_iris_dataset;
go

Tämän jälkeen taulu populoidaan R:stä haetulla aineistolla. Tässä R:ää kutsutaan siis luodusta proseduurista, joka puolestaan käyttää käyttää proseduuria ’sp_execute_external_script’. R:n kutsuminen T-SQL:stä siis luo varsinaisen R-koodin ympärille hieman ylimääräistä koodia, jotta tiedon välitys saadaan määriteltyä. Seuraava koodiblokki hakee tarvittavat tiedot R:stä:

create procedure get_iris_dataset
as
begin
	-- Return iris dataset from R to SQL:
	execute   sp_execute_external_script
					@language = N'R'
				  , @script = N'iris_data <- iris;'
				  , @input_data_1 = N''
				  , @output_data_1_name = N'iris_data'
	with result sets (("Sepal.Length" float not null, "Sepal.Width" float not null
				  , "Petal.Length" float not null, "Petal.Width" float not null, "Species" varchar(100)));
end;
go

Kun tiedot on haettu R:stä, niitä käytetään taulun populointiin:

--truncate table iris_data;
-- Populate data from iris dataset in R:
insert into iris_data ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
exec dbo.get_iris_dataset;
select top(10) * from iris_data;
select count(*) from iris_data;
go

Seuraavaksi ajetaan datalle naiivi Bayes -analyysi. Tässä kohdin analyysini karahti ensin virheilmoitukseen, mikä johtuu siitä, ettei SQL Serverin tai Revolution R:n asennuksen mukana tule R-pakettia e1071. Se pitää siis erikseen asentaa. Windows-koneelle oletusarvoilla (vaikuttaa paketin asennuspolkuun) tehdyn asennuksen jälkeen paketin voi asentaa kohdalleen seuraavalla R-koodilla:

install.packages("e1071", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")

Kyseinen R-koodi on ajettava sellaisesta R-sessiosta, joka on käynnistetty järjestelmänvalvojana. Mikä tahansa R-sessio siis kelpaa, kunhan versio on vähintäänkin tuon SQL Serverin käyttämän version 3.2.2:n tasoa.

Paketteja puuttuu itse asiassa muitakin, joten asennetaan kaikki puuttuvat yhdellä kertaa:

install.packages("e1071", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("ggplot2", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("gtable", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("scales", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("Rcpp", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("munsell", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("colorspace", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("plyr", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("labeling", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")
install.packages("digest", lib="C:\\Program Files\\RRO\\RRO-3.2.2-for-RRE-7.5.0\\R-3.2.2\\library")

Kunhan paketit on saatu paikoilleen, voidaan analyysi ajaa seuraavasti:

drop proc if exists generate_iris_model;
go
create procedure generate_iris_model
as
begin
	execute sp_execute_external_script
	  @language = N'R'
	, @script = N'
		library(e1071);
		irismodel <-naiveBayes(iris_data[,1:4], iris_data[,5]);
		trained_model <- data.frame(payload = as.raw(serialize(irismodel, connection=NULL)));
'
	, @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" from iris_data'
	, @input_data_1_name = N'iris_data'
	, @output_data_1_name = N'trained_model'
	with result sets ((model varbinary(max)));
end;
go

Loppuosa skriptistä, joka mm. ennustaa eri havainnoille lajin ja piirtää tuloksista kuvat on seuraavanlainen. Kommentoin koodista pois turhat grant execute -rivit, koska niitä ei paikallisessa asennuksessani tarvittu.

--truncate table iris_models;
-- Generate model based on Naive Bayes algorithm in e1071 package:
insert into iris_models (model)
exec generate_iris_model;
update iris_models set model_name = 'e1071 - Naive Bayes' where model_name = 'default model';
select * from iris_models;
go

drop procedure if exists predict_species;
go
create procedure predict_species (@model varchar(100))
as
begin
	declare @nb_model varbinary(max) = (select model from iris_models where model_name = @model);
	-- Predict species based on the specified model:
	exec sp_execute_external_script 
					@language = N'R'
				  , @script = N'
library("e1071");
irismodel<-unserialize(nb_model)
species<-predict(irismodel, iris_data[,2:5]);
OutputDataSet <- cbind(iris_data[1], species, iris_data[6]);
colnames(OutputDataSet) <- c("id", "Species.Actual", "Species.Expected");
OutputDataSet <- subset(OutputDataSet, Species.Actual != Species.Expected);
'
	, @input_data_1 = N'
	select id, "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species"
	  from iris_data'
	, @input_data_1_name = N'iris_data'
	, @params = N'@nb_model varbinary(max)'
	, @nb_model = @nb_model
	with result sets ( ("id" int, "Species.Actual" varchar(max), "Species.Expected" varchar(max))
			  );
end;
go

exec predict_species 'e1071 - Naive Bayes';
go


drop procedure if exists get_iris_plot1;
go
create procedure get_iris_plot1
as
begin
	-- Demonstrate how to generate plots from R & return to any SQL client:
	execute sp_execute_external_script
	  @language = N'R'
	, @script = N'
library("ggplot2");
image_file = tempfile();
jpeg(filename = image_file, width=600, height = 800);
print(qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
    xlab = "Sepal Length", ylab = "Petal Length",
    main = "Sepal vs. Petal Length in Fisher''s Iris data"));
dev.off();
OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));
'
	, @input_data_1 = N''
	with result sets ((plot varbinary(max)));


end;
go
--grant execute on get_iris_plot1 to dbo;
--go

drop procedure if exists get_iris_plot2;
go
create procedure get_iris_plot2
as
begin
	-- Demonstrate how to generate plots from R & return to any SQL client:
	execute sp_execute_external_script
	  @language = N'R'
	, @script = N'
library("ggplot2");
image_file = tempfile();
jpeg(filename = image_file, width=600, height = 800);
print(qplot(Sepal.Length, Petal.Length, data = iris, color = Species, size = Petal.Width,
    xlab = "Sepal Length", ylab = "Petal Length",
    main = "Sepal vs. Petal Length with Width in Fisher''s Iris data"));
dev.off();
OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));
'
	, @input_data_1 = N''
	with result sets ((plot varbinary(max)));
end;
go
--grant execute on get_iris_plot2 to rdemo;
--go

EXEC get_iris_plot1;
GO
EXEC get_iris_plot2;
GO

Management Studion output -ikkuna täyttyy tuloksista. Keskellä näkyy mm. pseudo-confusion matrix, josta voi päätellä, että ennustaminen on osunut varsin kohdalleen, koska ainoastaan kuusi tapausta on ennustettu väärin.

SQLServerQuery2

Yllä esitelty T-SQL-skripti tuottaa myös kaksi kuvaa ggplot2-kirjastoa käyttäen. Niiden saaminen tosialliseen kuvamuotoon vaatii vielä vähän perehtymistä. Ratkaisu ei ainakaan keskellä yötä vaikuttanut ihan itsestään selvältä. EDIT 2016-03-03: Postaus mssqltips.com-blogissa kuvaa hyvin, miten homma toimii.

Yhteenveto ja kokemuksia

Asennusvaiheiden jälkeen käyttö vaikuttaa kohtuullisen sujuvalta. Vaikka tuotetta ei ole vielä edes virallisesti julkaistu, löytyi verkosta sopivasti etsimällä jo käytännössä kaikkiin kohtaamiini ongelmiin valmis vastaus. R:n käyttö T-SQL:stä tuntuu tosin hieman hämmentävältä, ja käyttömukavuus voisi olla parempi, jos kutsuisi R Serviceä toisesta R-sessiosta, kuten Revolution Analytics:n blogissa on kuvattu.

Täytyy myös antaa Microsoftille siinä mielessä tunnustusta, että etenkin SQL Server:in ja R Service:n asennusvaiheet on saatu yllättävän kivuttomiksi. Asennuksesta analyysituloksiin alle neljässä tunnissa on mielestäni aika nappiin mennyt suoritus!

Samaten ohjeiden löydettävyys on kerrassaan hyvä. Puuttuvien R-pakettien asentaminen tosin heitti hetkeksi sivuraiteille, koska SQL Server ei osannutkaan käyttää niitä henkilökohtaisesta kirjastopolustani, vaikka se näyttää olevankin R Service:n käyttämän R-version .libPaths() -muuttujassa.


Vastaa

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *