SQL Server 2012

Использование обновляемых таблиц для отчетов в реальном времени

Дон Маккензи

Продукты и технологии:

SQL Server 2012, SQL Server 2014, Hadoop

В статье рассматриваются:

  • управление огромными таблицами данных SQL Server;
  • совместное использование индексов columnstore и разбиения на разделы (partitioning);
  • переключение разделов для упрощения прямых обновлений (live updates).

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

  • сумма в долларах за месяц по конкретному клиенту за последний год;
  • количество еженедельно закупаемого товара за последние полгода;
  • количество и сумма в долларах по заказу и по товару за последние 10 дней (включая сегодняшний день и по состоянию на данное время).

Удовлетворение столь разнообразных запросов может стать сложной задачей для проектировщиков системы, особенно на предприятиях с высокой интенсивностью транзакций. Хороший пример — Cox Digital Solutions (CDS). Эта компания обрабатывает около 20 000 транзакций в секунду.

Архитектура моей системы для CDS поддерживает актуальную отчетность с историей по годам в одной таблице базы данных SQL Server, которая обновляется каждые 10 минут. Это решение использует сразу два средства SQL Server — разбиение на разделы (partitioning) и индексацию columnstore — для достижения этого времени ответа при запросах к таким массивам данных.

Первые показы

CDS предоставляет рекламные услуги в Интернете. «Бесплатные телешоу» поддерживаются рекламой, а «бесплатные» веб-сайты — рекламными объявлениями, отображаемыми на страницах этих сайтов. CDS помогает публикаторам веб-сайта должным образом показывать рекламные объявления. Компания регистрирует информацию о каждом просмотре каждого рекламного объявления (это называется показом [impression]). Кроме того, она записывает другую информацию, такую как щелчки на рекламных объявлениях. Это генерирует приблизительно два миллиарда записей ежедневно (занимающих 1,5 Тб в сжатом виде).

Каждый показ, или транзакция, включает различные компоненты. Здесь два клиента: рекламодатель и публикатор на веб-сайте. Кроме того, в этом участвует продукт — реклама. Рекламодатели размещают заказы, а значит, частью записи является и идентификатор заказа. С рекламодателя взимается плата, а публикатору выплачивается некая сумма. Поэтому запись транзакции содержит множество полей, отражающих многочисленные мелкие детали этой транзакции.

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

Бизнес-пользователи зачастую не могут дожидаться данных по текущему дню до следующего дня. Им нужно знать, как реклама доставляется на протяжении всего дня. Это заставило меня выбрать архитектуру, которую я опишу здесь. Вы сможете адаптировать ее для многих приложений, где постоянный поток новых данных добавляется к уже большому набору данных.

Избыток данных

Вряд ли вы захотите поместить 500 миллиардов записей (и 500 Тб несжатых данных) в таблицу SQL Server. Необработанные транзакции хранятся в Hadoop Distributed File System (HDFS). Hadoop — отличное средство для хранения и анализа больших массивов данных, но отвечает на запросы ужасно долго (узнать больше можно на hadoop.apache.org).

Вряд ли вы захотите поместить 500 миллиардов записей в таблицу SQL Server.

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

Потребности большинства пользователей можно удовлетворить с помощью агрегированных данных. Транзакции суммируются по часу (а также по рекламодателю, веб-сайту, конкретной рекламе и другим ключевым атрибутам) и помещаются в таблицу базы данных SQL Server. Часы важны, потому что рекламная активность в полдень резко отличается от таковой в два часа ночи. Это процесс суммирует 70 миллионов транзакций в 50 000 записей в час.

Разбиение таблицы SQL Server на разделы

В SQL Server 2005 был введен неплохой уровень поддержки разбиения таблиц на разделы, который был усовершенствован в последующих версиях. Такая операция позволяет разбить большую таблицу на несколько (или множество) меньших внутренних таблиц. SQL Server хранит и индексирует каждую малую таблицу, или раздел, по отдельности (см. рис. A).

Разбитая на разделы таблица в SQL Server
Рис. A. Разбитая на разделы таблица в SQL Server

Partitioned table Разбитая на разделы таблица
External Appearance: One large table Представление извне: одна большая таблица
Internal Storage: Date ranges separated Внутреннее представление: раздельные диапазоны данных
5 PM 30 July 5 PM, 30 июля
4 PM 30 July 4 PM, 30 июля
Hour Час
2 July 2 июля
1 July 1 июля
Day День
June Июнь
May Май
Month Месяц

Partition Function (по сути, список граничный значений, отделяющих разделы) сообщает SQL Server, как разбить таблицу на разделы. В таблице часто присутствует столбец datetime (как в моей таблице Revenue), полезный для отделения разделов. В таблице есть разделы по месяцам для данных, старее одного месяца, разделы по дням для данных, не старее недели, и разделы по часам для самых свежих данных. Все эти небольшие разделы имеют одинаковое имя таблицы, одну схему и кажутся приложению одной крупной таблицей.

Когда SQL Server обрабатывает запрос с блоком WHERE, включающим столбец даты, она определяет, какие разделы нужны, и игнорирует остальные. Все запросы включают дату в блоке WHERE. Это повышает производительность обработки запроса, ограничивая его одним или несколькими меньшими хранилищами данных.

Эти записи помещаются в таблицу SQL Server с именем Revenue. Интерактивное веб-приложение дает возможность пользователю запрашивать эту таблицу для проверки выполнения заказа, просмотра того, как та или иная реклама работает на разных сайтах, анализа бюджетов и т. д. Эта таблица обновляется и почасовые данные (50 000 записей) заменяются через каждые 10 минут.

Организация большой таблицы базы данных

Эта таблица все еще слишком велика: 500 миллионов записей, занимающих 60 Гб. Обычная таблица такого размера была бы очень медленной в обновлении и при запросах. Добавление индексов может помочь, но они еще больше замедляют обновления. По мере увеличения таблиц создаются дополнительные уровни индексов (глубина индексов), что уменьшает пользу от них.

Многомерные OLAP-кубы (Multidimensional OLAP Cubes) — один из подходов для запросов к большим объемам данных, но они требуют крайне тщательного проектирования и планирования до развертывания. А после развертывания их уже нельзя обновлять. OLAP-кубы обычно перестраивают раз в день или даже реже.

Мой подход к управлению этой массивной таблицей основан на комбинации двух методов: разбиении на разделы и индексации columnstore. Существенное ограничение таблиц, индексированных по columnstore, в SQL Server 2012 заключается в том, что их нельзя обновлять, пока активен индекс. Вы должны отключать индекс на время обновления и полностью перестраивать его после обновления. Это основополагающий процесс, когда вы имеете дело с крупной таблицей. Я опишу способ преодоления этого ограничения. Такие ограничения были ослаблены в SQL Server 2014 (см. «Разбиение таблицы SQL Server на разделы»).

Мой подход к управлению этой массивной таблицей основан на комбинации двух методов: разбиении на разделы и индексации columnstore.

Разные размеры разделов — данные за месяц, за день и за час — совпадают с наиболее распространенными шаблонами запросов бизнес-пользователей. Они также совпадают с распространенными шаблонами обновления и обслуживания. В вашем приложении могут понадобиться другие размеры разделов и разбиение по значениям, отличным от дат. В других приложениях может оказаться эффективнее поддерживать только разделы с данными за час или за день. SQL Server 2012 поддерживает до 15 000 разделов на таблицу, что обеспечивает создание разделов на каждый час вплоть до 20 месяцев. За более подробными и авторитетными пояснениями разбиения на разделы обратитесь к документации SQL Server по ссылке bit.ly/1mtZkfl.

Индексы columnstore

Самая интересная новая функциональность в SQL Server 2012 — индексы columnstore. Это мощное средство для увеличения производительности запросов. В случае массивных таблиц, как у меня, я наблюдал стократное повышение производительности!

Серьезная проблема в SQL Server 2012 состоит в том, что таблица, индексированная columnstore, например OLAP-куб, предназначена только для чтения. Вы не можете обновить ее, не удалив индекс (или, как минимум, отключив его) и не собрав его заново после обновления. Удостоверившись в увеличении производительности, я стал искать способ преодоления ограничения «только для чтения» и обнаружил, что ответом является разбиение на разделы.

Я рассматриваю формирование индексов columnstore как создание вертикальных срезов таблицы по столбцу.

Если разбиение на разделы я рассматриваю как создание горизонтальных срезов таблицы (в моем случае — по времени), то формирование индексов columnstore — как создание вертикальных срезов таблицы по столбцу. Как и все некластеризованные индексы, этот индекс хранится отдельно от основной таблицы данных. Каждый столбец, проиндексированный columnstore, хранится индивидуально в собственном внутреннем контейнере больших двоичных объектов (blob) (далее для краткости просто двоичных объектов). Это сильно отличается от составного индекса (composite index) с несколькими индексированными или включенными вместе столбцами.

Поскольку базовая таблица предназначена только для чтения, данные в двоичных объектах columnstore не требуется обрабатывать при обновлениях и их можно сжимать с помощью одного из нескольких алгоритмов, выбираемых SQL Server в зависимости от типов данных. Поэтому индекс зачастую значительно меньше, чем исходные данные (рис. 1).

Применение индексов columnstore для управления большими хранилищами данных
Рис. 1. Применение индексов columnstore для управления большими хранилищами данных

Columnstore indexing Индексация columnstore
Base Table (row based) Базовая таблица (основана на строках)
Columnstore index each column stored separately with compression

Индекс columnstore

Каждый столбец хранится отдельно со сжатием

startDate (hour) startDate (hour)
advertiserld advertiserId
webSiteld webSiteId
adld adId
impressions показы
clicks щелчки

Мой индекс columnstore включает каждый столбец в таблице. Каждый столбец хранится отдельно. Запрос, который ссылается только на несколько столбцов, будет считывать лишь эти столбцы, игнорируя остальные. Запросам не требуется считывать строки в базовой таблице. Все значения данных находятся в индексе, а данные для каждого столбца в индексе сжаты, чтобы свести к минимуму число операций чтения с диска.

Помните: моя таблица также разбита на разделы, поэтому каждый раздел и каждый столбец в разделе имеют свое хранилище (рис. 2). SQL Server достаточно интеллектуален для чтения лишь необходимых данных (разделов и столбцов) для конкретного запроса, что существенно сокращает время от запроса до ответа по сравнению с построчным сканированием «обычной» таблицы или даже поиском необходимых данных по обычным индексам.

Применение разделов и индексов columnstore для разбора данных
Рис. 2. Применение разделов и индексов columnstore для разбора данных

Columnstore index (each column stored separately) Индекс columnstore (каждый столбец хранится отдельно)
Partitions (each time period stored separately) Разделы (хранятся отдельно за каждый период времени)
Monthly Ежемесячно
Daily Ежедневно
Hourly Ежечасно
Start hour StartHour
Ad Id AdId
Advertiser Id AdvertiserId
Site Id SiteId
# Clicks Число щелчков
# Impressions Число показов
5 PM 30 July 5 PM, 30 июля
June Июнь
May Май

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

Иногда блоки JOIN заставляли SQL Server выполнять для запроса полное сканирование таблицы, особенно когда вторичный атрибут был в блоке WHERE. Теперь многие из этих вторичных (и некоторых третичных) атрибутов хранятся в гораздо более широкой таблице Revenue, и при индексации columnstore обращений к ним не будет, пока они не понадобятся в конкретном запросе. В спроектированной мной таблице Revenue всего 25 столбцов. За более подробным и авторитетным описанием индексов columnstore обращайтесь к документации SQL Server по ссылке bit.ly/1zbsju1.

Обновление

Комбинация разбиения на разделы и индексации columnstore позволяет разделить массивную таблицу на управляемые сегменты умеренного размера. Однако из-за индекса columnstore обновления в SQL Server 2012 запрещены. Хотя каждый раздел хранится отдельно, все они обрабатываются как одна таблица, поэтому отключение, удаление или перестройка индекса затрагивает всю таблицу. Перестройка индекса columnstore может занимать полчаса, в течение которого таблица недоступна для запросов. А мне нужно, чтобы таблицу можно было обновлять через каждые 10 минут и чтобы она была доступна всегда.

Ключ к решению этой проблемы — в выражении, относящемся к разделам: ALTER TABLE ... SWITCH PARTITION. Это DDL-выражение перемещает раздел данных из одной таблицы в другую. Данные не копируются, а просто внутренняя информация схемы переупорядочивается так, чтобы хранилище раздела, принадлежавшее одной таблице, теперь принадлежало другой. Этот процесс контролируется определенными правилами, но ими можно управлять. Исчерпывающее описание переключения разделов см. в статье «Transferring Data Efficiently by Using Partition Switching» из TechNet Library (bit.ly/1ts04Xv).

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

Давайте вернемся к исходной задаче. С компьютеров, доставляющих рекламу, несется лавина данных (20 000 транзакций/с), направляемых в Hadoop круглосуточно. Я могу использовать Hadoop для суммирования данных текущего часа в примерно 50 000 записей SQL Server для одного только этого часа. Соответствующий запрос Hadoop занимает примерно пять минут. Я не могу обновить свою основную таблицу Revenue, но могу помещать эти строки в новую таблицу (разбитую на разделы и проиндексированную columnstore), которой я присвоил имя RevenueIn. У нее та же схема, что и у таблицы Revenue, но она пуста и ее индекс columnstore отключен, поэтому я могу вставлять строки.

После вставки строк за час можно перестроить индекс columnstore в таблице RevenueIn. Часовые данные умещаются точно в один раздел. Я индексирую всего 50 000 строк, что отнимает меньше минуты. Теперь можно использовать SWITCH PARTITION для перемещения одного этого раздела, который уже проиндексирован, в основную таблицу Revenue, и он мгновенно становится доступным для запросов отчетности. Заметьте, что выражение SWITCH PARTITION работает, только если целевой раздел пуст. Чтобы решить эту проблему, я использую третью таблицу с именем RevenueOut, которая пуста.

Существующие данные из раздела Revenue переключаются в RevenueOut (оставляя раздел Revenue пустым), а затем раздел RevenueIn (с новыми данными и своим индексом) переключается в Revenue. Два выражения SWITCH выполняются менее чем за 5 мс в моей среде. Старые данные в RevenueOut потом отбрасываются (рис. 3). Это вариация раздела со скользящим окном (sliding window partition problem), описанного по ссылке bit.ly/1wgPVkR.

Обновление данных раздела
Рис. 3. Обновление данных раздела

Replacing one partition's data in the revenue table Замена данных одного раздела в таблице Revenue
RevenueIn table Таблица RevenueIn
Revenue table Таблица Revenue
RevenueOut table Таблица RevenueOut
(Empty) (Пустая)
Step 1 New data (from Hadoop) Этап 1: новые данные (от Hadoop)
Step 3 Move new data in Этап 3: перемещение новых данных в
Step 2 Move old data out Этап 2: перемещение старых данных из
Step 4 Truncate old data Этап 4: отсечение старых данных

Детали

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

Разработчики тоже получают выигрыш от одной таблицы базы данных. В предыдущем решении использовалась одна таблица для «текущих» данных и другая — для исторических данных. Код на C# должен был решать, какие таблицы использовать, и комбинировать результаты запросов базы данных, если какой-то запрос использовал обе таблицы. Одна таблица и минимальное использование JOIN (благодаря включению денормализованных столбцов без издержек индексации columnstore) упрощает запросы к базе данных для получения отчетов.

Другая деталь — слияние (merging). Я храню устаревшие данные в «ежемесячных» разделах. Это упрощает сопровождение, так как я могу отбросить данные за тот месяц, который выходит за пределы требуемого периода. Меньшее количество раздело также упрощает внутреннюю логику SQL Server, выбирающую разделы для запросов, поскольку большинство запросов по старым данным включают целые месяцы. Кроме того, индекс columnstore работает с более крупными разделами несколько эффективнее за счет своих алгоритмов сжатия. Я использую метод, аналогичный вставке данных для сведения малых разделов в более крупные без перестройки данных.

В моем процессе участвует и некоторая внутренняя инфраструктура SQL Server. В сопутствующей онлайновой статье по ссылке msdn.microsoft.com/magazine/dn800596 даны пошаговые инструкции и пример кода на T-SQL для всех необходимых объектов базы данных SQL Server. Просмотрите ту статью и скопируйте из нее исходный код.


Дон Маккензи (Don Mackenzie) — директор по архитектуре ПО в Cox Digital Solutions, интернет-подразделении Cox Media Group и Cox Enterprises. Любит применять новые технологии в бизнес-приложениях. С ним можно связаться по адресу don@coxds.com.

Выражаю благодарность за рецензирование статьи эксперту Microsoft Римусу Русану (Remus Rusanu).