Google Cloud BigQuery & simple Ethereum Blockchain Analytic

Have been interested to find out more about Google Cloud Big Query. I tried it by adding the Ethereum public data set.

The very first impression on the Google Big Query UI: Messy…

As a new BigQuery user however with 10 years experience in various data system. I really have some issues finding out where to start initially. The UI design of Google Cloud Platform really has a lot of rooms for improvement.

In term of query analytic capability, Google Cloud BigQuery helps to reduce the learning curve of those data analyst who is moving from traditional RDBMS SQL to Big Data analytic. Google Cloud Big Query has a highly similar query syntax as SQL.

Below I just tried to do some basic queries on the Ethereum Blockchain Data.

Lets do a quick count of number of address (i.e. smart contracts) in the Ethereum blockchain:

SELECT count(address) FROM `bigquery-public-data.crypto_ethereum.contracts`

How many of the Ethereum smart contracts are in ERC20 standard?

SELECT is_erc20, count(address) FROM `bigquery-public-data.crypto_ethereum.contracts`
group by is_erc20;

How many of the Ethereum smart contracts are in ERC721 standard?

SELECT is_erc721, count(address) FROM `bigquery-public-data.crypto_ethereum.contracts`
group by is_erc721;

The above query on Ethereum Contract tables run and return result within 3 seconds.

Lets do a query on top 10 smart contracts with the highest block_number:

SELECT
name,
symbol,
total_supply,
block_number
FROM `bigquery-public-data.crypto_ethereum.tokens`
order by block_number desc
limit 10;

Top 10 address with the highest balance of ETH:

SELECT address, eth_balance
FROM bigquery-public-data.crypto_ethereum.balances
order by eth_balance desc
limit 10;

As you uses the Google Cloud BigQuery, you will realized it is very handy, especially those who has no experience in Big Data map reduce, spark etc. This is a good tool for purely insight analytic purpose.

Just a few more notes on the Google Cloud UI:

1. Google Cloud UI is not user-friendly, always I don’t know where to start.
2. As multiple working tabs open up, you will realize that your cursor can’t move the scroll bar within the tab

3. Some of your right click command window will be blocked by other tab unless you adjust it