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

How to ignore delimiters inside quoted strings when importing a csv file with RSQLite?

I want to import a csv file that has a similar structure with the example below: var1;var2;var3 "a";1;"Some text" "b";0;"More text" "c;0;"Delimiter in ; middle of the text" Traditional parsers such as the one used by data.table::fread deal with…
Nícolas Pinto
  • 363
  • 2
  • 14
0
votes
1 answer

How do I substitute values from the R session into SQL bind variable placeholders?

I want to re-use raw SQL within an R script. However, SQL has variable binding that lets us parameterize the query. Is there a quick way to directly substitute values from the R session into bind variable placeholders when using SQL within…
Kalin
  • 1,691
  • 2
  • 16
  • 22
0
votes
1 answer

Apply a ranking window function in dbplyr backend

I want to seamlessly identify new orders (acquisitions) and returns in my transactional database table. This sounds like the perfect job for a window function; I would like to perform this operation in dbplyr. My current process is to: Create a…
chopin_is_the_best
  • 1,951
  • 2
  • 23
  • 39
0
votes
1 answer

Mutate new character col based on another character col in oracle table using R dbplyr

I have a oracle table with a col COMPLAINT_REASON complaints_tbl %>% head() %>% select(COMPLAINT_REASON) # Source: lazy query [?? x 1] # Database: Oracle 12.01.0020[user@user_db/] COMPLAINT_REASON 1 Payment…
Shery
  • 1,808
  • 5
  • 27
  • 51
0
votes
1 answer

R: MySQL character encoding on Windows

I have problem with character encoding when pulling data from MySQL database that seems to be specific to Windows computer. The easy solution that's working on the Linux computer: # establish connection db = src_mysql(user = "user", password =…
ayasugihada
  • 329
  • 1
  • 2
  • 13
0
votes
1 answer

Changing imported R function globally

I want to globally add a parameter to a function after import. So in future function calls the function should be always called with the set parameter. In this case, I want to add the function parameter in_schema("abc") to the function tbl from…
Krisselack
  • 503
  • 3
  • 16
0
votes
1 answer

Getting a table from a schema from sql with a backslash

So I have to get a table which is in a schema in a database. The schema name contains a backslash, e.g david\b. I have my connection con so I use dbplyr tabel <- dplyr::tbl(con, in_schema("david\\b", "some_tabel")) But this does not work.
xhr489
  • 1,957
  • 13
  • 39
0
votes
1 answer

How to pass data.frame into SQL "IN" condition using R?

I am reading list of values from CSV file in R, and trying to pass the values into IN condition of SQL(dbGetQuery). Can some one help me out with…
rahul voruganti
  • 1
  • 1
0
votes
1 answer

multiple criteria filtering join using dplyr

I'm trying to accomplish the operation described below by creating a df named event_f. I want from the detail df as filtering criteria, all event_id that have type_id == 6 excluding those with a combination of 6 and 3 or 6 and 7. Note that there…
MattnDo
  • 444
  • 1
  • 5
  • 17
0
votes
1 answer

Why is my DBI connector doing a dummy query with predicate "where 0 = 1"?

I have some R test code that I use to connect to a MonetDB instance. I notice that for each connection that I create through tbl(conn, "some_table") I somehow always end up with a 'primer' query of the form SELECT * FROM "some_table" AS…
Yunus King
  • 1,141
  • 1
  • 11
  • 23
0
votes
1 answer

R database table pull encoding issue

I have a MySQL database containing a table of names. Once I pull them into R I run into some encoding problems which I don't understand but seem to be all related to the way R interpreted the file. con <- dbConnect(MySQL(), host =…
CER
  • 854
  • 10
  • 22
0
votes
0 answers

Connecting to a database within a Redshift database via RStudio using `r-dbi` and `dbplyr`

Am relatively new to both databases and Redshift, and I'd like to use the Connections within R Studio to be able to query a set of databases to extract subsets of data. Specific Question: after connecting to the correct database in Redshift, how…
daRknight
  • 253
  • 3
  • 17
0
votes
0 answers

How to use SQL to define a dbplyr table

I want to use dbplyr to define a hand-written SQL query rather than a table. How can I do that? For example, I would like to do something like this: my_custom_sql_tbl <- tbl( con, "SELECT * FROM MY_DATABASE_TABLE WHERE FOO IS NULL"…
Kalin
  • 1,691
  • 2
  • 16
  • 22
0
votes
1 answer

Cast timestamps to timestamptz using mutate_if

I have a database with timestamps (no timezone) that I would like to cast to timestamptz (with timezone) prior to using collect(). So far, I've tried these: db_tbl %>% mutate_if(lubridate::is.timepoint, funs(CAST)) But, I can't figure out how to…
Nick DiQuattro
  • 729
  • 4
  • 7
0
votes
0 answers

percentile_cont in bigrquery

I would like to get the 20th percentile of a column in big query using dplyr syntax in bigrquery, but I keep getting the following errors. Here is a reproducible example: library(bigrquery) library(dplyr) library(DBI) billing <-…
alex56
  • 11
  • 2
1 2 3
…
25 26