CODEWITHSUNDEEP
sqlgoogle-bigquery
user3447653
user3447653

Reputation: 4148

Joining 2 tables cause resources exceeded error in BigQuery

I am trying to fetch records by joining 2 tables, and in this case I consider 6 months worth of data. I get the "Resources exceeded error".

Could anyone please advice on some query enhancements that would help me in bypassing this error?

Thanks in advance!!

    SELECT
      *
    FROM (
      SELECT
        A.Id1 AS Id1,
        A.Id2 AS Id1,
        StartTime,
        StopTime,
        Duration AS ID,
        Latitude,
        Longitude,
        DateTime
      FROM (
        SELECT
          *
        FROM
          `Tb1`
        WHERE
          HardwareId IN (
            735,
            8055,
            8620,
            9489,
            214,
            7543,
            2471,
            681,
            451)
          AND _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
          AND TIMESTAMP('2016-12-31') ) AS A
      JOIN (
        SELECT
          *
        FROM
          `Tb2.T2016*`
        WHERE
          _TABLE_SUFFIX BETWEEN "0601"
          AND "1231"
          AND HardwareId IN (
            735,
            8055,
            8620,
            9489,
            214,
            7543,
            2471,
            681,
            451) ) AS B
      ON
        A.StartTime < B.DateTime
        AND A.StopTime >= B.DateTime
        AND A.Id1 = B.Id1 )
    ORDER BY
      Id1,
      Id2,
      DateTime`

Upvotes: 0

Views: 37

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Start with removing ORDER BY part of your query! 

ORDER BY
  Id1,
  Id2,
  DateTime

Usually, this is the main reason for 

resources exceeded error in BigQuery

I also noticed some issue with your query that most likely related to the fact that you slightly obscured your original query

1 - same column name used 

A.Id1 AS Id1,  
A.Id2 AS Id1,

2 - different time period

'2016-01-01' - '2016-12-31' in 

BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-12-31')

vs. '2016-06-01' - '2016-12-31'

BETWEEN "0601" AND "1231"

Upvotes: 1

Related Questions