1

As always I'm thankful for any pointer in the right direction. My Scenario is included two tables with one being a default table while the second one is clustered and partitioned by ID and Date. I believed this to be sufficient solution to my problem described here: Operation timed out - BigQuery optimizing window function

However, any sort of pruning by referencing the default table seems to result in the same error:

Cannot query over table without a filter over column(s) 'timestamp' that can be used for partition elimination.

This seems to suggest to me that joining clustered data not to be an option. Unfortunately joining clustered Tables isn't as well documented, so thank you for your help.

The Query:

SELECT *

FROM DefaultTable
RIGHT JOIN ClusteredTable 
ON DefaultTable.vehicleId = ClusteredTable.vehicleid
WHERE 
States.timestamp = Bikes.timestamp

EDIT: I should note that there is no issue when Bikes.timestamp is replaced with an actual String Date.

Ricco D
  • 6,873
  • 1
  • 8
  • 18
Aleko
  • 103
  • 8
  • 1
    Can you provide sample data for both tables just so the issue could be easily reproduced by the community? – Ricco D Dec 20 '21 at 09:37

1 Answers1

0

My problem ended up being that none of the timestamps matched up. The solution I ended up using was to aggregate timestamp ranges over an range. Thank you for your help and time :)

Aleko
  • 103
  • 8