Explorer API User Tutorial

1. Introduction

In this tutorial, we will cover

• a sample query to find mints and burns for Tether (USDT) on EVM chains

• how to use the Allium Explorer API to execute this query

• how to use the API to fetch results in your developer environment

• convert the fetched results into a Pandas data frame in Jupyter Notebook

2. Creating Query

The following query can be used to find mints and burns for Tether (USDT) on EVM chains since the start of 2023. To make queries run faster, we have selected a subset of popular stablecoins.

with
  mints_and_burns as (
	select
  	block_timestamp,
  	chain,
  	case
    	when from_address = '0x0000000000000000000000000000000000000000' then amount
    	when to_address = '0x0000000000000000000000000000000000000000' then - amount
  	end as amount,
  	case
    	when from_address = '0x0000000000000000000000000000000000000000' then raw_amount
    	when to_address = '0x0000000000000000000000000000000000000000' then - raw_amount
  	end as raw_amount
	from
  	crosschain.stablecoin.transfers
	where
  	1 = 1
  	and base_asset in ('usdt')
  	and chain not in ('solana', 'tron') -- For EVM chains
  	and (
    	to_address = '0x0000000000000000000000000000000000000000'
    	or from_address = '0x0000000000000000000000000000000000000000'
  	)
  )
select
  date_trunc ('day', block_timestamp) as date,
  chain,
  sum(amount) as amt,
  sum(raw_amount) as raw_amt
from
  mints_and_burns
group by
  1, 2

3. Using SQL as a query parameter

Allium Explorer queries support the use of parameters, as shown below.

One trick is to just create a singular parameter as the query body and use the Explorer API to insert any SQL you’d like in your developer environment. This is what this will look like:

Tip: If you don’t want to pass in SQL as a parameter, you can just copy the above SQL into the app’s query editor instead! Proceed to Save your query and Copy the query ID under the three dots in the top corner.

4. Execute the query in Jupyter

First set up the following cells with your API key, the Query ID you copied, and the SQL text:

Allium Explorer exposes the following endpoints through a REST API to fetch and poll your query run.

1. POST /api/v1/explorer/queries/{query_id}/run-async: Execute the query asynchronously. This creates a query run ID for you to poll the status and results from

2. GET /api/v1/explorer/query-runs/{run_id}/status: Get the status of a query run as an enumeration of created | queued | running | success | failed | canceled

3. GET /api/v1/explorer/query-runs/{run_id}/results: Get the results of a query run with status success.

The typical flow would be to

1. Create a query run, and get the run_id

2. Poll the query run status using the run_id in a while loop

3. When the query run status is success, get the results

Allium Explorer exposes the following endpoints through a REST API to fetch and poll your query run.

Here’s what this would look like in Jupyter:

1. Create a query run, and get the run_id

1. Poll the query run status using the run_id in a while loop

1. When the query run status is success, get the results

5. Get the results into a Pandas Dataframe

The results data is now in a list-of-dictionaries form. This can be loaded into a Pandas dataframe, as demonstrated below.

Resources

The notebook used in the above example can be found at: https://github.com/garlicshank/allium-api-examples/blob/main/notebooks/tether-mints-evm.ipynb

Questions?

If any questions remain after this guide on how to get started, connect to our APIs, or retrieve specific data you have in mind, feel free to reach out to [e-mail] at any time.