LoyceV's small Linux commands for handling big data

[LoyceV]

I do not really understand what do you mean as a "database". Do you think about any particular implementation? What do you mean by ".db format"?

That confirms that I know nothing about databases

Why not to launch mysql or maybe better postgresql server? Loading file like that to database table is trivial.
RAM has nothing to do with that I think. I mean - it helps, but is not a blocking constraint.

I've heard of mysql, but not PostgreSQL.

"Trivial" sounds great But I have no idea how Google shows this, if that's the right track I can try it.
Any idea how to handle duplicate addresses: 1 address with 2 balances that have to be added together?

[PawGo]

Mysql is also good, they give https://www.mysql.com/products/workbench/
I will not force you to install MS SQLServer or any monster from Oracle.
Let's say you decide to use postgresql. Then you receive a very nice client - pgAdmin https://www.pgadmin.org/  Using tool like that will be very helpful for you.

Then you may for example: create table (tx id, address, balanceChange),
txid could be our primary key (unique), you should also create index on recipient, as you will launch search using that field.
load data:
 https://sunitc.dev/2021/07/17/load-csv-data-into-a-database-table-mysql-postgres-etc/
(create index after you load data, otherwise loading will take ages)

Then you may very easily check balance change (delta) for each recipient:

Code:

select address, sum(balance) from tableName group by address

It should give you list of addresses with their balance changes.

Just try to list all the possible use cases, think what do you need, how you want to use it - to build a correct data model. It may be the most difficult task - just not to duplicate data, etc
https://www.guru99.com/relational-data-model-dbms.html
https://en.wikipedia.org/wiki/Database_normalization

But if you start, sky is the limit it will be much easier than playing with text files.

[LoyceV]

Thanks! I only now realize I can just add all balances for each address, and sum them later when needed.

The drawback of such a versatile database is that I have a lot of catching up to do. Thanks for the links, I'll see if I can get something working tomorrow

Update: I don't need a database anymore, I'll stick to what I know: clear text

[DeepComplex]

 I also prefer the clear tex model.

[LoyceV]

Question

I have a file (300 MB) with 8.9 million Bitcoin addresses. I also have a directory (67 GB) with all Bitcoin addresses. I want to know which address from the file is in the direcory more than once.

I use this:

Code:

grep -hf addresses.txt alladdys/* | sort | uniq -d > output.txt

For small lists of addresses, this works fine! However, with the 300 MB list, grep takes 94% of my 16 GB RAM, and there doesn't seem to be any progress. I didn't expect grep would use this much memory for a 300 MB file.
What would be a better solution?

[seoincorporation]

...
Main question: how do I put this in .db format?

You don't have to put it in a DB format at all because you can import text files to a data base. The tric is to use tabs and not space betweet the address and the balance.

Code:

echo "hello word" | sed -e 's/ /\t/g'

Once you have changed that, then you can load it in to a table with:

Code:

LOAD DATA INFILE '/tmp/addys.txt' INTO TABLE AddresTable;

Source: https://stackoverflow.com/questions/13579810/how-to-import-data-from-text-file-to-mysql-database

[PawGo]

Question

I have a file (300 MB) with 8.9 million Bitcoin addresses. I also have a directory (67 GB) with all Bitcoin addresses. I want to know which address from the file is in the direcory more than once.

Hi,
I do not understand what do you mean by "directory" - is it a file with addresses? Or directory on hdd where each file has name like an address? Then how you may have the same address twice?
I have prepared a small program for you:
https://github.com/PawelGorny/Loyce60787783
It reads into memory list of addresses and then reads "directory" file with addresses - if address exists in memory, is marked, if the same address in hit for the second time, is removed from memory and saved to file. If you want to calculate how many times address was hit, the change is needed.

[LoyceV]

I do not understand what do you mean by "directory" - is it a file with addresses? Or directory on hdd where each file has name like an address? Then how you may have the same address twice?

It's a directory with files. Each file has all Bitcoin addresses that were used that day, some of them more than once.

To my surprise, my grep script actually completed! It used up all RAM and added some swap, and after 24 hours of high load, it's done

I have prepared a small program for you:
https://github.com/PawelGorny/Loyce60787783
It reads into memory list of addresses and then reads "directory" file with addresses - if address exists in memory, is marked, if the same address in hit for the second time, is removed from memory and saved to file.

Thanks for this! I tried to test it, but I don't really want to install java on the server just for this. I am curious how this would perform though.

[PawGo]

I do not understand what do you mean by "directory" - is it a file with addresses? Or directory on hdd where each file has name like an address? Then how you may have the same address twice?

It's a directory with files. Each file has all Bitcoin addresses that were used that day, some of them more than once.

Ok, I understand now. That way I may change program to process all the files from the given directory, not only the one file (daily snapshot). The question would be if you look for double hits per day/per file, or totally in any of files.

If you change your mind, give it a try, maybe it will use less resources. I do not know how much memory will take 8.7mln addresses.

[LoyceV]

The question would be if you look for double hits per day/per file, or totally in any of files.

The total For now, I got it covered.
I can try something else too: if I use the list of addresses that are used more than once, it's only 3 GB (instead of 67), and I can search against that list. That was slower in my initial test, but that didn't cause memory problems, and I have to do it less often so it may pay off.

I'm looking for all addresses funded with 1, 2, 4, ...., 8196 mBTC in one transaction, that don't have more transactions than 1 funding and (possibly) one spending. I want to count how many of those chips exist on each day. It could be a good measure for privacy.

[citb0in]

Hello all and thanks to LoyceV providing this great ressource of information. For a certain query I'd like to have a file containing all addresses which

either

are funded (=positive balance)

or

had an output in the past (=sent some coins to someone)

Is it possible somehow to generate such a big file with this data which I could use for a query? Alternatively, I don't mind having two separate files: one that already exists <blockchair_bitcoin_addresses_and_balance_LATEST.tsv> and one additional which contains all addresses with outputs. I could run my query agains both of those, that would certainly do the job.

I'm grateful for any helpful information. Thank you so much!

[LoyceV]

are funded (=positive balance)

See List of all Bitcoin addresses with a balance.

had an output in the past (=sent some coins to someone)

Interesting, I don't have such a list, but it should be quite easy to get from outputs.

Is it possible somehow to generate such a big file with this data which I could use for a query?

Are you sure you're not looking for "and" instead of "or", so all addresses that sent funds before, and still hold a balance?

I could run my query agains both of those, that would certainly do the job.

Adding them together and removing duplicates is easy.

To be clear:

outputs:

Code:

block_id	transaction_hash	index	time	value	value_usd	recipient	type	script_hex	is_from_coinbase	is_spendable
152764	498ec7f88857d23bd19370252da439876960dad296a64534987ad54081a9cc39	0	2011-11-11 00:05:55	5001400010	143.5402	17aA19GvhzMHsq8xPwSXAPZutyr6kuzLEB	pubkey	4104a8dd3f118a6122c2f8c6be3261670f7af76568cac8ff0ed95e5ef63238018e69b223dbdef9f9d5e94a053ff1afc390e230844c0b71f3648405807cd668979958ac	1	1
152764	5cc203b9389f0f7eb50669eba04ac32d666892fc95f07c25603da8a6ed9316ae	0	2011-11-11 00:05:55	1199480	0.0344	1KPxwAbFVoDimPrVECF2zgiyfX9jGW9TCy	pubkeyhash	76a914c9ca1b452087cdc6b89754c1090928d7a67ef23988ac	0	-1

Would 17aA19GvhzMHsq8xPwSXAPZutyr6kuzLEB and 1KPxwAbFVoDimPrVECF2zgiyfX9jGW9TCy be what you're looking for?

[citb0in]

Not really. I am interested in addresses like that:

address, balance, outputs

1aDdressExampLeFundedxXxx, 123456, 789
bc1qnotfundedbutspent0utput, 0, 3

Addresses with balance=0 AND outputs=0 should not be listed. Only those matching this condition

if balance>0 OR (balance=0 AND outputs>0)

[LoyceV]

Addresses with balance=0 AND outputs=0 should not be listed.

No balance and no outputs, that means the address is unused. Those aren't in any of the data dumps.

Only those matching this condition
if balance>0

That list I have

OR (balance=0 AND outputs>0)

I'm confused: why would the 2 addresses I gave above not qualify for this?

[seoincorporation]

Hello LoyceV, i have been working in the Address to HASH160 conversion and i made some scripts that i would like to add to your Linux Commands.

Script to get all the HASH160 from the  addyBalance.tsv file from address starting with 1.

Code:

for a in $(cat addyBalance.tsv | cut -f1 | sed '/^b/d' | sed '/^1/d')
do
python3 -c "import binascii, hashlib, base58; hash160 = binascii.hexlify(base58.b58decode_check(b'$a')).decode()[2:]; print(hash160)";
done

Script to get all the HASH160 from the  addyBalance.tsv file from address starting with bc1.

Code:

for a in $(cat addyBalance.tsv | cut -f1 | sed '/.\{70\}/d' | sed '/^3/d' | sed '/^1/d')
do
python3 -c "import bech32; hash1 = bech32.decode(\"bc\", \"$a\"); hash2 = bytes(hash1[1]); print(hash2.hex())";
done

Script to get all the HASH160 from the  addyBalance.tsv file from address starting with 1.

Run:

You can print the HASH with:

Code:

sh addy.sh

Or oyu can send it to a file:

Code:

sh addy.sh > a.txt

The script prints an error because the first word in the file is 'Addres', but it works fine:

Code:

$ sh addy.sh
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/home/windows/.local/lib/python3.10/site-packages/base58/__init__.py", line 157, in b58decode_check
    raise ValueError("Invalid checksum")
ValueError: Invalid checksum
23e522dfc6656a8fda3d47b4fa53f7585ac758cd
cec49f4d16b05fe163e41b15856732d985d36277
d401a46b6d847399a45878b1f25f499aad959830
4017a4219d12f11e4649d2ae9eef5bd4c9bf0d80
c8ca150ee82589d47f69b8dcd7cad684d88283f1
288b23e4a5886136a544159550a8e99f2e5672ab
cd49d5f5215aaaa0fdbf1bd2e454250edf8a54e2
cafacdc40cf8d3daa60aa479774ccd9085b4c512
b91e28f4f8f6fced313112c0c72407d85ecec39a
4a782fe173a0b6718d39667b420d9c8b07e94262
9518af9ff9c31a47f94a36b95dce267e5edcd82d

And I made a small script for single address too:

sh bc.sh bc1qBitcoinAddress

Code:

python3 -c "import bech32; hash1 = bech32.decode(\"bc\", \"$1\"); hash2 = bytes(hash1[1]); print(hash2.hex())"

sh 1.sh 1BitcoinAddress

Code:

python3 -c "import binascii, hashlib, base58; hash160 = binascii.hexlify(base58.b58decode_check(b'$1')).decode()[2:]; print(hash160)"

You will need the Python dependencies to run this script.

Code:

pip install base58 bech32 binascii hashlib

[citb0in]

@seoincorporation: thanks for the scripts you provided, but: this should be very time-consuming and slow. Imagine you would run your script against the file of LoyceV which contains all funded addresses (1.8 GB file size). It would take weeks (?) until it gets finished ? what do you think, any ways for optimization ?

[seoincorporation]

@seoincorporation: thanks for the scripts you provided, but: this should be very time-consuming and slow. Imagine you would run your script against the file of LoyceV which contains all funded addresses (1.8 GB file size). It would take weeks (?) until it gets finished ? what do you think, any ways for optimization ?

Code:

$ cat addyBalance.tsv | cut -f1 | sed '/.\{70\}/d' | sed '/^3/d' | sed '/^1/d' |wc -l
11407170

I know the data is big 1.1 Million addys starting with 1, but i don't think it would take weeks.

I replace cat with head -n 10000, and with the time command i get:

Code:

real	0m37.877s
user	0m31.956s
sys	0m5.951s

So, 10,000 on 40 seconds, that's 4,000 seconds for 1 million, that's 66 minutes, or a little more than 1 hour.

I think it should be faster if you do it all with Python and not calling python from Bash as i did.