UINT256 Handling

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 BigQuerypage.

Go to BigQuery

The following query is loaded into the Editorfield:

  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