2

We're looking to do an airdrop to all Ethereum wallets with a balance of .1 ETH or greater, but are having a hard time capturing the balances of every wallet in a quick fashion. We're trying to use the JavaScript API to calculate balances but it's going very slow (will take many years at the current rate).

We have already downloaded the entire blockchain onto our local environment, and have extracted the list of addresses (27 million).

We are now thinking the best way to move quickly is to parse the blockchain files and save it into MS SQL so we can conduct rapid balance calculations.

Have anyone conducted an airdrop like this, or parsed the blockchain -- and have any advice?

Josh Henry
  • 21
  • 2
  • I've only had a quick play around with it in the past, but Quickblocks might be worth a look at (e.g. https://www.youtube.com/watch?v=c_KNulh3PF4&t=12s), especially if you've already got the chain data lying about. – Richard Horrocks Apr 09 '18 at 19:53
  • @RichardHorrocks Thanks for the tip! That video seems like it only works for ERC20 tokens, but not for the Ethereum blockchain itself. Do you have anything else that might work for the Ethereum blockchain itself? – Josh Henry Apr 09 '18 at 20:27
  • It should work for any data stored in the chain :-) Investing the time now to learn how to use the tool will save you more time in the long run. The alternatives are querying via web3/RPC, which will take forever, or playing around with parsing .ldb files, which you'd need to learn how to do. (e.g. https://ethereum.stackexchange.com/questions/2184/exploring-and-analyzing-the-ethereum-blockchain, https://ethereum.stackexchange.com/questions/1635/how-to-parse-blocks-with-python?rq=1, https://ethereum.stackexchange.com/questions/25620/how-to-access-geths-state-trie?noredirect=1&lq=1) – Richard Horrocks Apr 09 '18 at 20:31

1 Answers1

2

Here is a guide on how to export Ethereum data to csv and analyze it with Amazon Athena https://medium.com/@medvedev1088/exporting-and-analyzing-ethereum-blockchain-f5353414a94e

It uses https://github.com/medvedev1088/ethereum-etl which outputs the data into blocks.csv, transactions.csv, erc20_transfers.csv.

blocks.csv

Column                  | Type               |
------------------------|---------------------
block_number            | bigint             |
block_hash              | hex_string         |
block_parent_hash       | hex_string         |
block_nonce             | hex_string         |
block_sha3_uncles       | hex_string         |
block_logs_bloom        | hex_string         |
block_transactions_root | hex_string         |
block_state_root        | hex_string         |
block_miner             | hex_string         |
block_difficulty        | bigint             |
block_total_difficulty  | bigint             |
block_size              | bigint             |
block_extra_data        | hex_string         |
block_gas_limit         | bigint             |
block_gas_used          | bigint             |
block_timestamp         | bigint             |
block_transaction_count | bigint             |

transactions.csv

Column              |    Type     |
--------------------|--------------
tx_hash             | hex_string  |
tx_nonce            | bigint      |
tx_block_hash       | hex_string  |
tx_block_number     | bigint      |
tx_index            | bigint      |
tx_from             | hex_string  |
tx_to               | hex_string  |
tx_value            | bigint      |
tx_gas              | bigint      |
tx_gas_price        | bigint      |
tx_input            | hex_string  |

erc20_transfers.csv

Column              |    Type     |
--------------------|--------------
erc20_token         | hex_string  |
erc20_from          | hex_string  |
erc20_to            | hex_string  |
erc20_value         | bigint      |
erc20_tx_hash       | hex_string  |
erc20_block_number  | bigint      |
medvedev1088
  • 10,996
  • 5
  • 35
  • 63