Full relational diagram for Ethereum public data on Google BigQuery
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
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.
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.
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.
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”.
