Bigquery return duplicated rows as well as wrong count

1
votes

I am running BigQuery directly on user interface, the query results are duplicated (each row has one more copy) get 120 results. I also test select count(*) with same statement, still get 120 as the result. Even download result as csv file to local disk, the data is still duplicated. I have looked around but unable to get any helpful points. Any suggestions?

  1. id 1
  2. name John
  3. state VA
  4. keywords aging
  5. title
  6. budget_start 2016-01-30
  7. budget_end 2018-03-31
  8. total_cost 250000.0

id is required, others can be null; budget_start and budget_end are date type, total_cost is float, while other columns are string

2
google-bigquery
I have checked my data table, no duplicated rows at all. - Stephen
show query and describe a little table that you querying - Mikhail Berlyant
SELECT id, name, state, keywords, title,total_cost FROM [xxx table] where budget_start > 2015-1-1 and budget_end > 2017-12-30 and total_cost > 1000000 and state contains 'MO' Order by id DESC; - Stephen
have you considered case when duplicates are actually already in your table rather than generated by your query? - Mikhail Berlyant
Thanks for your insightful suggestion! I actually find out the problem, I upload table twice (find some error during first import, correct that error and load again) leading to a table with duplicated content (which I thought is replaced but actually merged) - Stephen

2 Answers

1
votes

From your query - it is obvious that you are using BigQuery Legacy SQL
The specifics of output for Legacy SQL is that it is gets flatten
This means that if you have nested rows - they will be flatten

See below example 

#legacySQL
SELECT id, NEST(x) AS xs
FROM 
(SELECT 1 AS id, 2 AS x),
(SELECT 1 AS id, 3 AS x),
(SELECT 1 AS id, 4 AS x),
(SELECT 2 AS id, 5 AS x),
(SELECT 2 AS id, 6 AS x)
GROUP BY id

It creates two rows as below 

Row id  xs  
1   1   [2,3,4]  
2   2   [5,6]

You can check this by running this query with destination table and then preview this table

Now - if you run this same query in Web UI (while in legacy SQL) - you will get 5 rows instead of "expected" 2 rows 

Row id  xs   
1   1   2    
2   1   3    
3   1   4    
4   2   5    
5   2   6

Please also note: that flattening happens only on final outer level - subquery do not gets flattened. For example below query will give you count = 2 as you would expect 

#legacySQL
SELECT COUNT(1) AS cnt FROM (
  SELECT id, NEST(x) AS xs
  FROM 
  (SELECT 1 AS id, 2 AS x),
  (SELECT 1 AS id, 3 AS x),
  (SELECT 1 AS id, 4 AS x),
  (SELECT 2 AS id, 5 AS x),
  (SELECT 2 AS id, 6 AS x)
  GROUP BY id
)  


Row cnt  
1   2

So, to address it - I recommend you to migrate to BigQuery Standard SQL

See equivalent example for BigQuery Standard SQL 

#standardSQL
WITH `yourTable` AS (
  SELECT 1 AS id, [2,3,4] AS xs UNION ALL
  SELECT 2, [5,6]
)
SELECT * FROM `yourTable`

with output of just two rows, as one would expected 

Row id  xs   
1   1   2    
        3    
        4    
2   2   5    
        6
0
votes

Thank you Mikhail so so much for your insightful suggestion! I actually find out the problem, I import same table from Google Storage twice (find some error during first import, correct that error and load again) leading to a table with duplicated content (which I thought is replaced but actually merged) which I did not realize