Simplified relational diagram for Ethereum public data on Google BigQuery
Find a simplified relational diagram for the Ethereum data coming from an amazing opensource project https://github.com/blockchain-etl.
The full Ethereum diagram can be used if you want more details on the trace table and internal transactions.
You can also find a simplified and full diagram for Bitcoin ₿.
Example Query
Run a BigQuery shared query, if you don't have BigQuery setup follow the simple step by step guide that gives you a free 1TiB a month with no credit card.
Keeping cost low
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 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'
Simplified diagram
- This simplified relational diagram should cover most needs.
- The full diagram version will have the composite keys relationships, details about internal contracts and how to use the log/trace tables.
Interactive diagram
Find an interactive diagram at https://dbdiagram.io/d/602d567d80d742080a3aed4b with more column-level details.
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”.
