This retrieves rows in chunks of page_size. It is most suitable for results
of smaller queries (<100 MB, say). For larger queries, it is better to
export the results to a CSV file stored on google cloud and use the
bq command line tool to download locally.
bq_table_download(x, max_results = Inf, page_size = 10000,
start_index = 0L, max_connections = 6L, quiet = NA)A bq_table
Maximum number of results to retrieve. Use Inf
retrieve all rows.
The number of rows returned per page. Make this smaller if you have many fields or large records and you are seeing a 'responseTooLarge' error.
Starting row index (zero-based).
Number of maximum simultaneously connections to BigQuery servers.
If FALSE, displays progress bar; if TRUE is silent;
if NA displays progress bar only for long-running jobs.
Because data retrieval may generalise list-cols and the data frame
print method can have problems with list-cols, this method returns
tibbles. If you need a data frame, coerce the results with
as.data.frame().
bigrquery will retrieve nested and repeated columns in to list-columns as follows:
Repeated values (arrays) will become a list-cols of vectors.
Records will become list-cols of named lists.
Repeated records will become list-cols of data frames.
In my timings, this code takes around 1 minute per 100 MB of data. If you need to download considerably more than this, I recommend:
Export a .csv file to Cloud Storage using bq_table_save()
Use the gsutil command line utility to download it
Read the csv file into R with readr::read_csv() or data.table::fread().
Unfortunately you can not export nested or repeated formats into CSV, and the formats that BigQuery supports (arvn and ndjson) that allow for nested/repeated values, are not well supported in R.
# NOT RUN {
if (bq_testable()) {
df <- bq_table_download("publicdata.samples.natality", max_results = 35000)
}
# }
Run the code above in your browser using DataLab