Functions to Decode Raw Data

Hex & Unhex()

All columns in the all_chains table that can contain a hexadecimal string are stored in byte code.

The columns stored in byte code include:

  1. block_hash

  2. block_parent_hash

  3. tx_hash

  4. tx_sender

  5. tx_recipient

  6. log_emitter

  7. topic0 - topic3

  8. data0 - data3

  9. data_rest

Example Hex()

SELECT uniq(tx_hash) as count
     , '0x'||hex(tx_sender) as tx_sender
     --hex() is used here to transform byte code into a readable string
FROM blockchains.all_chains 
WHERE chain_name = 'avalanche_mainnet'
  and signed_at > toDate(now()) - 7
GROUP BY tx_sender

Example Unhex()

SELECT uniq(tx_hash) as count
	   , date_trunc('day', signed_at) as date
FROM blockchains.all_chains 
WHERE chain_name = 'eth_mainnet'
AND tx_recipient = unhex('7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9')
 -- Aave's V2 Lending Pool Contract. Note that '0x' must be removed
AND signed_at > '2021-01-01'
AND signed_at < '2021-01-10' 
-- Using this date range as an example.


Contract and wallet addresses are stored differently depending on the columns they're in. For example, lets compare how the WETH contract is stored in the log_emitter and topic1 column.

SELECT hex(log_emitter) as log_emitter
FROM blockchains.all_chains
WHERE topic0 = unhex('7FCF532C15F0A6DB0BD6D0E038BEA71D30D808C7D98CB3BF7268A95BF5081B65')
AND tx_hash = unhex('D33F0D86C1EC1C5B7C5A5A068189C9189FB549292759DFA3EFAE73DD0C65B6D4')


SELECT hex(topic1) as topic1
FROM blockchains.all_chains
WHERE topic0 = unhex('8BC254D6F262E53C6F080B869501CA562EE1252C6EF58D59C4758BB14260A095')
AND tx_hash = unhex('0EB1A4415CE7E2C23B053232FD686C4007C027EA0F65A832DEC0D7845CC24FBC')

---------------- The Result ---------------------
-- log_emitter = C02AAA39B223FE8D0A0E5C4F27EAD9083C756CC2	                  
-- topic1 	   = 000000000000000000000000C02AAA39B223FE8D0A0E5C4F27EAD9083C756CC2

As you can see, hex(logemitter) returns a version of the WETH token contract that's often displayed on CoinGecko or Metamask. However, hex(topic1) returns a version of the WETH token contract with 24 leading zeros. To remove these leading zeros from the data or topic field, use the extract_address() function, which selects the rightmost 40 characters in a string.


The following image matches the different parts of a transaction log to columns in the all_chains table.

As you can see, data0 - data3 holds the information from individual rows in the data field. The data_rest column holds all the information in the data fields outside data0 - data3. To query individual rows from this blob of data, use data_field().


The data_field() is very rarely used in analysis. Let's assume you are trying to find the token amount bridged through LI.FI - we won't query the token address nor the USD amount bridged for simplicity reasons. To start, let's look at the bridge's on-chain event.

SELECT to_u256_raw(abi_field(data_rest, 5))
FROM blockchains.all_chains
WHERE tx_hash = unhex('98c43948bca05770d8a5d6e5ba8e2c4822dffd9d936b097667da113316f157f6') -- Li.Fi Bridge Transactions
AND topic0 = unhex('438f81f3fe94456cd9d98e9073524f1c2bafb3ce75def8ced69f708061ddd5c4')
AND chain_name = 'eth_mainnet'
-- Result: 4400000000000000

to_float64_raw() & to_float64_adj()

Numbers stored on the blockchain are formatted as hexadecimal strings - as seen below.

To query a number from the topic or data fields, use theto_float64raw() function. The code sample below is querying a number from the topic3 field:

SELECT to_float64_raw(data0)
FROM blockchains.all_chains
WHERE chain_name =  'eth_mainnet'
AND tx_hash = unhex('E207FF3ED73F1BBEA87B64B3D41025EADCC867A5ADECE7FB3CBC0731F7F7DA2F') -- Transfer Event

In addition, token amounts will often need to be divided by 10^(number decimal). To shorthand this equation, use the to_float64_adj(x,y) function..

In the to_float64_adj(x,y) function, x is the data or topic field that holds the token amount (integer), and y is the number decimal for the given token. The code example below shows that the to_float64_adj(x,y) function is the same as to_float64_raw(x)/10^y.

If the number is too large to fit into a floating point number format, which can be the case on the blockchain, use the `to_u256_raw` function.