В DuckDB можно хранить и даже обрабатывать видео. В этой статье мы рассмотрим, как это сделать.

«Ваши учёные были так заняты вопросом, могут ли они,
что не остановились подумать, стоит ли это делать».
Доктор Иэн Малкольм, «Парк юрского периода» (1993)

В команде DuckDB мы обожаем таблицы. Таблицы — это вечная изящная абстракция, которая появилась примерно на тысячу лет раньше литературы. А реляционные таблицы в частности способны представить вообще любую мыслимую информацию. Но то, что что-то можно сделать, ещё не означает, что это хорошая идея. Можно ли построить ракету на тяге ядерной цепной реакции, которая облучает землю по траектории полёта? Да. Стоит ли? Скорее всего, нет.

Дисклеймер

Массивоподобные данные вроде изображений и видео — классический пример из учебника того, чему хранение в базе данных может быть мало полезно. Да, любые двоичные данные можно положить в таблицу в виде BLOB, но добавленная ценность от этого невелика. Конечно, так сложнее «потерять» картинку, чем в индустриальном стандарте, где в базе лежит имя файла, указывающее на изображение. Но содержательных операций над BLOB, кроме «сохранить» и «загрузить», не так уж много. Не прикрутив какой-нибудь новомодный ИИ-инструмент, вы даже не сможете спросить у базы данных, что изображено на картинке.

У массивных данных есть и свой мир — узкоспециализированные форматы файлов и алгоритмы сжатия. Взять хотя бы повсеместный стандарт MPEG-4 для хранения видео. Это приближённые (неточные, с потерями) форматы, спроектированные с оглядкой на модели человеческого восприятия — поэтому они могут не хранить то, чего люди всё равно не замечают. Отсюда и впечатляющие коэффициенты сжатия: двухчасовой фильм в «Full» HD при кодировании в MPEG-4 ужимается примерно до 2 ГБ.

Игнорируя дисклеймер

Но каково это — превратить фильм в таблицу? Если говорить примитивно, фильм — это всего лишь последовательность быстро сменяющихся картинок («кадров»), обычно порядка 25 кадров в секунду. На такой скорости наш обезьяний мозг уже не различает отдельные изображения и «обманывается», воспринимая их как плавное движение. Ремарка для молодого поколения: киноплёнка — лента с последовательностью кадров — способ, которым фильмы распространяли на протяжении более ста лет.

Итак, у нас есть последовательность картинок. Каждую картинку можно дальше разложить на двумерный массив (то есть «матрицу») точек — так называемых «пикселей». Каждый пиксель, в свою очередь, состоит из трёх чисел: по одному на интенсивность красного, зелёного и синего, или сокращённо RGB. Заметим, что в этом посте мы игнорируем аудио-дорожки, но в принципе всё работало бы точно так же — просто с другим типом «интенсивности».

Дополнительная сложность в том, что реляционная модель (что общеизвестно) не требует абсолютного порядка записей. Значит, все возможные смещения и координаты нужно сделать явными, чтобы не потерять информацию. Это, разумеется, резко увеличивает размер набора данных. В итоге у нас получается таблица примерно такого вида:

У нас есть смещение по времени, или номер кадра i, есть x и y для позиции пикселя внутри кадра, и r, g, b для цветовых компонентов: красной, зелёной и синей. Довольно замороченно.

Но теперь фильм — это просто одна таблица. Если бы у нас был привычный и гарантированный полный порядок строк, то теоретически можно было бы выкинуть все столбцы, кроме r, g и b: при известном разрешении все остальные значения можно восстановить. Забавно, но именно так и устроено хранение данных в реальных видеофайлах, если не учитывать сжатие. Это ещё одна причина, почему реляционные таблицы, возможно, не лучшее «местонахождение» для фильма — но если у вас в руках только молоток… Мы могли бы воспользоваться и более современными возможностями SQL и хранить вложенные поля (в DuckDB это LIST), но давайте останемся в рамках таблицы, с которой справился бы даже System R. К тому же явные смещения избавляют от туманных договорённостей или дополнительной метаинформации о том, в каком порядке по осям были сериализованы массивные данные.

Эксперименты

Чтобы продвинуться в исследовании этой нелепой идеи (во имя науки!), мы конвертируем классику 1963 года — «Шарада» (Charade), «романтическую эксцентричную комедию-детектив» с Одри Хепбёрн и Кэри Грантом — в таблицу DuckDB. Я выбрал этот фильм потому, что он случайно оказался в общественном достоянии из-за ошибки в формулировке копирайт-уведомления (да-да, это правда). Поэтому его действительно можно свободно скачать из Internet Archive.

Поскольку мы просто создаём таблицу, будем использовать родной формат хранения DuckDB. Ниже — полный фрагмент кода, которым мы конвертировали фильм. По сути, этот код достаточно универсален, чтобы превратить в таблицу всё, что умеет читать ffmpeg. На случай, если вы захотите попробовать это дома на своих фильмах.

import imageio
import duckdb

# настройка чтения видео
vid = imageio.get_reader("Charade-1963.mp4", "ffmpeg")
dim_x = vid.get_meta_data()['size'][0]
dim_y = vid.get_meta_data()['size'][1]
rows_per_frame = dim_y * dim_x

# настройка базы и таблицы DuckDB
con = duckdb.connect()
con.execute("ATTACH 'charade.duckdb' AS m (STORAGE_VERSION 'latest'); USE m;")
con.execute("CREATE TABLE movie (i BIGINT, y USMALLINT, x USMALLINT, r UTINYINT, g UTINYINT, b UTINYINT)")

# эти смещения не меняются между кадрами, поэтому заранее их вычислим
con.execute("CREATE TEMPORARY TABLE y AS SELECT unnest(list_sort(repeat(range(?), ?))) y", [dim_y, dim_x])
con.execute("CREATE TEMPORARY TABLE x AS SELECT unnest(repeat(range(?), ?)) x", [dim_x, dim_y])

# проходим по каждому кадру фильма и вставляем данные по пикселям
for i_idx, im in enumerate(vid):
    v = im.flatten()
    r = v[0:len(v):3]
    g = v[1:len(v):3]
    b = v[2:len(v):3]

    con.execute('''INSERT INTO movie 
        FROM repeat(?, ?) i -- смещение кадра
        POSITIONAL JOIN   y -- временная таблица
        POSITIONAL JOIN   x -- временная таблица
        POSITIONAL JOIN   r -- сканирование NumPy-массива
        POSITIONAL JOIN   g -- сканирование NumPy-массива
        POSITIONAL JOIN   b -- сканирование NumPy-массива
        ''', [i_idx, rows_per_frame])

Этот скрипт использует не одну, а как минимум две классные возможности DuckDB. Во-первых, мы применяем так называемые подстановочные сканирования (replacement scans), чтобы напрямую выполнять запросы к NumPy-массивам r, g и b. Обратите внимание: они не создавались в DuckDB как таблицы и никак не регистрировались, но на них ссылаются по имени в INSERT. Дальше DuckDB просто просматривает контекст Python в поисках «недостающих таблиц» и находит объекты с такими именами, которые умеет читать. Вторая классная штука — POSITIONAL JOIN: он позволяет «складывать» несколько таблиц горизонтально по позиции, не выполняя реальный (и дорогой) JOIN. Так мы собираем все столбцы, которые нужны для одного кадра, в рамках одного пакетного INSERT, и он выполняется довольно эффективно.

У нас есть видеофайл с частотой 25 кадров в секунду и (примерно DVD-шным) разрешением 720×392 пикселя. Общая длительность — 01:53:02.56, что даёт 169 563 отдельных кадра. Поскольку у нас по строке на каждый пиксель, получаем 169 563 * 720 × 392 строк, то есть 47 857 461 120. 47 миллиардов строк! Наконец-то настоящие Big Data! При хранении в виде базы DuckDB размер файла, однако, «всего лишь» около 200 ГБ. Для ноутбука — вполне подъёмно!

Лёгкое сжатие DuckDB здесь работает довольно неплохо, учитывая, что в наивном (простом, без оптимизаций) двоичном формате нам пришлось бы хранить как минимум 15 байт на строку. Если умножить это на количество строк (47 миллиардов, напоминаю), получилось бы примерно 700 ГБ для такого гипотетического наивного формата.

Разумеется, превращая данные в реляционную таблицу, мы добавляем кучу информации, которая раньше была неявной — из-за отсутствия порядка в отношениях. Если бы мы просто сохраняли «сырые» байты пикселей — например, как неявно упорядоченную последовательность BMP-файлов (bitmap), — то получили бы объём примерно равный числу строк выше, умноженному на три, то есть 133 ГБ. Даже если явно материализовать все смещения, файл DuckDB всё равно получается сопоставимого размера (200 ГБ). И, конечно, сравнивать размер таблицы с версией фильма в MPEG-4 не совсем честно, потому что MPEG-4 — формат сжатия с потерями. Базы данных не могут просто так взять и «по настроению» пожертвовать численной точностью данных, которые они хранят!

Чтобы доказать, что преобразование получилось точным, попробуем превратить табличные данные для одного случайного кадра обратно в пригодное для человека изображение: выгрузим соответствующие строки из DuckDB и с помощью небольшой магии Python снова соберём из них PNG-файл:

import duckdb
import numpy as np
import PIL.Image

frame = 48000

con = duckdb.connect('charade.duckdb', read_only=True)
dim_y, dim_x = con.execute("SELECT max(y) + 1 dim_y, max(x) + 1 dim_x FROM movie WHERE i=0").fetchone()

res = con.execute("SELECT r, g, b FROM movie WHERE i = ? ORDER BY y, x", [frame]).fetchnumpy()
v = np.zeros(dim_y * dim_x * 3, dtype=np.uint8)
v[0:len(v):3] = res['r']
v[1:len(v):3] = res['g']
v[2:len(v):3] = res['b']

img = PIL.Image.fromarray(v.reshape((dim_y, dim_x, 3)))
img.save(f'frame.png')

И вуаля — на экране появляется замечательный кадр с Одри и Кэри. Этот приём можно использовать и для того, чтобы собрать последовательность картинок и снова записать её в файл MPEG-4 — например, с помощью библиотеки moviepy.

Но раз у нас теперь есть таблица, можно немного поэкспериментировать. Для начала проведём базовую разведку: начнём с DESCRIBE, который по сути показывает схему. Что, конечно, и так было нам известно.

DESCRIBE movie;

Ничего неожиданного. А сколько тут строк?

FROM movie SELECT count(*);

Да, 47 миллиардов. А каковы численные характеристики столбцов? В DuckDB есть удобная команда SUMMARIZE, которая за один проход вычисляет сводную статистику по таблице (или произвольному запросу).

SUMMARIZE movie;

Признаюсь, тут есть лёгкий элемент хвастовства. DuckDB умеет посчитать развёрнутую сводную статистику по всем 47 миллиардам строк примерно за 20 минут на MacBook. Вот результаты:

Раз уж по сути мы храним огромное количество цветов, сколько вообще разных комбинаций красного, зелёного и синего у нас получится, DuckDB?

FROM (FROM movie SELECT DISTINCT r, g, b)
SELECT count(*);

Любой опытный дата-инженер совершенно справедливо предостережёт вас от DISTINCT на таком объёме строк. Слишком уж много было аварий в продакшене из-за «раздувшихся» агрегаций. Но благодаря хеш-таблице для агрегаций в DuckDB, которая умеет работать с объёмом больше доступной памяти, этот запрос можно запускать вполне уверенно. Мы даже получаем симпатичный прогресс-бар и (начиная с DuckDB 1.4.0) удивительно точную оценку того, сколько времени займёт выполнение.

То есть примерно 800 тысяч разных цветов. В итоге это посчиталось примерно за 2 минуты. Но как распределены эти цвета по частотам? Давайте построим гистограмму из 10 самых часто встречающихся цветов!

FROM movie
SELECT r, g, b, count(*) AS ct
GROUP BY ALL
ORDER BY ct DESC
LIMIT 10;

Похоже, самые распространённые цвета здесь — тёмные оттенки серого. Логично! Только имейте в виду, что сжатие MPEG-4 выполняется с потерями и, вероятно, даёт местами странные цвета как артефакты округления.

Но можно пойти дальше и поэкспериментировать ещё. У нас вообще-то аналитическая СУБД. Как насчёт того, чтобы посчитать средний кадр для каждой тысячи кадров и затем «сшить» результаты обратно в фильм? По сути это просто большая агрегация. Для начала посчитаем сами средние значения:

CREATE TABLE averages AS
    FROM movie
    SELECT
        i // 1000 AS idx,
        y,
        x,
        avg(r)::UTINYINT AS r,
        avg(g)::UTINYINT AS g,
        avg(b)::UTINYINT AS b
GROUP BY ALL
ORDER BY idx, y, x;

Затем мы снова используем Python, чтобы превратить эту таблицу averages обратно в фильм:

# некоторую подготовку опустили

# выгружаем пачку кадров одним запросом
res = con.execute("SELECT r, g, b FROM averages ORDER BY i, y, x").fetchnumpy()

# снова делим RGB-массивы по кадрам
r_splits = np.split(res['r'], num_frames)
g_splits = np.split(res['g'], num_frames)
b_splits = np.split(res['b'], num_frames)

# генерируем изображения
image_files = []
for i in range(num_frames):
    v = np.zeros(dim_y * dim_x * 3, dtype=np.uint8)
    v[0:len(v):3] = r_splits[i]
    v[1:len(v):3] = g_splits[i]
    v[2:len(v):3] = b_splits[i]
    image_files.append(v.reshape((dim_y, dim_x, 3), order='C'))

# записываем видеофайл
clip = moviepy.video.io.ImageSequenceClip.ImageSequenceClip(image_files, fps=25)
clip.write_videofile('averages.mp4')

Здесь приходится немного повозиться, потому что мы хотим вытащить весь набор кадров одним махом, а не выполнять запрос для каждого кадра по отдельности. Дальше мы используем NumPy, чтобы нарезать данные обратно на кадры и «сшить» RGB-каналы в трёхмерный массив, который ожидают библиотеки для работы с изображениями. Никакой практической пользы это не даёт, но результат получается забавным — вот, например, средний кадр №68 (заранее приносим извинения актёрам):

Мы можем также «сшить» все усреднённые кадры вместе и получить дёрганый «средний» фильм:

Нажмите здесь, чтобы посмотреть дёрганый фильм, сгенерированный из «Шарады» (осторожно, картинка мельтешит):

Чтобы совсем повеселиться, можно даже написать SQL-запрос, который превращает кадр в HTML-таблицу, где каждая ячейка — один пиксель. Ниже — результат; будем надеяться, что ваш браузер сумеет это отрисовать, и ещё раз поблагодарим Cloudflare за спонсирование нашего трафика. Вот этот несколько кощунственный запрос, который всё это генерирует:

SELECT '<html><body><table style="padding:0px; margin: 0px; border-collapse: collapse;">';
FROM movie
SELECT
    IF(x = 0, '<tr>', '') ||
    printf('<td style="background-color: #%02x%02x%02x; height: 1px; width: 1px"; ></td>', r, g, b) ||
    IF(x = 719, '</tr>', '')
WHERE i = 48000
ORDER BY y, x;
SELECT '</table></body></html>';

Результат можно посмотреть в movies-table.html (имейте в виду: файл весит 20 МБ и отрисовывает каждый пиксель кадра как отдельную ячейку таблицы).

Заключение

Наверное, вы уже поняли, что эта статья не совсем серьезная. Мы неплохо повеселились. Но что мы узнали? Несколько вещей. Во-первых, по сути что угодно можно представить в виде таблицы — даже какой-нибудь малоизвестный фильм, скажем, 1966 года. В общем и целом, это, вероятно, не лучшая идея: есть отличные библиотеки с открытым исходным кодом вроде ffmpeg и приложения вроде VLC для работы с видеофайлами — и то же самое относится к их «массивным родственникам», в которых лежит музыка или просто изображения. Несмотря на чудовищное раздувание данных и миллиарды строк, DuckDB справился с этим на удивление хорошо — и его формат хранения, и движок выполнения запросов. 

Если идея «всё — таблица» кажется вам не шуткой, а рабочей моделью мышления, логичным продолжением становится глубокое понимание самой СУБД. На курсе по PostgreSQL будем разбирать, как проектировать и эксплуатировать базы без магии и героизма: от производительности и индексов до блокировок, бэкапов и работы с большими объёмами данных. Пройдите вступительный тест, чтобы узнать, подойдет ли вам программа курса.

Чтобы узнать больше о формате обучения и познакомиться с преподавателями, приходите на бесплатные демо-уроки:

• 14 января 18:30. «Улица разбитых кластеров: про бэкапы и реплики в PostgreSQL». Записаться

• 15 января 20:00. «Топ-5 SQL инструментов, которые используют аналитики каждый день». Записаться

• 27 января 19:00. «Percona XtraDB Cluster: создаём базу данных, которая не падает». Записаться