UINT256 Handling

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

This page provides information on how to handle blockchain UINT256 data types in Blockchain Analytics.

Lossless columns

Blockchain Analytics does not support UINT256 NUMERIC columns, but some blockchain data types have numerical precision up to UINT256.

In order to preserve the numerical precision, Blockchain Analytics datasets presents UINT256 values in two separate columns:

• An UINT128 NUMERIC column with potential loss of precision.
• A STRING column containing the full decimal value in string form. The string columns are marked with the _lossless prefix.

BigQuery User-Defined Functions (UDF)

Google Cloud hosts a Blockchain Analytics utility library that contains UDF for handling UINT256 computations and aggregations. The following UDFs are relevant for UINT256 computation.

• bqutil.fn.bignumber_add
• bqutil.fn.bignumber_sub
• bqutil.fn.bignumber_mul
• bqutil.fn.bignumber_div
• bqutil.fn.bignumber_sum
• bqutil.fn.bignumber_avg

See the BigQuery UDF GitHub repository for details on community managed BigQuery UDFs.

See the BigQuery UDF documentation for instructions on using BigQuery UDFs.

Lossless example with UDF workaround for UINT256

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

Go to BigQuery

The following query is loaded into the Editor field:

WITH withdrawals AS (
  SELECT
    w.amount_lossless AS amount,
    DATE(b.block_timestamp) AS block_date
  FROM
    bigquery-public-data.blockchain_analytics_ethereum_mainnet_us.blocks AS b
    CROSS JOIN UNNEST(withdrawals) AS w
)
SELECT
  block_date,
  bqutil.fn.bignumber_div(bqutil.fn.bignumber_sum(ARRAY_AGG(amount)), "1000000000") AS eth_withdrawn
FROM
  withdrawals
GROUP BY 1 ORDER BY 1 DESC