Video Audio Track Extraction

Objective

Extract audio tracks from video files for independent processing. This enables transcription, audio analysis, multi-language dubbing, and accessibility features.

Step 1: Create Video Table

Create a table for videos with audio metadata.

CREATE TABLE videos_with_audio (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    duration_seconds INTEGER,
    has_audio BOOLEAN DEFAULT TRUE,
    audio_channels INTEGER DEFAULT 2,
    audio_sample_rate INTEGER DEFAULT 48000,
    audio_codec VARCHAR(50) DEFAULT 'AAC',
    language VARCHAR(10) DEFAULT 'en',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Extracted Audio Table

Create a table for extracted audio tracks.

CREATE TABLE extracted_audio (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    audio_file AUDIO(MP3),
    audio_format VARCHAR(10) DEFAULT 'mp3',
    track_type VARCHAR(50) DEFAULT 'main',
    language VARCHAR(10),
    bitrate INTEGER,
    sample_rate INTEGER,
    channels INTEGER,
    duration_seconds INTEGER,
    file_size BIGINT,
    is_original BOOLEAN DEFAULT TRUE,
    extraction_status VARCHAR(20) DEFAULT 'pending',
    extracted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES videos_with_audio(id)
);

Step 3: Insert Sample Videos

Add sample video records.

INSERT INTO videos_with_audio (id, title, duration_seconds, has_audio, audio_channels, audio_sample_rate, audio_codec, language) VALUES
    (1, 'Corporate Presentation', 1200, TRUE, 2, 48000, 'AAC', 'en'),
    (2, 'Product Demo', 600, TRUE, 2, 44100, 'AAC', 'en'),
    (3, 'Silent Animation', 120, FALSE, 0, 0, NULL, NULL),
    (4, 'Interview Recording', 1800, TRUE, 1, 48000, 'AAC', 'en'),
    (5, 'Music Video', 240, TRUE, 2, 48000, 'AAC', 'en'),
    (6, 'Documentary Clip', 900, TRUE, 6, 48000, 'AC3', 'en'),
    (7, 'Tutorial Series Ep1', 1500, TRUE, 2, 44100, 'AAC', 'es');

Step 4: Insert Extracted Audio

Add extracted audio track records.

INSERT INTO extracted_audio (id, video_id, audio_format, track_type, language, bitrate, sample_rate, channels, duration_seconds, file_size, is_original, extraction_status) VALUES
    -- Video 1 audio tracks
    (1, 1, 'mp3', 'main', 'en', 192000, 48000, 2, 1200, 28800000, TRUE, 'completed'),
    (2, 1, 'wav', 'lossless', 'en', 1536000, 48000, 2, 1200, 230400000, TRUE, 'completed'),
    -- Video 2 audio
    (3, 2, 'mp3', 'main', 'en', 128000, 44100, 2, 600, 9600000, TRUE, 'completed'),
    -- Video 4 audio (mono interview)
    (4, 4, 'mp3', 'main', 'en', 96000, 48000, 1, 1800, 21600000, TRUE, 'completed'),
    (5, 4, 'wav', 'lossless', 'en', 768000, 48000, 1, 1800, 172800000, TRUE, 'completed'),
    -- Video 5 audio (music)
    (6, 5, 'mp3', 'main', 'en', 320000, 48000, 2, 240, 9600000, TRUE, 'completed'),
    (7, 5, 'flac', 'lossless', 'en', 1000000, 48000, 2, 240, 30000000, TRUE, 'completed'),
    -- Video 6 audio (surround)
    (8, 6, 'mp3', 'stereo_mixdown', 'en', 256000, 48000, 2, 900, 28800000, FALSE, 'completed'),
    (9, 6, 'ac3', 'surround', 'en', 640000, 48000, 6, 900, 72000000, TRUE, 'completed'),
    -- Video 7 audio (Spanish)
    (10, 7, 'mp3', 'main', 'es', 192000, 44100, 2, 1500, 36000000, TRUE, 'completed');

Step 5: Query Videos with Audio

Get videos that have audio content.

SELECT
    title,
    duration_seconds / 60 as minutes,
    audio_channels,
    audio_sample_rate,
    audio_codec,
    language
FROM videos_with_audio
WHERE has_audio = TRUE
ORDER BY title;

Step 6: Get Audio Tracks for Video

Retrieve all audio tracks for a specific video.

SELECT
    v.title,
    ea.track_type,
    ea.audio_format,
    ea.language,
    ea.bitrate / 1000 as bitrate_kbps,
    ea.channels,
    ea.file_size / 1048576 as size_mb
FROM videos_with_audio v
INNER JOIN extracted_audio ea ON v.id = ea.video_id
WHERE v.id = 1
ORDER BY ea.track_type;

Step 7: Compare Format Sizes

Compare audio format file sizes.

SELECT
    v.title,
    ea.audio_format,
    ea.bitrate / 1000 as bitrate_kbps,
    ea.file_size / 1048576 as size_mb,
    ea.duration_seconds
FROM videos_with_audio v
INNER JOIN extracted_audio ea ON v.id = ea.video_id
WHERE ea.extraction_status = 'completed'
ORDER BY v.title, ea.file_size;

Step 8: Audio Storage Summary

Calculate storage usage by format.

SELECT
    audio_format,
    COUNT(*) as track_count,
    SUM(file_size) / 1073741824 as total_gb,
    AVG(bitrate) / 1000 as avg_bitrate_kbps,
    SUM(duration_seconds) / 3600 as total_hours
FROM extracted_audio
WHERE extraction_status = 'completed'
GROUP BY audio_format
ORDER BY total_gb DESC;

Step 9: Find High Quality Audio

Get lossless or high bitrate tracks.

SELECT
    v.title,
    ea.audio_format,
    ea.track_type,
    ea.bitrate / 1000 as bitrate_kbps,
    ea.sample_rate,
    ea.channels
FROM videos_with_audio v
INNER JOIN extracted_audio ea ON v.id = ea.video_id
WHERE ea.bitrate >= 256000
   OR ea.audio_format IN ('wav', 'flac')
ORDER BY ea.bitrate DESC;

Step 10: Multi-Channel Audio

Find videos with surround sound.

SELECT
    v.title,
    v.audio_channels as video_channels,
    ea.channels as extracted_channels,
    ea.audio_format,
    ea.track_type
FROM videos_with_audio v
INNER JOIN extracted_audio ea ON v.id = ea.video_id
WHERE v.audio_channels > 2
ORDER BY v.audio_channels DESC;

Step 11: Videos Without Extracted Audio

Find videos needing audio extraction.

SELECT
    v.title,
    v.duration_seconds / 60 as minutes,
    v.audio_codec,
    v.language,
    'Needs extraction' as status
FROM videos_with_audio v
LEFT JOIN extracted_audio ea ON v.id = ea.video_id
WHERE v.has_audio = TRUE
  AND ea.id IS NULL
ORDER BY v.duration_seconds DESC;

Step 12: Audio Track by Language

Get audio tracks grouped by language.

SELECT
    language,
    COUNT(*) as track_count,
    SUM(duration_seconds) / 3600 as total_hours,
    COUNT(DISTINCT video_id) as videos_with_language
FROM extracted_audio
WHERE language IS NOT NULL
GROUP BY language
ORDER BY track_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS extracted_audio;
DROP TABLE IF EXISTS videos_with_audio;

Expected Outcomes

• Audio tracks extracted from videos
• Multiple formats supported
• Quality comparisons work
• Storage tracked by format
• Multi-language support

Audio Format Guide

Key Concepts Learned

• Video audio extraction patterns
• Multiple audio format storage
• Quality and format comparison
• Multi-channel audio handling
• Language-based organization