如何在BigQuery中查看INT64的二进制表示？转换为BYTES类型可行吗？

Hey there! Let's tackle your two BigQuery questions step by step—converting INT64 to BYTES, and viewing the binary representation of INT64 values.

Converting INT64 to BYTES in BigQuery #

BigQuery has a built-in function TO_BYTES() that handles this conversion directly. For INT64 values, it converts the number to its 64-bit two's complement binary representation (using big-endian byte order, the standard for network protocols).

Basic Usage #

-- Convert a positive INT64 to BYTES
SELECT TO_BYTES(CAST(42 AS INT64)) AS int64_to_bytes;

-- Convert a negative INT64 to BYTES (uses two's complement)
SELECT TO_BYTES(CAST(-42 AS INT64)) AS negative_int64_to_bytes;

If you need an unsigned integer representation instead, cast the INT64 to UINT64 first:

-- Convert INT64 to unsigned BYTES
SELECT TO_BYTES(CAST(42 AS UINT64)) AS uint64_to_bytes;

Viewing the Binary Representation of INT64 Values #

BigQuery doesn’t have a direct function to output binary strings from integers, but you can chain existing functions plus a simple custom UDF to get the full binary representation. Here's the workflow:

1. Convert the INT64 to BYTES using TO_BYTES()
2. Convert the BYTES to a hexadecimal string with TO_HEX()
3. Use a custom function to translate each hex character to its 4-bit binary equivalent

Step-by-Step Implementation #

First, create a temporary UDF to convert hex strings to binary:

CREATE TEMP FUNCTION HexToBinary(hex_str STRING) AS (
  STRING_AGG(
    CASE SUBSTR(hex_str, pos, 1)
      WHEN '0' THEN '0000' WHEN '1' THEN '0001' WHEN '2' THEN '0010' WHEN '3' THEN '0011'
      WHEN '4' THEN '0100' WHEN '5' THEN '0101' WHEN '6' THEN '0110' WHEN '7' THEN '0111'
      WHEN '8' THEN '1000' WHEN '9' THEN '1001' WHEN 'A' THEN '1010' WHEN 'B' THEN '1011'
      WHEN 'C' THEN '1100' WHEN 'D' THEN '1101' WHEN 'E' THEN '1110' WHEN 'F' THEN '1111'
      WHEN 'a' THEN '1010' WHEN 'b' THEN '1011' WHEN 'c' THEN '1100' WHEN 'd' THEN '1101'
      WHEN 'e' THEN '1110' WHEN 'f' THEN '1111'
    END, ''
  )
  FROM UNNEST(GENERATE_ARRAY(1, LENGTH(hex_str))) AS pos
);

Then use it to get the binary string of your INT64 value:

SELECT
  42 AS original_int64,
  TO_BYTES(CAST(42 AS INT64)) AS byte_representation,
  TO_HEX(TO_BYTES(CAST(42 AS INT64))) AS hex_representation,
  HexToBinary(TO_HEX(TO_BYTES(CAST(42 AS INT64)))) AS binary_representation;

Example Output #

For the value 42, you’ll get:

• byte_representation: b'\x00\x00\x00\x00\x00\x00\x00*'
• hex_representation: 000000000000002A
• binary_representation: 0000000000000000000000000000000000000000000000000000000000101010

For negative values like -42, the binary will be the 64-bit two's complement:
1111111111111111111111111111111111111111111111111111111111010110

Key Notes #

• TO_BYTES() uses big-endian byte order for integer conversions.
• INT64 conversions use two's complement, which is the standard for signed integers in modern systems.
• The temporary UDF works for any hex string, so you can reuse it for other BYTES-to-binary conversions.

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