Vertica Expert Skill

A comprehensive skill for Vertica database migration, including migration from Oracle, DB2, SQL Server, PostgreSQL, MySQL, and Teradata, SQL syntax reference, PL/vSQL stored procedure development, UDx custom function creation, in-database machine learning.

This skill is distilled from the Vertica product documentation and my memory to provide detailed guidance on:

• Migration Guides: Migrate scripts from Oracle, DB2, SQL Server, PostgreSQL, MySQL, and Teradata to Vertica, including DDL, DML, stored procedures, and queries
• Stored Procedures and UDx Development: Creating custom stored procedures or functions in SQL, C++, and Python
• Machine Learning: In-database predictive analytics with regression, classification, clustering, and time series

📚 Reference Document Summaries: For large-scale migrations using the Multi-Agent Workflow, agent-optimized summaries of reference documents are available in references/reference-summaries/ directory. These summaries contain ALL information needed for migration decisions while reducing context usage by ~88%. Full documents are for human reference with detailed examples.

vertica-expert-skill/
├── SKILL.md                                # Main skill definition
├── README.md                               # Project overview
├── CLAUDE.md                               # Internal documentation
├── install.sh                              # Installation script
├── uninstall.sh                            # Uninstallation script
├── references/                             # Detailed reference guides
│   ├── sql-syntax-reference.md             # Complete SQL syntax
│   ├── user-defined-sql-functions-guide.md # User-Defined SQL Functions
│   ├── stored-procedures-guide.md          # PL/vSQL development
│   ├── udx-development-guide.md            # Custom function development (C++, Python, Java, R)
│   ├── data-type-mapping.md                # Data type mapping guide
│   ├── function-mapping.md                 # Function conversion guide
│   ├── query-optimization.md               # Performance optimization
│   ├── migration-guides-overview.md        # Guide hierarchy and usage instructions
│   ├── generic-migration-guide.md          # 🚨 MANDATORY: Master migration requirements
│   ├── oltp-to-olap-rewrite-guide.md       # 🔄 OLTP→OLAP SQL rewrite patterns
│   ├── oracle-migration.md                 # Oracle-specific migration
│   ├── db2-migration.md                    # IBM DB2 migration guide
│   ├── sqlserver-migration.md              # SQL Server migration guide
│   ├── postgresql-migration.md             # PostgreSQL migration guide
│   ├── mysql-migration.md                  # MySQL migration guide
│   ├── teradata-migration.md               # Teradata migration guide
│   ├── machine-learning.md                 # In-database ML algorithms
│   ├── ml-function-mapping.md              # Cross-database ML mapping
│   ├── multi-agent-migration-guide.md      # 🤖 Multi-agent migration guide (Manager ONLY - other agents use their own config files)
│   └── reference-summaries/               # 📚 Agent-optimized reference summaries
│       ├── generic-migration-summary.md   # Generic migration requirements (agent-optimized)
│       ├── sql-syntax-summary.md          # SQL syntax reference (agent-optimized)
│       ├── oltp-to-olap-summary.md        # OLTP→OLAP rewrite patterns (agent-optimized)
│       ├── stored-procedures-summary.md   # Stored procedures guide (agent-optimized)
│       ├── user-defined-sql-functions-guide.md     # SQL functions guide
│       ├── oracle-migration-summary.md    # Oracle migration (agent-optimized)
│       ├── db2-migration-summary.md       # DB2 migration (agent-optimized)
│       ├── sqlserver-migration-summary.md # SQL Server migration (agent-optimized)
│       ├── postgresql-migration-summary.md # PostgreSQL migration (agent-optimized)
│       ├── mysql-migration-summary.md     # MySQL migration (agent-optimized)
│       └── teradata-migration-summary.md  # Teradata migration (agent-optimized)
├── examples/                               # Examples of other databases
└── slides/                                 # Slides in Python format

• Generic Migration Requirements 🚨 MANDATORY: Generic Migration Guide - Complete migration procedures that apply to ALL database types (Agent-Optimized Summary)
• OLTP to OLAP Rewrite 🔄 ESSENTIAL: OLTP to OLAP Rewrite Guide - 5 rewrite patterns for converting row-by-row procedural code to set-based SQL (adjacent DML merging, loop-DML→set-based, cursor→window functions, etc.) (Agent-Optimized Summary)
• Oracle to Vertica: Oracle Migration Guide - PL/SQL to PL/vSQL conversion following generic migration requirements (Agent-Optimized Summary)
• DB2 to Vertica: DB2 Migration Guide - PL/SQL to PL/vSQL conversion with DB2-specific features (modules, MQT, special registers) following generic requirements (Agent-Optimized Summary)
• SQL Server to Vertica: SQL Server Migration Guide - T-SQL to Vertica SQL with stored procedure migration following generic requirements (Agent-Optimized Summary)
• PostgreSQL to Vertica: PostgreSQL Migration Guide - PL/pgSQL to PL/vSQL with function mapping following generic requirements (Agent-Optimized Summary)
• MySQL to Vertica: MySQL Migration Guide - Schema and query conversion with performance optimization following generic requirements (Agent-Optimized Summary)
• Teradata to Vertica: Teradata Migration Guide - SPL to PL/vSQL conversion with Teradata-specific features (QUALIFY, PRIMARY INDEX, BTEQ, volatile tables) following generic requirements (Agent-Optimized Summary)

• Complete SQL Syntax: DDL, DML, queries, CTEs, window functions
• Advanced Analytics: Complex analytical queries and reporting
• Data Loading: COPY statements with transformation options
• Transaction Control: Complete transaction management

• Simple SQL Extensions: Create reusable SQL expressions with CREATE FUNCTION
• Data Transformation: NULL handling, string formatting, mathematical calculations
• Business Logic: Encapsulate frequently used calculations and rules
• Function Management: Overloading, privileges, and performance optimization
• Easy Testing: Comprehensive testing strategies with VSQL

• Procedure Creation: Complete PL/vSQL development framework
• Parameter Handling: IN, OUT, INOUT parameters with defaults
• Control Structures: IF, CASE, loops (FOR, WHILE)
• Exception Handling: Comprehensive error management and logging
• Dynamic SQL: EXECUTE statements for flexible queries

• Projection Design: Order-optimized, aggregate, replicated projections
• Encoding Strategies: RLE, DELTA, GZIP, LZO for optimal compression
• Query Optimization: Rewriting for columnar performance
• Resource Management: Resource pools and workload prioritization
• Statistics Management: ANALYZE_STATISTICS best practices

• Multiple Languages: C++, Python, Java, R support
• Function Types: Scalar, aggregate, analytic, transform functions
• High Performance: Optimized C++ implementations
• Easy Deployment: Registration and installation procedures

• Regression Algorithms: Linear, XGBoost, Random Forest, SVM, Poisson
• Classification: Logistic, XGBoost, Random Forest, Naive Bayes, SVM
• Clustering: K-Means, Bisecting K-Means, K-Prototypes
• Time Series: Autoregression, Moving Average, ARIMA
• Data Preparation: Imputation, encoding, outlier detection, balancing
• Model Management: Training, evaluation, deployment, monitoring

This skill provides comprehensive coverage of:

1. Database Migration - Converting Oracle, DB2, SQL Server, PostgreSQL, MySQL, and Teradata procedures
2. OLTP to OLAP Rewrite - Rewriting row-by-row procedural code to set-based SQL for Vertica's columnar architecture
3. Vertica SQL Development - Creating complex analytical queries from requirements
4. PL/vSQL Development - Building stored procedures with error handling
5. Query Optimization - Converting slow queries to high-performance Vertica
6. UDx Development - Creating custom aggregate functions in C++
7. Machine Learning - Implementing end-to-end ML workflows in Vertica

• Automated SQL conversion with function mapping
• Performance optimization recommendations
• Best practices for Vertica development

• Schema optimization guidance
• Projection design strategies
• Resource management best practices

• Migration planning assistance
• Performance modeling for Vertica
• Scalability considerations

• In-database ML workflows without data movement
• Algorithm selection and implementation guidance
• Model deployment and monitoring strategies

1. Install the skill using ./install.sh
2. Read Generic Migration Guide (from vertica-expert skill) 🚨 MANDATORY - Understand all requirements before proceeding
   • 💡 For Multi-Agent Workflow: Use Summary version to reduce context usage
3. Read OLTP to OLAP Rewrite Guide (from vertica-expert skill) 🔄 ESSENTIAL - Learn rewrite patterns for procedural/OLTP code
   • 💡 For Multi-Agent Workflow: Use Summary version to reduce context usage
4. Identify your source database (Oracle, DB2, SQL Server, PostgreSQL, MySQL, Teradata)
5. Process source files sequentially - never skip or reorder objects
6. Migrate ALL objects - tables, views, procedures, functions, DML, sequences
7. Rewrite procedural code using OLTP-to-OLAP patterns (cursors→window functions, loops→set-based SQL)
8. Test each object individually before considering it migrated
9. Execute complete migration and validate all dependencies
10. Optimize and validate performance results

1. Install the skill using ./install.sh
2. Explore vertica-expert skill's reference guides in the references/ directory
3. Use provided examples as templates for your development
4. Apply best practices for optimal performance
5. Test with your data and iterate as needed

1. Install the skill using ./install.sh
2. Choose your ML algorithm (regression, classification, clustering, time series)
3. Prepare your training data with appropriate features
4. Train and evaluate models using Vertica's in-database functions
5. Deploy models for real-time or batch predictions

1. Read Generic Migration Guide 🚨 MANDATORY FIRST STEP - Understand all non-negotiable requirements
   • 💡 For Multi-Agent Workflow: Use Summary version to reduce context usage
2. Read OLTP to OLAP Rewrite Guide 🔄 ESSENTIAL - Understand rewrite patterns for procedural code
   • 💡 For Multi-Agent Workflow: Use Summary version to reduce context usage
3. Assess your current database complexity and dependencies
4. Plan your migration strategy following sequential processing requirements
5. Convert ALL objects (tables, views, procedures, functions, DML) one-to-one
6. Rewrite procedural code using OLTP-to-OLAP patterns (cursors→window functions, loop-DML→set-based SQL)
7. Test every object individually before considering it migrated
8. Execute complete migration and validate all dependencies
9. Optimize for Vertica's columnar architecture
10. Validate performance against baselines

For common database script migrations , follow this simple approach:

/vertica-expert

Quick Start:

• First：

  Task: Using general migration workflow to migrate Oracle database scripts from "examples/oracle/" to Vertica, saving results to "examples/vertica/" with identical file names.
  
  Before actually starting the task, pause and wait for my confirmation.
  

  Or:

  Task: Using Embedded SQL Script Migration Workflow to migrate Teradata database script from "examples/teradata/app2.pl" to Vertica, saving results to "examples/vertica/" with identical file name.
  
  Before actually starting the task, pause and wait for my confirmation.
  

• Then：

  Please provide a detailed description of all the specific requirements you have understood for this task. Finally, pause and wait for my confirmation.
  

• Finally：

  Be sure to keep this task and the aforementioned requirements firmly in mind. I will rigorously inspect whether you have violated these requirements during this task.
  
  Start the task.
  

For some special situations:

• Remind the agent when the context is about to be exhausted：

  Save the state in preparation for compacting.
  

• Continue after compacting context：

  Restore from the state file you saved before compact.
  Continue.
  

⚠️ PREREQUISITE: Enable Background Agent Support

Before using this workflow, you may need set the environment variable CLAUDE_CODE_EXPERIMENTAL_AGENT_TEAMS to enable background agent execution and SendMessage communication for better experience:

Option 1: Set in ~/.claude/settings.json (Recommended)

{
  "env": {
    "CLAUDE_CODE_EXPERIMENTAL_AGENT_TEAMS": "1"
  }
}

Option 2: Set in profile before start claude

export CLAUDE_CODE_EXPERIMENTAL_AGENT_TEAMS=1

📖 Documentation: For more details on background agent execution and SendMessage API, see Claude Code Sub-Agents Documentation

When to Use: Multiple source files, or single file >200 lines, or multiple stored procedures/functions, or large-scale migrations requiring strict context management

When NOT to Use: Single small file, simple table structure migration, embedded SQL scripts (Shell/Perl/Python with Here doc SQL), files where temporary tables create inter-statement dependencies

4-Agent Architecture:

Workflow Loop:

Phase 1: Migration & Functional Testing (per source file, alphabetical order)
  Manager SENDMESSAGE to requester_agent → Requester READS section (offset=N, limit=50) → RETURNS code snippet
  → Manager SENDMESSAGE to migrator_agent → MIGRATES + unit tests (up to 10 attempts)
  → 🔍 MANAGER VERIFIES unit test (logs complete, no anomalies, status PASSED)
  → Manager SENDMESSAGE to tester_agent → FUNCTIONAL TEST (single VSQL call, autocommit, verify no errors)
  → 🔍 MANAGER VERIFIES test results (logs complete, no false positives)
  → PASS → APPEND to target file
  → FAIL → Manager SENDMESSAGE to migrator_agent to fix → RETEST

Phase 2: Integration Testing (after ALL objects migrated)
  Manager SENDMESSAGE to tester_agent: clears test database completely → executes ALL files in order → runs integration test
  → PASS → ✅ Migration complete
  → FAIL → Tester reports failures with complete logs → Manager SENDMESSAGE to migrator_agent with error info and ALL target files → Migrator analyzes errors and fixes issues → Manager SENDMESSAGE to tester_agent to clear test database and re-run integration test → Repeat until pass

Manager's Strict Limits:

• ✅ ONLY obtains source code from Requester — never reads files directly
• ✅ ONLY creates Requester, Migrator, Tester agents — no other agents allowed
• ✅ ONLY provides process control — NEVER gives migration rules/decisions to Migrator
• ✅ VERIFICATION, not migration expertise — verifies test logs, not code correctness
• ✅ ONLY re-initializes agents if they crash — uses SendMessage for all subsequent tasks

Benefits: Focused context windows · Clear separation of concerns · Dual verification ensures quality · Two-phase testing · Easy debugging · Agents persist across tasks — no repeated initialization overhead

Quick Start:

Task: Please use the Multi-Agent Migration Workflow to migrate [source_db] scripts from "[source_path]" to Vertica, saving results to "[target_path]" with identical file names.

You are the Manager agent. Before starting, wait for my confirmation.

Example:

Task: Please use the Multi-Agent Migration Workflow to migrate SQL Server database scripts from "examples/sqlserver/*.sql" to Vertica, saving results to "examples/vertica/" with identical file names.

You are the Manager agent. Before starting, wait for my confirmation.

Then monitor and frequently remind the Manager:

/loop 5m You are the Manager agent. Remember:
1. Never tell Migrator how to migrate, he is the expert of migration, not you.
2. Never tell Tester how to test, he is the expert of testing, not you.
3. Don't disclose the source and target files to anyone.
4. Save state after EVERY task.
5. Use SendMessage for all subsequent tasks. If you lose the IDs of the subagents, just look for them it in the place where Claude stores subagent information.
6. NEVER switch to General Migration Workflow when subagents are unreliable - follow Agent Lifecycle Management (wait → retry → re-initialize → resume).

Manager State File: The Manager agent saves its state to manager_state.md (in current working directory) after every task. This file prevents context loss from compaction and enables recovery from crashes. The file is automatically created and updated by the Multi-Agent Migration Workflow.

For some special situations:

• Remind the agent when the context is about to be exhausted：

  Save the state in preparation for compacting.
  

• Continue after compacting context：

  Restore from the state file `manager_state.md` (in current working directory) you saved before compact.
  You have the IDs of the subagents already, just SendMessage with them directly.
  Continue with the current Multi-Agent Migration Workflow.
  

• Recovery from a crash：

  You are the Manager in Multi-Agent Migration Workflow.
  Load Multi-Agent Migration Workflow guide at first.
  Then restore from the state file `manager_state.md` (in current working directory) you saved before crash.
  You have the IDs of the subagents already, just SendMessage with them directly.
  Continue with the current Multi-Agent Migration Workflow.
  

Reference Documentation:

• Manager reads:

  • Multi-Agent Migration Guide - Essential rules, personality traits, constraints, workflows, initialization templates, and detailed workflows (PRIMARY REFERENCE)

• Migrator reads:

  • Generic Migration Guide
  • OLTP to OLAP Rewrite Guide
  • Database-specific migration guide summaries (Oracle, DB2, SQL Server, PostgreSQL, MySQL, Teradata)
  • SQL Syntax Reference
  • Function Mapping Guide
  • Data Type Mapping Guide
  • Stored Procedures Guide
  • User-Defined SQL Functions Guide

All agents use their own configuration files (not listed above):

• Manager: Multi-Agent Migration Guide only
• Requester: agents/requester.md
• Migrator: agents/migrator.md + documents listed above
• Tester: agents/tester.md

1. Design for columnar storage from the beginning
2. Use appropriate data types for optimal compression
3. Create projections that match query patterns
4. Implement proper encoding for each column type
5. Update statistics regularly after data changes

1. Design projections first before loading data
2. Use appropriate encoding for each column
3. Update statistics regularly after data changes
4. Monitor query performance using system tables
5. Iterate on optimization based on actual usage

1. Prepare data with feature engineering and cleaning
2. Select algorithm based on your use case
3. Train models using in-database functions
4. Evaluate performance with built-in metrics
5. Deploy for production with real-time scoring

All SQL examples and stored procedures provided by this skill can be tested using the VSQL command-line tool.

The environment variable VSQL encapsulates the vsql connection parameters:

export VSQL='/opt/vertica/bin/vsql -h hostname -p 5433 -U username -w password dbname'

Important Autocommit Behavior: By default, vsql has autocommit OFF for interactive sessions. For testing, either:

• Enable autocommit: SET SESSION AUTOCOMMIT TO ON;
• Include explicit COMMIT statements after data modifications

Important Session Behavior: Each $VSQL -c command creates a new session. For data persistence across multiple commands, either:

1. Use explicit COMMIT statements in DML commands, or
2. Use here document syntax for multi-statement transactions

Checking Object Availability:

• Schema: $VSQL -c "\dn schema_name"
• Table: $VSQL -c "\dt table_name"
• View: $VSQL -c "\dt view_name"
• Projection: $VSQL -c "\dj projection_name"
• Function: $VSQL -c "\df function_name"

Additional VSQL Options:

• Run SQL file: $VSQL -f script.sql
• Interactive mode: $VSQL
• Enable timing: $VSQL -i

Single-line SQL commands:

$VSQL -c "SELECT VERSION();"

Multi-line SQL (recommended for stored procedures and complex queries):

$VSQL<<-'EOF'
CREATE OR REPLACE PROCEDURE example_proc() AS $$
BEGIN
    -- Your PL/vSQL code here
    RAISE NOTICE 'Test procedure executed';
END;
$$
EOF

Key Benefits of Here Document:

• Avoid escaping special characters like $ and "
• Maintain SQL code formatting and readability
• Ideal for stored procedures with $$ delimiters

• Reference Documentation: comprehensive guides including mandatory Generic Migration Guide and OLTP-to-OLAP Rewrite Guide
• Migration Hierarchy: Clear documentation structure with Generic Guide as foundation
• Installation Tools: Easy setup and configuration scripts
• Best Practices: Proven patterns for development, optimization, and ML
• Troubleshooting: Common issues and solutions for all use cases
• Examples: 100+ practical examples across development, migration, and ML

This skill provides everything needed for successful Vertica database migration, development, and machine learning with optimal performance outcomes.

The slides/ directory contains Python scripts that generate slides using python-pptx. Before running them, make sure the required Python package is installed:

# Check dependency
python3 -c "import pptx; print('python-pptx', pptx.__version__)"
# If missing:
pip install python-pptx

# Generate the presentation
python3 slides/vertica_expert_overview.py

The generated .pptx file will be saved in the slides/ directory.