Поколоночное и гибридное хранение записей в СУБД Teradata

    До недавнего времени все СУБД, работающие со структурированными данными (и не только их), можно было разделить на 2 категории: хранящие записи в построчном формате и хранящие записи в поколоночном формате. Это фундаментальное отличие, влияющее на то, как строки таблиц выглядят на уровне внутренних механизмов хранения СУБД. Долгое время СУБД Teradata относилась к первой группе, но с выходом 14-й версии представилась возможность определять, как хранить данные конкретной таблицы – в виде колонок или строк. Таким образом, появилось гибридное хранение. В этой статье мы хотим рассказать о том, зачем это нужно, как это реализовано и какие преимущества дает.

    Что такое Teradata Columnar?


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

    Как мы пишем на диск данные этой таблицы в случае ее строчного формата? Сначала пишем первую строку, затем вторую, третью и так далее:


    Как минимизировать нагрузку на дисковую систему при чтении этой таблицы? Можно использовать разные методы доступа к ней:
    1. Доступ по индексу – если нужно прочитать лишь несколько строк.
    2. Доступ к отдельным ее партициям (секциям) – если таблица очень большая (например, приведены транзакции за несколько лет, но нужно прочитать данные только за последние несколько недель). Это партиции по строкам.
    3. Полное чтение таблицы – если нужно прочитать большой процент от числа ее строк.

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

    А как насчет колонок? Если SQL-запрос использует не все колонки таблицы, а только некоторые из них? При чтении строк мы читаем с диска каждую строку полностью. Если в таблице 100 колонок, а конкретному SQL-запросу нужны лишь 5 из них, то мы вынуждены прочитать с диска 95 колонок, которые SQL-запрос не использует.

    Вот здесь-то на ум и приходит идея хранить данные не по строкам, а по колонкам. В случае формата хранения по колонкам – columnar – подход такой: сначала запишем на диск первую колонку, затем вторую, третью и так далее:


    Такое разбиение таблицы на колонки создает партиции по колонкам. Если запросу нужны только отдельные колонки, то мы читаем с диска только нужные партиции по колонкам, существенно сокращая количество операций ввода-вывода при чтении данных, которые нужны SQL-запросу.

    Интересная особенность такого подхода состоит в том, что партиции по строкам и партиции по колонкам можно использовать одновременно в одной и той же таблице, иными словами – партиции внутри партиций. Сначала заходим в нужные партиции по колонкам, затем внутри них читаем только нужные партиции по строкам.

    Суммируя вышесказанное, Teradata Columnar – это метод хранения данных в СУБД Teradata, который позволяет таблицам одновременно использовать два метода партиционирования:
    • Горизонтальные партиции – по строкам
    • Вертикальные партиции – по колонкам

    Преимущества Teradata Columnar


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

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

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

    Чтение данных из таблицы, хранящейся по колонкам


    Хранение данных по колонкам существенно меняет механику чтения данных из такой таблицы. Фактически нам нужно “собирать” данные из отдельных партиций колонок, чтобы получить те строки, которые должен вернуть SQL-запрос.

    Выглядит это следующим образом. Если в запросе есть условие WHERE для какой-то колонки, то сначала сканируем эту колонку. Отфильтровываем данные, и для тех строк, которые удовлетворяют условию WHERE, переходим на соседние партиции по колонкам и считываем значения остальных колонок для этих строк.

    Пример: Какие клиенты живут в Сочи?
    В формате строк (для сравнения) считываются значения всех колонок при фильтрации по колонке City (условие WHERE City=’Сочи’).


    В формате колонок считываются только данные колонок City и Cust. Number – сначала фильтр по колонке City, затем поиск соответствующих значений Cust. Number.


    Если колонок в условии WHERE несколько, то сначала выбирается наиболее селективная из них, фильтр по которой приведет к наибольшему отсечению строк, затем следующая колонка WHERE и т.д. И далее все остальные колонки, участвующие в запросе.

    Что касается механики перехода между партициями колонок для “сборки” одной строки из отдельных колонок, то такой переход осуществляется “позиционно”. При чтении конкретной строки мы знаем, что в каждой колоночной партиции значение нужной нам строки находится на N-ой позиции относительно начала таблицы. Для поиска строки используется специальная структура адреса строки rowid, – в которой содержится номер партиции и номер строки. Это позволяет переходить между партициями колонок, заменяя номер партиции внутри rowid для того же номера строки. Таким образом, мы собираем значения колонок для отдельной строки воедино.

    Гибридное хранение данных в одной таблице


    А если колонок в таблице очень много? Тогда понадобится больше накладных расходов, чтобы собирать строки из отдельных колонок. Этого эффекта можно избежать, создавая отдельные партиции не для каждой колонки, а для групп колонок. Если мы знаем, что какие-то колонки часто используются вместе и редко по отдельности, то помещаем эти колонки в одну партицию. Тогда внутри такой партиции данные этих колонок могут храниться по строкам, как если бы это была подтаблица. Такой метод хранения называется гибридным: часть данных таблицы хранится по колонкам, а часть данных этой же таблицы – по строкам.
    Пример (другая таблица, чем раньше):


    Единственно давайте оговоримся, что термин «гибридное хранение данных» может использоваться не только для указанного выше способа хранения данных, но и для хранения блоков данных на дисках разной скорости, о чем у нас на Хабрахабре была отдельная статья.

    Экономия дискового пространства


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

    Партиции по колонкам означают, что значения отдельной колонки находятся рядом друг с другом. Это очень удобно для компрессии данных. Например, если в колонке немного различных значений, то можно составить «словарь» часто используемых значений колонки и с его помощью сжимать данные. Причем для одной колонки таких словарей может быть несколько – отдельно для различных «контейнеров», на которые разбивается колонка при ее хранении на диске (по аналогии с блоками данных при хранении по строкам).

    Что удобно – Teradata подбирает словари автоматически на основе тех данных, которые загружаются в таблицу, и если данные со временем меняются, то меняются и словари для сжатия данных.

    Кроме словарей есть и другие методы сжатия данных, такие как кодирование run length encoding, отсечение Trim, компрессия значений Null, хранение дельты, UNICODE в UTF8. В детали каждого из них вдаваться не будем – скажем лишь, что они могут применяться как по отдельности, так и в сочетании друг с другом для одних и тех же данных. Teradata может динамически сменить механизм компрессии для колонки, если это принесет лучший результат.

    Цель компрессии – уменьшение объема данных (в гигабайтах), который таблица занимает на диске. Это позволяет хранить больше данных (в терминах количества строк таблицы) на том же оборудовании, а также больше данных на быстрых SSD-дисках, если они есть.

    Когда использовать Columnar?


    Teradata Columnar – это отличная функциональность, которая позволяет увеличивать производительность запросов и сжимать данные. Однако не следует рассматривать ее как идеальное решение. Выигрыш будет получен лишь для соответствующих данных и SQL-запросов с определенными характеристиками. В других случаях эффект может быть даже негативным – например, когда все запросы используют все колонки таблицы и данные не очень хорошо сжимаются.
    Удачными кандидатами для колоночного хранения являются таблицы, в которых много колонок, однако каждый SQL-запрос использует относительно небольшое их количество. При этом различные запросы могут использовать различные колонки, главное, чтобы каждый отдельный запрос использовал лишь небольшое количество колонок. В этих случаях происходит существенное сокращение количества операций ввода-вывода и улучшение производительности по I/O.

    Разнесение данных строки на отдельные колонки при вставке данных (insert) и последующий «сбор» значений колонок обратно в строки при выборке данных (select) – эти операции потребляют больше CPU для колоночных таблиц, чем для обычных. Поэтому следует учитывать, что если в системе есть значительная нехватка ресурсов CPU (так называемые CPU-bound-системы), то для них Teradata Columnar также следует применять с аккуратностью, поскольку это может уменьшить общую производительность системы из-за нехватки ресурсов CPU.

    Одно из требований для колоночных таблиц состоит в том, чтобы данные загружались в таблицу большими порциями INSERT-SELECT. Причина – в следующем: если вставлять данные «построчно», то для колоночных таблиц это очень неудобно, т.к. надо вместо одной записи строки (одна операция ввода-вывода), как это было бы для обычной таблицы, здесь нужно записать значения колонок в разные колонки отдельно – резко увеличивая количество операций ввода-вывода. Впрочем, это характерно для вставки именно одной строки. Если же вставляется сразу много строк, то запись большого набора строк сравнима по трудоемкости с тем, чтобы разбить тот же объем данных на колонки и записать эти колонки в нужные партиции по колонкам.

    По этой же причине в колоночных таблицах операции UPDATE и DELETE являются трудоемкими, ведь нужно зайти в различные партиции колонок, чтобы выполнить эти операции. Впрочем, трудоемкими – это не значит невозможными. Если объем таких изменений относительно небольшой, то Teradata Columnar вполне подойдет для таких задач.

    Еще одной особенностью колоночных таблиц является отсутствие первичного индекса – колонки, которая обеспечивает распределение строк по AMPам в системе Teradata. Доступ по первичному индексу – наиболее быстрый способ получения строк в Teradata. Для колоночных таблиц такого индекса нет, вместо него используется механизм No-Primary Index (No-PI) – когда данные равномерно распределяются самой Teradata, но без возможности обращения к этим данным по первичному индексу. Это означает, что колоночные таблицы не следует использовать для таблиц, специфика использования которых подразумевает наличие первичного индекса.

    Эти правила не являются однозначными. Каждую ситуацию следует анализировать отдельно. Например, можно создавать дополнительные индексы для смягчения эффекта отсутствия первичного индекса. Или другой пример: можно создать таблицу в формате по строкам, а поверх нее – join-индекс в формате по колонкам (join-индекс – это материализованное представление в Teradata, его тоже можно хранить в формате колонок). Тогда запросы, в которых используется много колонок, будут обращаться к самой таблице, а запросы, в которых мало колонок, будут использовать материализованное представление.

    Что удобно – вовсе не обязательно объявлять все таблицы колоночными. Можно лишь часть таблиц создать в формате колонок, а остальные таблицы – в формате строк.
    Суммируя вышесказанное – Teradata Columnar применяется для таблиц, которые обладают следующими свойствами:
    • SQL-запросы выполняются к отдельным наборам колонок таблицы
      ИЛИ
      SQL-Запросы выполняются к отдельному поднабору строк таблицы
      > Лучший результат – когда и то и другое
    • Данные могут загружаться большими INSERT-SELECT’ами
    • Нет или немного операций update/delete


    Если присмотреться внимательнее, многие очень большие таблицы, которые растут с течением времени, как раз обладают этими свойствами. Хотите, чтобы они занимали меньше места на дисках и запросы к ним выполнялись быстрее? Тогда функциональность Teradata Columnar стоит того, чтобы обратить на нее внимание.

    Чтобы помочь тем, кто выполняет физическое моделирование, определить, какие таблицы следует или не следует создавать в формате колонок, имеется специальный инструмент – Columnar Analysis Tool. Этот инструмент анализирует использование той или иной таблицы SQL-запросами и выдает рекомендации по применимости формата хранения по колонкам. Также много полезной информации есть, разумеется, и в документации по Teradata 14.

    Teradata

    35,00

    Компания

    Поделиться публикацией
    Комментарии 21
      0
      Было бы интересно взглянуть на реальные тесты производительности.
      При крайних и средних значениях — выбираем все столбцы, выбираем 1 столбец.
      На чтение, запись, удаление, обновление.
      И сравнить это с MS SQL 2008 R2, например.
      То что это быстрее теоретически — это да, но от реализации тоже многое зависит.
        0
        Реальные тесты производительности как правило делаются для конкретных Заказчиков, поскольку результаты очень сильно зависят от конкретной структуры таблиц и SQL-запросов к ним.

        Другой вариант — использовать виртуальные машины Teradata Express под VMWare, которые можно скачать в свободном доступе. Там вполне можно протестировать, насколько сильно сожмутся данные, если одну и ту же таблицу создать в двух вариантах — обычном и колоночном. А также можно посмотреть трудоемкость выполнения запросов к этим таблицам — правда не с точки зрения времени выполнения (на виртуальной машине оно будет заведомо не показательным), а с точки зрения потребления CPU и I/O каждым отдельным запросом (эта информация пишется в специальный журнал DBQL).
          0
          Структуру таблиц можно принять одинаковой для обеих испытуемых и все остальное тоже — SQL запросы, выделенной памяти etc.
          На одном сервере сначала производим тестирование MSSQL, а потом Teradata и смотрим есть выигрыш или нет и в каких тестах.
          Если Teradata в каких-то тестах обойдет MSSQL, это уже будет повод задуматься о её внедрении. Сам я конечно тоже могу провести эти тесты, но времени не хватает на все. Может быть Teradata так крута, что стоит прямо сейчас начать мигрировать не неё, но мы этого не узнаем :)
            0
            При выборе платформы для автоматизации чего-нибудь на крупном предприятии (а на мелких Teradata не нужна) критерий наличия экспертизы, т.е. программистов, администраторов и т. д. гораздо важнее скорости в каких бы то ни было тестах. А вот по этому показателю Teradata сливает буквально всем. Так что если у вас кругом MS SQL, и вам понадобилось хранилище, которое в обычный MS SQL не влезает, смело берите Parallel DWH. И, разумеется, не dell'овскую стойку, а только HP.
              0
              А почему не деловскую?
                –1
                Статистика — упрямая вещь. Та же терадата собрана на делловских серверах, и, по отзывам, сыпется с грохотом.
                Я не очень люблю HP, но x86 сервера у них, надо отдать должное, хорошие :)
                  0
                  А можно поточнее где Терадата сыпется и что?
                    –1
                    От человека, занимающегося администрированием серверов, слышал, что среднее время жизни терадатовского сервера во вверенных ему ЦОДах — около 2 лет.

                    То, что при закупке терадаты резервных серверов (HSN) закупается столько же, сколько рабочих (т. е. закупается, скажем, 10 серверов, из которых 5 работает, а 5 тупо ждут, когда один из этих пяти сгорит) косвенно подтверждает слова товарища.
                      0
                      На самом деле конфигурации бывают разные. Есть конфигурации, когда количество резервных серверов равно количеству рабочих, это правда. Но делается это не по тому, что сервера плохие, а по тому, что сервера иногда умирают. Это факт. Как в старой шутке, что есть 3 вида людей по отношению к бэкапам: люди которые не делают бэкапы, которые делают, и те, которые уже даже восстанавливались.
                      RAID-1, например, придумали тоже не из-за того что диски плохие, а из-за того, что какими бы они ни были — они умирают. И если наш клиент понимает, что для него система критична настолько, что недопустимы ситуации деградирования производительности даже в случае невероятных аппаратных сбоев (а надо понимать, что если поставить 1 резервный узел на 2 рабочих, то всё плохо будет если выпадет оба резервируемых рабочих узла (как дисковая пара в RAID-1)), то он предпочитает конфигурацию где число HSN (Hot Stand-by Node) равно числу рабочих. А можно поставить и 1 резервный на 2 рабочих. Можно и без резервных.
                      Касательно 2-х лет жизни сервера, то пока нет возможности подтвердить описаную вами информацию. Скиньте название клиента в личку, я спрошу наш «железный» саппорт о реальной статистике проблем с железом в этом клиенте. Заодно узнаете насколько можно доверять своему знакомому :)
                        0
                        RAID-1 — это ещё и скорость. Ни разу не слышал про то, что RAID-1 сильно надёжнее RAID-5. Вот что «быстрее» — сплошь и рядом.

                        Про клиента — спрошу.
            0
            Квестую сравнение с Amazon RedShift. Какова цена хранения 1 ТБ на год, с учётом нужного железа и производительности?
              0
              Сравнение не валидно. RedShift это облачное решение, а Терадата в 99% случаев это on-premises для корпоративного сегмента. Т.е. под определенные задачи собирается конфигурация, настраивается софт, ставится к заказчику и проводятся любые другие необходимые работы.

              P.S. No offense, но меня лично всегда удивлял способ сравнения чего либо ценой за 1ТБ. Это все равно что выбирать машину по принципу цены за лошадиную силу, или за литр багажника :)
                0
                On-premis с облачными решениями часто конкурируют и их можно и нужно сравнивать по цене.
                  0
                  В моей картинке мира выходит, что облачное решение распределяет все расходы на инфраструктурные компоненты (питание, координационные механизмы, системы мониторинга и пр.) между потребителями решения. Таким образом, каждый потребитель платит бесконечно маленькую толику за это всё (при количестве потребителей стремящемся к +бесконечности). Таким образом стоимость 1 ТБ равна примерно чистой стоимости 1 ТБ (если убрать маржу). А в on-premis решениях все расходы падают на одного покупателя. Таким образом на стоимость 1 ТБ (а количество ТБ далеко не +бесконечность) падает ненулевая дополнительная стоимость инфраструктурных компонентов. Таким образом грамотно спроектированное облачное решение за единицу объема в пределе будет дешевле для конечного потребителя (я не говорю что лучше, говорю про дешевле). Нет?
                    0
                    Нет, там много факторов надо учитывать. Если это лично ваше облачное решение, то да.
                    Но поскольку вам его продают — все давно просчитано, так чтобы не было слишком и дешево, даже если обходится в копейки — все это делается для извлечения максимальной прибыли.
                    При сравнении цен нужно учитывать много факторов — стоимость ПО, поддержку, хостинг, аммортизацию и прочее.
                    В итоге будет неоднозначная картина — в зависимоти от длительности использования облачных решений. Например первые 20 месяцев это дешевле, но на 21 месяц будет дешевле on-premis решение, потому как оно уже окупиться за это время, а в облаке будете продолжать платить.

                    В облаке часто взымают плату за трафик, транзакции — чего нет в вашем премисе.
                    Рассчитайте реально несколько решений в перспективе и увидите разницу.
                      0
                      Мы изначально говорили про цену за 1 ТБ для потребителя, а вы уже говорите про Total Cost of Ownership, что немного другое ;) но идея понятна, что ценообразование для облака многофакторное.

                      Но это не отменяет тезиса, что сравнивать машины по стоимости за 1 лошадиную силу как-то странно. Надо же чтобы они хотя бы одного класса были. Там, круиз контроль, подушки безопасности и все такое
                  0
                  Вы конкурируете за тех же клиентов что и RedShift. В чём ваше преимущество. Знаю клиентов которые отказались от решений Vertica и перешли на RedShift потому что намного выгоднее. Если у вас нет серёзных преимуществ, то решения похожие на ваши вымрут как динозавры.
                  Очевидно что для вашего решения нужно покупать очень недешовые устройства хранения, нанимать людей которые всё это железо настроят и будут поддерживать. Открыв первый попавшийся на глаза case study «Case Study ROI for a Customer Relationship Management Initiative at GST » от вашей компании вижу железо 1'500'000$ софт 2'500'000$, консалтинг 1'000'000$ + ежегодно за консалтинг 200'000$, за железо 180'000$, софт 450'000$. Если ваше решение при этом обрабатывает менее 1ПТ данных, то Amazon RedShift чистый победитель(примерно 1'000'000$ за 1 ПБ в год при аренде на 3 года), и за разницу можно нанять хорошую команду которая будет писать аналитику и ещё на несколько Бентли для CEO останется, а учитывая что цены на Амазоновские сервиса посточнно снижаются, то нужны очень серёзные причины чтобы выбрать ваше решение.
                  З другой стороны, очевидно что решение Amazon RedShift не сможет покрыть все нужды и ParAccel продаёт более серьёзное решение за другие деньги, но я пока не вижу что вы можете лучше предложить.
                    0
                    На самом деле преимуществ множество, и мы пытаемся потихоньку рассказывать о имеющихся возможностях в постах, публикуемых тут. Вы правильно сказали, что не всякое решение покроет все нужды, поэтому и существует выбор платформ. Может быть ваши вопросы как раз из-за того, что мы не так много успели рассказать :)
                    Но первое что пришло в голову сейчас просто для примера — в Терадате, как мы описали в посте, возможно гибридное хранение данных, где по колонкам, а где по строкам. Так что на вскидку для запросов, которые выбирают много данных из «широких» таблиц гибридные функции дадут лучшее время отклика, чем чисто на колоночном хранении. Так что если специфика ваших процессов такова, что требуются как точечные запросы, которые должны быстро вернуть малое количество колонок, так и большие аналитические запросы по большому количеству столбцов (кто собирал, например, банковскую аналитику, тот поймет), то гибридное хранение может дать очень серьезные преимущества. А если к этому добавить десятки тысяч конкурентных сессий…
                      0
                      Это «на вскидку». Опубликуйте результаты реальных тестов. Много зависит от реализации — в теории все красиво.
                0
                У вас при покупки машины нет ТЗ.
                В коммерческом проекте нам не нужен круиз контроль — мы делаем только то что надо.
                Онпремис и облако предлагает data warehouse. Определяем что нам нужно и если оба подходят можем сравнивать по деньгам.
                  0
                  точно, и я об этом же. сначала надо понять что всё подходит

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое