I’m pleased to announce that bigrquery 0.4.0 is now on CRAN. bigrquery makes it possible to talk to Google’s BigQuery cloud database. It provides both DBI and dplyr backends so you can interact with BigQuery using either low-level SQL or high-level dplyr verbs.
Install the latest version of bigrquery with:
Connect to a bigquery database using DBI:
library(dplyr) con <- DBI::dbConnect(dbi_driver(), project = "publicdata", dataset = "samples", billing = "887175176791" ) DBI::dbListTables(con) #>  "github_nested" "github_timeline" "gsod" "natality" #>  "shakespeare" "trigrams" "wikipedia"
(You’ll be prompted to authenticate interactively, or you can use a service token with
Then you can either submit your own SQL queries or use dplyr to write them for you:
shakespeare <- con %>% tbl("shakespeare") shakespeare %>% group_by(word) %>% summarise(n = sum(word_count)) #> 0 bytes processed #> # Source: lazy query [?? x 2] #> # Database: BigQueryConnection #> word n #>
#> 1 profession 20 #> 2 augury 2 #> 3 undertakings 3 #> 4 surmise 8 #> 5 religion 14 #> 6 advanced 16 #> 7 Wormwood 1 #> 8 parchment 8 #> 9 villany 49 #> 10 digs 3 #> # ... with more rows
- dplyr support has been updated to require dplyr 0.7.0 and use dbplyr. This means that you can now more naturally work directly with DBI connections. dplyr now translates to modern BigQuery SQL which supports a broader set of translations. Along the way I also fixed a vareity of SQL generation bugs.
- New functions
insert_extract_job()makes it possible to extract data and save in google storage, and
insert_table()allows you to insert empty tables into a dataset.
- All POST requests (inserts, updates, copies and
query_exec) now take
.... This allows you to add arbitrary additional data to the request body making it possible to use parts of the BigQuery API that are otherwise not exposed.
snake_caseargument names are automatically converted to
camelCaseso you can stick consistently to snake case in your R code.
- Full support for DATE, TIME, and DATETIME types (#128).
There were a variety of bug fixes and other minor improvements: see the release notes for full details.