ちょっとマニアックな BigQuery の話(2)

はじめに

Google Cloud Platform (GCP) の有力サービスのひとつである BigQuery は、サーバレスかつ分散処理を行ってくれるので、テラバイト級のデータを処理するクエリでも秒単位で結果が返ってきます。

今回は、前回(ちょっとマニアックな BigQuery の話)に引き続きちょっとマニアックなBigQuery の操作を行いたいと思います。

BigQuery について「ビッグデータ処理が高速で可能である。」というのは有名な話ですが、具体的にどれくらいの量の処理まで可能なのでしょうか。今回、BigQuery 単独(パーテーションなど使わず)でどこまでの量の処理が可能なのか挑戦してみました。

セットアップ

まず、XY平面上に200万個の座標をランダムに出力します。以下のような感じで表示されます。

この一つ一つの座標に対して、距離(ユークリッド距離)が近いものから20個ずつを出力していきます。

ということで、200万個×20個=4000万行のデータを作成する処理になります。

こちらの計算量が大きい理由としては、2乗オーダーで計算量が増えていくということです。つまり、件数が2倍になると4倍計算が増えるというイメージです。データが増えるととんでもなく計算が増えていくことになります。

計算について

以下のように、コードを書きました。

WITH points AS (
  SELECT *
	FROM `data-lab-169616.test_coconala_20230118.TSNE20230118`
	WHERE index IS NOT NULL
  LIMIT 350000 # データ数を絞る
), point_distances AS (
  SELECT
    point1.index AS point1_id,
    point2.index AS point2_id,
    POW(point1.x - point2.x, 2) + POW(point1.y - point2.y, 2) AS distance
  FROM
    points AS point1
  CROSS JOIN
    points AS point2
  WHERE
    # 最終的に出る近いポイントはほぼ距離が1未満なのでここで枝刈り
    ABS(point1.x - point2.x) < 1
    AND ABS(point1.y - point2.y) < 1
    AND point1.index != point2.index
), nearest_neighbors AS (
  SELECT
    point1_id,
    FORMAT("%T", array_agg(point2_id IGNORE NULLS ORDER BY distance LIMIT 20)) AS closest_points, # 距離の小さい順にIDを並べる
    FORMAT("%T", array_agg(sqrt(distance) IGNORE NULLS ORDER BY distance LIMIT 20)) AS closest_distances # 距離の小さい順に距離を並べる
  FROM
    point_distances
  GROUP BY
    point1_id
)

SELECT *
FROM nearest_neighbors
ORDER BY point1_id asc

結果について

こちらで、35万件まで計算できました!以下のような感じで出力されます。やはり2乗オーダーで増えていくので、さすがのBigQueryでも、全件(200万件)の計算は難しかったようです。

実務上で、どうしてもこれ以上やらなければならないときは、今回であれば、nearest_neighbors AS くらいまでで中間テーブルを作成したり、パーテーションを利用することが考えられると思います。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です