BigQuery won't return results when using windowing

2
votes

I'm trying to get the most recent token sent by each device using BigQuery. My test query works, but when I remove the limit clause, I wait 30+ minutes and get no results.

SELECT idfa, token, app_id, ds, stamp
FROM
  (
  SELECT idfa, token, app_id, ds, stamp
    , max(stamp) over(partition by idfa, app_id) as max_stamp
  FROM islot.token limit 300
  )
WHERE max_stamp = stamp;

I've tried ordering the results first and then partitioning, partitioning by day and then by stamp, but still no success. I know how to get this query to work in RedShift or Hadoop because I can take advantage of indexing and examine the explain plan.

Any tips for getting BigQuery to actually return results?

1
google-bigquery

1 Answers

3
votes

My idfa column was too sparse. Adding the following fixed it.

WHERE idfa != '' and idfa != '00000000-0000-0000-0000-000000000000'