Ethereum Logo with characters standing around.
Original drawing by Viktor Hachmang

Full relational diagram for Ethereum public data on Google BigQuery

https://dbdiagram.io/d/602e721380d742080a3b1265

Find a full relational diagram for Ethereum data coming from an amazing opensource project Blockchain-ETL.

Usage Examples

1. crypto_ethereum.transactions

The transactions table has the normal Ethereum transactions, and if you need to find the internal transactions, you need to query the trace table.

Internal transactions are not actually considered transactions, as they are not included directly in the blockchain. Instead can only be seen as a byproduct of having tracing on.

2. crypto_ethereum.contracts

Note — Don’t have any unique key on contracts table

Contracts & Transactions

contracts.address can be fond transactions.to_address or transactions.receipt_contract_address.

transactions.to_address - Address of the receiver. null when its a contract creation transaction.transactions.receipt_contract_address - The contract address created, if the transaction was a contract creation, otherwise null.

Joining Contracts & Transactions

You must use a composite foreign key when joining contracts & transactions.

"crypto_ethereum.transactions".("block_hash", "to_address") < "bigquery-public-data:crypto_ethereum.contracts".("block_hash", "address")OR"bigquery-public-data:crypto_ethereum.transactions".("block_hash", "receipt_contract_address") < "bigquery-public-data:crypto_ethereum.contracts".("block_hash", "address")

Contracts & Trace

Trace.from_address and Trace.to_address can both have contract.address

3. crypto_ethereum.traces

Learn more about tracing in Ethereum https://openethereum.github.io/JSONRPC-trace-module, the JSONRPC-trace-module is used to populates table crypto_ethereum.traces.

Internal transactions are not actually considered transactions, as they are not included directly in the blockchain. Instead can only be seen as a byproduct of having tracing on

Etherscan.io Internal Transactions Simplified and Advanced

If you use Etherscan to look up internal transactions, be aware of a Toggle between Simple and Advanced view

Toggle Off
Toggle On

Internal Transactions Simple View

How to query the trace table for internal transactions (default/simple view from Etherscan.io ). The total rows returned will match the Etherscan information screen regarding blocks.

125 internal transactions ( actually, there are more, these are ones that have a value > 0 )

Internal Transactions Advanced View

How to query the trace table for internal transactions (advanced view from Etherscan.io)

3. crypto_ethereum.amended_tokens

Token amended with data from CSV.

Deduplicate first since the tokens table might have duplicate entries due to CREATE2 https://medium.com/@jason.carver/defend-against-wild-magic-in-the-next-ethereum-upgrade-b008247839d2

##Query for View amended_tokensWITH tokens AS (
-- Deduplicate first since the tokens table might have duplicate entries due to CREATE2 https://medium.com/@jason.carver/defend-against-wild-magic-in-the-next-ethereum-upgrade-b008247839d2
SELECT
address,
ANY_VALUE(symbol) AS symbol,
ANY_VALUE(name) AS name,
ANY_VALUE(decimals) AS decimals,
FROM `bigquery-public-data.crypto_ethereum.tokens`
GROUP BY address
)
SELECT
address,
COALESCE(am.symbol, tokens.symbol) AS symbol,
COALESCE(am.name, tokens.name) AS name,
COALESCE(am.decimals, tokens.decimals) AS decimals,
FROM
`blockchain-etl-internal.common.token_amendments` AS am
FULL OUTER JOIN
tokens
USING(address)

4. crypto_ethereum.tokens

List of tokens

5. crypto_ethereum.token_transfers

The most popular type of transaction on the Ethereum blockchain invokes a contract of type ERC20 to perform a transfer operation, moving some number of tokens from one 20-byte address to another 20-byte address. This table contains the subset of those transactions and has further processed and denormalized the data to make it easier to consume for analysis of token transfer events.

6. crypto_ethereum.logs

Similar to the token_transfers table, the logs table contains data for smart contract events. However, it contains all log data, not only ERC20 token transfers. This table is generally useful for reporting on any logged event type on the Ethereum blockchain.

7. crypto_ethereum.balances

This table contains Ether balances of all addresses, updated daily.

Run-on BigQuery.

Partitioned Fields

You can see “Partitioned Field” if the table does have a partition setup in the diagram.

When working with the following tables

  • crypto_ethereum.contracts
  • crypto_ethereum.logs
  • crypto_ethereum.token_transfers
  • crypto_ethereum.traces
  • crypto_ethereum.transactions

Try to use a partitioned field. This will speed up your queries and reduce your costs.

WHERE transactions.block_timestamp = '2021-01-21'
WHERE blocks.timestamp = '2010-12-05'

Interactive diagram

Find an interactive diagram at https://dbdiagram.io/d/602e721380d742080a3b1265 with more column-level details.

https://dbdiagram.io/d/602e721380d742080a3b1265

Learn SQL and Cryptocurrencies

I have a few posts that will get you learning and using SQL very quick with blockchain data from different cryptocurrencies. Try “The fastest way to learn SQL with Bitcoin data on a live database from Google”.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rif Kiamil

Rif Kiamil

178 Followers

Happy to answer questions on SQL, ERP, Blockchain & Google Cloud Platform.