How do I return my large Google BigQuery v2 response?

Question

I have tried to run a simple query, but I get the error response too large to return

SELECT accno, year, month, count(*) as cnt
FROM eric.accesslogs_tbl
GROUP BY accno, year, month
ORDER BY cnt, year, month limit 1000;

My data table statistics:

Table Size: 64.7 GB
Number of Rows: 59,971,371

Is there a workaround for it? Otherwise, we can't use it.

Jordan Tigani Jordan Tigani · Accepted Answer · 2012-10-02T21:09:19

The limit you're hitting is not the number of rows you can return, the limit is the amount of data internally used by a GROUP BY operation.

There is an experimental feature that will let you remove this limitation: try using GROUP EACH BY instead of GROUP BY.

Alternately you could do this without using experimental features using the TOP function. Your case makes TOP a little bit trickier since you want the top results for three different fields, but you can concatenate them together:

SELECT TOP(acct_month, 1000), COUNT(*) AS cnt FROM (
    SELECT CONCAT(CONCAT(CONCAT(CONCAT(
        STRING(accno), '-'), STRING(year)), '-'), STRING(month)) 
        AS acct_month 
    FROM eric.accesslogs_tbl)

The results will be a little bit wonky, since they'll have a single field accno-year-month.

How do I return my large Google BigQuery v2 response?

1 Answers