Use cases

The examples below were tested under the Bitcoin Mainnet.

Please follow the instruction at https://github.com/bitbart/blockchain-analytics-api in order to test the examples.

A basic view of the Bitcoin blockchain

Our first case study focusses on a basic view of the Bitcoin blockchain containing no external data.

The documents in the resulting collection represent transactions, and they include: (i) the transaction hash; (ii) the hash of the enclosing block; (iii) the date in which the block was appended to the blockchain; (iv) the list of transaction inputs and outputs.

myblockchain_BTC_MongoDB.scala | myblockchain_BTC_MySQL.scala | myblockchain_ETH_MongoDB.scala

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package tcs.examples.bitcoin.mongo

import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.db.DatabaseSettings
import tcs.mongo.Collection

object MyBlockchain {
  def main(args: Array[String]): Unit = {

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet))
    val mongo = new DatabaseSettings("myDatabase")

    val myBlockchain = new Collection("myBlockchain", mongo)

    blockchain.foreach(block => {
      block.bitcoinTxs.foreach(tx => {
        myBlockchain.append(List(
          ("txHash", tx.hash),
          ("blockHash", block.hash),
          ("date", block.date),
          ("inputs", tx.inputs),
          ("outputs", tx.outputs)
        ))
      })
    })

    myBlockchain.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package tcs.examples.bitcoin.sql

import scalikejdbc._
import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.db.mysql.Table
import tcs.db.{DatabaseSettings, MySQL}
import tcs.utils.DateConverter.convertDate


object MyBlockchain{
  def main(args: Array[String]): Unit ={

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet))
    val mySQL = new DatabaseSettings("myblockchain", MySQL, "user", "password")

    val txTable = new Table(sql"""
      create table if not exists transaction(
        txid int(10) unsigned auto_increment not null primary key,
        transactionHash varchar(256) not null,
        blockHash varchar(256) not null,
        timestamp TIMESTAMP not null
      ) """,
      sql"""insert into transaction(transactionHash, blockHash, timestamp) values (?, ?, ?)""",
      mySQL)

    val inTable = new Table(sql"""
      create table if not exists input(
        id int(10) unsigned auto_increment not null primary key,
        transactionHash varchar(256) not null,
        inputScript text not null
      ) """,
      sql"""insert into input(transactionHash, inputScript) values (?, ?)""",
      mySQL)

    val outTable = new Table(sql"""
      create table if not exists output(
        id int(10) unsigned auto_increment not null primary key,
        transactionHash varchar(256) not null,
        outputScript text not null
      ) """,
      sql"""insert into output(transactionHash, outputScript) values (?, ?)""",
      mySQL)


    blockchain.end(473100).foreach(block => {
      block.bitcoinTxs.foreach(tx => {

        txTable.insert(Seq(tx.hash.toString, block.hash.toString, convertDate(block.date)))

        tx.inputs.foreach(in => { inTable.insert(Seq(tx.hash.toString, in.inScript.toString)) })

        tx.outputs.foreach(out => { outTable.insert(Seq(tx.hash.toString, out.outScript.toString)) })
      })

      if (block.height % 10000 == 0)
        println(block.height)
    })

    txTable.close
    inTable.close
    outTable.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package tcs.examples.ethereum

import java.util.Date

import tcs.blockchain.BlockchainLib
import tcs.mongo.Collection
import tcs.db.DatabaseSettings

object MyBlockchain {
  def main(args: Array[String]): Unit = {
    val blockchain = BlockchainLib.getEthereumBlockchain("http://localhost:8545").setStart(70000).setEnd(120000)
    val mongo = new DatabaseSettings("myDatabase")
    val weiIntoEth = BigInt("1000000000000000000")
    val myBlockchain = new Collection("myBlockchain", mongo)

    blockchain.foreach(block => {
      if(block.number % 1000 == 0){
        println("Current block ->" + block.number)
      }
      val date = new Date(block.timeStamp.longValue()*1000)
      block.transactions.foreach(tx => {
        val internalTransactions = block.internalTransactions.filter(itx => itx.parentTxHash.equals(tx.hash))
        val creates = if(tx.creates == null) "" else tx.creates
        val to = if(tx.to == null) "" else tx.to
        val list = List(
          ("txHash", tx.hash),
          ("blockHeight", tx.blockNumber.toString()),
          ("txIndex", tx.transactionIndex),
          ("date", date),
          ("from", tx.from),
          ("to", to),
          ("value", tx.value.doubleValue()/weiIntoEth.doubleValue()),
          ("creates", creates),
          ("internalTransactions", internalTransactions)
        )
        myBlockchain.append(list)
      })
    })

    myBlockchain.close
  }
}

Running this piece of code results in a collection, which we can process with the query language of MongoDB to obtain several standard statistics. Hereafter we consider the following analyses:


Analysing OP_RETURN metadata

In this use case we analyse the usage of the Bitcoin blockchain as a distributed storage.

Actually, besides being used as a cryptocurrency, Bitcoin allows for appending a few bytes of metadata to transaction outputs. This is done preeminently through the OP_RETURN operator of the Bitcoin scripting language. Several protocols exploit this feature to implement blockchain-based applications, like e.g. digital assets and notarization services. These protocols usually mark their transactions by pre-pending an identifier to each piece of metadata. Hence, a way to analyse the usage of metadata in Bitcoin is to count the transactions of each protocol. To this purpose, we construct a view of the blockchain which exposes the protocol metadata.

More specifically, we build a collection opReturnOutputs whose documents represent transaction outputs, and are composed of: (i) the hash of the transaction containing the output; (ii) the date in which the transaction has been appended to the blockchain; (iii) the name of the protocol that produced the transaction; (iv) the metadata contained in the OP_RETURN script.

opreturn_BTC_MongoDB.scala | opreturn_BTC_MySQL.scala

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
package tcs.examples.bitcoin.mongo

import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.db.DatabaseSettings
import tcs.mongo.Collection

object OpReturnOutputs {
  def main(args: Array[String]): Unit ={

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet))
    val mongo = new DatabaseSettings("myDatabase")

    val opReturnOutputs = new Collection("opReturn", mongo)

    blockchain.end(480000).foreach(block => {
      block.bitcoinTxs.foreach(tx => {
        tx.outputs.foreach(out => {
          if(out.isOpreturn()) {
            opReturnOutputs.append(List(
              ("txHash", tx.hash),
              ("date", block.date),
              ("protocol", OpReturn.getApplication(tx.inputs.head.outPoint.toString.substring(0, 64), out.outScript.toString)),
              ("metadata", out.getMetadata())
            ))
          }
        })
      })
    })

    opReturnOutputs.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package tcs.examples.bitcoin.sql

import scalikejdbc._
import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.custom.OpReturn
import tcs.db.{DatabaseSettings, MySQL}
import tcs.db.mysql.Table

object OpReturnOutputs {
  def main(args: Array[String]): Unit ={

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet))
    val mySQL = new DatabaseSettings("opreturn", MySQL, "user", "password")

    val outputTable = new Table(sql"""
      create table if not exists opreturnoutput(
        id serial not null primary key,
        transactionHash varchar(256) not null,
        txdate TIMESTAMP not null,
        protocol varchar(64) not null,
        metadata text not null
    )""", mySQL)

    blockchain.start(290000).end(473100).foreach(block => {

      block.bitcoinTxs.foreach(tx => {
        tx.outputs.foreach(out => {
          if(out.isOpreturn()) {
            var protocol: String = OpReturn.getApplication(tx.inputs.head.outPoint.toString.substring(0, 64), out.outScript.toString)
            var metadata: String = out.getMetadata()
            outputTable.insert(sql"insert into opreturnoutput (transactionHash, txdate, protocol, metadata) values (${tx.hash.toString}, ${block.date}, ${protocol}, ${metadata})")
          }
        })
      })
    })

    outputTable.close
  }
}

Hereafter we consider the following analyses:


Exchange rates

Several analyses use exchange rates for quantifying the economic impact of various phenomena (e.g. cyber-crime attacks, transaction fees, business activities).

In this use case we analyse how the value transferred in transactions is affected by the exchange rate between USD and BTC over the years. Since exchange rates are not stored in the Bitcoin blockchain, we need to obtain these data from an external source, e.g. the Coindesk APIs. Using these data, we construct a blockchain view where each transaction is associated with the exchange rate at the time it has been appended to the blockchain.

More specifically, we construct a MongoDB collection whose documents represent transactions containing: (i) the transaction hash; (ii) the sum of its output values (in BTC); (iii) the date in which the transaction has been appended to the blockchain; (iv) the exchange rate between BTC and USD in such date.

exchange_BTC_MongoDB.scala | exchange_BTC_MySQL.scala | exchange_ETH_MongoDB.scala

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package tcs.examples.bitcoin.mongo

import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.custom.Exchange
import tcs.db.DatabaseSettings
import tcs.mongo.Collection

object TxWithRates {
  def main(args: Array[String]): Unit = {

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet))
    val mongo = new DatabaseSettings("myDatabase")

    val txWithRates = new Collection("txWithRates", mongo)

    blockchain.foreach(block => {
      block.bitcoinTxs.foreach(tx => {
        txWithRates.append(List(
          ("txHash", tx.hash),
          ("date", block.date),
          ("outputSum", tx.getOutputsSum()),
          ("rate", Exchange.getRate(block.date))
        ))
      })
    })

    txWithRates.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package tcs.examples.bitcoin.sql

import scalikejdbc._
import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.custom.bitcoin.Exchange
import tcs.db.{DatabaseSettings, MySQL}
import tcs.db.mysql.Table
import tcs.utils.DateConverter
import tcs.utils.DateConverter.convertDate

object TxWithRates {
  def main(args: Array[String]): Unit = {

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet))
    val mySQL = new DatabaseSettings("rates", MySQL, "user", "password")

    val txTable = new Table(
      sql"""
      create table if not exists txrates(
        id serial not null primary key,
        txHash varchar(256) not null,
        txdate TIMESTAMP not null,
        outputsum bigint,
        rate float
    )""",
      sql"""insert into txrates (txHash, txdate, outputsum, rate) values(?,?,?,?)""",
      mySQL)


    blockchain.end(473100).foreach(block => {

      if (block.height % 10000 == 0) println(block.height)

      block.bitcoinTxs.foreach(tx => {
        txTable.insert(Seq(
          tx.hash.toString,
          convertDate(block.date),
          tx.getOutputsSum(),
          Exchange.getRate(block.date)))
      })
    })

    txTable.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package tcs.examples.ethereum

import java.text.SimpleDateFormat
import java.util.Date

import tcs.blockchain.BlockchainLib
import tcs.custom.ethereum.PriceHistorical
import tcs.mongo.Collection
import tcs.db.DatabaseSettings

object TxWithRates {
  def main(args: Array[String]): Unit = {
    val blockchain = BlockchainLib.getEthereumBlockchain("http://localhost:8545").setStart(70000).setEnd(150000)
    val mongo = new DatabaseSettings("myDatabase")
    val weiIntoEth = BigInt("1000000000000000000")
    val txWithRates = new Collection("txWithRates", mongo)
    val format = new SimpleDateFormat("yyyy-MM-dd")
    val priceHistorical = PriceHistorical.getPriceHistorical()

    blockchain.foreach(block => {
      if(block.number % 1000 == 0){
        println("Current block ->" + block.number)
      }
      val date = new Date(block.timeStamp.longValue()*1000)
      val dateFormatted = format.format(date)
      block.transactions.foreach(tx => {
        val creates = if(tx.creates == null) "" else tx.creates
        val to = if(tx.to == null) "" else tx.to
        val list = List(
          ("txHash", tx.hash),
          ("blockHeight", tx.blockNumber.toString()),
          ("txIndex", tx.transactionIndex),
          ("date", date),
          ("from", tx.from),
          ("to", to),
          ("value", tx.value.doubleValue()/weiIntoEth.doubleValue()),
          ("creates", creates),
          ("rate", if(block.timeStamp.longValue() < 1438905600) 0 else priceHistorical.price_usd(dateFormatted))
        )
        txWithRates.append(list)
      })
    })

    txWithRates.close
  }
}

Hereafter we consider the following analyses:


Transaction fees

In this use case we study transaction fees, which are earned by miners when they append a new block to the Bitcoin blockchain.

Each transaction in the block pays a fee, defined as the difference between its input and output values. While the values of outputs are stored explicitly in the transaction, those of inputs are not: to obtain them, one must retrieve from a past block the transaction that is redeemed by the input. This can be obtained through a “deep” scan of the blockchain, which is featured by our library.

We show how to construct a collection which contains, for each transaction: (i) the hash of the enclosing block; (ii) the transaction hash; (iii) the fee; (iv) the date in which the transaction was appended to the blockchain; (v) the exchange rate between BTC and USD in such date.

fees_BTC_MongoDB.scala | fees_BTC_MySQL.scala | fees_ETH_MongoDB.scala

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package tcs.examples.bitcoin.mongo

import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.custom.Exchange
import tcs.db.DatabaseSettings
import tcs.mongo.Collection

object TxWithFees {
  def main(args: Array[String]): Unit = {

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet, true))
    val mongo = new DatabaseSettings("myDatabase")

    val txWithFees = new Collection("txWithFees", mongo)

    blockchain.foreach(block => {
     block.bitcoinTxs.foreach(tx => {
        txWithFees.append(List(
          ("blockHash", block.hash),
          ("txHash", tx.hash),
          ("date", block.date),
          ("fee", tx.getInputsSum() - tx.getOutputsSum()),
          ("rate", Exchange.getRate(block.date))
        ))
      })
    })

    txWithFees.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
package tcs.examples.bitcoin.sql

import scalikejdbc._
import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.custom.bitcoin.Exchange
import tcs.db.{DatabaseSettings, MySQL}
import tcs.db.mysql.Table
import tcs.utils.DateConverter
import tcs.utils.DateConverter.convertDate

object TxWithFees {
  def main(args: Array[String]): Unit = {

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet, true))
    val mySQL = new DatabaseSettings("fees", MySQL, "user", "password")

    val txTable = new Table(
      sql"""
      create table if not exists txfees(
        id serial not null primary key,
        blockHash varchar(256) not null,
        transactionHash varchar(256) not null,
        txdate TIMESTAMP not null,
        fee bigint,
        rate float
    )""",
      sql"""insert into txfees (blockHash, transactionHash, txdate, fee, rate) values(?,?,?,?,?)""",
      mySQL)


    blockchain.foreach(block => {

      if (block.height % 10000 == 0) println(block.height)

      block.bitcoinTxs.foreach(tx => {
        txTable.insert(Seq(
          block.hash.toString,
          tx.hash.toString,
          convertDate(block.date),
          (tx.getInputsSum() - tx.getOutputsSum()),
          Exchange.getRate(block.date)))
      })
    })

    txTable.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package tcs.examples.ethereum

import java.text.SimpleDateFormat
import java.util.Date

import tcs.blockchain.BlockchainLib
import tcs.custom.ethereum.PriceHistorical
import tcs.db.DatabaseSettings
import tcs.mongo.Collection

object TxWithFees {
  def main(args: Array[String]): Unit = {
    val blockchain = BlockchainLib.getEthereumBlockchain("http://localhost:8545").setStart(70000).setEnd(150000)
    val mongo = new DatabaseSettings("myDatabase")
    val weiIntoEth = BigInt("1000000000000000000")
    val txWithFees = new Collection("txWithFees", mongo)
    val format = new SimpleDateFormat("yyyy-MM-dd")
    val priceHistorical = PriceHistorical.getPriceHistorical()

    blockchain.foreach(block => {
      if(block.number % 1000 == 0){
        println("Current block ->" + block.number)
      }
      val date = new Date(block.timeStamp.longValue()*1000)
      val dateFormatted = format.format(date)
      block.transactions.foreach(tx => {
        val creates = if(tx.creates == null) "" else tx.creates
        val to = if(tx.to == null) "" else tx.to
        val list = List(
          ("blockHash", block.hash),
          ("txHash", tx.hash),
          ("date", date),
          ("value", tx.value.doubleValue()/weiIntoEth.doubleValue()),
          ("creates", creates),
          ("gas", tx.gas),
          ("fee", (tx.gas * tx.gasPrice)/weiIntoEth),
          ("rate", if(block.timeStamp.longValue() < 1438905600) 0 else priceHistorical.price_usd(dateFormatted))
        )
        txWithFees.append(list)
      })
    })

    txWithFees.close
  }
}

Hereafter we consider the following analyses:


Address tags

The website blockchain.info/tags hosts a user-generated list of associations between bitcoin addresses and tags which briefly describe their usage. In this section we construct a blockchain view where outputs are associated with the tags of the address which can redeem them (we discard the outputs with untagged addresses).

More specifically, we construct a collection whose documents represent transaction outputs containing: (i) hash of the enclosing transaction; (ii) the date in which the transaction has been appended to the blockchain; (iii) the output value (in BTC); (iv) the address receiving the payment; (v) the tag associated to the address.

tags_BTC_MongoDB.scala | tags_BTC_MySQL.scala

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package tcs.examples.bitcoin.mongo

import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.custom.Tag
import tcs.db.DatabaseSettings
import tcs.mongo.Collection

object AddressesWithTags {
  def main(args: Array[String]): Unit = {

    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet, true))
    val mongo = new DatabaseSettings("myDatabase")

    val outWithTags = new Collection("outWithTags", mongo)
    val tags = new Tag("src/main/scala/tcs/custom/input.txt")

    blockchain.foreach(block => {
      block.bitcoinTxs.foreach(tx => {
        tx.outputs.foreach(out => {
          out.getAddress(MainNet) match {
            case Some(add) =>
              tags.getValue(add) match {
                case Some(tag) =>
                  outWithTags.append(List(
                    ("txHash", tx.hash),
                    ("date", block.date),
                    ("value", out.value),
                    ("address", add),
                    ("tags", tag)
                  ))
                case None =>
              }
            case None =>
          }
        })
      })
    })

    outWithTags.close
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package tcs.examples.bitcoin.sql

import scalikejdbc._
import tcs.blockchain.BlockchainLib
import tcs.blockchain.bitcoin.{BitcoinSettings, MainNet}
import tcs.db.{DatabaseSettings, MySQL}
import tcs.db.mysql.Table
import tcs.custom.Tag

object AddressesWithTags {
  def main(args: Array[String]): Unit = {
    val blockchain = BlockchainLib.getBitcoinBlockchain(new BitcoinSettings("user", "password", "8332", MainNet))
    val mySQL = new DatabaseSettings("outwithtags", MySQL, "user", "password")
    val tags = new Tag("src/main/scala/tcs/custom/input.txt")

    val outTable = new Table(
      sql"""
        create table if not exists tagsoutputs(
          id serial not null primary key,
          transactionHash varchar(256) not null,
          txdate TIMESTAMP not null,
          outvalue bigint unsigned,
          address varchar(256),
          tag varchar(256)
       )""", mySQL)

    blockchain.end(473100).foreach(block => {
      if (block.height % 500 == 0) println(block.height)

      block.bitcoinTxs.foreach(tx => {
        tx.outputs.foreach(out => {
          out.getAddress(MainNet) match {
            case Some(add) =>
              tags.getValue(add) match {
                case Some(tag) => {
                  outTable.insert(sql"insert into tagsoutputs (transactionHash, txdate, outvalue, address, tag) values (${tx.hash.toString}, ${block.date}, ${out.value}, ${add.toString}, ${tags.getValue(add)})")
                }
                case None =>
              }
            case None =>
          }
        })
      })
    })
    outTable.close
  }
}

Hereafter we consider the following analyses:

  • Aggregate all addresses whose tag starts with SatoshiDICE, and then we measure the number of daily transactions which send BTC to one of these addresses.