如何解决BigQuery中SHA256生成BYTES转STRING的报错问题？

The Root Cause #

When you use CAST(field_name AS STRING) on a BYTE column in BigQuery, the system tries to decode the byte sequence as UTF-8 encoded text. However, SHA256 generates a 256-bit (32-byte) binary hash—most of these bytes don't map to valid UTF-8 code points. UTF-8 has strict rules for valid byte sequences (like specific leading bits for multi-byte characters), so the hash's random-looking binary data almost always violates these rules, triggering the Invalid cast of bytes to UTF8 string error.

Solutions #

Absolutely, this problem is solvable—you just need to use the right functions to convert the binary hash into a string representation that makes sense for binary data:

• Convert to a hexadecimal string (most common for hashes)
  Use the TO_HEX() function, which converts each byte in the hash to a two-character hexadecimal string. This gives you a human-readable 64-character string (since 32 bytes × 2 hex chars = 64 chars):

  SELECT TO_HEX(your_sha256_byte_field) AS hash_string
  FROM your_table
  

• Convert to a Base64 string
  If you prefer a shorter string representation, use TO_BASE64() to encode the binary hash into Base64 format:

  SELECT TO_BASE64(your_sha256_byte_field) AS hash_base64
  FROM your_table
  

Both functions avoid trying to interpret the binary data as UTF-8, so they won't throw encoding errors. Choose hexadecimal if you need a standard hash display format, or Base64 if you want a more compact string.

内容的提问来源于stack exchange，提问作者Praxiteles