Getting an Insight of Blockchain Transactions Flow with Neo4j Graph Database

In the previous post I described how to explore and better understand information on Lisk blockchain by running SQL queries directly on the blockchain database. In this article I'll try to explain how to export the data from Lisk relational database to Neo4j graph database in order to be able to build and visually explore blockchain transactions flow graph.

Be advised, since we're going to run both Lisk Core and Neo4j in docker (to speed up the process and avoid Lisk and Neo4j installations), the tutorial assumes you have docker installed and running, and you're familiar with terminal. (I'm running macOS but the process should be the same on other operating systems.)

Run Lisk Core in Docker

First, we need to run and synchronise Lisk Core to have the latest blockchain data available.

1. Download and unzip this archive. The archive contains docker configuration for Lisk Core (mainnet) and Adminer.
2. cd to folder where you've downloaded and extracted the archive and run curl --output main_blockchain.db.gz https://downloads.lisk.io/lisk/main/blockchain.db.gz to download the latest Lisk blockchain snapshot. (It'll be used by step 3, otherwise it'll take days to synchronise database from scratch.)
3. Run make coldstart (it's necessary only first time) to start Lisk docker container.
4. Take a coffee, it will take some time for the script to populate Lisk database from the snapshot...

Run Neo4j in Docker

Once we have Lisk Core synchronised and running, we're going to start Neo4j in docker. Open a terminal window and run:

docker run \  
    --name testneo4j \
    -p7474:7474 -p7687:7687 \
    -d \
    -v $HOME/Docker/neo4j/data:/data \
    -v $HOME/Docker/neo4j/logs:/logs \
    -v $HOME/Docker/neo4j/import:/var/lib/neo4j/import \
    -v $HOME/Docker/neo4j/plugins:/plugins \
    --env NEO4J_AUTH=neo4j/test \
    neo4j:latest

Once Neo4j container is up and running you can start Neo4j browser. Go to http://localhost:7474/ in your browser to get feel of Neo4j browser.

Export data from Postgres (Lisk) database to CSV

With both Neo4j and Lisk ready, we're ready to export blockchain data to csv files later to imported to Neo4j:

1. Open a termninal window and type: psql -h localhost -p 5432 -U lisk lisk to connect to Lisk Postgres database (enter 'password' when asked for password) .
2. Enter commands below in psql shell to export the data as csv:

\COPY (SELECT address, balance FROM mem_accounts) TO '/Users/sdrpa/Downloads/accounts.csv' WITH CSV header;
\COPY (SELECT id, trs."senderId", trs."recipientId", timestamp, amount FROM trs WHERE type = 0) TO '/Users/sdrpa/Downloads/trs.csv' WITH CSV header;

After that you'll have accounts.csv and trs.csv files ready to be imported to Neo4j.

Import CSV data to Neo4j database

We need to copy accounts.csv and trs.csv to Neo4j container to be able to import the data to Neo4j.

Open a terminal window and run:

docker cp accounts.csv testneo4j:/var/lib/neo4j/import/accounts.csv  
docker cp accounts.csv testneo4j:/var/lib/neo4j/import/trs.csv  

Alternatively, you can use Finder to copy the files to $HOME/Docker/neo4j/import/ (but it's only applicable to macOS)

Now we need to create the nodes and the relationships for Neo4j graph. We'll do it using cypher shell.

Open a terminal window and run cypher-shell -u neo4j -p test to start Neo4j cypher shell.

In cypher shell type:

```
// Create accounts
USING PERIODIC COMMIT  
LOAD CSV WITH HEADERS FROM "file:///accounts.csv" AS row  
CREATE (:Account {address: row.address, balance: toInteger(row.balance) / 10^8});

// Create transactions
USING PERIODIC COMMIT  
LOAD CSV WITH HEADERS FROM "file:///trs.csv" AS row  
CREATE (:Transaction {tid: row.id, timestamp: date(datetime({epochSeconds: toInteger(row.timestamp) + 1464109200})), amount: toInteger(row.amount) / 10^8});

// Create indexes
CREATE INDEX ON :Account(address);  
CREATE INDEX ON :Transaction(tid);

// Create SEND_TO relationship
USING PERIODIC COMMIT  
LOAD CSV WITH HEADERS FROM "file:///trs.csv" AS row  
MATCH (sender:Account {address: row.senderId})  
MATCH (recipient:Account {address: row.recipientId})  
MERGE (sender)-[:SENT_TO]->(recipient);  

It creates Neo4j models (Account, Transaction) and (SENT_TO) relationship.

Explore blockchain transactions flow

Finally, you're ready to explore Lisk accounts and transactions flow with Neo4j browser.

Open Neo4j browser in your browser http://localhost:7474/ and enter initial query:

MATCH (sender:Account { address: '5055500162731337929L' })-[:SENT_TO]->(recipient:Account)  
RETURN sender, recipient  

Maximize graph window and start exploring:

Resources

SQL on Lisk blockchain via Lisk Docker and Adminer

How-To: Run Neo4j in Docker

Tutorial: Import Relational Data Into Neo4j