<blockquote>
<p>With almost 2k transaction per block, it will take a lot of queries per block.</p>
</blockquote>
<p>The <a href="https://developer.bitcoin.org/reference/rpc/getblock.html" rel="nofollow noreferrer"><code>getblock</code> RPC</a> supports a <code>verbosity=2</code> argument which returns all the transactions in the block as JSON objects, so you can make do with a single query per block.</p>
<p>With <a href="https://www.jsonrpc.org/specification#batch" rel="nofollow noreferrer">RPC batching</a> (i.e. sending multiple commands in a single request), you can do even better! You can query the transactions of <code>n</code> blocks with just 2 RPC requests: one to get all the block hashes, and one to get the blocks (with transactions). The below code snippet shows how you can implement both approaches, and includes a simple performance benchmark. On my machine, the batch approach is ~13x faster when querying the first 2000 blocks.</p>
<p>An example Python implementation, which should work out of the box if:</p>
<ul>
<li><code>requests</code> is installed (<code>pip3 install requests</code>)</li>
<li><code>bitcoind -signet -rpcuser=user -rpcpassword=pass</code></li>
</ul>
<pre class="lang-python prettyprint-override"><code>import json
import time
from typing import List

import requests


def get_n_blockhashes(n: int, start_height: int = 0):
    data = [{
        &quot;method&quot;: &quot;getblockhash&quot;,
        &quot;params&quot;: [height]
    } for height in range(start_height, start_height + n)]
    hashes = [item[&quot;result&quot;] for item in make_request(data)]
    return hashes


def get_block_transactions_single(block_hashes: List[str]):
    transactions = []
    for block_hash in block_hashes:
        data = {
            &quot;method&quot;: &quot;getblock&quot;,
            &quot;params&quot;: [block_hash, 2]
        }
        block_data = make_request(data)[&quot;result&quot;]
        transactions.append(block_data[&quot;tx&quot;])

    return transactions


def get_block_transactions_batch(block_hashes: List[str]):
    data = [
        {
            &quot;method&quot;: &quot;getblock&quot;,
            &quot;params&quot;: [block_hash, 2]
        } for block_hash in block_hashes
    ]
    transactions = [item[&quot;result&quot;][&quot;tx&quot;] for item in make_request(data)]

    return transactions


def make_request(data):
    url = &quot;http://user:pass@127.0.0.1:38332/&quot;
    r = requests.post(url, data=json.dumps(data))
    assert r.status_code == 200
    return r.json()


def time_function(fn, *args: str, **kwargs) -&gt; float:
    &quot;&quot;&quot;Return average fn time execution and check that the last obtained blockheader hash matches last_hash_check &quot;&quot;&quot;
    iters = 5
    start = time.perf_counter()
    for i in range(iters):
        fn(*args, **kwargs)
    avg_duration = (time.perf_counter() - start) / iters
    return avg_duration


if __name__ == '__main__':
    block_hashes = get_n_blockhashes(2000)
    print(f&quot;single: {time_function(get_block_transactions_single, block_hashes):.4f}s&quot;)
    print(f&quot;batch: {time_function(get_block_transactions_batch, block_hashes):.4f}s&quot;)
</code></pre>

CC BY-SA 4.0
2022-11-22T23:01:44.950Z
Looks promising as well, but I tried to run your code starting on block 500000 (full blocks) and I ran out of RAM (64GB).
Jose
CC BY-SA 4.0
2022-11-22T23:23:38.557Z
Blocks are currently between 1-2MB in serialized form, and even larger when represented as JSON. For easy calculation, let's just assume 1MB per block. 64GB would only be able to fit ~64.000 blocks, best case scenario. You probably don't want to keep all data in memory. This is not really a blockchain query problem anymore, but a typical data engineering problem - I think other StackExchange communities will be able to help you better with that.
stickies-v
CC BY-SA 4.0
2022-11-22T23:52:49.847Z
I thought so, but I actually only tried to parse 2000 blocks using your script (from 500000 to 502000), and it was enough to fill all my RAM and freeze my PC.
Jose
CC BY-SA 4.0
2022-11-23T14:11:09.547Z
Yes, that's not surprising. As I said, 1MB is best case estimate for easy calculation/example. The tx object returned from `getblock` contains much more data than what is actually stored on chain, there is the JSON overhead, and there's also Python overhead for maintaining the dict. Your question was about querying the blockchain, my answer resolves that. You'll now need to think about your data engineering to process the data. Some very simple ideas are to analyze each tx and then discard it, or don't keep all the tx fields returned by `getblock`, or store data to disk.
stickies-v
CC BY-SA 4.0
2022-11-22T18:02:43.647Z
2022-11-22T18:02:43.647Z
stickies-v
<p>I think the fastest way to go about this, is to process all blkNNNNN.dat files sequentially, and keeping an in-memory table of transaction-hash -&gt; block-timestamp.</p>
<p>You can shrink that table by using only the first 64bits / 8 bytes of the hash as an index.
I use <a href="https://github.com/greg7mdp/parallel-hashmap" rel="nofollow noreferrer">parallel-hashmap</a> for quick in-memory indexes.</p>
<p>Then for each transaction you can lookup the block-timestamp of the input, and subtract it from the timestamp of the current block. ... and calculate your statistics from that as you wish.</p>
<p>I think processing the entire blockchain would take a couple of hours on a modern laptop, with the blockchain stored on SSD.</p>
<p>and you'd need about 64G of RAM I think.
That worked for me at least.
... I looked it up: there are currently almost 800M txns,
So you would need about 13G of data for a table with 16-byte entries.
Maybe that would even fit in 32G of RAM.</p>
<p>btw, python is nice for the high-level code, but not so much for quickly processing 500G of data. I would do this in C++.</p>
<p>btw2: I noticed that ordering blocks by timestamp yields a different order than ordering blocks by block height. And also, blocks stored in the blkNNNN.dat files in yet another order. So you may want to take that into account.</p>

CC BY-SA 4.0
2022-11-23T01:11:46.343Z
2022-11-23T10:47:41.107Z
2022-11-23T10:47:41.107Z
Willem Hengeveld
<p>I am trying to go over all transactions data from every block on the bitcoin blockchain from the previous 4 years. With almost 2k transaction per block, it will take a lot of queries per block. I have a full node running locally and I tried two ways:</p>
<p>Python with RPC: This is very slow and keeps losing connection after some time (httpx.ReadTimeout)</p>
<p>Python with os.popen commands: Doesn't have the connection problem, but still very slow.</p>
<p>Would there be any other way? Any recommendation on how to analyze bulk data from the blockchain? The methods listed above are unfeasible given the time it would take.</p>
<p>EDIT: The problem isn't memory, but the time the bitcoin node takes to answer the queries.</p>

CC BY-SA 4.0
2022-11-20T22:44:59.157Z
Does this answer your question? [How can I take a "snapshot" of the bitcoin blockchain and analyze it?](https://bitcoin.stackexchange.com/questions/114804/how-can-i-take-a-snapshot-of-the-bitcoin-blockchain-and-analyze-it)
RedGrittyBrick
CC BY-SA 4.0
2022-11-21T23:51:21.550Z
@RedGrittyBrick Thanks, that looks promising. But I need to be able to find a transaction using the TxId, how would I know which .blk file to look?
Jose
CC BY-SA 4.0
2022-11-22T11:58:41.663Z
I would either parse the index files in the `blocks` folder or parse the blk*.dat files, calculate the TxId for each encountered transaction and write my own index of file and offset to block containing transaction. I think I would find the second idea easier and faster to implement.
RedGrittyBrick
CC BY-SA 4.0
2022-11-22T21:14:55.020Z
Can you be a bit more specific what information you want to extract of those transactions?  I build my own db for indexing keys, hashes and transactions, the db amounts to approx the same size as the blockchain itself.  with most tables about 32G, and one, indexing all inputs about 64G in size.   it helps to have lots of RAM to make searching fast.
Willem Hengeveld
CC BY-SA 4.0
2022-11-22T23:03:56.077Z
For all transactions in each block I am trying to get the time between received input vs spent input. For example, if address XYZ created a transaction in block 500, I want the time between block 500 and the block when the input for the transaction was received in address XYZ.
Jose
<blockchain><json-rpc><python><raw-data>
Analyse huge amount of blockchain data
Jose
# Analyse huge amount of blockchain data

(Asked by: username_0 on 2022-11-20)

I am trying to go over all transactions data from every block on the bitcoin blockchain from the previous 4 years. With almost 2k transaction per block, it will take a lot of queries per block. I have a full node running locally and I tried two ways:

Python with RPC: This is very slow and keeps losing connection after some time (httpx.ReadTimeout)

Python with os.popen commands: Doesn't have the connection problem, but still very slow.

Would there be any other way? Any recommendation on how to analyze bulk data from the blockchain? The methods listed above are unfeasible given the time it would take.

EDIT: The problem isn't memory, but the time the bitcoin node takes to answer the queries.

**Tags:** blockchain, json-rpc, python, raw-data

> **Comments:**
> **username_1**: Does this answer your question? [How can I take a "snapshot" of the bitcoin blockchain and analyze it?](https://bitcoin.stackexchange.com/questions/114804/how-can-i-take-a-snapshot-of-the-bitcoin-blockchain-and-analyze-it)  
> **username_0**: @username_1 Thanks, that looks promising. But I need to be able to find a transaction using the TxId, how would I know which .blk file to look?  
> **username_1**: I would either parse the index files in the `blocks` folder or parse the blk*.dat files, calculate the TxId for each encountered transaction and write my own index of file and offset to block containing transaction. I think I would find the second idea easier and faster to implement.  
> **username_2**: Can you be a bit more specific what information you want to extract of those transactions?  I build my own db for indexing keys, hashes and transactions, the db amounts to approx the same size as the blockchain itself.  with most tables about 32G, and one, indexing all inputs about 64G in size.   it helps to have lots of RAM to make searching fast.  
> **username_0**: For all transactions in each block I am trying to get the time between received input vs spent input. For example, if address XYZ created a transaction in block 500, I want the time between block 500 and the block when the input for the transaction was received in address XYZ.  

---

## 2 answers

### Answer by username_3 on 2022-11-22. Score: 3

> With almost 2k transaction per block, it will take a lot of queries per block.

The [`getblock` RPC](https://developer.bitcoin.org/reference/rpc/getblock.html) supports a `verbosity=2` argument which returns all the transactions in the block as JSON objects, so you can make do with a single query per block.

With [RPC batching](https://www.jsonrpc.org/specification#batch) (i.e. sending multiple commands in a single request), you can do even better! You can query the transactions of `n` blocks with just 2 RPC requests: one to get all the block hashes, and one to get the blocks (with transactions). The below code snippet shows how you can implement both approaches, and includes a simple performance benchmark. On my machine, the batch approach is ~13x faster when querying the first 2000 blocks.

An example Python implementation, which should work out of the box if:

* `requests` is installed (`pip3 install requests`)
* `bitcoind -signet -rpcuser=user -rpcpassword=pass`

```python
import json
import time
from typing import List

import requests

def get_n_blockhashes(n: int, start_height: int = 0):
    data = [{
        "method": "getblockhash",
        "params": [height]
    } for height in range(start_height, start_height + n)]
    hashes = [item["result"] for item in make_request(data)]
    return hashes

def get_block_transactions_single(block_hashes: List[str]):
    transactions = []
    for block_hash in block_hashes:
        data = {
            "method": "getblock",
            "params": [block_hash, 2]
        }
        block_data = make_request(data)["result"]
        transactions.append(block_data["tx"])

    return transactions

def get_block_transactions_batch(block_hashes: List[str]):
    data = [
        {
            "method": "getblock",
            "params": [block_hash, 2]
        } for block_hash in block_hashes
    ]
    transactions = [item["result"]["tx"] for item in make_request(data)]

    return transactions

def make_request(data):
    url = "http://user:pass@127.0.0.1:38332/"
    r = requests.post(url, data=json.dumps(data))
    assert r.status_code == 200
    return r.json()

def time_function(fn, *args: str, **kwargs) -> float:
    """Return average fn time execution and check that the last obtained blockheader hash matches last_hash_check """
    iters = 5
    start = time.perf_counter()
    for i in range(iters):
        fn(*args, **kwargs)
    avg_duration = (time.perf_counter() - start) / iters
    return avg_duration

if __name__ == '__main__':
    block_hashes = get_n_blockhashes(2000)
    print(f"single: {time_function(get_block_transactions_single, block_hashes):.4f}s")
    print(f"batch: {time_function(get_block_transactions_batch, block_hashes):.4f}s")

```

> **Comments:**
> **username_0**: Looks promising as well, but I tried to run your code starting on block 500000 (full blocks) and I ran out of RAM (64GB).  
> **username_3**: Blocks are currently between 1-2MB in serialized form, and even larger when represented as JSON. For easy calculation, let's just assume 1MB per block. 64GB would only be able to fit ~64.000 blocks, best case scenario. You probably don't want to keep all data in memory. This is not really a blockchain query problem anymore, but a typical data engineering problem - I think other StackExchange communities will be able to help you better with that.  
> **username_0**: I thought so, but I actually only tried to parse 2000 blocks using your script (from 500000 to 502000), and it was enough to fill all my RAM and freeze my PC.  
> **username_3**: Yes, that's not surprising. As I said, 1MB is best case estimate for easy calculation/example. The tx object returned from `getblock` contains much more data than what is actually stored on chain, there is the JSON overhead, and there's also Python overhead for maintaining the dict. Your question was about querying the blockchain, my answer resolves that. You'll now need to think about your data engineering to process the data. Some very simple ideas are to analyze each tx and then discard it, or don't keep all the tx fields returned by `getblock`, or store data to disk.  

---

### Answer by username_2 on 2022-11-23. Score: 0

I think the fastest way to go about this, is to process all blkNNNNN.dat files sequentially, and keeping an in-memory table of transaction-hash -> block-timestamp.

You can shrink that table by using only the first 64bits / 8 bytes of the hash as an index.
I use [parallel-hashmap](https://github.com/greg7mdp/parallel-hashmap) for quick in-memory indexes.

Then for each transaction you can lookup the block-timestamp of the input, and subtract it from the timestamp of the current block. ... and calculate your statistics from that as you wish.

I think processing the entire blockchain would take a couple of hours on a modern laptop, with the blockchain stored on SSD.

and you'd need about 64G of RAM I think.
That worked for me at least.
... I looked it up: there are currently almost 800M txns,
So you would need about 13G of data for a table with 16-byte entries.
Maybe that would even fit in 32G of RAM.

btw, python is nice for the high-level code, but not so much for quickly processing 500G of data. I would do this in C++.

btw2: I noticed that ordering blocks by timestamp yields a different order than ordering blocks by block height. And also, blocks stored in the blkNNNN.dat files in yet another order. So you may want to take that into account.


---

