DOCSEARCHbyAlgolia
Submit Docs

OSS Data Analyst

repository·main·Indexed 41 days ago

https://github.com/vercel-labs/oss-data-analyst

An open-source AI data science agent (v0.1.0) that converts natural language to SQL queries and provides intelligent data analysis. It utilizes a sandboxed exploration approach to dynamically discover schema definitions via a YAML-based semantic layer and executes queries against a SQLite database.

Tokens
5.9K
Snippets
22
Records
27
Agent score
100%

What's inside OSS Data Analyst

  1. How the sandboxed exploration approach works

    main

    Instead of hardcoding schema knowledge into prompts, the agent uses a sandboxed exploration approach. This allows the agent to adapt to any schema without redeployment or prompt changes.

    The Workflow:

    1. Sandbox Creation: A Vercel Sandbox is created and populated with your semantic layer YAML files.
    2. Schema Exploration: The agent uses shell commands (like cat, grep, and ls) to browse the catalog and entity definitions dynamically.
    3. Query Building: The agent constructs SQL queries based on the discovered schema.
    4. Execution: Queries are executed against a SQLite database.
    5. Reporting: Results are returned with a narrative explanation.

  2. Define and add your own schema via the Semantic Layer

    main

    The semantic layer defines your data model using YAML files located in src/semantic/. The agent reads these files at runtime to understand your schema.

    Directory Structure

    • src/semantic/catalog.yml: An entity index containing descriptions.
    • src/semantic/entities/*.yml: Individual entity definitions.

    Entity YAML Schema

    Each entity file in src/semantic/entities/ should include:

    • sql_table_name: The name of the underlying SQL table.
    • fields: Available columns, which can include SQL expressions.
    • joins: Relationships to other entities.
    • Example questions the entity can answer.

    How to add a new schema

    You can add new data models without changing any application code:

    1. Create a new YAML file in src/semantic/entities/.
    2. Register the new entity in src/semantic/catalog.yml.
    3. The agent will automatically discover the new schema during its next exploration cycle.
    # Example structure for an entity in src/semantic/entities/example.yml
sql_table_name: my_table
fields:
  - name: user_id
    expression: id
  - name: full_name
    expression: first_name || ' ' || last_name
joins:
  - entity: users
    on: user_id

  3. Quick Start with OSS Data Analyst

    main

    To get a local instance of the OSS Data Analyst running with sample data, follow these steps:

    1. Install dependencies:
      git clone https://github.com/vercel-labs/oss-data-analyst.git
cd oss-data-analyst
pnpm install
    2. Configure environment: Copy the example environment file and add your Vercel AI Gateway API key to .env.local.
      cp env.local.example .env.local
    3. Initialize the database (creates a SQLite database with sample Companies, People, and Accounts data):
      pnpm initDatabase
    4. Start the development server:
      pnpm dev
      Access the application at http://localhost:3000.
    git clone https://github.com/vercel-labs/oss-data-analyst.git
cd oss-data-analyst
pnpm install
cp env.local.example .env.local
pnpm initDatabase
pnpm dev

  5. Understand the Data Analyst workflow and semantic layer

    main

    The agent operates by following a specific four-step workflow to answer data questions using a semantic layer defined in YAML files.

    1. Schema Exploration

    The agent uses bash tools to browse the semantic layer:

    • semantic/catalog.yml: The entity catalog containing descriptions and field lists.
    • semantic/entities/*.yml: Detailed entity definitions including SQL expressions, joins, and metadata.

    2. SQL Building

    The agent constructs SQLite SELECT queries using the sql_table_name found in the entity YAML files. It is instructed to use table aliases (e.g., t0, t1) and apply LIMIT 1001.

    3. Execution

    Queries are executed via the ExecuteSQL tool. If a query fails, the agent is permitted up to 2 retry attempts, provided it modifies the SQL to address the error rather than retrying the exact same query.

    4. Reporting

    The process concludes by calling the FinalizeReport tool, which packages the final SQL, the results in CSV format, and a concise narrative (3-6 sentences).

  6. Enable observability with Langfuse

    main

    To enable observability, set OBSERVABILITY_ENABLED to true. When this flag is enabled, you must also provide LANGFUSE_SECRET_KEY and LANGFUSE_PUBLIC_KEY. If these keys are missing while observability is enabled, the application will fail to start with the error: LANGFUSE_SECRET_KEY and LANGFUSE_PUBLIC_KEY are required when OBSERVABILITY_ENABLED=true.

    OBSERVABILITY_ENABLED=true
LANGFUSE_SECRET_KEY=your_secret_key
LANGFUSE_PUBLIC_KEY=your_public_key
LANGFUSE_BASEURL=https://cloud.langfuse.com

  7. Troubleshoot OSS Data Analyst issues

    main

    If you encounter issues, try the following commands:

    Database Not Found If the SQLite database is missing or empty, re-run the initialization command:

    pnpm initDatabase

    Build Errors To verify type safety and catch build-time errors, run:

    pnpm type-check

  8. Configure Snowflake connection via environment variables

    main

    The Snowflake client uses environment variables to establish connections. Ensure the following configuration keys are set in your environment:

    • SNOWFLAKE_ACCOUNT
    • SNOWFLAKE_USERNAME
    • SNOWFLAKE_PASSWORD
    • SNOWFLAKE_DATABASE
    • SNOWFLAKE_SCHEMA
    • SNOWFLAKE_WAREHOUSE
    • SNOWFLAKE_ROLE
    • SNOWFLAKE_POOL_MAX (Optional: sets the maximum pool size)
    • SNOWFLAKE_STATEMENT_TIMEOUT (Optional: default timeout for SQL statements)
    • SNOWFLAKE_APPLICATION (Optional: defaults to oss-data-analyst-api)
    • SNOWFLAKE_CLIENT_SESSION_KEEP_ALIVE (Optional: defaults to true)

  9. Extract report data using `extractFinalizeReport`

    main

    After the agent has finished running, use extractFinalizeReport to parse the tool results and retrieve the structured report. This is useful for pulling the final SQL, CSV data, and narrative out of the AI's execution steps.

    import { extractFinalizeReport } from './lib/agent';

// result is the object returned by the streamText execution
const { hasFinalResult, sql, csvResults, narrative } = extractFinalizeReport(result);

if (hasFinalResult) {
  console.log('SQL:', sql);
  console.log('CSV:', csvResults);
  console.log('Narrative:', narrative);
}

  10. Manage the database lifecycle with getDatabase() and closeDatabase()

    main

    The SQLite connection is managed via a singleton pattern.

    • getDatabase(): Retrieves the existing connection or creates a new one. The database file is located at [process.cwd()]/data/oss-data-analyst.db. It also ensures foreign_keys = ON is enabled via PRAGMA.
    • closeDatabase(): Closes the active database connection and resets the internal instance to null.
    import { getDatabase, closeDatabase } from './lib/sqlite';

const db = getDatabase();
// ... perform operations ...

closeDatabase();

  11. Reset the configuration cache with resetConfig()

    main

    The resetConfig() function clears the internal cachedConfig. This is primarily intended for use in testing environments where you need to re-initialize the configuration with different environment variables between test cases.

    import { resetConfig } from '@/config';

resetConfig();

  12. Execute SQL queries with executeSQL()

    main

    Use executeSQL(sql: string) to run SQL commands against the SQLite database. The function automatically detects if the query is a SELECT statement or a data modification statement (like INSERT, UPDATE, or DELETE) and returns a QueryResult object accordingly.

    Returns:

    • For SELECT queries: An object containing rows (the data), columns (the field names), rowCount (number of rows returned), and executionTime (ms).
    • For modification queries: An object containing rows: [], columns: [], rowCount (number of affected rows), and executionTime (ms).

    Errors: If the query fails, it throws an error with the prefix SQLite Error: followed by the underlying message.

    import { executeSQL } from './lib/sqlite';

// Example SELECT
const result = await executeSQL('SELECT * FROM users WHERE id = 1');
console.log(result.rows);

// Example UPDATE
const updateResult = await executeSQL('UPDATE users SET name = "New Name" WHERE id = 1');
console.log(updateResult.rowCount); // Number of affected rows