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()

sql

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
ORDER BY count DESC
LIMIT 1

Result

Example Unhex()

sql

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.
GROUP BY date

extract_address()

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.

sql

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

UNION ALL

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.

data_field()

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().

Example

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.

sql

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:

sql

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.