Query On-Chain Data

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

How to Query the Blockchain

Add a Query Module to your notebook by selecting "Add SQL" from the lefthand menu.
From the database selector in the top right of the new Query module, select the crypto_ethereum database.
Add your SQL query to the Query module and press shift + enter to run on the ethereum blockchain.
Save the results of your query to a rosecode using logic: rosecode = SQL 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:
```
crypto.eth.chain.eth.burned.eth.map.raw.bigquery:ts(date, eth_burned)
```
```
crypto.eth.chain.eth.burned.eth.d.bq = crypto.eth.chain.eth.burned.eth.map.raw.bigquery:ts(date, eth_burned)
```

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:

crypto_ethereum.blocks

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)

crypto_ethereum.contracts

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

crypto_ethereum.logs

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

crypto_ethereum.token_transfers

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

crypto_ethereum.tokens

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

crypto_ethereum.traces

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}

crypto_ethereum.transactions

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