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

Dbplyr: in_schema to use the same connection for two different databases

For reasons I am not going to explain here, I need to use the same connection object to read two databases in dbplyr. I found some online resources, but I am not getting this right. Please have a look at the reprex below. Can anyone tell me what I…
larry77
  • 1,309
  • 14
  • 29
0
votes
1 answer

join two remote tables by fields that are transformed tolower

How can I join two remote tables (postgres) by a field in each table (different names) that should first be transformed tolower() on the db? Here is a basic example from the docs. This example does not need tolower() to work, but how would you add…
Eric Green
  • 7,385
  • 11
  • 56
  • 102
0
votes
2 answers

How to write virtual BQ table back to BQ using R DBI and bigrquery?

I want to be able to Access a BQ table. This is class [1] "tbl_BigQueryConnection" "tbl_dbi" "tbl_sql" [4] "tbl_lazy" "tbl" ` Alter the table using dbplyr to create a new table. Again, has class [1]…
abalter
  • 9,663
  • 17
  • 90
  • 145
0
votes
1 answer

Select rows where array contains one of several values in bigquery (ideally with dbplyr)

I have a large set of tweets on bigquery and now want to filter those that contain at least one of a list of hashtags. The hashtags are saved in an array column (uploaded from a list column in R). How can I select rows that contain one of multiple…
Lukas Wallrich
  • 372
  • 2
  • 8
0
votes
1 answer

Can't query VIEW in BigQuery with dbplyr

In the following reprex, I create a BigQuery dataset, create a table with mtcars, create a view, and then try to query the view. I can query the table, but the view returns no data. library(DBI) library(dplyr, warn.conflicts =…
abalter
  • 9,663
  • 17
  • 90
  • 145
0
votes
1 answer

DBI::dbWriteTable error when writing to BigQuery -- trying to coerce string to int

In an AI notebook, I have the following: %%R tempdf %>% summary() %>% print() DBI::dbWriteTable( conn=clinvar_conn, name=table_name, value=tempdf, overwrite=T ) Giving CHROM POS ID …
abalter
  • 9,663
  • 17
  • 90
  • 145
0
votes
1 answer

Algorithm in R to smooth out a vector while retaining rank order

I need to write a function that can smooth a vector without losing the original rank order of the vector's values. What I've come up with is the following: #1 Sort all values of vector in ascending order #2 for the kth value in vector s_k in the…
Cyrus Mohammadian
  • 4,982
  • 6
  • 33
  • 62
0
votes
1 answer

Using ODBC::dbConnect and dplyr to connect to Sybase IQ database - table name not found

I can connect to my Sybase IQ 16 database using a connection string such as: myDB_conn <- dbConnect(odbc(), "MyDSN_Name") When I run this command the connections view shows a list of databases and corresponding tables/views. However when I try to…
TheGoat
  • 2,587
  • 3
  • 25
  • 58
0
votes
1 answer

How to left join on a dataframe based on dates on `dbplyr`

I am trying to join two data.frames based on a condition. Consider the following situation where I have df_a and df_b. library(tidyverse) # Dummy data A df_a <- tibble( id = c("a", "b", "c", "a"), text = c("hi","why", "bye","cry"), …
Vivek Katial
  • 543
  • 4
  • 17
0
votes
1 answer

How can I translate this SQL code to R script using dplyr?

I'm currently working on a project and I want to summarize a column from a joined table twice. SQL code is this: SELECT M.date,T.team_long_name AS Home_Team, M.home_team_goal, Te.team_long_name AS Away_Team, M.away_team_goal FROM Match AS M JOIN…
Dimitris Papantzikos
  • 23
  • 4
0
votes
1 answer

postgres match through two lists (list in list)

I'd like to know if there is a way to check if there is at least one string in list a which matches in list b. > select 1 IN (1,2); ?column? ---------- t (1 row) In the case above I'm only checking 1 value against a list. But if I try as below I…
Aureliano Guedes
  • 767
  • 6
  • 22
0
votes
1 answer

Setting overwrite == TRUE using memdb and dbplyr

The following shiny app works the first time you run it, but then errors if you change the species input because the table name already exists in memory. I was wondering how to set overwrite == TRUE given the code…
MayaGans
  • 1,815
  • 9
  • 30
0
votes
1 answer

bigrquery - Error: No matching signature for operator - for argument types: DATE, FLOAT64

When filter()ing by date, and using a function to provide that date, I see an expected error: library(tidyverse) library(bigrquery) table1 %>% filter(date > as.character(today() - 730)) %>% tally() # Error: No matching signature for…
stevec
  • 41,291
  • 27
  • 223
  • 311
0
votes
1 answer

Create Clickhouse DB and load lots of table with R

I'm trying to create a Clickhouse DB and copy to a table the values from many files (all them equal column order). But I'm not understanding how to deal with it. I'm using RClickhouse which I don't know if has any difference compared to…
Aureliano Guedes
  • 767
  • 6
  • 22
0
votes
1 answer

Using pl/sql trunc function with dbplyr in R

I am trying to use dbplyr and the trunc function from pl/sql, to mutate the date column to the start of the month. df %>% mutate(start_month = sql(trunc(date_column, 'month')) however this throws an error invalid identifier when executing the…
RandomQ's
  • 11
  • 1
1 2 3
…
25 26