Google BigQuery Error: Resources exceeded during query execution

Question

I get a "resources exceeded" error when I run the following simple query in BigQuery, most of the answers to this error refer to the EACH clause , but I am not using any such clause in my query.

SELECT
  XXX_lnaourj_ID,
  XXX_DR_Tma_LC,
  XXX_CR_Tma_LC,
  XXX_Amount_LC,
  XXX_TLAffect_LC,
  XXX_Entry_Desc,
  XXX_DocType,
  XXX_TCode,
  XXX_Company,
  XXX_Posted_By,
  XXX_Parked_By,
  XXX_Accounting_Date,
  XXX_Creation_Date
FROM
  [acl_data.XXX_JE_All_Formatted]
WHERE
  XXX_lnaourj_ID IN (
  SELECT
    XXX_lnaourj_ID
  FROM
    [acl_data.XXX_JE_All_Formatted]
  WHERE
    XXX_Creation_Date - XXX_Accounting_Date > 30 )
ORDER BY
  XXX_lnaourj_ID ASC;

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-03-23T21:15:59

Remove ORDER BY and it should work

I think BigQuery engine translates your query using JOIN (and that's why you see it is referenced), so another option for you would be to rewrite your query using JOIN explicitly and try it with and/or without EACH clause

see https://stackoverflow.com/a/24191584/5221944 for more details from bigquery teammember

Edit

I haven't realized it is the same table! Why not to use something like below?

SELECT
  XXX_lnaourj_ID,
  XXX_DR_Tma_LC,
  XXX_CR_Tma_LC,
  XXX_Amount_LC,
  XXX_TLAffect_LC,
  XXX_Entry_Desc,
  XXX_DocType,
  XXX_TCode,
  XXX_Company,
  XXX_Posted_By,
  XXX_Parked_By,
  XXX_Accounting_Date,
  XXX_Creation_Date
FROM [acl_data.XXX_JE_All_Formatted]
WHERE XXX_Creation_Date - XXX_Accounting_Date > 30

Google BigQuery Error: Resources exceeded during query execution

1 Answers