SQL Server: not only SQL, а дещо більше

💡 Усі статті, обговорення, новини про Java — в одному місці. Приєднуйтесь до Java спільноти!

Хочу одразу сказати, якщо ви очікуєте прочитати тут про реалізацію NoSQL в Microsoft SQL Server, то не витрачайте час: мова піде не про це. В статті я хочу зробити огляд мов програмування, які можна застосувати в цій СКБД, використовуючи можливості, недоступні або складні для реалізації в рамках мови Transact-SQL.

Кілька разів в моїй роботі були ситуації, коли якийсь функціонал системи я пропонував перенести із сервісної програми, написаній на мейнстрімній мові (найчастіше C#, оскільки працювали переважно в екосистемі Microsoft) в базу даних. Це могли бути запит до API чи вебсторінки, парсинг html, запуск якихось сервісів тощо. Зазвичай це дозволяло спростити систему і зробити її легшою в керуванні.

І зазвичай ця розмова виглядала приблизно так:
— Ні, це неможливо!
— Чому?
— Нас вчили, що база даних призначена тільки для зберігання даних!
— А якщо вас вчили неправильно?
— Хм... Ну, може так. Але нам в команду не дали DB Developer’а, бо вирішили, що базу даних може зліпити і C# (Java, Go etc) Developer. Тому будемо робити не як краще, а як уміємо (або як вчили).

Будучи знайомим з багатьма вчорашніми студентами, знаю, що загалом вивченню СКБД у вузах приділяється небагато уваги та зазвичай вона концентрується на основних поняттях і якомусь діалекті SQL, що в принципі правильно, якщо метою є дати базову підготовку по якнайширшому колу предметів. Але в роки моєї студентської юності, коли вчорашні випускники приходили на виробництво (тоді ще не казали «приходили в бізнес», бо в СРСР бізнесу не було), їм казали: «Забудьте усе, чому вас вчили у вузі (слова „виш“ тоді ще також не існувало), на виробництві все по-іншому». Але зараз чи то освіта стала ближчою до життя, чи життя адаптується до освіти, тому вчорашні випускники вишів часто не виходять за рамки того, чому їх навчили, якщо це можливо і їм цього вистачає для виконання завдань. Не знаю, це добре чи погано.

Але життя йде вперед разом з СКБД, і підтримка мов програмування інших, ніж SQL, надавала базам даних широкі можливості, серед яких — інтегрування більшої частини або і всього ЕТЛ-процесу всередину БД. В якійсь із версій SQL Server 6.x (1995-96 рр.) з’явилися Extended Stored Procedures, які надавали можливість писати процедури на C/C++, компілювати їх в DLL і потім викликати з T-SQL. В якій саме версії, знайти пряме посилання на документацію Microsoft не вдалося, лише непрямі, тут і тут, і згадування файлів opends60.dll і opends60.lib в сучасній документації по Extended Stored Procedures. В Oracle у версії 8.0 (червень 1997-го) з’явилась підтримка зовнішніх збережених процедур на C, у версії 8.1.5 (1998 рік) — на Java. Та й пакети PL/SQL мали стільки можливостей, що іншим СКБД залишалось тільки заздрити. Але в SQL Server 2005 з’явився SQL CLR, що відкрило доступ зсередини SQL Server, хоча і зі значними обмеженнями, до можливостей .Net Framework, та підвищило конкурентноспроможність SQL Server в порівнянні з Oracle.

Через обмежені можливості SQL не втрачає популярність так звана трирівнева архітектура (як підвипадок N-рівневої), згідно з якою система має складатися з трьох рівнів — рівня презентації (presentation tier) — простіше кажучи, інтерфейс користувача, рівня застосунку (application tier), також відомого як рівень логіки або середній рівень, та рівня даних (data tier). З рівнем презентації все просто — це вебсторінка або десктопна програма.

Про рівні застосунку та даних гарно сказано тут:

Рівень застосунку, також відомий як логічний рівень або середній рівень, є серцем застосунку. На цьому рівні інформація, зібрана на рівні презентації, обробляється — іноді порівняно з іншою інформацією на рівні даних — за допомогою бізнес-логіки, певного набору бізнес-правил. Рівень застосунку також може додавати, видаляти або змінювати дані на рівні даних.

Рівень застосунку зазвичай розробляється за допомогою Python, Java, Perl, PHP або Ruby та взаємодіє з рівнем даних за допомогою викликів API.

Рівень даних, який іноді називають рівнем бази даних, рівнем доступу до даних або серверною частиною, — це місце, де зберігається та керується інформація, що обробляється програмою. Це може бути реляційна система керування базами даних, така як PostgreSQL, MySQL, MariaDB, Oracle, Db2, Informix або Microsoft SQL Server, або NoSQL-сервер бази даних, такий як Cassandra, CouchDB або MongoDB.

Постає питання: які елементи або дії реалізувати на рівні застосунку, а які на рівні даних? З однієї сторони, багатство можливостей мов високого рівня дозволяє легше розробляти складні системи. З іншої, сучасні програми працюють все з більшими об’ємами даних і реалізовувати бізнес-логіку за межами сервера БД з його ресурсами та можливостями оптимізації інколи стає дуже непросто.

І тут сягає думка: а навіщо мені для бізнес-логіки обробляти дані на рівні/сервері застосунку, якщо у мене СКБД «розуміє» C#, Java, Python etc?

Далі я хочу коротко описати, які мови можна використати «всередині» Transact-SQL Microsoft SQL Server (не забуваємо, що Transact-SQL ще використовується в продуктах Sybase, але описаних нижче можливостей у них, звісно, немає).

Існують три технології, крім Extended Stored Procedures (які все ще використовуються, але вже об’явлені Microsoft як deprecated), які дозволяють використовувати не SQL мови . Вірніше, чотири. В SQL Server Integration Services є компоненти Script Task та Script Component, в яких використовується C# чи VB.Net і пакети з ними можна запускати з Transact-SQL, якщо, наприклад, вони розміщені в SSIS Catalog. Але тут ми його розглядати не будемо, це, на мою думку, скоріше тема для статті по SSIS.

SQL CLR

SQL CLR або SQLCLR (SQL Common Language Runtime) — це технологія для розміщення загального середовища виконання Microsoft .NET (Common Language Runtime) у середовищі SQL Server. Вона дозволяє керованому коду розміщуватися та виконуватися в середовищі бази даних як звичайним об’єктам SQL.

Згідно з документацією теоретично ми можемо написати об’єкт (процедуру, функцію, тригер, тип даних тощо) на будь-якій .Net мові. Практично це просто зробити лише на C# і VB.Net, особливо у Visual Studio, створивши необхідні об’єкти в Database проєкті й просто через Publish згенерувавши SQL-скрипт для завантаження або напряму завантаживши проєкт в базу даних. Visual Studio також згенерує в скрипті або створить на сервері усі необхідні об’єкти-обгортки на Transact-SQL. Або можна використати скомпільований файл проєкту (.dacpac) і утіліту SqlPackage.

З .Net-мовами, відмінними від C# чи VB.Net, буде трішки важче. Потрібно буде вручну компілювати збірку, і писати або генерувати скрипти для обгорток кожного об’єкту. Загальні кроки по деплою коротко описані тут. В інтернеті подекуди трапляються приклади для F#, а от для інших мов я не зустрічав.

SQL Server Agent

SQL Server Agent — це планувальник завдань SQL Server. Його згадую тому, що там є чудова опція — тексти кроків можна писати на PowerShell. Коротко все описано тут.

Це прекрасна можливість використовувати все багатство функціоналу .Net без обмежень SQL CLR, та, в принципі, і без знання .Net. Командлети PowerShell покривають величезну купу задач в найрізноманітніших областях.

В інтернеті є багато прикладів, де в SQL Server Agent PowerShell скрипт запускається через cmd.exe, але ці приклади невдалі. Думаю, зручніше, коли ми працюємо з текстом PowerShell скрипта прямо з SSMS або іншого засобу розробки/адміністрування, в одному середовищі з кодом T-SQL.

External scripts

В SQL Server 2016 з’явився новий функціонал — SQL Server R Services. Він дозволяє запускати скрипти на R через процедуру sp_execute_external_script.

Слід сказати, що Microsoft доволі багато вклалася в розвиток мови R. У 2015 році Microsoft поглинула компанію Revolution Analytics (американська компанія, виробник програмного забезпечення для статистичної обробки, що фокусувалася на комерціалізації мови програмування R та створення програмних рішень з його використанням), після чого провела ребрендинг деяких їх розробок.

Варто згадати найбільш значимі внески в розвиток екосистеми R: Microsoft R Open (безкоштовна, покращена версія базового R), Microsoft R Server (переросла в Machine Learning Server з підтримкою Python, оптимізовані версії R для роботи з великими даними, що виходять за межі оперативної пам’яті, паралельне та розподілене виконання обчислень), інтеграція R у SQL Server, розширені бібліотеки для масштабованої аналітики (RevoScaleR, microsoftml, olapR), власний репозиторій Microsoft R Application Network (MRAN) як дзеркало відомого всім, хто працює з R, Comprehensive R Archive Network (CRAN), проте більш впорядкований.

Але, як то кажуть, «не злетіло», і Microsoft заявив про припинення підтримки Machine Learning Server (R Server) і MRAN, проте підтримка R в SQL Server не припиняється.

В SQL Server 2017 замість SQL Server R Services з’являється SQL Server Machine Learning Services з підтримкою Python, в SQL Server 2019 додано підтримку Java. З Java є особливість: вона «заходить» в SQL Server 2019 не через SQL Server Machine Learning Services, як R чи Python, а через SQL Server Language Extensions і потребує додаткових кроків для налаштування. І на відміну від R та Python, розширення Java не містить додаткових бібліотек машинного навчання — це радше інтеграція загального призначення.

В 2021 році Microsoft представила мовне розширення C# Language Extension, яке дозволяє виконувати C# код без обмежень SQL CLR як зовнішній скрипт, за аналогією з R, Python та Java. Тут можна подивитись порівняння можливостей SQL Server Language Extensions та SQL CLR.
Процедура sp_execute_external_script дозволяє виконати зовнішній скрипт (на R, Python, Java, C# та інших мовах, зареєстрованих через CREATE EXTERNAL LANGUAGE) ізсередини SQL Server, при необхідності отримуючи дані з T-SQL у вигляді скалярних параметрів та/або датасету і повертаючи результат (також як скалярні змінні чи датасет) назад у T-SQL.

Вхідний датасет передається як текст SQL-запиту, результат якого конвертується «всередині» скрипта в DataFrame-подібну структуру (data.frame в R, pandas.DataFrame у Python, власний DataFrame у C# extension). На виході також можна отримати датасет подібно до виводу звичайної процедури.

І, нарешті, хочу сказати, що код SQL Server Language Extensions відкритий, доступний в GitHub, і ви його можете використати, щоб інтегрувати в SQL Server вашу улюблену мову (Julia, Rust тощо) за аналогією з іншими мовами для зовнішніх скриптів.

Щасти вам!

P.S. Метою даної статті я ставив не тільки вказати на маловикористовувані можливості Microsoft SQL Server, а й ініціювати дискусію щодо доцільності їх використання для реалізації бізнес-логіки не на стороні клієнтської частини, а в базі даних. Як я згадував, у мене були такі випадки, коли архітектура системи реалізувалась, виходячи з наявних спеціалістів одного напряму (C#, Python etc) і браку інших (DB Engineer, DB Developer etc). Бо якось у багатьох Project Manager-ів, а часто й у розробників ще панує уявлення, що бази даних — це тільки таблиці.

Буду вдячний за коментарі та виправлення помилок та неточностей!

І трішки гумору!