CODEWITHSUNDEEP
google-bigquery
NewPy
NewPy

Reputation: 663

find unique column as row index in BigQuery

As I know there is no primary key in Google BigQuery, so what is the best why to find unique column to use as row index?

I can think of using:

SELECT count(*) as totalnumber, count(distinct columnname) as uniquecolumn from `db`

But is there any better way?

Thank you

Upvotes: 1

Views: 2060

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

You can use HASH functions for this - for example FARM_FINGERPRINT() 

#standardSQL
SELECT *, FARM_FINGERPRINT(TO_JSON_STRING(t)) AS uniquecolumn 
FROM `project.dataset.table` t

FARM_FINGERPRINT() computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library. The output of this function for a particular input will never change, so you can use it as a sort of row index

Upvotes: 4

Elliott Brossard
Elliott Brossard

Reputation: 33745

You can use the GENERATE_UUID function:

SELECT *, GENERATE_UUID() AS uniquecolumn
FROM `dataset.table`

Upvotes: 0

Related Questions