Поскольку рекурсивные CTE могут «итерироваться», можно реализовать обучение модели машинного обучения на чистом SQL — вообще без Python.

В статье Глубокая нейронная сеть, реализованная на чистом SQL поверх BigQuery автор утверждал, что использует чистый SQL для реализации модели глубокой нейронной сети. Но после того, как я открыл его репозиторий, я обнаружил, что он использует Python для реализации итеративного обучения, что на самом деле не является чистым SQL.

В этом посте я расскажу, как я обучал модель машинного обучения на чистом SQL в TiDB, распределенной базе данных SQL с открытым исходным кодом. Основные шаги включали:

  1. Выбор набора данных Iris
  2. Выбор модели логистической регрессии softmax для обучения
  3. Написание оператора SQL для реализации вывода модели
  4. Обучение модели

В своем тесте я обучил модель логистической регрессии softmax. Во время тестирования я обнаружил, что TiDB не допускает подзапросов и агрегатных функций в рекурсивных общих табличных выражениях (CTE). Изменив код TiDB, я обошел ограничения и успешно обучил модель, а точность набора данных Iris составила 98%.

Почему я выбрал TiDB для реализации модели машинного обучения

TiDB 5.1 представил много новых функций, в том числе общие табличные выражения (CTE) стандарта ANSI SQL 99. Мы можем использовать CTE в качестве инструкции для временного представления, чтобы отделить сложную инструкцию SQL и более эффективно разрабатывать код. Более того, рекурсивное CTE может ссылаться на себя. Это важно для улучшения функциональности SQL. Более того, CTE и оконные функции делают SQL полным по Тьюрингу языком.

Поскольку рекурсивные CTE могут «повторяться», я хотел попробовать и посмотреть, смогу ли я использовать чистый SQL для реализации обучения модели машинного обучения и логического вывода в TiDB.

Набор данных Ирис

Я выбрал набор данных Iris на scikit-learn. Этот набор данных содержит 150 записей 3 типов, по 50 записей в каждом. Каждая запись имеет 4 признака: длина чашелистика (sl), ширина чашелистика (sw), длина лепестка (pl) и ширина лепестка (pw). Мы можем использовать эти функции, чтобы предсказать, относится ли ирис к ирис-сетоза, ирис-разноцветный или ирис-виргиника.

После того, как я загрузил данные в формате CSV, я импортировал их в TiDB.

Логистическая регрессия Softmax

Я выбрал простую модель машинного обучения: логистическую регрессию softmax для многоклассовой классификации.

В регрессии softmax вероятность классификации x в категорию y составляет:

Функция стоимости:

Градиент:

Следовательно, мы можем использовать градиентный спуск для обновления градиента:

Вывод модели

Я написал оператор SQL для реализации логического вывода. На основе модели и данных, определенных выше, входные данные x имели пять измерений (sl, sw, pl, pw и константа 1,0). На выходе использовалось горячее кодирование.

Было 15 параметров: 3 типа * 5 размеров.

Я инициализировал входные данные 0,1, 0,2, 0,3. Я использовал разные числа для удобства демонстрации. Инициализация всех их до 0,1 в порядке.

Затем я написал оператор SQL для подсчета точности результатов для вывода данных. Для лучшего понимания я использовал псевдокод для описания этого процесса:

В приведенном выше коде я вычислил элементы в каждой строке данных. Чтобы сделать вывод выборки:

  1. Я получил EXP взвешенных векторов.
  2. Я получил значение softmax.
  3. Я выбрал наибольший из p0, p1 и p2 как 1; Я поставил остальные на 0.

Если результат вывода выборки согласуется с ее исходной классификацией, это правильный прогноз. Затем я суммировал правильные числа всех выборок, чтобы получить окончательную степень точности.

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

Приведенный выше оператор SQL практически реализует процесс вычисления псевдокода шаг за шагом. Я получил результат:

Далее я изучу параметры модели.

Обучение модели

Примечание. Чтобы упростить задачу, я не рассматривал вопросы «обучающего набора» и «проверочного набора», а все данные использовал только для обучения.

Я написал псевдокод, а затем на его основе написал оператор SQL:

Поскольку я вручную расширил векторы суммы и w, этот код выглядел немного громоздким.

Затем я начал писать обучение SQL. Во-первых, я написал оператор SQL только с одной итерацией.

Я устанавливаю скорость обучения и количество выборок:

Код повторяется один раз:

В результате получились параметры модели после одной итерации:

Ниже приводится основная часть. Я использовал рекурсивные CTE для итеративного обучения:

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

Затем я объединил оператор SQL итерации с этой структурой итерации. Для повышения точности вычислений я добавил преобразования типов в промежуточные результаты:

Между этим кодовым блоком и кодовым блоком с одной итерацией выше было два отличия. В этом блоке кода:

  • После data join weight я добавил where iter <@num_iterations для управления количеством итераций и столбец iter + 1 as iter в вывод.
  • Я добавил having count(*)> 0, чтобы агрегация не выводила данные, когда в конце не было входных данных. Эта ошибка может привести к тому, что итерация не завершится.

В результате:

Это показало, что рекурсивные CTE не допускают подзапросов в рекурсивной части. Но я мог бы объединить все подзапросы выше. После того, как я объединил их вручную, я получил это:

Он показал, что агрегатные функции не разрешены.

Затем я решил изменить реализацию TiDB.

Согласно введению в предложение, реализация рекурсивных CTE следовала базовой структуре выполнения TiDB. После того, как я проконсультировался с Wenjun Huang, R&D в PingCAP, я узнал, что есть две причины, по которым подзапросы и агрегатные функции не разрешены:

  • MySQL не разрешал их.
  • Если бы разрешили, было бы много сложных угловых случаев.

Но я просто хотел проверить возможности. Я временно убрал проверку подзапросов и агрегатных функций в diff.

Я снова выполнил код:

Это был успех! Я получил параметры после 1000 итераций.

Далее я использовал новые параметры для пересчета правильной ставки:

На этот раз точность достигла 98%.

Заключение

Используя рекурсивные CTE в TiDB 5.1, я успешно использовал чистый SQL для обучения модели логистической регрессии softmax в TiDB.

Во время теста я обнаружил, что рекурсивные CTE TiDB не допускают подзапросов и агрегатных функций, поэтому я изменил код TiDB, чтобы обойти эти ограничения. Наконец, я успешно обучил модель и получил точность 98% для набора данных Iris.

Моя работа также выявила пару идей, о которых я хотел бы узнать ваше мнение. Если вас интересуют эти темы, присоединяйтесь к сообществу TiDB в Slack и обсудите их со мной.

  • Проведя несколько тестов, я обнаружил, что ни PostgreSQL, ни MySQL не поддерживают агрегатные функции в рекурсивных CTE. Могут быть угловые случаи, с которыми трудно справиться.
  • В этом тесте я вручную расширил все размеры векторов. На самом деле, я также написал реализацию, которая не нуждалась в расширении всех измерений. Например, схема таблицы данных была (idx, dim, value), но в этой реализации таблицу весов нужно было соединять дважды. Это означает, что к нему нужно было обращаться дважды в CTE. Это также потребовало модификации реализации исполнителя TiDB. Поэтому я не говорил об этом в этой статье. Но на самом деле эта реализация была более общей, которая могла обрабатывать модели с большим количеством измерений, например, набор данных MNIST.