Google BigQuery Numeric: 4 Key Types Simplified

Google BigQuery is one of the most preferred Cloud Data Warehousing solutions available today. It allows enterprises to store their data in the Cloud where they can get much more scalability. Google BigQuery also provides its users with Data Analytics features that they can use to extract insights from their data for decision making. 

To use Google BigQuery, you will need to move your data from different data sources into your BigQuery Data Warehouse. This will mean that mapping data to the correct data types in Google BigQuery will be very important. Google BigQuery Numeric supports different data types, and these data types support different types of operations. 

Most people find it difficult to understand the different data types supported in Google BigQuery. They also experience challenges on how to map data from their source data types to the Google BigQuery Data Types. In this article, we will be discussing Google BigQuery Numeric Types in detail. 

Table of Contents

• What is Google BigQuery?
• 4 Key Google BigQuery Numeric Data Types
  • Integer (INT64)
  • Numeric (NUMERIC DECIMAL)
  • BigNumeric
  • Float (FLOAT 64)
• What are the Operations Supported by Google BigQuery Numeric?
  • Arithmetic/Mathematical Operations
  • Comparisons
  • Standard Compliant Floating Point Division
  • Safe Functions
• Conclusion

What is Google BigQuery?

Google BigQuery is a highly scalable, serverless Data Warehouse with a built-in Query Engine. It was developed by Google, hence, it uses the processing power of Google’s infrastructure. The Query Engine can run SQL Queries on terabytes of data within seconds, and petabytes within minutes. Google BigQuery gives you this performance without the need to maintain the infrastructure or rebuild or create Indexes. 

Google BigQuery’s speed and scalability make it suitable for use in processing huge datasets. It also comes with built-in Machine Learning capabilities that can help you to understand your data better. 

With Google BigQuery, you can achieve the following:

• Democratize insights with a scalable and secure platform that comes with Machine Learning features. 
• Improve business decision-making from data using a Multi-cloud and Flexible Analytics solution. 
• Adapt to data of any size, from bytes to petabytes, with no operational overhead. 
• Run large-scale analytics.  

Google BigQuery also allows you to create Dashboards and Reports that you can use to analyze your data and gain meaningful insights from it. It is also a powerful tool for Real-time Data Analytics. 

4 Key Google BigQuery Numeric Data Types

Google BigQuery Numeric Data Types store numbers. There are also different types of operations that can be applied to these numbers. 

The Google BigQuery Numeric Data Type can be broken down into four sub-categories:

• Integer (INT 64)
• Numeric (NUMERIC DECIMAL)
• BigNumeric
• Float (FLOAT 64)

1) Integer (INT 64)

The Integer data type stores numbers in the range of -2^63 to 2^63 – 1. That is from:

Min Value: -9,223,372,036,854,775,808
Max Value: 9,223,372,036,854,775,807,

This represents the maximum capacity of 64-bit Integers. Integers don’t store decimals or fractional components. 

The following SQL Query demonstrates how to return a value as an Integer data type:

SELECT cast('34524' as INT64) int;

The query will return 34524 as an Integer. 

2) Numeric (NUMERIC DECIMAL)

This Google BigQuery Numeric type is similar to DECIMAL and it stores values with up to 38 Decimal Digits of precision and 9 Decimal Digits at scale. It takes a:

Min Value: - 9.9999999999999999999999999999999999999E+29
Max Value: 9.9999999999999999999999999999999999999E+29

The following query demonstrates how to return a value as a Numeric Data Type:

SELECT cast('7.3E11' as NUMERIC) numeric;

This will return 730000000000.

3) BigNumeric

Just like the Numeric Data Type, the BigNumeric Data Type stores values with no fractional numbers. The two types, that is, Numeric and BigNumeric are good for exact calculations, hence, they are used in financial calculations. 

The BigNumeric BigQuery Numeric Data Type has a:

Min Value: -5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38

Max Value: 5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38

The following SQL Query demonstrates how to return a value as a BigNumeric Data Type:

SELECT cast('7.3E37' as BIGNUMERIC) bignumeric;

The query will return 73000000000000000000000000000000000000.

4) Float (Float 64)

The Float Google BigQuery Numeric Data Type stores values with approximate numeric values and decimal or fractional components. The following SQL Query shows how to return a value as a float:

SELECT cast('7.3621' as FLOAT64) float;

It will return 7.3621. 

What are the Operations Supported by Google BigQuery Numeric?

Google BigQuery Numeric Data Types support different types of operations. Let’s discuss them:

• Arithmetic/Mathematical Operations
• Comparisons
• Standard Compliant Floating Point Division
• Safe Functions

1) Arithmetic/Mathematical Operations

BigQuery Numeric Data Types support basic mathematical operations like addition, subtraction, multiplication, and more. Thus, you can apply mathematical operations to the columns with Numeric Types. 

The following query shows how to perform the division arithmetic operation on an Integer and a Floating-point Value:

SELECT (height/weight) AS height_weight_ratio 
FROM table1;

2) Comparisons

BigQuery Numeric Data Types support comparison operations. The comparison operations are performed using comparison operators, that is, (<, <=, >, >=, != ,<>). The comparison operators are very important when you want to use the WHERE Clause to filter the results of a query.  

The following Query Demonstrates how to apply a comparison operator to a BigQuery Numeric Data Type:

SELECT * FROM table1 
WHERE Height < 90;

3) Standard Compliant Floating Point Division

A division operation where the denominator is a zero (0) returns the division by zero error. Infinite values also give infinite output, which results in an overflow error. To avoid these errors when running your SQL Queries, you are advised to use special functions to run the queries. 

For example, the IEEE_divide function returns NaN (not a number) after a division by zero. This means that your SQL Query will run without returning errors. The following query shows how to use this function in your query:

SELECT *,(IEEE_Divide (height,weight)) AS height_weight_ratio 
FROM table1;

4) Safe Functions

The purpose of Safe Functions is to return NULL rather than an error for undefined mathematical operations. To apply this function to your query, just prefix the query with the SAFE Keyword. 

Consider the following example:

SELECT SAFE.LOG(10, -2) AS Log1, 
SAFE.LOG(10, 2) AS Log2;

In the above example, we are trying to find the logarithm of a negative number, which should return an error since there are no defined logarithms for negative numbers. However, the fact that we have prefixed the command with the SAFE keyword will return a NULL rather than an error. 

The query will return null for Log1 and 3.3219280948873626 for Log2.

Those are some of the operations supported by the Google BigQuery Numeric Types. 

Conclusion

You learned the fundamentals of Google BigQuery in this article. You also learned about Google BigQuery Numeric Data Types and their important features, along with the many operations that may be performed on Google BigQuery Numeric Types.

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 Numeric Types in the comments section below!