0

I need to generate a auto incremental numbers into a table just like a sequence in Oracle. I tried ROW_NUMBER() and GENERATE_UUID(), generate_array() , which is not helpful.

Our table will be a daily load and the column values should have auto increment. the table data we are talking will be millions. Can anyone help on it.

Regards, Guru

Guru
  • 11
  • 3
  • This has been answered in detail here: https://stackoverflow.com/questions/39390342/is-there-an-autoincrement-in-bigquery – Brad Jul 30 '22 at 11:44

1 Answers1

0

BigQuery doesn't have an auto increment sequence number like most OLTP vendors (e.g. oracle, sql). I would suggest simply hashing your business key (e.g. SHA1(concat(key1, key2, etc)) as MyKey) to create a PK and leave it at that.

If you're dead set on an auto incrementing integer then you'd need to build a function where you pass two values:

  1. ID (the current max id from your destination table)
  2. Rowcount (number of rows in your source table)

Your function could then return a list incrementing IDs which you'd need to add your source prior to or during the insert.

That's my best guess on how to do this.

pmo511
  • 569
  • 3
  • 9