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
