2. comp
  3. Взаимодействие с СУБД: пакеты DBI и sqldf

Взаимодействие с СУБД: пакеты DBI и sqldf

Рассмотрим, как с помощью R:

  1. создать в базе данных (БД) таблицу на основе файла с данными (.csv);
  2. загрузить данные из таблицы БД в R.

Для работы с базами данных в R широко используются пакеты DBI и sqldf.

Способ 1. DBI + RSQLite

Пакет DBI обеспечивает единый интерфейс ко множеству СУБД, в частности, к SQLite, MySQL и PostgreSQL.

Для работы с конкретной СУБД нужно установить её драйвер, реализованный в виде отдельного пакета. Для SQLite это пакет RSQLite, для PostgreSQL -- RPostgreSQL и т. п. Мы покажем принципы работы на примере взаимодействия с SQLite.

#
# Способ 1: DBI+RSQLite
#

library(DBI)

# Загружаем общий интерфейс и соединяемся с базой, используя её драйвер.
# Если базы не существует, то она будет создана.

db <- dbConnect(RSQLite::SQLite(), dbname="Test.sqlite")

# Скачиваем файл из репозитория и сохраняем в таблицу (iris)

fname <- "http://archive.ics.uci.edu/ml/machine-learning-databases/
          iris/iris.data"
iris <- read.table(file=fname, sep=",", header=FALSE)

# Запишем таблицу в БД.

dbWriteTable(conn = db, name = "Iris", value = iris,
             row.names = FALSE)

# Просмотрим список существующих в БД таблиц.

dbListTables(db)

# Извлечём содержимое таблицы в data.frame.

df <- dbGetQuery(db, "SELECT * FROM iris")

# Теперь с ней можно работать в R.

# Закрываем соединение.

dbDisconnect(db)

Способ 2. sqldf

Пакет sqldf импортирует DBI, но предлагает несколько иной способ работы с базами данных. Кроме того, он позволяет манипулировать с таблицами R (data.frame) при помощи языка SQL.

#
# Способ 2. sqldf поверх DBI + RRSQLite
#

library(sqldf) # загружает DBI и RSQLite

# Создаём новую БД.

sqldf("attach 'Test1.sqlite' as new")

# Скачиваем файл данных и сохраняем его на диск.

download.file("http://archive.ics.uci.edu/ml/
              machine-learning-databases/iris/iris.data",
              "iris.csv")

# Создаём в базе таблицу и заполняем её данными из файла.

read.csv.sql(file = "iris.csv", 
             header = FALSE,
             sql = "CREATE TABLE Iris AS SELECT * FROM file",
             dbname = "Test1.sqlite"
             )

# Извлекаем содержимое таблицы БД для работы в R.

df1 <- sqldf("SELECT * FROM Iris", dbname = "Test1.sqlite")

Проверим идентичность созданных таблиц:

identical(df,df1)

##[1] TRUE

Создание функций PostgreSQL на языке R

Итак, пакет sqldf позволяет манипулировать таблицами R, используя язык SQL, но возможен и в некотором роде обратный процесс -- создание функций и триггеров PostgreSQL на языке R. Для этого существует процедурный язык PL/R. По утверждению разработчиков, он позволяет использовать большинство возможностей R, а также поддерживает загрузку пакетов-расширений R.

При создании функций PL/R используется обычный синтаксис R, за исключением того, что тело функции не окружается фигурными скобками (далее используются примеры из руководства){rel="nofollow"}.

Вместо кода на R:

myfunc <- function(arguments) { function body }

для функции в PL/R нужно записать:

CREATE OR REPLACE FUNCTION funcname (argument-types)
RETURNS return-type AS '
    function body
' LANGUAGE 'plr';

При этом само тело функции представляет собой обычный R-скрипт.

При вызове функции значения аргументов передаются в этот скрипт как переменные с именами arg1, arg2, ... argN. Результат вычислений возвращается обычным для R способом. Вот, например, как выглядит функция, возвращающая большее из двух целых чисел:

CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
    if (arg1 > arg2)
       return(arg1)
    else
       return(arg2)
' LANGUAGE 'plr' STRICT;

Комментарии

comments powered by Disqus