NEMデータベースへの問い合わせ方法は？

2022年12月21日 17:28

この記事はSymbol/NEMの技術者であるcryptoBelieverさんの記事「How to query the NEM database?」を機械翻訳したものです。

はじめに

ご存じない方のために。NEMノードはデータの保存にH2を使用しています。H2はJavaで書かれたオープンソースのリレーショナルデータベース管理システムです（NISクライアントもJavaで書かれています）。H2は標準SQLのサブセットをサポートし、永続化機能を持つ高速なインメモリデータベースエンジンを提供します。H2は、その小さなフットプリントと使いやすさから、特にJavaで書かれたアプリケーションの組み込みデータベースとしてよく利用されています。

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

NEMノードデータベースのスナップショットを取得する

まず、接続するために現在のデータベースファイルが必要です。これを行うには、NEMノードを同期させる必要があります（ノードのセットアップの手順はこちらでご覧になれます）。同期処理を高速化するために、bob.nem.ninjaで利用可能な最新の同期済みデータベースダンプを使用することができます。ここから入手できます。8月のものなので、遊ぶだけで、最新のデータを必要としない場合は、これを使用することができます。

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

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

H2クライアントによるデータベースへの接続

これで、データベースのスナップショットが取得できましたので、接続を行います。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)');

サンプルSQLクエリ

これでデータのクエリを開始することができます。ここで指摘しておきたいのは、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ジェネシスブロック（March 29, 2015 at 00:06:25 UTC）からの秒数です。データベース内のすべてのタイムスタンプは、NEM創世記のブロックからの秒数です。日付を現実のタイムスタンプに変換するには、ジェネシスブロックのタイムスタンプを追加する必要があります。

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

Pythonコードからデータベースへの接続

H2 Consoleのおかげで）データベーステーブルの構造を学び、いくつかのクエリを実行したので、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のJourney with 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]

これらの例は、あなたにアイデアを与えるでしょう。データベースのデータからもっとたくさんのグラフを作成することができます。楽しんでください :)

実行中のノードのデータベースへの接続 (実験的)

前回、組み込みモードの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

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

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

この設定は、長いノードランではテストされていないので、ノードデータベースが破損する可能性があるので、慎重に使用してください。

まとめ

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

どうぞご覧ください。フィードバックはいつでも歓迎します

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