Introducing ether_sql

ether_sql is a Python library to push Ethereum blockchain data into a SQL database.

analyseether/ether_sql

The current Ethereum blockchain has more than 5.6 million blocks and 233 million transactions. Performing data-analysis on this data-set will provide useful insights on decentralized exchanges, tokens, ENS, prediction markets etc. But, unfortunately tools/libraries to easily perform data-analysis of Ethereum do not exist yet.

We at Analyse Ether wish to create these open-source libraries, ether_sql is the first of many with the goal of making Ethereum data easily queryable. This library can be used as a backbone for creating:

1. Block explorers (coming soon …)
2. Data analysis platforms

Buidl Status

The library is currently in alpha stage, and is not recommended for production use until it has received sufficient testing. ether_sql currently supports Geth, Infura and Parity nodes, but transaction traces (eg. internal transactions) are currently available only with a Parity node.

To install the library locally refer to the documentation below:

Installation Guide - ether_sql 0.0.1 documentation

Pushing data in sql

Once installed you can simply start pushing the data from any Ethereum node to SQL using the CLI option:

$ ether_sql scrape_block_range

This command will check for the highest available block in sql database and start the pushing data after that block number.

Querying the sql

After the database is filled with some blocks you can connect to it using the following command:

$ psql ether_sql

Once connected you can start querying the database, find below some examples.

1. A SQL query to get the block number of the first transaction.

ether_sql=# SELECT min(block_number) from blocks where transaction_count>0;
  min
  -------
  46147
  (1 row)

2. And here is a query to get the maximum value transfer in a transaction in first 100k blocks.

ether_sql=# SELECT max(value) from transactions where block_number <100001;
  max
  ----------------------------
  11901464239480000000000000
  (1 row)

3. Someone transferred 11.9 million ether! Let us check the hash of the transaction:

ether_sql=# SELECT transaction_hash from transactions where value = 11901464239480000000000000;
  transaction_hash
------------------------------------------------------------------ 0x9c81f44c29ff0226f835cd0a8a2f2a7eca6db52a711f8211b566fd15d3e0e8d4
  (1 row)

Indeed this transaction made a value transfer of 11.9 million ether.

To know more details about available tables and their columns refer to this link, you can check out more SQL examples here.

We can see that by pushing the Ethereum data into a query engine, even simple queries can give access to information not easily available through traditional block explorers. Our upcoming libraries will facilitate the creation of visualizations, writing machine-learning models on top of data, and bring in more transparency in the inner workings of several smart-contracts.

We believe this is a missing piece of infrastructure in the space and Analyse Ether wishes to fill this gap.

Come let us Analyse Ether together :)