We’re excited to announce that Execution layer data, extracted via cryo, is now available in the Xatu dataset.
paradigmxyz/cryo
cryo is the easiest way to extract blockchain data to parquet, csv, json, or python dataframes
Rust
1156
105
Summary:
- The data is dedicated to the public domain under the CC BY 4.0 license
- Data is partitioned in 1000 block chunks in Apache Parquet files
- We’ve extracted data all the way back to genesis
By providing this data we hope to enable more in-depth analysis and research into the Ethereum ecosystem and compliment the rest of the dataset.
Tables #
- canonical_execution_block
- canonical_execution_transaction
- canonical_execution_traces
- canonical_execution_logs
- canonical_execution_contracts
- canonical_execution_four_byte_counts
- canonical_execution_address_appearances
- canonical_execution_balance_diffs
- canonical_execution_balance_reads
- canonical_execution_erc20_transfers
- canonical_execution_erc721_transfers
- canonical_execution_native_transfers
- canonical_execution_nonce_diffs
- canonical_execution_nonce_reads
- canonical_execution_storage_diffs
- canonical_execution_storage_reads
About this data #
- Data is extracted via cryo only when the block is finalized on the beacon chain
- There is a delay before the Apache Parquet files are available for public access of approximately ~6-24 hours
- Data is stored in chunks of 1000 blocks eg;
0.parquet(block numbers 0 - 999)1000.parquet(block numbers 1000 - 1999)2000.parquet(block numbers 2000 - 2999)1000000.parquet(block numbers 1000000 - 1000999)
- All hex strings are lowercased, so remember to use the
lowerfunction eg.WHERE transaction_hash = lower('0xABcd1234') - For integer columns with a bit width greater than 64 (
Int128/UInt128/Int256/UInt256) in parquet files, use the ClickHouse functionreinterpretAsUIntX. For example, in the canonical_execution_transaction table, thevaluecolumn should be queried asreinterpretAsUInt256(value).
Getting started #
The easiest way to get started is to install the Clickhouse CLI and use clickhouse local to query the data. Working with the data has more details.
Examples #
The following examples demonstrate some trivial queries you can run on the data.
Example 1 #
What are the top 5 block producers between block 20000000 and 20010999 on Mainnet?
Answer: We can use the canonical_execution_block table to group on extra data column.
clickhouse local -q "
SELECT
count(*) as count,
author,
extra_data_string
FROM
url('https://data.ethpandaops.io/xatu/mainnet/databases/default/canonical_execution_block/1000/{20000..20010}000.parquet', 'Parquet')
GROUP BY
author, extra_data_string
ORDER BY
count DESC
LIMIT 5
FORMAT PrettyCompact
"
ββcountββ¬βauthorββββββββββββββββββββββββββββββββββββββ¬βextra_data_stringββββββββββββββββ
β 5591 β 0x95222290dd7278aa3ddd389cc1e1d165cc4bafe5 β beaverbuild.org β
β 3389 β 0x4838b106fce9647bdf1e7877bf73ce8b0bad5f97 β Titan (titanbuilder.xyz) β
β 441 β 0x1f9090aae28b8a3dceadf281b0f12828e676c326 β rsync-builder.xyz β
β 325 β 0x1f9090aae28b8a3dceadf281b0f12828e676c326 β @rsyncbuilder β
β 220 β 0xdf99a0839818b3f120ebac9b73f82b617dc6a555 β Illuminate Dmocratize Dstribute β
βββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββββββββ
Example 2 #
Top 5 transfers by value between blocks 20900000 and 20905999 on Mainnet?
Answer: We can use the canonical_execution_transaction table to filter out transactions with no calldata and order by value.
clickhouse local -q "
SELECT
block_number,
transaction_hash,
reinterpretAsUInt256(value) / 1e18 AS ether_value
FROM
url('https://data.ethpandaops.io/xatu/mainnet/databases/default/canonical_execution_transaction/1000/{20900..20905}000.parquet', 'Parquet')
WHERE
input IS NULL
ORDER BY
ether_value DESC
LIMIT 5
FORMAT PrettyCompact
"
ββblock_numberββ¬βtransaction_hashββββββββββββββββββββββββββββββββββββββββββββββββββββ¬ββββββββether_valueββ
β 20902203 β 0x62927f22026352b0af12fa844e588b999089849b98c50a89641cd19d69dfac35 β 14008.381015295294 β
β 20903687 β 0x39882cafc4195eee7b63444be4e9d16aad57e9465c2c4ef565758fa559a4e9f8 β 8444.154971169999 β
β 20900096 β 0x55a8950dc6e205bb1397bfe17a93767fb4ce026402b25ff9104476108b5d8807 β 8357.538338940001 β
β 20902260 β 0xbee9e1fcbc8405f6ef6620c08f47f568e8df0bb80feb5d77362aaf0e38e123a5 β 7572.340613229151 β
β 20902737 β 0x586f91964ed8a2adaab4f1c26a011b934a978c03e91303e9f74b206541fe9e21 β 6800 β
ββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββ
Example 3 #
What was the volume of USDT transferred between block 20900000 and 20905999 on Mainnet?
Answer: We can use the canonical_execution_erc20_transfers table to sum the value of the USDT erc20 contract address.
clickhouse local -q "
SELECT
sum(reinterpretAsUInt256(value)) / 1000000 as USDT
FROM
url('https://data.ethpandaops.io/xatu/mainnet/databases/default/canonical_execution_erc20_transfers/1000/{20900..20905}000.parquet', 'Parquet')
WHERE
erc20 = lower('0xdAC17F958D2ee523a2206206994597C13D831ec7') -- USDT erc20 contract address
FORMAT PrettyCompact
"
βββββββββββββββUSDTββ
β 1585831125.724754 β -- 1.59 billion
βββββββββββββββββββββ
Example 4 #
How can I debug the transaction 0xe1cf349a8e55309cfab9d8d3dd8a8094765f99d65b20864c265bcd234abfe570 in block 20875205 that failed to execute?
Answer: We can use the canonical_execution_traces table to get the full trace details. We know the transaction occured in block 20875205 so we can request the parquet chunk 20875000.parquet.
clickhouse local -q "
SELECT
trace_address,
action_from,
action_to,
action_value,
action_gas,
error
FROM
url('https://data.ethpandaops.io/xatu/mainnet/databases/default/canonical_execution_traces/1000/20875000.parquet', 'Parquet')
WHERE
transaction_hash = lower('0xe1cf349a8e55309cfab9d8d3dd8a8094765f99d65b20864c265bcd234abfe570')
ORDER BY internal_index
FORMAT PrettyCompact
"
ββtrace_addressββ¬βaction_fromβββββββββββββββββββββββββββββββββ¬βaction_toβββββββββββββββββββββββββββββββββββ¬βaction_valueββ¬βaction_gasββ¬βerrorβββββββ
β α΄Ία΅α΄Έα΄Έ β 0x1FCCC097DB89A86BFC474A1028F93958295B1FB7 β 0xEEFBA1E63905EF1D7ACBA5A8513C70307C1CE441 β 0 β 29587220 β Reverted β
β 0 β 0xEEFBA1E63905EF1D7ACBA5A8513C70307C1CE441 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0 β 28883047 β α΄Ία΅α΄Έα΄Έ β
β 0_0 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x065347C1DD7A23AA043E3844B4D0746FF7715246 β 0 β 28421311 β α΄Ία΅α΄Έα΄Έ β
β 0_1 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x065347C1DD7A23AA043E3844B4D0746FF7715246 β 0 β 28405020 β α΄Ία΅α΄Έα΄Έ β
β 0_2 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x35A338522A435D46F77BE32C70E215B813D0E3AC β 0 β 28394712 β α΄Ία΅α΄Έα΄Έ β
β 0_3 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x35A338522A435D46F77BE32C70E215B813D0E3AC β 0 β 28391907 β α΄Ία΅α΄Έα΄Έ β
β 0_4 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x74C5A0D5DFCC6D4527C849F09ECC360C5345D986 β 0 β 28384423 β α΄Ία΅α΄Έα΄Έ β
β 0_4_0 β 0x74C5A0D5DFCC6D4527C849F09ECC360C5345D986 β 0xEFF8E65AC06D7FE70842A4D54959E8692D6AE064 β 0 β 27898562 β α΄Ία΅α΄Έα΄Έ β
β 0_4_0_0 β 0xEFF8E65AC06D7FE70842A4D54959E8692D6AE064 β 0x78910E1DFE6DF94EA7EEC54B25921673DB0E2A06 β 0 β 27457785 β α΄Ία΅α΄Έα΄Έ β
...
β 221 β 0xEEFBA1E63905EF1D7ACBA5A8513C70307C1CE441 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0 β 67576 β Out of gas β
β 221_0 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x065347C1DD7A23AA043E3844B4D0746FF7715246 β 0 β 62480 β α΄Ία΅α΄Έα΄Έ β
β 221_1 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x065347C1DD7A23AA043E3844B4D0746FF7715246 β 0 β 48158 β α΄Ία΅α΄Έα΄Έ β
β 221_2 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x35A338522A435D46F77BE32C70E215B813D0E3AC β 0 β 45036 β α΄Ία΅α΄Έα΄Έ β
β 221_3 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x35A338522A435D46F77BE32C70E215B813D0E3AC β 0 β 44200 β α΄Ία΅α΄Έα΄Έ β
β 221_4 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x74C5A0D5DFCC6D4527C849F09ECC360C5345D986 β 0 β 41145 β α΄Ία΅α΄Έα΄Έ β
β 221_4_0 β 0x74C5A0D5DFCC6D4527C849F09ECC360C5345D986 β 0xEFF8E65AC06D7FE70842A4D54959E8692D6AE064 β 0 β 13996 β α΄Ία΅α΄Έα΄Έ β
β 221_4_0_0 β 0xEFF8E65AC06D7FE70842A4D54959E8692D6AE064 β 0x78910E1DFE6DF94EA7EEC54B25921673DB0E2A06 β 0 β 13345 β α΄Ία΅α΄Έα΄Έ β
β 221_5 β 0x9500F306CD233BF2BCFF1CC90D565C64626A162C β 0x74C5A0D5DFCC6D4527C849F09ECC360C5345D986 β 0 β 6184 β Out of gas β
βββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββββββ΄βββββββββββββ΄βββββββββββββ
Wrapping up #
We hope you find this data useful and look forward to seeing what you build with it! If you have any questions or feedback please reach out to us on Twitter or join the Xatu Telegram Group
Happy querying!
Love,
EthPandaOps Team β€οΈ