Databases
2015-06-15
Перевод
https://cran.r-project.org/web/packages/dplyr/vignettes/databases.html
Наряду с локальными таблицами данных (data frames / data tables), расположенными в памяти, dplyr также работает с удаленными данными на дисках, хранящимися в базах данных. Как правило, если ваши данные помещаются в памяти, то нет никаких преимуществ в использовании базы данных: меньше скорость, больше хлопот. Причиной, по которой вы захотите использовать dplyr с базой данных, может быть то, что ваши данные уже находятся в базе данных (и вы не хотите работать со статичными csv-файлами, которые кто-то для вас создал), или вы имеете так много данных, что они не помещаются в память. В настоящее время dplyr поддерживает три самые популярные базы данных с открытым исходным кодом (SQLite, MySQL и PostgreSQL) и Google BigQuery.Так как R почти всегда работает с данными в памяти, если у вас есть много данных в базе данных, вы не можете просто поместить их в R. Вместо этого вам придется работать с поднаборами или агрегированными данными, и dplyr стремится сделать это как можно более простым. Если вы работаете с большими данными, то вы также, вероятно, будете нуждаться в поддержке для получения данных в базу данных и для обеспечения наличия правильных индексов для хорошей производительности. dplyr предоставляет несколько простых инструментов, чтобы помочь с этими задачами, но они не могут заменить соответствующего специалиста.
Мотивацией для поддержки баз данных в dplyr является то, что вы никогда не извлекаете правильный поднабор или агрегированные данные из базы данных с первого раза, и обычно нужно переключаться между R и SQL много раз, пока не будет получен идеальный набор данных. Переключаться между языками когнитивно сложно (особенно потому, что R и SQL так опасно похожи друг на друга), так что dplyr позволяет писать код на R, который автоматически переводится в SQL. Целью dplyr не является замена всех функций SQL: это сложно и чревато ошибками. Вместо этого dplyr генерирует только выражения
SELECT - команды SQL, которые вы пишете чаще всего в качестве аналитика.Чтобы получить максимальную отдачу от этой главы, вы должны быть знакомы с запросами к базам данных SQL с использованием команды
SELECT. Если вы знакомы с SQL и хотите узнать больше, я думаю, будут полезны материалы как индексы работают в SQLite и 10 простых шагов для полного понимания SQL (ссылка не работает, но эти материалы можно нагуглить; еще пригодится Бен Форта “Освой самостоятельно SQL” - прим. пер.).Начало работы
Для экспериментов с базами данных проще всего начать с SQLite, поскольку все необходимое включено в пакет R. Вам не нужно устанавливать что-либо еще и иметь дело с настройкой сервера базы данных. Использовать базу данных SQLite в dplyr очень просто: достаточно задать путь и отметить, что нужно её создать:my_db <- src_sqlite("my_db.sqlite3", create = T)src - коллекция таблиц. Используйте src_sqlite(), src_mysql(), src_postgres() и src_bigquery() для соединения с разными базами данных, поддерживаемыми в dplyr.my_db в настоящий момент не содержит данных, поэтому мы загрузим туда данные flights с использованием удобной функции copy_to(). Это быстрый и “грязный”" способ для того, чтобы поместить данные в базу данных, но он не подходит для очень больших наборов данных, поскольку все данные должны проходить через R.library(nycflights13)
flights_sqlite <- copy_to(my_db, flights, temporary = FALSE,
indexes = list(c("year", "month", "day"), "carrier", "tailnum"))copy_to() имеет дополнительный аргумент, который позволяет вам задавать индексы для таблицы. Здесь мы создали индексы, которые позволят нам быстро преобразовывать данные по дням, перевозчикам и самолетам. copy_to() также выполняет команду SQL ANALYZE: это гарантирует, что база данных имеет актуальную статистику таблицу и может выбрать соответствующие средства оптимизации запросов.Для этого набора данных есть встроенная функция
src, которая будет кэшировать flights в стандартное расположение:flights_sqlite_copy <- tbl(nycflights13_sqlite(), "flights")## Caching nycflights db at C:\Users\A4F7~1\AppData\Local\Temp\Rtmpas5qzM/nycflights13.sqlite
## Creating table: airlines
## Creating table: airports
## Creating table: flights
## Creating table: planes
## Creating table: weatherflights_sqlite_copy## Source: sqlite 3.8.6 [C:\Users\A4F7~1\AppData\Local\Temp\Rtmpas5qzM/nycflights13.sqlite]
## From: flights [336,776 x 16]
##
## year month day dep_time dep_delay arr_time arr_delay carrier tailnum
## 1 2013 1 1 517 2 830 11 UA N14228
## 2 2013 1 1 533 4 850 20 UA N24211
## 3 2013 1 1 542 2 923 33 AA N619AA
## 4 2013 1 1 544 -1 1004 -18 B6 N804JB
## 5 2013 1 1 554 -6 812 -25 DL N668DN
## 6 2013 1 1 554 -4 740 12 UA N39463
## 7 2013 1 1 555 -5 913 19 B6 N516JB
## 8 2013 1 1 557 -3 709 -14 EV N829AS
## 9 2013 1 1 557 -3 838 -8 B6 N593JB
## 10 2013 1 1 558 -2 753 8 AA N3ALAA
## .. ... ... ... ... ... ... ... ... ...
## Variables not shown: flight (int), origin (chr), dest (chr), air_time
## (dbl), distance (dbl), hour (dbl), minute (dbl)tbl с помощью SQL:tbl(my_db, sql("SELECT * FROM flights"))## Source: sqlite 3.8.6 [my_db.sqlite3]
## From: <derived table> [?? x 16]
##
## year month day dep_time dep_delay arr_time arr_delay carrier tailnum
## 1 2013 1 1 517 2 830 11 UA N14228
## 2 2013 1 1 533 4 850 20 UA N24211
## 3 2013 1 1 542 2 923 33 AA N619AA
## 4 2013 1 1 544 -1 1004 -18 B6 N804JB
## 5 2013 1 1 554 -6 812 -25 DL N668DN
## 6 2013 1 1 554 -4 740 12 UA N39463
## 7 2013 1 1 555 -5 913 19 B6 N516JB
## 8 2013 1 1 557 -3 709 -14 EV N829AS
## 9 2013 1 1 557 -3 838 -8 B6 N593JB
## 10 2013 1 1 558 -2 753 8 AA N3ALAA
## .. ... ... ... ... ... ... ... ... ...
## Variables not shown: flight (int), origin (chr), dest (chr), air_time
## (dbl), distance (dbl), hour (dbl), minute (dbl)Основные глаголы
Удаленные источники данных используют те же самые 5 глаголов, что и локальные:select(flights_sqlite, year:day, dep_delay, arr_delay)## Source: sqlite 3.8.6 [my_db.sqlite3]
## From: flights [336,776 x 5]
##
## year month day dep_delay arr_delay
## 1 2013 1 1 2 11
## 2 2013 1 1 4 20
## 3 2013 1 1 2 33
## 4 2013 1 1 -1 -18
## 5 2013 1 1 -6 -25
## 6 2013 1 1 -4 12
## 7 2013 1 1 -5 19
## 8 2013 1 1 -3 -14
## 9 2013 1 1 -3 -8
## 10 2013 1 1 -2 8
## .. ... ... ... ... ...filter(flights_sqlite, dep_delay > 240)## Source: sqlite 3.8.6 [my_db.sqlite3]
## From: flights [1,524 x 16]
## Filter: dep_delay > 240
##
## year month day dep_time dep_delay arr_time arr_delay carrier tailnum
## 1 2013 1 1 848 853 1001 851 MQ N942MQ
## 2 2013 1 1 1815 290 2120 338 EV N17185
## 3 2013 1 1 1842 260 1958 263 EV N18120
## 4 2013 1 1 2115 255 2330 250 9E N924XJ
## 5 2013 1 1 2205 285 46 246 AA N5DNAA
## 6 2013 1 1 2343 379 314 456 EV N21197
## 7 2013 1 2 1332 268 1616 288 EV N41104
## 8 2013 1 2 1412 334 1710 323 UA N474UA
## 9 2013 1 2 1607 337 2003 368 AA N324AA
## 10 2013 1 2 2131 379 2340 359 UA N593UA
## .. ... ... ... ... ... ... ... ... ...
## Variables not shown: flight (int), origin (chr), dest (chr), air_time
## (dbl), distance (dbl), hour (dbl), minute (dbl)arrange(flights_sqlite, year, month, day)## Source: sqlite 3.8.6 [my_db.sqlite3]
## From: flights [336,776 x 16]
## Arrange: year, month, day
##
## year month day dep_time dep_delay arr_time arr_delay carrier tailnum
## 1 2013 1 1 517 2 830 11 UA N14228
## 2 2013 1 1 533 4 850 20 UA N24211
## 3 2013 1 1 542 2 923 33 AA N619AA
## 4 2013 1 1 544 -1 1004 -18 B6 N804JB
## 5 2013 1 1 554 -6 812 -25 DL N668DN
## 6 2013 1 1 554 -4 740 12 UA N39463
## 7 2013 1 1 555 -5 913 19 B6 N516JB
## 8 2013 1 1 557 -3 709 -14 EV N829AS
## 9 2013 1 1 557 -3 838 -8 B6 N593JB
## 10 2013 1 1 558 -2 753 8 AA N3ALAA
## .. ... ... ... ... ... ... ... ... ...
## Variables not shown: flight (int), origin (chr), dest (chr), air_time
## (dbl), distance (dbl), hour (dbl), minute (dbl)mutate(flights_sqlite, speed = air_time / distance)## Source: sqlite 3.8.6 [my_db.sqlite3]
## From: flights [336,776 x 17]
##
## year month day dep_time dep_delay arr_time arr_delay carrier tailnum
## 1 2013 1 1 517 2 830 11 UA N14228
## 2 2013 1 1 533 4 850 20 UA N24211
## 3 2013 1 1 542 2 923 33 AA N619AA
## 4 2013 1 1 544 -1 1004 -18 B6 N804JB
## 5 2013 1 1 554 -6 812 -25 DL N668DN
## 6 2013 1 1 554 -4 740 12 UA N39463
## 7 2013 1 1 555 -5 913 19 B6 N516JB
## 8 2013 1 1 557 -3 709 -14 EV N829AS
## 9 2013 1 1 557 -3 838 -8 B6 N593JB
## 10 2013 1 1 558 -2 753 8 AA N3ALAA
## .. ... ... ... ... ... ... ... ... ...
## Variables not shown: flight (int), origin (chr), dest (chr), air_time
## (dbl), distance (dbl), hour (dbl), minute (dbl), speed (dbl)summarise(flights_sqlite, delay = mean(dep_time))## Source: sqlite 3.8.6 [my_db.sqlite3]
## From: <derived table> [?? x 1]
##
## delay
## 1 1349.11
## .. ...select(), filter(), arrange(), mutate() и summarise() транслируются в SQL, так что они могут выполняться на базе данных. Эта трансляция является почти идеальной для наиболее распространенных операций, но существуют некоторые ограничения, о которых вы узнаете чуть позже.Ленивость
При работе с базами данных dplyr старается быть настолько “ленивым”“, насколько это возможно, двумя способами:- Никогда не возвращает данные в R, пока вы явно не попросите об этом.
- Это задерживает выполнение любой работы до последней минуты, собирая вместе всё, что вы хотите сделать, и осуществляя запрос к базе данных в один шаг.
c1 <- filter(flights_sqlite, year == 2013, month == 1, day == 1)
c2 <- select(c1, year, month, day, carrier, dep_delay, air_time, distance)
c3 <- mutate(c2, speed = distance / air_time * 60)
c4 <- arrange(c3, year, month, day, carrier)c4). Тогда dplyr создаст код SQL и запросит результаты из базы данных, и даже здесь будет возвращено только 10 строк.c4## Source: sqlite 3.8.6 [my_db.sqlite3]
## From: flights [842 x 8]
## Filter: year == 2013, month == 1, day == 1
## Arrange: year, month, day, carrier
##
## year month day carrier dep_delay air_time distance speed
## 1 2013 1 1 9E 0 189 1029 326.6667
## 2 2013 1 1 9E -9 57 228 240.0000
## 3 2013 1 1 9E -3 68 301 265.5882
## 4 2013 1 1 9E -6 57 209 220.0000
## 5 2013 1 1 9E -8 66 264 240.0000
## 6 2013 1 1 9E 0 40 184 276.0000
## 7 2013 1 1 9E 6 146 740 304.1096
## 8 2013 1 1 9E 0 139 665 287.0504
## 9 2013 1 1 9E -8 150 765 306.0000
## 10 2013 1 1 9E -6 41 187 273.6585
## .. ... ... ... ... ... ... ... ...collect(), которая возвращает tbl_df():collect(c4)## Source: local data frame [842 x 8]
##
## year month day carrier dep_delay air_time distance speed
## 1 2013 1 1 9E 0 189 1029 326.6667
## 2 2013 1 1 9E -9 57 228 240.0000
## 3 2013 1 1 9E -3 68 301 265.5882
## 4 2013 1 1 9E -6 57 209 220.0000
## 5 2013 1 1 9E -8 66 264 240.0000
## 6 2013 1 1 9E 0 40 184 276.0000
## 7 2013 1 1 9E 6 146 740 304.1096
## 8 2013 1 1 9E 0 139 665 287.0504
## 9 2013 1 1 9E -8 150 765 306.0000
## 10 2013 1 1 9E -6 41 187 273.6585
## .. ... ... ... ... ... ... ... ...query объекта:c4$query## <Query> SELECT "year" AS "year", "month" AS "month", "day" AS "day", "carrier" AS "carrier", "dep_delay" AS "dep_delay", "air_time" AS "air_time", "distance" AS "distance", "distance" / "air_time" * 60.0 AS "speed"
## FROM "flights"
## WHERE "year" = 2013.0 AND "month" = 1.0 AND "day" = 1.0
## ORDER BY "year", "month", "day", "carrier"
## <SQLiteConnection>explain(). Вывод для SQLite более детально описан на сайте SQLite; это полезно, если вы пытаетесь выяснить, какие индексы используются.explain(c4)## <SQL>
## SELECT "year" AS "year", "month" AS "month", "day" AS "day", "carrier" AS "carrier", "dep_delay" AS "dep_delay", "air_time" AS "air_time", "distance" AS "distance", "distance" / "air_time" * 60.0 AS "speed"
## FROM "flights"
## WHERE "year" = 2013.0 AND "month" = 1.0 AND "day" = 1.0
## ORDER BY "year", "month", "day", "carrier"
##
##
## <PLAN>
## selectid order from
## 1 0 0 0
## 2 0 0 0
## detail
## 1 SEARCH TABLE flights USING INDEX flights_year_month_day (year=? AND month=? AND day=?)
## 2 USE TEMP B-TREE FOR RIGHT PART OF ORDER BYПринудительные вычисления
Есть три способа принудительных вычислений запроса:collect()выполняет запрос и возвращает результаты в R.compute()выполняет запрос и сохраняет результаты во временной таблице в базе данных.collapse()превращает запрос в табличное выражение.
collect(): как только вы в интерактивном режиме дошли до правильного набора операций, используйте collect() для извлечения данный в локальный tbl_df(). При наличии знаний SQL вы можете использовать compute() и collapse() для оптимизации производительности.Вопросы производительности
dplyr пытается предотвратить случайное выполнение дорогостоящих операций запроса:nrow()всегда NA: в общем, нет никакого способа определить, сколько строк вернёт запрос, без выполнения самого запроса.- Печать таблицы запускает запрос, нужный только для получения первых 10 строк.
- Вы не можете использовать
tail()для таблиц базы данных: нельзя найти последние строки без выполнения запроса целиком.
Трансляция в SQL
При выполнении простых математических операций в форме, которую вы обычно используете при фильтрации, трансформации и обобщении, можно относительно просто перевести код R в SQL (или даже на любой язык программирования).Для эксперимента с переводом, используйте
translate_sql(). Следующие примеры показывают некоторые основные различия между R и SQL.# В SQLite имена переменных экранируются двойными кавычками
translate_sql(x)## <SQL> "x"# Строки экранируются одинарными кавычками
translate_sql("x")## <SQL> 'x'# Многие функции имеют слегка отличающиеся имена
translate_sql(x == 1 && (y < 2 || z > 3))## <SQL> "x" = 1.0 AND ("y" < 2.0 OR "z" > 3.0)translate_sql(x ^ 2 < 10)## <SQL> POWER("x", 2.0) < 10.0translate_sql(x %% 2 == 10)## <SQL> "x" % 2.0 = 10.0# R и SQL имеют разные значения по умолчания для целых и вещественных чисел.
# В R, 1 - вещественное, 1L - целое
# В SQL, 1 - целое, 1.0 - вещественное
translate_sql(1)## <SQL> 1.0translate_sql(1L)## <SQL> 1- основные математические операторы:
+,-,*,/,%%,^ - математические функции:
abs,acos,acosh,asin,asinh,atan,atan2,atanh,ceiling,cos,cosh,cot,coth,exp,floor,log,log10,round,sign,sin,sinh,sqrt,tan,tanh - логические сравнения:
<,<=,!=,>=,>,==,%in% - булевы операции:
&,&&,|,||,!,xor - основные агрегирующие функции:
mean,sum,min,max,sd,var
translate_sql(), описаны в книге Advanced R. translate_sql() построен поверх синтаксического движка R и был тщательно разработан, чтобы генерировать правильный код SQL. Он также защищает вас от атак типа SQL-инъекций путем правильного экранирования строк и имен переменных, как того требует база данный, к которой вы реализуете соединение.Невозможно обеспечить идеальную трансляцию, поскольку базы данных не имеют всех функций языка R. Целью dplyr является обеспечение смысловой трансляции: перевести, что вы имеете ввиду, без точных подробностей. Даже если функции существуют и в базах данных, и в R, вам не следует ожидать в точности одинаковых результатов; приоритеты программистов баз данных отличаются от приоритетов разработчиков R.
Например, в R функция
mean() делает два прохода по данным с целью повышения вычислительной точности ценой двукратного замедления. mean() также предоставляет опцию trim для расчёта усечённых средних, чего не предоставляют базы данных. Базы данных автоматически отбрасывают NULLs (их эквивалент пропущенных значений), в то время как в R вы должны об этом попросить. Это означает, что простые вызовы типа mean(x) будут транслироваться точно, но более сложные, такие как mean(x, trim = 0.5, na.rm = TRUE), вызовут ошибку:translate_sql(mean(x, trim = T))
# Error: Invalid number of args to SQL AVG. Expecting 1translate_sql(glob(x, y))## <SQL> GLOB("x", "y")translate_sql(x %like% "ab*")## <SQL> "x" LIKE 'ab*'Группировка
SQLite не хватает “оконных” функций, которые нужны для сгруппированного преобразования и фильтрации. Это означает, что единственной полезной операцией для группировки таблиц sqlite являетсяsummarise(). Сгруппированные итоги из введения транслируются правильно - единственное различие состоит в том, что базы данных всегда отбрасывают NULLs (их эквивалент пропущенных значений), поэтому не используется na.rm = TRUE.by_tailnum <- group_by(flights_sqlite, tailnum)
delay <- summarise(by_tailnum,
count = n(),
dist = mean(distance),
delay = mean(arr_delay)
)
delay <- filter(delay, count > 20, dist < 2000)
delay_local <- collect(delay)Другие базы данных
Использование других баз данных вместо SQLite работает аналогично, общий рабочий процесс такой же, независимо от того, к какой базе данных вы подключаетесь. В следующих разделах перейдем к более подробной информации об особенностях каждой базы данных. Все эти базы данных следуют модели “клиент-сервер” - кроме вашего компьютер, который подключается к базе данных, есть другой компьютер, который на самом деле осуществляет обработку (сервером может выступать ваш компьютер, но, как правило, это не так). Получение настроек этих баз данных выходит за рамки данной статьи, но есть много учебников, доступных в Интернете.Postgresql
src_postgres() имеет пять аргументов: dbname, host, port, user и password. Если вы используете локальную базу данных postgresql с настройками по умолчанию, вам потребуется только dbname, но в большинстве случаев нужны все аргументы. dplyr использует пакет RPostgreSQL для соединения с базой данных postgres. Это означает, что сейчас вы не можете подключиться к удаленной базе данных, требующей соединения SSL (например, Heroku).Например, следующий фрагмент кода позволяет мне подключиться к локальной базе данных postgresql, содержащей копию данных
flights:if (has_lahman("postgres")) {
flights_postgres <- tbl(src_postgres("nycflights13"), "flights")
}- более широкий спектр встроенных функций
- поддержку оконных функций, что позволяет работать сгруппированным преобразованиям и выделению поднаборов
if (has_lahman("postgres")) {
daily <- group_by(flights_postgres, year, month, day)
# Найти наибольшую и наименьшую задержку рейса за каждый день
bestworst <- daily %>%
select(flight, arr_delay) %>%
filter(arr_delay == min(arr_delay) || arr_delay == max(arr_delay))
bestworst$query
# Позиция каждого полета в пределах дня
ranked <- daily %>%
select(arr_delay) %>%
mutate(rank = rank(desc(arr_delay)))
ranked$query
}MySQL и MariaDB
Вы можете подключиться к MySQL и MariaDB (форк MySQL) используяsrc_mysql() через пакет RMySQLe. Подобно PostgreSQL, вам потребуется dbname, username, password, host и port.С точки зрения функциональности, MySQL лежит где-то между SQLite и PostgreSQL. Она обеспечивает более широкий спектр встроенных функций, но не поддерживает “оконные” функции (поэтому вы не сможете выполнять сгруппированные преобразования и фильтрацию).
Bigquery
Bigquery является сервером баз данных, предоставляемым google. Для подключения вы должны предоставить вашиproject, dataset и, возможно, проект для billing (если биллинг для project не включен). После создания src ваш веб-браузер откроется и попросит вас пройти аутентификацию. Ваши учетные данные хранятся в локальном кэше, так что вы должны будете сделать это только один раз.Bigquery поддерживает только один оператор SQL: SELECT. К счастью, это все, что вам нужно для анализа данных, и с помощью SELECT bigquery предоставляет полный охват на том же уровне, что и postgresql.
Прочитал и в оригинале и у вас, так и не понял, как одно следует из другого.
ОтветитьУдалитьЕсли вы используете локальную базу данных postgresql с настройками по умолчанию, вам потребуется только dbname, но в большинстве случаев нужны все аргументы. dplyr использует пакет RPostgreSQL для соединения с базой данных postgres. Это означает, что сейчас вы не можете подключиться к удаленной базе данных, требующей соединения SSL (например, Heroku).