Decode and Visualize Ethereum Data

Decode Ethereum transactions and events, and store the result in a ClickHouse database for data analysis.

Setup database schema

Use setup.sh to define tables and views for Ethereum data.

Decode Ethereum data and Store results in ClickHouse

Define environment variables, e.g.,

export ETHEREUM_URL=/data/ethereum/geth.ipc
export ETHERSCAN_APIKEY=<my-etherscan api key>
export CLICKHOUSE_URL=http://localhost:8123
export CLICKHOUSE_DB=ethdb
export CLICKHOUSE_USER=default
export CLICKHOUSE_PASSWORD=
export GLOG_logtostderr=false

Start decoder process:

nohup ./cmd -log_dir /data/log/default -command default 2>&1 > /data/log/nohup.out &
nohup ./cmd -log_dir /data/log/rejectTx -command rejectTx 2>&1 > /data/log/nohup2.out &

Sample ClickHouse Query

ERC20 token transfer transactions with known symbols

SELECT 
    Hash, From, To, 
    arrayElement(Params.ValueString, 1) as Recipient, 
    divide(arrayElement(Params.ValueDouble, 2), exp10(Decimals)) as Amount, 
    BlockTime, Symbol, Decimals 
FROM ethdb.transactions t 
INNER JOIN ethdb.contracts c 
ON t.Method = 'transfer' AND c.Symbol != '' AND t.To = c.Address

Top daily transfers of ERC20 tokens

SELECT 
    count() as Count, 
    divide(sum(arrayElement(Params.ValueDouble, 2), exp10(Decimals))) as Amount, 
    Symbol, toDate(BlockTime) as Date 
FROM ethdb.transactions t 
INNER JOIN ethdb.contracts c 
ON t.Method = 'transfer' AND c.Symbol != '' AND t.To = c.Address 
GROUP BY Symbol, Date 
ORDER BY Count DESC

Visualize data in Spotfire

Spotfire Analyst may connect to a ClickHouse database directly via an ODBC driver, or via a TIBCO Cloud Spotfire server that connects to a ClickHouse data source configured in a TIBCO Data Virtualization server by using either the Native JDBC Driver or the Official JDBC Driver.

A sample Ethereum dashboard implemented in Spotfire Analyst is described in this blog.

Visualize data in Redash

Start a Redash server instance as described here.

Follow Getting Started to login and create query and dashboard in a web browser.

Visualize data in JupyterLab

If you are a data analyst with Python knowledge, you can analyze and visualize Ethereum data in JupyterLab.

Install Python 3, e.g., using pyenv on MacOS:

brew install pyenv
pyenv install -l
pyenv install 3.10.1
echo -e 'if command -v pyenv 1>/dev/null 2>&1; then\n  eval "$(pyenv init --path)"\nfi' >> ~/.zshrc
. ~/.zshrc

Install JupyterLab according to the instruction, e.g.,

pip install jupyterlab

Install other dependencies, e.g.,

pip install clickhouse-driver
pip install plotly
pip install pandas
pip install kaleido

jupyter labextension install @jupyter-widgets/jupyterlab-manager @jupyterlab/geojson-extension
jupyter labextension install jupyterlab-plotly

Start JupyterLab:

jupyter lab

Open the sample notebook plotly-charts.ipynb, which describes steps to query the ClickHouse database and visualize results in plotly charts.

Todo

• Collect ERC token balance of every EOA (Externally-Owned Account), e.g., by calling balanceOf(eoa) on the contract in each ERC-20/ERC-721 transfer transaction, or balanceOfBatch() for ERC-1155 batch-transfer transactions.
• Collect ETH balance of every EOA by looking at state trie change after every block?

Alternatives

Google BigQuery is an alternative source of blockchain data. The open source projects in Blockchain ETL have been used to extract blockchain data into BigQuery tables, e.g., Ethereum transactions, or Bitcoin transactions.

These BigQuery tables are useful for analyzing blockchains that do not support dynamic data of smart contracts, e.g., Bitcoin or its variants, DogeCoin or LiteCoin etc. For Ethereum, however, the BigQuery public dataset stores only encoded data of smart contract methods and events, and thus they cannot be analyzed until the data is decoded.

Some data of commonly interesting Ethereum smart contracts are decoded and stored in tables under the BigQuery public project blockchain-etl, e.g., ENS Transfer Events. The blockchain-etl project also contains data of other blockchains, such as Solana and Avalanche, etc. The blog for ENS describes how blockchain data are extraced and decoded and then stored in BigQuery tables.

Unlike our approach of this project that uses a single ClickHouse table to store decoded data of all Ethereum contracts whose source code is verified in etherscan, the BigQuery approach must decode each contract individually, and store each event/method type in a separate BigQuery table.