SQL SERVER PARALLEL DATA WAREHOUSE Обзор, лучшие практики, новое в AU3 Резник Андрей anrez@microsoft.com Гвоздев Александр agvoiz@microsoft.com. - презентация

1 SQL SERVER PARALLEL DATA WAREHOUSE Обзор, лучшие практики, новое в AU3 Резник Андрей Гвоздев Александр

2 Содержание Обзор SQL Server PDW Организация данных Методы загрузки данных Новое в AU3

3 Предложения Microsoft для построения Хранилищ Данных HP Business Data Warehouse Appliance

4 Зачем SQL MPP ? Новая возможность для Архитекторов SQL

5 Программно-аппаратные комплексы Быстрый старт проекта Конфигурация Настройка Интегрированное управление Техническая поддержка комплекса Сбалансированная архитектура

6 Цели параллелизма Линейное ускорение добавление оборудования ведёт к увеличению скорости исполнения запроса Линейное масштабирование при увеличении объема аппаратного обеспечения большая задача выполняется за то же время 6Track # – Session #

7 Традиционный подход 7Track # – Session # Системы с общей памятью Системы с общим диском Память CPU Сеть хранения данных (SAN) … Узел 1 MEM CPU Узел 2 MEM CPU Узел Т MEM CPU

8 Преимущества Нет общих разделяемых компонент (как в системах с общей памятью) Нет распределённого менеджера блокировок (как в системах с общим диском) Пропускная способность памяти и дисков растёт линейно с добавлением узлов Независимые системы (shared-nothing) … Узел N MEM CPU Узел 2 MEM CPU Узел 1 MEM CPU Сеть

9 Массивно Параллельные Вычисления=Скорость Разделение данных на малые части Получение данных из каждой части Сборка результата MPP дает наиболее быстрый результат по большим объемам данных 9Track # – Session #

10 PDW Аппаратно-программный комплекс Независимые системы (Shared Nothing "ничего не разделяется") Массивно-параллельные вычисления (MPP) PDW поставляется в инсталлированном и полностью сконфигурированном состоянии. Plug & Play Стандартные: Сервера Системы хранения Сетевое оборудование

11 Control Rack Data Rack Архитектура PDW Запрос 1 ? ? ? ? ? ? ? ? ? ? Результат

12 Landing Zone ETL Tools Архитектура Распределенного Хранилища Departmental Reporting Regional Reporting High-Performance Reporting Central EDW Hub Regional Reporting with Business Decision Appliance Third-Party RDBMS Third-Party Data Integration Mobile Applications

13 v v PDW: Организация данных

14 Репликация Таблица копируется между всеми узлами Parallel Data Warehouse. Подходы к организации данных Распределение Таблица равномерно распределена между узлами Ультра Shared-Nothing Возможность создать дизайн таким образом, чтобы минимизировать перемещение данных между узлами

15 Реплицируемые таблицы Time Dim Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Store Dim Store Dim ID Store Name Store Mgr Store Size Store Dim ID Store Name Store Mgr Store Size Product Dim Prod Dim ID Prod Category Prod Sub Cat Prod Desc Prod Dim ID Prod Category Prod Sub Cat Prod Desc Sales Facts Date Dim ID Store Dim ID Prod Dim ID Mktg Camp Id Qty Sold Dollars Sold Mktg Campaign Dim Mktg Campaign Dim Mktg Camp ID Camp Name Camp Mgr Camp Start Camp End TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD Небольшие таблицы измерений реплицируются в каждый Вычислительный Узел TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD

16 Распределенные таблицы Time Dim Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Date Dim ID Calendar Year Calendar Qtr Calendar Mo Calendar Day Store Dim Store Dim ID Store Name Store Mgr Store Size Store Dim ID Store Name Store Mgr Store Size Product Dim Prod Dim ID Prod Category Prod Sub Cat Prod Desc Prod Dim ID Prod Category Prod Sub Cat Prod Desc Sales Facts Date Dim ID Store Dim ID Prod Dim ID Mktg Camp Id Qty Sold Dollars Sold Mktg Campaign Dim Mktg Campaign Dim Mktg Camp ID Camp Name Camp Mgr Camp Start Camp End TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD TDTD TDTD PDPD PDPD SDSD SDSD MDMD MDMD Большие таблицы фактов распределяются по хэш-ключу между всеми Вычислительными Узлами SF -1 SF -2 SF -3 SF -4

17 CREATE TABLE SalesFact ( DateKey INT NOT NULL, CustomerKey INT, DollarAmount MONEY) WITH (DISTRIBUTION = HASH(CustomerKey)) Распределенные таблицы (пример) PDW Узел 1 Create Table _a Create Table _b … Create Table _h 8 Таблиц PDW Узел 10 Create Table _a Create Table _b … Create Table _h PDW Узел …

18 Архитектура вычислительного узла CP U RAM VendorModel Form Factor Total CoresMemory HPDL360 G61U 8 Cores Hyper threaded 72 GB DELLR6101U 8 Cores Hyper threaded 96 GB SQL Server 2008Local Direct Attached Storage Dual Multi-Core Processors SAN Storage

19 SAN Архитектура вычислительного узла

20 Типы запросов Совместимое распределение Запрос не требует перераспределение данных Простой select по одной таблице Ключ распределения используется в операциях соединения или группировки распределенных таблиц Таблица репликации внутренне соединяется с таблицей распределения Несовместимое распределение Запрос требует перераспределение данных между узлами для возврата корректного результата. Простой запрос по распределенной таблице с Group By, который не включает в себя ключ распределения Ключ распределения не используется в соединения и группировках распределенных таблиц. Таблица репликации внешне соединяется с таблицей распределения

21 Типы соединений Shared Nothing Join Достигается совместимым распределением из- за использования совместимых ключей распределения в условии соединения Ultra Shared Nothing Join Достигается совместимым распределением из- за наличия реплицируемой таблицы Redistribution Join Требует динамического перераспределения данных между вычислительными узлами для достижения совместимого распределения

22 Node 1 Простой запрос типа Ultra Shared Nothing 22 Тип соединения: USN (Ultra Shared Nothing) Совместимое распределение Используется репликационная таблица. Ключ распределения Store Sales Distribution Key не используется. Item_keyColor 1Red 2Green 3Blue 4Yellow Item Dim ss_key Item_key Qty Store Sales SELECT ss_key, Qty FROM item_dim a JOIN store_sales b ON a.item_key = b.item_key WHERE a.color = Yellow Result Set 5,12 Result Set 5,12 Результат 5,12 : 6,17 Result Set 6,17 Result Set 6,17 Node 2 Item Dim ss_key item_key Qty Store Sales \ Item_keyColor 1Red 2Green 3Blue 4Yellow

23 Node 1 Простой запрос типа Shared Nothing Join 23 ss_keyQty Store Sales SELECT a.color, b.Qty FROM web_sales a JOIN store_sales b ON ws_key = ss_key WHERE a. color = Red Result Set Red,5 Result Set Red,5 Node 2 Результат Red,5 : Red,3 Result Set Red,3 Result Set Red,3 ss_keyQty Store Sales Distributed Table ws_keyColorQty 1Red15 3Blue20 5Yellow22 7Green17 Web Sales ws_keyColorQty 2Red13 4Blue21 6Yellow27 8Green11 Web Sales Distributed Table Тип соединения: shared nothing Совместимое распределение Соединение включает в себя совместимые ключи распределения

24 Соединение с перераспределением: Shuffle 24 Тип соединения: Перераспределение Таблицы физически не размещены согласно своим ключам распределения Несовместимое распределение Распределение используется только из левой таблицы (vendor_sales) Операция перераспределения Shuffle Данные правой таблицы Store_Sales) перестраиваются с ключем распределения VID Запрос становиться совместимым Node 1 ss_keyVIDQty Store Sales SELECT vs_key, a.ord, b.qty FROM vendor_sales a JOIN store_sales b ON a.vs_key = b.VID WHERE a. color = Red Result Set 11,15, 5 Result Set 11,15, 5 Результат 11,15,5 : 21,13,3 Result Set 21,13, 3 Result Set 21,13, 3 Node 2 ss_keyVIDQty Store Sales vs_keyColorOrd 11Red15 32Blue20 54Yellow22 78Green17 Vendor Sales vs_keyColorOrd 21Red13 42Blue21 63Yellow27 84Green11 Vendor Sales Distributed Table ss_keyVIDQty ss_keyVIDQty

25 Соединение с мульти-перераспределением 25 Тип соединения: Перераспределение Таблицы физически не размещены согласно своим ключам распределения Несовместимое распределение Соединения не идет по ключам распределения Двойная операция Shuffle Левая перестраивается (Vendor_Sales) с ключем распределения DK = Color Правая страница (Store Sales) перестраивается с ключем распределения DK = Color Запрос становится полностью совместимым Node 1 ss_keyColorQty 1Red5 3Blue10 5Yellow12 7Green7 Store Sales SELECT a.color, a.ord,b.qty FROM vendor_sales a JOIN store_sales b ON a.color = b.color WHERE a.color = Red Result Set 15, 5 13,3 Result Set 15, 5 13,3 Final Result Set 15,5 : 13,3 Result Set NULL Result Set NULL Node 2 ss_keyColorQty 2Red3 4Blue11 6Yellow17 8Green1 Store Sales Distributed Table vs_keyColorOrd 11Red15 32Blue20 54Yellow22 78Green17 Vendor Sales vs_keyColorOrd 21Red13 42Blue21 63Yellow27 84Green11 Vendor Sales Distributed Table ss_keyColorQty 1Red Blue10 4Blue11 ss_keyColorQty 5Yellow12 6Yellow17 7Green7 8 1 vs_keyColorOrd 11Red15 21Red13 32Blue20 42Blue21 vs_keyColorOrd 54Yellow22 63Yellow27 73Green17 84Green11 Distributed Table

26 26Track # – Session # PDW-иной зверь! Архитектура, заточенная на сканирование данных, а не на индексирование Запрос автоматически преобразуется в MPP план Добавляются необходимые операции перемещения данных Bottleneck – перемещение данных между узлами Необходимость для некоторых соединений Необходимость для некоторых агрегаций Производительность PDW великолепна, когда: Соединения могут быть выполнены локально Агрегации могут быть выполнены локально

27 v v PDW: Загрузка данных

28 Control Rack PDW процесс загрузки Control Node Active/Passive Control Node Active/Passive Landing Zone Database Server Nodes Storage Nodes Infiniband Файл DMS Ser er PDWEngine Load Manager Export Manager DMS Manager DMS Manager DMS SQL Server SQL Server Load Client DMS Converter Sender Receiver Writer DMS Converter Sender Receiver Writer Запуск DWLoader DMS читает файл и буферизирует записи для посылки Вычислительным узлам по round-robin Load Manager создает промежуточные таблицы такой же геометрии, что и целевая таблица Каждая строка конвертируется для bulk insert и хэшируется Хэшированная строка отсылается узлу, которому она принадлежит Полученная строка поступает в очередь получателя Строка вставляется в промежуточную таблицу методом bulk insert

29 Методы загрузки данных Integration ServicesDWLoader Используется для Extract, Transform and Load (ETL) Используется для Extract, Load and Transform (ELT) Выгрузка и загрузка данных напрямую из сторонних систем Загрузка данных из плоских файлов в PDW Трансформации до загрузки в PDWСамый быстрый механизм загрузки данных из файлов Трансформации проводятся после загрузки dwloader.exe -i D:\TPCH\lineItem.tbl -M Fastappend -E -m -d tpch_100gb -E -c -b rt value -rv 100 -R LineItem.tbl.rejects -e ascii -t "|" -r \r\n -U sa -P {password} -T tpch_100gb.dbo.lineitem_Load dwloader.exe -i D:\TPCH\lineItem.tbl -M Fastappend -E -m -d tpch_100gb -E -c -b rt value -rv 100 -R LineItem.tbl.rejects -e ascii -t "|" -r \r\n -U sa -P {password} -T tpch_100gb.dbo.lineitem_Load

30 Результат ELT SMP vs. PDW

31 Выводы Загрузка данных в PDW o DWLoader работает быстрее для плоских файлов Не нужна трансформация Нужно планировать ресурсы на создание файлов o PDW Destination Adapter для SSIS Если необходимы трансформации Параллельное выполнение для ускорения скорости загрузки o Использовать больше ELT вместо ETL для переноса нагрузки на MPP движок Использовать мощь операций CTAS o PDW дает возможность одновременной загрузки данных и запросов

32 Место PDW в BI стеке PDW Connector for Informatica PDW Connector for Hadoop

33 33Track # – Session # Что нового в PDW Appliance Update 3?

34 PDW начального уровня ½ Rack Функциональность: –~40% мощность (4+1 Compute Nodes) –До 50TB –Полная функциональность PDW Преимущества: –~40% от цены 1 rack –Наименьшая цена/TB на рынке

35 Преимущества PDW Appliance модель Система поставляется с преднастроенной аппаратной частью и установленным SQL Меньше настроек, меньше сложностей для администраторов Не нужно задумываться о физическом расположении файлов базы данных и таблиц Настройки памяти, параллелизм, много других опций уже настроены оптимально Решение разработано и оптимизированно специально для нагрузок типичных для хранилищ данных Проще «стартовать» проект и начать с ним работать: Вся система поставляется как один заказной номер Установив, можно сразу же создавать базы

36 v v Вопросы?