経理業務用にERC20トークンのICOや取引をノード無しでBigQueryで集計する

こんにちは。ERC20のICOや取引をまとめて簡単に集計する方法を話します。 実は、こちらの集計方法でとても困っていると、ある会社から私個人宛に相談を受けて、抽出手法をアドバイスさせていただいたのですが、デモまでしたらこれなら簡単に取れる！とのことで案件自体が消失してしまいました苦笑 交通費払った無料コンサルとなってしまって勿体無いので、せめて業界用に知見をオープンにします笑

Google BigQueryによる抽出という選択肢

過去にERC20の取引をフルノードから抽出する記事を書きましたが、現在はGoogle Cloud Platform（GCP）のBigQueryにEthereumのデータは綺麗に正規化された状態でパブリックデータとして提供されています。

フルノードからトランザクションを抽出する為には詳しくブロックチェーンの仕様を理解してJSON-RPCなどを活用した抽出プログラムを書く必要がありました。Google では、SQLで簡単にETHのトランザクションやERC20のトランザクションを取得、加工することができます。

BigQueryとは、Google Cloud Platformで使える、企業向けデータ ウェアハウスです。SQLで検索や加工が可能です。

cloud.google.com

ethereumデータはこれらのOSSで加工過程がオープンになっています。

github.com

簡単に取れると何が嬉しいのか

ブロックチェーンのトランザクションを簡単に取れると何が嬉しいのか

・経理業務に置いて、ブロックチェーン上のトランザクションや残高と社内DBの金額を合わせる必要があります。日次や月次の経理業務に置いて、簡単に集計ができると金額が正常かの調整業務が楽になります。

・ICOの税金計算。アドレスにひもづくERC20のトランザクションを取得することで、ICOトランザクションを集計して手持ち金額や購入したETH額を把握できます。

ERC20トークンを抽出する

ERC20は仕様上、送金時にeventと呼ばれるログ機能が呼ばれて、ログが刻まれます。 BigQueryでは、ERC20トークンの送金eventを判定してテーブルに入れています。

対象テーブルはtoken_transfersです。

bq_token_transfers_table

こちらのテーブルはtransfer eventのログを投入しており、ERC20のtoken_transfersにはis_ERC20がtrueとなっています。 nameにはERC20のname変数に入っている情報を登録されており、トークン名を知ることができます。

例えば、今月のERC20のトークン取引を抽出してみましょう。

SELECT 
    tokens.name,
    tokens.address,
    tokens.decimals,
    tokens.total_supply,
    transfers.block_timestamp,
    transfers.transaction_hash,
    transfers.from_address,
    transfers.to_address,
    transfers.value
FROM
    `bigquery-public-data.crypto_ethereum.token_transfers` as transfers
LEFT OUTER JOIN
    `bigquery-public-data.crypto_ethereum.tokens` as tokens
ON
    transfers.token_address = tokens.address
LEFT OUTER JOIN
    `bigquery-public-data.crypto_ethereum.contracts` as contracts
ON
    tokens.address = contracts.address
WHERE DATE(transfers.block_timestamp) BETWEEN "2019-06-01" AND "2019-06-28"
AND contracts.is_erc20 = TRUE
;

結果にはERC20のトランザクションが抽出されています。

result_token_transfers

可視化してみる

BNBの取引金額合計を時系列に可視化してみます。

BNB-visual

日付ごとのBNBの取引トークン金額合計を表した図です。

6月20日に跳ねている金額が見えたので確認してみました。なぜか大量のBNBを送信元、送信先が同じで送っているトランザクションがありました。etherscanで確認できます。

https://etherscan.io/tx/0xb6c3c52e5b889f25f8009199f83cea0e93b076630b6a94f077f636f01342acea

まとめ

BNBに限らず、ERC20トークンは全てこのように取引量を集計することができます。

ブロックチェーンのノードを立ててRPCを経由するのでは難易度が高いですが、SQLであれば多くの人が簡単に集計でき、経理業務や税金計算においてまとめて計算が可能になります。

巨人の肩に乗るとはこのことですね。