How should I store data for a transaction graph?

Question

I am trying to make bitcoin transaction graph, but I am not sure how to go about it. I have extracted the necessary data from blockchain like the sender, receiver, amount of bitcoins sent, transaction hash and the date and time when it first got included in the block. The format for which is shown below:-

TXID                                                                       Input Address                  Value               Output Address                    Time & Dates

363ec29e4fc43f97576423d0d522f5f0fc79c5c018c3a210c5644ab79a38041d                                        25.0301   [u'1CjPR7Z5ZSyWk6WtXvSFgkptmpoi4UM9BC']    2014-01-12 01:36:19

f264c5c36b624110201a27bd02883508d29dfe1f94975aa8cc652fb8bc1496a9 1AyHWFST4jhcdVs74Hd3pvCdafpktmiF8Y       299.0   [u'13vZBq8ayCzficfP3uwm52bctv5mSXKfM1']    2014-01-12 01:36:19
                                                             154yacyVMuVoahSd1dmsz6PDS8MZzX3FzB
                                                             1KzXSypcJUdStaHfLtucBiaqEWDZ5PhBix
                                                             14FJfPxbXpBrTPnPcCS1gwWpTz7XVmSJeQ
                                                             15CdDBvSfr5JsnbqoSgnEdSFvtKwf1GoNj   

I am not sure what should be my first step towards constructing transaction graph. I am trying to use hadoopcryptoledger library available in Apache spark for doing so, but not sure how to use the result that I have extracted from blockchain for that. Do I first need to create a database to store my result? The file that contains the above information is in .txt format. Any help would be appreciated.

Answer 1

The only reasonable way (in time and space required) to do this is parsing the raw file -> decode the information -> build the transaction graph and store the result in a file

I did it in my bachelor thesis (before Taproot) so if you are going to use my software enter code here

I wrote a parser from the Bitcoin primitive that it is able to convert all blockchain into JSON by adding extra information like the hash of the current block.

This will make the analysis much easier

Parser: https://github.com/vincenzopalazzo/SpyCblock Visualizer: https://vincenzopalazzo.github.io/SpyJSBlock.react/

P.S: The C++ code required some more love and also the cmake build system

Answer 2

Can you provide an example or design document to illustrate where are you stuck?

Spark offers SparkX to create a Bitcoin transaction graph. There is an example on the hadoopcryptoledger web site: https://github.com/ZuInnoTe/hadoopcryptoledger/wiki/Using-Spark-Scala-Graphx-to-analyze-the-Bitcoin-transaction-graph

best regards

Answer 3

The data that you collected is insufficient to create a transaction graph in full detail. Bitcoin is transferred in uniquely identifiable transaction outputs. This means that you can do better than to just associate with the address that funds were sent to, you can specifically identify which transaction funds came from.

You should look to collect the outpoints of transaction outputs consisting of the txid and output index for each output and input. The addresses (or output script) will provide an additional dimension of information by telling you which type of output was used, and tying together instances of address reuse.

When you store the data, a database would be useful as it would simplify handling the large amount of data. Since the transaction outputs’ outpoints are unique, you might want to use that as the key for the tracked outputs. Other useful fields may be “receive address“, “amount”, “created by transaction”, “output index”, “spent by transaction”, “input index”, “created in block”, “spent in block”, and “output script type”.

Since the spending can happen arbitrary amounts of time after the creation of an output, it might help to have two separate tables for “outputs” and “inputs”, which both use the “outpoint” as the unique key. A transactions view could be generated from these two tables.

There are multiple opensource projects that track this data, you may want to look at them for inspiration. See e.g. Mempool.space, Esplora.

Answer 4

While it's possible to use Hadoop and Spark for processing and analysis, you'll need to load your data into a suitable storage format that these technologies can work with. Common choices include Parquet, Avro, or even a database like HBase. You can also use tools like Hive or Spark SQL to query and manipulate the data once it's loaded.

Storing your data in a database is a good approach, especially for large datasets.