Get Unique Identifier in BigQuery Table

Question

Use case

I need a stable Unique Identifier in a BigQuery table which is inserted into by Google. Thus I can not insert a UUID or similiar along with each row entry (but this is exactly what I need). I am aware of BigQuery's GENERATE_UUID() however this would return a new UUID on each query, which is not desired.

My question

How can I get an unique identifier for each row in a BigQuery table which is stable across multiple queries?

Answer 1

You can use combination of TO_JSON_STRING() with one of hash functions like FARM_FINGERPRINT

SELECT FARM_FINGERPRINT(TO_JSON_STRING(t)), *
FROM `project.dataset.table` t