[Home](../README.md) | [Docs](../README.md#documentation) | [FAQ](../docs/FAQ.md) | [Tools](README.md)

---

# Voat SQL Splitter Tool

## Overview

The Voat SQL Splitter (`split_voat_by_subverse.py`) splits large Voat SQL dumps into individual per-subverse files, eliminating the 5-6 hour scan time when archiving individual communities.

**Performance Impact:**
- **Before:** 5-6 hours to archive any single subverse (must scan all 22,637 communities)
- **After:** 2-5 minutes to archive any single subverse (directly import the specific file)
- **Speedup:** 1000x for individual subverse archives

**Note**: Download complete Voat data from [Archive.org Voat Archive 2021](https://archive.org/details/voat-archive-2021). The archive contains 22,637 subverses with 100% coverage.

## Use Case

If you want to create archives for specific Voat subverses (e.g., just "technology" or "privacy"), you currently need to scan through the entire multi-GB SQL dump every time. This tool does the heavy lifting once, creating separate SQL files for each subverse.

## Usage

### Basic Usage

```bash
python tools/split_voat_by_subverse.py /data/voat/ --output /data/voat_split/
```

This will:
1. Read `submission.sql.gz` and `comment.sql.gz*` from `/data/voat/`
2. Split by subverse into `/data/voat_split/`
3. Create 38,127 files (22,582 submission files + 15,545 comment files for 22,637 subverses)
4. Generate `split_metadata.json` with statistics

### Advanced Usage

```bash
python tools/split_voat_by_subverse.py /data/voat/ \
  --output /data/voat_split/ \
  --max-open-files 100 \
  --buffer-size 500 \
  --compression-level 9
```

## Output Structure

```
voat_split/
├── submissions/
│   ├── AskVoat_submissions.sql.gz
│   ├── news_submissions.sql.gz
│   ├── politics_submissions.sql.gz
│   ├── technology_submissions.sql.gz
│   └── ... (22,582 files - all subverses with posts)
│
└── comments/
    ├── AskVoat_comments.sql.gz
    ├── news_comments.sql.gz
    ├── politics_comments.sql.gz
    ├── technology_comments.sql.gz
    └── ... (15,545 files - all subverses with comments)
│
└── split_metadata.json  # Statistics and file information
```

## Using Split Files with redd-archiver

Once you've split the files, you can quickly import individual subverses.

### Docker Workflow (Recommended)

```bash
# Import from pre-split files (2-5 minutes per subverse)
docker compose exec reddarchiver-builder python reddarc.py /data/voat_split/submissions/ \
  --subverse technology \
  --comments-file /data/voat_split/comments/technology_comments.sql.gz \
  --submissions-file /data/voat_split/submissions/technology_submissions.sql.gz \
  --platform voat \
  --output /output/ \
  --import-only

# Generate HTML
docker compose exec reddarchiver-builder python reddarc.py /data \
  --output /output/ \
  --export-from-database
```

> **⚡ Performance**: 2-5 minutes with pre-split files vs 30+ minutes scanning full dump!

> **🔧 CLI Enhancement**: The `--subverse` flag now works with explicit file paths (previously Reddit-only feature). Add `--platform voat` to avoid auto-detection ambiguity.

### Local Usage (without Docker)

```bash
python reddarc.py /data/voat_split/submissions/ \
  --subverse technology \
  --comments-file /data/voat_split/comments/technology_comments.sql.gz \
  --submissions-file /data/voat_split/submissions/technology_submissions.sql.gz \
  --platform voat \
  --output technology_archive/
```

## Command-Line Options

| Option | Default | Description |
|--------|---------|-------------|
| `voat_dir` | - | **Required.** Directory with Voat SQL dumps |
| `--output` | `voat_split/` | Output directory for split files |
| `--max-open-files` | `50` | Maximum concurrent file handles (LRU cache size) |
| `--buffer-size` | `100` | Rows to buffer before writing to disk |
| `--compression-level` | `6` | Gzip compression (1=fast, 9=best compression) |
| `--skip-empty-subverses` | `false` | Skip creating files for empty subverses |
| `--dry-run` | `false` | Show statistics without creating files |
| `--parallel-workers` | CPU-2 | Number of parallel workers for mapping phase |
| `--checkpoint-interval` | `100` | Save checkpoint every N files during mapping |

## Performance Characteristics

### One-Time Split Operation (Actual Benchmarks)
- **Input:** 3.0 GB compressed comments + 664 MB submissions
- **Output:** ~3.5 GB (15,545 subverses × 2 files)
- **Time:** ~4.5 hours with 12 workers (chunk-level parallelism)
- **Memory:** <500 MB (streaming with LRU cache)
- **Disk Space:** Need ~4 GB + 2× safety margin = ~8 GB free

### Voat Archive Statistics
- **Total comments:** 24,150,845
- **Total subverses:** 15,545
- **Orphan rate:** 0.000012% (3 orphaned comments with fallback)

### Chunk-Level Parallelism (v1.2)
The tool now uses **chunk-level parallelism** for processing large files:
- **Producer-consumer pattern**: One thread reads batches, N workers process in parallel
- **All workers active**: Even single large files use all available workers
- **Checkpoint/resume**: Progress saved after each file completes

**Performance**: 2.9GB file processed at ~1,500 comments/sec with 12 workers

### Mapping Phase Optimization (v1.1)
When the JSON mapping file doesn't exist, the tool uses:
- **Parallel processing**: Multiple workers process files concurrently (default: CPU count - 2)
- **Fast regex extraction**: 3-5x faster than full SQL parsing per file
- **Checkpoint/resume**: Progress saved every 100 files for interruption recovery

**Performance improvement**: 19+ hours → **30-60 minutes** (20-40x speedup)

### Future Imports (After Split)
- **Time per subverse:** 2-5 minutes (vs 5-6 hours before)
- **Memory:** Normal redd-archiver memory usage
- **Benefit:** Can parallelize multiple subverse imports

## How It Works

### Architecture

1. **Streaming SQL Parser** - Reuses existing `VoatSQLParser` to read SQL dumps line-by-line
2. **LRU File Cache** - Manages 600+ output files with configurable cache (default: 50 open handles)
3. **Buffered Writes** - Batches rows (default: 100) before flushing to disk
4. **SQL Preservation** - Maintains valid SQL format with CREATE TABLE headers

### Memory Efficiency

The tool uses constant memory regardless of dataset size:
- **Streaming:** Input files read line-by-line
- **LRU Cache:** Only 50 files open at once (automatically closes least-used)
- **Buffered Writes:** Small per-file buffers (100 rows default)
- **Result:** ~500 MB memory usage even for multi-GB datasets

## Metadata JSON

The `split_metadata.json` file contains:

```json
{
  "split_metadata": {
    "split_date": "2026-01-02T10:30:00Z",
    "total_subverses": 22637,
    "total_posts": 3813213,
    "total_comments": 24150845,
    "total_size_mb": 5500.45,
    "processing_time_seconds": 19800,
    "processing_time_human": "5h 30m 0s"
  },
  "subverses": [
    {
      "name": "AskVoat",
      "posts": 45678,
      "comments": 234567,
      "submission_file": "submissions/AskVoat_submissions.sql.gz",
      "comment_file": "comments/AskVoat_comments.sql.gz",
      "submission_size_mb": 12.3,
      "comment_size_mb": 45.6,
      "total_size_mb": 57.9
    },
    ...
  ]
}
```

## Troubleshooting

### Insufficient Disk Space

**Error:** `ERROR: Insufficient disk space!`

**Solution:** Free up space or use a different output directory with more space. The tool needs ~150% of input size (individual files have overhead).

### Too Many Open Files

**Error:** `OSError: [Errno 24] Too many open files`

**Solution:** Reduce `--max-open-files`:

```bash
python tools/split_voat_by_subverse.py /data/voat/ --max-open-files 20
```

Or increase system limit:

```bash
ulimit -n 1024  # Increase to 1024 file handles
```

### Memory Usage Too High

**Solution:** Reduce `--buffer-size`:

```bash
python tools/split_voat_by_subverse.py /data/voat/ --buffer-size 50
```

### Interrupted Split

**Status:** ✅ Resume is now supported!

The tool automatically saves checkpoints:
- **Mapping phase:** `mapping_checkpoint.json` saved every 100 files
- **Comment phase:** `comment_checkpoint.json` saved after each file completes

Simply restart the same command and it will resume from the last checkpoint.

## Comparison with Alternative Approaches

### Approach 1: SQL Splitter (This Tool) ✅
- **Pros:** Simple, no database required, one-time operation
- **Cons:** Still takes 5-6 hours for initial split
- **Best for:** Users who want standalone subverse SQL files

### Approach 2: PostgreSQL Export
- **Pros:** Cleaner data (validated during import), can query/filter
- **Cons:** Requires PostgreSQL running, two-step process (import → export)
- **Best for:** Users already using redd-archiver with PostgreSQL

## Implemented Features

1. ✅ **Resume Logic** - Checkpoint/resume for both mapping and comment phases
2. ✅ **Parallel Processing** - Chunk-level parallelism for comment splitting
3. ✅ **Orphan Recovery** - Fallback to comment's own subverse field (0.000012% orphan rate)

## Future Enhancements

1. **Incremental Updates** - Merge new SQL dumps into existing split
2. **Dry Run** - Preview statistics without creating files
3. **PostgreSQL Export** - Export individual subverses from database

## Examples

### Example 1: Split All Voat Data

```bash
# One-time split (5-6 hours)
python tools/split_voat_by_subverse.py /data/voat/ --output /data/voat_split/

# Future archives (2-5 minutes each)
python reddarc.py /data/voat_split/submissions/ \
  --subverse technology --output tech_archive/
```

### Example 2: High Compression

```bash
# Use maximum compression (slower but smaller files)
python tools/split_voat_by_subverse.py /data/voat/ \
  --output /data/voat_split/ \
  --compression-level 9
```

### Example 3: Low Memory Systems

```bash
# Reduce memory footprint
python tools/split_voat_by_subverse.py /data/voat/ \
  --output /data/voat_split/ \
  --max-open-files 20 \
  --buffer-size 50
```

## Integration with Existing Tools

### Update Scanner Tool

You can update `scan_voat_subverses.py` to read from split metadata:

```bash
# Instead of scanning 5-6 hours
python tools/scan_voat_subverses.py /data/voat/ --output subverses.json

# Read from split metadata (instant)
python tools/scan_voat_subverses.py --from-split /data/voat_split/split_metadata.json
```

(Note: `--from-split` option not yet implemented)

## Performance Benchmarks

### Input Dataset (Actual)
- Submission file: 664 MB compressed
- Comment files: 3.0 GB compressed (3 parts: 128MB + 2.9GB + 4.2MB)
- Total subverses: **15,545**
- Total posts: ~600K
- Total comments: **24,150,845**

### Actual Performance (12 workers)
- **Splitting time:** ~4.5 hours (comment phase with chunk-level parallelism)
- **Output size:** 3.5 GB
- **Memory usage:** <500 MB
- **Files created:** ~31,000 (15,545 subverses × 2 types)
- **Orphan rate:** 0.000012% (3 comments)

### Individual Subverse Import (After Split)
- **Before:** 5-6 hours (scan entire dump)
- **After:** 2-5 minutes (import single file)
- **Speedup:** ~1000x

---

## See Also

- [FAQ - Q8: Voat Pre-Split Files](../docs/FAQ.md#q8-how-do-i-use-voat-data-the-full-dump-takes-30-minutes-per-subverse) - Quick usage guide
- [Tools README](README.md) - Data catalog and scanner tools
- [SCANNER_TOOLS.md](../docs/SCANNER_TOOLS.md) - Complete scanner documentation
- [QUICKSTART.md](../QUICKSTART.md) - Archive generation guide

---

## Contributing

Suggestions for improvements:
1. ~~Resume logic for interrupted splits~~ ✅ Implemented
2. ~~Parallel submission/comment processing~~ ✅ Implemented (chunk-level)
3. Dry run mode implementation
4. PostgreSQL export option
5. Integration with scanner tool

## License

Same license as redd-archiver project.