Parsing Ethereum Blockchain into SQL

Question

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?

Answer 1

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      |