Proč a jak se připojit z erka k relační databázi?

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:

1. načtení knihoven a aktivace připojení (zde budeme potřebovat cestu, jméno a heslo)
2. využití připojení pro vlastní práci (toto bude těžiště naší aktivity)
3. 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 ...
$ hodnota    : num  36.2 11.1 24.7 45.3 47.2 ...
$ stapro_kod : int  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" ...
$ reprcen_txt: chr  "Rýže loupaná dlouhozrnná [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

Poprvé zveřejněno na www.jla-data.net/cze/erko-a-databaze.