Tutorial 2: Calculate balance of all bitcoin wallets

In Tutorial 1, we learned how to build a data app on the Chainslake platform to get data from the bitcoin blockchain. In this lesson, Chainslake will guide you to extract raw bitcoin data into blocks, transactions, inputs, outputs tables, from which you can build a balance table of all wallets on bitcoin.

Contents

  1. Extract blocks, transactions, inputs, outputs from raw bitcoin data
  2. Build latest UTXO table
  3. Calculate bitcoin balance of the wallets

Extract blocks, transactions, inputs, outputs from raw bitcoin data

The data retrieved from Bitcoin rpc is raw data in json format stored in bitcoin_origin.transaction_blocks table for each block. Each block consists of many transactions, each transaction contains input and output information, so we need to extract the raw data into corresponding data tables before we can use it.

To extract data, we need to build Data Apps for each type of data that needs to be extracted. First, we need to define data models based on the bitcoin RPC documentation here and here.

package chainslake.bitcoin

...
case class ResponseBlock(var jsonrpc: String,
                         var id: String,
                         var result: TransactionBlock)
...

Next, create scala objects corresponding to each Data App: Blocks, Transactions, Inputs, Outputs

Add name of Data Apps to JobFactory of bitcoin package:

package chainslake.bitcoin

import chainslake.bitcoin.extract.{Blocks, Inputs, Outputs, Transactions}
import chainslake.bitcoin.origin.TransactionBlocks
import chainslake.job.JobInf

object JobFactory {
  def createJob(name: String): JobInf = {
    name match {
      case "bitcoin_origin.transaction_blocks" => TransactionBlocks
      case "bitcoin.blocks" => Blocks
      case "bitcoin.transactions" => Transactions
      case "bitcoin.inputs" => Inputs
      case "bitcoin.outputs" => Outputs
    }
  }
}

Create test files and run tests for Data Apps: test_blocks, test_transactions, test_inputs, test_outputs

Finally create the jobs: extract_blocks, extract_transactions, extract_inputs, extract_outputs and add them to the chainslake pipeline:

...
bitcoin_extract_blocks = BashOperator(
    task_id="bitcoin.blocks",
    bash_command=f"cd {RUN_DIR} && ./extract/blocks.sh "
)

bitcoin_extract_transactions = BashOperator(
    task_id="bitcoin.transactions",
    bash_command=f"cd {RUN_DIR} && ./extract/transactions.sh "
)

bitcoin_extract_inputs = BashOperator(
    task_id="bitcoin.inputs",
    bash_command=f"cd {RUN_DIR} && ./extract/inputs.sh "
)

bitcoin_extract_outputs = BashOperator(
    task_id="bitcoin.outputs",
    bash_command=f"cd {RUN_DIR} && ./extract/outputs.sh "
)

bitcoin_origin_transaction_blocks >> [bitcoin_extract_blocks, bitcoin_extract_transactions, bitcoin_extract_inputs, bitcoin_extract_outputs]
...

Once completed, we need to commit and create PR to trigger the review process like previous article

Since your data apps have been executed on the Chainslake platform, you will see the data tables here.

Build latest UTXO table

Bitcoin uses the UTXO mechanism to keep track of wallet balances. Simply put, the balance of a wallet is equal to the total balance of the UTXOs that the wallet owns. Therefore, to calculate the balance of wallets on bitcoin, we need to build a table of the latest UTXOs.

We will use SQL Transformer Data App to build the necessary tables using SQL language including:

Create jobs utxo_transfer_hour, utxo_transfer_day, utxo_latest_day then add them to chainslake pipeline.

bitcoin_balances_utxo_transfer_hour = BashOperator(
    task_id="bitcoin_balances.utxo_transfer_hour",
    bash_command=f"cd {RUN_DIR} && ./balances/utxo_transfer_hour.sh "
)

bitcoin_balances_utxo_transfer_day = BashOperator(
    task_id="bitcoin_balances.utxo_transfer_day",
    bash_command=f"cd {RUN_DIR} && ./balances/utxo_transfer_day.sh "
)

bitcoin_balances_utxo_latest_day = BashOperator(
    task_id="bitcoin_balances.utxo_latest_day",
    bash_command=f"cd {RUN_DIR} && ./balances/utxo_latest_day.sh "
)

[bitcoin_extract_inputs, bitcoin_extract_outputs] >> bitcoin_balances_utxo_transfer_hour >> bitcoin_balances_utxo_transfer_day >> bitcoin_balances_utxo_latest_day

Commit and create PR to trigger the review process like previous article

Once your jobs have been executed on the Chainslake platform, you will see the data tables here.

Calculate bitcoin balance of the wallets

Now we need to build a dashboard on Chainslake platform to calculate the wallet balance. You can refer to the sample dashboard here or try it here