parse-ethereum-logs-example.sql

CREATE TEMP FUNCTION
  DECODE_ERC721_TRANSFER(data STRING, topics ARRAY<STRING>)
  RETURNS STRUCT<`from` STRING, `to` STRING, tokenId STRING>
  LANGUAGE js AS """
    var CRYPTOKITTY_TRANSFER = {"anonymous": false,"inputs": [{"indexed": false,"name": "from","type": "address"},{"indexed": false,"name": "to","type": "address"},{"indexed": false,"name": "tokenId","type": "uint256"}],"name": "Transfer","type": "event"};

    var interface_instance = new ethers.utils.Interface([CRYPTOKITTY_TRANSFER]);
    var parsedLog = interface_instance.parseLog({topics: topics, data: data});

    return parsedLog.values;
"""
OPTIONS
  ( library="gs://blockchain-etl-bigquery/ethers.js" );
SELECT
  DECODE_ERC721_TRANSFER(data, topics) AS transfer
FROM
  `bigquery-public-data.crypto_ethereum.logs`
WHERE
  topics[SAFE_OFFSET(0)] = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef" -- topic for Transfer(address,address,uint256) event
  AND address = "0x06012c8cf97bead5deae237070f9587f8e7a266d"
LIMIT 100;