Bigquery An internal error occurred and the request could not be completed. Error: 60909208

Question

I'm trying to run the following query but it keeps throwing this error after it keeps running for around 1 to 2 hours

An internal error occurred and the request could not be completed. Error: 60909208

WITH
Liveramp AS(
 SELECT maid, segment_id, device_type, date
    FROM `mikesky.Segments.Segment_Liveramp_Raw`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
),

Neustar AS(
 SELECT maid, segment_id, device_type, date
    FROM `mikesky.Segments.Segment_Neustar_Raw`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
),

PT AS(
 SELECT maid, segment_id, device_type, date
    FROM `mikesky.Segments.Segment_PT_Raw`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()
),

liveramp_split AS(
    SELECT maid, SPLIT(segment_id) AS segment_id, device_type, date
    FROM Liveramp
),

PT_split AS(
    SELECT maid, SPLIT(segment_id) AS segment_id, device_type, date
    FROM PT
),

Neustar_split AS(
    SELECT maid, SPLIT(segment_id) AS segment_id, device_type, date
    FROM Neustar
),
Liveramp_unnested AS(
    SELECT DISTINCT maid,segments AS segment_id, device_type, date 
    FROM liveramp_split , UNNEST(segment_id) AS segments
),
Neustar_unnested AS(
    SELECT DISTINCT maid, segments AS segment_id, device_type, date 
    FROM Neustar_split , UNNEST(segment_id) AS segments
),
PT_unnested AS(
    SELECT DISTINCT maid, segments AS segment_id, device_type, date 
    FROM PT_split , UNNEST(segment_id) AS segments
),

Taxonomy AS(
    SELECT segment_id, CAST(skydeo_segment_id AS STRING) AS skydeo_segment_id
    FROM `mikesky.Skydeo_Taxonomy.Segments_IDs_Mappings`
),

Liveramp_Taxonomy AS(
    SELECT maid, skydeo_segment_id, device_type, date
    FROM Liveramp_unnested  JOIN Taxonomy ON Liveramp_unnested.segment_id = Taxonomy.segment_id 
),

Neustar_Taxonomy AS(
    SELECT maid, skydeo_segment_id, device_type, date
    FROM Neustar_unnested JOIN Taxonomy ON Neustar_unnested.segment_id = Taxonomy.segment_id 
),

PT_Taxonomy AS(
    SELECT maid, skydeo_segment_id, device_type, date
    FROM PT_unnested JOIN Taxonomy ON PT_unnested.segment_id = Taxonomy.segment_id 
),

Liveramp_agg AS(
    SELECT maid, ARRAY_AGG(skydeo_segment_id) AS skydeo_segment_id, device_type, date 
    FROM Liveramp_Taxonomy
    GROUP BY maid, device_type, date
),

Neustar_agg AS(
    SELECT maid, ARRAY_AGG(skydeo_segment_id) AS skydeo_segment_id, device_type, date 
    FROM Neustar_Taxonomy
    GROUP BY maid, device_type, date
),

PT_agg AS(
    SELECT maid, ARRAY_AGG(skydeo_segment_id) AS skydeo_segment_id, device_type, date 
    FROM PT_Taxonomy
    GROUP BY maid, device_type, date
),

Super_set AS(
SELECT * FROM Liveramp_agg

UNION ALL

SELECT * FROM Neustar_agg

UNION ALL

SELECT * FROM PT_agg

)

SELECT *
FROM Super_set

This query is supposed to combine data from 3 tables (Liveramp_Raw, PT_Raw, Neustar_Raw) and then Join the result with the (Segments_IDs_Mappings) Table and then aggregate the results.

At first, I was running another version of this query but it was throwing this error

Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations. Consider provisioning more slots, reducing query concurrency, or using more efficient logic in this job.

So I tried optimizing it as much as I could.

Any Ideas why I'm getting this error or what does it mean?

Answer 1

I got the same error when I had several invalid data, more than one data reported error (e.g. data type errors, date error).