DuneでSolana Wallet の取引履歴を取得する

はじめに: DuneでSolana Wallet のtransactions履歴を取得したい

前回からの続き, BigQueryでクエリ一撃で29万円溶かしたけど助かった人の顔 | Futurismo

BigQueryはトラウマになってしまったので, DuneをつかってSolanaチェーンのwallet addressに紐づくtransaction履歴を取得してみた.

Dune とは

Duneとは, ブロックチェーンのデータをSQLで取得して分析できるサービス.¹

https://dune.com

Duneは無料でつかえるのか？

クエリを発行してデータを取得して分析するだけならば無料で使える.

APIやデータExportなど, 機能ごとにcreditsという単位でポイントみたいなものが消費される. そして, 月に2500creditsまでが付与されている.

2023/11のルールなので将来変更されるかも.

• https://dune.com/pricing
• https://dune.com/docs/api/?h=credit#api-pricing

solana.acount_activityテーブルをみる

DuneのSolana関連テーブルはraw tableとdecorded tablesの2種類があるようだ.

ref. Solana Overview - Dune Docs

• raw tables: solanaチェーンのからの生データ.
• decoreded tables: Programごとに整理されたテーブル.

transactions関連だとtransactionsというテーブルがあるのだが, この中身は結構データ量がおおく, tranasctionsに関連したいろんなアカウントの情報が含まれて自分で整理する必要がある.

今回, Wallet Addressとそれに関連するAssociated Acount Addressの残高の変化が欲しいので, accont_activityテーブルをみてみるのがよさそうだ. addressを指定することで, 残高や取引による残高の変化が取得できる.

• Transactions - Dune Docs
• Account activity - Dune Docs

SQLクエリを書く

Duneのエディタを開いてSQLを書いてクエリを実行する². なおUIからのクエリ実行は無料できるものの, 2分でタイムアウトする. 大きな データに対する高速なクエリは課金が必要.

SOL transactions

• SOLはlamportで数値が表示されるのでdecimals=9で割る.
• SOLはtx実行で手数料がかかるのでtx_successはWHERE句で指定しない.

SELECT
  block_time AS time,
  balance_change / 1e9 AS sol_change,
  post_balance / 1e9 AS sol_balance,
  address,
  tx_id
FROM
  solana.account_activity
WHERE
  (
    address = ''
    OR address = ''
    OR address = ''
  )
  AND block_date BETWEEN DATE '2023-11-20' AND DATE '2023-11-26'
ORDER BY
  1

SPL transactions

• tx_success = TRUEを指定して成功したtxのみ絞り込み.
• wSOLのtoken_balance_changeとSOLのbalance_change/1e9が一致する.
• wSOL post_token_balanceとSOL post_balance/1e9とは一致しない.
• addressの部分をtoken_owner_addressにするとwallet addressに紐づくassociated accountのtxがすべて取得可能. ただしクエリがおそすぎて実用的でない. addressを すべて列挙するのがよさそう.

SELECT
  block_time AS time,
  token_balance_change AS token_change,
  token_mint_address AS mint_address,
  token_balance_owner AS owner_address,
  address AS token_address,
  tx_id
FROM
  solana.account_activity
WHERE
  (
    address = ''
    OR address = ''
    OR address = ''
    OR address = ''
  )
  AND tx_success = TRUE
  AND block_date BETWEEN DATE '2023-11-20' AND DATE '2023-11-26'
ORDER BY
  1

クエリ結果をcsv exportする

データのexport機能はcreditsを消費するため, 月の無料枠内ならばいちおう無料でつかえる. ただし, データ量が多すぎるとやっぱり課金は必要.

WEb UIからの手動Export機能は課金しないとできない. 代わりに, APIをつかってcsvをダウンロードできる. APIの使用にもcreditsを消費する. ここでdatapointsという別の単位がでてくる. ざっくりいえばrows x colsの掛け算で消費creditsがきまる.

datapoints

rowsとcolsまたはデータ量から算出される値.

A datapoint can in most cases be thought of rows * columns with an additional limit of 100 avg bytes per cell in a set of results. This can be expressed as:

Datapoints = max(rows*columns, ceil(totalbytes/100))

https://dune.com/docs/api/faq/?h=datapoint#whats-a-datapoint

1000datapoints = 1 credits.

https://dune.com/docs/api/?h=credit#api-pricing

curlでdownloadする

APIといってもプログラムをかかなくてもcrulをwgetをつかってダウンロードできる. ただしAPI_KEYの発行が必要.

curl "https://api.dune.com/api/v1/query/{query_id}/results/csv?api_key=hogehoge" -o "dune_spl.csv"

おわりに: Dune無料枠ならばSolscan手動csvダウンロードでもいいかも

Transactionsや資金移動の履歴の取得だけが目的ならば, わざわざDuneからダウンロードしなくても, Solscanのcsvダウンロードを手動でポチポチするのが楽かも. Solscan APIは有料ぽかったのでDuneならば無料でできるかな？とおもったけど, Duneの無料枠にはそこそこ制限がある.

ただ, Duneというプロダクト自体をはじめて触って, とてもおもしろそうな予感がした. ブロックチェーンのデータをSQLで気軽に分析できるのはすごいプロダクトだ！しかも, データエクスポートではなくデータ分析だけならば無料内で使える.

追記: クリプタクトカスタムファイルっぽいフォーマット出力

CryptactにSolanaの取引履歴を取り込むためのスニペット. 実際は以下では動かないが, 誰かの参考になればということで, これをChatGPTと相談しながらカスタマイズすることを想定してスニペットもここにおいておく.

SELECT
  block_time AS Timestamp,
  CASE
    WHEN balance_change >= 0 THEN 'BONUS' ELSE 'LOSS'
  END AS Action,
  'solana' AS Source,
  'SOL' AS Base,
  balance_change / 1e9 AS Volume,
  '' AS Price,
  'JPY' AS Counter,
  0 AS Fee,
  'JPY' AS FeeCcy,
  'Solana trade' AS Comment
FROM
  solana.account_activity
WHERE
  (
    -- 自分のwallet address
    address = ''
    OR address = ''
  )
  AND
  (
    -- 除外したいtx
    tx_id != ''
    OR tx_id != ''
  )
AND block_date = DATE '2023-11-12'
ORDER BY
  1