R dbplyr SQL date filter issue

Question

I've connected to a SQL Server database with the code shown here, and then I try to run a query to collect data filtered on a date, which is held as an integer in the table in YYYYMMDD format

con <- DBI::dbConnect(odbc::odbc(), driver = "SQL Server", server = "***")
fact_transaction_line <- tbl(con,in_schema('***', '***'))

data <- fact_transaction_line %>% 
  filter(key_date_trade == 20200618)

This stores as a query, but fails when I use glimpse to look at the data, with the below error

"dbplyr_031"
WHERE ("key_date_trade" = 20200618.0)'

Why isn't this working, is there a better way for me to format the query to get this data?

have you tried the collect function? It will collect the results of your query you can also use show_query to see what is happening behind the scenes — Bruno

Simon.S.A. Simon.S.A. · Accepted Answer · 2020-06-21T21:38:13

Both fact_transaction_line and data in your example code are remote tables. One important consequence of this is that you are limited to interacting with them to certain dplyr commands. glimpse may not be a command that is supported for remote tables.

What you can do instead (including @Bruno's suggestions):

Use head to view the top few rows of your remote data.
If you are receiving errors, try show_query(data) to see the underlying SQL query for the remote table. Check that this query is correct.
Check the size of the remote table with remote_table%>% ungroup() %>% summarise(num = n()). If the remote table is small enough to fit into your local R memory, then local_table = collect(remote_table) will copy the table into R memory.
Combine options 1 & 3: local_table = data %>% head(100) %>% collect() will load the first 100 rows of your remote table into R. Then you can glimpse(local_table).

R dbplyr SQL date filter issue

1 Answers