A squid that saves USDC Transfers to a BigQuery dataset

This tiny blockchain indexer scrapes Transfer events emitted by the USDC contract and saves the data to a dataset on Google BigQuery.

Dependencies: NodeJS, Git, Squid CLI.

To try it out, first download it and install local dependencies:

git clone https://github.com/subsquid-labs/squid-bigquery-example
cd squid-bigquery-example
npm i

then populate the .env file and execute

sqd process

Make sure to use an ID of an existing dataset for GOOGLE_DATASET_ID!

If you visit the console now you should see that the two new tables status and transfers have been created and are being populated within your dataset.

Visit the documentation page for more details on using squids with BigQuery.

Troubleshooting

Transaction is aborted due to concurrent update

This means that your project has an open session that is updating some of the tables used by the squid.

Most commonly, the session is left by a squid itself after an unclean termination. You have two options:

1. If you are not sure if your squid is the only app that uses sessions to access your BigQuery project, find the faulty session manually and terminate it. See Get a list of your active sessions and Terminate a session by ID.

2. DANGEROUS If you are absolutely certain that the squid is the only app that uses sessions to access your BigQuery project, you can terminate all the dangling sessions by running

   FOR session in (
     SELECT
       session_id,
       MAX(creation_time) AS last_modified_time,
     FROM `region-us`.INFORMATION_SCHEMA.SESSIONS_BY_PROJECT
     WHERE
       session_id IS NOT NULL
       AND is_active
     GROUP BY session_id
     ORDER BY last_modified_time DESC
   )
   DO
     CALL BQ.ABORT_SESSION(session.session_id);
   END FOR;

   Replace region-us with your dataset's region in the code above.

   You can also enable abortAllProjectSessionsOnStartup and supply datasetRegion in your database config to perform this operation at startup:

   const db = new Database({
     // ...
     abortAllProjectSessionsOnStartup: true,
     datasetRegion: 'region-us'.
   })

   This method will cause data loss if, at the moment when the squid starts, some other app happens to be writing data anywhere in the project using the sessions mechanism.