CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
JoseKilo
JoseKilo

Reputation: 2453

Is there an Autoincrement in BigQuery?

Is there something like an AUTO_INCREMENT, SERIAL, IDENTITY or sequence in BigQuery ?

I'm aware of ROW_NUMBER https://cloud.google.com/bigquery/query-reference#row-number
But I want to persist a generated unique ID for every row in my table.

Upvotes: 20

Views: 41553

Answers (1)

Adam Lydick
Adam Lydick

Reputation: 1122

BigQuery does not have a notion of row key generation at load time. You could rewrite the table with a query to generate arbitrary keys for your rows.

As you noted, 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 unpartitioned window function over everything).

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.

In the linked answer, Felipe constructs a composite key, which may also work for you, if the combination of your keys is distinct.

Upvotes: 18

Related Questions