Skip to main content

Xatu Execution Layer data now available

·898 words·5 mins·
Xatu Execution Layer Cryo
savid
DevOps Engineer
samcm
DevOps Engineer
Table of Contents

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
1228
117

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
#

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 lower function 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 function reinterpretAsUIntX. For example, in the canonical_execution_transaction table, the value column should be queried as reinterpretAsUInt256(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 ❀️