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
0 answers

select a table from a database in R

I am using dbplyr to select a table from a remote database using Rstudio. I connected with Spark in the server using livy. It shows me the databases I have but when I try to access one of the tables in one of the schemas, it…
Fisseha Berhane
  • 2,533
  • 4
  • 30
  • 48
0
votes
1 answer

Difference in month with TIMESTAMPDIFF mysql function in dbplyr R

I am trying to calculate the difference in months between two dates in R using dbplyr package, I want to send the sql query to calculate it using "timestampdiff" native function in mysql but I'm getting an…
Alvaro Pabon
  • 43
  • 6
0
votes
1 answer

Add a variable to a table in MonetDBLite

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 =…
Geet
  • 2,515
  • 2
  • 19
  • 42
0
votes
2 answers

Complicated filter statements in dbplyr using lists of lists

In SQL you can make a statement like this: SELECT * FROM table WHERE (var1, var2, var3, var4) IN (("var1-1", "var2-1", "var3-1", "var4-1"), ("var1-2", "var2-2", "var3-2", "var4-2")) This means to grab all rows…
Jon M
  • 164
  • 1
  • 12
0
votes
1 answer

mutate on grouped database column using 'first()' without collect()'ing first

Without using the `collect()' function I'm getting an error with my code joined_table %>% filter(message.y == 'CURR') %>% filter(parameter.y == 'Volt') %>% select(flight, timestamp.y, value) %>% #collect() %>% group_by(flight) %>% …
Karl Uibo
  • 353
  • 4
  • 10
0
votes
1 answer

How to translate SQL inner join with like clause to dplyr workflow?

I have three mySQL database tables 1-3. My goal is to perform some left and inner join operations on these tables using R dplyr. I am attempting to translate my original SQL code to dplyr workflow using dbplyr in R. Part of my original SQL query…
SinghD
  • 55
  • 1
  • 5
0
votes
1 answer

Encoding issues R and MySQL

Seems like I opened another chapter of the encoding from hell book. I seek help with a problem I encounter when pulling and writing data from\to a MySQL data base with R. After a good amount of time I was able to write my data back but still don't…
CER
  • 854
  • 10
  • 22
0
votes
1 answer

Can't get data with dbplyr from shiny-server

I'm trying to get data from AWS SQL Server. This code works fine from local PC, but it didn't work from shiny-server (ubuntu). library(dbplyr) library(dplyr) library(DBI) con <- dbConnect(odbc::odbc(), driver = "FreeTDS", …
jyjek
  • 2,627
  • 11
  • 23
0
votes
1 answer

Preparing data for Plotly in R

I am able to create datatables, pivots, maps etc. but, cannot for the life of me create a dataset usable in Plotly for R. In this example I keep getting and error in the last element ending with 'object 'prem_council' not found My code is: ####…
civarchive
  • 67
  • 11
0
votes
4 answers

Calculating median value for months for several years

I have recently started my adventure with R and trying to solve the following problem. I have data.frame including arrivals and departures for particular months of the year. I have to find what is the median for every month through all this years.…
krakowi
  • 583
  • 5
  • 17
0
votes
1 answer

How do i use the spark-sql "range between" clause for a window operation with sparklyr

Context: I have a large table with logon times. I want to calculate a rolling count of logons within a specified period (e.g. 3600 sec). In SQL/HQL i would specify this as: SELECT id, logon_time, COUNT(*) OVER( PARTITION BY id ORDER BY logon_time…
rookie error
  • 165
  • 1
  • 7
0
votes
0 answers

How to write a table reference working with rjdbc?

I am working with really big data with R. My data is on Hive and I am using rjdbc. I am thinking of using a reference table on R because its impossible to load the table onto R even just using 10% sample. I am using the tbl function from…
Ninjia123
  • 47
  • 6
-1
votes
1 answer

How to manipulate/clean data located in a MySQL database using base R commands?

I've connected to a MySQL database using the RMariaDB package, and, thanks to the dbplyr package, am able to adjust the data using dplyr commands directly from R studio. However, there are some basic things I want to do that require base R functions…
dd_data
  • 93
  • 5
-1
votes
1 answer

dbplyr select not duplicate columns

I am working with the R programming language. I have the following table that is located on a server: age=18:29 height=c(76.1,77,78.1,78.2,78.8,79.7,79.9,81.1,81.2,81.8,82.8,83.5) gender=c("M","F","M","M","F","F","M","M","F","M","F","M") testframe =…
stats_noob
  • 5,401
  • 4
  • 27
  • 83
-1
votes
2 answers

Similliar aggregate function to unique/distinct in R Postgres Backend

How does it work to aggregate a variable in a postgres db backend table to its unique value. For example i have the following table: library(tidyverse) library(dbplyr) dbplyr::memdb_frame(a=c(2,2,2), b=c(2,3,4)) %>% summarise(aggregatedSum =…
werN
  • 109
  • 7
1 2 3
…
25
26