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
46
votes
1 answer

How might I get detailed database error messages from dplyr::tbl?

I'm using R to plot some data I pull out of a database (the Stack Exchange data dump, to be specific): dplyr::tbl(serverfault, dbplyr::sql(" select year(p.CreationDate) year, avg(p.AnswerCount*1.0) answers_per_question, …
Jon 'links in bio' Ericson
  • 20,880
  • 12
  • 98
  • 148
14
votes
4 answers

collect only if query returns less than n_max rows

Occasionally when connecting to my Oracle database through ROracle and dbplyr I will run a dplyr::collect operation that fetches more data than expected and than R can handle. This can make R crash and is often a sign I should have filtered or…
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
13
votes
3 answers

How to join tables from different SQL databases using R and dplyr?

I'm using dplyr (0.7.0), dbplyr (1.0.0), DBI 0.6-1, and odbc (1.0.1.9000). I would like to do something like the following: db1 <- DBI::dbConnect( odbc::odbc(), Driver = "SQL Server", Server = "MyServer", Database = "DB1" ) db2 <-…
stat_student
  • 787
  • 10
  • 17
12
votes
2 answers

Create the SQL query "SELECT * FROM myTable LIMIT 10" using dplyr

Suppose I have a connection to an external database called con. I would like to use dplyr to reproduce this query SELECT var1, var2, var3 from myTable LIMIT 10 I have tried qry <- tbl(con, "myTable") %>% select(var1) %>% …
Adam Black
  • 337
  • 3
  • 13
12
votes
0 answers

dbplyr - Error: x and y don't share the same src. Set copy = TRUE to copy y into x's source (this may be time consuming)

Normally we do not find any trouble in using the below connection method and run queries from redshift require("RPostgreSQL") drv <- dbDriver("PostgreSQL") conn <- dbConnect(drv, dbname = "redshiftdb", host = "XX.XX.XX.XX", port = "1234", user =…
ARIMITRA MAITI
  • 303
  • 3
  • 4
  • 14
11
votes
3 answers

Non-equi join in tidyverse

I was wondering whether someone knows if the dplyr extension packages (dbplyr and dtplyr) allow non-equi joins within the usual dplyr workflow? I rarely need data.table, but fast non-equi joins are the only moments where I always need to setDT, then…
b_surial
  • 512
  • 4
  • 14
11
votes
1 answer

Avoiding warning message “There is a result object still in use” when using dbSendQuery to create table on database

Background: I use dbplyr and dplyr to extract data from a database, then I use the command dbSendQuery() to build my table. Issue: After the table is built, if I run another command I get the following warning: Warning messages: 1. In…
i_love_chocolate
  • 414
  • 5
  • 16
11
votes
1 answer

How to escape Athena database.table using pool package?

I'm trying to connect to Amazon Athena via JDBC and pool: What has worked so far: library(RJDBC) library(DBI) library(pool) library(dplyr) library(dbplyr) drv <- RJDBC::JDBC('com.amazonaws.athena.jdbc.AthenaDriver',…
Googme
  • 914
  • 7
  • 27
11
votes
1 answer

Mutate variables in database tables directly using dplyr

Here is mtcars data in the MonetDBLite database file. library(MonetDBLite) library(tidyverse) library(DBI) dbdir <- getwd() con <- dbConnect(MonetDBLite::MonetDBLite(), dbdir) dbWriteTable(conn = con, name = "mtcars_1", value = mtcars) data_mt <-…
Geet
  • 2,515
  • 2
  • 19
  • 42
10
votes
2 answers

How to pipe SQL into R's dplyr?

I can use the following code in R to select distinct rows in any generic SQL database. I'd use dplyr::distinct() but it's not supported in SQL syntax. Anyways, this does indeed work: dbGetQuery(database_name, "SELECT t.* FROM…
Display name
  • 4,153
  • 5
  • 27
  • 75
9
votes
1 answer

dbplyr, dplyr, and functions with no SQL equivalents [eg `slice()`]

library(tidyverse) con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") copy_to(con, mtcars) mtcars2 <- tbl(con, "mtcars") I can create this mock SQL database above. And it's very cool that I can perform standard dplyr functions on this…
Display name
  • 4,153
  • 5
  • 27
  • 75
9
votes
1 answer

Update a table using subquery in SQLite

I want to add a column to my table using ALTER TABLE and UPDATE statements not to recreate the full table. When using a subquery in my UPDATE statement I don't get the output I expect. build reproducible…
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
9
votes
1 answer

Does sql_variant in dbplyr work as it should?

Let's take a look at the example in ?sql_variant: We define a new translator function for aggregated functions, expanded from the default one: postgres_agg <- sql_translator(.parent = base_agg, cor = sql_prefix("corr"), cov =…
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
9
votes
1 answer

How to use dplyr tbl on a SQL Server non-standard schema table

My question is how can I use dplyr functions, such as tbl, on SQL Server tables that do not use the default "dbo" schema? For more context, I am trying to apply the R database example given here to my own tables: https://db.rstudio.com/ (scroll…
meenaparam
  • 1,949
  • 2
  • 17
  • 29
8
votes
1 answer

dbplyr mutate character to date format in temp table

I have extracted data to a temporary table in SQL Server using DBI::dbGetQuery. Even though, in the real query (not the play query below), I select convert(date, date_value) as date_value, the dates are still stored as character. I then try to…
user1420372
  • 2,077
  • 3
  • 25
  • 42
1
2 3
…
25 26