Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите организовать переливку данных используя SSIS  [new]
нязнайка
Guest
Есть необходимость периодически агрегировать и переливать данные в хранилище из оперативной системы.
Планируется использовать SSIS.
Алгоритм такой :
1) Выборка новых данных из источника
2) Группировка внутри Data Flow.

Ожидается максимальное число групп не более 4 млн.

Далее нужно данные залить в хранилище.
Если группа уже создана, то обновляем общие столбцы, иначе добавляем новую группу.

Как я себе вижу этот процесс :

Каждый раз создаем (очищаем) предварительную (staging) таблицу.
Затем порциями (100k -500k) используя MERGE или UPDATE + INSERT WHERE NOT EXISTS добавляем данные из staging.

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

Оптимальный ли это вариант ? Что еще можно попробовать для подобной агрегации ?

Спасибо.
2 окт 12, 17:56    [13257703]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
нязнайка
Guest
up
Поделитесь пожалуйста опытом, это же типичная агрегация при заливке в хранилище.
3 окт 12, 11:00    [13260100]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
нязнайка
up
Поделитесь пожалуйста опытом, это же типичная агрегация при заливке в хранилище.

что есть это загадочное "агрегация" ????
3 окт 12, 12:23    [13260863]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
А вообще мезанизм такой
1. Мы должны знать какие данные изменились в источнике с момента последней загрузки.(можно триггерами апдейтить даты изменений или CDC прикрутить)
2. Забираем изменившиеся данные в staging (да, его можно чистить перед каждой загрузкой)
3. Заливаем данные в хранилище. Использовать ли мерж или Update+insret - дело индивидуальное. Зависит от того чего больше приходит апдейтов или инсертов.
3 окт 12, 12:29    [13260913]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35387
Блог
нязнайка
Экскалацию блокировок на время импорта будем отключать.


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

Поэтому не понятно, ваше желание. Ведь при загрузке данных в DWH как правило отсутствуют "читатели".
3 окт 12, 13:07    [13261231]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Критик
нязнайка
Экскалацию блокировок на время импорта будем отключать.


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

Поэтому не понятно, ваше желание. Ведь при загрузке данных в DWH как правило отсутствуют "читатели".
Это наверное чтобы гарантированно убить сервер, чтобы он загнулся от нехватки памяти под построчные/страничные блокировки.
3 окт 12, 19:58    [13264026]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
нязнайка
Guest
Это наверное чтобы гарантированно убить сервер, чтобы он загнулся от нехватки памяти под построчные/страничные блокировки.


Давайте считать :

Я же писал, что обновлять проциями по 100k-500k.
В случае 500k : одна блокировка на 64x системе = 120 байт т.е 6млн байт ~ 57,2 мб. одна строка у меня 36 байт (постоянная) - т.е на странице 8096 / 36 = 224 записи. 500k /224 * 120 /1024/1024 = 0,2 мб.

Итого 57,4. Что не так уж и много.

что есть это загадочное "агрегация" ????


Группировка за час + группировка по категориям.


Поэтому не понятно, ваше желание. Ведь при загрузке данных в DWH как правило отсутствуют "читатели".

А если читатели все-таки будут ? Версионность включать ? Вставлять/обновлять с TABLOCK ? Добиться эксклюзивного доступа ?

Версионности (особенно RCSI) я не очень доверяю, сюрпризы могут быть при выборках.Между заливками порциями. Может в SINGLE_USER переключаться или application_lock использовать?

Система еще только разрабатывается, поэтому рассматриваются вообще различные варианты заливки.

Пробовал сегодня использовать lookup трансформацию без кеширования - совершенно не понравилась. Понял что это только для справочников.

Нашел вариант секционирования - когда выкидывают секцию (switch out), сливают со staging и включают опять (switch in).
Но это не мой случай - у меня будут в основном только новые записи и немного обновлений.

Заливка в Staging на данный момент с опцииями TABLOCK и ORDER для bulk-logged logging. Сама таблица очищается (truncate), но кластерный индекс не удаляется.

Завтра буду пробовать заливку в кучу и затем создание кластрного индекса на staging.
3 окт 12, 22:26    [13264457]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5121
я так и не понял с чем у вас проблемы...
по оптимизации заливки см. тут и тут
4 окт 12, 09:27    [13265458]     Ответить | Цитировать Сообщить модератору
 Re: Помогите организовать переливку данных используя SSIS  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
нязнайка
Я же писал, что обновлять проциями по 100k-500k.

Незачем. На хорошем сервере мы у себя порциями по 3-20 млн (в зависимости от ширины таблицы) вставляем.

Группировка за час + группировка по категориям.

Странно вообще-то. Обычно в хранилище загружают все исходные данные. Группировка происходит уже
уровнем выше с помощью OLAP кубов и прочих BI инструментов.
Вообще вам хранилище с какой целью нужно?? Чисто регламентные отчеты строить? Или может аналитика on demand какая?

А если читатели все-таки будут ? Версионность включать ? Вставлять/обновлять с TABLOCK ? Добиться эксклюзивного доступа ?

Читателей в staging области нет вообще, кроме одного - ETL загрузчик в область витрин данных. Его само собой мы же сами и конфигурим когда ему стартовать. Так что в staging грузиться все просто, пока заливка не кончилась, чтение не стартует. В области витрин даных в принципе тоже самое, есть есть MOLAP - то он и будет единственный читатель.
Пользователи и отчеты будут обращаться уже к нему.

Пробовал сегодня использовать lookup трансформацию без кеширования - совершенно не понравилась. Понял что это только для справочников.

Lookup - полный ахтунг по перформансу. Забудте про него сразу.

Нашел вариант секционирования - когда выкидывают секцию (switch out), сливают со staging и включают опять (switch in).

Годный вариант - вполне можно юзать.

Но это не мой случай - у меня будут в основном только новые записи и немного обновлений.

Тогда рекомендую не делать мердж, а делать инсерт средствами SSIS, а потом апдейт скриптом.

Заливка в Staging на данный момент с опцииями TABLOCK и ORDER для bulk-logged logging. Сама таблица очищается (truncate), но кластерный индекс не удаляется.

Годно
4 окт 12, 12:04    [13266560]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить