BigQuery DBI driver — bigquery • bigrquery

Creates a BigQuery DBI driver for use in DBI::dbConnect().

Usage

# S4 method for BigQueryDriver
dbConnect(
  drv,
  project,
  dataset = NULL,
  billing = project,
  page_size = 10000,
  quiet = NA,
  use_legacy_sql = FALSE,
  bigint = c("integer", "integer64", "numeric", "character"),
  ...
)

Arguments

drv: an object that inherits from DBIDriver, or an existing DBIConnection object (in order to clone an existing connection).
project, dataset: Project and dataset identifiers
billing: Identifier of project to bill.
page_size: Number of items per page.
quiet: If FALSE, displays progress bar; if TRUE is silent; if NA picks based on whether or not you're in an interactive context.
use_legacy_sql: If TRUE will use BigQuery's legacy SQL format.
bigint: The R type that BigQuery's 64-bit integer types should be mapped to. The default is "integer" which returns R's integer type but results in NA for values above/below +/- 2147483647. "integer64" returns a bit64::integer64, which allows the full range of 64 bit integers.
...: Other arguments for compatibility with generic; currently ignored.

Examples

con <- DBI::dbConnect(
  bigquery(),
  project = "publicdata",
  dataset = "samples",
  billing = bq_test_project()
)
con
#> <BigQueryConnection>
#>   Dataset: publicdata.samples
#>   Billing: gargle-169921
DBI::dbListTables(con)
#> [1] "github_nested"   "github_timeline" "gsod"           
#> [4] "natality"        "shakespeare"     "trigrams"       
#> [7] "wikipedia"      
DBI::dbReadTable(con, "natality", n_max = 10)
#> # A tibble: 10 × 31
#>    source_year  year month   day  wday state is_male child_race
#>          <int> <int> <int> <int> <int> <chr> <lgl>        <int>
#>  1        2005  2005     5    NA     6 NA    FALSE           NA
#>  2        2005  2005     6    NA     3 NA    TRUE            NA
#>  3        2005  2005    11    NA     4 NA    FALSE           NA
#>  4        2005  2005     6    NA     5 NA    FALSE           NA
#>  5        2005  2005     5    NA     5 NA    FALSE           NA
#>  6        2005  2005    10    NA     5 NA    TRUE            NA
#>  7        2005  2005    12    NA     6 NA    FALSE           NA
#>  8        2005  2005    10    NA     4 NA    TRUE            NA
#>  9        2005  2005    10    NA     2 NA    TRUE            NA
#> 10        2005  2005     7    NA     2 NA    FALSE           NA
#> # ℹ 23 more variables: weight_pounds <dbl>, plurality <int>,
#> #   apgar_1min <int>, apgar_5min <int>, mother_residence_state <chr>,
#> #   mother_race <int>, mother_age <int>, gestation_weeks <int>,
#> #   lmp <chr>, mother_married <lgl>, mother_birth_state <chr>,
#> #   cigarette_use <lgl>, cigarettes_per_day <int>, alcohol_use <lgl>,
#> #   drinks_per_week <int>, weight_gain_pounds <int>,
#> #   born_alive_alive <int>, born_alive_dead <int>, born_dead <int>, …

# Create a temporary dataset to explore
ds <- bq_test_dataset()
con <- DBI::dbConnect(
  bigquery(),
  project = ds$project,
  dataset = ds$dataset
)
DBI::dbWriteTable(con, "mtcars", mtcars)
DBI::dbReadTable(con, "mtcars")[1:6, ]
#> # A tibble: 6 × 11
#>      am  carb    vs  qsec    wt  drat  disp    hp   cyl  gear   mpg
#>   <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> <dbl>
#> 1     0     2     1  20    3.19  3.69  147.    62     4     4  24.4
#> 2     0     2     1  22.9  3.15  3.92  141.    95     4     4  22.8
#> 3     0     1     1  20.0  2.46  3.7   120.    97     4     3  21.5
#> 4     0     1     1  19.4  3.22  3.08  258    110     6     3  21.4
#> 5     0     1     1  20.2  3.46  2.76  225    105     6     3  18.1
#> 6     0     4     1  18.3  3.44  3.92  168.   123     6     4  19.2

DBI::dbGetQuery(con, "SELECT count(*) FROM mtcars")
#> # A tibble: 1 × 1
#>     f0_
#>   <int>
#> 1    32

res <- DBI::dbSendQuery(con, "SELECT cyl, mpg FROM mtcars")
dbColumnInfo(res)
#>   name    type
#> 1  cyl INTEGER
#> 2  mpg   FLOAT
dbFetch(res, 10)
#> # A tibble: 10 × 2
#>      cyl   mpg
#>    <int> <dbl>
#>  1     4  24.4
#>  2     4  22.8
#>  3     4  21.5
#>  4     6  21.4
#>  5     6  18.1
#>  6     6  19.2
#>  7     6  17.8
#>  8     8  18.7
#>  9     8  14.3
#> 10     8  16.4
dbFetch(res, -1)
#> # A tibble: 22 × 2
#>      cyl   mpg
#>    <int> <dbl>
#>  1     8  17.3
#>  2     8  15.2
#>  3     8  10.4
#>  4     8  10.4
#>  5     8  14.7
#>  6     8  15.5
#>  7     8  15.2
#>  8     8  13.3
#>  9     8  19.2
#> 10     4  22.8
#> # ℹ 12 more rows
DBI::dbHasCompleted(res)
#> [1] TRUE