# bigrquery

[![Codecov test
coverage](https://codecov.io/gh/r-dbi/bigrquery/graph/badge.svg)](https://app.codecov.io/gh/r-dbi/bigrquery)

The bigrquery package makes it easy to work with data stored in [Google
BigQuery](https://docs.cloud.google.com/bigquery/docs) 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](https://r-dbi.org) 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](https://dbplyr.tidyverse.org/) 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.

## Installation

The current bigrquery release can be installed from CRAN:

``` r

install.packages("bigrquery")
```

The newest development release can be installed from GitHub:

``` r

#install.packages("pak")
pak::pak("r-dbi/bigrquery")
```

## Usage

### Low-level API

``` r

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)
#> # A tibble: 10 × 4
#>     year month   day weight_pounds
#>    <int> <int> <int>         <dbl>
#>  1  1969     9     5          7.00
#>  2  1969     6    16          8.06
#>  3  1969    12    17          5.94
#>  4  1969     9     1          7.44
#>  5  1969     4    25          7.94
#>  6  1969     3    13          7.31
#>  7  1969     4    17          7.13
#>  8  1969     7    19          7.00
#>  9  1969    12    13          7.75
#> 10  1969     7    22          6.38
```

### DBI

``` r

library(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)
#> # A tibble: 10 × 4
#>     year month   day weight_pounds
#>    <int> <int> <int>         <dbl>
#>  1  1969     9     5          7.00
#>  2  1969     6    16          8.06
#>  3  1969    12    17          5.94
#>  4  1969     9     1          7.44
#>  5  1969     4    25          7.94
#>  6  1969     3    13          7.31
#>  7  1969     4    17          7.13
#>  8  1969     7    19          7.00
#>  9  1969    12    13          7.75
#> 10  1969     7    22          6.38
```

### dplyr

``` r

library(dplyr)

natality <- tbl(con, "natality")

natality %>%
  select(year, month, day, weight_pounds) %>% 
  head(10) %>%
  collect()
#> # A tibble: 10 × 4
#>     year month   day weight_pounds
#>    <int> <int> <int>         <dbl>
#>  1  2005     8    NA          7.81
#>  2  2005    10    NA          7.10
#>  3  2005    12    NA          7.31
#>  4  2005     2    NA          3.75
#>  5  2005     1    NA          7.35
#>  6  2005    12    NA          3.19
#>  7  2005     7    NA          7.87
#>  8  2005     4    NA          8.31
#>  9  2005     4    NA          7.56
#> 10  2005    10    NA          7.19
```

## Important details

### BigQuery account

To use bigrquery, you’ll need a BigQuery project. Fortunately, if you
just want to play around with the BigQuery API, it’s easy to start with
Google’s free [public
data](https://docs.cloud.google.com/bigquery/public-data) and the
[BigQuery sandbox](https://docs.cloud.google.com/bigquery/docs/sandbox).
This gives you some fun data to play with along with enough free compute
(1 TB of queries & 10 GB of storage per month) to learn the ropes.

To get started, open <https://console.cloud.google.com/bigquery> and
create a project. Make a note of the “Project ID” as you’ll use this as
the `billing` project whenever you work with free sample data; and as
the `project` when you work with your own data.

### Authentication and authorization

When using bigrquery interactively, you’ll be prompted to [authorize
bigrquery](https://docs.cloud.google.com/bigquery/docs/authorization) in
the browser. You’ll be asked if you want to cache tokens for reuse in
future sessions. For non-interactive usage, it is preferred to use a
service account token, if possible. More places to learn about auth:

- Help for
  [`bigrquery::bq_auth()`](https://bigrquery.r-dbi.org/reference/bq_auth.html).
- [How gargle gets
  tokens](https://gargle.r-lib.org/articles/how-gargle-gets-tokens.html).
  - bigrquery obtains a token with
    [`gargle::token_fetch()`](https://gargle.r-lib.org/reference/token_fetch.html),
    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.
- [Non-interactive
  auth](https://gargle.r-lib.org/articles/non-interactive-auth.html).
  Explains how to set up a project when code must run without any user
  interaction.
- [How to get your own API
  credentials](https://gargle.r-lib.org/articles/get-api-credentials.html).
  Instructions for getting your own OAuth client or service account
  token.

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()`](https://bigrquery.r-dbi.org/reference/api-table.md)
or
[`bq_table_upload()`](https://bigrquery.r-dbi.org/reference/api-table.md)).
Our [Privacy policy](https://tidyverse.org/google_privacy_policy/)
provides more info.

## Useful links

- [SQL
  reference](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators)
- [API
  reference](https://docs.cloud.google.com/bigquery/docs/reference/rest)
- [Query/job console](https://console.cloud.google.com/bigquery/)
- [Billing console](https://console.cloud.google.com/)

## Policies

Please note that the ‘bigrquery’ project is released with a [Contributor
Code of Conduct](https://bigrquery.r-dbi.org/CODE_OF_CONDUCT.html). By
contributing to this project, you agree to abide by its terms.

[Privacy policy](https://tidyverse.org/google_privacy_policy/)

# Package index

## DBI and dplyr

- [`src_bigquery()`](https://bigrquery.r-dbi.org/reference/src_bigquery.md)
  : A BigQuery data source for dplyr.
- [`dbConnect(`*`<BigQueryDriver>`*`)`](https://bigrquery.r-dbi.org/reference/bigquery.md)
  : BigQuery DBI driver
- [`collect(`*`<tbl_BigQueryConnection>`*`)`](https://bigrquery.r-dbi.org/reference/collect.tbl_BigQueryConnection.md)
  : Collect a BigQuery table

## Low-level API

- [`bq_dataset_create()`](https://bigrquery.r-dbi.org/reference/api-dataset.md)
  [`bq_dataset_meta()`](https://bigrquery.r-dbi.org/reference/api-dataset.md)
  [`bq_dataset_exists()`](https://bigrquery.r-dbi.org/reference/api-dataset.md)
  [`bq_dataset_update()`](https://bigrquery.r-dbi.org/reference/api-dataset.md)
  [`bq_dataset_delete()`](https://bigrquery.r-dbi.org/reference/api-dataset.md)
  [`bq_dataset_tables()`](https://bigrquery.r-dbi.org/reference/api-dataset.md)
  : BigQuery datasets
- [`bq_job_meta()`](https://bigrquery.r-dbi.org/reference/api-job.md)
  [`bq_job_status()`](https://bigrquery.r-dbi.org/reference/api-job.md)
  [`bq_job_show_statistics()`](https://bigrquery.r-dbi.org/reference/api-job.md)
  [`bq_job_wait()`](https://bigrquery.r-dbi.org/reference/api-job.md) :
  BigQuery job: retrieve metadata
- [`bq_project_datasets()`](https://bigrquery.r-dbi.org/reference/api-project.md)
  [`bq_project_jobs()`](https://bigrquery.r-dbi.org/reference/api-project.md)
  : BigQuery project methods
- [`bq_table_create()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_meta()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_fields()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_size()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_nrow()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_exists()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_delete()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_copy()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_upload()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_save()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_load()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  [`bq_table_patch()`](https://bigrquery.r-dbi.org/reference/api-table.md)
  : BigQuery tables
- [`bq_auth()`](https://bigrquery.r-dbi.org/reference/bq_auth.md) :
  Authorize bigrquery
- [`bq_auth_configure()`](https://bigrquery.r-dbi.org/reference/bq_auth_configure.md)
  [`bq_oauth_client()`](https://bigrquery.r-dbi.org/reference/bq_auth_configure.md)
  : Edit and view auth configuration
- [`bq_deauth()`](https://bigrquery.r-dbi.org/reference/bq_deauth.md) :
  Clear current token
- [`bq_field()`](https://bigrquery.r-dbi.org/reference/bq_field.md)
  [`bq_fields()`](https://bigrquery.r-dbi.org/reference/bq_field.md)
  [`as_bq_field()`](https://bigrquery.r-dbi.org/reference/bq_field.md)
  [`as_bq_fields()`](https://bigrquery.r-dbi.org/reference/bq_field.md)
  : BigQuery field (and fields) class
- [`bq_has_token()`](https://bigrquery.r-dbi.org/reference/bq_has_token.md)
  : Is there a token on hand?
- [`bq_projects()`](https://bigrquery.r-dbi.org/reference/bq_projects.md)
  : List available projects
- [`bq_project_query()`](https://bigrquery.r-dbi.org/reference/bq_query.md)
  [`bq_dataset_query()`](https://bigrquery.r-dbi.org/reference/bq_query.md)
  : Submit query to BigQuery
- [`bq_dataset()`](https://bigrquery.r-dbi.org/reference/bq_refs.md)
  [`as_bq_dataset()`](https://bigrquery.r-dbi.org/reference/bq_refs.md)
  [`bq_table()`](https://bigrquery.r-dbi.org/reference/bq_refs.md)
  [`as_bq_table()`](https://bigrquery.r-dbi.org/reference/bq_refs.md)
  [`bq_job()`](https://bigrquery.r-dbi.org/reference/bq_refs.md)
  [`as_bq_job()`](https://bigrquery.r-dbi.org/reference/bq_refs.md) : S3
  classes for BigQuery datasets, tables and jobs
- [`bq_table_download()`](https://bigrquery.r-dbi.org/reference/bq_table_download.md)
  : Download table data
- [`bq_token()`](https://bigrquery.r-dbi.org/reference/bq_token.md) :
  Produce configured token
- [`bq_user()`](https://bigrquery.r-dbi.org/reference/bq_user.md) : Get
  info on current user