As opposed to the spreadsheet applications you may have heard of, like Google and Microsoft Excel Sheets, Google BigQuery offers a structure and a progressive query system to assist you with performing deep analysis on your data and extract significant information from it. However, the Structured Query Language (SQL) powers Google BigQuery’s query system – SQL is a programming language made unequivocally for database management systems and is especially helpful in taking care of structured data.
Other than the adaptability and performance, what makes Google BigQuery so well-known is its convenience. As Google BigQuery utilizes SQL as its query language, which is used by many popular databases and warehouse frameworks, it gets easier for many Data Engineers and Designers to get acquainted with it.
In this article, you will get to know about Google BigQuery CAST Functions, CAST functions key types, usage, and example. Google BigQuery function is one of the system syntaxes used to fetch data from a query table for analysis. A function syntax is a predefined set of clauses that accomplish a complex action. For instance, COUNT, SUM, and so on are some examples of the function syntax. You will get to know more about that in the subsequent sections.
Table of Contents
What is Google BigQuery?
Image Source
Google BigQuery is a fully managed Data Warehouse with built-in features such as Machine Learning, Geospatial Analysis, and Business Intelligence to assist you in managing and analyzing your data. It has a serverless architecture that allows you to use SQL queries to address your organization’s needs with no requirement for infrastructure maintenance. Furthermore, Google BigQuery’s scalable and distributed analytical engine lets you query terabytes of data in seconds and petabytes of data in minutes.
Google BigQuery uses a column-based architecture to store various types of data. This makes it a great fit for OLAP (Online Analytical Processing) and data streaming into Google BigQuery tables.
To know more about Google BigQuery, visit this link.
What is Google BigQuery CAST Function?
The CAST functions permit you to change between several Data Types in BigQuery. They are beneficial for guaranteeing that arguments in a comparison function have a similar data type. In one of our previous posts, we talked about BigQuery data types. BigQuery supports various data types. Each data type has several functions and operations that apply to them with restricted procedures.
Example of a CAST Function syntax:
CAST (expr AS typename)
Or
CAST(expression AS output_data_type)
The type name or data type in this instance are:
INT64, NUMERIC, BIGNUMERIC, FLOAT64, BOOL, STRING, BYTES, DATE, DATETIME, TIME, TIMESTAMP, ARRAY, STRUCT.
You can learn more about these data types in the post above.
For instance, assuming you need to form a sentence, so you must first change overall data (counting numbers) into a STRING data type. Using various data types isn’t suggested and can bring about a syntax error.
Examples of CAST Functions and their descriptions
| Functions | Description |
| BOOLEAN( <numeric_expr> ) | Returns true if <numeric_expr> is not 0 and not NULL.Returns false if <numeric_expr> is 0.
Returns NULL if <numeric_expr> is NULL. |
| FLOAT( expr ) | Returns expr as a double. The expr can be a string like ‘45.78’, but the function returns NULL for non-numeric values. |
| HEX_STRING( numeric_expr ) | Returns numeric_expr as a hexadecimal string. |
| INTEGER( expr ) | Returns expr as a 64-bit integer. This function expects an integer string, such as ’45’, and returns NULL for non-integer values. |
| STRING( numeric_expr ) | Returns numeric_expr as a string. |
Casting functions between data types that don’t effectively map from the first value to the target domain creates runtime errors. For instance, casting BYTES to STRING where the byte sequence isn’t legitimate, UTF-8 results in a runtime error. A few projects can incorporate a format clause, which gives directions to how to lead the CAST. For instance, you could instruct a CAST to change a sequence of bytes over to a BASE64-encoded string rather than a UTF-8-encoded string. The design of the format clause is extraordinary to each type of CAST, and more data is accessible in the segment for that CAST.
To know more about Google BigQuery CAST Functions, visit this link.
Hevo Data helps you directly transfer data from 100+ data sources (including 30+ free sources) to Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
Hevo takes care of all your data preprocessing needs required to set up the integration and lets you focus on key business activities and draw a much more powerful insight on how to generate more Leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.
Get Started with Hevo for Free
Check out what makes Hevo amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner 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, E-Mail, 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!
Google BigQuery CAST Functions
In Google BigQuery, the CAST function allows you to convert between various Data Types. Below are some of the commonly used CAST functions:
1) Google BigQuery CAST AS ARRAY
You can cast ARRAY in BigQuery. It can be used to represent the data type called ARRAY.
Syntax:
CAST(expression AS ARRAY<element_type>)
However, to cast from array to array, both element types must be the same array type.
![Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery]()
Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Learn the best practices and considerations for setting up high-performance ETL to BigQuery
2) Google BigQuery CAST AS BIGNUMERIC
Another data type that can be on Google BigQuery is BIGNUMERIC. It can be used to call the following data types expressions:
- BIGNUMERIC
- FLOAT64
- INT64
- NUMERIC
- STRING
Syntax:
CAST(expression AS BIGNUMERIC)
Example:
SELECT
str_number,
CAST(TRIM(str_number) AS INT64) AS number
FROM
(
SELECT
'3' AS str_number
UNION ALL
( SELECT
'8' AS str_number)
UNION ALL
( SELECT
'23' AS str_number)
) AS table_3
3) Google BigQuery CAST AS BOOL
The BOOL data type can also be cast on Google BigQuery as BOOL. It can be used to represent the following data types expressions:
Syntax:
CAST(expression AS BOOL)
Example:
SELECT
CAST(0 AS BOOL) AS zero_to_bool,
CAST(5 AS BOOL) AS five_to_bool
SELECT
SAFE_CAST('true' AS BOOL) AS true_to_bool,
SAFE_CAST('false' AS BOOL) AS false_to_bool,
SAFE_CAST('apples' AS BOOL) AS apples_to_bool
4) Google BigQuery CAST AS BYTES
The bytes data type can also be cast on Google BigQuery as BYTES. It can be used to represent the following data types expression:
Syntax:
CAST(expression AS BYTES [format_clause])
5) Google BigQuery CAST AS DATE
The date data type can also be cast on Google BigQuery as DATE. It can be used to represent the following data types expressions:
- DATETIME
- STRING
- TIME
- TIMESTAMP
Syntax:
CAST(expression AS DATE [format_clause])
6) Google BigQuery CAST AS DATETIME
The DATETIME data type can also be cast on Google BigQuery as DATETIME. It can be used to represent the following data types expressions:
- DATETIME
- STRING
- TIME
- TIMESTAMP
Syntax:
CAST(expression AS DATETIME [format_clause])
Example:
SELECT
CAST('2021-19-20 12:52:01' AS DATETIME) AS str_to_datetime
7) Google BigQuery CAST AS FLOAT64
The float64 data type can also be cast on Google BigQuery as FLOAT64. It can be used to represent the following data types expressions:
- BIGNUMERIC
- FLOAT64
- INT64
- NUMERIC
- STRING
Syntax:
CAST(expression AS FLOAT64)
8) Google BigQuery CAST AS INT64
The int64 data type can also be cast on Google BigQuery as INT64. It can be used to represent the following data types expressions:
- BIGNUMERIC
- BOOL
- FLOAT64
- INT64
- NUMERIC
- STRING
Syntax:
CAST(expression AS INT64)
Examples:
SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;
9) Google BigQuery CAST AS INTERVAL
The interval data type can also be cast on Google BigQuery as INTERVAL. It can be used to represent the following data type expressions:
Syntax:
CAST(expression AS INTERVAL)
Example:
SELECT input, CAST(input AS INTERVAL) AS output
FROM UNNEST([
'1-2 3 10:20:30.456',
'1-2',
'10:20:30',
'P1Y2M3D',
'PT10H20M30,456S'
]) input
10) Google BigQuery CAST AS NUMERIC
The numeric data type can also be cast on Google BigQuery as NUMERIC. It can be used to represent the following data types expressions:
- BIGNUMERIC
- FLOAT64
- INT64
- NUMERIC
- STRING
Syntax:
CAST(expression AS NUMERIC)
11) Google BigQuery CAST AS STRING
The string data type can also be cast on Google BigQuery as STRING. It can be used to represent the following data types expressions:
- BIGNUMERIC
- BOOL
- BYTES
- DATE
- DATETIME
- FLOAT64
- INT64
- NUMERIC
- STRING
- TIME
- TIMESTAMP
Syntax:
CAST(expression AS STRING [format_clause [AT TIME ZONE timezone_expr]])
Examples:
SELECT CAST(CURRENT_DATE() AS STRING) AS current_date
SELECT CAST(CURRENT_DATE() AS STRING FORMAT 'DAY') AS current_day
SELECT CAST(
TIMESTAMP '2008-12-25 00:00:00+00:00'
AS STRING FORMAT 'YYYY-MM-DD HH24:MI: SS TZH: TZM') AS date_time_to_string
SELECT CAST(
TIMESTAMP '2008-12-25 00:00:00+00:00'
AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM'
AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string
12) Google BigQuery CAST AS STRUCT
The struct data type can also be cast on Google BigQuery as STRUCT. It can be used to represent the following data type expression:
Syntax:
CAST(expression AS STRUCT)
13) Google BigQuery CAST AS TIME
The time data type can also be cast on Google BigQuery as TIME. It can be used to represent the following data types expressions:
- DATETIME
- STRING
- TIME
- TIMESTAMP
When you need to cast an expression of a particular data type to another, the format clause can be used to offer directives for carrying out the cast, especially if the expression is a string.
Syntax:
CAST(expression AS TIME [format_clause])
14) Google BigQuery CAST AS TIMESTAMP
The timestamp data type can also be cast on Google BigQuery as TIMESTAMP. It can be used to represent the following data types expressions:
- DATETIME
- STRING
- TIME
- TIMESTAMP
When you need to cast an expression of a specific data type to another, the format clause can offer directives for carrying out the cast, especially if the expression is a string.
Syntax:
CAST(expression AS TIMESTAMP [format_clause [AT TIME ZONE timezone_expr]])
The timestamp format clause comes with an added clause known as the AT TIME ZONE timezone_expr. The clause can be used to state a particular time zone during formatting. As long as you don’t include this optional clause, the system uses your current time zone.
Examples:
Take a look at some examples of a string-formatted timestamp as a timestamp:
SELECT CAST("2021-09-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp
SELECT CAST("06/09/2021 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE 'America/Los_Angeles') AS as_timestamp
SELECT CAST("06/09/2021 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE '00') AS as_timestamp
SELECT CAST('06/09/2021 17:00:53.110 +00' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS TZH') AS as_timestamp
15) PARSE_BIGNUMERIC
This data type expression is used to convert a string into a BiGNUMERIC value. One rule is that the string is numeric literal and should not pass the maximum precision or the BIGNUMERIC type range unless you get an error. If the digits number exceeds 38, the next BIGNUMERIC value starts from zero to 38 digits after the decimal.
Syntax:
PARSE_BIGNUMERIC(string_expression)
Examples:
SELECT PARSE_BIGNUMERIC("123.45") AS parsed
SELECT PARSE_BIGNUMERIC("123.456E37") AS parsed
SELECT PARSE_BIGNUMERIC("1.123456789012345678901234567890123456789") as parsed
This function is the same as the BIGNUMERIC cast function, but the PARSE_BIGNUMERIC function allows the following in the string and only agrees to string inputs.
SELECT PARSE_BIGNUMERIC("12.34e-1-") as parsed;
SELECT PARSE_BIGNUMERIC(" - 12.34 ") as parsed;
SELECT PARSE_BIGNUMERIC(" 1,2,,3,.45 + ") as parsed;
SELECT PARSE_BIGNUMERIC(".1234 ") as parsed;
16) PARSE_NUMERIC
Change a string to NUMERIC. The numeric exacting embedded in the string should not surpass the highest precision or the NUMERIC value range or display an error. If the digits after the decimal point reach nine, the subsequent NUMERIC value adjusts half away from zero to nine numbers after the decimal point.
Syntax:
PARSE_NUMERIC(string_expression)
Examples:
SELECT PARSE_NUMERIC("123.45") AS parsed
SELECT PARSE_NUMERIC("12.34E27") as parsed
SELECT PARSE_NUMERIC("1.0123456789") as parsed
17) SAFE_CAST
A query may fail while using CAST, and that occurs when Google BigQuery can’t perform the cast, the following error is generated by the query:
Syntax:
SAFE_CAST(expression AS typename [format_clause])
Examples:
SELECT CAST("apple" AS INT64) AS not_a_number;
SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;
SELECT
safe_CAST(date1 AS DATE) AS date1,
safe_CAST(date2 AS DATE) AS date2,
FORMAT_DATE('%b %d,%Y', safe_CAST(date2 AS DATE)) AS formatted_date2
FROM
(
SELECT
'20/9/21' AS date1,
'2021-09-20' AS date2
) AS table_1
We used SAFE_CAST here so that if the CAST to date failed, the whole query would not fail. In the example above, date1 returns a NULL value since it’s not in the right format. Similar rules apply for converting STRINGs to DATETIME, TIMESTAMP, and TIME:
When casting STRING -> DATETIME, the string must be in the format YYYY:MM: DD HH:MM: SS
SELECT
CAST('2020-12-25 03:22:01' AS DATETIME) AS str_to_datetime
When casting STRING -> TIMESTAMP, the string must be in a format YYYY:MM: DD HH:MM: SS [timestamp] (where the timestamp defaults to UTC if not specified).
SELECT
CAST('2020-12-25 03:22:01-5:00' AS TIMESTAMP) AS str_to_timestamp
When casting STRING -> TIME the string must be in the format HH:MM:SS
select cast('15:03:11' as TIME) as str_to_time
Format Clause for CAST
This can be utilized in some CAST functions. It is used to offer guidelines on how to carry out a cast. For instance, you could train a cast to change a succession of bytes over to a BASE64-encoded string rather than a UTF-8-encoded string. The configuration statement incorporates a format model. This model can contain elements joined together to form a format string.
Syntax:
format_clauses:
formats format_model
format_models:
format_string_expressions
1) Format BYTES as STRING
Syntax:
CAST(bytes_expression AS STRING FORMAT format_string_expression)
Example:
SELECT CAST(b'x48x65x6cx6cx6f' AS STRING FORMAT 'ASCII') AS bytes_to_string;
2) Format STRING as BYTES
Syntax:
CAST(string_expression AS BYTES FORMAT format_string_expression)
Example:
SELECT CAST('Hello' AS BYTES FORMAT 'ASCII') AS string_to_bytes
Conclusion
This article gave you a brief introduction to Google BigQuery CAST Functions. It also provided in-depth knowledge about Google BigQuery CAST functions syntax along with their examples. Now, you can easily utilize the CAST Functions to convert between data types in Google BigQuery with ease.
Visit our Website to Explore Hevo
Businesses can use automated platforms like Hevo Data to set the integration and handle the ETL process. It helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you a hassle-free experience.
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 of learning about Google BigQuery CAST Functions in the comments section below!