There are two options for connecting to Google BigQuery:
- Using the
odbcpackage with a database driver
- Using the
odbc package, in combination with a driver, provides
DBI support and an ODBC connection.
BigQuery - The official BigQuery website provides instructions on how to download and setup their ODBC driver: BigQuery Drivers
RStudio Professional Drivers - RStudio Workbench (formerly RStudio Server Pro), RStudio Desktop Pro, RStudio Connect, or Shiny Server Pro users can download and use RStudio Professional Drivers at no additional charge. These drivers include an ODBC connector for Google BigQuery. RStudio delivers standards-based, supported, professional ODBC drivers. Use RStudio Professional Drivers when you run R or Shiny with your production systems. See the RStudio Professional Drivers for more information.
The easiest way to connect to BigQuery is through a Google service account. There are 5 settings needed to make a connection using a Google service account:
- Driver - See the Drivers section for setup information
- Catalog - The name of the BigQuery project
- Email - The Google service account email address
- KeyFilePath - The full path to the
.jsonkey file. See here for more details.
- OAuthMechanism - Set to 0 to authenticate as a service account
con <- DBI::dbConnect(odbc::odbc(), Driver = "[your driver's name]", Catalog = "[BigQuery project name]", Email = "[Google service account email]", KeyFilePath = "[Full path to key file]", OAuthMechanism = 0)
To install from CRAN:
install.packages("bigrquery") The development version from github:
# install.packages("remotes") remotes::install_github("r-dbi/DBI") remotes::install_github("r-dbi/bigrquery")
library(DBI) con <- dbConnect( bigrquery::bigquery(), project = "publicdata", dataset = "samples", billing = billing )