Драйвер базы данных это
Собственно вопрос, каков механизм выполнения запросов к СУБД (например, пусть это будет MySQL) и какова роль в этом механизме таких компонентов, как язык SQL и ODBC? Непонятно вот что, если есть универсальный язык выполнения запросов, который нивелирует различия между СУБД, зачем нужен ещё и универсальный драйвер?
sql (язык) - не универсален. Есть диалекты. odbc это один из стандартов (библиотек), позволяющий как сделать запрос, так и узнать список таблиц полей процедур не используя запросы. Кроме odbc есть ещё jdbc, oledb и другие.
@nick_n_a, наличие диалектов не отменяет его универсальности, в противном случае в нём бы не было необходимости. Большинство запросов, написанных в соответствии со стандартом, будут одинаково интерпретированы на большинстве реляционных СУБД. А если вы под диалектами понимаете нечто вроде T-SQL или PL/SQL, ну так это скорее процедурные расширения универсального SQL, о которых, замечу, в вопросе речи не шло.
Вам правильно ответили. sql = язык. odbc = библиотека (интерфейс для драйверов) к СУБД. odbc может выполнять sql, а sql выполнять odbc нет (sql не связан с odbc). Под универсальностью я имел ввиду то что sql(mysql):show tables не будет равен sql(mssql):select * from sys.tables , а в odbc предусмотрена функция типа gettables которая всегда одинаково вернёт список таблиц для любой СУБД.
2 ответа 2
SQL это язык запросов. ODBC это способ подключения к конкретному серверу конкретной БД. Все БД имеют разные протоколы для доступа к серверу БД. ODBC предоставляет унифицированный интерфейс и скрывает конкретную реализацию протокола доступа.
Задача драйвера не только передать SQL-запрос серверу, такой протокол передачи можно было бы стандартизироать, если бы он был просто текстом. Но есть такая вещь как передаваемые в запрос параметры и самое главное - результат выполнения запроса. Задача драйвера упаковывать запрос, параметры и результаты в пакеты передаваемые по сети. И формат упаковки языком никак не оговаривается и каждая СУБД реализует его по своему. Тем более, что во многих СУБД существуют не только разные типы данных, но и сложные типы, например, как массивы PostgreSQL, которых в стандартах SQL вообще нет. И как передавать по сети эти типы данных опять же каждая БД решает самостоятельно.
Поэтому для работы с СУБД программы линкуются с библиотеками (драйверами) конкретных СУБД, которые умеют общаться с конкретной БД. Драйвер ODBC предназначен для того, что бы программа могла быть слинкована с единственной библиотекой и через нее получать доступ к разным БД, понятно, что при этом программа не сможет получить доступ к не стандартным типам данных и надо будет ограничиваться в работе стандартизированными, боле менее одинаковыми в разных СУБД возможностям языка SQL.
В мире вычислений есть множество непонятных концепций ODBC. для конечного пользователя это значок на панели управления Microsoft® Windows®. Для программиста приложения это библиотека, содержащая подпрограммы доступа к данным. Ко многим другим, это ответ на все возможные проблемы доступа к базе данных.
Первая и самое главное, ODBC является спецификацией для API базы данных. Этот API не зависит ни от одной СУБД или операционной системы; Хотя в этом руководстве используется C, API ODBC не зависит от языка. API ODBC основан на спецификациях CLI из Open Group и ISO/IEC. ODBC 3. x полностью реализует обе эти спецификации — более ранние версии ODBC были основаны на предварительных версиях этих спецификаций, но не полностью реализовали их, и добавляет функции, которые часто требуются разработчикам приложений баз данных на основе экранов, таких как прокручиваемые курсоры.
Функции API ODBC реализуются разработчиками драйверов, связанных с СУБД. Приложения вызывают эти функции в этих драйверах для доступа к данным независимо от СУБД. Диспетчер драйверов управляет взаимодействием между приложениями и драйверами.
несмотря на то, что корпорация майкрософт предоставляет диспетчер драйверов для компьютеров под управлением Microsoft Windows® 95 и более поздних версий, написал несколько драйверов odbc и вызывает функции odbc из некоторых приложений, каждый может писать приложения и драйверы odbc. На самом деле, большинство приложений и драйверов ODBC, доступных сегодня, написаны компаниями, отличными от Майкрософт. более того, драйверы и приложения ODBC существуют на® Macintosh и различных UNIXных платформах.
чтобы помочь разработчикам приложений и драйверов, корпорация майкрософт предлагает пакет средств разработки программного обеспечения ODBC (SDK) для компьютеров под управлением Windows 95 и более поздних версий, которые предоставляют диспетчер драйверов, библиотеку DLL установщика, средства тестирования и примеры приложений. корпорация майкрософт разработала с программным обеспечением висиженик, чтобы перенести эти пакеты sdk на Macintosh и различные платформы UNIX.
Важно понимать, что ODBC предназначен для предоставления возможностей баз данных, а не дополнять их. Таким образом, средства записи приложений не должны предполагать, что использование ODBC приведет к внезапному преобразованию простой базы данных в полнофункциональное ядро реляционной базы данных. И не являются модулями записи драйвера для реализации функциональности, не найденной в базовой базе данных. исключением является то, что разработчики, создающие драйверы, которые напрямую обращаются к данным файлов (например, к данным в файле Xbase), необходимы для создания ядра субд, поддерживающего по крайней мере минимальную функциональность SQL. еще одно исключение заключается в том, что компонент ODBC Windows SDK, который ранее включался в пакет SDK для компонентов доступа к данным (MDAC), предоставляет библиотеку курсоров, которая имитирует прокручиваемые курсоры для драйверов, реализующих определенный уровень функциональности.
Приложения, использующие ODBC, отвечают за любые функции межбазовых баз данных. Например, ODBC не является разнородным механизмом соединения и не является обработчиком распределенных транзакций. Однако, поскольку он не зависит от СУБД, он может использоваться для создания таких межбазовых инструментов.
Поскольку львиная доля бизнес информации храниться в базах данных. На каком бы языке программирования вы не писали, вам придётся производить различные действия с ними.
В этой статье я расскажу о двух интерфейса для работы с базами данных в R. Большая часть примеров демонстрируют работу с Microsoft SQL Server, тем не менее все примеры кода будут работать и с другими базами данных, такими как: MySQL, PostgreSQL, SQLite, ClickHouse, Google BigQuery и др.
Если вы интересуетесь анализом данных, и в частности языком R, возможно вам будут интересны мои telegram и youtube каналы. Большая часть контента которых посвящена языку R.
Для того, что бы повторить все описанные в статье примеры работы с СУБД вам потребуется перечисленное ниже, бесплатное программное обеспечение:
-
;
- Среда разработки RStudio;
- Система Управления Базами Данных, на выбор:
3.1. Microsoft SQL Server
3.2. MySQL
3.3. PostgreSQL
Пакет DBI является наиболее популярным и удобным способом взаимодействия с базами данных в R.
DBI предоставляет вам набор функций, с помощью которых вы можете управлять базами данных. Но для подключения к базам данных требуется установка дополнительных пакетов, которые являются драйверами к различным системам управления базами данных (СУБД).
Список основных функций DBI
- dbConnect — подключение к базе данных;
- dbWriteTable — запись таблицы в базу данных;
- dbReadTable — загрузка таблицы из базы данных;
- dbGetQuery — загрузка результата выполнения запроса;
- dbSendQuery — отправка запроса к базе данных;
- dbFetch — извлечение элементов из набора результатов;
- dbExecute — выполнение запросов на обновление / удаление / вставку данных в таблицы;
- dbGetInfo — запрос информацию о результате запроса или подключении;
- dbListFields — запрос списка полей таблицы;
- dbListTables — запрос списка таблиц базы данных;
- dbExistsTable — проверка наличия таблицы в базе данных;
- dbRemoveTable — удаление таблицы из базы данных;
- dbDisconnect — разрыв отсоединения с базы данных.
Подключение к базам данных
Для взаимодействия с базами данных предварительно к ним необходимо подключиться. В зависимости от СУБД с которой вы планируете работать вам потребуется дополнительный пакет, ниже перечень наиболее часто используемых.
- odbc — Драйвер для подключения через ODBC интерфейс;
- RSQLite — Драйвер к SQLite;
- RMySQL / RMariaDB — Драйвер к СУБД MySQL и MariaDB;
- RPostgreSQL — Драйвер к PosrtgreSQL;
- bigrquery — Драйвер к Google BigQuery;
- RClickhouse / clickhouse — Драйвер к ClickHouse;
- RMSSQL — Драйвер к Microsoft SQL Server (MS SQL), на момент написания статьи присутствует только на GitHub.
Пакет DBI поставляется с базовой комплектацией R, но пакеты, которые являются драйверами к базам данных необходимо устанавливать с помощью команды install.packages("название драйвера") .
Для установки пакетов с GitHub вам также понадобится дополнительный пакет — devtools . Например пакет RMSSQL на данный момент не опубликован в основном репозитории R пакетов, для его установки воспользуйтесь следующим кодом:
Пример подключения к Microsoft SQL Server с помощью пакета odbc
Перед использованием любого пакета в R сессии его предварительно необходимо подключить с помощью функции library("название пакета") .
Я неспроста выбрал Microsoft SQL Server в качестве основной СУБД на которой будет приведена большая часть примеров этой статьи. Дело в том, что это достаточно популярная база данных, но при этом она до сих пор не имеет драйвера для подключения из R опубликованного на CRAN.
Но к счастью SQL Server, как и практически любая другая база имеет ODBC (англ. Open Database Connectivity) интерфейс для подключения. Для подключения к СУБД через ODBC интерфейс в R есть ряд пакетов. Первым мы рассмотрим подключение через пакет odbc .
В функцию dbConnect() вам необходимо первым аргументом drv передать функцию, которая является драйвером для подключения к СУБД ( odbc() ). Такие функции обычно называются также, как и СУБД, и поставляются с пакетами которые являются драйверами для DBI .
Далее необходимо перечислить параметры подключения. Для подключения к MS SQL через ODBC необходимо задать следующие параметры:
- Driver — Название ODBC драйвера;
- Server — IP адрес SQL сервера;
- Database — Название базы данных к которой необходимо подключиться;
- UID — Имя пользователя базы данных;
- PWD — Пароль;
- Port — Порт для подключения, у SQL Server по умолчанию порт 1433.
ODBC драйвер для подключения к Microsoft SQL Server включен в комплектацию Windows, но он может иметь и другое название. Посмотреть список установленных драйверов можно в Администраторе источника данных ODBC. Запустить администратор источника данных в Windows 10 можно по следующему пути:
- 32-разрядной версии: %systemdrive%\Windows\SysWoW64\Odbcad32.exe
- 64-разрядной версии: %systemdrive%\Windows\System32\Odbcad32.exe
Получить список всех установленных на вашем ПК драйверов также можно с помощью функции odbcListDrivers() .
Для различных СУБД название параметров для подключения могут быть другими, например:
- PostgreSQL / MySQL / MariaDB — user, password, host, port, dbname;
- GoogleBigQuery — project, dataset;
- ClickHouse — user, password, db, port, host;
С помощью администратора источника данных ODBC вы можете запустить мастер для создания ODBC источника данных. Для этого достаточно открыть администратор, перейти на вкладку "Пользовательский DSN" и нажать кнопку "Добавить. ".
При создании источника данных используя администратор вы присваиваете ему имя, DSN (Data Source Name).
В примере выше мы создали источник данных с DSN "my_test_source". Теперь мы можем использовать этот источник для подключения к Microsoft SQL Server, и не указывать в коде остальные параметры подключения.
Посмотреть имена всех созданных на вашем ПК источников данных ODBC можно с помощью функции odbcListDataSources() .
Пример подключения к Microsoft SQL Server с помощью пакета RMSSQL
RMSSQL не опубликован на CRAN, поэтому установить его можно с GitHub с помощью пакета devtools .
В большинстве случаев, используя для работы с базами данных пакет DBI , вы будете подключаться именно таким способом. Т.е. устанавливать один из требуемых пакетов — драйверов, передавая в качестве значения аргумента drv функции dbConnect , функцию — драйвер для подключения к нужной вам СУБД.
Как скрыть пароли от базы данных в R скриптах
Выше я привёл несколько примеров которые можно использовать для подключения к любой базе данных но в них есть один минус, в таком виде все доступы к базам данных, включая пароли, хранятся в виде текста в самих скриптах.
Если все ваши скрипты развёрнуты и запускаются исключительно локально на вашем ПК, и он при этом защищён паролем, то скорее всего никакой проблемы в этом не будет. Но если вы совместно с кем то работаете на одном сервере то хранение паролей от баз данных в тексте ваших скриптов не лучшее решение.
В любой операционной системе есть утилита для управления учётными данными. Например, в Windows это диспетчер учетных данных (Credential Manager). Добавить в это хранилище пароль который вы используете для подключения к базе данных можно через пакет keyring . Пакет кроссплатформенный и приведённый пример будет работать в любой операционной системе, как минимум на Windows, MacOS и Linux.
Т.е. с помощью функции key_set_with_value() вы добавляете пароль в хранилище учётных данных, а с помощью key_get() запрашиваете его, при этом запросить пароль может только тот пользователь который добавил его в хранилище. С помощью keyring можно хранить пароли не только от баз данных, но и от любых сервисов, а так же авторизационные токены при работе с API.
Создание таблиц и запись в базу данных
Запись в базу данных осуществляется функцией dbWriteTable() .
Аргументы функции dbWriteTable() :
Жирным шрифтом выделены обязательные аргументы, курсивом — не обязательные
- conn — объект подключения к СУБД, созданный с помощью функции dbConnect ;
- name — название таблицы в СУБД, в которую будут записаны данные;
- value — таблица (объект класса data.frame / data.table / tibble_frame) в R, данные из которого будут записаны в СУБД;
- row.names — Добавляет в таблицу столбец row_names, с номерами строк, по умолчанию имеет значение FALSE.
- overwrite — Перезаписывать таблицу, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
- append — Дописывать данные, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
- field.types — Принимает на вход именованный вектор, и задаёт тип данных в каждом поле при записи в СУБД, по умолчанию имеет значение NULL;
- temporary — Позволяет создавать временные таблицы в СУБД, которые будут доступны до момента разрыва соединения с базой, по умолчанию имеет значение FALSE.
Для просмотра таблиц в базе данных служит функция dbListTables() , для удаления таблиц dbRemoveTable()
Чтение данных из СУБД
С помощью DBI вы можете запрашивать либо таблицы целиком, либо результат выполнения вашего SQL запроса. Для выполнения этих операций используются функции dbReadTable() и dbGetQuery() .
Манипулирование данными в СУБД (DML)
Рассмотренная выше функция dbGetQuery() используется исключительно для запросов на выборку данных (SELECT).
Для операций манипуляций с данными, таких как UPDATE, INSERT, DELETE, в DBI существует функция dbExecute() .
Транзакции в СУБД
Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.
Транзакция инкапсулирует несколько операторов SQL в элементарную единицу. В DBI начало транзакции инициируется с помощью dbBegin() и далее либо подтверждается с помощью dbCommit() , либо отменяется с помощью dbRollback() . В любом случае СУБД гарантирует, что: либо все, либо ни одно из утверждений не будут применены к данным.
Для примера, давайте в ходе транзакции добавим в таблицу iris 51 строку, далее изменим значение Sepal.Width в 5 строке, и удалим 43 строку из таблицы.
В базовом R объектно ориентированное программирование реализовано на S3 классах, в основе которых лежат обобщённые функции. Эта идея имеет мало общего с классическим объектно ориентированным программированием. Тем не менее пакет R6 реализует классическую реализацию объектно ориентированного программирования в языке R.
Использовать классическое ООП при работе с базами данных довольно удобно. Вы создаёте объект подключения и обращаетесь к его методам. Такую возможность вам даёт пакет rocker , который является обёрткой над DBI .
В rocker реализованы все необходимые функции из DBI . Для начала необходимо создать объект базы данных, и настроить драйвер.
При создании объекта базы данных вы можете использовать аргумент id, тем самым настроить в одной сессии сразу несколько подключений, пометив их идентификаторы.
Под некоторый популярные базы данных в rocker есть встроенные методы настройки драйверов:
Созданный объект базы данных имеет весь функционал DBI в виде своих методов, но т.к. сам объект подключение хранится внутри экземпляра класса, вам не потребуется использовать его в каждой функции. Ниже пример работы с базой данных с помощью ООП и пакета rocker :
По такому же принципу в rocker реализованы и все остальные функции DBI, в том числе функции для работы с транзакциями.
Помимо объекта подключения вы можете хранить в экземпляре класса имя пользователя и пароль используемые при подключении. Реализовать это можно с помощью аргумента protect :
В таком случае при повторном подключение к базе вам не понадобится передавать учётные данные:
Пакет RODBC предоставляет автономный интерфейс для подключения и работы с СУБД через ODBC интерфейс.
RODBC не совместим с DBI , т.е. вы не можете использовать объект подключения созданный с помощью RODBC в функциях предоставляемых пакетом DBI .
Основные функции пакета RODBC
- odbcConnect — Подключение к СУБД через DSN;
- odbcDriverConnect — Подключение к базе через строку подключения;
- sqlQuery — Отправка запроса в СУБД, и получение результата его выполнения. Поддерживает запросы любого типа: SELECT, UPDATE, INSERT, DELETE.
- sqlFetch — Получить целиком таблицу из СУБД;
- sqlTables — Получить список таблиц в базе.
- sqlSave — Создание новой таблицы в базе данных, или добавление новых данных в уже существующую таблицу;
- sqlUpdate — Обновление данных в таблице которая уже существует в СУБД;
- sqlDrop — Удаление таблицы в СУБД;
- odbcClose — Завершение соединения с СУБД.
Пример работы с RODBC
С моей точки зрения RODBC менее функционален чем DBI , но в нём есть все необходимые функции для работы с СУБД.
По умолчанию транзакционность в RODBC выключена. Управление транзакциями осуществляется двумя функциями.
- odbcSetAutoCommit — Переключение между обычным и транзакционным режимом работы с СУБД;
- odbcEndTran — Подтверждение или отмена транзакции.
Включение и отключение транзакционного режима осуществляется функцией odbcSetAutoCommit с помощью аргумента autoCommit.
Два описанных в статье метода работы с базами данных на языке R, DBI и RODBC , достаточно универсальны, и будут работать практически с любой СУБД.
Единственная разница в работе между различными СУБД заключается в процессе подключения. Для большинства популярных СУБД существуют отдельные пакеты которые являются драйверами. Для остальных СУБД необходимо настраивать подключение через ODBC интерфейс используя пакеты odbc или RODBC . Все остальные манипуляции, вне зависимости от выбранной вами СУБД, будут неизменны. Исключением является отправка SQL запросов, в зависимости от SQL диалекта который поддерживается СУБД с которой вы работаете.
P.S.
Если вы дочитали стататью, то наверняка интересуетесь языком R, в таком случае думаю вам будет интересен мой телеграм и youtube каналы, большая часть контента которых посвящена языку R. Подписывайтесь.
Привет, Хабр! Мы команда мониторинга и анализа данных биотехнологической компании BIOCAD. Хотим рассказать вам о том, как мы собираем данные для аналитики из практически всех сервисов компании и при этом вполне успешно справляемся без полноценного дата-инженера. Пост будет интересен как тем, кто только ищет решение для ETL, так и тем, кто уже работает с NiFi или другими аналогичными инструментами и желает познакомиться с наработками, идеями и опытом других команд.
О команде
Наверное, мы похожи на стандартную команду аналитиков данных (BI + DS) в IT-департаменте производственной компании. Работаем в Jira, делаем прототипы с заказчиком в Miro, ведём базу знаний в Confluence, используем Python и SQL для решения ежедневных задач, а итоговую отчётность предоставляем клиентам в Power BI. Исторически сложилось так, что мы сами общаемся с заказчиком, составляем ТЗ, подготавливаем данные, разрабатываем модели, внедряем ML в производство, ну и, конечно, строим дашборды (и не только их).
Этот аспект (вкупе с большим количеством бизнес-направлений) неизбежно приводит к постоянному нашему развитию в различных областях — со временем учишься работать с промышленными данными SCADA, собирать данные из облачного SalesForce, редактировать объекты через API Jira и ещё массе всего.
В итоге это привело к тому, что мы самостоятельно построили аналитическое хранилище данных (PostgreSQL + MongoDB) для предоставления BI отчётности, быстрой отработки различных ad-hoc запросов и решения Data Science задач. Но выделенного дата-инженера у нас нет, и команда самостоятельно выстраивает пайплайны сбора и обработки необходимых данных из 30+ различных сервисов. При таком подходе есть свои плюсы и минусы, нам нравится, как о них написали в блоге Skyeng
О задачах
Четыре задачи, которые являются самыми распространенными в нашей практике, и самостоятельно решаются любым аналитиком данных с помощью NiFi:
Забрать данные из MySQL (или любой другой реляционной БД);
Записать данные в PSQL;
Масштабировать цепочку для сбора-записи данных;
Инкрементально собрать данные с оборудования по REST API.
Мы используем NiFi версии 1.9 (1.9.0.1.0.0.0-90) — обратите на это внимание, если планируете опираться на этот пост при настройке собственной инфраструктуры. Разные версии инструмента имеют отличия, связанные с работой процессоров, Controller Service и их атрибутов.
В рамках этого материала мы не будем говорить о базовой функциональности Apache NiFi, поэтому, если вы не знакомы с инструментом, рекомендуем изучить другие материалы по теме.
Забрать данные из MySQL
Одним из самых распространенных источников данных являются различные реляционные БД. Чтобы извлечь из них информацию с помощью Apache NiFi, понадобится всего два процессора: GenerateFlowFile и ExecuteSQL.
Первый процессор — служит для запуска всей цепочки. Он формирует FlowFile, который поступает на вход следующего процессора и выполняет роль «триггера». GenerateFlowFile может запускаться по требованию или по расписанию, задаваемому выражением Сron. У нас это происходит в ночное время, чтобы не мешать работе наших сервисов днем. Например, на скриншоте ниже видно, что запуск задачи назначен на 02:50. Мы учитываем специфику работы БД, из которых будут загружаться данные. Такой подход позволяет нам равномерно распределять нагрузку и поддерживать стабильную работу всех задействованных систем.
Каждый FlowFile содержит данные (content) и метаданные (attributes), то есть полезную нагрузку для последующих процессоров. В качестве полезной нагрузки FlowFile’а может выступать необходимая нам информация.
Для нашего случая передадим в содержимом FlowFile SQL-скрипт, с помощью которого следующий процессор запросит в БД нужные данные.
Для этого используем поле CustomText во вкладке Properties в окне настроек процессора GenerateFlowFile.
Если данных становится слишком много для ежедневной перезаписи, следует организовать инкрементальный сбор (хорошо подходит для различных логов, событий, показаний датчиков и т. д). Тогда не придется каждый раз заново собирать все данные, а можно «дособирать» только свежие — например, появившиеся после определённой даты или отсортированные по последнему ключу.
Второй процессор — ExecuteSQL — исполняет передаваемый в него запрос и возвращает полученные данные в формате Avro (формат хранения файлов, применяемый во многих продуктах Apache). Стоит отметить, что вместо ExecuteSQL можно задействовать процессор ExecuteSQLRecord. В зависимости от выбранного Controller Service в качестве RecordWriter он может отдавать FlowFile с содержимым в форматах Avro, JSON, CSV и XML, однако его настройка будет несколько сложнее.
В свойствах Controller Service указываем URL-адрес целевой БД (Connection URL), имя класса драйвера (Database Driver Class Name) и путь до драйвера в файловой системе (Database Driver Location). Понять, что нужно писать в поле Class Name, можно по различным инструкциям в сети. В нашем случае мы указали Class Name для JDBC-драйвера — com.mysql.jdbc.Driver — и прописали его расположение на сервере NiFi. Этот драйвер позволяет выполнять SQL-запросы к базе данных и зависит от её типа, поэтому его нужно предварительно загрузить с официального сайта разработчиков [вот ссылка для MySQL, а вот — для PSQL].
Далее, остается выбрать настроенный сервис в свойствах процессора и заполнить остальные обязательные (выделены жирным) атрибуты.
Далее, объединим процессоры, указав при этом тип соединения с помощью отношения success или failure. Так, процессор будет понимать, куда направлять FlowFile c результатами работы или ошибкой. Теперь осталось запустить цепочку, и если все сделано правильно, то на схеме красные квадратики сменятся на зеленые треугольники.
В итоге мы сформировали цепочку из двух процессоров, которые получают таблицу целиком из внешней базы данных MySQL. Остается записать эти данные в новую базу данных для дальнейшей обработки и использования.
Записать данные в PSQL
Теперь, когда у нас есть собранные данные в формате Avro, запишем их в нашу БД PostgreSQL. Сначала создадим таблицу:
Для случаев в будущем (когда наша цепочка уже записала данные в таблицу), нужно предусмотреть удаление старых данных, перед перезаписью используем процессор PutSQL для выполнения TRUNCATE lims_test_active.
Далее, обратимся к процессору PutDatabaseRecord для записи новых данных. Требуется создать еще один Database Connection Pooling Service для подключения к БД, в которую мы будем записывать данные. Также пару слов тут нужно сказать про Record Reader. Это Controller Service для получения схемы данных из FlowFile (его контента или атрибутов).
Перейдем в настройки Record Reader, где в качестве значения для свойства Schema Access Strategy выберем встроенную в Avro схему данных (Use Embedded Avro Schema) и пропишем строку $ в блок Schema Text. Мы выбираем Use Embedded Avro Schema, так как до этого использовали процессор, получающий данные в формате Avro. На выбор стратегии влияет то, куда эта схема записана в пришедшем FlowFile (почитать про настройку можно в официальной документации, например, в описании AvroReader, или на тематических площадках). В свою очередь, содержание строки Schema Text (или Schema Name) зависит от того, в какой из этих атрибутов ранее была помещена схема.
Соединяем процессоры между собой с помощью отношения success, не забывая при этом «выключить» другие отношения (auto terminate). Это нужно сделать для того, чтобы процессор не выдавал ошибку типа: «Realationship X is invalid because Relationship X is not connected to any component and is not auto-terminated» (не все активные соединения знают, что им делать).
Отключить отношения можно вот так:
Схема принимает такой вид:
Если все прошло успешно, то мы увидим в нашей БД таблицу с данными, готовыми для дальнейшей обработки и анализа.
Конечно, если таблиц и сервисов много, то не стоит делать такую цепочку под каждую таблицу, это займет много времени и будет очень затратно при поддержке. Поэтому далее расскажем, как можно масштабировать этот пайплайн, для работы с десятками и даже сотнями таблиц.
Масштабировать цепочку для сбора-записи данных
В рамках этого поста мы опустим ряд моментов, связанных с логированием, мониторингом, разделением и слиянием больших файлов, а также некоторыми настройками процессоров. Если у вас возникнут вопросы, мы постараемся ответить на них в комментариях.
Основная идея масштабирования состоит в том, чтобы держать список таблиц в одном месте, а при его обработке разделять и распараллеливать сборы. Мы храним список таблиц и запросов в XML, но подойдут и другие структуры данных вроде JSON или простого текста с разделителями. Вот так выглядит наш журнал для сбора таблиц из БД:
Структура документа следующая: теги задают начало и конец списка таблиц, а теги — структуру одной таблицы. Теги определяют SQL скрипт, а теги хранят в себе наименование таблицы для сохранения данных, полученных в результате выполнения SQL запроса.
Двумя важными компонентами масштабируемого пайплайна являются процессоры SplitXML и EvaluateXPath.
Первый нужен, чтобы разделить FlowFile с изначальным XML на несколько файлов — по одному на каждую таблицу (теги ). Второй выполняет XPath-запросы (язык запросов к элементам XML-документа) по отношению к содержимому FlowFile. Их результаты записывает в соответствующие атрибуты FlowFile. Другими словами, процессор нужен для того, чтобы определенную информацию из контента переложить в атрибуты collection и query, которые теперь можно использовать в любом месте.
После того как EvaluateXPath выполнил свою задачу, в дело вступает ExecuteSQL, который реализует полученный на предыдущем шаге SQL-запрос (атрибут query) для загрузки нужных данных из БД.
На этом шаге можно настроить количество параллельно исполняемых задач процессором. Это указывается во вкладке Scheduling в настройках процессора ExecuteSQL.
Далее, используем PutSQL для очистки старых данных в нужной таблице БД, исполняя через него команду TRUNCATE, ее передаем в атрибуте SQL Statement, используя Expression Language для подстановки нужного имени таблицы.
Для вставки полученных данных в нужную таблицу используем процессор PutDatabaseRecord с настройками INSERT и указанием целевой таблицы $ .
В итоге мы получили масштабируемую EL-цепочку, которую можно использовать для сбора данных из множества таблиц.
Инкрементально собрать данные с оборудования по REST API
Основная идея инкрементального сбора заключается в том, чтобы собирать только новые данные и копить их у себя в БД. Например, ночью мы получаем данные за прошлый день и сохраняем их.
Каждый день мы обновляем список серий, ошибок, измерений, калибровок, событий и других параметров с различных сервисов, систем на производстве и в лабораториях. Для получения этих данных можно использовать различные промышленные протоколы например OPC UA, но благодаря усилиям коллег, занимающихся разработкой АСУ ТП, мы можем забирать их через REST API. Если интересно, то можно почитать их пост о системе управления биореакторами на базе openSCADA и отечественных контроллеров.
Далее — общий вид ETL-цепочки для этого кейса:
В этом случае GenerateFlowFile задает список параметров в XML — так же, как и в предыдущем кейсе.
Далее, в работу вступают уже знакомые процессоры SplitXML и EvaluateXPath. Мы разбиваем XML на разные FlowFile и записываем их содержимое в атрибуты device_name и parameter.
Следующий шаг — получить последнюю дату успешного сбора данных по конкретному датчику. Выполним этот запрос с помощью ExecuteSQLRecord и получим последнее значение end_time из нашей таблицы с логами.
Далее, передаем эту информацию в EvaluateJsonPath, который запишет её в атрибут start.
Значение даты в start пригодится нам для реализации нового запроса к данным. Для формирования его тела используем процессор ReplaceText.
Как вы можете видеть, в параметр end_time мы подставляем текущую дату, используя now() из Expression language.
Чтобы привести полученные данные в необходимый вид перед записью в БД, используем процессор JoltTransformJSON. Отладить Jolt-трансформации удобно в песочнице на сайте Jolt Transform Demo.
Дальнейшие шаги по добавлению новых полей, логированию и записи в БД тут опустим, думаем, что принцип уже понятен. Такая ETL-цепочка позволяет нам ежедневно собирать данные в PSQL через REST API и довольно легко масштабируется на новые сервисы и датчики.
Приходилось ли вам решать аналогичные задачи? Если у вас есть предложения, как можно улучшить наши пайплайны или есть вопросы — напишите, пожалуйста, в комментарии, интересно узнать о вашем опыте работы с дата-инженерами в вашей команде.
JDBC — это платформенно независимый промышленный стандарт взаимодействия Java-приложений с реляционными базами данных. Впервые был включен в состав JDK 1.1 в 1997 году. JDBC управляет:
- подключением к базе данных;
- выдачей запросов и команд;
- обработкой данных, полученных из базы.
Как работает JDBC
Пакет JDBC состоит из двух главных компонентов:
- API (программного интерфейса), который поддерживает связь между Java-приложением и менеджером JDBC;
- Драйвера JDBC, который поддерживает связь между менеджером JDBC и драйвером базы данных.
Соединение с базой устанавливается по особому URL. При этом разработчику не нужно знать специфику конкретной базы — API выступает в качестве посредника между базой и приложением. Это упрощает как процесс создания приложения, так и переход на базу данных другого типа.
Этапы подключения к базе данных
- Установка базы данных на сервер или выбор облачного сервиса, к которому нужно получить доступ.
- Подключение библиотеки JDBC.
- Проверка факта нахождения необходимого драйвера JDBC в classpath.
- Установление соединения с базой данных с помощью библиотеки JDBC.
- Использование установленного соединения для выполнения команд SQL.
- Закрытие соединения после окончания сеанса.
Рассмотрим каждый из этих шагов подробнее.
Установка SQLite
СУБД (система управления базами данных) SQLite отличается компактными размерами и простотой установки — для ее использования не нужна инсталляция дополнительных сервисов. Вся информация хранится в одном файле формата .db, который нужно поместить в папку с программой. Учебную базу можно скачать здесь.
Освойте программирование, backend-разработку на Java, Spring и основы DevOps.
Импорт JDBC в Java-приложение
Для использования JDBC, как и в случае со всеми остальными приложениями на платформе Java, в системе должен быть установлен JDK. Код для работы с JDBC можно писать как в среде разработки (IDE), так и в обычном текстовом редакторе. Простейшая программа может выглядеть так:
class WhatIsJdbcpublic static void main(String args[])System.out.println(«Hello World»);
>
>
Скомпилируйте этот код с помощью команды:
javac WhatIsJdbc.java
Теперь, когда программа готова, можно импортировать библиотеки JDBC. Для этого вставьте данный ниже код перед строками программы «Hello, World»:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
Каждый из импортированных модулей предоставляет доступ к классам, которые необходимы для взаимодействия Java-приложения с базой данных:
- Connection подготавливает подключение к базе.
- DriverManager обеспечивает подключение. Другая опция — модуль DataSource.
- SQLException обрабатывает SQL-ошибки, возникающие при взаимодействии приложении и базы данных.
- ResultSet и Statement моделируют наборы результатов данных и операторы SQL.
Добавление JDBC-драйвера в classpath
JDBC-драйвер — это класс, обеспечивающий взаимодействие интерфейса JDBC API с базой данных определенного типа. Драйвер для SQLite представляет собой .jar-файл — его нужно добавить в classpath, как показано ниже:
java.exe -classpath /path-to-driver/sqlite-jdbc-3.23.1.jar:. WhatIsJdbc
Установление соединения с базой данных
Теперь в classpath есть доступ к драйверу. Вставьте приведенный ниже код в файл с вашей первой программой:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
class WhatIsJdbcpublic static void main(String[] args) Connection conn = null;
try String url = «jdbc:sqlite:path-to-db/chinook/chinook.db»;
conn = DriverManager.getConnection(url);
System.out.println(«Соединение установлено»);
> catch (SQLException e) throw new Error(«Ошибка при подключении к базе данных», e);
> finally try if (conn != null) conn.close();
>
> catch (SQLException ex) System.out.println(ex.getMessage());
>
>
>
Создание запроса к базе данных
В приведенном ниже примере показано, как создать запрос к базе SQLite, используя Connection и Statement:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
class WhatIsJdbcpublic static void main(String[] args) Connection conn = null;
try String url = «jdbc:sqlite:path-to-db-file/chinook/chinook.db»;
conn = DriverManager.getConnection(url);
Statement stmt = null;
String query = «select * from albums»;
try stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) String name = rs.getString(«title»);
System.out.println(name);
>
> catch (SQLException e ) throw new Error(«Problem», e);
> finally if (stmt != null) < stmt.close(); >
>
> catch (SQLException e) throw new Error(«Problem», e);
> finally try if (conn != null) conn.close();
>
> catch (SQLException ex) System.out.println(ex.getMessage());
>
>
>
Результатом выполнения этого кода будет вывод списка музыкальных альбомов из учебной базы в консоль.
Возможностей JDBC API достаточно для реализации простых приложений. Для более масштабных решений чаще используют JPA API, который позволяет сохранять Java-объекты в базе данных.
Читайте также: