CODEWITHSUNDEEP
castinggoogle-bigquery
Ted
Ted

Reputation: 1062

BigQuery: Cast INT64 to BYTES (or binary representation)

How can I see the binary representation of an INT64 in BigQuery? Casting it to the BYTES type would also work.

Upvotes: 3

Views: 5421

Answers (2)

Matt Martin
Matt Martin

Reputation: 168

I believe you could use the ToHex UDF shown here: https://stackoverflow.com/a/51600210/4966331

Then pass the string output by that UDF to BigQuery's builtin FROM_HEX

For example, this command:

bq --location US query \
  --nouse_legacy_sql \
'
CREATE TEMP FUNCTION ToHex(x INT64) AS (
  (SELECT STRING_AGG(FORMAT("%02x", x >> (byte * 8) & 0xff), "" ORDER BY byte DESC)
   FROM UNNEST(GENERATE_ARRAY(0, 7)) AS byte)
);

SELECT
  FROM_HEX(ToHex(1)) = b"\x00\x00\x00\x00\x00\x00\x00\x01",
  FROM_HEX(ToHex(2)) = b"\x00\x00\x00\x00\x00\x00\x00\x02",
  FROM_HEX(ToHex(256)) = b"\x00\x00\x00\x00\x00\x00\x01\x00",
'

returns:

+------+------+------+
| f0_  | f1_  | f2_  |
+------+------+------+
| true | true | true |
+------+------+------+

Upvotes: 0

dsesto
dsesto

Reputation: 8178

As stated by @Elliot Brossard, it is a matter of implementation how the INT64 type is represented.

Moreover, having a look at the documentation, you will be able to see the available conversion types in BigQuery. INT64 can only cast to BOOL, FLOAT64, INT64 and STRING.

If you really need the Bytes or Binary representation of an integer value in BigQuery, you will have to either pre-process it programatically and add a new column containing those values, or post-process it programatically when you retrieve your data.

Upvotes: 1

Related Questions