IDENTITY or AUTO_INCREMENT columns in Google BigQuery

We often use column functions like AUTO_INCREMENT, SERIAL, IDENTITY or sequence, Is there an equivalent for it in BigQuery ?

No, BigQuery does not support row key generation at load time, so, none of the those functions will work in BigQuery. You will generate those auto increment values yourself and then provide to BigQuery. Here are some methods to achieve it:

Generate Unique keys using a Row_Number:

ROW_NUMBER would give you a unique index for each row, but you may hit size limits for particularly large tables (since you'd need an un-partitioned window function over everything)

Select empid, Name, 
ROW_NUMBER() over (Order by DOB ASC) as UniqueKey 
from MyDataset.Employee;

Generate Unique Keys using GENERATE_UUID function in Bigquery:

If you can tolerate a larger string key, you might consider generating a UUID for each row (which can be done randomly and doesn't require coordination with the rest of your data). If you're using Standard SQL (and you should!) the GENERATE_UUID() function will accomplish this

GENERATE_UUID()

nVector

posted on 18 May 20