Option 1\u200a\u2014\u200aReplicate Auto-Incrementing Integers<\/h4>\n\n\n\n
Your first instinct is to try to replicate the incrementing keys by using the While it\u2019s nice to have an ever-increasing numeric identifier that encodes information about the size and distribution of your records, it comes at a cost.<\/p>\n\n\n\n To implement A better alternative might be to use a Universally Unique Identifier (UUID) by using the This option will return 32 hexadecimal digits in 5 groups e.g. <\/p>\n\n\n\n Here, you\u2019re basically guaranteed to get a unique ID, but the long string means slower joins across big datasets compared to the smaller integer keys.<\/p>\n\n\n\n My favourite option is to use a hash key to represent unique entities. The idea is to concatenate multiple columns that may uniquely identify a record (you can even choose all columns if you wish) into a string and running a cryptographic hash function on it. <\/p>\n\n\n\n Cryptographic hash functions have 2 extremely useful properties, which in the context of surrogate key generation, have little to do with security:<\/p>\n\n\n\n In Bigquery, you have a few functions to choose from: <\/p>\n\n\n\n Returns 16 bytes using the MD5 algorithm<\/a><\/p>\n\n\n\n \u200a<\/strong>Returns 20 bytes using the SHA1 algorithm<\/a>.<\/p>\n\n\n\n \u200a<\/strong>Returns 32 bytes using the SHA256 algorithm<\/a>.<\/p>\n\n\n\n Returns 64 bytes using the SHA512 algorithm<\/a>.<\/p>\n\n\n\n Returns a unique number <\/strong>using the open-source farmhash library<\/a>. <\/p>\n\n\n\n Although UUIDs and incremental keys give you unique keys, hash keys have some properties that make them shine in a data warehouse context.<\/p>\n\n\n\n So, please\u2026 Do yourself a favour and use hash keys.<\/p>\n\n\n\n When choosing a hash algorithm, there are some important considerations to take in mind:<\/p>\n\n\n\n 3. With that in mind, If you really care about join performance and don\u2019t mind staying in the BigQuery world, use the Thanks to the pigeonhole principle<\/a>, since we\u2019re squeezing a huge range of inputs into a small range of outputs (like 16 bytes, in the case of MD5), there is a small chance of 2 different inputs resolving to the same hash key. <\/p>\n\n\n\n What are the chances of that happening?<\/p>\n\n\n\n MD5 returns a 128-bit output, which means the probability of any 2 hashes colliding is In other words, on average, you\u2019d have to generate roughly 5.8 million records a second for the next 1,000 years<\/a><\/strong> before getting a collision.<\/p>\n\n\n\n Should you still test for collisions and have a fallback strategy? Yes.<\/p>\n\n\n\n Should it keep you up at night? No.<\/p>\n\n\n\n This post looked at the conceptual angle behind the choice of key types in data systems. <\/p>\n\n\n\n We explored the pros and cons of each alternative and showed how a simple MD5 hash can generate surrogate keys and meet many additional desirable requirements of modern data systems.<\/p>\n\n\n\nROW_NUMBER()<\/code> aggregation function<\/p>\n\n\n\nSELECT \n ROW_NUMBER() OVER() AS SurrogateKey,\n *\nFROM `mytable`<\/code><\/pre>\n\n\n\nROW_NUMBER()<\/code>, BigQuery needs to sort values at the root node of the execution tree<\/a>, which is limited by the amount of memory in one execution node.<\/p>\n\n\n\nOption 2\u2014 Generate a UUID<\/h4>\n\n\n\n
GENERATE_UUID()<\/code> function.<\/p>\n\n\n\nSELECT
GENERATE_UUID() AS SurrogateKey,
*
FROM `mytable`<\/code><\/pre>\n\n\n\n2e8815a9\u201346fc-48fe-a7a8-cc531da385b6<\/pre>\n\n\n\n
Option 3\u2014 Generate a Hash Key (Recommended)<\/h4>\n\n\n\n
MD5<\/h4>\n\n\n\n
EXPRESSION:<\/strong>\nMD5('CAT')\n\nOUTPUT (BASE64-ENCODED BYTES AS A STRING):<\/strong>\n'wBrhpfEi8lzlZ1+GAotTag=='<\/code><\/pre>\n\n\n\nSHA1\u200a<\/strong><\/h4>\n\n\n\n
EXPRESSION:<\/strong>\nSHA1('CAT')\n\nOUTPUT (BASE64-ENCODED BYTES AS A STRING):<\/strong> 'z5t3XCxERSAXjTDCZ0QAZsbv9ug='<\/code><\/pre>\n\n\n\nSHA256\u200a<\/strong><\/h4>\n\n\n\n
EXPRESSION:<\/strong>\nSHA_256('CAT')\n\nOUTPUT (BASE64-ENCODED BYTES AS A STRING):<\/strong>\n'FbiaVpR0JAphb5qU3QRbJxHURd3pVbYr9Ljyoq+vD2s='<\/code><\/pre>\n\n\n\nSHA512\u200a<\/strong><\/h4>\n\n\n\n
EXPRESSION:<\/strong>\nSHA_512('CAT')\n\nOUTPUT (BASE64-ENCODED BYTES AS A STRING):<\/strong>\n'AEMYdVzb+Pm7YdWZ0mgWotIaKcdPY82gaiNw0ZD+GivBjBWzSH5rRfbLH2ynG+kH1vzjjoZAkZ09qk6CYLQujQ=='<\/code><\/pre>\n\n\n\nFARM_FINGERPRINT\u200a<\/strong><\/h4>\n\n\n\n
EXPRESSION:<\/strong> FARM_FINGERPRINT('CAT')\nOUTPUT:<\/strong> -1069775538560612551<\/code><\/pre>\n\n\n\nWhat Key Should I Use?<\/h3>\n\n\n\n
Choosing a Hash Algorithm<\/h4>\n\n\n\n
MD5<\/code> is faster to generate than SHA1<\/code>, followed by SHA256<\/code> and SHA512<\/code><\/li>MD5<\/code> ) mean fewer bytes to process, which leads to faster join performance.<\/li><\/ol>\n\n\n\nFARM_FINGERPRINT<\/code> returns an INT<\/code>, which is faster to join on than BYTES<\/code> or STRING<\/code> types.<\/p>\n\n\n\nFARM_FINGERPRINT<\/code> function. However, if you build hybrid systems and want to generate keys across boundaries, you\u2019ll have a tough time generating farm fingerprints on every system.<\/p>\n\n\n\nMD5<\/code> is a nice balance between speed and compatibility with pretty much any technology as is my personal preference.<\/p>\n\n\n\n![\"\"](\"https:\/\/showmethedata.blog\/wp-content\/uploads\/2021\/01\/feature-major-key-1024x576.jpg\")
What About Hash Collisions?<\/h4>\n\n\n\n
1\/2\u00b9\u00b2\u2078<\/strong><\/code> but since we\u2019re storing all of the hashes, the birthday paradox takes effect and the probability becomes 1\/2\u2076\u2074<\/strong><\/code><\/p>\n\n\n\nConclusion<\/h2>\n\n\n\n