What is the currently most efficient and reliable method to store the Bitcoin blockchain in a local database?

Question

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?

Answer 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.

Answer 2

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.

Answer 3

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.

Answer 4

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.