Как записать виртуальную таблицу BQ обратно в BQ с помощью R DBI и bigrquery?

Я хочу иметь возможность

  1. Доступ к таблице BQ. Это класс
[1] "tbl_BigQueryConnection" "tbl_dbi"                "tbl_sql"               
[4] "tbl_lazy"               "tbl"   `
  1. Измените таблицу с помощью dbplyr, чтобы создать новую таблицу. Опять же, есть класс
[1] "tbl_BigQueryConnection" "tbl_dbi"                "tbl_sql"               
[4] "tbl_lazy"               "tbl"
  1. Запишите эту новую таблицу в BQ.

Я получаю следующую ошибку:

Ошибка в (function (classes, fdef, mtable): невозможно найти унаследованный метод для функции «dbWriteTable» для сигнатуры «BigQueryConnection, character, tbl_BigQueryConnection»

MRE

library(DBI)
library(dplyr, warn.conflicts = FALSE)
library(bigrquery)

############  CREATE BQ TABLE TO ACCESS  #################
dataset = bq_dataset(bq_test_project(), "test_dataset")

if (bq_dataset_exists(dataset))
{
  bq_dataset_delete(dataset, delete_contents = T)
}
#> Suitable tokens found in the cache, associated with these emails:
#>   * [email protected]
#>   * [email protected]
#> The first will be used.
#> Using an auto-discovered, cached token.
#> To suppress this message, modify your code or options to clearly consent to the use of a cached token.
#> See gargle's "Non-interactive auth" vignette for more details:
#> https://gargle.r-lib.org/articles/non-interactive-auth.html
#> The bigrquery package is using a cached token for [email protected].

bq_dataset_create(dataset)
#> <bq_dataset> elite-magpie-257717.test_dataset

conn = DBI::dbConnect(
  bigrquery::bigquery(),
  project = bq_test_project(),
  dataset = "test_dataset",
  KeyFilePath = "google_service_key.json",
  OAuthMechanism = 0
)


if (dbExistsTable(conn, "mtcars"))
{
  dbRemoveTable(conn, "mtcars")
}

dbWriteTable(conn, "mtcars", mtcars)

#######################################################


### Access BQ table
mtcars_tbl = tbl(conn, "mtcars")
class(mtcars_tbl)
#> [1] "tbl_BigQueryConnection" "tbl_dbi"                "tbl_sql"               
#> [4] "tbl_lazy"               "tbl"

### Create new virtual table
hp_gt_100_tbl = mtcars_tbl %>% filter(hp>100)
class(hp_gt_100_tbl)
#> [1] "tbl_BigQueryConnection" "tbl_dbi"                "tbl_sql"               
#> [4] "tbl_lazy"               "tbl"

### Write new table
dbWriteTable(conn, "hp_gt_100", hp_gt_100_tbl)
#> Error in (function (classes, fdef, mtable) : unable to find an inherited method for function 'dbWriteTable' for signature '"BigQueryConnection", "character", "tbl_BigQueryConnection"'

dbExecute(conn, "DROP TABLE mtcars")
#> [1] 0
dbExecute(conn, "DROP TABLE hp_gt_100")
#> Error: Job 'elite-magpie-257717.job_O8e7BtdfAnAb_8Vdtwybibgd7DpA.US' failed
#> x Not found: Table elite-magpie-257717:test_dataset.hp_gt_100 [notFound]

Создано 11 ноября 2020 г. пакетом REPEX (v0.3.0)


r google-bigquery dplyr dbplyr bigrquery
person abalter    schedule 11.11.2020    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Я не думаю, что вы сможете сделать это с dbWriteTable, используя ваш текущий подход. dbWriteTable записывает, перезаписывает или добавляет [локальный] фрейм данных в таблицу базы данных (источник ).

Таким образом, один из вариантов - собрать эти данные в R, а они записать их обратно в SQL, используя dbWriteTable. Но, скорее всего, это будет неэффективно.

Подход, который я бы порекомендовал, заключается в создании оператора bigquery INSERT INTO и передаче его в dbExecute. Примерно так:

sql_query <- glue::glue("INSERT INTO {db}.{schema}.{tbl_name}\n",
                         dbplyr::sql_render(input_tbl))

result <- dbExecute(db_connection, as.character(sql_query))

sql_render возьмет определение вашей текущей виртуальной таблицы и вернет текст запроса. dbExecute передаст эту команду серверу bigquery для выполнения.

Обратите внимание: я недостаточно знаком с синтаксисом INSERT INTO для bigquery, чтобы гарантировать, что синтаксис sql_query выше верен, но я знаю, что общий подход работает, поскольку я широко использую dbplyr и DBI на сервере SQL.

person Simon.S.A.    schedule 11.11.2020
comment
Это определенно работает, и его хорошо держать в заднем кармане на случай других ситуаций. Мне удалось найти способ решить проблему с помощью функции dplyr copy_to с соответствующим переключателем. - person abalter; 13.11.2020

arrow_upward
2
arrow_downward

Я принимаю ответ Саймона С.А. Однако мне удалось найти более прямой метод с использованием bigrquery функции bq_project_query.

library(DBI)
library(dplyr, warn.conflicts = FALSE)
library(bigrquery)

bq_deauth()
bq_auth(email="[email protected]")


############  CREATE BQ TABLE TO ACCESS  #################

dataset = bq_dataset("elite-magpie-257717", "test_dataset")

if (bq_dataset_exists(dataset))
{
  bq_dataset_delete(dataset, delete_contents = T)
}
bq_dataset_create(dataset)
#> <bq_dataset> elite-magpie-257717.test_dataset

conn = dbConnect(
  bigrquery::bigquery(),
  project = "elite-magpie-257717",
  dataset = "test_dataset",
  KeyFilePath = "google_service_key.json",
  OAuthMechanism = 0
)

dbWriteTable(conn, "mtcars", mtcars, overwrite=T)

dbListTables(conn)
#> [1] "mtcars"

#######################################################


### Access BQ table
mtcars_tbl = tbl(conn, "test_dataset.mtcars")
class(mtcars_tbl)
#> [1] "tbl_BigQueryConnection" "tbl_dbi"                "tbl_sql"               
#> [4] "tbl_lazy"               "tbl"

### Create new virtual table
hp_gt00_tbl = mtcars_tbl %>% filter(hp>100)
class(hp_gt00_tbl)
#> [1] "tbl_BigQueryConnection" "tbl_dbi"                "tbl_sql"               
#> [4] "tbl_lazy"               "tbl"

hp_gt00_tbl %>% dbplyr::sql_render()
#> <SQL> SELECT *
#> FROM `test_dataset.mtcars`
#> WHERE (`hp` > 100.0)

bq_project_query(
  x = dataset$project,
  query = hp_gt00_tbl %>% dbplyr::sql_render(),
  destination = bq_table(dataset, "hp_gt_00")
)
#> <bq_table> elite-magpie-257717.test_dataset.hp_gt_00

bq_dataset_tables(dataset)
#> [[1]]
#> <bq_table> elite-magpie-257717.test_dataset.hp_gt_00
#> 
#> [[2]]
#> <bq_table> elite-magpie-257717.test_dataset.mtcars

bq_dataset_delete(dataset, delete_contents = T)

Создано 15 ноября 2020 г. пакетом REPEX (v0.3.0)

person abalter    schedule 13.11.2020