Simple guide to DIY NEM blockchain analysis

Guides
#1

Step 1: download and unpack NIS

$ wget -qO- http://bob.nem.ninja/nis-0.6.95.tgz | tar xzv -C /opt/nem/

Step 2: obtain blockchain database snapshot

a) either from your locally running and fully synchronized NIS

  • configure nis.bootName and nis.bootKey in /opt/nem/package/nis/config.properties
  • run NIS
$ cd /opt/nem/package
$ ./nix.runNis.sh

(I run this usually inside “screen” (apt-get install screen) session, so I can detach from it and logout from my VPS)

  • let it synchronize - this may take a long time; you may speed things up by downloading recent database snapshot from bob.nem.ninja (i.e. nis5_mainnet.h2-1453k.db.zip) and unzipping it into ~/nem/data directory … it should be imported once you start the NIS; the correct order is
    – start NIS
    – stop NIS
    – copy/unzip database from bob.nem.ninja into ~/nem/data (overwrite the one already in there)
    – start NIS again
    – wait until it is fully synchronized

  • copy the database (so we don’t have to stop the NIS to open the database with another program)

$ cp ~/nem/nis/data/nis5_mainnet.h2.db ~/nem/nis/data/nis5_mainnet-copy.h2.db

b) or by downloading from bob.nem.ninja (i.e. nis5_mainnet.h2-1453k.db.zip); of course, this one will not be up-to-date, but you may use it to play around - inspect the database schema and experiment with SQL queries

Step 3: inspect the database

$ java -Dh2.bindAddress=127.0.0.1 -cp /opt/nem/package/libs/h2-1.3.176.jar org.h2.tools.Server -baseDir ~/nem/nis/data
  • open your browser and go to http://localhost:8082/
  • fill-in JDBC URL: jdbc:h2:nis5_mainnet-copy
  • both name and password are empty
  • connect and enjoy

Step 4: write more SQL queries

  • select max(height) from blocks
  • select count(*) from accounts
  • select sum(totalfee)/1000000.0 from blocks
  • select b.height as block, b.totalfee/1000000.0 as fee,a.printablekey as harvester from blocks b left outer join accounts a on a.id=b.harvesterid order by b.totalfee desc
  • select sum(b.totalfee)/1000000.0 as fee,a.printablekey as harvester from blocks b left outer join accounts a on a.id=b.harvesterid group by harvester order by fee desc
  • below all transactions from some exchange address:
select

asender.printablekey as sender,
arecipient.printablekey as recipient,
case t.senderid=a.id
when 0 then t.amount/1000000.0
else -t.amount/1000000.0
end as amount


from
  accounts a
  left outer join transfers t on a.id=t.senderid or a.id=t.recipientid
  left outer join accounts asender on t.senderid=asender.id
  left outer join accounts arecipient on t.recipientid=arecipient.id

where a.printablekey like 'ND2JRPQIWXHKAA26INVGA7SREEUMX5QAI6VU7HNR'

Step 5: automate

  • create file BlockExplorer.java:
import java.sql.*;
public class BlockExplorer {
    public static void main(String[] a) throws Exception {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/nem/nis/data/nis5_mainnet-copy", "", "");

        Statement stmt = conn.createStatement();
        ResultSet rs;

        rs = stmt.executeQuery("SELECT * FROM ACCOUNTS"); //use whatever SQL query you created in Step 4  
        while ( rs.next() ) {
            String address = rs.getString("PRINTABLEKEY");
            System.out.println(address);
        }

        conn.close();
    }
}
  • compile
$ javac -g BlockExplorer.java
  • run
$ java -cp .:/opt/nem/package/libs/h2-1.3.176.jar BlockExplorer

Step 6: enjoy you new superpower

(and if you do, feel free to send me an equivalent of 1 beer in XEM)

ND6TCGW2UY3VRFBFUOJMQJ55TPZB3LU5IRDO5LWB

3 Likes
Nemesis block information
#2

Thank you for this. I have moved it to the guide section, hopefully it will get more attention there.

#3

It seems that if you append “;FILE_LOCK=NO” to the connection string (example in Step 5 of the guide), you can work directly with the live NIS database (database opened and locked by someone else). Again, use at own risk!

Thank you @CryptoBeliever for finding this out!

Connection conn = DriverManager.getConnection(“jdbc:h2:~/nem/nis/data/nis5_mainnet;FILE_LOCK=NO”, “”, “”);

edit: my NIS stopped working with “ERROR Timeout trying to lock table “BLOCKS”; SQL statement:” after trying the above; I do not recommend to use it until (if) solution is found.

1 Like