<p>There are roughly two approaches as you've noticed:</p>

<ol>
<li>process the block <code>.dat</code> files from running <code>bitcoind</code></li>
<li>rpc to a node, pull blocks or headers, and batch process them in parallel</li>
</ol>

<p>For speed, probably doing #1 is best, as <code>BitcoinDataBaseGenerator</code> 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.  </p>

<p>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.</p>

<p>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.  </p>

<p>Having said that, the Cross-Chain Group has a <a href="https://github.com/summa-tx/riemann-zeta" rel="nofollow noreferrer">working implementation</a> 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.</p>

CC BY-SA 4.0
2020-02-02T17:26:28.850Z
2020-02-02T17:26:28.850Z
Chan-Ho Suh
<p>Thanks to <a href="https://bitcoin.stackexchange.com/users/24925/anonymous">@Anonymous</a> who pointed out <a href="https://github.com/rustyrussell/bitcoin-iterate" rel="nofollow noreferrer">bitcoin-iterate</a>, I think I have found a solution that will work for me.</p>

<p>This tool makes it very easy to extract all data you need in a <a href="https://github.com/rustyrussell/bitcoin-iterate/blob/master/doc/bitcoin-iterate.1.txt" rel="nofollow noreferrer">custom format</a>. All the tools that I listed in my original question had some pre-defined structure.</p>

<p>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.</p>

<pre><code>{
  "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
            }
          ]
        }
      ]
    }
  ]
}
</code></pre>

<p>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.</p>

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

<pre><code>./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 &gt;/dev/null
</code></pre>

<p>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.</p>

CC BY-SA 4.0
2020-02-02T22:57:21.630Z
2020-02-02T23:26:09.280Z
2020-02-02T23:26:09.280Z
Johannes Mols
<p>You may use my <a href="https://github.com/ragestack/blockchain-parser" rel="nofollow noreferrer">blockchain parser</a>. 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.</p>

<p>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.</p>

CC BY-SA 4.0
2020-03-20T12:47:45.937Z
2020-03-20T13:01:59.590Z
2020-03-20T13:01:59.590Z
Denis Leonov
<p><a href="https://github.com/tenthirtyone/blocktools" rel="nofollow noreferrer">https://github.com/tenthirtyone/blocktools</a>. this program will help you ,but it doesn't put data to the data base,you need to change it.</p>

CC BY-SA 4.0
2020-03-15T04:47:00.073Z
2020-03-15T04:47:00.073Z
张振永
<p>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.</p>

<p>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.</p>

<p>There <a href="https://github.com/igorbarinov/awesome-bitcoin#blockchain-dump" rel="nofollow noreferrer">is a list of tools</a> available to do this, and I have had problems with all of them:</p>

<ul>
<li><strong>WebBTC:</strong> The files don't seem to be available</li>
<li><strong>BitcoinDataBaseGenerator:</strong> 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.</li>
<li><strong>Blockparser + SQL:</strong> This only keeps the data in memory.</li>
<li><strong>BitcoinABE:</strong> 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.</li>
<li><strong>NBitcoin.Indexer:</strong> Made for Azure, I want to have a local database though.</li>
<li><strong>Blockchain2Graph:</strong> 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.</li>
</ul>

<p>Then I found one more on Google:</p>

<ul>
<li><strong><a href="https://github.com/in3rsha/bitcoin-to-neo4j" rel="nofollow noreferrer">Bitcoin-to-neo4j</a>:</strong> 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.</li>
</ul>

<p>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?</p>

CC BY-SA 4.0
2020-02-02T00:25:53.527Z
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
CC BY-SA 4.0
2020-02-02T00:30:55.547Z
@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.
Johannes Mols
CC BY-SA 4.0
2020-02-02T00:32:00.050Z
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
CC BY-SA 4.0
2020-02-02T00:33:26.430Z
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](https://github.com/rustyrussell/bitcoin-iterate) if you want to do the same.
Claris
CC BY-SA 4.0
2020-02-02T00:35:01.233Z
@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.
Johannes Mols
CC BY-SA 4.0
2020-02-02T00:35:46.403Z
I linked a tool above which can filter the entire 300GB chain for OP_RETURN outputs in a couple of minutes, limited by your disk speed only.
Claris
CC BY-SA 4.0
2020-02-02T00:39:01.547Z
@Anonymous Thank you, that seems like a good solution, I will check it out. I wasn't aware of any tools that can do this, which is why I made this post.
Johannes Mols
CC BY-SA 4.0
2020-02-02T20:33:56.860Z
I want to add another idea, I worked on analysis of Bitcoin blockchain and I the result was that insert all information on database is inefficient but is possible. I have proposed the new solution for problem to analysis Bircoon blockchain. My idea is, convert the blk into json version and developer an Softwer for easy developed of different analysis Bitcoin blockchain into json version. I can implement an answer with more details
vincenzopalazzo
<bitcoin-core><database><op-return><mysql>
What is the currently most efficient and reliable method to store the Bitcoin blockchain in a local database?
Johannes Mols
# What is the currently most efficient and reliable method to store the Bitcoin blockchain in a local database?

(Asked by: username_0 on 2020-02-01)

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](https://github.com/igorbarinov/awesome-bitcoin#blockchain-dump) 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](https://github.com/in3rsha/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?

**Tags:** bitcoin-core, database, op-return, mysql

> **Comments:**
> **username_1**: 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.  
> **username_0**: @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.  
> **username_1**: 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.  
> **username_1**: 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](https://github.com/rustyrussell/bitcoin-iterate) if you want to do the same.  
> **username_0**: @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.  
> **username_1**: I linked a tool above which can filter the entire 300GB chain for OP_RETURN outputs in a couple of minutes, limited by your disk speed only.  
> **username_0**: @Anonymous Thank you, that seems like a good solution, I will check it out. I wasn't aware of any tools that can do this, which is why I made this post.  
> **username_2**: I want to add another idea, I worked on analysis of Bitcoin blockchain and I the result was that insert all information on database is inefficient but is possible. I have proposed the new solution for problem to analysis Bircoon blockchain. My idea is, convert the blk into json version and developer an Softwer for easy developed of different analysis Bitcoin blockchain into json version. I can implement an answer with more details  

---

## 4 answers

### Answer by username_3 on 2020-02-02. Score: 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](https://github.com/summa-tx/riemann-zeta) 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 by username_0 on 2020-02-02. Score: 1

Thanks to [@Anonymous](https://bitcoin.stackexchange.com/users/24925/anonymous) who pointed out [bitcoin-iterate](https://github.com/rustyrussell/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](https://github.com/rustyrussell/bitcoin-iterate/blob/master/doc/bitcoin-iterate.1.txt). 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 by username_4 on 2020-03-20. Score: 0

You may use my [blockchain parser](https://github.com/ragestack/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.


---

### Answer by username_5 on 2020-03-15. Score: 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.


---

