417 questions
0
votes
1
answer
79
views
Write multiple tables to Oracle database in a single transaction (via ROracle)
Is it possible to write multiple R dataframes to an Oracle database in a single transaction via ROracle/DBI and dbWriteTable ?
dbWriteTable commits the table in full, so transaction management via ...
- 293
3
votes
1
answer
98
views
Creating custom dbplyr compatible function in SQL
I'm working with dbplyr and DuckDB to process very large Parquet files using limited system resources. To make my workflow more efficient, I want to create a custom function that can be seamlessly ...
- 31
0
votes
1
answer
90
views
bigrquery & DBI Error: Syntax error: Unexpected string literal 'table_name'
There appears to be a change or error with the bigrquery package. To connect to bigquery using DBI and dplyr you previously needed to do the following, which I got from https://github.com/r-dbi/...
- 1,271
0
votes
2
answers
70
views
How to translate R code to SQL using show_query
I want to create a VIEW in my PostgreSQL database. Therefore I try to translate some R code to PostgreSQL code, as I am not firm in the latter one. To translate the code I try to use show_query() from ...
- 11
2
votes
1
answer
104
views
dbplyr cyclic namespace dependency detected: does it unload correctly?
I am unable to unload the tidyverse namespaces after using dbplyr.
Did I find a bug in dbplyr? Does it unload correctly?
library(tidyverse)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:&...
- 5,266
0
votes
1
answer
338
views
bigrquery + dplyr error when using functions and sourcing from separate R script
I'm writing a set of common functions to QC tables in a BigQuery database using the dplyr interface, which I'm source()-ing to a series of table-specific RMD files for documentation. I'm not sure how ...
0
votes
1
answer
67
views
R Shiny app connected to BigQuery cannot filter by dates
I'm working on a R Shiny app that used to load the data from a CSV file and display it on a plot. The user had the ability to do some filtering via dropdowns and dateRangeInput selectors.
My goal is ...
- 105
0
votes
0
answers
45
views
How can i update and insert new values in RSQlite?
I have a SQLite database which i want to fill using the RSQlite Interface. Some rows need to be updated and some rows need to be added. So using dbWriteTable() is neither sufficient with the option ...
- 195
0
votes
1
answer
66
views
Join column from a table in SQL Server without write permissions to a data.frame in R using dbplyr
I have a data.frame in R that contains pairwise combinations of genetic sequence IDs, grouped by cluster name. The list of pairs has been deduplicated and there are no self-comparisons.
I am trying to ...
- 1,103
0
votes
1
answer
49
views
Object 'sql_query_wrap.Oracle' not found
With dbplyr 2.1.1 I was using the next code
sql_translate_env.JDBCConnection <<- dbplyr:::sql_translation.Oracle
sql_select.JDBCConnection <<- dbplyr:::sql_query_select.Oracle
sql_subquery....
3
votes
1
answer
69
views
base::cumsum() not working anymore with dplyr::mutate_at() in R Sparklyr
Issue
I have to do a cumulative sum over several columns on a grouped and ordered (big) spark dataframe.
I used to do it with base::cumsum() and dplyr::mutate_at() and it worked fine. It does not work ...
- 163
0
votes
1
answer
121
views
R DBI with ODBC to cast datatype for additional database
I'm using below DBI connection with ODBC driver to connect to a Trino database
DBI::dbConnect(
drv = odbc::odbc(),
'trino'
)
It failed to translate as.character().
m_tbl %>% mutate(col1 = as....
- 73
0
votes
1
answer
86
views
R dbplyr percentages not calculated correctly from live connection to Microsoft SQL Server [duplicate]
I have a Microsoft SQL Server database which I have connected to with an ODBC link. There are three columns of interest in the table I am querying: serovar, reportyear and hospitalised.
Each row ...
- 1,103
0
votes
4
answers
142
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,987
1
vote
2
answers
68
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] &...
- 21.7k