Google BigQuery Numeric: 4 Key Types Simplified

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. 

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!