Questions tagged [dbplyr]
dbplyr: A 'dplyr' Back End for Databases
dbplyr
406
questions
0
votes
3
answers
72
views
How to retrieve Common Table Expressions (CTEs) as a R list?
Context
I am connected to a PostgreSQL database and I use dplyr + dbplyr to query it. One of the query is quite long and complex and uses multiple CTEs.
Problem
I need to use these CTEs to make some ...
- 2,927
1
vote
2
answers
27
views
Using time zones with as.Date in dbplyr?
I'm connecting to Postgres 15.1 in R 4.3.0 using DBI 1.2.2 and dbplyr 2.3.2.
Here is a statement that works:
> my_table %>% mutate(date = as.Date(created_at)) %>% head %>% pull(date)
[1] &...
- 21k
0
votes
1
answer
350
views
saving and reading a duckdb database in R
I'm learning more about duckdb and exploring the various ways to "save" a database. I understand I should be able to save a duckdb database however when I try to save it, I get an error.
...
- 967
0
votes
2
answers
51
views
Generating filter conditions for dbplyr from dataframe
I have a table that I query in a remote database that I want to create a subset of. This subset is defined by a local dataframe that contains all valid ranges for a column in that data and all valid ...
1
vote
2
answers
52
views
Best practice for including remote data sources in an R package?
When authoring an R package, it is often good practice to bake in some datasets, usually for educational/tutorial purposes. There's a chapter about this in Hadley Wickham's book about R packages: ...
- 185
0
votes
1
answer
54
views
Write custom lazy evaluation function like dbplyr to get SQL [closed]
How can I write the logic to this function which should be able to do 2 things.
get_data <- function(database, table=NULL, query=NULL){
rlang::check_required(
x = database
)
if(is....
- 79
4
votes
0
answers
195
views
R arrow query is extremely slow the first time, but it is fast thereafter
I'm going through this tutorial of how to use arrow instead of regular dplyr. The second and subsequent times I run queries on this dataset, it's very fast, but the first one is incredibly slow (times ...
- 48.6k
1
vote
1
answer
182
views
Issue interacting via dbplyr with remote SQL tables probably since odbc or DBI or dbplyr package update
I used to have a R code working fine a few months back, which was connecting remotely to a SQL DB and interacting with it via dbplyr package.
Recently, I had to reinstall R/RStudio with all packages. ...
- 785
0
votes
1
answer
53
views
Use dplyr to query a temporal table at a point in time
In Microsoft SQL Server you can define system times for tables and query them like this:
select top 10 * from table FOR SYSTEM_TIME AS OF '2024-01-01'
I would like to use R and dplyr to query the ...
- 1,491
0
votes
1
answer
80
views
How do I convert a "tbl_df" "tbl" "data.frame" object to a "tbl_Redshift" "tbl_dbi" "tbl_sql" "tbl_lazy" "tbl" object for uploading?
I am very new to using servers for data analysis, and I am completely baffled by what a "tbl_df""tbl""data.frame" object, and "tbl_Redshift" "tbl_dbi" ...
- 31
0
votes
0
answers
34
views
Function `tbl()` does not work with hive table under ODBC connection
When I try to connect to a Hive table using:
con <- DBI::dbConnect(odbc::odbc(), 'MyDataBase')
db_tbl <- dplyr::tbl(con, dbplyr::in_schema('my_schema','my_table'))
I get the following error:
...
- 1,101
1
vote
1
answer
58
views
How to retain the matching column in dbplyr:::left_join.tbl_lazy from y?
In pure SQL I could do something like this:
SELECT
A.id,
CASE WHEN B.id IS NOT NULL THEN NULL ELSE A.load END AS load
FROM
A
LEFT JOIN
B ON A.id = B.id;
How would I translate that ...
- 19.1k
0
votes
2
answers
63
views
Is there a way to add a LIKE in a dbplyr inner_join SQL translation?
This is a follow-up question to this one: How to join dataframes using a prefix as a match?
I have those two tables on a SQL server side:
data <- memdb_frame(
x = c("ANOTHER", "...
- 2,021
0
votes
2
answers
53
views
How to filter on a date with dbplyr and ROracle?
Setup
I am using tidyverse and ROracle.
I have established a connection conn:
> conn
## User name:
## Connect string: IPIAMPR2.WORLD
## Server version: 19.0.0.0.0
## ...
- 2,021
0
votes
0
answers
49
views
Why is my query returning varying results, and how do I go about troubleshooting this issue?
When I run a simple count(*) using dbplyr, I get anywhere from 40k to 70k at a result:
> tbl(con,sql("select count(*) from app_public.members"))
# Source: SQL [1 x 1]
# Database: ...
- 372