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 use DBI::dbConnect() to read and write tables from multiple databases

I have a Netezza SQL server I connect to using DBI::dbConnect. The server has multiple databases we will name db1 and db2. I would like to use dbplyr as much as possible and skip having to write SQL code in RODBC::sqlQuery(), but I am not sure…
Zoltan
  • 760
  • 4
  • 15
0
votes
1 answer

Is there a way to append tables within a do.call() when using dplyr verbs within a database?

Say there's a list of dataframes that is generated following a lapply call on some vector and the aim is to append the list of dataframes to one another to form a single dataframe. In R, one approach is do.call(rbind, data.ls), where data.ls is a…
Cyrus Mohammadian
  • 4,982
  • 6
  • 33
  • 62
0
votes
1 answer

Fill in missing values in dbplyr

I have data in a database that look like: ID month_year value 1 01/06/2014 10 1 01/07/2014 100 1 01/10/2014 25 I would like to fill in the missing months: ID month_year value 1 01/06/2014 10 1 01/07/2014 …
Liam
  • 159
  • 11
0
votes
1 answer

In R can you create an in-memory Teradata database?

dbplyr's reference page begins showing how to create an in-memory SQLite database and copy over a dataset: library(dplyr, warn.conflicts = FALSE) con1 <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") copy_to(con1, mtcars) It does this utizing…
Display name
  • 4,153
  • 5
  • 27
  • 75
0
votes
1 answer

How to deal with multiple database connections and select statements in R with tidyverse

I am using tidyverse to connect to multiple databases with the same data structure (clusters). Due to different database sources a union ist not possible without copy locally. I can do everything with long coding, but now I try to shorten the code…
Thomas Preuß
  • 3
  • 2
0
votes
2 answers

How do you filter with a Dataframe, list, vector etc. to a table in a database in R?

I have a large set of id-s which is in a csv file. How could I filter on a database table using only that one-columned table in the csv file? For example in the ODBC database we have: TABLE 1 +---------+------+ | ID | TYPE…
JayAr
  • 57
  • 7
0
votes
1 answer

R dbplyr SQL Error "expecting something between Database_Name and LIMIT"

Here's the connection we're working with: con <- DBI::dbConnect(odbc::odbc(), Driver = "[your driver's name]", Host = "[your server's path]", DBCName = "[IP Address or Alias]" …
Display name
  • 4,153
  • 5
  • 27
  • 75
0
votes
1 answer

Week of year using bigquery / bigrquery and dbplyr? (equivalent of lubridate::week)

I'm trying to use bigrquery and dbplyr to get the week of the year that a date corresponds to (i.e. the same as lubridate::week(), i.e. library(lubridate) library(dbplyr) library(bigrquery) week("2015-08-11") # [1] 32 but I am using bigrquery…
stevec
  • 41,291
  • 27
  • 223
  • 311
0
votes
2 answers

Error in max(.) : invalid 'type' (list) of argument when using dplyr dbplyr and bigrquery

I'm running some very simple dplyr code on a bigquery table using dbplyr transactions %>% select(date) %>% max(.) Produces the error Error in max(.) : invalid 'type' (list) of argument I have checked that The table I am querying queries as…
stevec
  • 41,291
  • 27
  • 223
  • 311
0
votes
0 answers

Convert nvarchar of a column with dbplyr?

Is it possible to replicate the following query in dbplyr: SELECT convert(nvarchar(4000), column_name) [column_name] FROM table_name The 'convert(nvarchar(4000)' bit is what I'm interested in here. I'd guess that this would be outside the scope of…
MaxL
  • 101
  • 1
  • 4
0
votes
1 answer

Optimisation of BigQuery generated from bigrquery and dbplyr?

dplyr functions are typically incredibly performant, having been optimised by the open source R community, with many going so far as to run c++ under the hood to make them much faster. Does the BigQuery code generated through bigrquery and dbplyr…
stevec
  • 41,291
  • 27
  • 223
  • 311
0
votes
1 answer

How to unittest PostGIS database queries with R

I've been playing around with database queries in R that are executed on a Postgres database with the PostGIS extension. This means I use some of the PostGIS functions that do not have an R equivalent. If it wasn't for that, I could probably just…
telegott
  • 196
  • 1
  • 10
0
votes
1 answer

dbplyr error when use select and where to filter table

I am trying to use dbplyr to pull data from SQL DB using the code below by select columns and filter rows. df <- tbl(con, in_schema("adm", "tablename")) %>% select(col1, col2, col3) %>% filter(col1 >= min_date) %>% collect() But I have the…
Gavin
  • 1,411
  • 5
  • 18
  • 31
0
votes
1 answer

How to Connect to SQL from R Studio

I use Microsoft SQL Server Management Studio on Windows 10 to connect to the following database and this is what the login screen looks like: Server Type: Database Engine Server Name: …
Display name
  • 4,153
  • 5
  • 27
  • 75
0
votes
1 answer

dplyr / bigrquery way of querying/binding multiple tables of the same schema in BigQuery?

Using the methods outlined in this readme doc, it's straight forward to query a single table, like so library(bigrquery) library(dplyr) natality <- tbl(con, "natality") natality %>% select(year, month, day, weight_pounds) %>% head(10) %>% …
stevec
  • 41,291
  • 27
  • 223
  • 311
1 2 3
…
25 26