CODEWITHSUNDEEP
google-bigquery
rachit
rachit

Reputation: 93

Add new id column and fill incremental value to already existing table

I need to add an id column to my table which is having around 500cr rows and update it with incremental value. Please suggest what is the best way of doing it. I tried running a query to update the value and store in new table but the query failed after 6 hrs saying timeout. Please help.

Upvotes: 4

Views: 7841

Answers (2)

Klimentij Bulygin
Klimentij Bulygin

Reputation: 23

I found this to work better when you need to generate numeric ids:

RANK() OVER(ORDER BY GENERATE_UUID())

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

row_number() over() is quite expensive!
You should try row_number() over(partition by some_field) to redistribute numbering across partitions.
Here, you should pick partition field which has reasonable number of distinct values in your table - let's say 1K or so.
After this "first round" - you will have compound key: partition_field + incremental number that you can use as a primary/foreign key or if you want you can then combine further those two into one key

Upvotes: 5

Related Questions