OurNetwork Learn Episode 2: Web3 Data Landscape On Ethereum

Introduction

In this section, the speaker introduces the lecture and explains that it will be mostly focused on web3. They also mention that there will be pause points for questions throughout the lecture.

Lecture Overview

• The lecture will cover the web3 raw data landscape.

• The speaker will break down all the data from an Ethereum transaction and explain how it is placed into SQL tables.

• A quick overview of the Dune interface will be provided.

• Basic SQL queries on Rabbit Holes Quest data will be demonstrated.

Web3 Raw Data Landscape

This section covers what the web3 raw data landscape is and provides an introduction to blockchain clients.

Blockchain Clients

• Ethereum runs through clients like Geth or Go Ethereum, which are software that can be downloaded and run on a computer.

• Running a client allows access to the blockchain at its lowest level.

• Nodes endpoints as a service providers allow querying of their nodes' endpoints without running a client locally.

Node Endpoints as a Service Providers

• Services or companies run their own set of nodes with exposed endpoints that allow querying those endpoints.

• Alchemy and Infura are two examples of node endpoint as a service providers.

• Metamask wallet uses node endpoint as a service provider to connect to websites or dapps.

Querying Data on Blockchain

This section covers how to query data on blockchain using different methods.

Running Client Locally

• Running a client locally is the highest effort way of connecting to the blockchain and accessing data on it.

• It requires syncing with everyone else who's running a client to get chain data.

Using Node Endpoints as Service Providers

• Using node endpoints as service providers is less effort than running a client locally but still requires some effort.

• It allows querying of nodes' endpoints without running a client locally.

Connecting Wallet to Websites or Dapps

• Connecting a wallet to websites or dapps requires using node endpoint as a service provider.

• Metamask wallet uses node endpoint as a service provider to connect to websites or dapps.

Understanding APIs and Data Mappers

This section explains the difference between APIs and data mappers, and how they are used to access blockchain data.

APIs vs. Data Mappers

• An API allows you to get one piece of data at a time from the blockchain, such as your Ethereum wallet balance or the current block number.

• A data mapper, like Dune or The Graph, takes all of the raw transaction data from the blockchain and formats it into tables that can be queried with SQL or GraphQL.

• Raw data is accessed at three levels: transaction level, contract level, and token level.

Transferring ERC20 Tokens

This section covers transferring ERC20 tokens on the Ethereum blockchain.

Understanding ERC20 Tokens

• ERC20 tokens are similar to US dollars on the Ethereum blockchain.

• When transferring an ERC20 token, you have to sign a transfer function specifying who is receiving it and how much is being sent.

• After signing a transaction, it is sent to the blockchain where it creates call data and sub interactions.

Analyzing Transactions

• Transactions create call data that includes information about who sent the transaction (from parameter), which contract was interacted with (to column), whether it was successful (success state), value transferred in ETH (value column), gas used (gas columns), call data for functions called (call data column), and a unique hash for each action done on the blockchain (transaction hash).

Introduction to Etherscan

In this section, the speaker introduces Etherscan as a tool for searching everything that's happening on the Ethereum blockchain.

Using Etherscan

• To search for USDC, go to Etherscan and search for USDC.

• Clicking on a random transfer shows the sender and receiver addresses, as well as the amount transferred.

• The contract that was called is also shown, along with the function that was called.

Event Logs

In this section, the speaker explains how event logs work in Ethereum.

Emitting Events

• Inside of the transfer function, an emit transfer event is called which emits who the sender was, who the recipient was and what amount was transferred.

• Ethereum has logs which are like a notepad of every single action that's happened.

• Storing all these events can be expensive so instead of filtering through all these event calls, you can look up specific events using their topic identifier.

Understanding Event Logs

• The logs table stores information such as contract address where an event was emitted from and topics two three four and data are like other parameters that were emitted in this event log.

• These tables can all be linked by transaction hash typically.

Introduction to Traces

In this section, the speaker introduces the concept of traces and explains how they work in Solidity. They also provide an example of how multiple contracts can be called from one contract.

Traces and Internal Transactions

• Traces are used to analyze transfers of ETH between parties.

• When a contract calls another contract, it shows up as an internal transaction in the trace.

• USDC is implemented as a set of contracts instead of a single contract structure, which is why there is a delegate call showing up in the trace.

• The reason for using traces is to look for transfers of ETH between parties.

Understanding Hashes and Contract Addresses

In this section, the speaker explains what hashes are and how they are used to store data efficiently. They also explain how hashes relate to blockchains and transaction root hashes.

Hashes and Merkle Root

• Hashes are an efficient way of storing data.

• All transactions in a block get hashed together until you get to the top, which is called a merkle root hash.

• The block hash is somewhat based off of the transaction root hash.

• This method keeps the blockchain small and easy to run on computers.

Introduction to Ethereum Transactions and SQL

In this section, the speaker introduces the hashing algorithm used by Ethereum called SHA-256. They also provide an overview of SQL and how it is used to query data from the Ethereum transactions table.

Understanding SQL

• The two most important words in SQL are "select" and "from".

• "From" specifies which table you want to query, while "select" specifies which columns you want to retrieve.

• Adding a limit clause can help speed up queries by limiting the number of rows returned.

Querying Ethereum Transactions

• The speaker demonstrates how to use SQL to query the Ethereum transactions table.

• Each row in the table represents a single transaction, with its own unique hash.

• By querying this table, users can see details about each transaction such as when it occurred and what was transferred.

Using Dune Analytics

• The Dune Analytics platform provides a user-friendly interface for querying Ethereum data.

• Users can search for specific smart contract events or function calls using the left-hand side of the interface.

• The right-hand side displays the results of your query in tabular form.

Conclusion

• Overall, understanding how to use SQL and Dune Analytics is essential for anyone looking to analyze data on the Ethereum blockchain.

Querying Transactions by Hash

In this section, the speaker demonstrates how to query transactions by hash using SQL.

Using the WHERE Function to Select Specific Rows

• To select specific rows from a table, use the WHERE function.

• Copy and paste the transaction hash into the WHERE function to select only rows where the hash is equal to that transaction.

Handling Data Formats in SQL

• When copying over data formats like bytes, remember to change "0x" to "/x" in SQL.

• If there is a bug or error with data types, you may need to cast it instead.

• Different data types include date, integer, bytes, boolean, and string.

Querying Ethereum Traces

In this section, the speaker demonstrates how to query Ethereum traces using SQL.

Renaming Variables for Multiple Tables

• Use "tr" as a variable name for ethereum traces and rename columns accordingly.

• This becomes important when working with multiple tables.

Analyzing Sub Traces

• Sub traces are calls made to another contract within a transaction.

• While sub traces may not provide immediate value on their own, they can be useful in analyzing overall transaction behavior.

Analyzing Event Logs

In this section, the speaker demonstrates how to analyze event logs using SQL.

Understanding Event Logs

• Event logs are emitted by smart contracts during transactions.

• They contain information about what happened during that transaction.

Decoding Function Signatures

• Function signatures can be decoded from event logs using SQL.

• This allows you to see details such as token transfers.

Understanding Dune's Numbering System

In this section, the speaker explains that in Dune, the numbers are shifted by one. Topic two is actually topic one.

Shifting Numbers in Dune

• The numbering system in Dune is shifted by one.

• Topic two is actually topic one.

Converting Hexadecimal to Numeric Form

In this section, the speaker demonstrates how to convert hexadecimal data to numeric form using a built-in function called byte2numeric.

Converting Data from Bytes to Numbers

• To convert data from bytes to numbers, use the built-in function byte2numeric.

• Apply the function to the data column.

• Dividing by 10^6 pads decimals instead of storing them on chain.

• To remove padded zeros and get back to original value, divide by 10^6 or write it in scientific notation as 1e6.

• When applying a function to a column, it messes with the column name. Rename it accordingly.

Using Rabbit Holes Dataset for Basic Queries

In this section, the speaker introduces Rabbit Holes dataset and explains how it can be used for basic queries on-chain.

Introduction to Rabbit Holes Dataset

• Rabbit Holes dataset offers rewards for completing certain tasks on-chain.

• Tasks run in intervals of one week and offer rewards such as AVE tokens for depositing into AVE bank.

Introduction to the Data Set

In this section, the speaker introduces a data set and explains how they will be searching for specific events within it.

Searching for Specific Events

• The speaker uses a data set to search for specific events.

• They explain that instead of starting with the contract and finding the event, they will go through the data set and search for the event.

• The speaker searches for an "ave event deposit" in Polygon and finds a lending pool contract with a deposit event emitted.

• They select all from the table and limit it to 10 to make it faster.

• The data is already nicely parsed, saving time.

Analyzing Transactions

In this section, the speaker analyzes transactions related to deposits.

Analyzing Deposits

• The speaker selects one transaction related to deposits worth roughly $5,500.

• They show how contracts can call other contracts during transactions.

• The speaker looks for purely deposit-related transactions by searching through contracts on EtherScan or PolygonScan.

• They find that deposit events emit reserve (the token address), user (who is doing the deposit), amount (being deposited), and referral.

Understanding the Transaction Table

In this section, the speaker explains how to check user and amount in a transaction table.

Checking User and Amount in a Transaction Table

• The speaker checks the user and amount in a transaction table by looking at a specific row.

• If there is an error, the speaker realizes they may have copied the wrong information.

• The speaker acknowledges that viewers are seeing what it looks like to go through this process live.

• The correct row is identified by the speaker.

• The speaker confirms that they have found the correct row.

Finding Users Who Fulfilled Quest

In this section, the speaker explains how to find users who fulfilled a quest using data from Rabbit Hole.

Finding Users Who Fulfilled Quest

• The speaker wants to find users who fulfilled a quest and uses data from Rabbit Hole to do so.

• A list of addresses is shown as part of the data from Rabbit Hole.

• By running a query on this list of addresses, only results for depositors into Ave v2 on Polygon will be shown.

• The limit can be removed from the query to make it run faster.

• Queries will timeout after 30 minutes, so it's important to ensure queries run quickly enough within that time frame.

Sorting Results by Amount Deposited

In this section, the speaker explains how to sort results by amount deposited.

Sorting Results by Amount Deposited

• The speaker wants to sort results by the amount deposited.

• If descending is not added, the table will be sorted in ascending order.

• The limit does not help with making queries run faster.

• It's important to filter results before ordering them to make queries run faster.

Filtering Results by Time

In this section, the speaker explains how to filter results by time.

Filtering Results by Time

• The speaker wants to filter results by time and shows how to do so using a specific column in the table.

• By filtering for deposits within the last month, the query runs much faster than if it had checked all deposits.

Understanding Token Decimals

In this section, the speaker explains how to divide by decimals to get more sensible numbers when working with tokens. They also discuss the complications that arise when dealing with different token addresses and their respective decimal formats.

Dividing by Decimals

• To get more sensible numbers when working with tokens, you can divide by decimals.

• The speaker demonstrates an example where dividing by decimals results in a faster calculation time.

• However, dividing by decimals can be complicated because each token address has its own decimal format.

• Some tokens have 18 decimals, while others have zero or six.

• Extra work is required to figure out the correct decimals to divide by.

Querying Across Different Tokens

In this section, the speaker emphasizes the importance of understanding how different tokens connect and interact with each other. They explain that having a good understanding of three tables will allow you to query across much more complex queries.

Understanding How Tokens Connect

• Many people only query one specific token (e.g., Aave), but it's important to understand how different tokens connect and interact with each other.

• Understanding three tables (deposits, transfers, and events) will allow you to query across much more complex queries.

Accessing Web3 Data Providers

In this section, the speaker discusses accessing web3 data providers and explains that Dune Analytics only supports querying in its window.

Accessing Web3 Data Providers

• Dune Analytics only supports querying in its window.

• Other web3 data providers may require using bigquery or ethereum clients if you want to interact with the blockchain directly.

• If you're just analyzing data, however, you wouldn't need either of these.

Interacting with the Blockchain

In this section, the speaker explains that if you want to interact with the blockchain (e.g., send a transaction), you would need to use one of the web3 data providers.

Interacting with the Blockchain

• If you want to interact with the blockchain (e.g., send a transaction), you would need to use one of the web3 data providers.

• Dune Analytics only supports querying in its window and doesn't allow for direct interaction with the blockchain.

• Other web3 data providers may require using bigquery or ethereum clients if you want to interact with the blockchain directly.

Flashbots and Mempool Analysis

In this section, the speaker discusses the need for a client or endpoint as a service to run flashbots or mempool analysis. They recommend using Block Native for this purpose.

Using Block Native for Flashbots and Mempool Analysis

• To run flashbots or mempool analysis, you need to use a client or an endpoint as a service.

• Block Native is a great option for this purpose.

Analyzing Finalized Transactions with Dune

In this section, the speaker explains that there is no need to use anything other than Dune for analyzing finalized transactions.

Analyzing Finalized Transactions with Dune

• There is no need to use anything other than Dune for analyzing finalized transactions.

Converting Byte Code into String

In this section, the speaker walks through how to convert byte code into string format.

Converting Topic 2 into Hexadecimal String

• To convert topic 2 into string format, it needs to be encoded as hex.

• The resulting string will have extra zeros that need to be removed.

• Addresses are only 40 characters long, so any extra characters should be removed.

• The resulting string can then be converted back into bytes format by adding the byte operator at the front and casting it at the end.

• The final result should be renamed as "from address".

Importance of Fundamentals in Ethereum Development

In this section, the speaker emphasizes the importance of understanding fundamental concepts in Ethereum development.

Importance of Understanding Fundamentals

• Knowing fundamental concepts in Ethereum development will help significantly over time.

• It is recommended to read through additional resources linked in the lecture slides.

• The lecture slides are available in the syllabus, and will be updated as the course progresses.