CODEWITHSUNDEEP
google-bigquery
Ondřej Veselý
Ondřej Veselý

Reputation: 125

Google Bigquery says "Response too large to return" with simple select

Modifier allowLargeResults is set on and I have also tried interactive and batch query priority. There are 70M records in table search_results, 10M records in searches and about (just) 900 in buy table. And also the WHERE reduces the number of rows pretty well.

SELECT
    s.flyFrom, s.to, s.typeFlight, r.price, b.price, b.affily
FROM [sptest.buy] AS b
INNER JOIN [sptest.search_results] AS r
ON b.booking_token=r.booking_token
INNER JOIN [sptest.searches] AS s
ON s.searchid=r.searchid
WHERE
    DATE(r.saved_at) >= DATE('2015-06-23 00:00:00') AND 
    DATE(s.saved_at) >= DATE('2015-06-23 00:00:00')
LIMIT 10

Could the problem be caused by large joining keys? The booking_token key is variable size 50-600 chars.

Upvotes: 1

Views: 95

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

I would do couple modifications to this query:

  1. Move WHERE clause filters closer to the table scan
  2. Use JOIN EACH construct

SELECT s.flyFrom, s.to, s.typeFlight, r.price, b.price, b.affily FROM [sptest.buy] AS b INNER JOIN EACH (SELECT * FROM [sptest.search_results] WHERE saved_at > DATE('2015-06-23 00:00:00')) AS r ON b.booking_token=r.booking_token INNER JOIN EACH (SELECT * FROM [sptest.searches] WHERE saved_at > DATE('2015-06-23 00:00:00') AS s ON s.searchid=r.searchid LIMIT 10

Upvotes: 2

Related Questions