Conversion rules in Dataflow SQL

"Conversion" includes, but is not limited to, casting and coercion.

  • Casting is explicit conversion and uses the CAST() function.
  • Coercion is implicit conversion, which Dataflow SQL performs automatically given the conditions described in this page.

The following table summarizes all possible CAST and coercion possibilities for Dataflow SQL data types. "Coercion To" applies to all expressions of a given data type (for example, a column).

From Type CAST to Coercion To
INT64 INT64
FLOAT64
STRING
 FLOAT64
FLOAT64 FLOAT64
  
BOOL BOOL
  
STRING INT64
STRING
BYTES
TIMESTAMP
  
BYTES BYTES
STRING
  
TIMESTAMP STRING
TIMESTAMP
  
ARRAY ARRAY  
STRUCT STRUCT  

Casting

Syntax:

CAST(expr AS typename)

Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type.

Example:

CAST(x=1 AS STRING)

This example results in "true" if x is 1, "false" for any other non-NULL value, and NULL if x is NULL.

Casts between supported types that do not successfully map from the original value to the target domain produce runtime errors. For example, casting BYTES to STRING where the byte sequence is not valid UTF-8 results in a runtime error.

When casting an expression x of the following types, these rules apply:

From To Rules when casting x
INT64 FLOAT64 Returns a close but potentially not exact FLOAT64 value.
FLOAT64 STRING Returns an approximate string representation.
STRING BYTES STRINGs are cast to BYTES using UTF-8 encoding. For example, the STRING "©", when cast to BYTES, would become a 2-byte sequence with the hex values C2 and A9.
BYTES STRING Returns x interpreted as a UTF-8 STRING.
For example, the BYTES literal b'\xc2\xa9', when cast to STRING, is interpreted as UTF-8 and becomes the unicode character "©".
An error occurs if x is not valid UTF-8.
ARRAY ARRAY Must be the exact same ARRAY type.
STRUCT STRUCT Allowed if the following conditions are met:
  1. The two STRUCTs have the same number of fields.
  2. The original STRUCT field types can be explicitly cast to the corresponding target STRUCT field types (as defined by field order, not field name).

Coercion

If needed to match function signatures, Dataflow SQL coerces the result type of an expression to another type. For example, a function func() is defined to take a single argument of type INT64. If an expression is used as an argument that has a result type of FLOAT64, then the result of the expression is coerced to INT64 type before func() is computed.