Google BigQuery has quickly become one of the most popular Serverless Data Warehouses for companies around the globe. Owned by Google Cloud, BigQuery is offered as a Platform as a Service (PaaS) to companies that want a proper space for analyzing their data.
BigQuery supports queries using ANSI SQL, which is one of the main reasons why it’s so popular. If your company deals in large amounts of data, you might already be using BigQuery. With the Machine Learning capabilities that BigQuery offers, users can easily get a comprehensive analysis of their data.
More importantly, BigQuery also gives you a whole host of mathematical functions that you can use. BigQuery Math functions are particularly useful when you are dealing with large chunks of data, and need to quickly and efficiently calculate important figures from large datasets. This article will list some of the commonly used BigQuery Math functions. So, read along and gain insights into BigQuery Math functions.
Table of Contents
Prerequisites
Before you start using BigQuery Math functions, it’s important for you to know two important things:
- If any input parameters are NULL, then the return is also NULL.
- Secondly, you will get NaN if any of the arguments is also NaN.
Understanding Rules of Function Calls
Before we go into detail about Google BigQuery Math functions, there are a few important things that you should know about functions, operators, and expressions.
Unless explicitly specified, all functions follow the below-mentioned rules:
- The default time zone for all functions is UTC, specifically for functions that are time zone sensitive.
- More importantly, if an operand is a floating-point and the other is of a numeric type, both operands are then converted automatically to FLOAT64 before evaluation.
- Apart from the IS operator, any operand that’s NULL will automatically return the NULL result.
To read more about function call rules, refer to BigQuery Documentation.
Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources including 40+ Free Sources. It is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination.
Hevo loads the data onto the desired Data Warehouse/destination such as Google BigQuery in real-time and enriches the data and transforms it into an analysis-ready form without having to write a single line of code. Its completely automated pipeline, fault-tolerant, and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.
GET STARTED WITH HEVO FOR FREE
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Simplify your BigQuery ETL and Data Analysis with Hevo today!
SIGN UP HERE FOR A 14-DAY FREE TRIAL!
Commonly Used BigQuery Math Functions
Now that you know the basics, let’s talk about some of the most popular BigQuery Math functions. Few are listed below:
1) ABS (X)
This function is designed to compute the absolute value of a dataset. For instance, if the argument being analyzed is an integer, and the output value can’t be represented as an integer too, this function will return an error. Keep in mind that this only happens for the highest negative input value.
Image Source
So, for instance, if X is 20, then ABS (X) will also be 20. However, if X is -20, then ABS (X) will still be 20.
Learn more about the BigQuery Math function – ABS(X) here.
2) SIGN (X)
Another popular math function used in BigQuery is the SIGN (X) function. This one returns a -1, 0, or a +1 for negative, positive, or any zero arguments. In the case of a floating-point argument, the function doesn’t discriminate between a negative or a positive zero.
So, for instance, if X = 12, then SIGN (X) will be a +1. However, if X = -12, then SIGN (X) will be +1. But, if X = 0, then SIGN (X) will still be 0.
Learn more about the BigQuery Math function – SIGN(X) here.
3) IS_INF
This is another popular math function used in BigQuery, which simply returns TRUE if the value is either negative infinity or positive. So, for instance, if X is +inf, then IS_INF (X) will be TRUE. The same is returned if the value is -inf. However, if the value is 10, for example, then it’s going to return a FALSE.
Learn more about the BigQuery Math function – IS_INF here.
4) IS_NAN (X)
If the value provided is a NaN value, then IS_NAN (X) will return TRUE. Thus, if X= NaN, it’ll return TRUE. On the other hand, if the value is 15, for example, IS_NAN (X) will be false.
Learn more about the BigQuery Math function – IS_NAN (X) here.
5) GREATEST
This is a simple function that returns the largest value between X1, X2, …, XN, based on the conventional < comparison. However, if any of the values of X is NULL, the value returns are also NULL.
So, if you run the function GREATEST (2, 6, 1), it’s going to give you 6 as the answer.
Learn more about the BigQuery Math function – GREATEST here.
6) LEAST
Conversely, this function returns the smallest value of X based on the values available. Similarly, if any value is NULL, then the return value is also NULL.
For example, if you run LEAST (2, 6, 3), the value it’s going to return is 2.
Learn more about the BigQuery Math function – LEAST here.
7) DIV
A popular function used by data analysts using BigQuery is the DIV function. The syntax is generally DIV (X, Y), and it’s used to get the result of dividing the integer of X with Y. As basic math goes, if you try to divide by zero, it’ll return an error. Similarly, if you divide by a negative value, such as -1, it’ll probably overflow.
If X is 24 and Y is assigned a value of 4, then DIV (X, Y) will return a value of 6. Similarly, if X is given a value of 0 and Y is given any other value, such as 12, the result will be 0. On the other hand, if X is assigned a value of 26 but Y is given a value of 0, then DIV (X, Y) will return an Error.
Learn more about the BigQuery Math function – DIV here.
8) SAFE_DIVIDE
Building on the previous function, you can also use SAFE_DIVIDE. This is similar to the DIV (X, Y) function, but in case an error occurs, the result will simply be NULL. This is ideal for spotting issues, especially when making larger calculations.
For instance, if you run SAFE_DIVIDE (25, 0), then the result is going to be NULL. However, if you run a conventional division, such as SAFE_DIVIDE (25, 5), then it’ll just give you 5 as a result.
Learn more about the BigQuery Math function – SAFE_DIVIDE here.
9) LOG10
Image Source
For computing logarithms with a base 10, you will use the LOG10 function. If you use LOG10 (100), then it’ll give you 2.0 as an answer. On the other hand, if X is less than or equal to 0, the function will simply give an error in return.
Learn more about the BigQuery Math function – LOG10 here.
10) SAFE_MULTIPLY
Another commonly used function is SAFE_MULTIPLY (X, Y). This function is similar to the multiplication operator, which is denoted by a “*” and returns a NULL if there’s an overflow. So, if you run SAFE_MULTIPLY (10, 2), the result will be 20.
Learn more about the BigQuery Math function – SAFE_MULTIPLY here.
11) SAFE_ADD
SAFE_ADD is similar to the operator for adding numbers, which is “+” and as the previous function, will return NULL if there’s an overflow. This is ideal for making simple additions. For instance, if you run SAFE_ADD (2, 2), the result will be 4.
Learn more about the BigQuery Math function – SAFE_ADD here.
12) SAFE_SUBSTRACT
Just like other functions with the SAFE prefix, this one also returns the value of Y when it’s subtracted from X. It’s similar to the subtraction operator but will return a NULL value if there’s an overflow.
So, if you run SAFE_SUBSTRACT (8, 4), it’ll give you 4 as an answer.
Learn more about the BigQuery Math function – SAFE_SUBSTRACT here.
13) MOD
One of the most popular BigQuery math functions is the Modulo function. It simply gives you the remainder from the division of X by Y. The value that it returns carries the same sign as X. However, if Y is 0, then it’ll return 0 as an answer.
For instance, if you use MOD (25, 12), the return will be 1. However, if you replace Y with a 0, the MOD function will return an Error.
Learn more about the BigQuery Math function – MOD here.
14) TRUNC
The TRUNC function is used to round X to the closest integer whose absolute value isn’t greater than the absolute value assigned to X. If however, N is also defined, such as TRUNC ( X [ , N]), then TRUNC simply acts like the ROUND function (defined below).
However, unlike the ROUND function, TRUNC will always round down to zero, and will never overflow.
So, for instance, if you use TRUNC (2.3), it’ll give you 2.0 as an answer. Similarly, if you run TRUNC (-4.4), it’ll round it down to -4.4.
Learn more about the BigQuery Math function – TRUNC here.
15) CEIL (X)
The ceiling function is used to get the smallest integral value that’s equal to or higher than X. For instance, if you run CEIL (2.5), it’ll give you 3.0 as an answer.
Similarly, if you run CEIL (-2.5), it’ll return -2.0 as an answer. Another function that does the same job is CEILING (X).
Learn more about the BigQuery Math function – CEIL (X) here.
16) COS (X)
Image Source
To calculate the cosine value of X, specifically, if X is given in radians, you should run the COS (X). If X is NaN, then COS (X) will also be NaN.
Learn more about the BigQuery Math function – COS (X) here.
17) SIN (X)
Another function used in trigonometry is the SIN (X) function. If X is specified in radians, you can easily calculate the sine of X using this function.
Learn more about the BigQuery Math function – SIN (X) here.
18) TAN (X)
Similarly, if X is provided in radians, you can use TAN (X) to compute the tangent of X. If there’s an overflow, this function returns an error.
Learn more about the BigQuery Math function – TAN (X) here.
Conclusion
In this article, you understood and explored the commonly used BigQuery Math functions. You also learned their syntax and arguments. BigQuery is great for running calculations on large sums of data. If you have to run computations on large amounts of data, these are some of the many BigQuery Math functions that you can use. Obviously, this list is not exhaustive, and people who are familiar with SQL queries can run all other functions that they want. However, if you want to pull your data from multiple sources – using a no-code data pipeline such as Hevo is an excellent idea.
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 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 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 BigQuery Math Functions in the comments section below!
Skilled in freelance writing within the data industry, Najam is passionate about simplifying the complexities of data integration and data analysis through informative content for those delving deeper into these subjects.