Erko ve svém základu práci s databázemi nepodporuje – Base R typicky čte vstupní data jako textové soubory s oddělovačem. Připojení k databázím ovšem možné je, a to formou rozšiřujících balíčků (packagí).
Motivace k připojení k databázi z erka bývá různá:
- chceme analyzovat data, která již v databázi existují
- chceme pomocí databáze obejít omezení erka (například pracovat s daty většími než paměť)
- potřebujeme trvalé a sdílené úložiště dat napříč organizací (“jednu pravdu”)
Všechny uvedené důvody jsou dobré. S tím, že ten první bývá nejčastější.
Pro připojení k databázím jsou typicky potřeba dvě knihovny:
- balíček
DBI
, který definuje virtuální třídy pro komunikaci mezi R a relační databází - konkrétní balíček, který implementuje virtuální třídy v kontextu určitého RDBMS
Volba implementačního balíčku bude záviset na databázi, ke které se potřebujeme připojit. Osobně doporučuji DBI doplnit obecným balíčkem odbc
, který ale vyžaduje na straně klienta nainstalované ODBC drivery; postup jejich instalace bude záviset na vašem operačním systému (Windows, iOS či Linux).
Připojení k databázi z erka má tři fáze:
- načtení knihoven a aktivace připojení (zde budeme potřebovat cestu, jméno a heslo)
- využití připojení pro vlastní práci (toto bude těžiště naší aktivity)
- uzavření připojení, a uvolnění zdrojů klienta i serveru
Prvním krokem je načtení knihoven – DBI
a odbc
– a aktivace připojení. Připojení k databázi aktivuji vyvoláním funkce DBI::dbConnect()
a uložením výsledku; zde do objektu con
(jako connection).
# načtení knihoven
library(DBI)
library(odbc)
# aktivace připojení (connection)
con <- dbConnect(odbc(),
driver = "{PostgreSQL Unicode}", # vyžaduje instalaci na úrovni OS!
dbname = "R4SU",
Uid = "R4SU", # uživatel R4SU má pouze selekt práva ...
Pwd = "R4SU", # ... a tak jeho heslo nemusí být 100% utajené :)
server = "db.jla-data.net", # cesta k databázi na sítích internetu
port = 5432)
Když máme připojení aktivní (což snadno poznáme v RStudiu na záložce Connections / zkratka Ctrl + F5) tak s ním můžeme pracovat. Konkrétní připojení je povinným parametrem každého dalšího volání.
Typické příkazy pro práci s databázemi jsou tři:
- poslat z erka do databáze dotaz, a vyžádat si jeho výsledek zpět:
erkovy_data_frame <- DBI::dbGetQuery(con, sql_code)
; typické pro DQL ("select"
) dotazy, jejichž výsledek chci využít v erku pro další práci - poslat z erka do databáze příkaz, a nevyžádat si jeho výsledek zpět:
DBI::dbSendQuery(con, sql_code)
; typické pro DML příkazy, které chci “pouze” vykonat v databázi. Například kultovnídbSendQuery(con, "drop table students;")
- poslat z erka do databáze datový objekt k uložení do tabulky na serveru:
DBI::dbWriteTable(con, database_object, erkovy_data_frame)
; tento příkaz využije metodu bulk insert, pokud jí databázový backend podporuje
Nejlépe si použití ukážeme na příkladu. Použijeme databázové připojení con
z předchozího kroku, a do lokálního erkového objektu potraviny_sql
uložíme výsledek SQL dotazu na tabulku potraviny na serveru.
Poté si prohlédneme strukturu objektu potraviny_sql
- a vidíme, že je to úplně klasický data.frame, který můžeme použít všude tam, kde se data.frames používají.
# vlastní akce - naplnění erkového objektu výstupem SQL dotazu
potraviny_sql <- dbGetQuery(con, "select *
from potraviny
where obdobiod >= date '2018-12-01'")
# co jsme získali?
str(potraviny_sql)
## 'data.frame': 405 obs. of 11 variables:
## $ idhod :integer64 801136762 801136747 801136792 801136807 801136777 801137002 801137017 801136642 ...
## $ hodnota : num 36.2 11.1 24.7 45.3 47.2 ...
## $ stapro_kod : int 6137 6137 6137 6137 6137 6137 6137 6137 6137 6137 ...
## $ reprcen_cis: int 503 503 503 503 503 503 503 503 503 503 ...
## $ reprcen_kod: chr "0111101" "0111201" "0111301" "0111303" ...
## $ obdobiod : Date, format: "2018-12-10" "2018-12-10" ...
## $ obdobido : Date, format: "2018-12-16" "2018-12-16" ...
## $ uzemi_cis : int 97 97 97 97 97 97 97 97 97 97 ...
## $ uzemi_kod : int 19 19 19 19 19 19 19 19 19 19 ...
## $ uzemi_txt : chr "Česká republika" "Česká republika" "Česká republika" "Česká republika" ...
## $ reprcen_txt: chr "Rýže loupaná dlouhozrnná [1 kg]" "Pšeničná mouka hladká [1 kg]" "Chléb konzumní kmínový [1 kg]" "Pečivo pšeničné bílé [1 kg]" ...
Na závěr, když jsme práci s databázovým připojením con
dokončili, je vhodné jeho platnost ukončit pomocí DBI::dbDisconnect()
. Tak uvolníme zdroje na straně klienta i serveru.
# uklidit po sobě je slušnost...
dbDisconnect(con)
Pro zájemce o tematiku doporučuji tyto internetové zdroje:
- DBI Homepage: stránky projektu DBI s (anglickou) dokumentací
- Databases using R: stránky (rovněž anglicky) k problematice databází v RStudiu od RStudia