BigQuery error: Connection error. please try again -

BigQuery error: Connection error. please try again -

i seek run big query involve joining 5 tables. query need run every half hour, time completes in matter of seconds, takes hours, worst scenario fails @ end , error "connection error, please seek again". i'm not using "join each" "join" due performance reason , underlying dataset seems not big now.

the query is:

select '2014-10-13' date, i.eventid event_id, e.event_name event_name, datediff('2014-10-13', e.event_start_date)+1 event_day, i.impressioncts total_viewers, round(i.top10cts*100/i.impressioncts) percent_pos_top10, round(i.ct1020*100/i.impressioncts) percent_pos_1020, round(i.ct2030*100/i.impressioncts) percent_pos_2030, round(i.ct3050*100/i.impressioncts) percent_pos_3050, round(i.over50cts*100/i.impressioncts) percent_pos_50above, p.purchasers purchasers, round(p.purchasers*100/i.impressioncts) conversion_rate_view_to_purchase, p.total_units total_purchased_units, round(p.total_demand,2) total_purchased_demand, ck.total_clickiers total_clickers, ck.total_clicks total_clicks, round(ck.total_clickiers*100/i.impressioncts) conversion_rate_view_to_click, round(ck.clickers_pos_top10*100/i.top10cts) conversion_rate_view_to_click_pos_top10, round(ck.clickers_pos_1020*100/i.ct1020) conversion_rate_view_to_click_pos_1020, round(ck.clickers_pos_2030*100/i.ct2030) conversion_rate_view_to_click_pos_2030, round(ck.clickers_pos_3050*100/i.ct3050) conversion_rate_view_to_click_pos_3050, round(ck.clickers_pos_above50cts/i.over50cts) conversion_rate_view_to_click_pos_50above, ca.cartadders total_cartadders, ca.qty total_qty_addedtocart, round(ca.cartadders*100/ck.total_clickiers) conversion_rate_click_to_cartadd, round(p.purchasers*100/ca.cartadders) conversion_rate_cartadd_to_purchase (select integer(eventid) eventid,count(distinct customerid) impressioncts, count(distinct if(position < 10, customerid,null) ) top10cts, count(distinct if(position between 10 , 19, customerid,null)) ct1020, count(distinct if(position between 20 , 29, customerid,null)) ct2030, count(distinct if(position between 30 , 49, customerid,null)) ct3050, count(distinct if(position >= 50, customerid,null)) over50cts clickstream.event_impression_20141013 grouping each eventid) bring together (select magenta_event_id,event_name,event_start_date,event_end_date zudw.event date(event_start_date)<='2014-10-13' , date(event_end_date)>='2014-10-13') e on e.magenta_event_id = i.eventid left bring together (select integer(eventid) eventid,count(distinct customerid) total_clickiers, count(customerid) total_clicks, count(distinct if(position < 10, customerid,null) ) clickers_pos_top10, count(distinct if(position between 10 , 19, customerid,null)) clickers_pos_1020, count(distinct if(position between 20 , 29, customerid,null)) clickers_pos_2030, count(distinct if(position between 30 , 49, customerid,null)) clickers_pos_3050, count(distinct if(position >= 50, customerid,null)) clickers_pos_above50cts clickstream.click_20141013 type='event' , (refererurl null or refererurl='http://www.zulily.com/' or instr(refererurl,'.zulily.com/new-today/')>0 or instr(refererurl,'.zulily.com/newtoday')>0 or instr(refererurl,'.zulily.com/endssoon')>0 or instr(refererurl,'.zulily.com/toys')>0 or instr(refererurl,'.zulily.com/ready')>0 or instr(refererurl,'.zulily.com/lastday/')>0 or instr(refererurl,'.zulily.com/shopbyage/')>0 or instr(refererurl,'.zulily.com/shopbycategory/')>0 or instr(refererurl,'.zulily.com/girls')>0 or instr(refererurl,'.zulily.com/boys')>0 or instr(refererurl,'.zulily.com/women')>0 or instr(refererurl,'.zulily.com/men')>0 or instr(refererurl,'.zulily.com/home')>0 or instr(refererurl,'.zulily.com/shoes')>0 or instr(refererurl,'.zulily.com/health')>0 or instr(refererurl,'.zulily.com/baby')>0) grouping each eventid ) ck on i.eventid=ck.eventid left bring together (select integer(eventid) eventid, count(distinct customerid) cartadders, sum(qty) qty clickstream.cartadd_20141013 grouping each eventid ) ca on i.eventid=ca.eventid left bring together (select integer(eventid) eventid, count(distinct customerid) purchasers, sum(qty) total_units, sum(price) total_demand clickstream.purchase_20141013 grouping each eventid ) p on i.eventid = p.eventid date(e.event_start_date)<='2014-10-13'

a couple of things:

if hitting urgent issue, best contact support. stackoverflow tends populated engineers check in infrequently , other community members may not able help urgent request. if provide job id (with project id), makes lot easier happening queries. if non-shuffled bring together (as in bring together each) queries taking long time, there may additional things can tune them. try using bring together each. while realize there intermittent issue bring together each, if you're seeing queries taking on order of hours, you're hitting resource limits bring together each can help with. verify don't have bring together explosion. is, create sure each key on left matches 1 key on right. people think case, there can empty or dummy value can cause multiple matches. try pushing clause within subselect filters happen before join, if possible. try making sure right hand tables (i.e. tables aren't in original clause) smaller tables.

with more info job, can happened in logs see why got connection error , previous queries long.

google-bigquery