<p>Google BigQuery already maintains a public dataset for Bitcoin, with tables optimized for querying transactions and blocks.</p>
<p>You can find the dataset here: <a href="https://console.cloud.google.com/marketplace/product/bitcoin/crypto-bitcoin" rel="noreferrer">https://console.cloud.google.com/marketplace/product/bitcoin/crypto-bitcoin</a></p>
<p>and I've also written a guide on connecting to this dataset with Python/R here: <a href="https://github.com/josibake/bitcoin-data-analysis/" rel="noreferrer">https://github.com/josibake/bitcoin-data-analysis/</a></p>
<p>If you want to use your local copy of the blockchain, there are block explorers you can run alongside your node, such as <a href="https://bitcoin.stackexchange.com/questions/91746/configure-esplora-to-point-to-the-bitcoind-servers-ip-address">Esplora</a>.</p>
<p>I suspect, however, that a block explorer will be more cumbersome if you try to do analytical queries which span multiple blocks and that a SQL database is better suited.</p>
<p>As to whether or not Bitcoin was designed for maximally efficient querying, I'd argue no. Bitcoin's data structures are designed to be as small as possible on disk, which often makes querying more difficult.</p>
<p>Disclaimer: I am the author of one of the linked resources.</p>

CC BY-SA 4.0
2023-02-13T17:56:55.357Z
this is great thanks so much - had no idea BigQuery already maintained a public dataset!
d3wannabe
CC BY-SA 4.0
2024-01-31T07:25:31.717Z
Google BigQuery is good , however I am looking for a local db as we need all the data so bigquery would be too costly. Any new suggestions on tools
Mohit Sethi
CC BY-SA 4.0
2023-02-13T16:23:09.230Z
2023-02-13T16:23:09.230Z
josie
<p>I have a full node, and have written some limited python programs with bitcoinrpc.  I want to start doing slightly more ambitious things now, e.g. visualisations across the entire blockchain (possibly analysing all transactions) and need to slice and dice the data in ways that make me instinctively want to have it queryable in a db (e.g. bigquery).  Then again there's already over 800m transactions and so the performance of querying a table of transactions might not be any better than querying via bitcoinrpc.</p>
<p>I suppose my question is whether anyone who's made similar decisions in the past has any guiding principles for thinking about when it makes sense to dump large parts of the blockchain into your own db instead of query it directly via your node?  E.g. I'm assuming the bitcoin api services (e.g. blockcypher) aren't just running a bunch of full nodes and querying them directly when new requests come in but instead have structured the data in a way that makes that much more efficient?  A related question I have is whether Bitcoin was even designed with maximally-efficient-blockchain-querying in mind?</p>

CC BY-SA 4.0
2023-02-13T15:57:42.387Z
Have you considered using a blockexplorer backend like Esplora? They may have done a lot of the heavy lifting for what you want to do already.
Murch
CC BY-SA 4.0
2023-02-13T16:28:34.077Z
thanks for the suggestion @Murch - not familiar with Esplora but I'm guessing it must be one of those API models where I'm going to end up rate limited or paying for the privilege.  Also part of me wants to do this for the fun and learning opportunity!
d3wannabe
CC BY-SA 4.0
2023-02-13T16:32:50.597Z
Okay, just figured that I’d point it out as an option. Esplora is open source software that you can run on your own computer: https://github.com/Blockstream/esplora
Murch
<bitcoin-core><transactions><database>
Efficiency of storing/querying transactions in a relational database
d3wannabe
# Efficiency of storing/querying transactions in a relational database

(Asked by: username_0 on 2023-02-12)

I have a full node, and have written some limited python programs with bitcoinrpc. I want to start doing slightly more ambitious things now, e.g. visualisations across the entire blockchain (possibly analysing all transactions) and need to slice and dice the data in ways that make me instinctively want to have it queryable in a db (e.g. bigquery). Then again there's already over 800m transactions and so the performance of querying a table of transactions might not be any better than querying via bitcoinrpc.

I suppose my question is whether anyone who's made similar decisions in the past has any guiding principles for thinking about when it makes sense to dump large parts of the blockchain into your own db instead of query it directly via your node? E.g. I'm assuming the bitcoin api services (e.g. blockcypher) aren't just running a bunch of full nodes and querying them directly when new requests come in but instead have structured the data in a way that makes that much more efficient? A related question I have is whether Bitcoin was even designed with maximally-efficient-blockchain-querying in mind?

**Tags:** bitcoin-core, transactions, database

> **Comments:**
> **username_1**: Have you considered using a blockexplorer backend like Esplora? They may have done a lot of the heavy lifting for what you want to do already.  
> **username_0**: thanks for the suggestion @username_1 - not familiar with Esplora but I'm guessing it must be one of those API models where I'm going to end up rate limited or paying for the privilege.  Also part of me wants to do this for the fun and learning opportunity!  
> **username_1**: Okay, just figured that I’d point it out as an option. Esplora is open source software that you can run on your own computer: https://github.com/Blockstream/esplora  

---

## 1 answers

### Answer by username_2 on 2023-02-13. Score: 6 | Accepted answer

Google BigQuery already maintains a public dataset for Bitcoin, with tables optimized for querying transactions and blocks.

You can find the dataset here: <https://console.cloud.google.com/marketplace/product/bitcoin/crypto-bitcoin>

and I've also written a guide on connecting to this dataset with Python/R here: <https://github.com/josibake/bitcoin-data-analysis/>

If you want to use your local copy of the blockchain, there are block explorers you can run alongside your node, such as [Esplora](https://bitcoin.stackexchange.com/questions/91746/configure-esplora-to-point-to-the-bitcoind-servers-ip-address).

I suspect, however, that a block explorer will be more cumbersome if you try to do analytical queries which span multiple blocks and that a SQL database is better suited.

As to whether or not Bitcoin was designed for maximally efficient querying, I'd argue no. Bitcoin's data structures are designed to be as small as possible on disk, which often makes querying more difficult.

Disclaimer: I am the author of one of the linked resources.

> **Comments:**
> **username_0**: this is great thanks so much - had no idea BigQuery already maintained a public dataset!  
> **username_3**: Google BigQuery is good , however I am looking for a local db as we need all the data so bigquery would be too costly. Any new suggestions on tools  

---

