一键在 Manus 中运行任何 Skill

historical-backfill-execution

Execute chunked historical blockchain data backfills using canonical 1-year pattern. Use when loading multi-year historical data, filling gaps in ClickHouse, or preventing OOM failures on Cloud Run. Keywords chunked_backfill.sh, BigQuery historical, gap filling, memory-safe backfill.

在 Manus 中运行

概览

安装命令

npx skills add https://github.com/terrylica/gapless-network-data --skill historical-backfill-execution

复制此命令并粘贴到 Claude Code 中以安装该技能

来源

terrylica/gapless-network-data

星标0

分支0

更新时间2025年11月29日 12:19

文件资源管理器

5 个文件

SKILL.md

readonly

name	historical-backfill-execution
description	Execute chunked historical blockchain data backfills using canonical 1-year pattern. Use when loading multi-year historical data, filling gaps in ClickHouse, or preventing OOM failures on Cloud Run. Keywords chunked_backfill.sh, BigQuery historical, gap filling, memory-safe backfill.

Historical Backfill Execution

Version: 2.0.0 Last Updated: 2025-11-29 Purpose: Execute chunked historical blockchain data backfills using canonical 1-year pattern

When to Use

Use this skill when:

Loading multi-year historical data (e.g., 2015-2025 Ethereum blocks, 23.8M blocks total)
Gaps detected in ClickHouse requiring backfill
Preventing OOM (Out of Memory) failures on Cloud Run (4GB memory limit)
Need to execute complete historical data collection
Keywords: chunked_backfill.sh, BigQuery historical, gap filling, memory-safe backfill

Canonical Pattern (Established 2025-11-10)

Empirically Validated Approach:

Chunk size: 1 year (~2.6M blocks for Ethereum)
Memory usage: <4GB per chunk (Cloud Run safe, no OOM errors)
Execution time: ~1m40s-2m per chunk (total ~20 minutes for 10 years)
Idempotency: ReplacingMergeTree allows safe re-runs (automatic deduplication)
Cost: $0/month (within BigQuery 1TB/month free tier, ~10MB per query)

Why 1-Year Chunks?

See Backfill Patterns Reference for complete rationale (memory constraints, retry granularity, progress tracking).

Prerequisites

GCP project access: eonlabs-ethereum-bq
BigQuery dataset: bigquery-public-data.crypto_ethereum.blocks
ClickHouse Cloud database: ethereum_mainnet.blocks
ClickHouse credentials configured (via Doppler or environment variable)
Python dependencies: google-cloud-bigquery, pyarrow, clickhouse-connect

Workflow

1. Execute Chunked Backfill

Run the canonical 1-year chunking script:

cd /deployment/backfill
./chunked_backfill.sh 2015 2025

What This Does:

Loads blocks year-by-year from BigQuery
Uses PyArrow for efficient data transfer
Inserts into ClickHouse with ReplacingMergeTree (automatic deduplication)
Shows progress for each year: "Loading blocks 1 - 2,600,000"

Alternative (use provided validation wrapper):

.claude/skills/historical-backfill-execution/scripts/chunked_executor.sh 2015 2025

2. Monitor Progress

Watch the output for year-by-year progress:

Loading blocks for year 2015...
Year 2015: Loading blocks 1 - 2,600,000
Completed 2015 in 1m42s

Loading blocks for year 2016...
Year 2016: Loading blocks 2,600,001 - 5,200,000
Completed 2016 in 1m38s

...

All years completed successfully!
Total blocks loaded: 23,800,000
Total time: 18m45s

3. Verify Completeness

After backfill completes, verify all blocks loaded:

cd 
doppler run --project aws-credentials --config prd -- python3 -c "
import clickhouse_connect
import os
client = clickhouse_connect.get_client(
    host=os.environ['CLICKHOUSE_HOST'],
    port=8443,
    username='default',
    password=os.environ['CLICKHOUSE_PASSWORD'],
    secure=True
)
result = client.query('SELECT COUNT(*) as total, MIN(number) as min_block, MAX(number) as max_block FROM ethereum_mainnet.blocks FINAL')
print(f'Total blocks: {result.result_rows[0][0]:,}')
print(f'Block range: {result.result_rows[0][1]:,} to {result.result_rows[0][2]:,}')
"

Expected Output (healthy):

Total blocks: 23,800,000+
Block range: 1 to 23,800,000+

4. Detect Gaps (If Needed)

Run gap detection to ensure zero missing blocks. The gap monitor Cloud Function runs every 3 hours automatically. For manual checks:

# Trigger manual gap check via Cloud Scheduler
gcloud scheduler jobs run motherduck-monitor-trigger --location=us-east1

# View logs
gcloud functions logs read motherduck-gap-detector --region=us-east1 --gen2 --limit=50

5. Handle Specific Year Range (Partial Backfill)

Load specific years only:

# Load only 2023-2024
cd deployment/backfill
./chunked_backfill.sh 2023 2024

Use Cases:

Filling detected gaps in specific years
Testing backfill process on small range
Recovering from failed backfill (resume from specific year)

Memory Management

Cloud Run Limits

Constraint: 4GB memory limit per Cloud Run Job execution

Solution: 1-year chunks keep memory usage <4GB per execution

What Happens on OOM:

Cloud Run Job exits with code 137 (SIGKILL)
Error: "Memory limit exceeded"
Solution: Reduce chunk size or increase Cloud Run memory allocation

Local Execution

For local testing with memory constraints:

# Validate memory requirements before execution
.claude/skills/historical-backfill-execution/scripts/validate_chunk_size.py --year 2020

Output:

Estimating memory for 2020 backfill...
Block count: ~2,600,000
Column count: 11 (optimized schema)
Expected memory: ~3.2 GB
Cloud Run safe: (under 4GB limit)

Troubleshooting

See Troubleshooting Reference for complete guide.

Common Issues:

Issue	Cause	Solution
OOM error (code 137)	Chunk too large	Reduce year range (6 months instead of 1 year)
BigQuery quota exceeded	>1TB in 30 days	Wait for quota reset or reduce query frequency
"Permission denied"	Missing IAM roles	Grant `roles/bigquery.user` to service account
"Table not found"	Wrong dataset	Verify `bigquery-public-data.crypto_ethereum.blocks`
Slow execution (>5min/year)	Network issues	Check Cloud Run region (use same as BigQuery: `us`)

Backfill Patterns

See Backfill Patterns Reference for alternatives and rationale.

Pattern Comparison:

Pattern	Chunk Size	Memory	Time (10yr)	Retry Granularity	Recommended
1-Year Chunks	~2.6M blocks	<4GB	20 min	Year-level	Yes (canonical)
Month Chunks	~220K blocks	<1GB	35 min	Month-level	Over-chunked (slower)
Full Load	26M blocks	>8GB	N/A	All-or-nothing	No (OOM errors)

Operational History

Complete Historical Backfill (2025-11-10):

Goal: Load 23.8M Ethereum blocks (2015-2025)
Method: 1-year chunked backfill via chunked_backfill.sh
Execution Time: ~20 minutes total
Memory Usage: <4GB per chunk (no OOM errors)
Result: 100% completeness, zero gaps, zero duplicates
Verification: 23.8M blocks in ClickHouse Cloud, latest block within 60 seconds
Cost: $0 (within BigQuery free tier)

SLO Achievement: Complete historical data collection (10 years, 23.8M blocks) in <30 minutes with zero manual intervention.

Scripts

validate_chunk_size.py - Estimate memory requirements before execution
chunked_executor.sh - Wrapper for deployment/backfill/chunked_backfill.sh with validation

References

backfill-patterns.md - 1-year chunking rationale, comparison with alternatives
troubleshooting.md - OOM errors, retry strategies, Cloud Run logs analysis

同仓库更多 Skills

同仓库

service-monitoring-setup

terrylica/gapless-network-data

Autonomous setup and management of external service monitoring using UptimeRobot (HTTP endpoint monitoring) and Healthchecks.io (heartbeat/Dead Man's Switch monitoring). Use when setting up monitoring for Cloud Run Jobs, VM services, or investigating monitoring configuration issues. Includes Pushover integration, validated API patterns, and dual-service architecture.

2025-12-020

bigquery-ethereum-data-acquisition

terrylica/gapless-network-data

Workflow for acquiring historical Ethereum blockchain data using Google BigQuery free tier. Empirically validated for cost estimation, streaming downloads, and DuckDB integration. Use when planning bulk historical data acquisition or comparing data source options for blockchain network metrics.

2025-12-020

blockchain-data-collection-validation

terrylica/gapless-network-data

Empirical validation workflow for blockchain data collection pipelines before production implementation. Use when validating data sources, testing DuckDB integration, building POC collectors, or verifying complete fetch-to-storage pipelines for blockchain data.

2025-12-020

blockchain-rpc-provider-research

terrylica/gapless-network-data

Systematic workflow for researching and validating blockchain RPC providers. Use when evaluating RPC providers for historical data collection, rate limits, archive access, compute unit costs, or timeline estimation for large-scale blockchain data backfills.

2025-12-020

vm-infrastructure-ops

terrylica/gapless-network-data

Troubleshoot and manage GCP e2-micro VM running eth-realtime-collector. Use when VM service is down, systemd failures occur, real-time data stream stops, or VM network issues arise. Keywords systemd, journalctl, eth-collector, gcloud compute.

2025-11-290

data-pipeline-monitoring

terrylica/gapless-network-data

Monitor and troubleshoot dual-pipeline data collection systems on GCP. This skill should be used when checking pipeline health, viewing logs, diagnosing failures, or monitoring long-running operations for data collection workflows. Supports Cloud Run Jobs (batch pipelines) and VM systemd services (real-time streams).

2025-11-290

来源

terrylica

terrylica/gapless-network-data

打开 GitHub 仓库查看创作者相关仓库

安装命令

下载

在 Manus 中运行

适用职业SOC

软件开发工程师计算机与数学类职业15-1252L4

name	historical-backfill-execution
description	Execute chunked historical blockchain data backfills using canonical 1-year pattern. Use when loading multi-year historical data, filling gaps in ClickHouse, or preventing OOM failures on Cloud Run. Keywords chunked_backfill.sh, BigQuery historical, gap filling, memory-safe backfill.

Historical Backfill Execution

Version: 2.0.0 Last Updated: 2025-11-29 Purpose: Execute chunked historical blockchain data backfills using canonical 1-year pattern

When to Use

Use this skill when:

Loading multi-year historical data (e.g., 2015-2025 Ethereum blocks, 23.8M blocks total)
Gaps detected in ClickHouse requiring backfill
Preventing OOM (Out of Memory) failures on Cloud Run (4GB memory limit)
Need to execute complete historical data collection
Keywords: chunked_backfill.sh, BigQuery historical, gap filling, memory-safe backfill

Canonical Pattern (Established 2025-11-10)

Empirically Validated Approach:

Chunk size: 1 year (~2.6M blocks for Ethereum)
Memory usage: <4GB per chunk (Cloud Run safe, no OOM errors)
Execution time: ~1m40s-2m per chunk (total ~20 minutes for 10 years)
Idempotency: ReplacingMergeTree allows safe re-runs (automatic deduplication)
Cost: $0/month (within BigQuery 1TB/month free tier, ~10MB per query)

Why 1-Year Chunks?

See Backfill Patterns Reference for complete rationale (memory constraints, retry granularity, progress tracking).

Prerequisites

GCP project access: eonlabs-ethereum-bq
BigQuery dataset: bigquery-public-data.crypto_ethereum.blocks
ClickHouse Cloud database: ethereum_mainnet.blocks
ClickHouse credentials configured (via Doppler or environment variable)
Python dependencies: google-cloud-bigquery, pyarrow, clickhouse-connect

Workflow

1. Execute Chunked Backfill

Run the canonical 1-year chunking script:

cd /deployment/backfill
./chunked_backfill.sh 2015 2025

What This Does:

Loads blocks year-by-year from BigQuery
Uses PyArrow for efficient data transfer
Inserts into ClickHouse with ReplacingMergeTree (automatic deduplication)
Shows progress for each year: "Loading blocks 1 - 2,600,000"

Alternative (use provided validation wrapper):

.claude/skills/historical-backfill-execution/scripts/chunked_executor.sh 2015 2025

2. Monitor Progress

Watch the output for year-by-year progress:

Loading blocks for year 2015...
Year 2015: Loading blocks 1 - 2,600,000
Completed 2015 in 1m42s

Loading blocks for year 2016...
Year 2016: Loading blocks 2,600,001 - 5,200,000
Completed 2016 in 1m38s

...

All years completed successfully!
Total blocks loaded: 23,800,000
Total time: 18m45s

3. Verify Completeness

After backfill completes, verify all blocks loaded:

cd 
doppler run --project aws-credentials --config prd -- python3 -c "
import clickhouse_connect
import os
client = clickhouse_connect.get_client(
    host=os.environ['CLICKHOUSE_HOST'],
    port=8443,
    username='default',
    password=os.environ['CLICKHOUSE_PASSWORD'],
    secure=True
)
result = client.query('SELECT COUNT(*) as total, MIN(number) as min_block, MAX(number) as max_block FROM ethereum_mainnet.blocks FINAL')
print(f'Total blocks: {result.result_rows[0][0]:,}')
print(f'Block range: {result.result_rows[0][1]:,} to {result.result_rows[0][2]:,}')
"

Expected Output (healthy):

Total blocks: 23,800,000+
Block range: 1 to 23,800,000+

4. Detect Gaps (If Needed)

Run gap detection to ensure zero missing blocks. The gap monitor Cloud Function runs every 3 hours automatically. For manual checks:

# Trigger manual gap check via Cloud Scheduler
gcloud scheduler jobs run motherduck-monitor-trigger --location=us-east1

# View logs
gcloud functions logs read motherduck-gap-detector --region=us-east1 --gen2 --limit=50

5. Handle Specific Year Range (Partial Backfill)

Load specific years only:

# Load only 2023-2024
cd deployment/backfill
./chunked_backfill.sh 2023 2024

Use Cases:

Filling detected gaps in specific years
Testing backfill process on small range
Recovering from failed backfill (resume from specific year)

Memory Management

Cloud Run Limits

Constraint: 4GB memory limit per Cloud Run Job execution

Solution: 1-year chunks keep memory usage <4GB per execution

What Happens on OOM:

Cloud Run Job exits with code 137 (SIGKILL)
Error: "Memory limit exceeded"
Solution: Reduce chunk size or increase Cloud Run memory allocation

Local Execution

For local testing with memory constraints:

# Validate memory requirements before execution
.claude/skills/historical-backfill-execution/scripts/validate_chunk_size.py --year 2020

Output:

Estimating memory for 2020 backfill...
Block count: ~2,600,000
Column count: 11 (optimized schema)
Expected memory: ~3.2 GB
Cloud Run safe: (under 4GB limit)

Troubleshooting

See Troubleshooting Reference for complete guide.

Common Issues:

Issue	Cause	Solution
OOM error (code 137)	Chunk too large	Reduce year range (6 months instead of 1 year)
BigQuery quota exceeded	>1TB in 30 days	Wait for quota reset or reduce query frequency
"Permission denied"	Missing IAM roles	Grant `roles/bigquery.user` to service account
"Table not found"	Wrong dataset	Verify `bigquery-public-data.crypto_ethereum.blocks`
Slow execution (>5min/year)	Network issues	Check Cloud Run region (use same as BigQuery: `us`)

Backfill Patterns

See Backfill Patterns Reference for alternatives and rationale.

Pattern Comparison:

Pattern	Chunk Size	Memory	Time (10yr)	Retry Granularity	Recommended
1-Year Chunks	~2.6M blocks	<4GB	20 min	Year-level	Yes (canonical)
Month Chunks	~220K blocks	<1GB	35 min	Month-level	Over-chunked (slower)
Full Load	26M blocks	>8GB	N/A	All-or-nothing	No (OOM errors)

Operational History

Complete Historical Backfill (2025-11-10):

Goal: Load 23.8M Ethereum blocks (2015-2025)
Method: 1-year chunked backfill via chunked_backfill.sh
Execution Time: ~20 minutes total
Memory Usage: <4GB per chunk (no OOM errors)
Result: 100% completeness, zero gaps, zero duplicates
Verification: 23.8M blocks in ClickHouse Cloud, latest block within 60 seconds
Cost: $0 (within BigQuery free tier)

SLO Achievement: Complete historical data collection (10 years, 23.8M blocks) in <30 minutes with zero manual intervention.

Scripts

validate_chunk_size.py - Estimate memory requirements before execution
chunked_executor.sh - Wrapper for deployment/backfill/chunked_backfill.sh with validation

References

backfill-patterns.md - 1-year chunking rationale, comparison with alternatives
troubleshooting.md - OOM errors, retry strategies, Cloud Run logs analysis

historical-backfill-execution

Historical Backfill Execution

When to Use

Canonical Pattern (Established 2025-11-10)

Prerequisites

Workflow

1. Execute Chunked Backfill

2. Monitor Progress

3. Verify Completeness

4. Detect Gaps (If Needed)

5. Handle Specific Year Range (Partial Backfill)

Memory Management

Cloud Run Limits

Local Execution

Troubleshooting

Backfill Patterns

Operational History

Related Documentation

Scripts

References

Historical Backfill Execution

When to Use

Canonical Pattern (Established 2025-11-10)

Prerequisites

Workflow

1. Execute Chunked Backfill

2. Monitor Progress

3. Verify Completeness

4. Detect Gaps (If Needed)

5. Handle Specific Year Range (Partial Backfill)

Memory Management

Cloud Run Limits

Local Execution

Troubleshooting

Backfill Patterns

Operational History

Related Documentation

Scripts

References