Stack Overflow
Stack Overflow

Questions tagged [dbplyr]

dbplyr: A 'dplyr' Back End for Databases

A back end for databases that allows you to work with remote database tables as if they are in-memory data frames. Basic features works with any database that has a 'DBI' back end; more advanced features require 'SQL' translation to be provided by the package author.

377 questions
6
votes
2 answers

How do I "flush" data to my RSQLite disk database?

I'm creating a database using R package dbplyr, using RSQLite, but my database is zero-bytes in size on disk despite my writing (and reading back) a table. Here is my script: library("RSQLite") library("dbplyr") library("dplyr") data(mtcars) con…
Thomas Browne
  • 23,824
  • 32
  • 78
  • 121
5
votes
2 answers

How to escape characters in SQL code in an R Markdown chunk?

``` {r setup, include=FALSE} knitr::opts_chunk$set(echo = TRUE) library(tidyverse) library(odbc) library(DBI) library(dbplyr) ``` ```{sql, connection=con, output.var="df"} SELECT DB_Fruit.Pear, Store.Name, Cal.Year, Sales.Qty FROM DB_Fruit ``` #>…
Display name
  • 4,153
  • 5
  • 27
  • 75
5
votes
1 answer

Non-Latin characters show as question marks when using rodbc/odbc/dbplyr with SQL-Server

I'm using dbplyr to get data from SQL-Server into R, but Chinese, Japanese and other non-Latin characters are appearing as "?". I'm using a windows machine. I've read through the following threads: How does R handle Unicode / UTF-8? How to use…
MaxL
  • 101
  • 1
  • 4
5
votes
2 answers

Combining dbplyr and case_when in SQL Server

I am using dbplyr to write and run queries in SQL Server, and want to apply a conditioned mutate. This can be done using ifelse or using case_when. The query works when using ifelse but throws and exception when using case_when. The issue appears to…
Simon.S.A.
  • 6,240
  • 7
  • 22
  • 41
5
votes
1 answer

R: dbplyr: postgres: How to create an index on a table

A user has a large table (3+ billion rows). To speed up queries for the next few months, an index on the remote database must be created. Assuming there is a connection called conn - what is the best way to create an index and make it persist after…
userJT
  • 11,486
  • 20
  • 77
  • 88
5
votes
1 answer

Database calculations with dbplyr

I have very simple problem that produces error. Example will clear this one. library(odbc) library(DBI) library(dplyr) library(dbplyr) con <- dbConnect(odbc(), "myDSN") tbl_test <- tibble(ID = c("A", "A", "A", "B", "B", "B"), …
Hakki
  • 1,440
  • 12
  • 26
5
votes
1 answer

Sparklyr using case_when with variables

Sparklyr fails when using a case_when with external variables. Working Example: test <- copy_to(sc, tibble(column = c(1,2,3,4))) test %>% mutate(group = case_when( column %in% c(1,2) ~ 'group 1', column %in%…
rookie error
  • 165
  • 1
  • 7
5
votes
2 answers

dplyr Filter Database Table with Large Number of Matches

I am working with dplyr and the dbplyr package to interface with my database. I have a table with millions of records. I also have a list of values that correspond to the key in that same table I wish to filter. Normally I would do something like…
cdeterman
  • 19,630
  • 7
  • 76
  • 100
5
votes
1 answer

Error: The dbplyr package is required to communicate with database backends

I am new to R and learning R from sources. I am trying to use the dplyr package for connecting to the database. I am trying out the following tutorial, and getting this…
Vishal Prajapati
  • 51
  • 1
  • 3
4
votes
3 answers

How to use `last()` when mutating by group with {dbplyr}?

Consider the following remote table: library(dbplyr) library(dplyr, w = F) remote_data <- memdb_frame( grp = c(2, 2, 2, 1, 3, 1, 1), win = c("B", "C", "A", "B", "C", "A", "C"), id = c(1,3,5,7,2,4,6), ) I wish to group by grp, order by win and…
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
4
votes
1 answer

Using string matching like grepl in a dbplyr pipeline

dbplyr is very handy as it convert dplyr code into SQL. This works really well except when it doesn't. For example i am trying to subset rows by partially matching a string against values in a column. With exception of postgres, it appears as though…
boshek
  • 4,100
  • 1
  • 31
  • 55
4
votes
2 answers

R: Update a mysql table with data frame

I have a MariaDB and I want to update a table with a local R data frame. As an example, I have a table with these column names: id,foo,bar id is the primary key on the data base table. Is there a function with which I can easily update the remote…
MKR
  • 1,620
  • 7
  • 20
4
votes
2 answers

Use variable with regex in string::str_detect in dbplyr SQL query

I would like to filter a SQL database based whether a regular expression appears within any column. I would like to specify the regex as a variable; however it is read as a literal string. I am having trouble getting the regex in as a variable.…
maia-sh
  • 537
  • 4
  • 14
4
votes
2 answers

dbplyr: delete row from a table in database

What is the dbplyr verbs combination that is equivalent to DBI::dbSendQuery(con, "DELETE FROM WHERE "). What I want is not querying data from database, but removing data from and updating a table in database. I want to do it in a…
englealuze
  • 1,445
  • 12
  • 19
4
votes
2 answers

Dropping containing NA rows with dbplyr

here is how I ran some SQL queries by dbplyr library(tidyverse) library(dbplyr) library(DBI) library(RPostgres) library(bit64) library(tidyr) drv <- dbDriver('Postgres') con <- dbConnect(drv,dbname='mydb',port=5432,user='postgres') table1 <-…
Samet Sökel
  • 2,515
  • 6
  • 21
1 2
3
…
25 26