BigQueryで整数(INT64型)や浮動小数点数(FLOAT64型)をバイト列(BYTES型)にキャストする

整数(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形式と呼ばれる多くの環境で使われているもののようです

• IEEE 754 - Wikipedia

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でバイナリデータをどうこうしようと考えること自体が間違っているような気がしてきました

BigQuery で _TABLE_SUFFIX をビューやWITH句の外側から指定する

BigQueryのクエリでテーブル名を書くときに末尾に*を書いておくと、その部分が_TABLE_SUFFIXというカラムに入っていてWHEREの条件に書いて使うテーブルを絞り込むことができます
BigQueryではテーブル名の末尾に日付を書いておいて、_TABLE_SUFFIXで絞り込むのがよく使われています

参照したバイト数によって課金されるので、できるだけ余分なテーブルを参照するのを避けるとコストを節約できます
以下の画像のように_TABLE_SUFFIXの条件を外すと処理されるバイト数が増えています

BigQueryではパラメータ付きのViewやWITH句が書けないので、ビューの外側からパラメータとして_TABLE_SUFFIXの絞り込みを書くのはできないかと思っていたのですが、やる方法を教えてもらったので詳しい挙動を調べてブログに書いておきます
ビューとWITH句でだいたい同じ挙動のようなので以下ではWITH句で説明していきます

方法

必要なのは、_TABLE_SUFFIXをカラムとして外側に渡すことです
ただし_TABLE_で始まるカラムをユーザーが作るのは許されていないのでなんらかの適当な名前にしておく必要があります

外側に渡した_TABLE_SUFFIXのカラムに対して条件をかけることで参照されるテーブルを絞ることができます

制約

いくつか試したところ、ビューやWITH句のクエリの中にLIMITがあったり_TABLE_SUFFIXをPARTITION BYの条件に含まないウィンドウ関数を書いたりすると、外側から条件で絞っても全部使われてしまうようです(他にもダメなものはありそう)
当然かも知れませんが、ビューやWITH句のクエリの時点で全テーブルのデータが影響してくる場合はダメみたいです

利用例

単純にSELECTしてそれぞれの行のデータを取ってくる

カラムを絞ったり追加したり、値に演算をしたり、UNNEST()したりするだけの場合この記事の方法が役に立ちそうです

GROUP BYやウィンドウ関数で集計する場合

ビューやWITH句の外側に_TABLE_SUFFIXを出さなければならない都合上、GROUP BYに_TABLE_SUFFIXのカラムを含める必要があってクエリが制限されます

同様にウィンドウ関数を使う場合にはPARTITION BYに_TABLE_SUFFIXを含める必要があります

テーブルをJOINする場合

個人的には少し意外だったのですが、テーブルをJOINする場合でもJOINしたビューやWITH句の外側で_TABLE_SUFFIXによる絞り込みが効くようです
以下は同じ月と日のデータをJOINしているだけの特に意味のないクエリですが、_TABLE_SUFFIXによる絞り込みでバイト数が抑えられているのがわかります

まとめ

試してみたところ意外といろいろなタイプのクエリでビューの外側からでも_TABLE_SUFFIXの指定ができました
試してはいませんがパーティションで分けられたテーブルの_PARTITIONTIMEでも同じようなことはできそうです

一つ注意点としては、ビューで*を使って大量のテーブルをまとめた場合_TABLE_SUFFIXを指定し忘れると意図せず大量のテーブルにアクセスしてしまうので実行前にどれぐらいのバイト数か確認したほうがよさそうです。
あらかじめ一ヶ月や一年以内のテーブルに_TABLE_SUFFIXを絞る条件を書いておいたり、クエリを実行するときの最大のバイト数に制限を書けたほうが安全かもしれません

上で出てきたクエリ例

SELECTしてくるだけ

#standardSQL
WITH data AS (
  SELECT
    *,
    _TABLE_SUFFIX AS table_suffix
  FROM `bigquery-public-data.noaa_gsod.gsod*`
)

SELECT
  *
FROM data
WHERE table_suffix = '2018'

GROUP BYで集計

#standardSQL
WITH data AS (
  SELECT
    _TABLE_SUFFIX AS table_suffix,
    mo AS month,
    COUNT(*) AS count
  FROM `bigquery-public-data.noaa_gsod.gsod*`
  GROUP BY table_suffix, month
)

SELECT
  month,
  count
FROM data
WHERE table_suffix = '2018'

ウィンドウ関数で集計

#standardSQL
WITH data AS (
  SELECT
    _TABLE_SUFFIX AS table_suffix,
    *,
    COUNT(*) OVER (PARTITION BY _TABLE_SUFFIX, mo)
  FROM `bigquery-public-data.noaa_gsod.gsod*`
)

SELECT
  *
FROM data
WHERE table_suffix = '2018'

JOINが含まれるクエリ

#standardSQL
WITH data AS (
  SELECT
    *,
    _TABLE_SUFFIX AS table_suffix
  FROM `bigquery-public-data.noaa_gsod.gsod*`
), join_test AS (
  SELECT
    data1.*,
    data2.year AS year2,
    data2.table_suffix AS table_suffix2
  FROM data AS data1
  JOIN data AS data2
  ON data1.mo = data2.mo
    AND data1.da = data2.da
)

SELECT
  *
FROM join_test
WHERE table_suffix = '2018'
AND table_suffix2 = '2017'