整数(INT64
型)や浮動小数点数(FLOAT64型)をBYTES
型にしたかったので自前で処理をUDFとして書きました
BigQueryのCAST
関数でBYTES
型にキャストでできるのはBYTES
型自身とSTRING
型に限られているので、他の型を変換したいときには自分で処理を書く必要があります
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#conversion-rules
整数型について
BigQueryの整数のバイト列の表現は普通の2の補数表現のものと同じようなので、論理シフトを使って整数を1バイトずつ見ていけばBYTES
型に変換できます
整数型をバイト列で表す場合、下位のバイトが先に配置されるリトルエンディアンとその逆のビッグエンディアンがあるので一応両方の場合の関数を考えます
INT64
から BYTES
への変換
CODE_POINTS_TO_BYTES関数を使うと0から255の整数値の配列をBYTES
へと変換してくれるので、INT64
の値を1バイトずつに区切ってこの関数を使います
#standardSQL CREATE TEMP FUNCTION convertInt64ToBytesAsLittleEndian(n INT64) AS ( CODE_POINTS_TO_BYTES( ARRAY( SELECT n >> i * 8 & 255 FROM UNNEST(GENERATE_ARRAY(0, 7)) AS i ORDER BY i ) ) ); CREATE TEMP FUNCTION convertInt64ToBytesAsBigEndian(n INT64) AS ( CODE_POINTS_TO_BYTES( ARRAY( SELECT n >> i * 8 & 255 FROM UNNEST(GENERATE_ARRAY(0, 7)) AS i ORDER BY i DESC ) ) );
上のUDFでは1から8バイト目までをループしていますが、8バイト分の処理しかないので下の関数のように全部展開して書いてもいいです
CREATE TEMP FUNCTION convertInt64ToBytesAsLittleEndian(n INT64) AS ( CODE_POINTS_TO_BYTES([ n >> 0 & 255, n >> 8 & 255, n >> 16 & 255, n >> 24 & 255, n >> 32 & 255, n >> 40 & 255, n >> 48 & 255, n >> 56 & 255 ]) );
試しにこれを使って変換して更に16進表記で表すとSELECT TO_HEX(convertInt64ToBytesAsLittleEndian(2018))
がe207000000000000
となるのでちゃんと動いていそうです
BYTES
から INT64
への変換
TO_CODE_POINTS関数を使うとBYTES
を1バイトずつのINT64
の配列に変換することができるのでこれを使って計算します
#standardSQL CREATE TEMP FUNCTION convertBytesToInt64AsLittleEndian(b BYTES) AS ( IF( LENGTH(b) != 8, ERROR('castBytesToInt64AsLittleEndian: Argument is not 8 bytes.'), (SELECT SUM(n << i * 8) FROM UNNEST(TO_CODE_POINTS(b)) AS n WITH OFFSET i) ) ); CREATE TEMP FUNCTION convertBytesToInt64AsBigEndian(b BYTES) AS ( IF( LENGTH(b) != 8, ERROR('castBytesToInt64AsBigEndian: Argument is not 8 bytes.'), (SELECT SUM(n << (7 - i) * 8) FROM UNNEST(TO_CODE_POINTS(b)) AS n WITH OFFSET i) ) );
先程SELECT TO_HEX(convertInt64ToBytesAsLittleEndian(2018))
した結果のe207000000000000
を逆変換します
SELECT convertBytesToInt64AsLittleEndian(FROM_HEX('e207000000000000'))
すると元の2018に戻りました
浮動小数点数型について
BigQueryのSQLの関数だけだと浮動小数点数をバイト列にするのはちょっと大変そうなのでJavaScript UDFを使ってJavaScript側で変換します
JavaScriptの浮動小数点数のバイト列の表現はIEEE 754形式と呼ばれる多くの環境で使われているもののようです
FLOAT64
から BYTES
への変換
JavaScript側で一度Float64Array
に入れてからUint8Array
として解釈し直して、BigQuery側でBYTES
型に変換します
#standardSQL CREATE TEMP FUNCTION _convertFloat64ToUint8ArrayAsLittleEndian(f FLOAT64) RETURNS ARRAY<INT64> LANGUAGE js AS """ return Array.from(new Uint8Array(new Float64Array([f]).buffer)); """; CREATE TEMP FUNCTION convertFloat64ToBytesAsLittleEndian(f Float64) AS ( CODE_POINTS_TO_BYTES(_convertFloat64ToUint8ArrayAsLittleEndian(f)) ); CREATE TEMP FUNCTION convertFloat64ToBytesAsBigEndian(f Float64) AS ( REVERSE(CODE_POINTS_TO_BYTES(_convertFloat64ToUint8ArrayAsLittleEndian(f))) );
SELECT TO_HEX(convertFloat64ToBytesAsLittleEndian(1.25))
をしたら000000000000f43f
になりました
BYTES
から FLOAT64
への変換
逆向きの変換を同様にやればよいです
#standardSQL CREATE TEMP FUNCTION _convertUint8ArrayToFloat64AsLittleEndian(x ARRAY<INT64>) RETURNS FLOAT64 LANGUAGE js AS """ return new Float64Array(new Uint8Array(x).buffer)[0]; """; CREATE TEMP FUNCTION convertBytesToFloat64AsLittleEndian(b BYTES) AS ( _convertUint8ArrayToFloat64AsLittleEndian(TO_CODE_POINTS(b)) ); CREATE TEMP FUNCTION convertBytesToFloat64AsBigEndian(b BYTES) AS ( _convertUint8ArrayToFloat64AsLittleEndian(TO_CODE_POINTS(REVERSE(b))) );
まとめ
BigQueryのINT64
とFLOAT64
をバイト列に変換するUDFを書きました
ちょっと書き換えれば他のサイズの整数や浮動小数点数の型にも使えるかも
ここまでやっておいてなんですが、BigQueryでバイナリデータをどうこうしようと考えること自体が間違っているような気がしてきました