4

I need to add row numbers to a large (ca. billion rows) dataset in BigQuery. When I try:

SELECT 
  *
  ROW_NUMBER() OVER (ORDER BY d_arf DESC) plarf 
FROM [trigram.trigrams8]

I get "Resources exceeded during query execution.", because an analytic/window function needs to fit in one node.

How can I add row numbers to a large dataset in BigQuery?

Community
  • 1
  • 1
uner
  • 131
  • 1
  • 2
  • 7

1 Answers1

7

You didn't give me a working query, so I had to create my own, so you'll need to translate it to your own problem space. Also I'm not sure why do you want to give a row number to each row in such a huge dataset, but challenge accepted:

SELECT a.enc, plarf, plarf+COALESCE(INTEGER(sumc), (0)) row_num
FROM (
  SELECT STRING(year)+STRING(month)+STRING(mother_age)+state enc, 
         ROW_NUMBER() OVER (PARTITION BY year ORDER BY enc) plarf,
         year
  FROM [publicdata:samples.natality] ) a
LEFT JOIN (
  SELECT COUNT(*) c, year+1 year, SUM(c) OVER(ORDER BY year) sumc
  FROM [publicdata:samples.natality] 
  GROUP BY year
) b
ON a.year=b.year
  • I want to do a ROW_NUMBER() OVER(), but I can't because there are too many elements.
  • Having an OVER(PARTITION) fixes this issue, but now each partition starts with 1.
  • But that's OK. On another subquery I will count how many elements are there in each partition.
  • And the surrounding query will take the row_number of each partition, and add it to the local-to-the-partition count.
  • Ta da.
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • Wow, neat solution, thanks! I wanted to be able to quickly poke at certain parts of the data when ordered by one column. This seems to solve it. – uner Oct 24 '15 at 17:51
  • 1
    I like this approach although one problem I have with this query is that over a large dataset partitioned by territory (say) is that the generated row_num is liable to generate duplicates whenever the partitions are of similar sizes. The data I am working with is a daily data set which does not allow for partitioning by any date components. – Ian Lewis Jan 03 '18 at 17:15