Week 5 — Data Import & Export: Formats, Tools & ETL¶

LOAD DATA INFILE '/var/lib/mysql-files/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       ESCAPED BY '\\'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES                          -- skip header row
(first_name, last_name, email, gpa, dept_id, enroll_year);

-- LOCAL reads the file from the CLIENT machine (not server filesystem)
-- Requires: mysql --local-infile=1  and  local_infile=ON in my.cnf
LOAD DATA LOCAL INFILE '/home/john/exports/new_students.csv'
INTO TABLE students
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES  TERMINATED BY '\r\n'            -- Windows line endings
IGNORE 1 LINES
(first_name, last_name, @raw_email, @raw_gpa, dept_id, enroll_year)
SET email = LOWER(TRIM(@raw_email)),   -- transform on load
    gpa   = IF(@raw_gpa = '', NULL, CAST(@raw_gpa AS DECIMAL(3,2)));

-- Import enrollment history from legacy system with date conversion
LOAD DATA INFILE '/var/lib/mysql-files/enrollments_legacy.csv'
INTO TABLE enrollments
FIELDS TERMINATED BY '|'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES
(student_id, course_id, @raw_semester, @raw_grade, instructor_id)
SET semester = CASE
                   WHEN @raw_semester LIKE 'FA%' THEN CONCAT('F', SUBSTR(@raw_semester, 3))
                   WHEN @raw_semester LIKE 'SP%' THEN CONCAT('S', SUBSTR(@raw_semester, 3))
                   ELSE @raw_semester
               END,
    grade    = NULLIF(TRIM(@raw_grade), '');

-- IGNORE skips error rows (logs to warning list)
LOAD DATA INFILE '/var/lib/mysql-files/students.csv'
IGNORE                                  -- skip duplicate-key and data-conversion errors
INTO TABLE students
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(first_name, last_name, email, gpa, dept_id, enroll_year);

-- Check what was skipped:
SHOW WARNINGS;

-- REPLACE overwrites existing rows with same unique key
LOAD DATA INFILE '/var/lib/mysql-files/students_update.csv'
REPLACE
INTO TABLE students
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(student_id, first_name, last_name, email, gpa, dept_id, enroll_year);

-- Export current semester enrollments to CSV
SELECT  s.student_id,
        s.first_name,
        s.last_name,
        c.course_code,
        e.semester,
        COALESCE(e.grade, 'IN PROGRESS') AS grade
FROM    students   s
INNER JOIN enrollments e ON e.student_id = s.student_id
INNER JOIN courses     c ON c.course_id  = e.course_id
WHERE   e.semester = 'F2025'
INTO OUTFILE '/var/lib/mysql-files/f2025_enrollments.csv'
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       ESCAPED BY '\\'
LINES  TERMINATED BY '\n';

-- Export with custom delimiter for Excel compatibility (tab-separated)
SELECT  student_id, first_name, last_name, gpa
FROM    students
WHERE   gpa >= 3.5
INTO OUTFILE '/var/lib/mysql-files/honor_roll.tsv'
FIELDS TERMINATED BY '\t'
LINES  TERMINATED BY '\r\n';

-- Inline JSON array → relational rows
SELECT jt.*
FROM JSON_TABLE(
    '[
        {"student_id": 1001, "name": "Alice Chen",  "gpa": 3.9, "dept": "ITEC"},
        {"student_id": 1002, "name": "Bob Martinez", "gpa": 3.5, "dept": "MATH"},
        {"student_id": 1003, "name": "Carol Liu",   "gpa": null, "dept": "ITEC"}
    ]',
    '$[*]' COLUMNS (
        student_id INT         PATH '$.student_id',
        full_name  VARCHAR(100) PATH '$.name',
        gpa        DECIMAL(3,2) PATH '$.gpa'    DEFAULT '0.00' ON EMPTY,
        dept_code  VARCHAR(10)  PATH '$.dept'
    )
) AS jt;

-- Step 1: Load raw JSON text into a staging table
CREATE TABLE json_staging (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    raw_json LONGTEXT,
    loaded_at DATETIME DEFAULT NOW()
);

-- (Load the file content via LOAD DATA or application)

-- Step 2: Shred JSON into target table using JSON_TABLE
INSERT INTO students (first_name, last_name, email, gpa, dept_id)
SELECT  jt.first_name,
        jt.last_name,
        jt.email,
        jt.gpa,
        d.dept_id
FROM    json_staging js
CROSS JOIN JSON_TABLE(
    js.raw_json,
    '$[*]' COLUMNS (
        first_name VARCHAR(50)  PATH '$.first_name',
        last_name  VARCHAR(50)  PATH '$.last_name',
        email      VARCHAR(100) PATH '$.email',
        gpa        DECIMAL(3,2) PATH '$.gpa',
        dept_code  VARCHAR(10)  PATH '$.department'
    )
) AS jt
INNER JOIN departments d ON d.dept_name LIKE CONCAT('%', jt.dept_code, '%')
ON DUPLICATE KEY UPDATE gpa = VALUES(gpa);

-- Flatten nested enrollments array within student JSON
-- Input: {"student_id": 1001, "enrollments": [{"course": "ITEC445", "sem": "F2025"}, ...]}
SELECT  jt.student_id,
        enr.course_code,
        enr.semester
FROM    json_staging js
CROSS JOIN JSON_TABLE(
    js.raw_json,
    '$[*]' COLUMNS (
        student_id  INT  PATH '$.student_id',
        NESTED PATH '$.enrollments[*]' COLUMNS (
            course_code VARCHAR(10) PATH '$.course',
            semester    CHAR(6)     PATH '$.sem'
        )
    )
) AS jt
CROSS JOIN LATERAL (SELECT jt.course_code AS course_code, jt.semester AS semester) AS enr
WHERE jt.student_id IS NOT NULL;

-- Import with date stored as MM/DD/YYYY string
LOAD DATA INFILE '/var/lib/mysql-files/enrollments.csv'
INTO TABLE enrollments
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(student_id, course_id, @raw_date, grade)
SET enrolled_date = STR_TO_DATE(@raw_date, '%m/%d/%Y');

-- Map empty strings and literal "NULL" to SQL NULL
LOAD DATA INFILE '/var/lib/mysql-files/grades.csv'
INTO TABLE enrollments
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(student_id, course_id, semester, @raw_grade)
SET grade = CASE
    WHEN TRIM(@raw_grade) = ''     THEN NULL
    WHEN UPPER(TRIM(@raw_grade)) = 'NULL' THEN NULL
    WHEN UPPER(TRIM(@raw_grade)) = 'N/A'  THEN NULL
    ELSE TRIM(@raw_grade)
END;

-- Always import into a staging table first, validate, then promote
CREATE TABLE enrollment_staging (
    row_id       INT          PRIMARY KEY AUTO_INCREMENT,
    student_id   INT,
    course_id    INT,
    semester     CHAR(6),
    grade        CHAR(2),
    raw_line     TEXT,                     -- original CSV line for debugging
    is_valid     TINYINT      DEFAULT 0,
    error_msg    VARCHAR(500),
    loaded_at    DATETIME     DEFAULT NOW()
);

-- Validate: check referential integrity
UPDATE enrollment_staging s
SET    is_valid  = 0,
       error_msg = 'Unknown student_id'
WHERE  NOT EXISTS (SELECT 1 FROM students WHERE student_id = s.student_id);

UPDATE enrollment_staging s
SET    is_valid  = 0,
       error_msg = CONCAT(COALESCE(error_msg, ''), ' | Unknown course_id')
WHERE  NOT EXISTS (SELECT 1 FROM courses WHERE course_id = s.course_id);

-- Validate: duplicate detection
UPDATE enrollment_staging s
SET    is_valid  = 0,
       error_msg = CONCAT(COALESCE(error_msg, ''), ' | Duplicate enrollment')
WHERE  EXISTS (
    SELECT 1 FROM enrollments e
    WHERE  e.student_id = s.student_id
      AND  e.course_id  = s.course_id
      AND  e.semester   = s.semester
);

-- Mark valid rows
UPDATE enrollment_staging
SET    is_valid = 1
WHERE  is_valid = 0 AND error_msg IS NULL;

-- Promote valid rows to production
INSERT INTO enrollments (student_id, course_id, semester, grade)
SELECT student_id, course_id, semester, grade
FROM   enrollment_staging
WHERE  is_valid = 1;

-- Report results
SELECT
    SUM(is_valid = 1) AS promoted,
    SUM(is_valid = 0) AS rejected
FROM enrollment_staging;

-- Temporarily allow loading without FK checks (faster but risky)
SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE '/var/lib/mysql-files/enrollments_bulk.csv'
INTO TABLE enrollments
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(student_id, course_id, semester, grade);
SET FOREIGN_KEY_CHECKS = 1;

-- Then verify no orphans were introduced:
SELECT COUNT(*) AS orphaned_enrollments
FROM   enrollments e
WHERE  NOT EXISTS (SELECT 1 FROM students s WHERE s.student_id = e.student_id)
   OR  NOT EXISTS (SELECT 1 FROM courses  c WHERE c.course_id  = e.course_id);

-- For MyISAM: disable index maintenance during load
ALTER TABLE enrollments DISABLE KEYS;

LOAD DATA INFILE '/var/lib/mysql-files/enrollments_10M.csv'
INTO TABLE enrollments
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(student_id, course_id, semester, grade, instructor_id);

ALTER TABLE enrollments ENABLE KEYS;   -- rebuilds all secondary indexes at once (faster)

-- For InnoDB: similar effect with:
SET unique_checks   = 0;
SET foreign_key_checks = 0;
-- ... load ...
SET unique_checks   = 1;
SET foreign_key_checks = 1;
ANALYZE TABLE enrollments;             -- update optimizer statistics

-- After any large import, refresh statistics so the optimizer has accurate data
ANALYZE TABLE students, enrollments, courses, instructors, departments;

-- Check table statistics:
SELECT table_name, table_rows, avg_row_length, data_length, index_length
FROM   information_schema.tables
WHERE  table_schema = 'university_db'
ORDER BY data_length DESC;

-- A complete idempotent ETL step: upsert from staging to production
INSERT INTO students (student_id, first_name, last_name, email, gpa, dept_id, enroll_year)
SELECT  s.student_id,
        TRIM(s.first_name),
        TRIM(s.last_name),
        LOWER(TRIM(s.email)),
        CASE WHEN s.gpa BETWEEN 0.0 AND 4.0 THEN s.gpa ELSE NULL END,
        d.dept_id,
        s.enroll_year
FROM    student_staging s
INNER JOIN departments d ON d.dept_name = TRIM(s.dept_name)
WHERE   s.is_valid = 1
ON DUPLICATE KEY UPDATE
    first_name  = VALUES(first_name),
    last_name   = VALUES(last_name),
    email       = VALUES(email),
    gpa         = VALUES(gpa),
    updated_at  = NOW();