From Zero to 10GB: Complete SQL Big Data Pipeline with Python & Shell

1. Test Data Generation (10GB SQL File)

The author provides a Python script generate_log_sql.py that creates a 10GB SQL file ( log_system_10g.sql) containing five related tables for a log system: base_log, user_operation_log, system_perf_log, error_log, and log_archive. The script uses configuration constants to control output size:

TARGET_FILE_SIZE = 10 * 1024 * 1024 * 1024  # 10 GB
BATCH_SIZE = 10000
TOTAL_BATCHES = 100000

Each batch writes INSERT statements for all five tables, maintaining foreign-key relationships. The script monitors file size in real time and stops when the 10 GB target is reached. It also appends UPDATE, DELETE, and cross-database backup statements at the end. The generated schema uses utf8mb4 charset, InnoDB engine, and includes indexes and foreign keys with ON DELETE CASCADE.

2. Extracting a Valid Test Sample

To verify the generated SQL, the author first extracts the first 100 lines with head -n 100 log_system_10g.sql > test.sql. Because the head may cut mid-statement, a Bash repair script ( fix_sql.sh) is provided that:

Backs up the original file.

Finds the last CREATE TABLE and checks if it ends with );; if not, truncates from that line.

Finds the last INSERT INTO and applies the same completeness check.

Ensures the file ends with a semicolon.

The repaired test.sql is then executed with mysql -u root -p < test.sql to confirm syntax correctness.

3. Splitting the 10GB File into Manageable Chunks

A Python class UltimateSQLSplitter reads the large SQL file and splits it into multiple 500 MB parts (configurable) under ./sql_final_parts/. The splitting logic:

Structure file ( 00_structure.sql): extracts all statements up to the first INSERT, adds SET FOREIGN_KEY_CHECKS = 0; and SET NAMES utf8mb4;.

Data files ( 01_data.sql, 02_data.sql, …): each contains complete INSERT statements, prefixed with USE `log_system_prod`; and foreign-key disable.

Final file ( 99_final.sql): re-enables foreign keys, runs ANALYZE TABLE on key tables.

The splitter tracks progress every 10,000 lines or 5 seconds, reporting GB processed. In the example, the 10 GB file produced about 20 data files.

4. Bulk Import with Pure Python (PyMySQL)

The BatchSQLImporterPurePython class imports the split files either sequentially or in parallel (default 4 workers). Key features:

Uses pymysql – no external mysql client required.

Smart SQL statement splitter ( _split_sql_statements) that respects strings, backticks, and both -- and /* */ comments.

Commits every 500 statements to avoid huge transactions.

Ignores tolerable errors ( already exists, Duplicate entry, Cannot drop, doesn't exist).

Progress persisted in import_progress.txt for resume capability.

Generates a retry_failed.py script for any files that error out.

Before import, an optional optimization step runs global MySQL settings via PyMySQL:

SET GLOBAL max_allowed_packet = 256*1024*1024;
SET GLOBAL innodb_buffer_pool_size = 2*1024*1024*1024;
SET GLOBAL innodb_log_file_size = 512*1024*1024;
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
SET GLOBAL sync_binlog = 0;
SET GLOBAL foreign_key_checks = 0;
SET GLOBAL unique_checks = 0;

After import, the script verifies the database log_system_prod exists and counts rows in base_log.

5. Summary of the End-to-End Workflow

Run generate_log_sql.py → produces log_system_10g.sql (~10 GB).

Optionally test with head + fix_sql.sh + mysql < test.sql.

Run UltimateSQLSplitter → creates ./sql_final_parts/ with structure, data, and final files.

Run BatchSQLImporterPurePython (sequential or parallel) → loads all data into MySQL.

Check import_progress.txt and retry_failed.py if any failures.

The author emphasizes that both Shell and Python approaches are valid; the choice depends on the specific environment and preference. All scripts are provided in full for direct reuse and adaptation.