balancer new pool bigquery example with ethers.js and ethjs-abi.js

query.sql

CREATE TEMP FUNCTION
  KECCAK256(data STRING)
  RETURNS STRING
  LANGUAGE js AS """
    var utf8 = unescape(encodeURIComponent(data));
    var arr = [];
    for (var i = 0; i < utf8.length; i++) {
      arr.push(utf8.charCodeAt(i));
    }
    return ethers.utils.keccak256(arr)
"""
OPTIONS
  ( library="gs://blockchain-etl-bigquery/ethers.js" );

CREATE TEMP FUNCTION
  PARSE_LOG(data STRING, topics ARRAY<STRING>)
  RETURNS STRUCT<`caller` STRING, `pool` STRING>
  LANGUAGE js AS """
    var parsedEvent = {"anonymous": false, "inputs": [{"indexed": true, "internalType": "address", "name": "caller", "type": "address"}, {"indexed": true, "internalType": "address", "name": "pool", "type": "address"}], "name": "LOG_NEW_POOL", "type": "event"}
    return abi.decodeEvent(parsedEvent, data, topics, false);
"""
OPTIONS
  ( library="gs://blockchain-etl-bigquery/ethjs-abi.js" );

WITH parsed_logs AS
(SELECT
    logs.block_timestamp AS block_timestamp
    ,logs.block_number AS block_number
    ,logs.transaction_hash AS transaction_hash
    ,logs.log_index AS log_index
    ,PARSE_LOG(logs.data, logs.topics) AS parsed
FROM `bigquery-public-data.crypto_ethereum.logs` AS logs
WHERE TRUE 
  -- AND address = '0x9424b1412450d0f8fc2255faf6046b98213b76bd'
  AND topics[SAFE_OFFSET(0)] = KECCAK256("LOG_NEW_POOL(address,address)")
)
SELECT
     block_timestamp
     ,block_number
     ,transaction_hash
     ,log_index
    ,parsed.caller AS `caller`
    ,parsed.pool AS `pool`
FROM parsed_logs
LIMIT 1000