Write a tbl_dbi as a table in bigquery

[joscani]

Write a tbl_dbi as a table in bigquery

[hadley]

Shouldn't that be tabla3 %>% compute(I("churn.tabla3"), temporary = FALSE) ? If that doesn't work, I'd say it's a bug.

[joscani]

Ups.. You're right. The I is neccesary

tabla3  %>% compute(I("churn.test"), temporary = FALSE)

load_tabla3  <- sc  %>% tbl(I("churn.test"))

load_tabla3  %>% head(6)

# Source:   SQL [6 x 41]
# Database: BigQueryConnection
  customerID gender_x SeniorCitizen_x Partner_x Dependents_x tenure_x
  <chr>      <chr>              <int> <lgl>     <lgl>           <int>
1 8883-GRDWQ Male                   1 FALSE     FALSE              20
2 1088-CNNKB Male                   0 TRUE      FALSE              63
3 2282-YGNOR Female                 0 FALSE     FALSE              29
4 2361-FJWNO Male                   0 FALSE     FALSE              40
5 3605-JISKB Male                   1 TRUE      FALSE              55
6 4003-OCTMP Female                 0 TRUE      FALSE              31

Thanks a lot. In next months I'll use this package in my job..

[joscani]

Maybe sometthing like that.

write_table_bq  <-  function(table_to_write, project,dataset, table_new){

  query  <- stringr::str_glue("CREATE TABLE `{project}.{dataset}.{table_new}` AS {dbplyr::sql_render(table_to_write) %>% as.character()}")

  bigrquery::bq_project_query(project, query)
}

[hadley]

You should be able to use compute(tbl, "name", temporary = FALSE).

[joscani]

Thanks @hadley by answer me and for your bigrquery package. bigrquery will help me in my job the next months.

compute(tbl, "name", temporary = FALSE) not work , (using name like myproject.dataset.nametable, neither with dataset.nametable or only nametable.

For write tables I use the function in my previous coment. write_table_bq
And this morning I'm working in a simple way to write table in temporary dataset and then bring the refereence again to use in bigrquery and use dplyr syntax.
What do you think about this idea?

get_bq_table <-  function(table, project,dataset){
  query  <- stringr::str_glue("{dbplyr::sql_render(table) %>% as.character()}")
 tb_bq  <- bigrquery::bq_project_query(project, query)
}


get_lazy_from_bq <- function(table, con){
    meta <- bigrquery::bq_table_meta(table)
    tb_name <- paste0(meta$tableReference$datasetId, ".", meta$tableReference$tableId)
    con  %>% tbl(I(tb_name))
  }

bq_compute <- function(table) {

  atrib_con  <-  attributes(table$src$con)
  project  <- atrib_con$project
  dataset  <- atrib_con$dataset
  con <- table$src

  temp_table  <-  get_bq_table(table, project, dataset)
  ref_table  <-  get_lazy_from_bq(temp_table, con)
}

And I can do things like that

tabla4  <-  tabla1 %>% 
  inner_join(tabla2, by = "customerID")  %>%
  bq_compute()

[hadley]

If compute() doesn't work, it should be made to work, because that's the official dbplyr syntax.

[joscani]

Uhm, I'm just update my packages, and compute works perfectly. Thanks @hadley . I close the issue with this comment

[joscani]

The problem was if I have a connection without dataset .

sc <- src_bigquery("my_project", dataset = NULL,  max_pages = 10)

tabla1  <-  sc  %>% tbl(I("churn.tabla1"))
tabla2  <-  sc  %>% tbl(I("churn.tabla2"))


# This works
tabla3 <- tabla1 %>% 
  inner_join(tabla2, by = "customerID") %>%
  compute()

# This not works. 
tabla3  %>% 
compute("churn.tabla3.", temporary = FALSE)

But if I use src_bigquery with dataset, all works fine

sc <- src_bigquery("my_project", dataset = "churn",  max_pages = 10)