title: Avoiding and removing duplicates in bigquery
body: <p>We have started using bigquery for event logging from our games.</p>
  
  <p>We collect events from appengine nodes and enques them in chunks now and then which are placed in a task queue.</p>
  
  <p>A backend is then processing this queue and uploads events to bigquery. </p>
  
  <p>Today we store ca 60 million daily events from one of our games and 6 million from another.</p>
  
  <p>We have also made cron jobs to process these events to gather various gaming KPI's. (I.e. second day retention, active users, etc etc)</p>
  
  <p>Everything has gone quite smooth but we do now face a tricky problem !</p>
  
  <p>======== Question 1 ===============================================</p>
  
  <p>Due to some reason the deletion of the queue tasks fails now and then. Not very often but it happens and often in bursts. </p>
  
  <p>TransientFailureException is probably the cause ... I say probable since we are deleting process events in batch mode. I.e. ...</p>
  
  <pre><code>List&lt;Boolean&gt; Queue.deleteTask(List&lt;TashHandle&gt; taskstoDelete)
  </code></pre>
  
  <p>... so we actually don't know why we failed to delete a task. </p>
  
  <p>We have today added retry code that will try to delete those failed deletions again.</p>
  
  <p><strong>Is there a best practice to deal with this kind of problem?</strong></p>
  
  <p>========= Question 2 =======================================================</p>
  
  <p>Duplicate detection</p>
  
  <p>The following SQL succeds to find duplicates for or smaller game but exceeds resources for
  the bigger one.</p>
  
  <pre><code>SELECT DATE(ts) date, SUM(duplicates) - COUNT(duplicates) as duplicates
  FROM (
    SELECT ts, eventId, userId, count(*) duplicates
    FROM [analytics_davincigameserver.events_app1_v2_201308]
    GROUP EACH BY ts, eventId, userId
    HAVING duplicates &gt; 1
  )
  GROUP EACH BY date
  </code></pre>
  
  <p><strong>Is there a way to detect duplicates even for our bigger game?</strong> 
  I.e. a query that bigquery will be able to mangle our 60 million daily rows and locate duplicates.</p>
  
  <p>Thanks in advance!</p>