NEMデータベースをクエリする方法は？

2023年12月24日 11:47

この記事はCryptoBelieverさんの記事「How to query the NEM database?」をChatGPTを用いて翻訳したものです。

Introduction

知らない方のために：NEMノードはデータを格納するためにH2を使用しています。H2はJavaで書かれたオープンソースのリレーショナルデータベース管理システムであり（NISクライアントもJavaで書かれています）、SQL標準のサブセットをサポートし、高速なインメモリデータベースエンジンを提供しています。H2は埋め込みデータベースの人気のある選択肢であり、特にJavaで書かれたアプリケーションにとっては、小さなフットプリントと使いやすさから利用されています。

この記事では、NEMノードデータベースに格納されたデータを抽出する方法を紹介します。これにより、すべてのトランザクション、特定のアドレスのトランザクション、既知のアカウントの数、ブロックなどをエクスポート（たとえばCSV形式で）し、そのデータを基にさまざまな統計やチャートを作成することができます。では始めましょう :)

Get snapshot of the NEM node database

まず、接続するために現在のデータベースファイルが必要です。これを行うには、NEMノードを同期させる必要があります（ノードの設定手順はこちらで確認できます）。同期プロセスを速めるために、bob.nem.ninjaで利用可能な最新のプリシンクロナイズドデータベースダンプを使用することができます。こちらから入手できます - これは8月のもので、プレイするだけで最新のデータが必要でない場合に使用できます。

デフォルトでは、H2データベースファイルは次のディレクトリにあります：/nem/nis/data（例：/home/user/nem/nis/data/nis5_mainnet.mv.db）。

H2は単一ユーザーデータベースです。それに接続するには、ノードを停止する必要があります（記事の最後の部分では、この制限を回避する実験的な方法を紹介します）。データベースファイルをコピーして使用することをお勧めします。

Connection to the database with H2 Client

データベースのスナップショットができたので、それに接続できます。H2データベースに接続するには、複数のクライアントを使用できます。ここでは、ブラウザで実行される公式のH2クライアントの使用方法を紹介します。NEMデータベースダンプと互換性のあるバージョン1.4.200を入手する必要があります。データベースに接続する際は、ユーザーとパスワードを空白のままにし、JDBC URLにはファイルへのパス（mv.db拡張子なし）を入力します。例えば：

jdbc:h2:/home/user/nem/nis/data/nis5_mainnet_copy

接続が確立されると、H2クライアントでテーブルの構造を確認できます。これはSQLクエリを書くのに役立ちます。もしSQL言語に慣れていない場合は、インターネット上に多くのチュートリアルがあるので、参照すると良いでしょう。

H2クライアントでは、csvwrite関数を使用してSQLクエリの結果をエクスポートできます。これにより、さらなる分析のためにCSVファイルをエクスポートできます。使用例：

CALL CSVWRITE('/home/user/result_export.csv', '(sql_query)');

Sample SQL queries

さて、データのクエリを開始できます。NEMクライアントは、データベースからブロックを読み込む際に多くの計算を行います（特定のトランザクションやブロックに関連するモザイクの徴収や、ハーベストされたブロックなど）。したがって、たとえば正確なアカウント残高（いくつかの要因に依存する）が必要な場合は、この情報をノードAPIを介してクエリする必要があります。データベースをクエリすることは、一般的な統計やトランザクションに関する情報を取得するために適しています。以下にクエリの例を示します。

すべての登録されたネームスペース（期限が切れているものも含む）を取得します：

SELECT fullname, height, a.printablekey 
FROM namespaces n
LEFT JOIN accounts a ON a.id = n.ownerid;

すべての既知のアドレスをカウントする：

SELECT count(*) FROM accounts;

最後のブロックの高さを問い合わせる：

SELECT max(height) FROM blocks;

タイムスタンプ、ブロックの高さ、ハッシュ、マルチシグのハッシュ（マルチシグの場合）、送信者、受信者、転送されたXEMの金額、メッセージを含むすべての転送トランザクションを取得します：

SELECT 
    block.timestamp + 1427587585 AS timestamp, 
    block.height,
    tran.transferhash AS hash, 
    multisig.transferhash AS multisig_hash,  
    sender.printablekey AS sender, 
    recipient.printablekey AS recipient, 
    CASE WHEN tranmosaics_xem.quantity IS NULL AND tranmosaics_other.quantity IS NULL THEN CAST(tran.amount/1000000.0 AS numeric(20,6))
            WHEN tranmosaics_other.quantity IS NULL THEN CAST(tranmosaics_xem.quantity/1000000.0 * tran.amount/1000000.0 AS numeric(20,6))
            ELSE 0.000000 
   END AS xem_amount,
   tran.messagetype, 
   tran.messagepayload
FROM transfers tran
LEFT JOIN accounts sender ON tran.senderid = sender.id
LEFT JOIN accounts recipient ON tran.recipientid = recipient.id
LEFT JOIN blocks block ON tran.blockid = block.id
LEFT JOIN multisigtransactions multisig ON tran.id = multisig.transferid
LEFT JOIN transferredmosaics tranmosaics_xem ON tran.id = tranmosaics_xem.transferid AND tranmosaics_xem.dbmosaicid = 0
LEFT JOIN transferredmosaics tranmosaics_other ON tran.id = tranmosaics_other.transferid AND tranmosaics_other.dbmosaicid != 0;

上記のクエリについての説明です：

なぜタイムスタンプに1427587585を追加するのですか？これはNEMジェネシスブロック（2015年3月29日00:06:25 UTC）からの秒数です。データベース内のすべてのタイムスタンプは、NEMジェネシスブロックからの秒数です。日付を実際のタイムスタンプに変換するには、ジェネシスブロックのタイムスタンプを追加する必要があります。

XEMの金額は上記のように計算されます。XEMはV1転送（モザイクの配列フィールドが空で、転送されたXEMの金額はamountフィールドにあります）またはV2転送（モザイクの配列が空でない）として転送できます。amountフィールドは乗数として機能し、XEM数量はモザイクの配列から取得し、amountと掛け算する必要があります。特定のトランザクションの例計算はこちらで確認できます。

Connection to the database from Python code

H2コンソールのおかげでデータベーステーブルの構造を学び、いくつかのクエリを実行したので、次にPythonコードを使用してデータベースに接続する方法を学びましょう。

接続には jaydebeapi を使います。h2-1.4.200.jarドライバ(こちら)も必要です。サンプルコードです：

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import jaydebeapi

connection = jaydebeapi.connect(
    "org.h2.Driver",
    "jdbc:h2:/home/user/nem/nis/data/nis5_mainnet_copy",
    ["", ""],
    "/home/user/nem/nis/data/h2-2019-10-14/h2/bin/h2-1.4.200.jar"
)

query = """SELECT block.timestamp + 1427587585 AS timestamp, CAST(tran.transferhash as VARCHAR) AS hash
FROM transfers tran
LEFT JOIN blocks block ON tran.blockid = block.id"""

cursor = connection.cursor()
cursor.execute(query)
result = list(cursor.fetchall())
cursor.close()
connection.close()

上記のクエリでは、2つの列があります：

転送トランザクションのタイムスタンプ
転送トランザクションのハッシュ

これらの値はすべてresultフィールドにあります。これをpandasのDataFrameオブジェクトに変換し、それを使用してチャートを生成できます。NineLivesさんは、このMongodbのジャーニーに関する記事でこの素晴らしいライブラリを紹介してくれました。まだ読んでいない場合は、ぜひご覧ください！

上記のクエリは単純ですが、日次転送トランザクションのチャートを作成することができます。これを行うには、SQLの結果からDataFrameを作成する必要があります：

data_frame = pd.DataFrame(result, columns=['Timestamp', 'Hash'])
data_frame["Datetime"] = pd.to_datetime(data_frame["Timestamp"], unit = "s")
data_frame["Date"] = data_frame["Datetime"].dt.date

次に、Dateフィールドでグループ化します：

grouped_by_day = pd.DataFrame().assign(Date=data_frame['Date'], Transactions=1)
grouped_by_day = grouped_by_day.groupby(['Date']).count()

さて、結果をグループ化したら、グラフを描こう：

fig, ax = plt.subplots()
grouped_by_day.plot(ax=ax, color='#762D99', linewidth = '1')
ax.set_title('Daily Transactions')
ax.xaxis.set_major_locator(mdates.MonthLocator(interval=6))
ax.get_legend().remove()
plt.xticks(rotation=30)
plt.xlabel("Date")
plt.ylabel("Transactions")
plt.savefig('/home/user/images/daily_transactions.png', dpi=600)
plt.show()

また、日付でデータをフィルタリングし、過去12ヶ月間のレポートを作成することもできます：

last_year_data_frame = data_frame['Datetime'] >= '2021-12-18 00:00:00'
last_year_data_frame = data_frame.loc[last_year_data_frame]

これらの例はアイデアを提供するはずです。データベースのデータを基にさまざまなチャートを生成できます。楽しんでください :)

Connection to the database of the running node (experimental)

以前に、H2が埋め込みモードで単一ユーザーデータベースであると述べました。そのため、実行中のノードのデータベースに接続することはできません。H2データベースの構成の可能なスイッチを調査し、実行中のノードのベースに接続できる構成に関する情報を見つけました。これはAutomatic Mixed Modeと呼ばれ、これを有効にするには、db.propertiesファイルのノード構成を変更し、接続文字列にAUTO_SERVER=TRUEを追加します：

jdbc.url=jdbc:h2:${nem.folder}/nis/data/nis5_${nem.network};DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE

同じパラメータAUTO_SERVER=TRUEは、H2コンソールクライアントからデータベースに接続する際にも追加する必要があります。

jdbc:h2:/home/user/nem/nis/data/nis5_mainnet;AUTO_SERVER=TRUE

この構成は長時間ノードを実行していないため、ノードデータベースが破損する可能性があるため注意して使用してください。

Summary

この記事では、クライアントとPythonコードを使用してNEMノードのH2データベースに接続する方法を学びました。また、簡単なチャートも生成しました。お役に立てれば嬉しいです。

ぜひご覧ください！フィードバックは常に歓迎です！

この記事が気に入ったらサポートをしてみませんか？