4

I have seen several posts on this before, and have tried all the suggested solutions, but I can't get any of them to work.

I am interested in analysing transactions with the OP_RETURN opcode for a project, and need to dump the entire Bitcoin in a database to start working on that. I have already installed Bitcoin Core and downloaded all data until today.

There is a list of tools available to do this, and I have had problems with all of them:

  • WebBTC: The files don't seem to be available
  • BitcoinDataBaseGenerator: This seems like the most promising solution, as it is easy to set up on Windows, and a MS SQL Server database is easy to use. However, after file blk005**.dat it crashed, saying that there are blocks with an unknown version. I also can't try to run it again because the queries to delete data from that file won't finish executing and timeout after ~2 hours. Looking at the issues at GitHub, people seem to be having problems with that too, as the repo is not maintained any more.
  • Blockparser + SQL: This only keeps the data in memory.
  • BitcoinABE: I have not tried this one yet. I read many issues that it is very slow though, and there are generally many open issues. Might work though, I will try later.
  • NBitcoin.Indexer: Made for Azure, I want to have a local database though.
  • Blockchain2Graph: This also seems promising, and it is under active development. It uses neo4j, which is fine. At the moment I have no preference for either SQL or graph databases. I set up a Debian Virtual Machine and followed all the steps, which did work. However, at the end I can't get the application to actually show up in the browser. From my understanding it uses RPC calls and not the blk*.dat files directly.

Then I found one more on Google:

  • Bitcoin-to-neo4j: This takes the block files directly and puts them in a neo4j database. The problem with this is that the database is apparently six times the size of the actual Bitcoin data. I don't have 1.7 Terabytes of free storage space available. It also mentions that it takes several weeks to process it all. I simply don't have the time to wait for that.

So, my question is if there are any other tools currently available to easily write the Bitcoin blk*.dat files to a local database. Maybe I did something wrong, with the above mentioned solutions, but none of them seem to be an ideal solution. How easy would it be to write a custom application that only extracts the data I'm interested in, and are there code templates for that?

8
  • The most efficient thing to do is design your tools to not need this sort of implementation. There are no open source tools which will be able to do this efficiently given to the sheer amount of data in the block chain (and the indexes that you'll no doubt be wanting). You're talking about interacting with billions of rows in a database, with a huge amount of cross relations that would have been to kept up to date as the data changes, it's a non-trivial engineering challenge that would need serious server hardware behind it to execute.
    – Claris
    Feb 2, 2020 at 0:25
  • @Anonymous I agree, it's not a trivial task. However, all the tools above show that it is possible. Especially BitcoinDatabaseGenerator seems like a good solution, as that takes care of indexing and the resulting data is not larger than the original data. The problem is not the storage or processing power required to do this, as I would query only once for the results I want, not on demand. I might be successful if I modify that application a little, but it's weird that there is no easy solution to this. Feb 2, 2020 at 0:30
  • The tools not working is a good demonstration that it isn't possible. If you're just interested in the small number of OP_RETURN outputs, why do you need complete indexing of the block chain? They're a vanishingly small portion of the total size.
    – Claris
    Feb 2, 2020 at 0:32
  • 3
    For reference, it takes under a couple of minutes for me to read every block from disk and filter it for outputs which contain a OP_RETURN. Making less generalized tools is a powerful solution rather than throwing an entire engineering team at something unnecessary. You might be interested in bitcoin-iterate if you want to do the same.
    – Claris
    Feb 2, 2020 at 0:33
  • @Anonymous I need to have some way of getting all transactions with OP_RETURN outputs, and the only way to do this is to go through the entire blockchain. If there are any datasets or maybe even APIs out there that do this, please point me in that direction. Of course it's overkill, but I don't see another way currently. Feb 2, 2020 at 0:35

4 Answers 4

1

There are roughly two approaches as you've noticed:

  1. process the block .dat files from running bitcoind
  2. rpc to a node, pull blocks or headers, and batch process them in parallel

For speed, probably doing #1 is best, as BitcoinDataBaseGenerator does. Unfortunately, as you've noted, it stopped being maintained in early 2017 and so does not support Segwit. I think in practice, a lot of people do 2, as you can then rely upon external nodes maintained by others.

I have a working prototype using #2, but will not link it here, as it is missing some things. I only mention this to say that I did not find it tremendously difficult to do so (the work I did was scattered over a period of time where I was busy with other things also). I think probably the real challenge is trying to construct the right tables for whatever you need; the actual work to persist the blockchain data into say, a Postgres db, is fairly straightforward. It did take me a week or so to persist the data, but this was using a small EC2 instance.

It is rather surprising solutions aren't more widely available, but in the end, understandable. I do think everyone needs to tweak their databases a certain way and it's just easier to process the data yourself in a way you understand.

Having said that, the Cross-Chain Group has a working implementation that connects to an Electrum server and persists it into sqlite. Looking through the code, I don't believe it would be difficult to change the persistence code to persist to another db. They've done the hard work of processing the block headers and transactions.

1

Thanks to @Anonymous who pointed out bitcoin-iterate, I think I have found a solution that will work for me.

This tool makes it very easy to extract all data you need in a custom format. All the tools that I listed in my original question had some pre-defined structure.

I decided on a preliminary data structure that I defined in JSON. I will probably remove some values later on when I realize that I don't need them.

{
  "blocks": [
    {
      "bLength": 0,
      "bVersion": 0,
      "bTimestamp": 0,
      "bTxCount": 0,
      "bHash": "",
      "bHeight": 0,
      "transactions": [
        {
          "tNumInBlock": 0,
          "tHash": "",
          "tVersion": 0,
          "tInputCount": 0,
          "tOutputCount": 0,
          "inputs": [
            {
              "iHash": "",
              "iIndex": 0,
              "iScriptLength": 0,
              "iScript": "",
              "iSeqValue": "",
              "iNumInTx": 0,
              "iUTXOBlockNum": 0,
              "iUTXOTxNum": 0
            }
          ],
          "outputs": [
            {
              "oAmount": 0,
              "oScriptLength": 0,
              "oScript": "",
              "oNumInTx": 0,
              "oUnspendable": 0
            }
          ]
        }
      ]
    }
  ]
}

When I have the entire data in that file, I will probably try to write that to a database, and a graph database such as neo4j seems like a good option to do that. However, I'm open for suggestions on that one.

To get the data in that format, I'm using the following bash command to run bitcoin-iterate:

./bitcoin-iterate --blockdir='path/to/blocks' \
--cache='path/to/cache' --quiet --progress='100' \
--block='{"bLength":%bl,"bVersion":%bv,"bTimestamp":%bs,"bTxCount":%bc,"bHash":"%bh","bHeight":%bN,"transactions":[]}' \
--transaction='{"tNumInBlock":%tN,"tHash":"%th","tVersion":%tv,"tInputCount":%ti,"tOutputCount":%to,"inputs":[],"outputs":[]}' \
--input='{"iHash":"%ih","iIndex":%ii,"iScriptLength":%il,"iScript":"%is","iSeqValue":"%iq","iNumInTx":%iN,"iUTXOBlockNum":%iB,"iUTXOTxNum":%iT}' \
--output='{"oAmount":%oa,"oScriptLength":%ol,"oScript":"%os","oNumInTx":%oN,"oUnspendable":oU}' \
| sudo tee path/to/output.txt >/dev/null

The problem with this is that each block, transaction, input, and output will output their formatted string in the order they arrive. This means that I can't nest the JSON, as there will be multiple transactions per block, and multiple I/O per transaction. To solve this, I will write a small helper script which takes the JSON file with a JSON object on each line and move the objects into the pre-defined arrays. I couldn't find a better solution to this yet, and I'm also open for suggestions on that one.

0

https://github.com/tenthirtyone/blocktools. this program will help you ,but it doesn't put data to the data base,you need to change it.

0
0

You may use my blockchain parser. It convert dat files to a simple text stream (files), and it is more than easy to change the code for yours needs. For OP_RETURN opcode analysing you need to focus on "script" fields and do some tiny changes for to goal your result. I think that my answer would be helpful for you.

But if the question is really about the the most efficient and reliable method to store the Bitcoin blockchain in a local database for the opcode analysing that you want to proceed, so I think would be better to parse dat files uses parser into the some simple and lightweight form (not the all data from dat files) and then do what you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.