Google BigQuery CAST Functions 101: Syntax and Usage Simplified

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?
• What is Google BigQuery CAST Function?
• Google BigQuery CAST Functions
  • Google BigQuery CAST AS ARRAY
  • Google BigQuery CAST AS BIGNUMERIC
  • Google BigQuery CAST AS BOOL
  • Google BigQuery CAST AS BYTES
  • Google BigQuery CAST AS DATE
  • Google BigQuery CAST AS DATETIME
  • Google BigQuery CAST AS FLOAT64
  • Google BigQuery CAST AS INT64
  • Google BigQuery CAST AS INTERVAL
  • Google BigQuery CAST AS Numeric
  • Google BigQuery CAST AS STRING
  • Google BigQuery CAST AS STRUCT
  • Google BigQuery CAST AS TIME
  • Google BigQuery CAST AS TIMESTAMP
  • PARSE_BIGNUMERIC
  • PARSE_NUMERIC
  • SAFE_CAST
• Format Clause for CAST
  • Format BYTES as STRING
  • Format STRING as BYTES
• Conclusion

What is Google BigQuery?

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.

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:

• Google BigQuery CAST AS ARRAY
• Google BigQuery CAST AS BIGNUMERIC
• Google BigQuery CAST AS BOOL
• Google BigQuery CAST AS BYTES
• Google BigQuery CAST AS DATE
• Google BigQuery CAST AS DATETIME
• Google BigQuery CAST AS FLOAT64
• Google BigQuery CAST AS INT64
• Google BigQuery CAST AS INTERVAL
• Google BigQuery CAST AS Numeric
• Google BigQuery CAST AS STRING
• Google BigQuery CAST AS STRUCT
• Google BigQuery CAST AS TIME
• Google BigQuery CAST AS TIMESTAMP
• PARSE_BIGNUMERIC
• PARSE_NUMERIC
• SAFE_CAST

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.

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:

• BOOL
• INT64
• STRING

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:

• BYTES
• STRING

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:

• STRING

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:

• STRUCT

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!