I'm currently working on a bioinformatics project using R, and I'm encountering an error when trying to use the biomaRt package. After installing the package and loading it into R, I tried to selec...

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...

I am using dbplyr to query an MSSQL database, and frequently round dates to the first of the month using mutate(YM = DATEFROMPARTS(YEAR(Date), MONTH(Date), 1)). I would like to be able to create an...

I would like to calculate the Jaro-Winkler string distance in a database. If I bring the data into R (with collect) I can easily use the stringdist function from the stringdist package. But my dat...

This question arises because I wish to make a function for my convenience: as.numeric_psql <- function(x) { return(as.numeric(as.integer(x))) } to convert boolean values in a remote postgre...

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 cras...

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(dr...

I'd like to explore a Google Analytics 360 data with bigrquery using dplyr syntax (rather than SQL), if possible. The gist is that I want to understand user journeys—I'm interested in finding the m...

I hope that what I am about to write makes some sense. If you look at How to deal with a 50GB large csv file in r language? it is explained how to query à la SQL, a csv file from R. In my case, I h...

I have a question similar to this Stackoverflow post. How can I create a persistent table from a SQL query in a database (I use a DB2 database)? My goal is to use a table from one schema and to per...

I am trying to use str_detect, str_replace, str_replace_all methods in dbplyr with oracle as the beckend database but cant seem to access this methods. here is the error: db_tbl %>% mutate(COM...

I would like that but with the conditions in the summarise_at() Edit #1: I've added the word dynamically in the title: When I use vars(c()) in the summarise_at() it's for fast and clear examples, b...

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(da...

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 have been asked to make a bunch of charts for a large organization and have been given access to their Power BI dashboard. I want to go around Power BI's interface so I can make the charts in R. ...

left_join works as expected with NA values on tibbles or data frames, but on tbl it seems it does not match NAs, even with the option na_matches = "na". R version and package versions > sessio...

I currently have access to an Apache Hive database via the beeline CLI. We are still negotiating with IT to get R on the server. Until that time, I would like to (ab)use the R dbplyr package to gen...

Given 2 remote tables (simulated with tbl_lazy for this example) library("dplyr") library("dbplyr") t1 <- tbl_lazy(df = iris, src = dbplyr::simulate_mysql()) t2 <- tbl_lazy(df = mtcars, src...

I am trying to copy a local dataframe from R to my db2 database. I have permissions to write to the table and I have verified the connection is working. I am using: copy_to(connection, data.frame...

I'm trying to use dplyr to query a SQL database, matching on provided arguments. id <- tbl(conn, "My_Table") %>% filter(Elem1 == elem1 & Elem2 == elem2 & Elem3 == elem3) %>% se...

Say I have a remote tbl open using dbplyr, and I want to use a SQL query on it (maybe because there's not dbplyr translation for what I want to do), how do I give it such that it returns a remote t...

I normally use filter with grepl in dplyr, but when using dbplyr. I get an error that grepl is not a recognized function. My guess is that it can't translate to SQL server. What is a way arou...

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 foll...

I have reading rights to some "Views" (tables) in Microsoft SQL Server Management Studio (SSMS). I connect, make my query and export a files as csv and then read it in R. Now I would like to make m...

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.AnswerCo...