[Перевод] Сказ о том, как SQL время экономит
Существует компания, предоставляющая платформу для работы с большими данными. Эта платформа позволяет хранить генетические данные и эффективно управлять ими. Для полноценной работы платформы требуется возможность обрабатывать динамические запросы в среде выполнения не более чем за две секунды. Но как преодолеть этот барьер? Для трансформации существующей системы было решено использовать хранилище данных SQL. Заглядывайте под кат за подробностями!
Цикл статей «Digital Transformation»
Технологические статьи:
1. Начало.
2. Лотерея в облаке.
3. Блокчейн в банке.
4. Учим машину разбираться в генах человека.
5. Учим машину разбираться в языках.
6. Сказ о том, как SQL время экономит.
7. Loading…Серия интервью с Дмитрием Завалишиным на канале DZ Online:
1. Александр Ложечкин из Microsoft: Нужны ли разработчики в будущем?
Ситуация
Поскольку примерно 99,5% генома человека являются общими, при секвенировании ДНК обычно сравнивают образцы последовательности с отдельными известными эталонными геномами. Результаты сравнения сохраняют в файл с необработанными данными с расширением VCF. Он содержит только различия между образцами генома и эталонным геномом. В нем записан примерно один миллион строк, каждая из которых представляет мутацию гена, то есть генный индекс и значение, отличающееся от значения эталонного генома для данного индекса.
Полученный файл VCF проходит процедуру нормализации, которая готовит его к применению различных сценариев, доступных на платформе. Она представляет собой последовательность шагов по добавлению метаданных и аннотаций из внешних баз генных данных. В процессе нормализации создается огромный файл, содержащий около 1 млн строк и примерно 50 столбцов.
Проблема
Медикам и исследователям нужна возможность обрабатывать динамические запросы в среде выполнения, используя панель мониторинга для обзора результатов секвенирования пациентов. Базовый запрос поддерживает фильтрацию образцов по содержимому нескольких столбцов. Расширенные запросы могут связывать образец с эталонной базой данных (в которой 60 млн строк). Более развернутый сценарий подразумевает ряд операций присоединения в рамках одного запроса, что позволяет проводить генетический анализ родословной и связываться с репозиторием внешней эталонной базы данных.
Поскольку запросы формируются с помощью панели мониторинга, требовалось получать результат менее чем за две секунды. Если на получение результата уходило больше времени, операция считалась выполненной неуспешно. Обработка таких запросов в среде выполнения (например, если каждый образец содержит большое количество строк и столбцов) требует значительного объема вычислительных ресурсов и занимает много времени.
Взаимодействие
Целью нашего сотрудничества с этой перспективной компанией было измерение производительности обработки запросов, чтобы в дальнейшем оценивать эффективность ряда масштабируемых архитектур системы хранения данных:
Запуск Impala в кластере Cloudera
Impala рекомендуется использовать для обработки запросов SQL в рамках интерактивной исследовательской аналитики больших наборов данных, а модули Hive и MapReduce — для выполнения пакетных задач с большой длительностью обработки.
В Impala также используется колоночная система хранения данных, которая гораздо эффективнее обрабатывает запросы описанного выше типа. Это обусловлено способом физического хранения данных на диске. При выполнении запросов SELECT и проецировании нескольких столбцов с диска считываются только соответствующие блоки выбранных столбцов, поскольку данные в разных строках одного столбца рас-положены рядом друг с другом. Дополнительные сведения о колоночном хранилище см. здесь.
Запуск Spark в кластере HDInsight
Spark — технология, которую рекомендуется использовать для обработки больших данных в HDFS в первую очередь. Spark успешно подготовит систему к использованию других технологий (например, Impala). Дополнительные сведения о технологии Spark см. здесь.
Хранилище данных SQL
Хранилище данных SQL представляет собой распределенную систему управления базами данных для массово-параллельной обработки (MPP). Благодаря разнесению данных и обработке на разных узлах хранилище данных SQL обеспечивает гибкие возможности масштабирования. Дополнительные сведения о хранилище данных SQL см. здесь.
Запросы
Мы согласовали количество запросов, поддерживающих типовые сценарии. Мы использовали одинаковые файлы образцов в рамках различных систем хранения данных и проверили, сколько времени занимает обработка запросов для каждого из таких файлов. Для наглядности следующие запросы написаны «по образу и подобию SQL». Они обработаны каждой системой хранения данных в соответствии с собственным синтаксисом.
Таблица эталонной базы данных содержит 60 млн строк в рамках аналогичной структуры файла VCF, а также дополнительные метаданные для каждого гена. В процессе нормализации эти данные используются для аннотирования строк в VCF-файле образца.
В следующей таблице представлено описание запросов и результаты измерения производительности:
| . | Технология | Impala | Spark в HD Insight | Spark в Cloudera | Хранилище данных SQL | Хранилище данных SQL |
|---|---|---|---|---|---|---|
| . | Тип кластера | 3 x D13 | 3 x D12 | 3 x DS13 (5 дисков P30 каждый) | 400 DWU (ед. хранилища данных) | 1000 DWU (ед. хранилища данных) |
| Индекс | Сценарий | с | с | с | с | с |
| 1 | Выбрать из образцов, упорядочить по позиции гена, взять первые 100 записей | 2 | 5 | 2 | 1 | 1 |
| 2 | Выбрать из таблицы эталонной базы данных, фильтровать по одному полю, упорядочить по другому полю, взять первые 100 записей | 11 | 96 | 38 | 2 | 6 |
| 3 | Связать образец с таблицей эталонной базы данных по идентификатору гена, упорядочить по позиции гена, взять первые 100 записей | 775 | 347 | 168 | 15 | 6 |
| 4 | Связать образец с таблицей эталонной базы данных по идентификатору гена, взять первые 100 записей | 211 | 275 | 121 | 1 | 1 |
| 5 | Связать образец с таблицей эталонной базы данных по идентификатору гена, фильтровать по од-ному полю, упорядочить по позиции гена, взять первые 100 записей | 13 | 111 | 61 | 1 | 1 |
| 6 | Связать образец с таблицей эталонной базы данных по идентификатору гена, группировать по одному полю, упорядочить по другому полю, взять первые 100 записей | 37 | 41 | 23 | 5 | 2 |
| 7 | Выбрать из таблицы эталонной базы данных, группировать по одному полю, упорядочить по другому полю, взять первые 100 записей | 12 | 25 | 20 | 15 | 7 |
| 8 | Выбрать из файла образца строки, которые не существуют в таблице эталонной базы данных, взять первые 100 записей | 37 | 367 | 432 | 5 | 2 |
| 9 | Связать четыре образца по идентификатору гена, фильтровать по одному полю, упорядочить по другому полю, взять первые 100 записей | 7 | 16 | 20 | 3 | 2 |
| 10 | Добавить 1 млн записей в таблицу, содержащую 60 млн записей | 7 | 35 | 33 | 4 | 3 |
| 11 | Добавить 1 млн записей в новую пустую таблицу | 4,5 | 35 | 33 | 6 | 21 |
| 12 | Добавить 60 млн записей в новую пустую таблицу | 140 | 500 | 280 | 214 | 124 |
Заключение
Учитывая полученные результаты, указанные выше альтернативные варианты не удовлетворяют требованию к длительности обработки (2 с).
Мы рекомендуем выполнить следующие действия.
- В краткосрочной перспективе: используйте реляционную СУБД (например, SQL Server, MySql, Postgres) для обработки большинства запросов в реальном времени, отправленных с панели мониторинга, которые нужно обработать менее чем две секунды. Убедитесь в том, что план выполнения не предусматривает полное сканирование таблицы.
- Разверните небольшой кластер Hadoop (включая Spark, Impala, HDFS и Parquet). Используйте его для разгрузки отдельных сложных веб-запросов, отправленных с панели мониторинга, которые не требуют отклика реляционной СУБД в адрес Impala менее чем за секунду. Через некоторое время увеличьте число запросов, обрабатываемых за пределами реляционной СУБД, используя Impala для выполнения наиболее сложных и ресурсоемких задач.
- Используйте дополнительные специальные запросы и оптимизируйте подготовку данных с помощью Impala и Spark. Выполните предварительную обработку необработанных данных с помощью Spark и Impala, а затем загрузите соответствующие срезы данных в реляционную СУБД. Используйте технологию Impala для построения новых специальных запросов в веб-приложении и добавьте соответствующие изменения в интерфейс (например, «Подождите…», «Загрузка…»), чтобы увеличенное время отклика не влияло на работу пользователей.
