R console stuck for several minutes when api="arrow"

[thiagoveloso]

Also reported on bigrquery since I am not sure where the problem might be: r-dbi/bigrquery#647

I am using bigrquery to retrieve a table with 23 cols and over 3.3MM rows that includes all types of data (character, numeric, date).

api="json" takes a bit to run but works OK:

> system.time(bq_results_json <- bq_table_download(bq_submit, bigint = "integer64", api = "json"))
Downloading first chunk of data.
Received 28,209 rows in the first chunk.
Downloading the remaining 3,340,910 rows in 158 chunks of (up to) 21,156 rows.
   user  system elapsed                                        

 53.490   7.913  66.507 

api="arrow" finishes faster:

> system.time(bq_results_arrw <- bq_table_download(bq_submit, bigint = "integer64", api = "arrow"))
Streamed 3369119 rows in 2646 messages.                                                                                                                                                     
   user  system elapsed 
  9.575   4.333  34.643

but it renders the R console useless for several minutes. In my last attempt it stayed frozen like this for nearly 10 minutes:

Any hints on what can be wrong?

[meztez]

@thiagoveloso can you provide the execution environment and a way to reproduce the behavior?

Is it the same behavior when not wrapped within system.time?

xfun::session_info('bigrquerystorage')

[thiagoveloso]

@thiagoveloso can you provide the execution environment and a way to reproduce the behavior?

Is it the same behavior when not wrapped within system.time?

xfun::session_info('bigrquerystorage')

@meztez thanks for the quick reply! Yes, the pure command without system.time presents the same behaviour.

I am unable to share my working data since it contains private information but I've found a public dataset with a similar-ish size/structure that can be queried with:

### WARNING: this query will charge your project 2.55 GB and will create a ~31MM-row table
query <- "SELECT passenger_count, pickup_datetime, trip_distance, payment_type, tip_amount, fare_amount, total_amount FROM bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2021"
bq_submit <- bq_project_query(project, query)
bq_results_json <- bq_table_download(bq_submit, bigint = "integer64", api = "json")
bq_results_arrw <- bq_table_download(bq_submit, bigint = "integer64", api = "arrow")

FWIW this is what my environment looks like:

> xfun::session_info('bigrquerystorage')
R version 4.5.1 (2025-06-13)
Platform: x86_64-pc-linux-gnu
Running under: Ubuntu 20.04.6 LTS, RStudio 2025.5.1.513

Locale:
  LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
  LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

Package version:
  assertthat_0.2.1       bigrquerystorage_1.2.2 bit_4.6.0              bit64_4.6.0.1          cli_3.6.5              glue_1.8.0             graphics_4.5.1        
  grDevices_4.5.1        lifecycle_1.0.4        magrittr_2.0.3         methods_4.5.1          nanoarrow_0.7.0        pillar_1.11.0          pkgconfig_2.0.3       
  Rcpp_1.1.0             rlang_1.1.6            stats_4.5.1            tibble_3.3.0           tools_4.5.1            utf8_1.2.6             utils_4.5.1           
  vctrs_0.6.5

Does that help?

[meztez]

interesting, I don't think the official package builds on Ubuntu 20.04. Did you build manually?

[thiagoveloso]

interesting, I don't think the official package builds on Ubuntu 20.04. Did you build manually?

yep, I am the one who managed to manually build it: #71 (comment)

[meztez]

I just saw that.

I would consider using bqs_set_log_verbosity(0) or going with 3.

Otherwise this could be an upstream bug in gRPC and dependencies that have since been resolved.

If the behavior is not present using a more recent grpc version, I won't investigate further.

[thiagoveloso]

I just saw that.

I would consider using bqs_set_log_verbosity(0) or going with 3.

Otherwise this could be an upstream bug in gRPC and dependencies that have since been resolved.

If the behavior is not present using a more recent grpc version, I won't investigate further.

@meztez thanks for the hint. I managed to test this behaviour on my Windows laptop as well:

> xfun::session_info('bigrquerystorage')
R version 4.5.1 (2025-06-13 ucrt)
Platform: x86_64-w64-mingw32/x64
Running under: Windows 11 x64 (build 22631), RStudio 2025.5.1.513

Locale:
  LC_COLLATE=English_United States.utf8  LC_CTYPE=English_United States.utf8    LC_MONETARY=English_United States.utf8 LC_NUMERIC=C                          
  LC_TIME=English_United States.utf8    

Package version:
  assertthat_0.2.1       bigrquerystorage_1.2.2 bit_4.6.0              bit64_4.6.0.1          cli_3.6.5              glue_1.8.0             graphics_4.5.1        
  grDevices_4.5.1        lifecycle_1.0.4        magrittr_2.0.3         methods_4.5.1          nanoarrow_0.7.0        pillar_1.11.0          pkgconfig_2.0.3       
  Rcpp_1.1.0             rlang_1.1.6            stats_4.5.1            tibble_3.3.0           tools_4.5.1            utf8_1.2.6             utils_4.5.1           
  vctrs_0.6.5

bigrquerystorage was installed on this machine with a simple install.packages("bigrquerystorage"), so no gRPC compillation was needed. It still causes the RStudio sluggishness, even though it's a pretty fast machine (14 cores, 32GB ram, snappy NVME disk).

Any other thoughts?

[meztez]

Nice, I will try to reproduce

[meztez]

So. I've investigated. The hang is happening within the tibble::tibble(as.data.frame(, basically transforming the arrow object to an R tibble. It's mostly instant if I remove this step from the download function.

[meztez]

FYI:
It's faster to use duckdb.

library(duckdb)
library(tictoc)
library(tibble)
con <- duckdb::dbConnect(duckdb::duckdb())
Sys.setenv("GOOGLE_APPLICATION_CREDENTIALS" = "auth.json")
duckdb::dbSendQuery(con, "INSTALL bigquery FROM community; LOAD bigquery;")
tictoc::tic()
duckdb::dbSendQuery(con, "CREATE TABLE memory.TEST AS SELECT * FROM bigquery_query('neoxone-testing', 'SELECT passenger_count, pickup_datetime, trip_distance, payment_type, tip_amount, fare_amount, total_amount FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2021`;');")
tictoc::toc()
tictoc::tic()
tb <- tibble::tibble(duckdb::dbFetch(duckdb::dbSendQuery(con, "SELECT * FROM memory.TEST")))
tictoc::toc()

> library(duckdb)
library(tictoc)
library(tibble)
con <- duckdb::dbConnect(duckdb::duckdb())
Sys.setenv("GOOGLE_APPLICATION_CREDENTIALS" = "auth.json")
duckdb::dbSendQuery(con, "INSTALL bigquery FROM community; LOAD bigquery;")
tictoc::tic()
duckdb::dbSendQuery(con, "CREATE TABLE memory.TEST AS SELECT * FROM bigquery_query('neoxone-testing', 'SELECT passenger_count, pickup_datetime, trip_distance, payment_type, tip_amount, fare_amount, total_amount FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2021`;');")
tictoc::toc()
tictoc::tic()
tb <- tibble::tibble(duckdb::dbFetch(duckdb::dbSendQuery(con, "SELECT * FROM memory.TEST")))
tictoc::toc()
Loading required package: DBI
<duckdb_result 56330 connection=71bd0 statement='INSTALL bigquery FROM community; LOAD bigquery;'>
<duckdb_result 38110 connection=71bd0 statement='CREATE TABLE memory.TEST AS SELECT * FROM bigquery_query('neoxone-testing', 'SELECT passenger_count, pickup_datetime, trip_distance, payment_type, tip_amount, fare_amount, total_amount FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2021`;');'>
18.329 sec elapsed
1.941 sec elapsed

[meztez]

and duckdb only depends on DBI (for the interface), otherwise it's the future of database interaction.

[thiagoveloso]

@meztez - awesome findings, thanks for deep diving on that issue! Quick question, though: do you plan to incorporate any of your findings on bigrquerystorage?

From my (very personal) point of view: I myself am a data.table user, so whenever possible I prefer output tables returned as regular data frames and not tibbles. Also: I have seen some good performance with duckdb as well (reading thousand of parquet files), to the point of incorporating it to my workflow. So I would be very happy with its adoption by bigrquerystorage :).

[meztez]

Since you can mostly skip bigrquery with duckdb, no.

The development of this package predates the apparition of duckdb. If duckdb was available at the time, I wouldn't have developed this.