BigQuery Hash Function: Easy Guide

Google BigQuery is the product offered by Google Cloud Platform, which is serverless, cost-effective, highly scalable data warehouse capabilities along with built-in Machine Learning features. Google Bigquery supports ANSI SQL, allowing users to execute SQL queries on massive datasets to manage business transactions, perform data analytics, and many more.

BigQuery is getting popular nowadays because of its easy-to-use and understand use cases for digital marketing and functions for developers’ needs. Hence, in today’s article, we will talk about BigQuery Hash Function. But before continuing, let’s talk about some of the most popular features of BigQuery:

Scalable Architecture: BigQuery has a scalable architecture and offers a petabyte scalable system that users can scale up and down as per load.

Faster Processing: Being a scalable architecture, BigQuery executes petabytes of data within the stipulated time and is more rapid than many conventional systems. BigQuery allows users to run analysis over millions of rows without worrying about scalability.

Fully Managed: BigQuery is a product of Google Cloud Platform, and thus it offers fully managed and serverless systems.

Security: BigQuery has the utmost security level that protects the data at rest and in flight. 

Real-time data ingestion: BigQuery can perform real-time data analysis, thereby making it famous across all the IoT and Transaction platforms.

Fault Tolerance: BigQuery offers replication that replicates data across multiple zones or regions. It ensures consistent data availability when the region/zones go down.

Table of Contents

1. What are BigQuery Hash Functions?
2. Working with BigQuery Hash Functions
   • Farm Fingerprint
   • MD5
   • SHA1
   • SHA256
   • SHA512
3. Conclusion

What are BigQuery Hash Functions?

BigQuery Hash Functions are the function that maps the data of arbitrary size to fixed-size values. The values generated by a hash function are known as hash values and cannot be reverted to their original form.

BigQuery Hash Functions are used in scenarios where columns contain sensitive data like SSN or PII information. A unique BigQuery hash key will encrypt these fields and make them available for use without worrying about privacy and security.

The various types of BigQuery Hash functions available are – 

1. Farm Fingerprint
2. MD5
3. SHA1
4. SHA256
5. SHA512

Working with BigQuery Hash Functions

Let us discuss each type of BigQuery Hash Function and its implementation in practice.

Farm Fingerprint

This BigQuery Hash Function hashes the input string and produces the hashed output in an integer format. For the Farm fingerprint function, the output values are fixed for a particular input, and they will never change. 

Consider the following table named test – 

id	name	status
1	foo	true
2	apple	false
3		true

The implementation of the hash function is as follow – 

SELECT 
  *,
  FARM_FINGERPRINT(CONCAT(CAST(id AS STRING), name, CAST(status AS STRING)))AS row_fingerprint
FROM test;

The output of the above query will be – 

id	name	status	row_fingerprint
1	foo	true	–1541654101129638711
2	apple	false	2794438866806483259
3		true	–4880158226897771312

MD5

The MD5 Algorithm hashes the input and provides the 16-byte hash output. The input needs to be either in String or in Bytes format. The MD5 hashing function returns the output, which is irreversible, and hence the user cannot revert the String to its original form.

Let us understand the MD5 algorithm with the following example – 

Consider the following table named test – 

id	name	status
1	foo	true
2	apple	false
3		true

The implementation of the MD5 hash function is as follow – 

SELECT 
  *,
  MD5(CONCAT(CAST(id AS STRING), name, CAST(status AS STRING)))AS row_md5
FROM test;

The output of the above query will be – 

id	name	status	row_md5
1	foo	true	0xD140D8F42AF3C2FA0015971E70DD06B3
2	apple	false	0xB4FAF9B0ACF42A2F10F98BFDF02E93FB
3		true	0x9C55B03BB2FB0CD1A85FD7C69CDB8CED

SHA1

This function computes the hash value from the input string or bytes with the SHA-1 Algorithm. The SHA1 algorithm returns the 20-byte value. The input needs to be either in String or in Bytes format. 

Let us understand the SHA1 algorithm with the following example – 

Consider the following table named test – 

id	name	status
1	foo	true
2	apple	false
3		true

The implementation of the SHA1 hash function is as follow – 

SELECT 
  *,
  SHA1(CONCAT(CAST(id AS STRING), name, CAST(status AS STRING)))AS row_sha1
FROM test;

The output of the above query will be – 

id	name	status	row_sha1
1	foo	true	0x8FE4A7341EE21255E7185213D7F2FF4946F6B702
2	apple	false	0x4AD4116E5C80C21F61D3F79C5EADF347C714B6AA
3		true	0x0B1F3EC0237596A2B05650BE32FD50FDBC3660E2

SHA256

This function computes the hash value from the input string or bytes with the SHA-1 Algorithm. The SHA256 Algorithm returns the 32-byte value. The input needs to be either in String or in Bytes format. SHA-256 is the most encrypted and secured compared to the ones mentioned above.

Let us understand the SHA256 algorithm with the following example – 

Consider the following table named test – 

id	name	status
1	foo	true
2	apple	false
3		true

The implementation of the SHA256 hash function is as follow – 

SELECT 
  *,
  SHA256(CONCAT(CAST(id AS STRING), name, CAST(status AS STRING)))AS row_sha256
FROM test;

The output of the above query will be – 

id	name	status	row_sha256
1	foo	true	0xC6157D297C8C2DE1B17F3BF46329A337E33B9374A00D4D42A60B4206441103BD
2	apple	false	0x3DB83CCE392A254A1012919FC1FD76A5A6725C214396D15A823F6C71E9C8A66D
3		true	0x8D03A108DF70F3C43568615EBCE7A2389364104DDCF215EBE6B4B4BB6E1297A8

SHA512

This function computes the hash value from the input string or bytes with the SHA-512 Algorithm. The SHA512 Algorithm returns the 64-byte value. The input needs to be either in String or in Bytes format. SHA-512 is the most encrypted and secured compared to the ones mentioned above.

Let us understand the SHA512 algorithm with the following example – 

Consider the following table named test – 

id	name	status
1	foo	true
2	apple	false
3		true

The implementation of the SHA512 hash function is as follow – 

SELECT 
  *,
  SHA512(CONCAT(CAST(id AS STRING), name, CAST(status AS STRING)))AS row_sha512
FROM test;

The output of the above query will be – 

id	name	status	row_sha512
1	foo	true	0xC7E82903791D4D4A713C38260B6D6D131E98B698718017D2F6BA5E3DC7691CF21D71AFAD1F9DA575F99D82935AC695D26219E22B1E503E98ADCB6B6D783E0BDF
2	apple	false	0x112B75E3E758D48A1B508C7F377A043BC848F6AC3ED35EF79F258E38E087EE8298AE890F5470469C0FB2B95091108359D4BAB3A38921DE77C15FFFAE4D05C9C1
3		true	0x551C2F6C5543F1E820C8F501D97A371E59107EC2113B976B7A43B64E91573C11C01FF5AB198BC1FF9F73FB1D6ED630C1E4770207685145A37185D4B4706441E0

Conclusion

This blog post discusses what hashing functions are and discusses their different types and how to use and implement them in Google BigQuery. We have also demonstrated how the output value for the same input changes with a change in hash functions.

Extraction of complicated data from a variety of data sources, such as databases, CRMs, project management tools, streaming services, and marketing platforms, and loading it into Google BigQuery can be difficult. This is when a simple solution like Hevo can come in handy!

Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 100+ Data Sources including 40+ Free Sources, into your Data Warehouse such as Google BigQuery to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin? SIGN UP for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience with Google BigQuery Hash in the comments section below!