SolanaのPublic DataをBigQueryで取得したかった

えー、お笑いを一席. ブロックチェーンSolanaのデータがGoogle Cloud BigQueryで使えるようになったというニュースをたまたまネット推薦記事でみかけた1.

おや, 面白そうだ. ちょっとやってみようかな… BigQueryはさわるのが１年以上つかってないかも, どうやるんだっけ… とりあえずカラムとかサンプルでちょっとデータをみたいよな, こんな感じだっけか？

SELECT * FROM `bigquery-public-data.crypto_solana_mainnet_us.Transactions` LIMIT 10;

とりあえず動かしてみよう, ポチッとな.

…

5秒でレスポンスが帰ってくる. おー、速い. えーっと, あれ課金データ309TB？！

いちげきひっさつ、ハサンギロチン2.

BigQueryでクエリ一撃5 秒で29万円溶かした人の顔

話題の画像生成AI, DALL･Eをつかって BigQueryでお金溶かした人の顔を表現してもらった3.

あまりのシュールな現実ににサルバドール・ダリも夢から目を覚ました.

BigQuery 29万の課金の分析

心臓の鼓動を落ち着けて, なぜこうなったのか調べてみた.

BigQueryの課金単位はスキャンしたデータ量

まずBigQueryはクリエのデータ量によって料金がかかる. $5.00 per TBが相場だが, 米ドル以外の通貨での決済では為替の影響なのかもう少し高めな設定4. 今回は309TBなので, 309 x 5 = $1545で, 実際の日本円課金は288334円. たとえば為替USDJPYが150円ならば231750, まあいちおう課金のロジックは筋が通ってそうだ.

BigQueryバットプラクティスを踏んだ

また, たとえLIMITを書いてもデータをフルスキャンするようだ. そして SELECT * では必要がないカラムをすべて取得するので, たいていは必要なカラムだけを指定しましょうというのがBigQueryベストプラクティス.

いいかえれば, 初心者が踏みやすい地雷をドッカンドッカン踏んでしまったようだ.

Googleサポートに相談して課金を免れた

とりあえず父親に報告した

心の動揺を落ち着けるために父親に電話したが, 状況をうまく説明するのに苦労した. なにしろ, 5秒という一瞬で30万が課金されたと説明したのだが, なんだそれはGoogleは危ない詐欺サイトじゃないか？と疑われた.

たとえば電気料金や水道料金のようにつかえばつかっただけ課金されるんだけど, 今回はつかったデータ量で課金されるんだよといっても, なんで5秒なんだといわれた.

ブロックチェーンとは分散型台帳といわれて, たとえばみずほ銀行の自分の口座の取引履歴をみたいとおもったときに, 自分の通帳じゃなくてみずほ銀行の全取引履歴をダウンロードしちゃったようなもんだよ, と我ながらうまい説明をしたものの, なんだそれはといわれた.

とりあえずChatGPTに相談した

人間はもうだめなので, 最近の唯一の友達であるチャッピーことChatGPTにつらい現実と対処方法を相談したら, ChatGPTがGoogle サポートに相談してみたらどうでしょうか？とアドバイスをもらった.

サポートに連絡: 誤って大量のデータをクエリしてしまった場合、Google Cloudのサポートチームに連絡して事情を説明し、対処法を相談することをお勧めします。

Google サポートに相談した

チャッピーのアドバイスに従って, Googleサポートにチャットで相談した. すると, ああありがちなミスのようなれた感じで対応された. サポートには課金を帳消しにする権限はなく, 判断は別の専門チームの承認が必要となったため, サポートから技術チームに事情を説明して動いてくれることになった. できるかぎり解決にむけて努力します, I’ll do my best といわれて泣きそうになった. それから数日後, 課金額に対するクレジットが発行されるかたちで帳消しになった.

まずはGoogle Cloudサポートに相談することを伝えたいために記事を書いた

わたしは運が良かったのかも知れない. ネットで調べると必ずしも帳消しになるわけではなく, 技術チームの調査と承認があった上で認められれば認められればということになる.

そして, この記事を書く目的は, 同じような失敗によってBigQueryで高額課金されて泣きそうになっている人に向けて, 「まずはサポートに相談する」ことを伝えたいからだ. 正直, このミスは初心者的なミスで, これをしてしまったということが恥ずかしいことだ. あえて記事を書く必要もない. それでも, わたしは同じように苦しんで震えている人がネットでどうすればいいか検索してこの記事に辿り着いたならば,

まずは, おちついて！そしてGoogle Cloudサポートに相談してみて!

というメッセージを伝えたい. とても不安と恐怖でつらい気分になっているならば, 同じような感情をわたしも先日抱いた. その気持ちについてとても同情する. 苦しみが取り除かれますようにと願いたい. だから, 自分のミスをさらけ出す恥ずかしいことにはなるものの, 泣きそうになっているあなたのためにこの記事をかいたのだ.

結局どうすれば正解だったのか？

しばらくいったんBigQueryから離れたあと, この失敗を活かすためどうすることが正解だったのか考えてみた. ベストプラクティスを勉強することにした5.

まず, 数行程度をみるだけなばクエリを叩かずともプレビューを見ればいいだけの話だ. これでカラムも中身のデータも確認できる.

ref. Solana Blockchain (Community Dataset)

ベストプラクティスに従うことでクエリのデータ量はある程度削減できる.

  • SELECT * はつかわずに必要なカラムだけを指定する.
  • LIMITを書いてもフルスキャンしたあとにフィルタリングするだけでスキャンデータ量に効果はない.
  • WHEREについても同様.

このPublic dataはパーティション分割テーブル6のようだ. 月別でデータが小分けされているため, 必要な月ごとにデータにアクセスすればいい. 具体的には block_timestampがタイムスタンプのフィールドに設定されている.

ということで, よおおし, 必要な行と列だけに絞り込めばなんとかなるはずだ！

SELECT block_timestamp, status, signature
FROM `bigquery-public-data.crypto_solana_mainnet_us.Transactions`
WHERE block_timestamp > '2023-11-01'
  AND signature = 'hogehoge';

だがしかし, これでも1TB以上はある. たしかに, 309TBからすれば大幅なデータ量削減に成功した. しかしそれでもTBあたり5ドルくらい課金されるとまだまだ費用が高い. うーん…

とりあえず, これ以上深堀せずにBigQueryをつかうのをやめることにした. 後味が悪いものの, この記事は技術的深堀を主張したいのではなく, 悲しみの日記的な感じで書いた.

  1. Solana data goes live on Google Cloud BigQuery | Solana ↩︎

  2. ハサミギロチンはポケモンの技のひとつ. ハサミギロチン (はさみぎろちん)とは【ピクシブ百科事典】 ↩︎

  3. BigQueryで150万円溶かした人の顔 #BigQuery - Qiita ↩︎

  4. SKUs | Google Cloud ↩︎

  5. クエリ計算の最適化  |  BigQuery  |  Google Cloud ↩︎

  6. パーティション分割テーブルに対するクエリ  |  BigQuery  |  Google Cloud ↩︎