R e database relazionali

Tra le possibili sorgenti di dati sfruttabili con comandi R, non potevano mancare i database relazionali. In questa lezione, in particolare, vedremo come interfacciarci con MySQL. Ci collegheremo ad una nostra installazione del DBMS sfruttando un apposito modulo da integrare in R:
RMySQL.

Integrazione di RMySQL

Per prima cosa, scarichiamo il modulo da uno dei tanti repository disponibili on line. Ciò sarà necessario solo la prima volta, e qualora dovessimo ripetere il passaggio non verrebbero comunque apportate modifiche alla piattaforma di lavoro.
Questo il comando da digitare nella console interattiva:

install.packages("RMySQL")

Ciò che è necessario fare sempre, all'inizio della sessione di lavoro, è importare la libreria. Procederemo
così:

library("RMySQL")

A questo punto possiamo iniziare la connessione ai nostri database.

Connessione al DBMS

Per i nostri test, abbiamo in esecuzione un'installazione di MySQL con tutti i parametri di default: indirizzo
127.0.0.1 (localhost), username "root" e password vuota. Il DBMS attualmente non gestisce alcun database.
Con il seguente comando creeremo una connessione al DBMS che conserveremo nell'oggetto my:

my=dbConnect(MySQL(), username='root', password='')

Vogliamo creare un database per lavorare, pertanto inotreremo il comando SQL CREATE DATABASE mediante la
funzione di R dbSendQuery:

dbSendQuery(my, "CREATE DATABASE vendite")
dbSendQuery(my, "USE vendite")

Avremo così creato un database di nome "vendite" e l'avremo impostato come database corrente. È importante notare che dalla interazione successiva avremo già un database che potremo indicare in fase di connessione:

my=dbConnect(MySQL(), username='root', password='', db='vendite')

Al termine della connessione al database, è opportuno disconnettersi correttamente. A tale scopo esiste la funzione dbDisconnect, che riceve in input l'oggetto connessione su cui vogliamo agire:

dbDisconnect(my)

La disconnessione interrompe contestualmente ogni collegamento a risorse del database come, ad esempio, ai resultset
che rappresentano i risultati delle interrogazioni fatte, aspetto che vedremo a breve.

Creazione di tabelle da dataframe

Possiamo interagire come vogliamo con le tabelle di MySQL da R, eseguendo - ad esempio - inserimenti di singoli
record. Nonostante ciò, nel corso delle elaborazioni con R, potrà capitare di trovarci in possesso di un
data.frame che vorremo salvare in una tabella. L'operazione sarà semplicissima e richiederà l'uso di una sola
funzione R. Iniziamo a costruire un data.frame con dati casuali in maniera simile a quello che abbiamo fatto nelle
scorse lezioni. Possiamo fare tutto da console interattiva:

anni <- seq(2010,2016)
rapp <- c("R001", "R002", "R003", "R004")
anno <- sample(anni, size=280, replace=TRUE)
id_agente <- sample(rapp, size=280, replace=TRUE)
importo <- round(runif(280)*600+80, digits=2)
dataset <- data.frame(id_agente, anno, importo)

Al termine dell'introduzione dei comandi avremo un data.frame, l'oggetto dataset munito di tre campi,
in grado di rappresentare un insieme di ordini raccolti da agenti di commercio, per ognuno dei quali sarà indicato
l'ID dell'agente, l'anno di riferimento e l'importo. Eccone uno stralcio:

id_agente anno importo
1        R002 2016  567.90
2        R002 2012  206.31
3        R002 2016  376.10
4        R001 2016  114.74
5        R001 2013  179.68

Potremo salvare l'intero data.frame nel database facendo in modo che esso costituisca una nuova tabella:

dbWriteTable(my, "ordine", dataset, overwrite = TRUE)

Come si vede forniamo:

• la connessione al DBMS;
• il nome della nuova tabella;
• il riferimento all'oggetto data.frame appena creato in R;
• l'ordine di sovrascrittura in caso di tabella preesistente.

Potremo a questo punto constatare in MySQL la presenza di una nuova tabella, con all'interno tutto il nostro
data.frame.

Eseguire query

Per eseguire query, dovremo utilizzare ancora la funzione dbSendQuery fornendo la connessione
attiva al DBMS ed il testo del comando SQL:

res = dbSendQuery(my, "select * from ordine")

L'oggetto res sarà il nostro ResultSet, ossia un puntatore all'insieme di risultati
selezionati mediante la query. Per leggere i risultati potremo fare il fetch dei dati, ma si faccia attenzione
che una volta eseguitolo su una o più righe queste non potranno più essere lette. Per esempio, l'invocazione:

fetch(res, n=10)

restituirà le prime dieci righe del ResultSet, ma ripetendolo il comando restituirà le successive dieci righe, e non più le precedenti.

Se catturiamo il risultato di un fetch in un oggetto R, questo avrà la struttura interna di data.frame e
pertanto il seguente comando avrà il comodo effetto di svuotare il ResultSet ed inserirlo totalmente in un data.frame:

dataframe <- fetch(res)

Qualora inoltrassimo nuove interrogazioni verso i database, ci potrebbe essere risposto che non si può procedere
se si ha attualmente un ResultSet già aperto. A quel punto sarà sufficiente chiudere il risultato dell'ultima interrogazione fatta e
procedere con la nuova. Questo il comando:

dbClearResult(res)

Invio di ulteriori comandi

Per inoltrare ulteriori comandi la procedura non differirà da quanto visto sinora. Si userà sempre la funzione
dbSendQuery fornendo opportune direttive SQL. Si potranno in questo modo eseguire operazioni di INSERT, DELETE e UPDATE
per andare a modificare i valori nella tabella.

In conclusione, questa interazione tra R e database relazionali offre una duplice validità. Da un lato, permette
di utilizzare i database come sorgenti dati per alimentare le nostre elaborazioni in R, dall'altro permette di
sfruttare un database come una valida struttura di persistenza in cui salvare i data.frame prodotti, per poi recuperarli
successivamente.