Google Bigquery (Analytics)

The BigQuery Cronos zkEVM dataset makes it possible for anyone to perform complex queries on transactions, blocks, batches and logs.

BigQuery datasets are available for Cronos zkEVM and Cronos EVM on Google's Web3 product page.

By leveraging datasets in BigQuery, you can access blockchain data as easily as your internal data. You can query the full history of blocks, transactions, logs and receipts for these two chains. By joining chain data with application data, you can get a complete picture of your users and your business.

Developers only need a Google Cloud account with BigQuery enabled.

Quickstart

1. Go to Cronos zkEVM dataset and click on one of the samples.

2. You will get to the console and see the Cronos zkEVM dataset on the left in the explorer.

   

Please note BigQuery charges are based on the amount of data processed by your queries, so running the query may incur charges to your account. You can find the consumption estimate in the top right corner similar to the warning of "This query will process 1.37 GB when run."

3. You can see below BigQuery SQL query examples to run in console by click "Run".

To start developing your own BigQuery SQL code, you can refer to the following syntax. For the Cronos data schema we refer to the Google Cloud Cronos schema.

Example Queries

1. Show all Veno USD transfers

This query shows transfers of the Veno USD token on Cronos zkEVM since genesis.

In the Google Cloud console, go to the BigQuery page.

The following query is loaded into the Editor field: Replace your-project-id.cronos_zkevm_mainnet in the following query with the project ID and the linked dataset name you used when you subscribe to this dataset.

-- UDF for easier string manipulation.
CREATE TEMP FUNCTION ParseSubStr(hexStr STRING, startIndex INT64, endIndex INT64)
RETURNS STRING
LANGUAGE js
AS r"""
  if (hexStr.length < 1) {
    return hexStr;
  }
  return hexStr.substring(startIndex, endIndex);
""";

-- UDF to convert hex to decimal.
CREATE TEMP FUNCTION HexToDecimal(hexStr STRING)
RETURNS BIGNUMERIC
LANGUAGE js
AS r"""
  return BigInt(hexStr, 16);
""";

SELECT
  t.hash as transaction_hash,
  t.from_address AS from_address,
  CONCAT("0x", ParseSubStr(l.topic2, 26, LENGTH(l.topic2))) AS to_address,
  (HexToDecimal(l.data) / 1000000000000000000) AS veno_usd_transfer_amount
FROM
  `your-project-id.cronos_zkevm_mainnet.transactions` AS t
INNER JOIN
  `your-project-id.cronos_zkevm_mainnet.logs` AS l
ON
  l.transaction_hash = t.hash
WHERE
  t.to_address = LOWER("0x5b91e29Ae5A71d9052620Acb813d5aC25eC7a4A2") -- Veno USD
AND
  l.topics_count > 0
AND
  -- Transfer(address indexed src, address indexed dst, uint wad)
  l.topic0 = LOWER("0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef")
;

The following shows an example result:

transaction_hash	from_address	to_address	veno_usd_transfer_amount
0x1810569dbf9f188abef8a81f71cedc526dc5826185f901bde77a315836e8bb2e	0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a	0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890	1.065316917013257903
0xe3064e01b2d0915373d2134c50bdeb4a0a4bda3e7e3c48c58efe50e84afec0ec	0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a	0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890	1.06532400320732326
0xd872a1f2a89d0c5ffdf8348de232aee2359fee0893504567cf382fbe24fa746e	0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a	0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890	1.06532435751826383
0xbef982225518fcf3dfc2b705dbd1855dd7895f3b12688b1bfff9f69982cd0458	0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a	0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890	1.065326129074734257
0x851199e970aac6c5a4089edba79eab0c9526a4a9151d4ca27b2494b48cfff3db	0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a	0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890	1.065322585964739367

2. Wrapped zkCRO activity

This query shows the wallets with the most interactions with Wrapped zkCRO in the last 30 days.

In the Google Cloud console, go to the BigQuery page.

The following query is loaded into the Editor field:

Note: Replace your-project-id.cronos_zkevm_mainnet in the following query with the project ID and the linked dataset name you used when you subscribe to this dataset.

SELECT
  from_address AS address,
  CONCAT("https://explorer.zkevm.cronos.org/address/", from_address) AS explorer_link,
  COUNT(from_address) AS num_transactions
FROM
  `your-project-id.cronos_zkevm_mainnet.transactions` AS t
WHERE
  to_address = LOWER("0xC1bF55EE54E16229d9b369a5502Bfe5fC9F20b6d") -- Wrapped zkCRO
AND
  block_timestamp > (CURRENT_TIMESTAMP() - INTERVAL 30 DAY)
GROUP BY
  from_address
ORDER BY
  COUNT(from_address) DESC
;

The following shows an example result:

address	explorer_link	num_transactions
0x4712633d145d5c5b5b35b95e1a4fb06bbad4b1ec	https://explorer.zkevm.cronos.org/address/0x4712633d145d5c5b5b35b95e1a4fb06bbad4b1ec	59
0x9d6e06ec5c09b09559d65a3e2906b2b1c2ac0b68	https://explorer.zkevm.cronos.org/address/0x9d6e06ec5c09b09559d65a3e2906b2b1c2ac0b68	37
0x06556fe0eb119fcfcc0025ab7427e2c9eafd8e98	https://explorer.zkevm.cronos.org/address/0x06556fe0eb119fcfcc0025ab7427e2c9eafd8e98	28
0xed0598b07faacb711870b58eae40878128928a4b	https://explorer.zkevm.cronos.org/address/0xed0598b07faacb711870b58eae40878128928a4b	26
0x3d5bc4222549f7e74d1da14745ab34dec8b2c90c	https://explorer.zkevm.cronos.org/address/0x3d5bc4222549f7e74d1da14745ab34dec8b2c90c	20

Contract Addresses

You can find a list of key contract addresses on Cronos zkEVM documentation.