Converting Ethereum Event log data from hex to integers in Python, JS and BigQuery

converting-blockchain-event-logs-hex.md

Example

https://basescan.org/tx/0x8daaf7b5269104c70ee22023f929d5a3874f3f2fca9d4367c133da6596775bf2#eventlog

decoded values:

• flowRate: 32724505000
• totalSenderFlowRate: -6610350010000
• totalReceiverFlowRate: 32724505000

raw event data split by 64 chars:

0x
000000000000000000000000000000000000000000000000000000079e8851a8
fffffffffffffffffffffffffffffffffffffffffffffffffffff9fce86f9170
000000000000000000000000000000000000000000000000000000079e8851a8
0000000000000000000000000000000000000000000000000000000000000080
0000000000000000000000000000000000000000000000000000000000000000

The ABI of the contract gives us the datatypes:

• flowRate: int96
• totalReceiverFlowRate: int256
• totalSenderFlowRate: int256

Python

flowRate: 12 bytes => 24 characters => int96

int.from_bytes(bytes.fromhex("000000000000000000000000000000000000000000000000000000079e8851a8"), byteorder='big', signed=True)

result: 32724505000

totalSenderFlowRate: 32 bytes => 64 characters => int256

int.from_bytes(bytes.fromhex("fffffffffffffffffffffffffffffffffffffffffffffffffffff9fce86f9170"), byteorder='big', signed=True)

result: -6610350010000

BigQuery (using Javascript UDFs)

CREATE TEMP FUNCTION
  convert_hex(hex STRING)
  RETURNS NUMERIC
  LANGUAGE js AS r"""
function hexToBytes(hex) {
    let bytes = new Uint8Array(hex.length / 2);
    for (let i = 0; i < hex.length; i += 2) {
        bytes[i / 2] = parseInt(hex.substr(i, 2), 16);
    }
    return bytes;
}

function bytesToBigInt(bytes, signed = false) {
    let hex = '';
    for (let i = 0; i < bytes.length; i++) {
        hex += bytes[i].toString(16).padStart(2, '0');
    }

    let bigIntValue = BigInt('0x' + hex);

    if (signed) {
        // Detect if the number is negative
        let byteLength = bytes.length;
        let signBit = 1n << BigInt((byteLength * 8) - 1);
        if (bigIntValue >= signBit) {
            // Apply two's complement
            bigIntValue -= signBit << 1n;
        }
    }

    return bigIntValue;
}
let byteArray = hexToBytes(hex);
let bigIntValue = bytesToBigInt(byteArray, true);
return bigIntValue
""";
WITH
  inputs AS (
  SELECT
    '000000000000000000000000000000000000000000000000000000079e8851a8' AS hex
  UNION ALL
  SELECT
    'fffffffffffffffffffffffffffffffffffffffffffffffffffff9fce86f9170' AS hex)
SELECT
  hex,
  convert_hex(hex) AS number
FROM
  inputs;

References

https://cloud.google.com/bigquery/docs/user-defined-functions#javascript-udf-structure

https://docs.soliditylang.org/en/v0.8.7/abi-spec.html#formal-specification-of-the-encoding

https://issuetracker.google.com/issues/283746287