Поскольку львиная доля бизнес информации храниться в базах данных. На каком бы языке программирования вы не писали, вам придётся производить различные действия с ними.

В этой статье я расскажу о двух интерфейса для работы с базами данных в R. Большая часть примеров демонстрируют работу с Microsoft SQL Server, тем не менее все примеры кода будут работать и с другими базами данных, такими как: MySQL, PostgreSQL, SQLite, ClickHouse, Google BigQuery и др.

Содержание

Требуемое программное обеспечение
Пакет DBI
Пакет RODBC
Заключение

Требуемое программное обеспечение

Для того, что бы повторить все описанные в статье примеры работы с СУБД вам потребуется перечисленное ниже, бесплатное программное обеспечение:

Язык R;
Среда разработки RStudio;
Система Управления Базами Данных, на выбор:
3.1. Microsoft SQL Server
3.2. MySQL
3.3. PostgreSQL

Пакет DBI

Пакет DBI является наиболее популярным и удобным способом взаимодействия с базами данных в R.

DBI предоставляет вам набор функций, с помощью которых вы можете управлять базами данных. Но для подключения к базам данных требуется установка дополнительных пакетов, которые являются драйверами к различным системам управления базами данных (СУБД).

Список основных функций DBI

dbConnect — подключение к базе данных;
dbWriteTable — запись таблицы в базу данных;
dbReadTable — загрузка таблицы из базы данных;
dbGetQuery — загрузка результата выполнения запроса;
dbSendQuery — отправка запроса к базе данных;
dbFetch — извлечение элементов из набора результатов;
dbExecute — выполнение запросов на обновление / удаление / вставку данных в таблицы;
dbGetInfo — запрос информацию о результате запроса или подключении;
dbListFields — запрос списка полей таблицы;
dbListTables — запрос списка таблиц базы данных;
dbExistsTable — проверка наличия таблицы в базе данных;
dbRemoveTable — удаление таблицы из базы данных;
dbDisconnect — разрыв отсоединения с базы данных.

Подключение к базам данных

Для взаимодействия с базами данных предварительно к ним необходимо подключиться. В зависимости от СУБД с которой вы планируете работать вам потребуется дополнительный пакет, ниже перечень наиболее часто используемых.

odbc — Драйвер для подключения через ODBC интерфейс;
RSQLite — Драйвер к SQLite;
RMySQL / RMariaDB — Драйвер к СУБД MySQL и MariaDB;
RPostgreSQL — Драйвер к PosrtgreSQL;
bigrquery — Драйвер к Google BigQuery;
RClickhouse / clickhouse — Драйвер к ClickHouse;
RMSSQL — Драйвер к Microsoft SQL Server (MS SQL), на момент написания статьи присутствует только на GitHub.

Пакет DBI поставляется с базовой комплектацией R, но пакеты, которые являются драйверами к базам данных необходимо устанавливать с помощью команды install.packages("название драйвера").

Для установки пакетов с GitHub вам также понадобится дополнительный пакет — devtools. Например пакет RMSSQL на данный момент не опубликован в основном репозитории R пакетов, для его установки воспользуйтесь следующим кодом:

install.packages("devtools") devtools::install_github("bescoto/RMSSQL")

Пример подключения к Microsoft SQL Server с помощью пакета odbc

Перед использованием любого пакета в R сессии его предварительно необходимо подключить с помощью функции library("название пакета").

Я неспроста выбрал Microsoft SQL Server в качестве основной СУБД на которой будет приведена большая часть примеров этой статьи. Дело в том, что это достаточно популярная база данных, но при этом она до сих пор не имеет драйвера для подключения из R опубликованного на CRAN.

Но к счастью SQL Server, как и практически любая другая база имеет ODBC (англ. Open Database Connectivity) интерфейс для подключения. Для подключения к СУБД через ODBC интерфейс в R есть ряд пакетов. Первым мы рассмотрим подключение через пакет odbc.

Простое подключение к БД через odbc интерфейс

# установка пакета odbc install.packages("odbc")  # подключение пакета library(odbc)  # подключение к MS SQL con <- dbConnect(drv = odbc(),                  Driver   = "SQL Server",                  Server   = "localhost",                  Database = "mybase",                  UID      = "my_username",                  PWD      = "my_password",                  Port     = 1433)

В функцию dbConnect() вам необходимо первым аргументом drv передать функцию, которая является драйвером для подключения к СУБД (odbc()). Такие функции обычно называются также, как и СУБД, и поставляются с пакетами которые являются драйверами для DBI.

Далее необходимо перечислить параметры подключения. Для подключения к MS SQL через ODBC необходимо задать следующие параметры:

Driver — Название ODBC драйвера;
Server — IP адрес SQL сервера;
Database — Название базы данных к которой необходимо подключиться;
UID — Имя пользователя базы данных;
PWD — Пароль;
Port — Порт для подключения, у SQL Server по умолчанию порт 1433.

ODBC драйвер для подключения к Microsoft SQL Server включен в комплектацию Windows, но он может иметь и другое название. Посмотреть список установленных драйверов можно в Администраторе источника данных ODBC. Запустить администратор источника данных в Windows 10 можно по следующему пути:

32-разрядной версии: %systemdrive%\Windows\SysWoW64\Odbcad32.exe
64-разрядной версии: %systemdrive%\Windows\System32\Odbcad32.exe

Получить список всех установленных на вашем ПК драйверов также можно с помощью функции odbcListDrivers().

   name                                  attribute        value                                       <chr>                                 <chr>            <chr>                                     1 SQL Server                            APILevel         2                                         2 SQL Server                            ConnectFunctions YYY                                       3 SQL Server                            CPTimeout        60                                        4 SQL Server                            DriverODBCVer    03.50                                     5 SQL Server                            FileUsage        0                                         6 SQL Server                            SQLLevel         1                                         7 SQL Server                            UsageCount       1                                         8 MySQL ODBC 5.3 ANSI Driver            UsageCount       1                                         9 MySQL ODBC 5.3 Unicode Driver         UsageCount       1                                        10 Simba ODBC Driver for Google BigQuery Description      Simba ODBC Driver for Google BigQuery2.0 # ... with 50 more rows

Скачать ODBC драйвера для других СУБД можно по следующим ссылкам:

Для различных СУБД название параметров для подключения могут быть другими, например:

PostgreSQL / MySQL / MariaDB — user, password, host, port, dbname;
GoogleBigQuery — project, dataset;
ClickHouse — user, password, db, port, host;

С помощью администратора источника данных ODBC вы можете запустить мастер для создания ODBC источника данных. Для этого достаточно открыть администратор, перейти на вкладку «Пользовательский DSN» и нажать кнопку «Добавить…».

При создании источника данных используя администратор вы присваиваете ему имя, DSN (Data Source Name).

В примере выше мы создали источник данных с DSN «my_test_source». Теперь мы можем использовать этот источник для подключения к Microsoft SQL Server, и не указывать в коде остальные параметры подключения.

Подключение к БД через odbc интерфейс с использованием DSN

# подключение через DSN con <- dbConnect(odbc(),                 DSN = "my_test_source",                 UID = "my_username",                 PWD = "my_password")

Посмотреть имена всех созданных на вашем ПК источников данных ODBC можно с помощью функции odbcListDataSources().

   name            description                              <chr>           <chr>                                  1 BQ              Simba ODBC Driver for Google BigQuery  2 BQ_main         Simba ODBC Driver for Google BigQuery  3 BQ ODBC         Simba ODBC Driver for Google BigQuery  4 OLX             Simba ODBC Driver for Google BigQuery  5 Multicharts     Simba ODBC Driver for Google BigQuery  6 PostgreSQL35W   PostgreSQL Unicode(x64)                7 hillel_bq       Simba ODBC Driver for Google BigQuery  8 blog_bq         Simba ODBC Driver for Google BigQuery  9 MyClientMSSQL   SQL Server                            10 local_mssql     SQL Server                            11 MSSQL_localhost SQL Server                            12 my_test_source  SQL Server                            13 Google BigQuery Simba ODBC Driver for Google BigQuery

Пример подключения к Microsoft SQL Server с помощью пакета RMSSQL

RMSSQL не опубликован на CRAN, поэтому установить его можно с GitHub с помощью пакета devtools.

install.packages("devtools") devtools::install_github("bescoto/RMSSQL")

Пример подключения с помощью DBI драйвера RMSSQL

# подключение требуемых пакетов library(RJDBC) library(RMSSQL) library(DBI)  # через RMSSQL con <- dbConnect(MSSQLServer(),                   host     = 'localhost',                   user     = 'my_username',                   password = 'my_password',                   dbname   = "mybase")

В большинстве случаев, используя для работы с базами данных пакет DBI, вы будете подключаться именно таким способом. Т.е. устанавливать один из требуемых пакетов — драйверов, передавая в качестве значения аргумента drv функции dbConnect, функцию — драйвер для подключения к нужной вам СУБД.

Пример подключения к MySQL, PostgreSQL, SQLite и BigQuery

# подключение к MySQL library(RMySQL)  con <- dbConnect(MySQL(),                   host     = 'localhost',                   user     = 'my_username',                   password = 'my_password',                   dbname   = "mybase",                  host     = "localhost")  # подключение к PostrgeSQL library(RPostgreSQL)  con <- dbConnect(PostgreSQL(),                   host     = 'localhost',                   user     = 'my_username',                   password = 'my_password',                   dbname   = "mybase",                  host     = "localhost")  # подключение к PostrgeSQL library(RPostgreSQL)  con <- dbConnect(PostgreSQL(),                   host     = 'localhost',                   user     = 'my_username',                   password = 'my_password',                   dbname   = "mybase",                  host     = "localhost")  # Подключение к SQLite library(RSQLite)  # connection or create base con <- dbConnect(drv = SQLite(),                  "localhost.db")  # Подключение к Google BigQuery library(bigrquery)  con <- dbConnect(drv     = bigquery(),                  project = "my_proj_id",                  dataset = "dataset_name")

Создание таблиц и запись в базу данных

Запись в базу данных осуществляется функцией dbWriteTable().

Аргументы функции dbWriteTable():

Жирным шрифтом выделены обязательные аргументы, курсивом — не обязательные

conn — объект подключения к СУБД, созданный с помощью функции dbConnect;
name — название таблицы в СУБД, в которую будут записаны данные;
value — таблица (объект класса data.frame / data.table / tibble_frame) в R, данные из которого будут записаны в СУБД;
row.names — Добавляет в таблицу столбец row_names, с номерами строк, по умолчанию имеет значение FALSE.
overwrite — Перезаписывать таблицу, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
append — Дописывать данные, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
field.types — Принимает на вход именованный вектор, и задаёт тип данных в каждом поле при записи в СУБД, по умолчанию имеет значение NULL;
temporary — Позволяет создавать временные таблицы в СУБД, которые будут доступны до момента разрыва соединения с базой, по умолчанию имеет значение FALSE.

Пример записи данных в СУБД через DBI

# подключаем пакет library(odbc) # соединяемся с базой через DSN con <- dbConnect(odbc(),                 DSN = "my_test_source",                 UID = "my_username",                 PWD = "my_password") # создаём в базе таблицу iris, и записываем в неё данные из встроенного в R набора iris dbWriteTable(conn  = con,              name  = "iris",               value = iris)  # разрыв соединения с БД dbDisconnect(con)

Для просмотра таблиц в базе данных служит функция dbListTables(), для удаления таблиц dbRemoveTable()

Пример запроса списка таблиц и удаления таблицы из СУБД

# подключение пакета library(odbc)  # подключение к БД con <- dbConnect(odbc(),                  DSN = "my_test_source",                  UID = "my_username",                  PWD = "my_password")  # просмотр списка таблиц dbListTables(con) # удаление таблицы iris dbRemoveTable(con, "iris")  # разрыв соединения с БД dbDisconnect(con)

Чтение данных из СУБД

С помощью DBI вы можете запрашивать либо таблицы целиком, либо результат выполнения вашего SQL запроса. Для выполнения этих операций используются функции dbReadTable() и dbGetQuery().

Пример запроса таблицы iris из СУБД

# подключение пакета library(odbc)  # подключение к БД con <- dbConnect(odbc(),                  DSN = "my_test_source",                  UID = "my_username",                  PWD = "my_password")  # загружзка табоицы iris в объект iris dbiris <- dbReadTable(con, "iris")  # разрыв соединения с БД dbDisconnect(con)

Пример загрузки результата выполнения SQL из СУБД

# подключение пакета library(odbc)  # подключение к БД con <- dbConnect(odbc(),                  DSN = "my_test_source",                  UID = "my_username",                  PWD = "my_password")  # Запрашиваем результат выполнения запроса setosa <- dbGetQuery(con,                      "SELECT * FROM iris WHERE Species = 'setosa'")  # разрыв соединения с БД dbDisconnect(con)

Манипулирование данными в СУБД (DML)

Рассмотренная выше функция dbGetQuery() используется исключительно для запросов на выборку данных (SELECT).

Для операций манипуляций с данными, таких как UPDATE, INSERT, DELETE, в DBI существует функция dbExecute().

Пример кода для манипуляции данными в СУБД

# подключение пакета library(odbc)  # подключение к БД con <- dbConnect(odbc(),                  DSN = "my_test_source",                  UID = "my_username",                  PWD = "my_password")  # Вставка строк (INSERT) dbExecute(con,            "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])            VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")  # Обновление данных (UPDATE) dbExecute(con,            "UPDATE iris            SET [Species] = 'old_value'            WHERE row_names = 51")  # Удаление строк из таблицы (DELETE) dbExecute(con, "DELETE FROM iris WHERE row_names = 51")  # разрыв соединения с БД dbDisconnect(con)

Транзакции в СУБД

Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.

Цитата из статьи «Транзакции и механизмы их контроля»

Транзакция инкапсулирует несколько операторов SQL в элементарную единицу. В DBI начало транзакции инициируется с помощью dbBegin() и далее либо подтверждается с помощью dbCommit(), либо отменяется с помощью dbRollback(). В любом случае СУБД гарантирует, что: либо все, либо ни одно из утверждений не будут применены к данным.

Для примера, давайте в ходе транзакции добавим в таблицу iris 51 строку, далее изменим значение Sepal.Width в 5 строке, и удалим 43 строку из таблицы.

Пример кода проведения транзакции

# подключение пакета library(odbc)  # подключение к БД con <- dbConnect(odbc(),                  DSN = "my_test_source",                  UID = "my_username",                  PWD = "my_password")  # запрашиваем значения до внесения изменений dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")  #   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species # 1         5          5.0         3.6          1.4         0.2     setosa # 2        43          4.4         3.2          1.3         0.2     setosa # 3        51          7.0         3.2          4.7         1.4 versicolor  # инициируем начало транзакции dbBegin(con)  # добавляе строку dbExecute(con,            "INSERT INTO iris            (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])            VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")  # меняем строку  dbExecute(con,            "UPDATE iris            SET [Sepal.Width] = 8            WHERE row_names = 5")  # удаляем строку 43 dbExecute(con, "DELETE FROM iris WHERE row_names = 43")  # подтверждаем транзакцию dbCommit(con)  # запрашиваем значения после внесения изменений dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")  #   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species # 1         5            5         8.0          1.4         0.2     setosa # 2        51            7         3.2          4.7         1.4 versicolor # 3        51            5         3.3          1.7         0.3 new_values

Пример кода отмены транзакции

# подключение пакета library(odbc)  # подключение к БД con <- dbConnect(odbc(),                  DSN = "my_test_source",                  UID = "my_username",                  PWD = "my_password")  # запрашиваем значения до внесения изменений dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")  #   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species # 1         5          5.0         3.6          1.4         0.2     setosa # 2        43          4.4         3.2          1.3         0.2     setosa # 3        51          7.0         3.2          4.7         1.4 versicolor  # инициируем начало транзакции dbBegin(con)  # добавляе строку dbExecute(con,            "INSERT INTO iris            (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])            VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")  # меняем строку  dbExecute(con,            "UPDATE iris            SET [Sepal.Width] = 8            WHERE row_names = 5")  # удаляем строку 43 dbExecute(con, "DELETE FROM iris WHERE row_names = 43")  # отменяем транзакцию dbRollback(con)  # запрашиваем значения после внесения изменений dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")  #   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species # 1         5          5.0         3.6          1.4         0.2     setosa # 2        43          4.4         3.2          1.3         0.2     setosa # 3        51          7.0         3.2          4.7         1.4 versicolor

Пакет RODBC

Пакет RODBC предоставляет автономный интерфейс для подключения и работы с СУБД через ODBC интерфейс.

RODBC не совместим с DBI, т.е. вы не можете использовать объект подключения созданный с помощью RODBC в функциях предоставляемых пакетом DBI.

Основные функции пакета RODBC

odbcConnect — Подключение к СУБД через DSN;
odbcDriverConnect — Подключение к базе через строку подключения;
sqlQuery — Отправка запроса в СУБД, и получение результата его выполнения. Поддерживает запросы любого типа: SELECT, UPDATE, INSERT, DELETE.
sqlFetch — Получить целиком таблицу из СУБД;
sqlTables — Получить список таблиц в базе.
sqlSave — Создание новой таблицы в базе данных, или добавление новых данных в уже существующую таблицу;
sqlUpdate — Обновление данных в таблице которая уже существует в СУБД;
sqlDrop — Удаление таблицы в СУБД;
odbcClose — Завершение соединения с СУБД.

Пример работы с RODBC

С моей точки зрения RODBC менее функционален чем DBI, но в нём есть все необходимые функции для работы с СУБД.

Пример взаимодействия с СУБД через RODBC

# подключение пакета library(RODBC) # строка подключения con_string <- odbcDriverConnect(connection = "Driver=SQL Server;Server=localhost;Database=mybase;UID=my_username;PWD=my_password;Port=1433") # подключение через DSN con_dsn    <- odbcConnect(dsn = "my_test_source",                           uid = "my_username",                           pwd = "my_password")  # создание таблицы в базе sqlSave(con_dsn,         dat       = iris,         tablename = "iris")  # дописать строки в табдицу iris sqlSave(con_dsn,         dat       = iris,         tablename = "iris",         append    = TRUE)  # запрашиваем первые 4 строки sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)  # вносим изменение в данные в R iris[1, 5] <- "virginica"  # обновляем табицу в СУБД sqlUpdate(con_dsn,           dat       = iris,           tablename = "iris")  # запрашиваем первые 4 строки после изменения данных sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)  # удаление таблицы sqlDrop(con_dsn, sqtable = "iris")  # разрыв соеденения с базой odbcCloseAll()

Транзакционность

По умолчанию транзакционность в RODBC выключена. Управление транзакциями осуществляется двумя функциями.

odbcSetAutoCommit — Переключение между обычным и транзакционным режимом работы с СУБД;
odbcEndTran — Подтверждение или отмена транзакции.

Включение и отключение транзакционного режима осуществляется функцией odbcSetAutoCommit с помощью аргумента autoCommit.

Примре работы в транзакционном режиме в RODBC

# подключение пакета library(RODBC) # подключение через DSN con_dsn    <- odbcConnect(dsn = "my_test_source",                           uid = "my_username",                           pwd = "my_password")  # создание таблицы в базе sqlSave(con_dsn,         dat       = iris,         tablename = "iris")  # включение транзакционного режима odbcSetAutoCommit(con_dsn, autoCommit = FALSE)  # запрашиваем первые 4 строки sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)  #   rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1        1         5.1        3.5         1.4        0.2  setosa # 2        2         4.9        3.0         1.4        0.2  setosa # 3        3         4.7        3.2         1.3        0.2  setosa # 4        4         4.6        3.1         1.5        0.2  setosa  # вносим изменение в данные в R iris[1, 5] <- "virginica"  # обновляем табицу в СУБД sqlUpdate(con_dsn,           dat       = iris,           tablename = "iris")  # запрашиваем первые 4 строки sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)  #   rownames SepalLength SepalWidth PetalLength PetalWidth   Species # 1        1         5.1        3.5         1.4        0.2 virginica # 2        2         4.9        3.0         1.4        0.2    setosa # 3        3         4.7        3.2         1.3        0.2    setosa # 4        4         4.6        3.1         1.5        0.2    setosa  # отменяем изменения odbcEndTran(con_dsn, commit = FALSE)  # запрашиваем первые 4 строки sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)  #   rownames SepalLength SepalWidth PetalLength PetalWidth Species # 1        1         5.1        3.5         1.4        0.2  setosa # 2        2         4.9        3.0         1.4        0.2  setosa # 3        3         4.7        3.2         1.3        0.2  setosa # 4        4         4.6        3.1         1.5        0.2  setosa  # обновляем табицу в СУБД sqlUpdate(con_dsn,           dat       = iris,           tablename = "iris")  # применяем изменения odbcEndTran(con_dsn, commit = TRUE)  # запрашиваем первые 4 строки после изменения данных sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)  #   rownames SepalLength SepalWidth PetalLength PetalWidth   Species # 1        1         5.1        3.5         1.4        0.2 virginica # 2        2         4.9        3.0         1.4        0.2    setosa # 3        3         4.7        3.2         1.3        0.2    setosa # 4        4         4.6        3.1         1.5        0.2    setosa  # разрыв соеденения с базой odbcClose(con_dsn)

Заключение

Два описанных в статье метода работы с базами данных на языке R, DBI и RODBC, достаточно универсальны, и будут работать практически с любой СУБД.

Единственная разница в работе между различными СУБД заключается в процессе подключения. Для большинства популярных СУБД существуют отдельные пакеты которые являются драйверами. Для остальных СУБД необходимо настраивать подключение через ODBC интерфейс используя пакеты odbc или RODBC. Все остальные манипуляции, вне зависимости от выбранной вами СУБД, будут неизменны. Исключением является отправка SQL запросов, в зависимости от SQL диалекта который поддерживается СУБД с которой вы работаете.

ссылка на оригинал статьи https://habr.com/ru/post/461063/

Взаимодействие R с базами данных на примере Microsoft SQL Server и других СУБД