Генерация больших объемов полезных данных

Хочу поделиться опытом создания механизма генерации большой базы данных товаров. С его помощью наши пользователи могут за несколько минут сгенерировать более миллиона однотипных, но разных записей.

Мы создавали большую систему учета товаров. Количество товаров обещало превысить несколько миллионов и нам стало понятно, что ручного добавления товаров в таблицу будет недостаточно. Товары, как правило, однотипные и отличаются только параметрами. Например:
  • Шуруп оцинкованный крест D 3мм длина 16мм для дерева
  • Шуруп черный крест D 3мм длина 20мм для ГК
  • Шуруп оцинкованный шлиц D 4мм длина 16мм для металла
  • Шуруп черный шлиц D 4мм длина 20мм универсальный
  • ...

Шурупов может быть несколько тысяч. Для группы товаров «метизы» нужны еще гвозди, болты, гайки, шайбы, дюбеля… В примере видно, что структура названия состоит из нескольких полей, каждое из которых имеет несколько вариантов значений. Весь набор названий шурупов- это язык (напомню, что язык- это множество строк) и этот язык может быть описан регулярным выражением. Обычно регулярное выражение используется для ответа на вопрос: принадлежит ли строка языку или нет. В нашем случае нужно решить обратную задачу: по регулярному выражению построить весь набор строк или сгенерировать язык. То есть, пользователь может ввести что-то вроде
Шуруп (оцинкованный|черный) D (2|3|3,5|4|5|6|7|8|9|10) длина (10|12|16|20|25|30|40|50|60|80|100) (крест|шлиц) (для дерева|для металла|для гк|универсальный)
Программа должна сгенерировать всевозможные варианты шурупов. Надо учесть, что некоторые регулярные выражения описывают бесконечные языки, поэтому не любое выражение может использоваться для генерации данных. Например выражение [0-9]+ описывает язык целых неотрицательных чисел и он бесконечен.
Для генерации данных мы используем два способа.

Генерация с помощью пакета Automation
OpenSource пакет Automation умеет работать с конечными автоматами, чем по сути и является регулярное выражение. В числе прочих возможностей, библиотека умеет генерировать строки удовлетворяющие заданному регулярному выражению. Дальше дело техники- сгенерировать все варианты и поместить в базу данных.
Однако у этого способа есть существенный недостаток- скорость работы. Он хорошо работает на нескольких тысячах строк, но на миллионы записей может уходить несколько часов (в основном из-за медленной вставки в БД по одной строчке). Поэтому был разработан второй способ.

Генерация SQL по регулярному выражению
Представьте, что будет если выполнить SQL запрос (диалект MySQL)
select 
concat('Шуруп диаметр ', t1.a, 'мм длина ', t2.a, 'мм')
from (
  SELECT '3' as a
  union 
  select '4' as a
) as t1, 
(
  SELECT '16' as a
  union 
  select '20' as a
) as t2

Получится результат из 4 строк, который можно сразу вставить в таблицу:
Шуруп диаметр 3мм длина 16мм
Шуруп диаметр 4мм длина 16мм
Шуруп диаметр 3мм длина 20мм
Шуруп диаметр 4мм длина 20мм

Этот простой пример показывает, что можно написать SELECT запрос и использовать его как часть INSERT запроса для вставки нужных строк в базу данных. Для генерации такого SQL мы написали несложный анализатор регулярных выражений. Он просто выделяет скобки внутри выражения, затем использует все тот же Automation, чтобы сгенерировать всевозможные варианты строк внутри скобок. То есть например для выражения
Шуруп диаметр (3|4)мм длина (16|20)мм
Программа выделяет выражения в скобках 3|4 и 16|20, затем с помощью Automation для каждой пары скобок генерирует всевозможные значения и формирует SQL запрос.
Такой запрос выполняется намного быстрее, чем вставка по одной записи и способен добавить миллионы строк за несколько секунд.

Ограничение генерируемых данных
Оставалась последняя проблема. Представьте себе шуруп толщиной 2 мм и длинной 100 мм. Таких не существует. Нужен был механизм ограничения некоторых сочетаний параметров. Проще всего попросить пользователя написать функцию на каком-нибудь скриптовом языке, которая принимает все параметры и возвращает true/false. Но скорее всего это встретило бы непонимание со стороны пользователей. Поэтому мы решили использовать Pivot Table, чтобы пользователь мог выделить нужные сочетания параметров, которые мы потом просто добавляем в условия WHERE внутри SELECT запроса.

image

На иллюстрации изображена сводная таблица, с раскрытыми столбцами и выделенными желтым цветом ячейками. Синим цветом обозначаются товары, уже добавленные в базу, зеленым, те, что еще не добавлены. Видно что уже добавлены все шурупы черного цвета для дерева, и добавлена половина (110/220) из всех оцинкованных шурупов для металла (чтобы понять какие именно- нужно развернуть столбец «Материал для металла» в сводной таблице). Также пользователь выделил и готовится добавить шурупы для ГК черные под крестовую отвертку и оцинкованные под шлицевую отвертку.
Я опускаю подробности того, как мы обеспечивали удаление ошибочно сгенерированных данных и обработку повторного добавления уже существующих записей. Это больше относиться к тонкостям работы с MySQL (или другой БД) и не соответствует теме поста.
Данный механизм хорошо зарекомендовал себя. Пользователи смогли его освоить и используют в своей работе. На данный момент самый большой объем базы данных товаров у одного из наших клиентов превышает 50 млн записей. Большая часть этого объема была создана в первый месяц работы.

Similar posts

AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 2

    0
    Хороший подход в плане того, что все наименования будут одинаково отформатированы. Скажите, пожалуйста, пользователи запускают генерацию когда вводят накладные или заранее? И еще вопросы: генерится только текст или еще поля для отбора (к примеру нужно найти все шурупы диаметром 3 мм и длиной от 16 до 20 мм); рассматривался ли вариант вести учет в разрезе характеристик, когда в таблице наименований товаров будет только Шуруп, а длина, диаметр и т.д — в других таблицах?
      0
      Генерацию желательно запускать заранее, чтобы на момент ввода накладной наименование товара было в таблице. Но мы предусмотрели возможность ввода накладной, без этого требования. Но тогда просто накладная не попадает на следующий шаг, пока не будет добавлена «правильная» запись в таблицу товаров.
      Поля для отбора мы тоже генерируем- есть дополнительная таблица атрибутов товара (да, это антипаттерн Entity-Attribute-Value)
      CREATE TABLE `good_attribute` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(32) NOT NULL,
        `value` varchar(32) NOT NULL,
        `good_id` int(11) NOT NULL,
        PRIMARY KEY (`id`),
      )
      

      Но пока эта таблица не используется- оставлена на будущее, т.к. мы используем обычный текстовый поиск, т.е. пользователь вводит что-то вроде

      Шур 4 10
      

      И мы показываем ему первые 10 вариантов, где встречаются эти подстроки. Работает довольно быстро- из нескольких миллионов позиций выборка происходит за 1-5 секунд.
      Разные таблицы для параметров мы не используем т.к. заранее мы не знаем какие параметры нужны будут пользователю (для метизов это одни параметры, для труб или оборудования- совсем другие), а генерацией таблиц «на лету» мы решили не увлекаться.

    Only users with full accounts can post comments. Log in, please.