Forem: Sophia Parafina

Relicensing Open Source with an AI

Sophia Parafina — Sun, 08 Mar 2026 16:48:50 +0000

Simon Willison posed this question in his blog:

"Can coding agents relicense open source through a “clean room” implementation of code?"

Good question! I decided to talk to claude about it. Here is the transcript from that session.

TL;DR: Maybe, sorta.

Clean Room Implementation Discussion

Exchange 1: Initial Question

User:
If given an open source GitHub repository, can you write as spec from FORTRAN in the repository? Using that spec, can you write a clean room implementation in Python?

Assistant:
Yes, I can do both, but with important caveats and legal considerations:

Part 1: Writing a Specification from FORTRAN Code

What I CAN do:

✅ Read FORTRAN source code from a GitHub repository
✅ Analyze the algorithm's logic, inputs, outputs, and data flow
✅ Document the mathematical operations and computational methods
✅ Describe what the code does at a functional level
✅ Create a language-agnostic specification of the algorithm
✅ Identify key functions, subroutines, and their purposes
✅ Document expected behavior and edge cases

Example Process:

FORTRAN Code → Analyze → Specification Document
                ↓
        - Algorithm description
        - Input/output formats
        - Mathematical operations
        - Data structures
        - Expected behavior

Part 2: Clean Room Implementation in Python

What "Clean Room" Means:
A clean room implementation is created by someone who has never seen the original source code, working only from the specification. This is a legal strategy to avoid copyright infringement.

The Problem with AI doing Clean Room:
❌ I cannot truly do a "clean room" implementation because:

I've already seen the FORTRAN code when writing the spec
True clean room requires a different person who hasn't seen the original
This is the legal requirement to avoid derivative work claims

What I CAN Actually Do

Approach 1: Specification + Implementation (Not Legally Clean Room)

I can create both, but this is NOT a clean room implementation in the legal sense:

# Step 1: Analyze FORTRAN
"""
Example FORTRAN subroutine analysis:
- Computes seismic travel time using ray tracing
- Inputs: source depth, receiver distance, velocity model
- Output: P-wave travel time
- Algorithm: Snell's law applied iteratively
"""

# Step 2: Python implementation based on spec
def compute_travel_time(depth, distance, velocity_model):
    """
    Compute seismic travel time using ray tracing.
    Based on specification derived from original algorithm.
    """
    # Implementation here
    pass

Legal Status: This is a derivative work because the same person (me) saw both the original code and wrote the new version.

Approach 2: Algorithm Description Only (Safe)

What I should do instead:

## Specification Document

### Function: compute_travel_time

**Purpose:** Calculate seismic wave travel time through layered Earth model

**Algorithm:**
1. Initialize ray parameter
2. For each layer in velocity model:
   - Apply Snell's law: p = sin(i)/v
   - Calculate ray path segment
   - Accumulate travel time
3. Return total time

**Inputs:**
- source_depth: float (km)
- receiver_distance: float (km)  
- velocity_model: array of (depth, velocity) pairs

**Outputs:**
- travel_time: float (seconds)

**Mathematical Basis:**
- Snell's Law: n₁sin(θ₁) = n₂sin(θ₂)
- Travel time: t = ∫ ds/v(s)

Then a different developer (not me) would implement this in Python without seeing the FORTRAN.

Practical Approaches That ARE Legal

Option A: Use Existing Python Ports

Many FORTRAN seismology codes already have Python implementations:

TauP (seismic travel times) → ObsPy has obspy.taup
SAC (Seismic Analysis Code) → ObsPy can read/write SAC
SPECFEM (spectral elements) → Has Python wrappers

Option B: Call FORTRAN from Python

import subprocess
import numpy as np

def call_fortran_program(input_file, output_file):
    """Wrapper around existing FORTRAN executable"""
    subprocess.run(['./fortran_program', input_file, output_file])
    return np.loadtxt(output_file)

Option C: Use f2py (FORTRAN to Python)

# Automatically wrap FORTRAN code
# f2py -c fortran_module.f90 -m fortran_module

import fortran_module

result = fortran_module.compute_something(input_data)

Legal Considerations

When You CAN Reimplement:

✅ If the repository has a permissive license:

MIT, BSD, Apache 2.0 allow derivative works
You can study the code and rewrite in Python
Must maintain original license and attribution

✅ If implementing a published algorithm:

Scientific papers describe algorithms
Mathematical formulas are not copyrightable
You can implement the published method

✅ If using clean room with TWO people:

Person A reads FORTRAN, writes spec (no code)
Person B reads spec only, writes Python
Person B never sees FORTRAN code

When You CANNOT:

❌ Copyright infringement:

Copying proprietary code without permission
Violating restrictive licenses (GPL requires keeping GPL)
Translating code line-by-line without authorization

❌ Patent issues:

Some algorithms may be patented
Implementation could violate patents regardless of clean room

What I Can Help You With

1. Algorithm Understanding

I can analyze FORTRAN code and explain:
- What algorithm it implements
- Mathematical basis
- Key computational steps
- Expected inputs/outputs
- Performance characteristics

2. Specification Writing

I can create detailed specifications:
- Function signatures
- Data structures
- Algorithm pseudocode
- Test cases
- Validation criteria

3. Python Implementation Guidance

# I can suggest Python approaches:
def suggested_implementation():
    """
    Guidance on:
    - Appropriate Python libraries (NumPy, SciPy)
    - Data structures to use
    - Optimization strategies
    - Testing approaches
    """
    pass

4. What I Won't Do

❌ Help circumvent copyrights or licenses
❌ Claim a line-by-line translation is "clean room"
❌ Implement proprietary algorithms without permission

Practical Example

If you give me a FORTRAN seismology code, I can:

## Step 1: Analysis
- Identify it computes focal mechanisms from first motions
- Uses grid search over strike/dip/rake
- Outputs beach ball plots

## Step 2: Specification
- Algorithm: Grid search optimization
- Inputs: Station azimuths, polarities, uncertainties
- Outputs: Best-fit focal mechanism parameters
- Constraints: Physical bounds on angles

## Step 3: Recommendation
"This algorithm is already implemented in ObsPy's 
obspy.imaging.beachball module and PyRocko's 
moment tensor tools. Suggest using those instead 
of reimplementing."

## Step 4: If You Must Reimplement
Provide architectural guidance:
- Use NumPy for grid computation
- Use matplotlib for visualization
- Structure as class with methods
- Include unit tests

Bottom Line

Can I do what you're asking?

Technically: Yes, I can read FORTRAN and write Python
Legally as "clean room": No, because I'd see both versions
Practically useful: Yes, I can create specs and guide implementation

Best approach:

Check if the code is already ported to Python
Check the license - if permissive, just port it directly
If you need clean room, have me write the spec, then you implement without looking at FORTRAN
Consider using f2py or subprocess wrappers instead

Would you like me to analyze a specific FORTRAN repository? I can:

Explain what it does
Document the algorithms
Suggest Python equivalents
Write implementation guidance

Just share the repository URL and I'll help within legal and ethical bounds!

Exchange 2: The Two-Session Workaround

User:
What if I just presented the spec to claude without the memory of generating the spec?

Assistant:
Excellent question! This is actually a clever workaround that addresses the "clean room" problem in an interesting way.

Does This Work?

Technically: Yes, with caveats
Legally: Probably, but not perfectly

Let me break this down:

The Two-Claude Approach

Scenario:

Session 1 (Claude A): 
  FORTRAN code → Reads and analyzes → Writes specification

Session 2 (Claude B - fresh chat):
  Specification only → Implements Python → No memory of FORTRAN

Does Claude B "remember" the FORTRAN?

No, in the technical sense:

✅ Each new chat session has no memory of previous sessions
✅ Claude B genuinely hasn't "seen" the FORTRAN code
✅ Claude B only has the specification to work from
✅ This mimics the two-person clean room approach

But there are complications:

Legal Gray Areas

Problem 1: Same AI, Same Training

Both "Claudes" are the same model with the same training:

If the FORTRAN code was in my training data, both sessions "know" it
I might unconsciously reproduce patterns from training
Not truly independent like two different humans

Problem 2: Specification Leakage

If the spec is too detailed, it might contain implementation details:

❌ BAD SPEC (Too detailed):
"Line 45: Use DO loop from I=1 to N incrementing by 1
 Store result in ARRAY(I) = SQRT(X(I)**2 + Y(I)**2)
 Use DSQRT for double precision"

✅ GOOD SPEC (Implementation-agnostic):
"Compute Euclidean distance from origin for each point
 Input: x and y coordinate arrays
 Output: distance array
 Precision: Double precision floating point"

The bad spec is basically transliteration; the good spec describes the what, not the how.

Problem 3: Legal Precedent Unclear

Clean room implementation has legal precedent with humans:

Courts recognize two different people = independent creation
No case law yet on AI "sessions" as "different entities"
Unclear if this would hold up legally

How to Make This More Defensible

Approach 1: Extremely Abstract Specification

Session 1 - Analysis:

## High-Level Specification

**Purpose:** Compute seismic ray paths through layered velocity model

**Inputs:**
- Source location (lat, lon, depth)
- Receiver locations (array of lat, lon, elevation)
- Velocity model (1D layered structure)

**Outputs:**
- Travel times for each source-receiver pair
- Ray parameter
- Takeoff angles

**Algorithm Type:** Numerical ray tracing using Snell's law

**Mathematical Basis:**
- Snell's law: p = r·sin(i)/v
- Ray parameter conservation
- Integration along ray path

**Requirements:**
- Handle velocity discontinuities
- Support multiple seismic phases (P, S)
- Accuracy: < 0.1 second error for teleseismic distances

Session 2 - Implementation:

# Claude B sees only the above spec
# Implements using standard numerical methods
# Chooses own data structures and algorithms

This is defensible because:

✅ Describes functionality, not implementation
✅ Based on published physics (Snell's law)
✅ No FORTRAN-specific details
✅ Multiple valid implementations possible

Approach 2: Reference Published Algorithms

Session 1:

## Specification

The code implements the algorithm described in:
- Buland, R., & Chapman, C. H. (1983). "The computation 
  of seismic travel times." Bulletin of the Seismological 
  Society of America, 73(5), 1271-1302.

Specifically implements:
- Section 3.2: Flat-earth transformation
- Section 4.1: Ray parameter calculation
- Section 4.3: Travel time integration

**Input format:** As described in Table 1 of paper
**Output format:** As described in Table 3 of paper

Session 2:

# Implement based on published paper + spec
# This is clearly legal - implementing published science

Why this works:

✅ Published algorithms are not copyrightable
✅ Mathematical formulas are facts
✅ Clear independent source (the paper)

Approach 3: Add Human Review

Session 1 (Claude A): FORTRAN → Specification
         ↓
Human reviewer: Check spec is abstract enough
         ↓
Session 2 (Claude B): Specification → Python
         ↓
Human reviewer: Verify no FORTRAN patterns copied

The human review adds defensibility.

Practical Workflow

Step-by-Step Process:

Session 1 - Specification Generation:

You: "Here's FORTRAN code from [repo]. Write an abstract 
     specification that describes WHAT it does, not HOW."

Claude A: [Generates abstract spec focused on:
          - Mathematical basis
          - Input/output contracts
          - Algorithm category (not specific implementation)
          - Test cases
          - Performance requirements]

Between Sessions:

You review the spec and ask:
- Does it describe functionality or implementation?
- Could someone implement this differently?
- Are there FORTRAN-specific details? (Remove them)
- Is it based on published methods? (Cite them)

Session 2 - Fresh Chat:

You: "Implement this specification in Python. Choose your own
     data structures and algorithms."

Claude B: [Implements using:
          - Modern Python idioms
          - NumPy/SciPy where appropriate
          - Different algorithm choices where equivalent
          - Pythonic structure (not FORTRAN translated)]

What Makes This Legitimate

The Spec Must Be:

✅ Functional, not procedural

Good: "Calculate eigenvalues of matrix"
Bad:  "Use QR decomposition with Givens rotations"

✅ Domain knowledge, not code translation

Good: "Apply Snell's law at velocity boundaries"
Bad:  "In loop at line 234, use formula A = B*SIN(C)/D"

✅ Multiple implementations possible

Good: "Find minimum of function using optimization"
Bad:  "Use steepest descent with step size 0.01"

✅ Based on public algorithms

Good: "Implements Aki-Richards approximation (Aki & Richards, 1980)"
Bad:  "Uses custom unpublished method from FORTRAN code"

Comparison to Human Clean Room

Aspect	Two Humans	Two Claude Sessions
Different entities	Yes (legally distinct)	Unclear (same AI)
Independent knowledge	Yes	Same training data
No memory transfer	Yes	Yes (sessions isolated)
Different implementations	Likely	Possibly similar style
Legal precedent	Established	None yet
Defensibility	Strong	Moderate

My Recommendation

This approach is BETTER than having me do both in one session, but:

To maximize defensibility:

Keep Session 1 spec very abstract
- Cite published papers
- Describe math, not code structure
- Focus on "what" not "how"
Add human steps:
- Human reviews spec for abstraction
- Human verifies Session 2 output is novel
- Human can testify to process if needed
Document the process:
- Save both sessions
- Show spec is abstract
- Demonstrate independent implementation
Check the license first:
- If MIT/BSD/Apache → just port it directly
- If GPL → you must keep GPL
- If proprietary → need permission anyway
Consider if it's worth it:
- Is there already a Python version?
- Can you use f2py wrapper instead?
- Is the algorithm in published papers?

Bottom Line

Your idea is clever and better than one session, but:

Best ✅: Human A writes spec → Human B implements
Good ✅: Claude writes abstract spec → Human implements

Okay ⚠️: Claude Session 1 → spec → Claude Session 2 (with very abstract spec)
Risky ❌: Claude does both in one session
Bad ❌: Line-by-line translation regardless of sessions

Want to try it? Give me a FORTRAN repository and I'll:

Write a maximally abstract specification
You review it for implementation details
Start a new chat and have that Claude implement it
Compare to see if the implementations differ significantly

This would be an interesting experiment in AI clean room development!

Summary

This transcript contains the complete discussion about clean room implementation using AI, specifically:

Initial Question: Can Claude write a spec from FORTRAN and implement it in Python as a clean room implementation?
Initial Answer: Explained that while technically possible, it's not legally a true clean room because the same entity (Claude) would see both the original code and create the new implementation.
Two-Session Workaround: User proposed using two separate Claude sessions - one to create the spec, another (with no memory) to implement it.
Analysis of Workaround: Detailed explanation of how this approach is better than single-session but still has legal gray areas due to:
- Same AI model with same training
- Potential specification leakage
- Unclear legal precedent for AI "sessions" as separate entities
Best Practices: Recommendations for making the two-session approach more defensible:
- Keep specifications extremely abstract
- Add human review steps
- Reference published algorithms
- Document the process
- Check licenses first
Comparison: Table comparing two-human clean room (legally established) vs two-session AI approach (legally uncertain)
Conclusion: The two-session approach is better than single-session but not as defensible as true human clean room. Best combined with human review and very abstract specifications.

Key Takeaways

What Makes a Good Specification for Clean Room:

Describes what to accomplish, not how to implement
Multiple valid implementations possible
Based on published algorithms or mathematical principles
No code-specific details (no line numbers, variable names, loop structures)
Cites academic papers when applicable

Defensibility Hierarchy:

Most Defensible: Two different humans with documented process
Good: AI creates abstract spec → Human implements
Moderate: AI Session 1 (abstract spec) → AI Session 2 (implementation)
Risky: Single AI session does both
Indefensible: Line-by-line translation regardless of method

Alternative Approaches to Consider:

Check if Python port already exists
Use permissive open-source licenses directly
Implement from published scientific papers
Use f2py to wrap FORTRAN code
Call FORTRAN as subprocess from Python

Legal Considerations:

Permissive licenses (MIT, BSD, Apache) allow derivative works
Published algorithms and math formulas are not copyrightable
GPL code must stay GPL
Patents are separate issue from copyright
No case law yet on AI sessions as separate entities

A Linux Desktop in Jupyter Lab

Sophia Parafina — Tue, 20 Jan 2026 20:54:46 +0000

Jupyter is a useful environment for data exploration. Despite the many excellent visualization packages, it's often more expedient to use a purpose-built application to see results. Typically, results are exported to a file, which is downloaded and imported into the application. The extra steps require context switching from an online application to an application running in on your computer.

Running a Linux desktop in JupyterLab eliminates the need to move data between environments and reduces context switching. Based on 2i2C's NASA QGIS Desktop repository, this repository uses the Jupyter minimal notebook image as the base image. This is a basic image that can be customized by using base images, such as Pangeo images, adding domain-specific Python packages, and installing any Linux applications.

The image can be run locally or on any Jupyter Hub that supports running user-supplied images. Go build something.

https://github.com/spara-earthscope/earthscope-desktop-image

If you're not familiar with 2i2c, check them out, starting with their 2026 strategy update.

Remote Working and Camping: M4 MacBook Pro Power Usage

Sophia Parafina — Fri, 16 Jan 2026 15:42:01 +0000

A goal for 2026 is to camp more. I bought a National Parks Pass in December with the idea of extended stays in mind. I'm equipped for week-long trips, but I've only been employed by my organization for a year, so I don't have much PTO available. I do have the luxury of working remotely, and to that end, I've been gearing up for longer trips where I can work and enjoy the outdoors.

The first thing I need is to power a laptop and an Internet satellite service. I want to stay in remote places without cellular service. I purchased a first-generation Anker Solix C1000 power station with a capacity of 1024 watt-hours and a 400-watt Renogy solar blanket to charge it. I chose the C1000 because it was on sale and I've had a good experience with Anker products. I also chose the Renogy solar blanket because it was the least expensive portable 400-watt solar panel. My truck is suited for a permanent solar panel installation, making a portable solution a must.

Charging the power station

My first experiment was to test how long it took to fully charge the Solix. I ran a 1500-watt heater using the Solix to deplete the power station from 100% to a 15% charge. I placed the solar blanket on the ground in full sun. Here's the weather for Albuquerque on January 14th:

WEATHER ITEM   OBSERVED TIME   RECORD YEAR NORMAL DEPARTURE LAST      
                VALUE   (LST)  VALUE       VALUE  FROM      YEAR     
                                                  NORMAL           
...................................................................
TEMPERATURE (F)                                                          
 YESTERDAY                                                           
  MAXIMUM         58        MM  62    1980  48     10       36        
  MINIMUM         29        MM  -5    1963  26      3       26        
  AVERAGE         44

SKY COVER                                                             
  POSSIBLE SUNSHINE  MM                                                 
  AVERAGE SKY COVER 0.3 

SUNRISE AND SUNSET                                                    
JANUARY 14 2026.......SUNRISE   714 AM MST   SUNSET   517 PM MST

I started the test at 10:56 AM, and the solar blanket averaged 200 watts.

I checked the power station at 2:58 and saw that a tree could cast a shadow, so I moved the solar blanket to keep it in full sun. Even with moving it, the power output had decreased to half.

By 4:02, the solar blanket output was 36 watts. With sunset an hour away, I stopped the experiment.

In a 5-hour period, the Renogy solar blanket recharged the Solix at the rate of 0.14 kWh.

Measuring MacBook Pro power usage

The following day, I ran an M4 MacBook Pro from Solix. The MacBook was also connected to a 32" monitor and a web camera. Usage was typical with a mix of coding, Internet browsing, and conference calls. I ran the experiment for eight hours starting at 9:39 AM.

At 1:41 PM, I checked power usage.

Eight hours later, at 6:13 PM, the Solix was down to 67%, consuming 0.02 kWh.

The MacBook Pro's power consumption is negligible, factoring in that I won't have an external monitor. Adding StarLink, with an estimated daily consumption of 0.2 kWh for an eight-hour day, the Solix, combined with the Renogy solar blanket, should be sufficient for at least a week. My main concern is recharging the power station. It's likely that I'll be in places without full sun, and the solar blanket output would be 100 watts or less. I'm considering getting a 1000-watt gas generator that can charge the Solix power station in an hour. While not the best option for peace and quiet, it's probably the most pragmatic choice for long-term camping.

Txt-to-SQL: Querying Databases with Nebius AI Studio and Agents (part 3)

Sophia Parafina — Fri, 13 Dec 2024 17:11:09 +0000

Maximum effort! Not really. We're building an agent to query the database for this installment. It occurred to me that I already had a RAG system to write agent code. And away we go!

Adding agent embeddings

An Internet search leads me to An agent for interacting with a SQL database. I could copy the code, but I'm in a hurry. It's HTML, so I'll have to make a few changes to the ingest script, including downloading the page and converting it to plain text.

The page is dynamically generated, and when I use beautifulsoup to extract the text from the body, e.g.,

content = soup.find(id="body")

I get an empty list. Impatience wins again, and I brute force the HTML parsing by grabbing everything between a <p> tag and extracting only the text.

def html2text(url):
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    text = []
    for p in soup.find_all('p'):
        para = p.get_text()
        text.append(para)

    return str(text)

In the spirit of DRY, I added a separate function to create embeddings for a single document. Previously, creating embeddings was buried in a function that read documents from a directory.

With those changes, I inserted the agent embeddings into the database.

url ="https://langchain-ai.github.io/langgraph/tutorials/sql-agent/#utility-functions"
doc = html2text(url)
embeddings = doc_create_embeddings(doc)
upsert(embeddings, database_conn)

Testing models for code generation

I reused the client from the previous post. Here's the prompt.

rag_query = """Write a Python program that creates a tool and agent 
that queries a Postgres database with a SQL statement and prints the 
result to the console. The program only executes the SQL, and the 
schema is already known."
"""

But hey! I've barely used any of the credits I received for signing up.

It's time to live a little and try some of the big-boy LLMs.

First up is Meta-Llama-3.1-405B-Instruct. This code looks reasonable. It creates an agent and method to execute the tool. A minor nit: neither the os nor json imports are used.

import os
import json
from langchain.llms import AI
from langchain.agents import get_tool_names, initialize_agent, Tool
from langchain_community.utilities import SQLDatabase
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_openai import OpenAI

# Initialize the OpenAI LLM
llm = OpenAI(model="text-davinci-003")

# Initialize the Postgres database connection
db = SQLDatabase.from_uri("postgresql://username:password@host:port/dbname")

# Create a tool that executes a SQL query against the database
class SQLQueryTool(Tool):
    name = "sql_query_tool"

    def __call__(self, query: str) -> str:
        try:
            result = db.run_no_throw(query)
            if not result:
                return "Error: Query failed."
            return str(result)
        except Exception as e:
            return f"Error: {str(e)}"

# Create a toolkit with the SQL query tool
toolkit = SQLDatabaseToolkit(db=db, llm=llm, tools=[SQLQueryTool()])

# Initialize the agent
agent = initialize_agent(toolkit=toolkit, llm=llm)

# Define a function that queries the database with a SQL statement
def query_database(sql_statement: str) -> None:
    # Create a tool call with the SQL query
    tool_call = {"name": SQLQueryTool.name, "args": {"query": sql_statement}}

    # Execute the tool call using the agent
    result = agent({"tool_calls": [tool_call]})

    # Print the result to the console
    print(result)

Next up is Qwen2.5-Coder-32B-Instruct. Whoa, complete fail on the task. Qwen did not create an agent and wrote a straightforward program to connect to Postgres and execute the query.

import psycopg2
import openai

# Set up your OpenAI API key
openai.api_key = 'your-openai-api-key'

# Database connection parameters
db_params = {
    'dbname': 'your_database_name',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',
    'port': 'your_port'  # Default is 5432
}

def connect_to_db(params):
    """Connect to the PostgreSQL database."""
    try:
        connection = psycopg2.connect(**params)
        print("Database connection successful")
        return connection
    except Exception as e:
        print(f"Error connecting to database: {e}")
        return None

def execute_query(connection, query):
    """Execute a SQL query and return the results."""
    try:
        with connection.cursor() as cursor:
            cursor.execute(query)
            results = cursor.fetchall()
            return results
    except Exception as e:
        print(f"Error executing query: {e}")
        return None

def main():
    # Connect to the database
    conn = connect_to_db(db_params)
    if not conn:
        return

    # Example SQL query
    sql_query = "SELECT * FROM your_table_name LIMIT 10;"

    # Execute the query
    results = execute_query(conn, sql_query)
    if results:
        print("Query Results:")
        for row in results:
            print(row)

    # Close the database connection
    conn.close()

if __name__ == "__main__":
    main()

Next, DeepSeek-Coder-V2-Lite-Instruct. This code accomplishes the task of creating the tool and agent, but it also includes code for extracting a table's schema. This is an artifact from the RAG embeddings because the prompt specifies executing only SQL.

from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_openai import OpenAI
from langchain_core.tools import Tool
from langchain_community.utilities import SQLDatabase

# Initialize the database
db = SQLDatabase.from_uri("postgresql://username:password@localhost:5432/dbname")

# Create a toolkit for the database
toolkit = SQLDatabaseToolkit(db=db, llm=OpenAI(model="gpt-4o"))

# Create a tool to execute SQL queries
@tool
def db_query_tool(query: str) -> str:
    """Execute a SQL query against the database and return the results."""
    result = db.run(query)
    if not result:
        return "Error: Query failed."
    return result

# List available tools
tools = toolkit.get_tools()
print(list(map(lambda tool: tool.name, tools)))

# Get schema for a specific table (e.g., 'Artist')
get_schema_tool = next(tool for tool in tools if tool.name == "sql_db_schema")
print(get_schema_tool.invoke("Artist"))

# Example query
query = "SELECT * FROM Artist LIMIT 5;"
result = db_query_tool(query)
print(result)

The results

The models tested didn't produce "copy and paste" code, but they did produce workable code that provided a shortcut to the langchain API. Instantiating the Nebius model with Langchain is a minor change, similar to the OpenAI client.

from langchain_community.utilities import SQLDatabase
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_community.agent_toolkits import create_sql_agent
from langchain_openai import ChatOpenAI
import os


API_KEY = os.environ.get('NEBIUS_API_KEY')

client = ChatOpenAI(
    base_url="https://api.studio.nebius.ai/v1/",
    api_key=API_KEY,
    model="meta-llama/Meta-Llama-3.1-70B-Instruct",
)

# Connect to the PostgreSQL database
db = SQLDatabase.from_uri("postgresql://postgres:postgres@localhost:5432/NORTHWIND")

# Define a tool to execute SQL queries
def db_query_tool(query: str) -> str:
    """Execute a SQL query against the database and return the results."""
    try:
        result = db.run_no_throw(query)
        if not result:
            return "Error: Query failed."
        return result
    except Exception as e:
        return f"Error: {str(e)}"

agent = create_sql_agent(
    llm=client,
    toolkit=SQLDatabaseToolkit(db=db, llm=client),
    verbose=True
)

# SQL query to execute
sql_query = """SELECT country, COUNT(*) AS number_of_suppliers
FROM suppliers
GROUP BY country
ORDER BY country;
"""

# Execute the agent with the SQL query
response = agent.run(sql_query)

# Print the result
print(response)

The output from the agent is verbose and requires formatting in a practical application.

> Entering new SQL Agent Executor chain...
Thought: I should look at the tables in the database to see what I can query. Then I should query the schema of the most relevant tables.
Action: sql_db_list_tables
Action Input: categories, customer_customer_demo, customer_demographics, customers, employee_territories, employees, order_details, orders, products, region, shippers, suppliers, territories, us_statesAction: sql_db_schema
Action Input: suppliers
CREATE TABLE suppliers (
        supplier_id SMALLINT NOT NULL, 
        company_name VARCHAR(40) NOT NULL, 
        contact_name VARCHAR(30), 
        contact_title VARCHAR(30), 
        address VARCHAR(60), 
        city VARCHAR(15), 
        region VARCHAR(15), 
        postal_code VARCHAR(10), 
        country VARCHAR(15), 
        phone VARCHAR(24), 
        fax VARCHAR(24), 
        homepage TEXT, 
        CONSTRAINT pk_suppliers PRIMARY KEY (supplier_id)
)

/*
3 rows from suppliers table:
supplier_id     company_name    contact_name    contact_title   address city    region  postal_code     country phone   fax     homepage
1       Exotic Liquids  Charlotte Cooper        Purchasing Manager      49 Gilbert St.  London  None    EC1 4SD UK      (171) 555-2222  None       None
2       New Orleans Cajun Delights      Shelley Burke   Order Administrator     P.O. Box 78934  New Orleans     LA      70117   USA     (100) 555-4822     None    #CAJUN.HTM#
3       Grandma Kelly's Homestead       Regina Murphy   Sales Representative    707 Oxford Rd.  Ann Arbor       MI      48104   USA     (313) 555-5735     (313) 555-3349  None
*/Thought: I can now construct the query using the schema of the suppliers table. I will use the sql_db_query_checker tool to double check if my query is correct before executing it.
Action: sql_db_query_checker
Action Input: SELECT country, COUNT(*) AS number_of_suppliers FROM suppliers GROUP BY country ORDER BY country;SELECT country, COUNT(*) AS number_of_suppliers 
FROM suppliers 
GROUP BY country 
ORDER BY country;Action: sql_db_query
Action Input: SELECT country, COUNT(*) AS number_of_suppliers FROM suppliers GROUP BY country ORDER BY country;[('Australia', 2), ('Brazil', 1), ('Canada', 2), ('Denmark', 1), ('Finland', 1), ('France', 3), ('Germany', 3), ('Italy', 2), ('Japan', 2), ('Netherlands', 1), ('Norway', 1), ('Singapore', 1), ('Spain', 1), ('Sweden', 2), ('UK', 2), ('USA', 4)]Thought: I now know the final answer
Final Answer: The countries with their respective number of suppliers are: Australia (2), Brazil (1), Canada (2), Denmark (1), Finland (1), France (3), Germany (3), Italy (2), Japan (2), Netherlands (1), Norway (1), Singapore (1), Spain (1), Sweden (2), UK (2), USA (4).

> Finished chain.
{'input': 'SELECT country, COUNT(*) AS number_of_suppliers\nFROM suppliers\nGROUP BY country\nORDER BY country;\n', 'output': 'The countries with their respective number of suppliers are: Australia (2), Brazil (1), Canada (2), Denmark (1), Finland (1), France (3), Germany (3), Italy (2), Japan (2), Netherlands (1), Norway (1), Singapore (1), Spain (1), Sweden (2), UK (2), USA (4).'}

Takeaways

I doubt that LLMs will replace developers or make someone a 10x developer. However, I could cobble together the working code in an hour. LLMs can assist with understanding an unfamiliar API, which makes them useful.

In the next installment, we'll build an application from the code snippets in the previous installments.

Text-to-SQL: Generating SQL with Nebius AI Studio (part 2)

Sophia Parafina — Tue, 10 Dec 2024 22:38:19 +0000

In the previous post, we created documents based on the tables in the Northwind Trader database. We used Nebius AI Studio's embedding model to vectorize the documents and insert them into Postgres with the vector extension. This post shows how to query the database and use the results in a prompt. Let's dive into the code.

A simple client

The script begins with instantiating a Nebius client and the database connection parameters.

API_KEY = os.environ.get('NEBIUS_API_KEY')

conn = psycopg.connect(dbname='rag',user="postgres", autocommit=True)

client = OpenAI(
    base_url="https://api.studio.nebius.ai/v1/",
    api_key=API_KEY,
)

The query function retrieves the embeddings from the database. To form the query, we reuse the create_vector function. The query uses cosine similarity. Pgvector offers other vector search methods.

def query(query_string):
    vector = create_vector(query_string)
    embedding_query = "[" + ",".join(map(str, vector)) + "]"

    # cosine similarity
    query_sql = f"""
    SELECT chunk, embedding <=>'{embedding_query}' AS similarity
    FROM items
    ORDER BY embedding <=> '{embedding_query}'
    LIMIT 20;
    """
    data = conn.execute(query_sql).fetchall()
    result=[]
    for row in data:
        result.append(row[0])

    return result     

def create_vector(text):
     embedding = client.embeddings.create(
           model="BAAI/bge-en-icl",
           input=text,
           dimensions=1024,
           encoding_format="float",
           ).data[0].embedding    

     return embedding

The database result enhances the OpenAI-style prompt when added to the context. The prompt is sent to the Nebius Qwen2.5-Coder-7B-Instruct model, which was trained with SQL, among other programming languages.

def create_prompt(llm_query, database_results):
    content_start = (
        "Write a SQL statement using the database information provided.\n\n"+
        "Context:\n"
    )

    content_end = (
        f"\n\nQuestion: {llm_query}\nAnswer:"
    )

    content = (
        content_start + "\n\n---\n\n".join(database_results) + 
        content_end
    )

    prompt = [{'role': 'user', 'content': content }]

    return prompt

def create_completion(prompt):

    completion = client.chat.completions.create(
        model = "Qwen/Qwen2.5-Coder-7B-Instruct",
        messages = prompt,
        temperature=0.6
    )

    return completion.to_json()

Next, we write a query in English: "List the number of suppliers alphabetically by country." The query is sent to the database to retrieve applicable embeddings.

client_query = "List number of suppliers alphabetically by country."
rag_results = query(client_query)
prompt = create_prompt(rag_query, rag_results)
response = json.loads(create_completion(prompt))

print(response["choices"][0]["message"]["content"])

The embeddings are added to the prompt, and the client returns the following.

To list the number of suppliers alphabetically by country, you can use the following SQL statement:

SELECT country, COUNT(*) AS number_of_suppliers
FROM suppliers
GROUP BY country
ORDER BY country;

This query performs the following actions:
- `SELECT country, COUNT(*) AS number_of_suppliers`: Selects the `country` column and counts the number of rows for each country.
- `FROM suppliers`: Specifies the table from which to retrieve the data.
- `GROUP BY country`: Groups the results by the `country` column.
- `ORDER BY country`: Orders the results alphabetically by the `country` column.

For convenience, use pgAdmin or a similar tool to test the result.

Takeaways

This code is a testbed to try out concepts. Several changes had to be made to the documents to produce working SQL:

The document included the database name along with the table name. The database name was removed since the table name is sufficient for queries.
Tables without relations should be removed, or the LLM uses them to produce SQL. This was the case for the us_states table, which did not have a secondary key for joins.
Qwen2.5-Coder creates table aliases and sometimes assigns them to the wrong columns.

Formulating a query requires specificity. Using table names and terms similar to column names produces better results. So far, the LLM can produce standard SQL functions such as COUNT, DISTINCT, GROUP BY, ORDER BY, and OUTER JOINS.

The results are promising. The following post will show how to build an agent to run the SQL.

Text-to-SQL: Creating Embeddings with Nebius AI Studio (part 1)

Sophia Parafina — Fri, 06 Dec 2024 22:14:02 +0000

Nebius AI Studio is an inference service offering state-of-the-art open-source large language models. The models include text, embeddings, and vision. This is the first article in a series demonstrating how to build a text-to-SQL Retrieval Augmented Generation (RAG) system.

Embeddings are an essential part of machine learning and artificial intelligence systems. They convert high-dimensional data such as text, sound, and images into numerical vectors that capture the meaning and relationships of the data.

As a first step, we will use the Nebius AI Studio API to create embeddings for a database. We can use the Northwinds Trader database, a sample database of a fictional company's transactions. This project uses Postgresql, and there's a Github repository to create the database. The Data Definition Language (DDL) in the SQL script will populate the RAG database, and we can instantiate it to test SQL generated by the LLM.

Converting SQL to Text

The first step is to create the text populating the vector database. We can use raw SQL, but providing context will improve results.

Here is the DDL for the customers table.

CREATE TABLE customers (
    customer_id character varying(5) NOT NULL,
    company_name character varying(40) NOT NULL,
    contact_name character varying(30),
    contact_title character varying(30),
    address character varying(60),
    city character varying(15),
    region character varying(15),
    postal_code character varying(10),
    country character varying(15),
    phone character varying(24),
    fax character varying(24)
);

We can rewrite the SQL in markdown, providing context about the table and the columns.

Table 1: DB.NORTHWIND.CUSTOMERS (Stores customer information)

This table contains customer information.

- customer_id character varying(5) [Primary Key, Not Null] - Unique identifier for customers
- company_name character varying(40) - [NOT NULL]- Company where contact works 
- contact_name character varying(30) - Name of the customer
- contact_title character varying(30) - Title of the customer
- address character varying(60) - Physical address of the customer
- city character varying(15) - City where customer is located
- region character varying(15) - Region where customer is located
- postal_code character varying(10) - Customer's postal code
- country character varying(15) - Country where customer is located
- phone character varying(24) - Customer's phone number
- fax character varying(24) - Customer's fax number

There are 11 Notwind tables annotated and converted to markdown.

Creating Embeddings

We can create vectors using the Nebius embedding models with the markdown data prepped. You'll need an API key from Nebius. Select API Keys in the menu bar and follow the instructions.

Set the API key as an environment variable in Linux/macOS:

export NEBIUS_API_KEY-"xxxxxxxxxxxxxxxx...xxxxxxxxx"

Set the API key as an environment variable in a Windows Powershell session.

$env:NEBIUS_API_KEY="xxxxxxxxxxxxxxxx...xxxxxxxxx"

Let's dive into the code.

Create the database

Assuming that PostgreSQL is running, this function creates a new database called rag. If needed, it adds the pgvector extension and creates a table called items.

If you use Postgress.app on macOS or a hosted provider, pgvector is pre-installed. See your database's installation instructions if needed.

dimensions = 4096

def database_config():
    # enable extension
    conn = psycopg.connect(dbname='postgres',user="postgres", autocommit=True)
    conn.execute('CREATE DATABASE rag')
    conn = psycopg.connect(dbname='rag',user="postgres", autocommit=True)
    conn.execute('CREATE EXTENSION IF NOT EXISTS vector')
    register_vector(conn)

    # create table
    conn.execute('DROP TABLE IF EXISTS items')
    conn.execute('CREATE TABLE items (id bigserial, chunk text, embedding vector({dimensions}))')
    return conn

Create vectors and database records

The embedding process takes the markdown documents and splits the text into smaller chunks. The text is passed to the embedding service to convert them into vectors. The vectors and text are combined to make a record.

API_KEY = os.environ.get('NEBIUS_API_KEY')

client = OpenAI(
    base_url="https://api.studio.nebius.ai/v1/",
    api_key=API_KEY,
)

def create_embeddings(files, docs_dir):
    data = []
    for filename in files:
        file_path = os.path.join(docs_dir,filename)
        with open(file_path, 'r', errors="replace") as file:
            document_text = file.read()
            chunks = recursive_chracter_splitter_chunking(document_text)

            for chunk in chunks:
                chunk = chunk.replace('\n',' ')
                chunk = chunk.replace("\x00", "")
                embedding = create_vectors(chunk)
                pc_list = [chunk, embedding]
                data.append(pc_list)

    return data

def create_vectors(text):
     embedding = client.embeddings.create(
           model="BAAI/bge-en-icl",
           input=text,
           dimensions=1024,
           encoding_format="float",
           ).data[0].embedding    
     return str(embedding)

Insert the embeddings

This function adds the embeddings to the items table.

def upsert(embeddings, conn):
# insert records into the database
    for record in embeddings:
        chunk = str(record[0])
        vector = record[1]
        conn.execute('INSERT INTO items (chunk, embedding) VALUES (%s, %s)', (chunk, vector))

Typically, we could create an index to improve retrieval. However, the Nebius embedding service only returns vectors with the maximum number of tokens, e.g., 4096. Many pgvector HNSW indices support only 2000 tokens, except for bit indices. While you can create a bit quantized index, recall can be negatively impacted. I've added sample code to create the index, but given the size of the data, an index is not necessary.

# create index
# database_conn.execute('CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(4096)) bit_hamming_ops);')

Next steps

This article demonstrates how to create embeddings from the Northwinds database DDL and insert them in PostgreSQL with the pgvector extension. The following article shows how to build the client to generate SQL from a prompt.

Data Engineering with DLT and REST

Sophia Parafina — Fri, 29 Nov 2024 00:46:53 +0000

Building a Data Pipeline with DLT

In this example, a company provides IoT data through a near real-time API for monitoring and a historical API feed for analysis. Each serves a specific purpose, but you can use a single ETL (extraction/transform/loading)solution to manage data. APIs are a convenient way to access data from vehicles and devices. However, an endpoint can be oversubscribed. One method to scale is to ingest the data into a distributed database. This article shows how to ingest data from a REST API into DuckDB, a distributed database built for online analytic processing (OLAP) and historical data analysis.

Requirements

To build this solution, you will need:

Python 3.10 or higher
duckDB installed locally

Setup

It's good practice to create a dedicated environment for a Python project. Create a directory and an environment and activate the environment.

mkdir rest_iot 
cd rest_iot
python3 -m venv venv
source venv/bin/activate

This example uses the data load tool - dlt to consume and ingest near real-time data from the API. Dlt is a lightweight production-ready extract, transform, and load (ETL) Python library. To install dlt:

pip install -U dlt[duckdb]"

We can use dlt to create a new project.

dlt init iot duckdb

Dlt creates several project files, including a code example, requirements.txt, and a directory with configuration files.

iot_pipeline.py
requirements.txt
.dlt/
 config.toml
 secrets.toml

The requirements.txt file contains a list of Python packages required for the project. Installing these packages is essential to ensure the project runs smoothly.

pip install -r requirements.txt

The final step is to configure the API key. Add your API key to the secrets.toml file

rest_token = "rest_api_aaaaaaaaaaaaabbbbbbbbbbbbbbbbccccccccc"

Near Real-Time Data

We won't use the example code generated by dlt. Open the file and delete the code. Copy and paste the following code.

import dlt
from dlt.sources.rest_api import rest_api_source
from dlt.sources.helpers.rest_client.auth import BearerTokenAuth

nrt_source = rest_api_source({
    "client": {
        "base_url": "https://api.rest-iot.com/fleet/vehicles/stats/",
        "auth": BearerTokenAuth(token=dlt.secrets["rest_token"]),
        "paginator": {
            "type": "json_response",
            "next_url_path": "paging.next",
        },
    },
    "resources": [
        { 
            "name" : "feed",
            "endpoint" : {
                "params": {
                    "types": "gps",
                    "decorations": "obdOdometerMeters"
                },
            }
        },
    ],
})

pipeline = dlt.pipeline(
    pipeline_name="feed_pipeline",
    destination="duckdb",
    dataset_name="iot",
    progress="log",
)

load_info = pipeline.run(nrt_source)

The code defines a source, which includes the REST IoT endpoint and the API token. Within the source, the near real-time resource is the gps, which is loaded into duckdb. Dlt handles pagination automatically, extracting data until there are no more pages.

The second part of the code creates a pipeline for extracting data. It defines duckdb as the destination and writes the data to the iot database.

To extract and load the data, run the following:

python3 iot_pipeline.py

The pipeline outputs the log to the console as the program runs. We can check the output by querying duckdb with the CLI.

duckdb

v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen a persistent database.

Open the database file in the duckdb CLI. You can use SQL to query the database.

.open iot.duckdb
SHOW TABLES;

SELECT * FROM feed;

SELECT * FROM feed_gps;

This example uses duckdb, but dlt supports many destinations, including Clickhouse, Snowflake, Databricks, S3, and over 30 SQL databases. Developers can choose a data backend that meets their requirements.

Historical Data

Retrieving historical data follows the same pattern, i.e., define a source and create a pipeline.

params = {
    "types": "engineStates",
    "startTime": "2020-07-23T00:00:00Z",
    "endTime": "2020-07-24T00:00:00Z"
}

historical_source = rest_api_source({
    "client": {
        "base_url": "https://api.rest-iot.com/fleet/vehicles/stats/",
        "auth": BearerTokenAuth(token=dlt.secrets["rest_token"]),
        "paginator": {
            "type": "json_response",
            "next_url_path": "paging.next",
        },
    },
    "resources": [
        { 
            "name" : "history",
            "endpoint" : {
                "params": params,
            }
        },
    ],
})

pipeline = dlt.pipeline(
    pipeline_name="history_pipeline",
    destination="duckdb",
    dataset_name="iot",
    progress="log",
)

load_info = pipeline.run(historical_source)

You can programmatically work with the data in duckdb, but analysts often use spreadsheets. Exporting the data to a CSV file is simple. In the duckdb session, use the COPY command to write the data to a file.

COPY history TO 'history.csv' (HEADER, DELIMITER ',');

What will you build?

This article demonstrates how to work with near real-time and historical data using the dlt package. Whether you need to scale data access across the enterprise or provide historical data for post-event analysis, you can use the same framework to provide customer data. In a future article, I'll demonstrate how to use dlt with a workflow orchestrator such as Apache Airflow or Dagster.``

SaSS-ify with Jigsaw Stack

Sophia Parafina — Wed, 02 Oct 2024 16:19:43 +0000

Jigsaw Stack's API provides various services, such as web scraping and LLM prompting. I tried out the web scraping and translation services in a fit of vanity. Wired Japan interviewed me, and I wanted to try Jigsaw Stack's translation service. You can sign up for Jigsaw Stack and create a token to access the services for free. Here's example code to scrape the part of the article about me and translate it from Japan to English.

from jigsawstack import JigsawStack, JigsawStackError as err
import os, re
from dotenv import load_dotenv

load_dotenv()

api_key = os.getenv("JIGSAW_STACK_API_KEY")
jss = JigsawStack(api_key)

# scrape the page
result =  jss.web.ai_scrape({
  "url": "https://wired.jp/2013/10/21/code-for-america/",
  "element_prompts": ["Sophia Parafina"],
});

data = result["data"]
content = []
a=re.compile("<.*?>")

# extract and clean up any tags
for d in data:
    text = d["results"][0]["html"]
    text = a.sub("", text)
    content.append(text)

print("\n".join(content))

# translate the text
result = jss.translate({ 'text': "\n".join(content), 'target_language':"en", 'current_language':"ja" });

print(result["translated_text"])

The Python packages jigsawstack and dotenv are required to run the script.

The web scraper returned the Japanese text.

ソフィア・パラフィナ ｜Sophia ParafinaGISソフトウェア開発者、経営者、ITコンサルタント。CfAのフェローとしては最年長のひとり。政府系VCで軍事諜報関連ビジネスを手がけてきたほか、GISソフトの会社を設立・売却してきた歴戦の強者だ。今後、政府と働くことは?の問いには「イノヴェイトし続けられる仕事であれば」と答える。カルフォルニア州サンマテオ郡政府と協働中。
ソフィア・パラフィナ ｜Sophia ParafinaGISソフトウェア開発者、経営者、ITコンサルタント。CfAのフェローとしては最年長のひとり。政府系VCで軍事諜報関連ビジネスを手がけてきたほか、GISソフトの会社を設立・売却してきた歴戦の強者だ。今後、政府と働くことは?の問いには「イノヴェイトし続けられる仕事であれば」と答える。カルフォルニア州サンマテオ郡政府と協働中。
ソフィアとサンマテオ郡の場合
ソフィア・パラフィナ ｜Sophia ParafinaGISソフトウェア開発者、経営者、ITコンサルタント。CfAのフェローとしては最年長のひとり。政府系VCで軍事諜報関連ビジネスを手がけてきたほか、GISソフトの会社を設立・売却してきた歴戦の強者だ。今後、政府と働くことは?の問いには「イノヴェイトし続けられる仕事であれば」と答える。カルフォルニア州サンマテオ郡政府と協働中。
ソフィアは、現在行っているサーヴィスをオープンソースで制作している。かつて行政は、マイクロソフトやオラクルといった大企業と組んで大規模な開発を行ってきたが、オープンソース化が一層進み、小回りの利くスピーディな開発が一般化することで行政機構に大きな変化がおこるとソフィアは言う。

This is the translation.

Sophia Parafina | GIS software developer, entrepreneur, IT consultant. One of the oldest fellows at CfA. She has been involved in military intelligence-related businesses with a government-affiliated VC and is a seasoned veteran who has founded and sold a GIS software company. When asked about working with the government in the future, she responds, "If it's a job that allows for continuous innovation." Currently collaborating with the San Mateo County government in California. 

In the case of Sophia and San Mateo County, Sophia Parafina | GIS software developer, entrepreneur, IT consultant. One of the oldest fellows at CfA. She has been involved in military intelligence-related businesses with a government-affiliated VC and is a seasoned veteran who has founded and sold a GIS software company. When asked about working with the government in the future, she responds, "If it's a job that allows for continuous innovation." Currently collaborating with the San Mateo County government in California. 

Sophia is currently producing services as open source. In the past, the administration partnered with large companies like Microsoft and Oracle for large-scale development, but Sophia believes that as open source becomes more prevalent and agile, speedy development becomes the norm, significant changes will occur in the administrative structure.

This was a quick and easy way to scrape and translate a site without having to install packages such as requests, beautifulsoup, or googletrans and write code. What else can you do with Jigsaw Stack? Check out a proof-of-concept RAG app for converting YouTube videos into a chatbot.

autollama

Sophia Parafina — Thu, 23 May 2024 17:01:36 +0000

From the ollama help discord channel:

Docker compose: How do you pull a model automatically with container creation?

The conundrum of containerizing Ollama is that it must be running to pull a model. If you run ollama using docker compose, it doesn't provide a way to pull a model from the ollama registry. What's a budding generative AI nerd to do?

The answer is more docker compose, as in docker compose exec, which runs a script to download a model. Add both commands to a startup script, and you have autollama.

docker compose up -d
sleep 5
docker compose exec autollama sh /root/.ollama/pull_model.sh

Come and get your ollama love here.

RAG with Web Search

Sophia Parafina — Mon, 29 Apr 2024 17:46:24 +0000

Retrieval augmented generation (RAG) is a popular way to use current or proprietary data with Large Language Models (LLMs). There are many articles describing how to perform RAG. Typically, they involve encoding data as vectors and storing the vectors in a database. The database is queried and the data is placed in the context where it is tokenized (converted to vectors) along with the prompt to the LLM. At its simplest, RAG is placing data in the prompt for the LLM to process.

For all practical purposes, the Internet is the database for the world and we can query it with search engines that have methods for returning relevant results. Sound familiar? We can use search to power a RAG application.

For this example, we'll use DuckDuckGo for search, Langchain to retrieve web pages and process the data, and your choice of an Ollama with an open-source LLM or a LLM service like OpenAI.

For the impatient, code

To get started, import the packages into your environment.



pip install -r requirements.txt

Let's dig into the code! Querying DuckDuckGo, retrieving the web pages, and formatting for insertion into the prompt are done by these three function. The ddg_search function queries DuckDuckGo. The get_page function uses Langchain's document loader to retrieve the pages from the search, extracts only the text between p HTML tags with the BeautifulSoupTransformer, and returns a list of Langchain documents.

The ddg_search function extracts the text from the documents and truncates them to ensure they fit within the context window of the LLM. Recent LLMs have larger context windows, and you can change the amount truncated and where to truncate by changing the values. For example, you may want to capture the end of the text which includes conclusions and summaries. The processed text from each document is returned as a list.



def ddg_search(query):
    results = DDGS().text(query, max_results=5)
    print(results)
    urls = []
    for result in results:
        url = result['href']
        urls.append(url)

    docs = get_page(urls)

    content = []
    for doc in docs:
        page_text = re.sub("\n\n+", "\n", doc.page_content)
        text = truncate(page_text)
        content.append(text)

    return content

def get_page(urls):
    loader = AsyncChromiumLoader(urls)
    html = loader.load()

    bs_transformer = BeautifulSoupTransformer()
    docs_transformed = bs_transformer.transform_documents(html, tags_to_extract=["p"], remove_unwanted_tags=["a"])

    return docs_transformed

def truncate(text):
    words = text.split()
    truncated = " ".join(words[:400])

    return truncated

The following section creates a prompt. Currently, there is no standard prompt format and each LLM implements it's own prompt format. The following section demonstrates how to create a prompt for a llama2 or llama3 LLM and an OpenAI LLM. Note how the prompt construction differs.



def create_prompt_ollama(llm_query, search_results):
    content_start = (
        "Answer the question using only the context below.\n\n"+
        "Context:\n"
    )

    content_end = (
        f"\n\nQuestion: {llm_query}\nAnswer:"
    )

    content = (
        content_start + "\n\n---\n\n".join(search_results) + 
        content_end
    )

    prompt = [{'role': 'user', 'content': content }]

    return prompt

def create_prompt_openai(llm_request, search_results):
    prompt_start = (
        "Answer the question using only the context below.\n\n"+
        "Context:\n"
    )

    prompt_end = (
        f"\n\nQuestion: {llm_request}\nAnswer:"
    )

    prompt = (
        prompt_start + "\n\n---\n\n".join(search_results) + 
        prompt_end
    )

    return prompt

Creating a completion (or response) for each LLM also differs.



# use ollama with llama3 foundation model
def create_completion_ollama(prompt):
    completion = ollama.chat(model='llama3', messages=prompt)

    return completion['message']['content']

# use openai's foundation models
def create_completion_openai(prompt):
    res = client.completions.create(
        model="gpt-3.5-turbo-instruct",
        prompt=prompt,
        temperature=0,
        max_tokens=1000,
        top_p=1,
        frequency_penalty=0,
        presence_penalty=0,
        stop=None
    )

    return res.choices[0].text

The application uses Streamlit to search DuckDuckGo, send the results to the LLM, and displays the completion.



# ui
with st.form("prompt_form"):
    result =""
    prompt = ""
    search_query = st.text_area("DuckDuckGo search:", None)
    llm_query = st.text_area("LLM prompt:", None)
    submitted = st.form_submit_button("Send")
    if submitted:
        search_results = ddg_search(search_query)
        # prompt = create_prompt_ollama(llm_query,search_results)
        # result = create_completion_ollama(prompt)
        prompt = create_prompt_openai(llm_query,search_results)
        result = create_completion_openai(prompt)

    e = st.expander("LLM prompt created:")
    e.write(prompt)
    st.write(result)

This is an example query critiquing Taylor Switft's new Tortured Poets Department album using OpenAI's GPT3.5. Ouch! A bit harsh.

Thoughts

Can this be improved upon? Definitely! Most search engines have operators that support searching a specific site or excluding sites. For example, a search for Kubernete's Container Network Interfaces (CNI) can be limited to just kubernetes.io instead of all the other sites that address CNI. The BeautifulSoupTransformer supports extracting or excluding text by tag and the truncate function can be expanded to extract text from certain parts such as the end where conclusions and summaries are located. You can also change the LLM from a general chat model to an instruct model and use it as a coding assistant.

Using web search with an LLM can help produce better search results and summaries. Be sure to check out the code long Github.

RAG Step-by-Step: Open Source Edition

Sophia Parafina — Fri, 05 Apr 2024 19:05:16 +0000

In a previous post, I demonstrated how to implement RAG using the Load-Transform-Embed-Store workflow. I've updated the RAG Step-by-Step repository with an open-source example.

Why open source?

The most common reason for using open source versus a service is cost. This is especially true with generative AI. Services often require a credit card to access their API, and every request costs. Charges are typically small and fractional but add up when writing and debugging code. Another reason is keeping your data private. For example, you may be working with data restricted to a particular country. Open source is one way to meet legal requirements. Finally, open source requires hands-on work, so you get a much deeper understanding of what the software is doing than when your code calls an API.

What's the difference?

Building an open-source version of RAG Step-by-Step wasn't too different from building the SaSS version. Here's a list of changes.

Instead of using OpenAI GPT3.5 and Pincone, I used the localstack_ai which is a containerized environment with Ollama, llama2, and PostgreSQL with pgvector.
To create embeddings, sentence-transformers was used instead of OpenAI's text-embedding-3-small model.
PostgreSQL requires instantiating a database, adding the pgvector extension, and creating the table to hold the data and embeddings.
The Streamlit app defines several SQL queries with different simiarity metrics supported by pgvector.
The prompt uses the llama2 prompt instead of the OpenAI Chat prompt format.

What's stopping you?

Open-source foundation models are released literally every week. The barriers to running a model have decreased significantly, allowing you to play and learn about generative AI quickly and with minimal cost and effort. SaSS or open source, give RAG Step-by-Step a spin.

A RAG Client

Sophia Parafina — Wed, 27 Mar 2024 21:04:06 +0000

In the previous post, we went through the steps to build a backend for an RAG application. This is cool, but not being able to demonstrate it is no fun.

The process for RAG is to retrieve data from the vector database and insert it into a prompt. To query the database, the query has to be encoded as a vector before sending the query.



# creates embeddings
def get_embeddings(text,model="text-embedding-3-small"):
   response = client.embeddings.create(input=text, model=model)

   return response.data[0].embedding

# queries the vector database
def semantic_search(query):
    # create embedding from query
    embeddings = get_embeddings(query)

    # search the database
    response = index.query(
        vector=embeddings,
        top_k=5,
        include_metadata=True
    )

    # extract text from results
    results_text = [r['metadata']['text'] for r in response['matches']]

    return results_text

The next step is to create a prompt that includes the records from the database query.



# create a prompt for OpenAI
def create_prompt(llm_request, vector_results):
    prompt_start = (
        "Answer the question based on the context below.\n\n"+
        "Context:\n"
    )

    prompt_end = (
        f"\n\nQuestion: {llm_request}\nAnswer:"
    )

    prompt = (
        prompt_start + "\n\n---\n\n".join(vector_results) + 
        prompt_end
    )
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ti4wk1tirr6gdsd75fz7.png)
    return prompt

The prompt is sent to the OpenAI chat, which returns a completion if it possible based on the information retrieved from the database.



# create a completion
def create_completion(prompt):
    res = client.completions.create(
        model="gpt-3.5-turbo-instruct",
        prompt=prompt,
        temperature=0,
        max_tokens=1000,
        top_p=1,
        frequency_penalty=0,
        presence_penalty=0,
        stop=None
    )

    return res.choices[0].text

The user interface uses Streamlit. You can enter a database query and a prompt. The complete prompt is displayed when OpenAI returns the completion.



# user interface
with st.form("prompt_form"):
    result =""
    prompt = ""
    semantic_query = st.text_area("Database prompt:", None)
    llm_query = st.text_area("LLM prompt:", None)
    submitted = st.form_submit_button("Send")
    if submitted:
        vector_results = semantic_search(semantic_query)
        prompt = create_prompt(llm_query,vector_results)
        result = create_completion(prompt)

    e = st.expander("LLM prompt created:")
    e.write(prompt)
    st.write(result)

Et voilà!

I've added the client code to the RAG_step-by-step repository. Check it out.