SQLite позволяет стать ближе к данным

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

В большинстве случаев механизмов, встроенных в платформу достаточно, чтобы реализовать сложную логику операций с таблицами, поэтому нет необходимости применять какие-либо дополнительные инструменты. Однако, при обработке больших наборов возникают ситуации, когда можно существенно повысить скорость получения выборок, используя СУБД, например, в ситуациях, если:

• Данные ранее были загружены в базу и построены индексы
• Для получения выборки требуется связывать множество таблиц
• Из большой базы необходимо отфильтровать и извлечь небольшой набор

Применение СУБД

Если данные уже находятся в базе и выполнены подготовительные операции, такие как индексация, сбор статистики и прочее, то СУБД способны обеспечить высокую скорость выполнения запросов. Однако использование «внешних» СУБД связано с рядом проблем как финансового, так и временного плана, основными из которых являются:

• необходимость приобретения СУБД;
• необходимость развёртывания, администрирования и поддержки СУБД;
• необходимость вручную писать SQL-запросы, а, следовательно, иметь персонал, соответствующей квалификации, либо приглашать специалистов со стороны.

Тем не менее, если данные постоянно накапливаются, объемы увеличиваются, а для получения выборки требуется связывать множество таблиц, то выбора практически не остаётся: импорт при помощи SQL-запроса занимает меньше времени, чем выполнение сценария в аналитическом приложении.

Одной из основных причин, по которой СУБД при работе с данными имеют более высокую производительность, чем встроенные средства аналитического приложения, заключается в том, что в приложении каждый обработчик в сценарии работает независимо от других. В то же время SQL-запрос определяет всю логику задачи, что позволяет СУБД оптимизировать процесс работы с таблицами.

Например, внутреннее соединение для трёх таблиц можно описать с помощью всего лишь одного SQL-запроса:

SELECT 
  table3.room_number 
FROM 
  table1 INNER JOIN table2 ON 
  table1.user_id = table2.user_id INNER JOIN table3 ON table2.phone_id = table3.phone_id 
WHERE 
  table1.username = 'name'

При этом следует отметить, что использование SQL-запросов к внешним СУБД не имеет каких-либо принципиальных преимуществ перед использованием встроенных в платформу функций или компонентов. Результат и в том, и в другом случае будет одинаковым. Это вопрос масштаба задачи и поиска компромисса между производительностью и затратами.

Внешняя СУБД – это еще один программный продукт, который необходимо приобретать, развёртывать, администрировать и поддерживать. Она предоставляет дополнительные возможности, но увеличивает сложность проекта.

Встраиваемые СУБД

Помимо «тяжелых» баз данных, есть целый класс компактных встраиваемых СУБД. Они отлично подходят для обработки средних по размеру данных.

На практике в 80% задач анализа объемы обрабатываемой информации не так уж и велики, в том смысле, что хотя они и превосходят возможности встроенных средств в аналитических приложениях, применение продвинутых СУБД для них являются избыточными. Встраиваемые СУБД «ближе» к аналитику, хотя и имеют некоторые ограничения.

Встраиваемыми называются системы управления базами данных, тесно связанные с аналитическим приложением, работающие на том же компьютере, не требуя профессионального администрирования. Они не требует самостоятельной установки, предназначены для локального хранения данных «своего» приложения и не рассчитаны на коллективное использование в сети.

Встраиваемые СУБД не используют программу-сервер, а представляют собой библиотеку статически или динамически присоединённую к основной программе. Они обладают высокой производительностью и малым расходом памяти, благодаря специализированному API, минимизирующему число операций чтения-записи.

Как правило, встраиваемые СУБД имеют ограниченный по меркам БД максимальный объем: от нескольких десятков или сотен гигабайт, и ограниченную совместимость с SQL-92. Кроме того, они являются однопользовательскими системами и не содержат средств архивации и репликации данных.

Тем не менее, если учитывать все ограничения встраиваемых СУБД и разумно относиться к выбору класса решаемых задач, они являются привлекательным решением для работы совместно с другими компонентами аналитической платформы.

SQLite — встроенная база данных

Одной из наиболее популярных встраиваемых СУБД является СУБД SQLite. Ее основные преимущества:

• открытость;
• бесплатность;
• SQL-совместимость (хотя и неполная);
• отсутствие потребности в администрировании;
• простая процедура подключения;
• высокая производительность, при достаточном объеме ОЗУ;
• возможность шифрования данных.

В то же время есть и ограничения:

• не предусмотрена совместная работа с данными;
• отсутствуют некоторые элементы «продвинутых» СУБД, такие как триггеры, хранимые процедуры и т.д.;
• есть ограничения по объему хранимых данных.

Если понимать все ограничения и разумно подходить к оценке сложности задач, использование СУБД SQLite является отличным решением для операций с данными. Она дополняет и расширяет возможности встроенных в платформу компонентов обработки таблиц.

SQLite в Loginom

Создавать и редактировать таблицы в базах SQLite можно в DB Browser for SQLite. Доступны и другие инструменты, как платные, так и бесплатные. Однако если вы работаете с Loginom, то можно обойтись без сторонних продуктов, поскольку манипуляции с таблицами реализованы непосредственно в платформе.

Процедура создания таблицы в Loginom и заполнение ее данными несложная. Для этого достаточно построить небольшой сценарий, представленный на рисунке 1.

В данном сценарии узел «Текстовый файл» импортирует таблицу, которая будет загружена в базу данных. Узел «SQLite» – подключение к базе данных, в которую производится загрузка.

Доступна загрузка в существующую таблицу или создание новой структуры. При этом мастер настройки предлагает выбрать тип экспорта (Рисунок 2).

Там же, в мастере можно создать новую таблицу в базе SQLite. Необходимо задать её имя и структуру: имена полей, типы, размеры. По умолчанию структура соответствует загружаемому набору данных, но, при необходимости, её можно сформировать вручную. В созданную таблицу можно загружать данные (Рисунок 3).

Процедура извлечения данных из базы SQLite также проста. Её реализует следующий сценарий, представленный на рисунке 4. Здесь так же создаётся подключение-ссылка, определяющая источник, и узел импорта из базы данных SQLite, в котором необходимо настроить параметры импорта. Импортировать можно, выбрав таблицу/представление в базе, либо написав SQL-запрос.

Ценной дополнительной функцией СУБД SQLite является возможность шифрования данных. Особенно значимо это для компаний, специализирующихся в области сбора, обработки и анализа данных. Действительно, зачастую сами данные представляют собой даже большую ценность, чем алгоритмы их анализа.

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

SQLite в решениях

Например, в решении Loginom Data Quality СУБД SQLite используется при очистке баз данных адресов, телефонов и других доменов. Очистка производится путём сравнения обрабатываемой базы с эталонной. При этом объем эталонной базы немаленький – сотни миллионов записей.

Использование в решении SQLite, которая хранит все данные в памяти, позволяет выполнять очистку данных быстро и удобно, без установки дополнительного ПО или каких-либо других действий.