19

I need to generate a SQL database (preferably SQLite3) of every transaction that has occurred in the blockchain. Is there an efficient way to do this using open-source software? I'm attempting to do it myself using a Haskell script that makes system calls to bitcoind but I'm having trouble making it sufficiently fast.

2
  • Does it have to be SQL or can you use LevelDB? Jun 18, 2013 at 2:07
  • I know that bitcoind (if you changed the settings, which I have) creates a LevelDB database for its own use. However, there is very little documentation on LevelDB in general, and even less in my language of choice (Haskell). I want something that I can easily use in a clearly correct way in code that analyzes the data, and SQL seems like the only option for that.
    – Mike
    Jun 18, 2013 at 15:40

4 Answers 4

7

I created a fork of blockparser with a 'csvdump' command that will generate a details dump of the blockchain in CSV format. There are also some support scripts in that repository for creation, population and periodic update of a PostgreSQL database.

Be aware that the resultant database is pretty large; you might want to consider PostgreSQL over SQLite for this one.

11
  • I remember blockparser being slow as sin for single transaction and account data when I first tried it, but I'll give the CSV dump a shot and see how long it takes. Do you remember how long it took for you? If so, what were the specs of the machine you ran it on?
    – Mike
    Jun 18, 2013 at 15:44
  • blockparser is relatively CPU and memory intensive. I just carried out a full csvdump on my machine and it took 9 mins, maxing out at just under 9GB memory used. It will definitely help if you have a decent CPU and SSDs for storage.
    – jgm
    Jun 18, 2013 at 16:38
  • 1
    I'm currently running the SQL dump. It's much faster than Bitcoin-Abe; it looks like it's only going to take about five hours. However, I wonder whether it parses the transaction data or just leaves it in raw hex.
    – Mike
    Jun 18, 2013 at 18:01
  • Depends what you mean by 'data'. Information is supplied on individual transaction inputs and outputs, but there is no work carried out to parse the signatures.
    – jgm
    Jun 18, 2013 at 18:03
  • This works great, but I have one qualm: why don't the transactions have timestamps?
    – Mike
    Jun 21, 2013 at 23:43
3

Possibly checkout ABE - Alternative Block Parser. It uses python and a SQL-based database but you can hook up other databases based on the driver.

https://github.com/jtobey/bitcoin-abe

3
  • This is a legitimate option. However, it's getting to be unrealistically slow as the blockchain grows. With SQLite3, it now takes days, from what I can tell.
    – Mike
    Jun 18, 2013 at 17:59
  • Well start it, then have a client that keeps it up to date. Yes, it'll grow, as you're asking for a database that will encompass the blockchain. There's no immediate solution to that unless you want a database that has a reference to points in the chain. Did you want something "fast"? Use LevelDB. Jun 18, 2013 at 21:03
  • 2
    Or mcdee's blockparser.
    – Mike
    Jun 27, 2013 at 22:39
3

You can always use BlockchainSQL Server for SQL querying the Bitcoin blockchain.

The web version is free, the server is commercial.

Disclaimer: I am the developer.

1
  • Website seems down
    – Shayan
    Dec 21, 2017 at 6:15
1

To transfer to a SQL Server database you can try BitcoinDatabaseGenerator.
You can download it from https://github.com/ladimolnar/BitcoinDatabaseGenerator/releases
Sources and wiki: https://github.com/ladimolnar/BitcoinDatabaseGenerator

The transfer is very fast. At the time of writing this, the transfer of 266 blockchain files totaling 33 GB took less than one hour and 30 minutes on my development machine. Of course, the time will vary depending on hardware and configuration.

2
  • 1
    is it working under linux?
    – jangorecki
    Jun 8, 2015 at 9:53
  • 1
    BitcoinDatabaseGenerator is Windows only.
    – Ladi
    Jun 9, 2015 at 21:05

Your Answer

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

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