BigQueryのブロックチェーン一般公開データセットを使う

はじめに

BigQuery（以下、BQ）には誰もが参照できる一般公開データセットがあります。
その中にはブロックチェーンに関するデータセットも含まれます。
これらのデータセットについて調査したことやTipsをまとめました。

ブロックチェーンの一般公開データセットについて

どのように利用を開始するのか？

下記の手順を踏むだけでデータセットを利用開始できます。

• マーケットプレイスにアクセス
• データセットを表示を押下
• BQコンソールにbigquery-public-data/crypto_*が表示されるように

どのブロックチェーンが一般公開データセット化されている？

コチラのリポジトリにリストが記載されており、BitcoinやPolygon等に対応していることが分かります。

※上記画像は2023年4月26日現在

参考：一般公開データセットを構築する基盤

この一般公開データセットはどのように作られているのでしょうか？

まず前提としてbitcoin-etlやethereum-etlというBlockchain ETLが開発するOSSがあります。
（ブロックチェーンからデータを取ってきてパースしCSVやJSON形式で取得できるOSS）

これらを利用し、一般公開データセットを構築している旨がGoogle Cloud公式ブログに記載されています。
ethereum-etlやAirflowを組み合わせてETLを行っているようです。

Blockchain ETL以下にはethereum-etl-airflowがあり、こちらはポチポチするだけでethereum-etlとAirflowを組み合わせたETL基盤をデプロイできるものです。公式の基盤もこれに近しい形で構築されているものと思われます。

参考：EVMやBitcoin系以外のデータセットはないのか

他にも、Blockchain ETLにインスピレーションを受けPolkadot系のブロックチェーンをETL可能にしたsubstrate-etlが存在します。
READMEに記載の手順でPolkadot系データセットも利用できます。

実際に分析してみる

以降ではEthereumをターゲットに話を進めていきます。
Ethereumの一般公開データセットであるbigquery-public-data/crypto_ethereum以下には：

• balances
• blocks
• contracts
• logs
• sessions
• token_transfers
• tokens
• traces
• transactions

といったテーブルがあり、それぞれの意味についてはDescriptionを見るのが手っ取り早いかと思います。
例えばtransactionsテーブルをBQコンソールで開くと下記の記載があります。

Each block in the blockchain is composed of zero or more transactions. Each transaction has a source address, a target address, an amount of Ether transferred, and an array of input bytes.
This table contains a set of all transactions from all blocks, and contains a block identifier to get associated block-specific information associated with each transaction.
Data is exported using https://github.com/medvedev1088/ethereum-etl

まずはクエリを発行してみる

上記リポジトリにbigquery-public-data/crypto_ethereumへのサンプルクエリが載っているので試しに実行してみます。

top_ethereum_balances.sql

-- 残高上位1000件のアドレスとその残高を取得するためのクエリ

WITH double_entry_book AS (
    -- debits
    SELECT to_address AS address, value AS value
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE to_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    UNION ALL
    -- credits
    SELECT from_address AS address, -value AS value
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE from_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    UNION ALL
    -- transaction fees debits
    SELECT 
        miner AS address, 
        SUM(CAST(receipt_gas_used AS numeric) * CAST((receipt_effective_gas_price - COALESCE(base_fee_per_gas, 0)) as numeric)) AS value
    FROM `bigquery-public-data.crypto_ethereum.transactions` AS transactions
    join `bigquery-public-data.crypto_ethereum.blocks` AS blocks ON blocks.number = transactions.block_number
    GROUP BY blocks.number, blocks.miner
    UNION ALL
    -- transaction fees credits
    SELECT 
        from_address AS address, 
        -(CAST(receipt_gas_used AS numeric) * CAST(receipt_effective_gas_price AS numeric)) AS value
    FROM `bigquery-public-data.crypto_ethereum.transactions`
)
SELECT address, SUM(value) AS balance
FROM double_entry_book
GROUP BY address
ORDER BY balance DESC
LIMIT 1000

コンソール上で実行すると次のように結果が出てきます。データを整備する必要がないので楽ですね！

もっと詳細に分析したい！

どのような分析ができるのかは、Kaggleにてcrypto_ethereumをキーワードに検索すると先人のクエリを見ることができるので、参考になるかと思います。

分析する上で生じた問題点

Etherscan上のアドレスでクエリしても結果が出ない

Etherscan上のトランザクションがcrypto_ethereum内に存在するか確認してみます。
CryptoPunksに関するトランザクションをEtherscanにて調べ、最新トランザクションのfromアドレスをコピーしてクエリしてみました。

-- アドレスの一部を`.`でマスクしています

SELECT
  *
FROM
  `bigquery-public-data.crypto_ethereum.transactions`
WHERE
  from_address = "0x...B0F...A7FBBDf.............Bf3844976"
AND
    DATE(block_timestamp) = "2023-04-25"

結果は下記の通り「表示するデータはありません」。

なぜこのような結果になったかと言えば、Etherscanサイトに記載のトランザクションのアドレスはチェックサム・アドレス形式になっています。
つまり、一部に大文字が混入しているのです。

暗号通貨アドレスには、チェックサム2と呼ばれているエラーチェック機能が組み込まれており、これによりアドレスのタイプミス（間違えて一文字抜かしてしまうなど）を防ぐことができます。イーサリアム・アドレスには、チェックサムバージョン、および非チェックサムバージョンの両方があります。チェックサム・バージョンのイーサリアム・アドレスには、大文字が含まれていますが、非チェックサム・バージョンには小文字しか含まれていません。
チェックサム・アドレス：0x4a44A0XXXXXXXXXX290217C51Df0c6158a59CAD2
非チェックサム・アドレス：0x4a44a0xxxxxxxxxx290212721df0c6158a59cad2

暗号通貨およびDeFiユーザーがよくやる9個の失敗

LOWER()等で全て小文字に直すことでうまくいきます。

SELECT
  *
FROM
  `bigquery-public-data.crypto_ethereum.transactions`
WHERE
  from_address = LOWER("0x...B0F...A7FBBDf.............Bf3844976")
AND
    DATE(block_timestamp) = "2023-04-25"

最新のデータがヒットしない

基本的にcrypto_ethereum等は日次で更新されていますが、一部データセットは最終更新が過去の場合があります。
テーブルの詳細から最終更新がいつなのかは確認するのが良いです。
下記のスクショはbigquery-public-data/crypto_polygonのトランザクションテーブルです。

クエリのコストを下げたい

ブロックチェーンデータは大きいので、素朴にクエリ発行すると数TBの処理になり課金が走ってしまいます（BQは1TB/月以上有料）。
基本的にクエリを行う前に右上に表示されるバイト数を確認する基本動作が重要です。

その他コストを削減するためのポイントは3点です。

• ブロックチェーン公開データセットのテーブルは日付でパーティションされています。そのためWHERE句にDATE(block_timestamp) > "2023-04-01"といったような制限を加えることでコスト削減に繋がります
  • https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ja
• BQはカラムナーDBなのでLIMITを実施してもコスト削減にはなりません。代わりにSELECT *ではなく本当に必要なカラムだけをSELECTすればコストは下がります
• どうしても期間を限定せず、アドホックにデータ分析を行いたい場合はBQのセッション機能を使うのがオススメです
  • https://cloud.google.com/bigquery/docs/sessions-create?hl=ja

参考：トラブルシューティング先

どうしても分からんことが生まれたら、Blockchain ETLには公式Telegramがあるので質問してみると良いかもです。