The bigrquery package makes it easy to work with data stored in Google BigQuery by allowing you to query BigQuery tables and retrieve metadata about your projects, datasets, tables, and jobs. The bigrquery package provides three levels of abstraction on top of BigQuery:
The low-level API provides thin wrappers over the underlying REST API. All the low-level functions start with bq_, and mostly have the form bq_noun_verb(). This level of abstraction is most appropriate if you’re familiar with the REST API and you want do something not supported in the higher-level APIs.
The DBI interface wraps the low-level API and makes working with BigQuery like working with any other database system. This is most convenient layer if you want to execute SQL queries in BigQuery or upload smaller amounts (i.e. <100 MB) of data.
The dplyr interface lets you treat BigQuery tables as if they are in-memory data frames. This is the most convenient layer if you don’t want to write SQL, but instead want dbplyr to write it for you.
The current bigrquery release can be installed from CRAN:
The newest development release can be installed from GitHub:
library(bigrquery)
billing <- bq_test_project() # replace this with your project ID
sql <- "SELECT year, month, day, weight_pounds FROM `publicdata.samples.natality`"
tb <- bq_project_query(billing, sql)
bq_table_download(tb, n_max = 10)
#> First chunk includes all requested rows.
#> # A tibble: 10 x 4
#> year month day weight_pounds
#> <int> <int> <int> <dbl>
#> 1 1969 1 20 7.00
#> 2 1969 1 27 7.69
#> 3 1969 6 19 6.75
#> 4 1969 5 30 6.19
#> 5 1969 11 9 7.87
#> 6 1969 5 25 7.06
#> 7 1969 7 25 7.94
#> 8 1969 9 11 7.06
#> 9 1969 7 13 6.00
#> 10 1969 9 27 8.13library(DBI)
con <- dbConnect(
bigrquery::bigquery(),
project = "publicdata",
dataset = "samples",
billing = billing
)
con
#> <BigQueryConnection>
#> Dataset: publicdata.samples
#> Billing: gargle-169921
dbListTables(con)
#> [1] "github_nested" "github_timeline" "gsod" "natality"
#> [5] "shakespeare" "trigrams" "wikipedia"
dbGetQuery(con, sql, n = 10)
#> First chunk includes all requested rows.
#> # A tibble: 10 x 4
#> year month day weight_pounds
#> <int> <int> <int> <dbl>
#> 1 1969 1 20 7.00
#> 2 1969 1 27 7.69
#> 3 1969 6 19 6.75
#> 4 1969 5 30 6.19
#> 5 1969 11 9 7.87
#> 6 1969 5 25 7.06
#> 7 1969 7 25 7.94
#> 8 1969 9 11 7.06
#> 9 1969 7 13 6.00
#> 10 1969 9 27 8.13library(dplyr)
natality <- tbl(con, "natality")
natality %>%
select(year, month, day, weight_pounds) %>%
head(10) %>%
collect()
#> # A tibble: 10 x 4
#> year month day weight_pounds
#> <int> <int> <int> <dbl>
#> 1 1969 10 6 3.25
#> 2 1969 5 11 5.75
#> 3 1969 6 29 7.94
#> 4 1969 3 7 8.38
#> 5 1970 4 26 6.38
#> 6 1971 10 6 6.69
#> 7 1971 2 23 6.69
#> 8 1971 8 12 7.37
#> 9 1969 9 3 5.25
#> 10 1969 4 25 6.62When using bigrquery interactively, you’ll be prompted to authorize bigrquery in the browser. Your token will be cached across sessions inside the folder ~/.R/gargle/gargle-oauth/, by default. For non-interactive usage, it is preferred to use a service account token and put it into force via bq_auth(path = "/path/to/your/service-account.json"). More places to learn about auth:
bigrquery::bq_auth().gargle::token_fetch(), which supports a variety of token flows. This article provides full details, such as how to take advantage of Application Default Credentials or service accounts on GCE VMs.Note that bigrquery requests permission to modify your data; but it will never do so unless you explicitly request it (e.g. by calling bq_table_delete() or bq_table_upload()). Our Privacy policy provides more info.
If you just want to play around with the BigQuery API, it’s easiest to start with Google’s free sample data. You’ll still need to create a project, but if you’re just playing around, it’s unlikely that you’ll go over the free limit (1 TB of queries / 10 GB of storage).
To create a project:
Open https://console.cloud.google.com/ and create a project. Make a note of the “Project ID” in the “Project info” box.
Click on “APIs & Services”, then “Dashboard” in the left the left menu.
Click on “Enable Apis and Services” at the top of the page, then search for “BigQuery API” and “Cloud storage”.
Use your project ID as the billing project whenever you work with free sample data; and as the project when you work with your own data.
Please note that the ‘bigrquery’ project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.