BigQuery Allow Large Results doesn't work when

Question

I am trying to retrieve table from BigQuery into Rstudio Server with the following:

project <- "my-project"
sql <- 'SELECT * FROM [my-project:dataset.table]'
data <- query_exec(sql, project = project, max_pages = Inf)

Error: Response too large to return. Consider setting allowLargeResults to true in your job configuration. For more details, see https://cloud.google.com/bigquery/querying-data#largequeryresults

I've checked "Allow Large Results" when configuring the table in the browser, still get the same error.

Used the following as well but without any success.

bq query --allow_large_results --destination_table=dataset.table1 "select x, y, z from dataset.table"

Answer 1

In general, when you get this error, you have to set allowLargeResults to true and specify a destination table. You can find the explanation in the link provided by the error message:

Normally, queries have a maximum response size. If you plan to run a query that might return larger results, you can set allowLargeResults to true in your job configuration. Configuring large results requires you to specify a destination table. You incur storage charges for the destination table.

In your case, I guess you are using the bigrquery package. If so, the function query_exec has an optional argument called destination_table, whose description is:

(optional) destination table for large queries, either as a string in the format used by BigQuery, or as a list with project_id, dataset_id, and table_id entries

In summary, using this argument you can provide a destination table (and implicitly set allowLargeResults to true), so you should use:

project <- "my-project"
sql <- 'SELECT * FROM [my-project:dataset.table]'
data <- query_exec(sql, project = project, max_pages = Inf,
                   destination_table = "my-project:dataset.newtable")

Just keep in mind that you will create a new table called newtable in your dataset.