See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
Get the guideType casting in BigQuery refers to the process of converting data from one data type to another. This transformation is made possible using the CAST function. The general syntax for CAST is: CAST(expression AS target_type []). Here, the expression represents the value or column you want to convert, target_type is the data type you want to convert the expression to, and format_string is an optional parameter used for specific data type conversions like formatting dates or parsing strings.
CAST("123" AS INTEGER) -- Converts the string "123" to the integer 123
The above code example demonstrates how to convert a string to an integer using the CAST function in BigQuery. The string "123" is converted to the integer 123.
In BigQuery, you can convert a date string to a DATE data type using the CAST function. This is particularly useful when you need to perform date-specific operations on your data.
CAST("2024-06-05" AS DATE) -- Converts the date string "2024-06-05" to a DATE data type
The code above shows how to convert a date string "2024-06-05" to a DATE data type using the CAST function in BigQuery.
BigQuery allows you to format a timestamp while casting. This can be done by using the CAST function along with a format string.
CAST(TIMESTAMP("2024-06-05 14:35:00") AS STRING FORMAT '%Y-%m-%d %H:%M') -- Formats the timestamp as "2024-06-05 14:35" (excluding seconds)
The above code example demonstrates how to format a timestamp while casting in BigQuery. The timestamp "2024-06-05 14:35:00" is formatted as "2024-06-05 14:35", excluding seconds.
While casting can be a powerful tool for data transformation, it's important to be aware that it can sometimes lead to data loss or unexpected results if the conversion is not possible. For instance, casting a string containing non-numeric characters to an integer will result in an error. To handle potential conversion errors gracefully, BigQuery offers a SAFE_CAST function that returns NULL instead of raising an error if the cast fails.
SAFE_CAST("abc" AS INTEGER) -- Returns NULL as the string "abc" cannot be converted to an integer
The code above shows the use of the SAFE_CAST function in BigQuery. When attempting to convert the string "abc" to an integer, the function returns NULL instead of raising an error.
Type casting in BigQuery can be used in various scenarios such as data cleaning and transformation, data integration, and customizing output formatting. For instance, you might cast a string representing a numeric value to an integer for calculations. When combining data from different sources, casting might be necessary to ensure all data adheres to a consistent data type for seamless analysis. Additionally, you can use casting with a format string to format the output of your queries in a specific way, such as presenting dates in a desired format.
CAST("123.45" AS FLOAT64) -- Converts the string "123.45" to a float for calculations
The code example above illustrates a use case of type casting in BigQuery where a string representing a numeric value is cast to a float for calculations.
Built for data teams, designed for everyone so you can get more from your data stack.
© 2024 Secoda, Inc. All rights reserved.
By using this website, you accept our Terms of Use and Privacy Policy.
