UTXO (Unspent Transaction Output) are transactions in which the outputs have not been spent, that is, sent to another address. A Bitcoin transaction is made up of one or more incoming addresses with BTC/satoshis and one or more outgoing addresses where the funds end. If these BTC have not been sent in another subsequent transaction to another address, it is called UTXO. The sum of all the UTXO exit funds add up to the total of coins there are. In Bitcoin there is a database called chainstate that contains only those transactions and that is used to avoid having to perform certain searches on the entire Blockchain. 

To save these UTXOs,software is used, it LevelDB is a key-value storage of very fast reading and writing developed by Google that provides an ordered assignment of keys and values.

Greg Walker has implemented theutility bitcoin-utxo-dump to export the UTXO and for this he relied onbitcoin tools Sergi Delgado Segura’s, we used the in3rsha tool to create a script that dumps all the UTXO is a base of PostgreSQL data. Thanks to this, we can make any query about UTXO with the facilities provided by a relational database.

I take this opportunity to launch some queries on the utxo table generated to find out how many addresses there are with more than 0.00001, 0.1, 1 and 1000 BTC. If we were interested in having this data daily, for example, to know the evolution of the number of whales (> = 1000BTC), we would only have to create a job in linux to execute the script every day.

https://github.com/BGeometrics/bitcoin-utils/blob/master/scripts/utxo2ddbb.sh

#!/bin/bash

# Script to dump bitcoin utxo database to postgres database
 
# Install go and postgreSQL
#### apt install golang postgresql
# Stop bitcoin full node because two applications cannot access LevelDB at the same time
# Access user postgres
#### sudo su - postgres

DIR_CHAINSTATE=/mnt/hgfs/Proyect/bitcoin-utxo-dump/chainstate
DIR_DUMP=/mnt/hgfs/Proyect/bitcoin-utxo-dump/
PGPASSWORD=postgres
 
# Download and compile program bitcoin-utxo-dump of ins3rsha
go get github.com/in3rsha/bitcoin-utxo-dump

# Execure dump Bitcoin utxo to file
./go/bin/bitcoin-utxo-dump -db $DIR_CHAINSTATE

# Import dump in table in database
psql -d postgres -U postgres -c "DROP INDEX IF EXISTS index_address_amount;"
psql -d postgres -U postgres -c "DROP TABLE IF EXISTS utxo;"
psql -d postgres -U postgres -c "CREATE TABLE IF NOT EXISTS utxo (id INT NOT NULL, tx_id VARCHAR(100) NOT NULL, vout INT NOT NULL, amount BIGINT NOT NULL, type VARCHAR(50) NOT NULL, address VARCHAR(100));"
psql -d postgres -U postgres -c "copy utxo(id, tx_id, vout, amount, type, address) FROM '$DIR_DUMP/utxodump.csv'  DELIMITER ',' CSV HEADER;"
psql -d postgres -U postgres -c "CREATE INDEX IF NOT EXISTS index_address_amount ON utxo (address, amount);"
psql -d postgres -U postgres -c "CREATE TABLE IF NOT EXISTS address (THE_DATE DATE NOT NULL DEFAULT CURRENT_DATE, BTC000001 INT, BTC1 INT, BTC1000 INT);"

# Selects
# Address >= 0.00001 BTC
BTC000001=$(psql -d postgres -U postgres -t -c "select count(*) from (select address, sum(amount) / 100000000 as BTC from utxo group by address having sum(amount) / 100000000 >= 0.00001) B000001")

# Address >= 1 BTC
BTC1=$(psql -d postgres -U postgres -t -c "select count(*) from (select address, sum(amount) / 100000000 as BTC from utxo group by address having sum(amount) / 100000000 >= 1) B1")

# Whales Bitcoin, address >= 1000 BTC
BTC1000=$(psql -d postgres -U postgres -t -c "select count(*) from (select address, sum(amount) / 100000000 as BTC from utxo group by address having sum(amount) / 100000000 >= 1000) B1000")
psql -d postgres -U postgres -c "INSERT INTO address(BTC000001, BTC1, BTC1000) VALUES ($BTC000001, $BTC1, $BTC1000)"

psql -d postgres -U postgres -c "select * from address"

Categories: Bitcoin, Database, Script