Leveraging Ethereum Data with SQL Agents and AI

Introduction

In the ever-evolving world of blockchain technology, Ethereum has carved out a unique space not just as a platform for cryptocurrency but as a robust development ecosystem. With its versatile smart contracts and decentralized applications, Ethereum offers a wealth of data that can be harnessed for innovative applications.

Imagine being able to query real-time Ethereum transaction data with the ease of a conversation. Whether you’re an investor looking to track your assets, a developer analyzing blockchain interactions, or a business monitoring decentralized finance (DeFi) trends, the ability to interact with blockchain data in a natural, intuitive way is becoming increasingly valuable.

In this post, we’ll explore how to combine the power of Ethereum’s blockchain data with modern AI and SQL tools to create an interactive chatbot. We’ll walk through the steps to query Ethereum data, store it in a SQLite database, and set up an AI-powered SQL agent that can answer complex questions about blockchain transactions – all enhanced with data visualization for a richer experience.

By the end of this tutorial, you’ll have the foundation to build your own transactional chatbot, capable of providing insights into the Ethereum blockchain at your fingertips. Whether you’re a seasoned developer or just diving into the world of blockchain and AI, this guide will equip you with the tools and knowledge to create something truly powerful.

Understanding the Ethereum Blockchain

Ethereum is more than just a cryptocurrency platform; it’s a decentralized computing environment that powers a wide range of applications, from financial services to gaming. At the heart of Ethereum is its blockchain – a distributed ledger that records every transaction made with Ether (ETH), the platform’s native cryptocurrency. Unlike Bitcoin, which primarily focuses on digital currency, Ethereum is designed to be a flexible platform for developers to create decentralized applications (dApps) using smart contracts.

Smart contracts are self-executing agreements where the terms are written directly into code. They automatically enforce and execute the contract’s terms, eliminating the need for intermediaries. This has led to Ethereum becoming a go-to platform for developers looking to build applications that require transparency, security, and decentralization.

One of the most compelling aspects of Ethereum is the wealth of data it generates. Every transaction, contract interaction, and token transfer is recorded on the blockchain, making it an invaluable resource for developers, analysts, and businesses. However, accessing and analyzing this data can be complex, requiring knowledge of blockchain technology, APIs, and data management.

In this tutorial, we’ll demystify the process of working with Ethereum data. We’ll show you how to query the Ethereum blockchain for transaction data, account balances, and other key metrics using the Etherscan API. This data will then be stored in a local SQLite database, making it easier to manage and query. By the end of this section, you’ll have a solid understanding of how to retrieve and organize Ethereum data, setting the stage for building more advanced applications.

Setting Up the Environment

Before diving into querying Ethereum data and building our AI-powered SQL agent, we need to set up the necessary environment. This involves installing the required tools and libraries, setting up an API key, and preparing a SQLite database where we will store our Ethereum data.

Prerequisites

To follow along with this tutorial, you’ll need the following:

• Python 3.8+: Ensure that you have Python installed. You can check this by running python --version in your terminal.
• SQLite: SQLite is a lightweight, self-contained database engine. It comes pre-installed with Python, so no additional installation is required.
• Required Python Libraries: We’ll be using several Python libraries, including requests for API calls, sqlite3 for database management, and langchain for creating our SQL agent.

You can install the required libraries using pip:

pip install requests langchain langchain_community

Setting Up the Etherscan API Key

Etherscan is a popular block explorer for Ethereum, and it provides an API to access various data points on the Ethereum blockchain. To use the Etherscan API, you’ll need to sign up for an API key. Here’s how you can get started:

1. Sign Up: Go to Etherscan and sign up for a free account.
2. Generate API Key: Once logged in, navigate to the API Keys section and generate a new API key.

After obtaining the API key, you can set it up in your script like this:

api_key = 'YOUR_ETHERSCAN_API_KEY'

Make sure to replace 'YOUR_ETHERSCAN_API_KEY' with the actual key you received from Etherscan.

Querying Ethereum Data

With the API key ready, we can now start querying Ethereum data. We’ll fetch the following information from the Ethereum blockchain:

1. Current Ether Price: The latest price of Ether in USD.
2. Ether Supply: The total circulating supply of Ether.
3. Gas Prices: The current gas prices in Gwei.
4. Account Balances: The Ether balance of specific Ethereum addresses.
5. Transaction History: Up to 10,000 recent transactions for each Ethereum address.

Here’s how you can query this data:

import requests

api_key = 'YOUR_ETHERSCAN_API_KEY'

# Example account addresses
accounts = [
    {'name': 'beaver_build', 'address': '0x95222290DD7278Aa3Ddd389Cc1E1d165CC4BAfe5'},
    {'name': 'stake.com 11', 'address': '0x787B8840100d9BaAdD7463f4a73b5BA73B00C6cA'}
]

def query_api(accounts):
    stats = {}

    # Current Ether price
    ether_price_api = f"https://api.etherscan.io/api?module=stats&action=ethprice&apikey={api_key}"
    response_price = requests.get(ether_price_api)
    stats["ether_price"] = response_price.json()['result']

    # Ether supply
    ether_supply_api = f"https://api.etherscan.io/api?module=stats&action=ethsupply&apikey={api_key}"
    response_supply = requests.get(ether_supply_api)
    stats["ether_supply"] = response_supply.json()['result']

    # Gas prices
    gas_prices_api = f"https://api.etherscan.io/api?module=gastracker&action=gasoracle&apikey={api_key}"
    response_gas = requests.get(gas_prices_api)
    stats["gas_prices"] = response_gas.json()['result']

    # Account balances and transaction history
    accounts_updated = []
    for account in accounts:
        balance_api = f"https://api.etherscan.io/api?module=account&action=balance&address={account['address']}&tag=latest&apikey={api_key}"
        response_balance = requests.get(balance_api)
        balance = int(response_balance.json()['result']) / 1e18  # Convert from Wei to Ether
        accounts_updated.append({'name': account['name'], 'address': account['address'], 'balance': balance})

    return stats, accounts_updated

stats, accounts_updated = query_api(accounts)

This script does the following:

• Fetches the current Ether price and supply.
• Retrieves the current gas prices.
• Queries the balance for a list of Ethereum addresses.

Storing Data in SQLite

With the Ethereum data retrieved, we can store it in a SQLite database. SQLite is ideal for this purpose because it’s lightweight and easy to set up. Here’s how to create the database and store the data:

import sqlite3

dbname = "ethereum_transactions.db"

def create_sqlite_database(dbname, stats, accounts_updated):
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()

    # Create tables
    cursor.execute("""CREATE TABLE IF NOT EXISTS general_info (
                      timestamp DATETIME,
                      ether_price_usd REAL,
                      ether_supply INTEGER,
                      gas_price_base REAL,
                      gas_price_average_priority REAL)""")
    
    cursor.execute("""CREATE TABLE IF NOT EXISTS accounts (
                      account_name TEXT,
                      account_address TEXT,
                      current_balance REAL)""")
    
    # Insert data into tables
    cursor.execute(f"""INSERT INTO general_info VALUES (
                      datetime('now'), 
                      {stats['ether_price']['ethusd']}, 
                      {stats['ether_supply']}, 
                      {float(stats['gas_prices']['suggestBaseFee']) / 1e9}, 
                      {(float(stats['gas_prices']['SafeGasPrice']) + 
                        float(stats['gas_prices']['ProposeGasPrice']) + 
                        float(stats['gas_prices']['FastGasPrice'])) / 3 / 1e9})""")
    
    for account in accounts_updated:
        cursor.execute(f"""INSERT INTO accounts VALUES (
                          '{account['name']}', 
                          '{account['address']}', 
                          {account['balance']})""")

    conn.commit()
    conn.close()

create_sqlite_database(dbname, stats, accounts_updated)

This code:

• Connects to a SQLite database (or creates it if it doesn’t exist).
• Creates two tables: one for general Ethereum information (general_info) and another for account balances (accounts).
• Inserts the fetched Ethereum data into these tables.

Once you have the data stored in the SQLite database, it becomes much easier to query and manipulate, setting the stage for creating an interactive SQL agent in the next section.

Creating an AI-Powered SQL Agent

With our Ethereum data securely stored in a SQLite database, the next step is to make this data interactive. We’ll achieve this by creating an AI-powered SQL agent that can query the database and answer questions about the Ethereum blockchain data. This agent will be powered by an AWS Bedrock model and integrated using the langchain library.

Introduction to SQL Agents

A SQL agent is a tool that bridges the gap between natural language and structured database queries. It allows users to ask questions in plain English, and the agent translates these queries into SQL commands that retrieve the relevant data from the database. By leveraging AI models, the agent can understand the context and nuances of your queries, making it easier to interact with complex datasets.

In our case, we’ll use the langchain library to set up the SQL agent, which will be connected to our SQLite database containing the Ethereum transaction data. This agent will be able to answer questions like “What is the current Ether supply?” or “What are the top 3 accounts by transaction volume?”

Setting Up the SQL Agent

First, let’s start by creating the SQL agent using the langchain library. We’ll configure it to connect to our SQLite database and use an AI model from AWS Bedrock to process the queries.

from langchain_community.utilities import SQLDatabase
from langchain.agents import create_sql_agent
from langchain_aws import ChatBedrock

# Connect to the SQLite database
dbname = "ethereum_transactions.db"
db = SQLDatabase.from_uri(f"sqlite:///{dbname}")

# Define the AI model from AWS Bedrock
region_name = 'us-west-2'
model_id = "anthropic.claude-3-opus-20240229-v1:0"
model_kwargs = {
    "temperature": 0.0,
}

llm = ChatBedrock(model_id=model_id, region_name=region_name, model_kwargs=model_kwargs)

# Create the SQL agent
sql_agent = create_sql_agent(
    llm=llm,
    db=db,
    verbose=True,
    handle_parsing_errors=True,
)

# Example query: What is the current Ether supply?
result = sql_agent.run("What is the current Ether supply?")
print(result)

This code does the following:

1. Connects to the SQLite Database: We use SQLDatabase.from_uri() to connect to our SQLite database containing the Ethereum data.
2. Configures the AI Model: The ChatBedrock class is used to integrate an AI model from AWS Bedrock, which will process and interpret natural language queries.
3. Creates the SQL Agent: The create_sql_agent() function initializes the SQL agent, linking it to the AI model and the database. It also handles parsing errors to ensure smooth operation.
4. Example Query: Finally, we run a query asking for the current Ether supply, which the agent processes and retrieves from the database.

Query Examples

Now that the SQL agent is set up, you can start asking it questions about the Ethereum data. Here are a few examples of what you can do:

1. Check the Current Gas Prices:

result = sql_agent.run("What is the current gas price in Ether?")
print(result)

2. Retrieve an Account Balance:

result = sql_agent.run("What is the current balance of account 0x95222290DD7278Aa3Ddd389Cc1E1d165CC4BAfe5? Please convert to US dollars.")
print(result)

3. Find the Top 3 Recipient Accounts:

result = sql_agent.run("What are the top 3 accounts where the account 0x95222290dd7278aa3ddd389cc1e1d165cc4bafe5 has sent the most amount of Ether?")
print(result)

These examples show how the SQL agent can translate natural language queries into SQL commands and return meaningful answers from the Ethereum dataset. The AI model helps interpret the queries, making it easier to interact with the data without needing to write SQL manually.

Handling Errors and Schema Mismatches

One of the strengths of using an AI-powered SQL agent is its ability to handle errors and adapt to schema changes. If the database schema doesn’t match the query (for example, if a column name has changed), the agent can often detect the issue and suggest a corrected query. This feature is particularly useful in dynamic environments where the data structure may evolve over time.

By integrating AI with SQL, we’ve created a powerful tool that simplifies the process of querying and analyzing Ethereum blockchain data. In the next section, we’ll enhance this setup by adding data visualization capabilities, making the data even more accessible and actionable.

Visualizing Blockchain Data

While querying data with a SQL agent is powerful, visualizing that data can make the insights even more impactful. Visual representations of data help in understanding patterns, trends, and outliers at a glance, which is particularly useful when working with complex datasets like blockchain transactions. In this section, we’ll enhance our SQL agent by adding data visualization capabilities using the Plotly library.

Importance of Data Visualization

Data visualization is an essential part of data analysis. It allows us to:

• Identify Trends: Quickly see how metrics like Ether price or gas fees change over time.
• Compare Data: Easily compare balances across multiple accounts or transaction volumes between different periods.
• Spot Anomalies: Detect unusual activity in transactions or sudden spikes in gas prices.

By integrating visualization into our SQL agent, we can generate charts and graphs directly from the Ethereum data stored in our SQLite database, making the results more intuitive and easier to interpret.

Adding Plotly to the SQL Agent

To enable our SQL agent to generate visualizations, we’ll use Plotly, a popular Python library for creating interactive graphs. We’ll start by defining a function that takes the query results and creates a bar chart.

import plotly.express as px
import plotly.io as pio
import json
from langchain.agents import initialize_agent
from langchain.tools import Tool, StructuredTool
from langchain.pydantic_v1 import BaseModel, Field

pio.renderers.default = 'iframe'  # This setting ensures that the charts render correctly in a Jupyter notebook

# Define a function to plot results using Plotly
def plot_result_plotly(plottitle: str, xlabel: str, ylabel: str, dict_of_values: str):
    dict_of_values = json.loads(dict_of_values.strip().replace("'", '"'))
    items = list(dict_of_values.keys())
    values = list(dict_of_values.values())
    fig = px.bar(x=items, y=values, title=plottitle)
    fig.update_yaxes(title=ylabel)
    fig.update_xaxes(title=xlabel)
    fig.show()
    return "Success"

# Define the input schema for the plotting function
class PlottingInput(BaseModel):
    plottitle: str = Field(description="Plot title")
    xlabel: str = Field(description="X-axis label")
    ylabel: str = Field(description="Y-axis label")
    dict_of_values: str = Field(description="JSON object with keys as x-axis values and corresponding y-axis values")

# Create a tool for the Plotly plotting function
plotting_tool = StructuredTool.from_function(
    func=plot_result_plotly,
    name="Plotting Results",
    description="Create bar plot of the results using Plotly library",
    handle_tool_error=True,
    args_schema=PlottingInput,
    return_direct=True,
)

# Initialize the agent with the plotting tool
sql_plot_agent = initialize_agent(
    tools=[
        Tool(
            name="databaseAgent",
            func=sql_agent.run,
            description="Agent to query SQLite database",
        ),
        plotting_tool,
    ],
    llm=llm,
    agent=AgentType.STRUCTURED_CHAT_ZERO_SHOT_REACT_DESCRIPTION,
    verbose=True,
    agent_executor_kwargs={'handle_parsing_errors': True},
    handle_parsing_errors=True
)

This code does the following:

1. Sets Up Plotly: Configures Plotly to render charts in a Jupyter notebook environment.
2. Defines a Plotting Function: plot_result_plotly() takes the title, axis labels, and data values to create a bar chart.
3. Creates a Tool for Plotting: Uses StructuredTool to wrap the plotting function, making it callable by the SQL agent.
4. Enhances the SQL Agent: Integrates the plotting tool into the SQL agent so that it can generate charts based on query results.

Example Visualizations

Now that our SQL agent can generate visualizations, let’s see some examples of how this works in practice.

1. Plot the Top 3 Recipient Accounts:

sql_plot_agent.run(
    """Based on the SQLite database, please find out the top 3 accounts 
    where the account 0x95222290dd7278aa3ddd389cc1e1d165cc4bafe5 
    has sent the most amount of Ether. Please plot the results."""
)

This command queries the database for the top 3 recipient accounts of a specific Ethereum address and then visualizes the result in a bar chart. The chart will show the addresses on the x-axis and the total Ether received on the y-axis.

2. Visualize Monthly Ether Transactions:

sql_plot_agent.run(
    """Based on the SQLite database, please find out how much Ether 
    the account 0x95222290dd7278aa3ddd389cc1e1d165cc4bafe5 sent in 
    August 2024, July 2024, and June 2024. Please plot the results."""
)

This example queries the database for the total Ether sent by an account in each of the specified months and then generates a bar chart comparing the monthly totals.

By integrating Plotly with our SQL agent, we can transform raw data into informative visualizations, making it easier to interpret and act upon the insights. Whether you’re comparing transaction volumes, monitoring account balances, or analyzing gas prices, these visual tools bring your Ethereum data to life.

In the final section, we’ll explore potential use cases for this AI-powered SQL agent and discuss how you can extend this setup to meet your specific needs.

Use Cases and Applications

Now that we’ve set up an AI-powered SQL agent capable of querying and visualizing Ethereum blockchain data, let’s explore some potential use cases and how you can extend this setup to meet your specific needs. This flexible and powerful tool can be applied in various scenarios, from financial analysis to educational tools and beyond.

Potential Use Cases

Investor Dashboards:

• Overview: Investors can use this setup to monitor their Ethereum accounts, track transaction histories, and visualize Ether price trends. By integrating the SQL agent with a front-end dashboard, users can get real-time insights into their portfolios.
• Example Query: “What was the total Ether sent from my wallet in the last month? Plot the weekly breakdown.”

Blockchain Analytics for Developers:

• Overview: Developers working on decentralized applications (dApps) can benefit from this tool by analyzing transaction patterns, gas price fluctuations, and smart contract interactions. This can help optimize contract performance and cost efficiency.
• Example Query: “What are the peak gas price times for transactions in the last week? Display this in a line chart.”

Educational Tools:

• Overview: Educators and students can use this SQL agent to learn about blockchain technology by querying real-world data. Visualizing transaction flows and Ether supply changes can make complex concepts more understandable.
• Example Query: “How has the Ether supply changed over the past year? Show the monthly changes in a bar chart.”

Compliance and Auditing:

• Overview: Companies and auditors can utilize this setup to ensure compliance with financial regulations by tracking large transactions, monitoring account balances, and generating reports on blockchain activities.
• Example Query: “Identify any transactions over 100 Ether in the past quarter. Provide a detailed list.”

Research and Development:

• Overview: Researchers studying blockchain technology can analyze large datasets of transactions, smart contract deployments, and token transfers. This can help in identifying trends, potential vulnerabilities, or market behaviors.
• Example Query: “What are the top 5 accounts by transaction volume in the last 6 months? Plot the distribution.”

Conclusion

In this blog post, we’ve walked through the process of creating a powerful tool for interacting with Ethereum blockchain data. From querying real-time data and storing it in a SQLite database to setting up an AI-powered SQL agent and visualizing the results, you’ve built a versatile system that can be adapted to a wide range of applications.

Whether you’re an investor, developer, educator, or researcher, this setup offers a flexible foundation for exploring and analyzing blockchain data. The possibilities are vast, and with a few tweaks, you can extend this tool to meet your specific needs or even build entirely new applications.

We encourage you to experiment with the code, explore different use cases, and think about how this setup could be applied in your own projects. Blockchain technology is rapidly evolving, and having the tools to interact with and understand this data is key to staying ahead in this dynamic field.