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
7
votes
2 answers

R and dplyr: How can I use compute() to create a persistent table from SQL query in a different schema than the source schema?

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 permanently create a more or less modified table in…
MatSchu
  • 383
  • 1
  • 13
7
votes
2 answers

How to select a nested field with bigrquery using dplyr syntax?

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 most common sequences of pages at the user level…
Khashir
  • 341
  • 3
  • 20
7
votes
1 answer

cant access string methods in dbplyr

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(COMMENTS_NEW = str_detect(COMMENTS,"[^[:alnum:]///' ]", ""))…
Shery
  • 1,808
  • 5
  • 27
  • 51
7
votes
2 answers

How to create custom SQL functions with R code in dbplyr?

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 R function that will simplify this for me, e.g.…
Aaron Cooley
  • 438
  • 3
  • 8
7
votes
1 answer

Connecting to Microsoft SQL Server with R (view is in a database in Microsoft SQL Server Management Studio (SSMS)

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 my queries inside R. I have spendt some hours…
xhr489
  • 1,957
  • 13
  • 39
7
votes
1 answer

R: Best Practices - dplyr and odbc multi table actions (retrieved from SQL)

Say you have your tables stores in an SQL server DB, and you want to perform multi table actions, i.e. join several tables from that same database. Following code can interact and receive data from SQL server: library(dplyr) library(odbc) con <-…
Mathias Pagh
  • 111
  • 6
7
votes
3 answers

How to filter by a string containing variables in dbplyr

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 around this with dbplyr Here is a reproducible…
Alex
  • 2,603
  • 4
  • 40
  • 73
7
votes
1 answer

dbplyr::in_schema case sensitive

The function dbplyr::in_schema() can not connect to tables with uppercase letters. When I create a table in PostgreSQL. CREATE TABLE public."OCLOC" ( cod_ocloc double precision NOT NULL, lab_ocloc character varying(255), CONSTRAINT pk_ocloc…
Diego
  • 2,196
  • 1
  • 21
  • 26
7
votes
1 answer

Adding column to sqlite database

I am trying to add a vector which I generated in R to a sqlite table as a new column. For this I wanted to use dplyr (I installed the most recent dev. version along with the dbplyr package according to this post here). What I tried:…
Alex
  • 4,925
  • 2
  • 32
  • 48
6
votes
1 answer

connecting to a database in R using an Office Data Connection (.odc) file from Power BI

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. Power BI offers an 'analyse in excel' option which…
gfgm
  • 3,627
  • 14
  • 34
6
votes
2 answers

Function that composes functions with existing sql translations in dbplyr

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 postgres table into numeric. The step to convert to integer is…
Alex
  • 15,186
  • 15
  • 73
  • 127
6
votes
1 answer

left_join for tbl: na_matches not working

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 > sessionInfo() R version 3.6.1 (2019-07-05) Platform:…
Habert
  • 347
  • 2
  • 10
6
votes
1 answer

Generate CROSS JOIN queries with dbplyr

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 = dbplyr::simulate_mysql()) How can I perform an actual*…
asachet
  • 6,620
  • 2
  • 30
  • 74
6
votes
3 answers

How to use custom SQL function in dbplyr?

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 data is very large and I'd like to filter on…
jfeigenbaum
  • 403
  • 4
  • 13
6
votes
1 answer

how to generate SQL from dbplyr without a database connection?

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 generate SQL queries on another machine, copy them…
steveo'america
  • 206
  • 1
  • 7
1
2
3
…
25 26