How to parse 8-byte double-precision number (little endian) into float64 in BigQuery?

Question

I have a string written as 8-byte double-precision number with little endian and would like to convert it with float64 so that BigQuery can handle it as a number.

# example
from: hex(little endian): EC51B81E852B4340

to: float64: 38.34

Is there an smart solution for this?

Thank you.

Answer 1

I have only the first step that you can resort to JavaScript capability. I searched but didn't find a library that you can directly reference from BigQuery. You'll have to find a library and upload it to GCS to reference it. I also found some code snippet to do the bytes -> double encoding but not sure about their quality.

CREATE TEMP FUNCTION hex_to_float(x BYTES) RETURNS FLOAT64
LANGUAGE js
AS """
  // Call a JS library to translate byte array to double
""";

SELECT hex_to_float(FROM_HEX('EC51B81E852B4340'));