Как извлечь с помощью фильтра dplyr по первому столбцу таблицы в БД sqlite

Question

Я пытаюсь извлечь информацию из таблицы sqlite с помощью dplyr,

Norway <- tbl(conn, "own_fleet") %>% (mmsi==235060247) %>% filter(timestamp>='2018-08-16T00:00:01') %>% collect()

Это приводит к ошибке:

Error in eval(rhs, env, env) : object 'mmsi' not found

тем не мение mmsi существует в таблице и является первым столбцом, который я подтвердил, запустив PRAGMA table_info(own_fleet),

sqlite> PRAGMA table_info(own_fleet);
0|mmsi|INTEGER|0||0
1|lat|REAL|0||0
2|lon|REAL|0||0
3|rateOfTurn|INTEGER|0||0
4|sogKts|REAL|0||0
5|cog|REAL|0||0
6|heading|REAL|0||0
7|timestamp|TEXT|0||0
8|imoNumber|INTEGER|0||0
9|dimensionToBow|INTEGER|0||0
10|dimensionToStern|INTEGER|0||0
11|dimensionToPort|INTEGER|0||0
12|dimensionToStarboard|INTEGER|0||0
13|etaMonth|INTEGER|0||0
14|etaDay|INTEGER|0||0
15|etaHour|INTEGER|0||0
16|etaMinute|INTEGER|0||0
17|draught|INTEGER|0||0
18|name|TEXT|0||0
19|destination|TEXT|0||0
20|callsign|TEXT|0||0

Также timestamp хранится как символьная переменная в таблице. Есть ли способ конвертировать его в формат даты с помощью скажем ymd_hms() от lubridate внутри filter() от dplyr?

Answer 1

У меня были похожие сообщения об ошибках при попытке подключения к базе данных. Из этих примеров я обошел проблему, используя dplyr::collect()до правонарушения и/или .

Сделайте некоторые образцы данных и запишите их в новую базу данных:

      library(tidyverse)
library(lubridate)
library(RSQLite)
library(dbplyr)

# make a sample table, with dates
tibble(
  datetime = seq(ymd_hms('2020-03-11 12:00:00'), ymd_hms('2020-03-30 12:00:00'), by = '5 days'), 
  id = 1:4
) %>% 
  mutate_all(as.character) %>% 
  {. ->> my_data}

my_data

# # A tibble: 4 x 2
#   datetime               id
#   <dttm>              <int>
# 1 2020-03-11 12:00:00     1
# 2 2020-03-16 12:00:00     2
# 3 2020-03-21 12:00:00     3
# 4 2020-03-26 12:00:00     4


# make database
conn <- DBI::dbConnect(RSQLite::SQLite(), 'my_database.sqlite')

# add data to the database
DBI::dbWriteTable(conn, 'my_data', my_data, overwrite = TRUE)

Получить таблицу из базы данных; обратите внимание, что я сохранил дату и время в символьном формате, чтобы предотвратить их преобразование в числовой формат при сохранении в базу данных:

      # retrieve database
my_database <- tbl(conn, 'my_data')

my_database

# # Source:   table<my_data> [?? x 2]
# # Database: sqlite 3.37.0 [\\Ucstaff\dfs\Associated Organisations\Institute for Applied
# #   Ecology\Personnel\ALLAN\Masters\r\projects\shiny apps\cotter acoustic project\cotter acoustic project_1-5\my_database.sqlite]
#   datetime            id   
#   <chr>               <chr>
# 1 2020-03-11 12:00:00 1    
# 2 2020-03-16 12:00:00 2    
# 3 2020-03-21 12:00:00 3    
# 4 2020-03-26 12:00:00 4 

Тогда, если мы попробуем и datetimeстолбец с использованием , мы получаем ошибку.

      my_database %>% 
  mutate(
    datetime = ymd_hms(datetime)
  )
# Error: no such function: ymd_hms

Ошибка предполагает, что R не может найти ymd_hms()функция от lubridate, поэтому я попытался явно вызвать его:

      my_database %>% 
  mutate(
    datetime = lubridate::ymd_hms(datetime)
  )
# Error in lapply(list(...), .num_to_date) : object 'datetime' not found

Эта ошибка предполагает, что столбец не существует, что странно.

Точно так же использование возвращает неожиданные результаты, чем при фильтрации обычного data.frameили же . Обычно я считаю, что вы можете указать число в качестве фильтра для столбца символов без проблем (я предполагаю, что R должен преобразовать одно или другое, чтобы найти совпадения), но это, похоже, не работает с подключением к базе данных.

      my_database %>% 
  filter(
    id == 2
  )

# Source:   lazy query [?? x 2]
# Database: sqlite 3.37.0 [\\Ucstaff\dfs\Associated Organisations\Institute for Applied
#   Ecology\Personnel\ALLAN\Masters\r\projects\shiny apps\cotter acoustic project\cotter acoustic project_1-5\my_database.sqlite]
# ... with 2 variables: datetime <chr>, id <chr>

## no results


my_database %>% 
  filter(
    id == '2'
  )

# # Source:   lazy query [?? x 2]
# # Database: sqlite 3.37.0 [\\Ucstaff\dfs\Associated Organisations\Institute for Applied
# #   Ecology\Personnel\ALLAN\Masters\r\projects\shiny apps\cotter acoustic project\cotter acoustic project_1-5\my_database.sqlite]
#   datetime            id   
#   <chr>               <chr>
# 1 2020-03-16 12:00:00 2

Таким образом, мы можем обойти эти проблемы, используя collect()до filter()или же mutate(), который извлекает данные в локальный tibble. Я предполагаю, что это означает, что вы работаете локально, а не через базу данных, но это один из способов обойти это.

      my_database %>% 
  collect %>% 
  filter(
    id == 2
  )

# # A tibble: 1 x 2
#   datetime            id   
#   <chr>               <chr>
# 1 2020-03-16 12:00:00 2  


my_database %>% 
  collect %>% 
  mutate(datetime = ymd_hms(datetime))

# # A tibble: 4 x 2
#   datetime            id   
#   <dttm>              <chr>
# 1 2020-03-11 12:00:00 1    
# 2 2020-03-16 12:00:00 2    
# 3 2020-03-21 12:00:00 3    
# 4 2020-03-26 12:00:00 4