Долгие годы люди стремились к всё более реалистичному изображению окружающих их вещей. Много лет прошло от симпатичных наскальных мамонтов до шедевров эпохи Ренессанса и Просвещения. Однако где‑то в 19-м веке (примерно, когда стала появляться первая фототехника, ага), что‑то пошло не так, и живопись сменила своё направление от реализма к абстракции. Дальше больше; и все «скатилось» до клякс, пятен и потёков, размазанных по холсту или любой другой поверхности стоимостью в миллионы долларов... И при этом зачастую совершенно было непонятно, кто автор «шедевра»: 3-х летний ребенок, маститый художник, нейросеть или кот, опрокинувший банку варенья.

Похожие процессы происходят и в мире данных, синтетические, сгенерированные, абстрактные данные обретают всё большую ценность на рынке. Такие данные являются более безопасными, а также позволяют тестировать системы качественнее и воспроизводить проблемы до их появления в продакшене... А еще делать прогнозы, анализ, безопасно обмениваться и многое другое.

В этом посте мы рассмотрим основные моменты генерации данных с нуля (на основе схемы БД), а так же на основе уже существующих данных. Рассмотрим способы, методы, особенности и инструменты. А каждый шаг будем иллюстрировать примерами живых и настоящих SQL‑запросов (в основном PostgreSQL‑flavour, но постараемся и не только). И в итоге убедимся, что SQL позволяет нам не только эффективно работать с уже существующими данными (на минуточку, уже почти на протяжении 50 лет), но с помощью него их можно еще и довольно эффектно придумывать.

Представим архетипическую ситуацию: на этапе разработки у нас уже есть схема данных, и даже само приложение, но абсолютно нет данных. Разве что несколько пользователей вида «Иванов Иван Иванович» (он же John Doe) и несколько товаров вида «test1», «test2» в корзине. Это усложняет тестирование приложения (интеграционное, нагрузочное и даже приемочное) и понимание того, что вообще это приложение делает и как им пользоваться. Поэтому мы уверены, что еще с ранних этапов разработки необходимо задаться вопросом генерации качественных тестовых данных в достаточном количестве и, конечно же, максимально автоматизировать этот процесс.

И, конечно, в нынешних условиях у нас нет иного выбора, кроме того, как начать с ChatGPT. Вежливо попросим бота сгенерировать данные для тестовой схемы Pagila (о ней мы поговорим подробнее далее):

В итоге мы получим несколько настоящих валидных sql‑скриптов в нужном порядке, но будет необходимо еще дополнительно запросить несколько деталей (как минимум проследить, что учтены все таблицы). С одной стороны, это, конечно, очень круто и уже может быть достаточным для некоторых кейсов; но с другой — существует огромное количество нюансов при генерации (например, определенное распределение данных, близость к предметной области и многое другое), которое будет довольно сложно и трудозатратно объяснить боту. Плюс, наверняка нам понадобится генерировать большой объем данных, причем за очень ограниченное время и для приватных корпоративных схем... Поэтому давайте засучим рукава и пройдемся по всем основным этапам генерации данных с нуля, используя старый добрый SQL (мы же все‑таки в SQL‑хабе).

Генерация строк

Как мы знаем, SQL был задуман как язык для работы с реальными данными, хранящимися в таблицах. Однако в SQL-стандарте далекого 99-го года были представлены рекурсивные запросы, которые позволяют (помимо множества других полезных вещей) сгенерировать произвольное количество строк без обращения к какой-либо конкретной таблице. В то же время различные СУБД могут иметь собственные (зачастую более удобные) конструкции для генерации строк:

-- Standard SQL:1999 way
with recursive tmp (r) as (
  select 0 union all
  select r+1 from tmp
   where r < 365)
select r from tmp

-- PostgreSQL
select generate_series
  from generate_series(1, 365)

-- Oracle
select level
  from dual
connect by level <= 365

Генерация значений

Итак, мы уже умеем генерировать собственно строки, теперь нам нужно чем-то их наполнить. Насколько я знаю, SQL стандарты не предусматривают каких-либо конструкций для генерации случайных данных (почему-то). Однако большинство СУБД имеют свои собственные инструменты для этого. Немного поковырявшись с конкретной СУБД, мы сможем получить любое количество случайных данных, отдаленно похожих на имена, мэйлы, даты, адреса и пр., не выходя из тёплой ламповой консоли. Давайте "нагенерим" тысячу (можем и больше) сотрудников для таблицы employee:

-- PostgreSQL
insert into employee(id, first_name, last_name, 
                     years_of_experience, email, order_date, is_student)
select generate_series
     , md5(random()::text)
     , md5(random()::text)
     , floor(random() * 99)::int
     , md5(random()::text) || '@gmail.com'
     , now() - (random() * (interval '90 days')) 
     , case when random() > 0.5 then true else false end
  from generate_series(1, 1000)

"Не верю!" (с)

Теперь мы можем генерировать тонны каких‑то данных, вставлять их в наши таблицы и, во многих случаях, этого уже будет вполне достаточно. Но классик бы нам не поверил, поэтому давайте попробуем сделать что‑то более похожее на реальные данные, и способов для этого существует огромное количество.

Начнем, пожалуй, с самой популярной задачи — нам нужны настоящие имена и фамилии для наших сотрудников и клиентов. Одно из самых простых и довольно эффективных решений — это заготовить 2 множества (одно с фамилиями, другое с именами) и выполнить декартово произведение (я вдохновлялся вот этим «гистом» на GitHub'е):

-- PostgreSQL
select first_name
     , last_name
  from (select unnest(array['Adam',/*...*/'Susan']) as first_name) as f
 cross join
       (select unnest(array['Matthews',/*...*/'Hancock']) as last_name) as l
 order by random()

В итоге из 48-ми имен и 29-ти фамилий мы получим целых 1392 уникальных сочетаний, что вполне неплохо для начала. Похожую технику мы можем применять для генерации других данных, таких, как почтовые ящики и адреса, завернув в хранимую функцию или генератор шаблонов (а-ля Jinja), для простоты использования и дальнейшей модификации.

Существует также огромное количество сторонних сервисов для генерации реалистичных данных, которые мы можем попробовать затащить в свою базу данных (например, Fake Name Generator сервис позволяет выгружать данные в csv формате). А некоторые из них даже могут выгрузить полученные данные в виде уже готового SQL-скрипта (как,например, Generatedata сервис):

insert into persons (name, company, address, email)
values
  ('Berk Cotton','Tempus Eu Ligula Incorporated','Ap #633-4301 Tempus, St.','interdum.libero.dui@icloud.ca'),
  ('Ahmed Sandoval','Nullam Lobortis Foundation','P.O. Box 902, 9630 Convallis Rd.','magna.suspendisse@google.edu'),
  ('Hedy Mcbride','Risus Nulla Limited','5235 Lacinia Avenue','donec.felis@icloud.com'),
  ('Kermit Mcintosh','Erat Associates','278-141 Pellentesque St.','vel.faucibus@icloud.ca'),
  ('Susan Berg','Mauris Institute','Ap #876-781 Vehicula Street','ipsum.nunc@protonmail.ca');

Учитывая, что проблема генерации данных далеко не нова, существует множество программных библиотек для генерации случайных данных высокого качества для различных языков (Java, Python, JS, Ruby и пр.). К счастью, некоторые СУБД позволяют подключать такие библиотеки (с помощью расширений, например) и использовать их прямо в наших SQL-запросах. Например, расширение PostgreSQL Faker позволяет написать вот такой запрос:

select faker.name()
     , faker.company()
     , faker.address()
     , faker.email()
  from generate_series(1, 5)

И это еще не все (голосом из "магазина на диване")! Расширение faker_fdw предоставляет нам настоящий реляционный способ сгенерировать что-нибудь реальное, используя таблицы, "джойны" и вот это все:

select p.name
     , c.company
     , a.address
     , i.ascii_email 
  from (select row_number() over() as id, p.* from person p limit 5) p
  join (select row_number() over() as id, a.* from address a limit 5) a on a.id = p.id
  join (select row_number() over() as id, c.* from company c limit 5) c on c.id = p.id
  join (select row_number() over() as id, i.* from internet i limit 5) i on i.id = p.id

Уникальные значения

Хотя наши данные и случайные, это не значит, что к ним вообще нет никаких требований. Например, нам могут понадобиться только уникальные данные для некоторых колонок. Существует достаточно много способов добиться этого. Например, многие СУБД в той или иной степени поддерживают концепцию upsert'а на основании значений одной или нескольких колонок (merge, on conflict и пр.):

-- PostgreSQL
create table orders (code varchar(4) primary key, operation_date date);

insert into orders
select substr(md5(random()::text), 1, 4) as code
     , now() - (random() * (interval '90 days')) as operation_date 
  from generate_series(1, 1000)
    on conflict (code) do nothing

Также мы можем отсечь дублирующиеся значения еще при генерации. Например, старый добрый distinct, или небольшие танцы с аналитическими запросами:

-- PostgreSQL
select code
     , operation_date
  from (select code
             , operation_date
             , row_number() over (partition by code order by operation_date) as rn
      from (select substr(md5(random()::text), 1, 4) as code
                 , now() - (random() * (interval '90 days')) as operation_date 
              from generate_series(1, 1000)) s) s
 where rn=1

Минус двух предыдущих решений состоит в том, что в итоге мы получим меньше строк, чем задано (что может быть и не сильно страшно). С другой стороны, для большей точности можем обратиться к встроенным в СУБД средствам генерации уникальных данных, таким, как последовательности, «сиквенсы», UUID и пр.:

-- PostgreSQL
select gen_random_uuid()
select nextval('film_film_id_seq')

-- PostgreSQL Faker
select faker.unique_name()
     , faker.unique_address()
  from generate_series(1, 10)

Запланированная случайность

Случайность — это, конечно, хорошо, но как быть, если мы захотим написать какие‑нибудь тесты для наших данных? т. е., нам нужна какая‑то повторяемость, предсказуемость данных. Для этого большинство СУБД предоставляет возможность установить начальное значение (seed) для генератора случайных значений:

-- PostgreSQL
select setseed(0.5);

-- Oracle
exec dbms_random.seed(42);

-- PostgreSQL Faker
select faker.seed(4321);

Аватары

До этого момента мы оперировали только текстовыми данными. Однако порой нам понадобится «придумать» какое‑нибудь изображение, например, аватарку для профиля пользователя. К счастью, существует огромное количество сервисов с собственными API (часто http), которые могут генерировать различного вида аватарки, — от забавных мультяшек (dicebear.com, api.multiavatar.com) до вполне себе реальных фото людей (randomusers). Давайте придумаем несколько пользователей, используя «залипательный» сервис robohash.org:

select name
     , format('https://robohash.org/%s?set=set%s',
              replace(name, ' ', '_'),
              set_number) as avatar
  from (select trunc(random() * 4 + 1) as set_number
             , faker.name()
          from generate_series(1, 3)) s

Время для серьёзных вещей

Пришло время сделать что-то полезное. В качестве подопытной схемы возьмем замечательную тестовую базу данных Pagila (на которую мы уже натравливали ChatGPT в самом начале поста). На самом деле, в ней уже есть данные, но их достаточно мало (максимум 16к в паре таблиц), поэтому мы займемся тем, что для пустой схемы (скрипт pagila-schema.sql) сгенерируем много данных самостоятельно:

Чтобы наши SQL-скрипты генерации получились более простыми и более читаемыми, а сгенерированные данные более реалистичными, мы возьмем за основу Postgres-расширение PostgreSQL Faker. Тем более, что создатели расширения уже подготовили готовый Docker-образ, из которого мы и поднимем БД для дальнейших экспериментов:

docker run \
  --name pagila-faker \
  -p 5432:5432 \
  --env POSTGRES_PASSWORD=postgres \
  registry.gitlab.com/dalibo/postgresql_faker

Остается только не забыть подключиться к свежеподнятой БД и зарегистрировать расширение:

docker exec \
  -it pagila-faker sh -c \
  "psql -U postgres -d postgres \
    -c \"create schema faker;\" \
    -c \"create extension faker schema faker cascade;\" \
  "

И накатить схему Pagila БД:

git clone https://github.com/devrimgunduz/pagila.git
cd pagila
docker cp ./pagila-schema.sql pagila-faker:/docker-entrypoint-initdb.d/pagila-schema.sql

docker exec -it pagila-faker sh -c \
  "psql -U postgres -d postgres -f /docker-entrypoint-initdb.d/pagila-schema.sql"

Справочники

Итак, начнем с самой простой задачи — таблицы‑справочники верхнего уровня (те, которые сами не зависят от других справочников). В нашем случае, это таблицы стран и языков. Тут все довольно просто — синтезируем нужное количество строк (например 20) посредством generate_series и для каждой вызываем готовую функцию unique_country (или unique_language_name для справочника языков, или любую другую подходящую функцию из огромного множества, которое предоставляет расширение PostgreSQL Faker):

insert into country(country)
select faker.unique_country()
  from generate_series(1, 20) as id

Теперь чуть более сложный случай - таблица-справочник городов, т.к. она зависит от таблицы стран. Мы, конечно, можем вставить какое-то фиксированное количество городов для каждой страны и успокоиться, но так не интересно, - мы все знаем, что есть маленькие страны, есть большие, и поэтому нам тоже нужен какой-то разброс по количеству городов в каждой стране. Один из вариантов - делаем cross join между таблицей стран и пустой последовательностью из 1000 строк (промежуточно получим 20к строк и одинаковое количество городов в каждой стране), а потом "рандомно" отбросим часть данных от общего множества:

insert into city(city, country_id)
select faker.unique_city()
     , country_id
  from country
 cross join generate_series(1, 1000)
 where random() > 0.9

select с.country_id
     , cnt.country 
     , с.cities_per_country_count
     , floor(cities_per_country_count / cities_count * 100) as percent
  from (select country_id
             , count(1) as cities_per_country_count
             , sum(count(1)) over () as cities_count
          from city
         group by country_id) с
  join country cnt on cnt.country_id = с.country_id
 order by cities_per_country_count desc
 limit 5

Связанные данные

Пришло время для чего‑то поинтереснее — генерация персонала (staff таблица). Эта таблица имеет 2 родительских таблицы — store и address, и нам необходимо случайным образом их скомбинировать. Первое, что приходит в голову — это выполнить cross join (как мы делали это уже выше с небольшими справочными таблицами). Но в данном случае, мы получим очень медленный запрос, т.к. декартово произведение двух больших множеств ожидаемо породит чудовищное количество строк, которое нужно будет еще случайно отсортировать, и только потом отсечь нужное количество строк. К счастью, стандарт SQL:2003 вводит выражение tablesample для select‑запросов, которое позволяет читать не всю таблицу целиком, а только ее часть (указанную в качестве параметра в процентном отношении). А метод bernoulli позволяет просканировать все блоки таблицы (а не только несколько случайных, как в методе system), но вычитать только часть случайных строк, что приведет к достаточно равномерному распределению. Т.о., мы сможем прочитать только часть строк из таблиц store и address (скажем, по 1%) и выполнить cross join между полученными небольшими наборами:

-- PostgreSQL
insert into staff(first_name, ..., password)
select faker.first_name()
     , ...
     , faker.password()
  from (select a.address_id
             , s.store_id
          from store s tablesample bernoulli(1)
         cross join address a tablesample bernoulli(1)
         limit 50000) s

Таблицы временных рядов

На тему генерации time series данных (также посредством чистого SQL‑я, всё как мы любим) уже есть 3 просто шикарнейших статьи (раз, два, три) от компании TimescaleDB. Мы же в рамках нашего поста ограничимся простеньким решением для таблицы rental, в котором будем вычитать из текущей даты количество минут, соответствующее номеру случайной строки:

insert into rental(rental_date, inventory_id, customer_id, staff_id)
select current_date - (((row_number() over())::text) || ' minute')::interval
     , inventory_id
     , customer_id
     , staff_id
  from (select i.inventory_id
             , c.customer_id
             , s.staff_id 
          from inventory i tablesample bernoulli(1) 
         cross join customer c tablesample bernoulli(1)
         cross join staff s tablesample bernoulli(1)
         limit 1000000) s

Размножение данных

Все это время мы занимались с вами генерацией данных так сказать «from scratch». Но, в некоторых случаях, нам может понадобиться увеличить количество уже существующих данных, сохранив текущее распределение данных. Давайте попробуем увеличить количество городов в справочнике city в 4 раза, сохранив старое распределение количества городов по странам. Идея простая, считаем количество городов по странам, умножаем на 3 и генерируем такое количество новых городов по каждой стране:

insert into city(city, country_id)
select unnest(array(select faker.unique_city()
                      from generate_series(1, c.cities_count*3) as id)) as city
     , country_id
  from (select country_id
             , count(1) as cities_count
          from city
         group by country_id
         order by country_id) c

Теперь делаем выборку топ 5 стран по количеству городов и убеждаемся, что она точно такая же, как при заполнении справочника с нуля (см. соответствующую главу выше), но количество строк заметно увеличилось:

Собираем все вместе

Все упомянутые SQL-скрипты (и те, которые не были упомянуты, для промежуточных таблиц) доступны в этом GitHub репозитории. Их достаточно легко увидеть в действии, запустив генерацию с помощью docker-compose:

git clone https://github.com/synthesized-io/pagila-data-generation.git
cd pagila-data-generation
docker-compose up

К чему я вас и приглашаю. Так же буду рад звёздочкам, PR'ам, issues'ам и пр.

Впереди еще много работы

Как мы видим, чтобы получить первые плоды генерации данных, было проделано достаточно много работы. Но, на самом деле, это только начало, впереди нас ожидает еще много новых вызовов, например:

• Схема БД — сейчас наши скрипты жестко завязаны на конкретную схему БД. Если мы захотим проделать что‑то похожее с другой схемой, то основную часть работы придется выполнить снова.

• Миграции схемы — также придется все время «рихтовать» наши скрипты при миграции схемы БД.

• Много объектов — в демонстрационной БД Pagila чуть больше десятка таблиц, но в реальной жизни мы обычно сталкиваемся со схемами в сотни и тысячи таблиц, и сложными зависимостями между ними.

• Качество данных — по ходу статьи мы не предъявляли строгих требований к качеству и свойствам синтезированных данных, однако в реальной жизни обычно бывает по другому, — нам может понадобиться особое распределение значений по тем или иным параметрам, локализация значений и многое другое.

• Различные типы данных — помимо текста, дат и числовых значений (с которыми мы работали в этом посте), различные СУБД поддерживают множество других более сложных типов данных. Например, объектные типы, домены, json и пр., которые тоже может понадобиться обрабатывать во время генерации.

• Производительность.

Всё не так плохо

К счастью существует множество уже готовых решений (неполный список можно найти здесь и даже пополнить его посредством PR'а), которые разными способами решают вышеописанные проблемы. А мы, в свою очередь, попробуем сгенерировать данные для нашей схемы с помощью инструмента Synthesized TDK. Вы, возможно, спросите - "Максим, почему именно TDK?". Все очень просто, с недавних пор я работаю в компании Synthesized, которая и разрабатывает этот замечательный тулз.

Итак, TDK это config-based инструмент, и для быстрого старта нам необходимо только подготовить небольшой yaml-файл и указать в нём режим (сейчас нас интересует GENERATION) и ожидаемое количество строк:

-- config.yaml
default_config:
  mode: GENERATION
  target_row_number: 100_000

Этого конфига уже вполне достаточно для генерации тестовых данных как для небольшой тестовой БД Pagila (как в нашем случае) так и для настоящей боевой схемы с сотнями таблиц и связей - TDK самостоятельно определит и применит нужные параметры генерации в зависимости от режима, типа колонок и исходных данных.

docker run -i \
  -v $(pwd)/config.yaml:/app/config.yaml \
  --env SYNTHESIZED_INPUT_URL=jdbc:postgresql://localhost:6000/postgres \
  --env SYNTHESIZED_INPUT_USERNAME=postgres \
  --env SYNTHESIZED_INPUT_PASSWORD=postgres \
  --env SYNTHESIZED_OUTPUT_URL=jdbc:postgresql://localhost:6001/postgres \
  --env SYNTHESIZED_OUTPUT_USERNAME=postgres \
  --env SYNTHESIZED_OUTPUT_PASSWORD=postgres \
  --env SYNTHESIZED_USERCONFIG_FILE=/app/config.yaml \
  --net=host \
  synthesizedio/synthesized-tdk-cli:latest

В итоге приложение просканирует все таблицы и их данные (если такие есть) в INPUT БД, и сгенерирует по 100К строк для каждой таблицы в OUTPUT БД, учитывая тип каждой колонки и уже имеющееся распределение.

Однако мы можем изменять стандартное поведение TDK, добавляя дополнительные инструкции в конфигурационный файл. Допустим, мы хотим чтобы добрых фильмов для всей семьи (MPPA-рейтинг G – General Audiences) было большинство. Для этого в наш yaml-файл добавим дополнительные конфигурации для генерации значений колонки rating таблицы film:

tables:
  - table_name_with_schema: "public.film"
    target_row_number: 10_000
    transformations:
      - columns: ["rating"]
        params:
          type: "categorical_generator"
          categories:
            type: string
            values: ["G",  "PG", "PG-13", "R", "NC-17"]
          probabilities: [0.5, 0.2, 0.1, 0.1, 0.1]

После генерации с таким конфигом мы можем заглянуть в таблицу film и убедиться, что указанное распределение успешно применилось среди 10К фильмов:

Так же мы можем дополнительно конфигурировать генерацию адресов, имен, строк, различных последовательностей и много чего ещё. Подробнее об этом можно почитать в документации по видам трансформаций. А ещё TDK обеспечивают простую интеграцию с CI/CD пайплайнами (например, с помощью соответствующего GitHub Action) и Testcontainers-ами (хабр-пост от @IvanPonomarev ).

А для ещё более легкого старта мы заготовили небольшое демо - pagila-tdk-generation. Его можно запустить и посмотреть, всего за пару команд, используя docker-compose:

git clone https://github.com/synthesized-io/tdk-docker-demo
cd tdk-docker-demo
docker-compose run tdk

В итоге у нас будут подняты 2 PostgreSQL БД (для INPUT базы проброшен порт 6000, для OUTPUT - 6001), установлена схема Pagila на каждую из них и запущен TDK для генерации данных (с использованием более продвинутой конфигурации в config.yaml). Когда TDK завершит свою работу, управление вернется в командную строку, а мы сможем подключиться к целевой БД по порту 6001 (postgres/postgres в качестве имени и пароля) и оценить синтезированные данные.

На этом все, спасибо за внимание, буду рад любому фидбэку;)