Поскольку рекурсивные CTE могут «итерироваться», можно реализовать обучение модели машинного обучения на чистом SQL — вообще без Python.
В статье Глубокая нейронная сеть, реализованная на чистом SQL поверх BigQuery автор утверждал, что использует чистый SQL для реализации модели глубокой нейронной сети. Но после того, как я открыл его репозиторий, я обнаружил, что он использует Python для реализации итеративного обучения, что на самом деле не является чистым SQL.
В этом посте я расскажу, как я обучал модель машинного обучения на чистом SQL в TiDB, распределенной базе данных SQL с открытым исходным кодом. Основные шаги включали:
- Выбор набора данных Iris
- Выбор модели логистической регрессии softmax для обучения
- Написание оператора SQL для реализации вывода модели
- Обучение модели
В своем тесте я обучил модель логистической регрессии 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 для подсчета точности результатов для вывода данных. Для лучшего понимания я использовал псевдокод для описания этого процесса:
В приведенном выше коде я вычислил элементы в каждой строке данных. Чтобы сделать вывод выборки:
- Я получил EXP взвешенных векторов.
- Я получил значение softmax.
- Я выбрал наибольший из 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.