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?
Image Source
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.
Image Source
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:
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.
Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDKs, 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.
SIGN UP HERE FOR A 14-DAY FREE TRIAL!
What are the Operations Supported by Google BigQuery Numeric?
Google BigQuery Numeric Data Types support different types of operations. Let’s discuss them:
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!