Skip to content

BigQuery dataset

This dataset is subject to change without notice.
Please provide feedback on missing features to be included in the final ETL.

Public dataset

Nodely maintains a public BigQuery dataset with Algorand mainnet contents:

  • snapshot only (daily updates once schema is finalized)
  • schema inspired by Google public blockchain datasets (ETH, …)
  • dataset is public but not free - Google charges for data read from this dataset
  • contact [email protected] for datasets and/or SelfService BI with hourly or 5m incremental loads.

Quick start

Go to Algorand dataset @ BigQuery and start playing ;D

Reading list

Schema

Base tables

CREATE TABLE `nodely-data-pub.algorand_mainnet_daily.transactions`
(
block_number INT64 NOT NULL -- Number of the block in which this transaction was located.
block_timestamp TIMESTAMP NOT NULL -- Unix timestamp when the block was assembled
transaction_hash STRING NOT NULL -- Hash of the transaction
transaction_group BYTES -- Parent atomic group id (binary)
transaction_offset INT64 NOT NULL -- Transaction's index position in the block
transaction_type STRING NOT NULL -- One of acc_close acc_payment acc_payment_create app_call app_clear app_close app_delete app_optin app_update asa_clawback asa_config asa_create asa_destroy asa_freeze asa_optin asa_optout asa_transfer asa_unfreeze key_reg key_unreg stpf
from_addr_id INT64 -- Sender address id
to_addr_id INT64 -- Recipient address id
value_float FLOAT64 NOT NULL -- Decimal adjusted amount of Algo or token
value_int NUMERIC(20) NOT NULL -- Integer amount of Algo or Token without decimal shift
close_to_addr_id INT64 -- Address id of recipient of the closing balance
close_value_float FLOAT64 NOT NULL -- Closing balance amount, decimal adjusted
close_value_int NUMERIC(20) NOT NULL -- Closing balance amount without decimal shift
gas INT64 NOT NULL -- Transaction free in microAlgos
note STRING -- Optional note (up to 1KB)
rekey_to_addr_id INT64 -- Account rekey to address id
auth_by_addr_id INT64 -- Transaction authorized by this address id (if sender is rekeyed)
has_inners BOOL NOT NULL -- Transaction has inner transactions
is_inner BOOL NOT NULL -- Transaction is an inner transaction issued by smart contract
clawback_from_addr_id INT64 -- Address id of the account the token is clawed back from
token_cfg STRUCT<list ARRAY<STRUCT<element INT64>>> NOT NULL -- new address ids for reserve/clawback/freezer/manger in random order
token_id INT64 -- Asset id (token/nft)
token_total NUMERIC(20) -- Total number of assets minted
token_unit_name STRING -- Asset unit name
contract_id INT64 -- ID of the contract called
input STRING -- Extra TX data / app call input
root_offset INT64 -- transaction offset pointing to the root (outer) transation
parent_offset INT64 -- transaction offset pointing to parent transaction
depth INT64 -- the depth of the transaction in the tree
inners_all INT64 -- number of all inners spawned by this transaction (recursive)
inners_direct INT64 -- number of all direct (1st level) inners spawned by this transaction
PRIMARY KEY (block_number, transaction_offset) NOT ENFORCED,
FOREIGN KEY (from_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (close_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (rekey_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (from_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (close_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (rekey_to_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (auth_by_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (auth_by_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (clawback_from_addr_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.accounts`(addr_id) NOT ENFORCED,
FOREIGN KEY (token_id) REFERENCES `nodely-data-pub.algorand_mainnet_daily.tokens`(token_id) NOT ENFORCED
)
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, MONTH)
CLUSTER BY block_number, transaction_offset
OPTIONS(
description="Algorand mainnet transactions. See: https://nodely.io/docs/public/bigquery",
labels=[("status", "production")]
);

Base views

-- Simple non zero Algo / NFT / Token flows
-- Each transaction creates 2 or more flows , +close_to, +clawback
-- Caveats:
-- * Clawback not rendered yet
-- * An NFT is a token with a total number or units == 1
--
create view `algorand_mainnet_daily.v_flows_simple` as
with flows as (
-- no clawback
select
block_timestamp
, from_addr_id addr_id
, to_addr_id other_addr_id
, 'outflow' as dir
, value_float as flow_value
, gas / pow(10,6) gas_algo
, case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
, token_id
, t.is_inner
, t.transaction_type
from
`algorand_mainnet_daily.transactions` t
where
(t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
UNION ALL
select
block_timestamp
, from_addr_id addr_id
, close_to_addr_id other_addr_id
, 'outflow' as dir
, close_value_float as flow_value
, 0 gas_algo
, case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
, token_id
, t.is_inner
, t.transaction_type
from
`algorand_mainnet_daily.transactions` t
where
(t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
and close_to_addr_id IS NOT NULL
UNION ALL
select
block_timestamp
, to_addr_id addr_id
, from_addr_id other_addr_id
, 'inflow' as dir
, value_float as flow_value
, 0 gas_algo
, case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
, token_id
, t.is_inner
, t.transaction_type
from
`algorand_mainnet_daily.transactions` t
where
(t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
UNION ALL
select
block_timestamp
, close_to_addr_id addr_id
, from_addr_id other_addr_id
, 'inflow' as dir
, close_value_float as flow_value
, 0 gas_algo
, case when t.transaction_type like 'acc%' then 'algo' when token_total = 1 then 'nft' else 'token' end xfer
, token_id
, t.is_inner
, t.transaction_type
from
`algorand_mainnet_daily.transactions` t
where
(t.transaction_type like 'acc%' or t.transaction_type like 'asa%')
and close_to_addr_id IS NOT NULL
) select * from flows where flow_value > 0;

Aux tables

CREATE TABLE `nodely-data-pub.algorand_mainnet_daily`.token_prices
(
token_id INT64 NOT NULL -- id of the token
token_name STRING NOT NULL -- token name
token_ticker STRING NOT NULL -- token ticker
algo_value FLOAT64 NOT NULL -- token current value in Algo
market_cap FLOAT64 NOT NULL -- token current MC in Algo
first_block_number INT64 NOT NULL -- first block with price data")
)
CLUSTER BY token_id
OPTIONS(
description="Token prices, updated daily. See: https://nodely.io/docs/public/bigquery",
labels=[("status", "development")]
);

Example queries

Accounts

Addresses created monthly

-- <1 GB processed
SELECT
TIMESTAMP_TRUNC(bs.block_timestamp, MONTH) ts
, sum(`addresses_new_cnt) addresses_created
FROM
`algorand_mainnet_daily.blocks` bs
GROUP BY
ts
ORDER BY
ts

Daily discinct addresses stats

-- no clawback
select
TIMESTAMP_TRUNC(flows.block_timestamp, DAY) ts
,count(distinct(flows.addr_id)) uniq_addresses
,countIf(dir = 'outflow' and transaction_type = 'acc_payment_create') new_addresses
,count(distinct(case when dir = 'outflow' then flows.addr_id else NULL end)) uniq_senders
,count(distinct(case when dir = 'outflow' and xfer='algo' and flow_value > 100 then flows.addr_id else NULL end)) uniq_algo_senders_over_100A
,count(distinct(case when dir = 'inflow' then flows.addr_id else NULL end)) uniq_reveicers
,count(distinct(case when dir = 'inflow' and xfer='algo' and flow_value > 100 then flows.addr_id else NULL end)) uniq_algo_receivers_over_100A
,count(distinct(case when dir = 'inflow' then flows.addr_id || ':' || other_addr_id else NULL end)) uniq_pairs
from
`algorand_mainnet_daily.v_flows_simple` flows
where
flows.block_timestamp between '2024-04-01' and '2024-04-30'
group by ts
order by ts

Top 10

-- White Stars (spawning new accounts)
-- ~5GB
WITH whiteStars AS (
SELECT
from_addr_id addr_id,
count(distinct(to_addr_id)) spread
FROM `algorand_mainnet_daily.transactions`
WHERE
block_timestamp > CURRENT_TIMESTAMP() - INTERVAL 7 DAY
AND transaction_type = 'acc_payment_create'
AND value_int > 0
GROUP BY from_addr_id
ORDER BY spread DESC
LIMIT 10
) SELECT
address , spread as new_accounts
FROM
whiteStars JOIN `algorand_mainnet_daily.accounts` acc USING (addr_id)
ORDER BY new_accounts DESC

Token value

-- Circulating (non clawback, non reserve, not burned) tokens value in USD
DECLARE algousdc FLOAT64 DEFAULT 0;
SET (algousdc) = (
SELECT AS STRUCT algo_value
FROM `algorand_mainnet_daily.token_prices`
WHERE token_id = 31566704 /* USDC */
);
SELECT
sum(th.balance_float * algo_value) / algousdc MC_USD
FROM
`algorand_mainnet_daily.token_holdings` th
JOIN `algorand_mainnet_daily.token_prices` tp using (token_id)
WHERE
is_circulating
and th.addr_id not in (2853884730,1970085357,2451993965,1970086565,2451993800,2451993961,3579003880) /* skip known reserve */

Demo

SELECT
TIMESTAMP_TRUNC(block_timestamp, MONTH) yearMonth
, count(*) newContractDeployments
FROM
nodely-data-pub.algorand_mainnet_daily.contracts
WHERE
block_timestamp < TIMESTAMP_TRUNC(current_timestamp(), MONTH)
GROUP BY
yearMonth;

Video tutorials


Upcoming

  • Base tables
    • contracts_accountstate
  • Aux tables
    • Account labels
    • NFD labels
  • Virtual transaction tables
    • Genesis
    • Legacy rewards
    • 2024+ block rewards