Skip to content

Query On-Chain Data

Users can query the Ethereum blockchain directly from the Rose Web Platform via Google BigQuery instance using SQL.

Demo Rose Notebook

How to Query the Blockchain

  1. Add a Query Module to your notebook by selecting "Add SQL" from the lefthand menu.
  2. From the database selector in the top right of the new Query module, select the crypto_ethereum database.
  3. Add your SQL query to the Query module and press shift + enter to run on the ethereum blockchain.

    annotated query module

  4. Save the results of your query to a rosecode using logic: rosecode = SQL query

    push logic query

    1. By doing this, we can then use Rose transformations to act on the data given by the query.
    2. Below is an example where we used the Rose :ts(date_column, value_column) transfomrmation to create a timeseries and then renamed that timeseries to be more concise.

      Example:, eth_burned)

      converting to a ts =, eth_burned)
      push logic

Tables Available to Query

Rose has 7 different BigQuery tables that are available to be queried upon, with each table having many unique data columns. The available tables are the following:


  • Timestamp - publishing time stamp
  • Number - categorical number
  • Hash - block chain management pathway, fixed pointer size
  • Parent_hash - previous block’s hash
  • Nonce - number try to set such that the block’s hash starts with a desired number of zeros - leads to signed - process of mining (signed block)
  • Sha3_uncles - blocks that were close in time to being mined, didn’t validate across nodes. Contains information about who mined it, the time it occurred, height at which it occurred, and eth reward
  • Logs_bloom - bloom filter (mathematical way of minimizing searches) for the logs of the block
  • Transactions_root - address root to the transactions block
  • State_root - address to the latest state root
  • Receipts_root - address to the latest receipts root
  • Miner - miner that solved the block
  • Difficulty - mining difficulty of the block
  • Total_difficulty - total difficulty of the chain until this block - accumulated sum of difficulty of all previous blocks
  • Size - size of the data
  • Extra_data - extra data not included in the core data of the blockchain
  • Gas_limit - limit of the amount of gas for the block set by the network
  • Gas_used - gas used in the creation of the block
  • Transaction_count - The number of transactions in the block
  • Base_fee_per_gas - base eth cost per unit of gas - gas price for the block
  • (Root -> is the hash - mined signed code - of the trie of data in the block)
  • (Trie -> is the array and linked lists in the data set)


List of all contracts - no real unique key - type of ethereum account. Deployed not as a user but as a program

  • Address - address that has verified source code
  • Bytecode-combined storage of unique operating codes instruction set for contract
  • Function_sighashes - functional signature of the hash
  • Is_erc20 - true/false if the contract is an ERC20 contract
  • Is_erc721 - true/false if the contract is an ERC721 contract
  • Block_number - block number where the contract was created
  • Block_timestamp - timestamp of the block where the contract was created
  • Block_hash - hash of the block where the contract was created


Contains data for smart contract events. Elegant way to store tiny amounts of data on the EVM blockchain for little gas. Useful to let other people know something has happened w/o having to query contracts individually

  • Log_index - index of the log in the block it’s a part of
  • Transaction_hash - transaction hash that produced this log
  • Transaction_index - the index position of the transaction in the block -
  • Address - address this log originated from
  • Data - unindexed excess/further information data regarding the even
  • Topic0 - 32 - byte hex strings - first is signature of the event unless defined by anonymous specifier
  • Topic1 - topics are the description of events for the log
  • Topic2 - potentially index keys of events that map to the same value
  • Topic3 - see other topics
  • Block_timestamp - mapping key to blocks table
  • Block_number - mapping key to blocks table
  • Block_hash - primary mapping key to blocks table


Subset of transactions that further process and denormalize data for token transfers

  • Token_address -ERC20 token address
  • From_address - address of the sender
  • To_address - address of the receiver
  • Value -amount of the ERC20 tokens’s sent / id of the token transferred (ERC721)
  • Transaction_hash - hash relation key that maps to ‘transactions’ table
  • Log_index - log index in the transaction receipt - likely maps to transactions table
  • Block_timestamp - relation back to the blocks table time of transfer
  • Block_number - self description relation back to the blocks table
  • Block_hash - self description relation back to the blocks table


  • Address - address of the ERC20 Token
  • Symbol - symbol or ticker of the token
  • Name - token long name
  • Decimals - number of decimals or granularity of the token NUMERIC or FLOAT 64
  • Total_supply - CURRENT - The total token supply. Use safe_cast for casting to NUMERIC or FLOAT64
  • Block_timestamp - block timestamp of this update
  • Block_number - block number of this update
  • Block_hash - block hash of this update


Deeper insight into transaction processing (internal transactions) - actions are logged and can be found in the EMV execution trace (trace). You will not be able to accurately recalculate balances using only transactions and internal transactions - need traces as well.

  • Transaction_hash - relational hash to transactions table/tree - that contains the trace
  • Transaction_index - co-relational key to the transactions table (contains the transaction intenteger/index in the block table)
  • From_address - address of the sender - null when trace_type is genesis (creation) or reward (likely something defined in contracts log)
  • To_address - depends on trace type; trace type =
    • Call - address of the receiver
    • Create - null or address of the contract
    • Suicide - beneficiary address
    • Reward - miner address
    • Genesis - shareholder address
    • Daofork - WithdrawDAO address
  • Value - value transferred in wei
  • Input - Message Call and the Data Sent
  • Output - Output of the message call, Trace-type=Create: bytecode of contract
  • Trace_type - Required - Call, Create, Suicide, Reward, Genesis, Daofork
  • Call_type - (if trace_type is Call), - possibilities Callcode, Delegatecall, staticcall - * transfer log of one account to another
  • Reward_type - can be block or uncle - UNKNOWN
  • Gas - gas provided with the message call
  • Gas_used - gas used by the message call or transaction in wei
  • Subtraces - number of subtraces
  • Trace_address - comma separated list of trace addresses in the call tree/graph forest
  • Example: [0, 2, 1] is the parent of [0, 2, 1, 0]
  • Error - if call failed - error message
  • Status - either (1) Success or (0) failure
  • Block_timestamp - block timestamp of this update
  • Block_number - block number of this update
  • Block_hash - block hash of this update
  • Trace_id - unique string that identifies the traces.
  • Transaction scoped traces: {trace_type}{transaction_hash}
  • Block-scoped traces: {trace_type}{block_number}


Blocks in the chain contain zero or more transactions. Contains source, to addresses and amount of ethereum transferred, and array of input bites. Contains set of all transactions in all blocks.

  • Hash - unique hash for the triangulation (similar to block)
  • Nonce - unique random number per transaction block for signature to create acceptable hash form (similar to block)
  • Transaction_index - index of the transaction in the block
  • From_address - address of the sender
  • To_address - address of the receiver - null when contract is creation transaction
  • Value - value transferred in wei
  • Gas - gas provided by the sender
  • Gas_price - gas price
  • Input - data set along with the transaction
  • Receipt_cumulative_gas_used - total amount of gas used (by all transactions in the block) when this transaction was executed in the block
  • Receipt_gas_used - amount of gas used by this transaction alone
  • Receipt_contract_address -Contract Address if “contract creation” transaction else Null
  • Receipt_root - 32 bytes of the post-transaction stateroot (pre Byzantium)
  • Need to research more here, likely obsolete / denigrated
  • Receipt_status - (1) transaction success and posted to the blockchain (0) failure
  • Post Byzantium - need to study Byzantium
  • Block_timestamp - block timestamp of this update
  • Block_number - block number of this update
  • Block_hash - block hash of this update
  • Max_fee_per_gas - total fee of base and priority fees - (base + gas?)
  • Max_priority_fee_per_gas - max priority incentive fee to include in transaction
  • Transaction_type - type of transaction
  • Receipt_effective_gas_price - actual gas deducted from sender's account - replacement of “gas_price” after EIP-1559