T.L.M.

and

BigQuery

What is BigQuery?

  • analytical database managed by Google
  • can store and process very very large datasets
  • it is very fast and expensive

Pricing

Pay per query

Pay flat fee

More data scanned for query, more cost

Pay for monthly  slots (computation power)

Why do we need it?

  • we don't have historical transactions and operations
  • third-party APIs are too slow, expensive or wrong
  • BigQuery hosts public datasets for ethereum and polygon

How do we use it?

Attempt to backfill live

Attempt to backfill for a year

  • triggered when we see a new wallet address in our system
  • slow because the dataset is not optimized for querying by wallet address
  • queries BigQuery for last year of data for ALL the wallets
  • inefficient use of BigQuery slots
  • fast because the data is optimized for time based queries

May 24 20:44:31.346 [INFO] Running backfill transactions cron
May 24 20:44:31.349 [INFO] Backfilling transactions from 2021-10-16 to 2021-10-09
May 24 20:44:33.130 [INFO] Processing BigQuery job:  wnIRuQQF16WWLgRQirnIneQFytl
May 24 20:45:17.148 [INFO] Fetch and save internal transactions executed map[count:100000 remaining:2361766]
May 24 20:45:34.848 [INFO] Fetch and save internal transactions executed map[count:100000 remaining:2261766]
May 24 20:45:54.230 [INFO] Fetch and save internal transactions executed map[count:100000 remaining:2161766]
...
...
...
May 24 21:00:26.924 [INFO] Fetch and save internal transactions executed map[count:100000 remaining:61766]
May 24 21:00:54.060 [INFO] Fetch and save internal transactions executed map[count:61766 remaining:0]

May 24 21:00:54.077 [INFO] Backfilling transactions from 2021-10-09 to 2021-10-02
May 24 21:00:55.489 [INFO] Processing BigQuery job:  0JzfWnUeriC8KCkN1Sud8rxUTj8
May 24 21:02:10.897 [INFO] Fetch and save internal transactions executed map[count:100000 remaining:2459893]
May 24 21:02:53.119 [INFO] Fetch and save internal transactions executed map[count:100000 remaining:2359893]
May 24 21:03:22.509 [INFO] Fetch and save internal transactions executed map[count:100000 remaining:2259893]
...
...
...

How does it look?

T.L.M.

and

BigQuery

T.L.M. and BigQuery

By Tarun Batra

T.L.M. and BigQuery

  • 281