Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
Входной текстовый файл (минута данных) порядка 300-400МБ.

Парсинг этого файла + загрузка в MS SQL занимает ~ 1.5 минуты. Что безусловно плохо и медленно.
При этом сам парсинг занимает около 7 секунд (C#) 5 секунд (С++).
Oстальное подготовка к загрузке данных (конвертация в CSV формат, сохранение его на HDD) & сама загрузка непосредственно.
В данный момент пытаемся оптимизировать процесс.
Парсинг время подвинуть тяжело.
Можно выиграть секунду-две, но это близко к пределу уже.
Подготовка и загрузка - вот тут есть опции.
Положим eсли остановиться на CSV и дампнуть его на диск - вполне так реально уложится в 12-15 секунд.
Далее,как вариант, вообще не вставлять отпаршенные данные в SQL,а держать в промежуточном (CSV в данный момент) формате.
К примеру час данных (~ 20ГБ) - отдельный файл.
Плюс сделать определенную навигацию по этому хранилищу, которая будет уметь:
1) быстро находить нужный файл.
2) быстро читать определенную его часть.
3) загружать дата сабсет в SQL.
Данных много. (~1+Тб в неделю)
Данные неизменяемы.
UPDATEs нету. Исключительно INSERTs.
Запросы только на чтение.

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

Заранее спасибо.
5 июл 11, 19:49    [10927738]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
BCP, SSIS ... загрузка в таблицу-кучу
5 июл 11, 19:53    [10927753]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
Knyazev Alexey
BCP, SSIS ... загрузка в таблицу-кучу

Все ясно кроме таблица-куча.
Это какой-то специальный тип таблицы?
5 июл 11, 20:03    [10927790]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
delpasso
При этом сам парсинг занимает около 7 секунд (C#) 5 секунд (С++).
А делать Bulk load через класс SQLBulkCopy не пробовали?
Нужно только написать DataReader, который будет из вашего файла строки выдавать.

Мы недавно как раз такую задачу решали.
5 июл 11, 20:15    [10927838]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
DeColo®es
delpasso
При этом сам парсинг занимает около 7 секунд (C#) 5 секунд (С++).
А делать Bulk load через класс SQLBulkCopy не пробовали?
Нужно только написать DataReader, который будет из вашего файла строки выдавать.

Мы недавно как раз такую задачу решали.


Хм. Забавно.
А какой производительности добились, если не секрет?
В колличестве вставленных строк нет смысла наверно мерять.
У нас порядка 400 столбцов и данные достаточно дыряво распределены между ними.
Если мерять скажем в МегаБайт/секунду?
5 июл 11, 20:56    [10927974]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
delpasso, у датаадаптеров кстати, в C# есть UpdateBatchSize - тот же BULK по сути. Реализовывалась такая задача некоторое время назад. Главное при вставке в таблицу - адекватный размер пакета и отсутствие индексов. Скорость вполне нормальная, учитывая что вставка происходила с сервера приложений на сервер баз данных по сети. Хотите конкретных цифер? Дык набросайте тестовое приложение с заполнением адаптера и выгрузкой в БД, да и замерьте. Делов то на полчасика.
5 июл 11, 21:15    [10928006]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
delpasso
Данных много. (~1+Тб в неделю)
Не понял, в чём проблема.

Данных всего 1 Тб в неделю, скорость загрузки 5 минут на Гб. Т.е. недельные данные загружаются за 2 часа. Это что, долго???
5 июл 11, 21:40    [10928052]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
alexeyvg
delpasso
Данных много. (~1+Тб в неделю)
Не понял, в чём проблема.

Данных всего 1 Тб в неделю, скорость загрузки 5 минут на Гб. Т.е. недельные данные загружаются за 2 часа. Это что, долго???


alexeyvg, вы обсчитались.
к сожалению все не так быстро.
5 июл 11, 21:53    [10928078]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
delpasso
alexeyvg, вы обсчитались.
к сожалению все не так быстро.
А, точно.

5000 минут на Тб, т.е. 100 часов на неделю.
Действительно много...
delpasso
Положим eсли остановиться на CSV и дампнуть его на диск - вполне так реально уложится в 12-15 секунд.
Наверное так, или, как уже писали, загружать SQLBulkCopy прямо из приложения.
5 июл 11, 22:12    [10928150]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
скажите, а индекс в вашей таблице только один?
по дате?
5 июл 11, 22:46    [10928302]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
delpasso
Хм. Забавно.
А какой производительности добились, если не секрет?
В колличестве вставленных строк нет смысла наверно мерять.
У нас порядка 400 столбцов и данные достаточно дыряво распределены между ними.
Если мерять скажем в МегаБайт/секунду?
Вот специально посмотрел на результат на одном из дев серверов
19000 csv файлов объёмом 4800 мб загрузились булком за 5.8 секунды.

Продакшен по загрузкам быстрее раз в 10, только на нём не могу посмотреть - там немного другой процесс, трудно сопоставить.
5 июл 11, 23:10    [10928426]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
alexeyvg
Вот специально посмотрел на результат на одном из дев серверов
19000 csv файлов объёмом 4800 мб загрузились булком за 5.8 секунды.

Человек же сказал, в неделю - терабайт.
Вы сперва недель 10 подгрузите, а потом посмотрите, как подсядет скорость...
5 июл 11, 23:14    [10928451]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
Makar4ik
alexeyvg
Вот специально посмотрел на результат на одном из дев серверов
19000 csv файлов объёмом 4800 мб загрузились булком за 5.8 секунды.

Человек же сказал, в неделю - терабайт.
Вы сперва недель 10 подгрузите, а потом посмотрите, как подсядет скорость...
Да с чего это она подсядет для булка???

И вообще, я просто ответил на просьбу рассказать о скорости загрузки.
5 июл 11, 23:46    [10928655]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
alexeyvg,

да я без наезда.
Вот жду от ТС описания таблицы.
Ну там 400 полей, много дыр.
А индексы-то, индексы?
И по каким параметрам данные выбираются из базы?

Вполне может так статься, что вместо MSSQL будет достаточно своего лично самописного движка на 300-900 строк, и всё залетает.
5 июл 11, 23:56    [10928732]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
delpasso
Положим eсли остановиться на CSV и дампнуть его на диск - вполне так реально уложится в 12-15 секунд.
Далее,как вариант, вообще не вставлять отпаршенные данные в SQL,а держать в промежуточном (CSV в данный момент) формате.
К примеру час данных (~ 20ГБ) - отдельный файл.
Плюс сделать определенную навигацию по этому хранилищу, которая будет уметь:
1) быстро находить нужный файл.
2) быстро читать определенную его часть.
3) загружать дата сабсет в SQL.

Вот я и про то же:
1. А надо ли вам загружать в SQL? Сможете ли вы для приложения быстро выцепить нужные данные сразу из дампов?
2. Если ответ на 1-й вопрос положительный, то почему CSV? Свой двоичный формат, и вперед! как максимум - плюс 3-4 файла индексов. Тоже решается не такой уж и большой кровью. :)
6 июл 11, 00:07    [10928798]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Ну и еще вопрос к топикстартеру, для размышления...
Какой процент данных из этого терабайта в неделю необходимо потом обработать и выдать приложению?
Ну, в среднем?
То есть, каков оценочный процент мертвых данных в базе?
6 июл 11, 00:19    [10928855]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
Makar4ik
скажите, а индекс в вашей таблице только один?
по дате?


Ну да. Сейчас пока не дергаемся с другими. В идеале хотелось бы 4-5 индексов.
Опять же. Если предположить что мы остаемся с кастом/CSV файлами и не будет сохранять в MSSQL вообще, то временного индекса будет достаточно
чтобы найти нужный CSV выкусить из него нужный рекордсет и залить его в ДБ для анализа.
Вопрос с хранилищем остается открытым.
Безусловно с ДБ проще и привычней работать, но непонятно как MSSQL будет вести себя на больших обьемах.
Допустим пришли мы к приемлемому решению и начали складывать себе данные каждый день в SQL.
Прошел год и вот наша табличка весит уже 60ТБ (1+Тб в неделю 52 недели в году).
Как скорость вставки будет отличаться через год от начальной?
Понятно что нужно делать партишенс. Вопрос как их делать оптимально?
Делать одну партицию на день данных и иметь 300 партиций в год или на ндедлю данных и соответственно иметь 50 партиций в год?

Plus. Как я уже сказал кол-во столбцов в таблице/CSV достаточно велико (400+).
Кол-во столбцов в отдельном типичном SELECT запросе будет в среднем 10-15.
Возможно имеет смысл перейти на колоночное хранилище вместо МSSQL?
Может ли кто-нибудь порекомендовать что-либо стоящее из существующих колоночных хранилищ, учитывая вышеописанные характеристики входящих данных?

Много вопросов одним словом...особенно к народу, который работал с похожими обьемами.
6 июл 11, 00:27    [10928905]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
alexeyvg
delpasso
Хм. Забавно.
А какой производительности добились, если не секрет?
В колличестве вставленных строк нет смысла наверно мерять.
У нас порядка 400 столбцов и данные достаточно дыряво распределены между ними.
Если мерять скажем в МегаБайт/секунду?
Вот специально посмотрел на результат на одном из дев серверов
19000 csv файлов объёмом 4800 мб загрузились булком за 5.8 секунды.

Продакшен по загрузкам быстрее раз в 10, только на нём не могу посмотреть - там немного другой процесс, трудно сопоставить.


5ГБ данных заливаете за 5.8 секунд в девелопменте, а на продакшине соответственно за 0.58 секунд?
Это ж космос.
Вы че в памяти все храните?
Не один же винт на таких скоростях не пишет.
Даже ССД...
6 июл 11, 00:31    [10928932]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
Makar4ik
alexeyvg,

да я без наезда.
Вот жду от ТС описания таблицы.
Ну там 400 полей, много дыр.
А индексы-то, индексы?
И по каким параметрам данные выбираются из базы?

Вполне может так статься, что вместо MSSQL будет достаточно своего лично самописного движка на 300-900 строк, и всё залетает.


DATETIME основной, да.
Есть еще порядка 4-5 важных и ключевых полей (строковых).
6 июл 11, 00:35    [10928949]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
delpasso
Прошел год и вот наша табличка весит уже 60ТБ (1+Тб в неделю 52 недели в году).
Как скорость вставки будет отличаться через год от начальной?
Никак

delpasso
Понятно что нужно делать партишенс. Вопрос как их делать оптимально?
Делать одну партицию на день данных и иметь 300 партиций в год или на ндедлю данных и соответственно иметь 50 партиций в год?
Зависит от способа загрузки

Лучьше на день, можно сделать более быструю загрузку.
delpasso
Если предположить что мы остаемся с кастом/CSV файлами и не будет сохранять в MSSQL вообще, то временного индекса будет достаточно
чтобы найти нужный CSV выкусить из него нужный рекордсет и залить его в ДБ для анализа.
Вопрос с хранилищем остается открытым.
Ну, если вам не нужна функциональность реляционной субд, то так наверное лучьше.
Всё таки субд на 60 тб - это немало.

Но вот если нужна... Делать самому такую функциональность ещё сложнее.
delpasso
Много вопросов одним словом...особенно к народу, который работал с похожими обьемами.
Вот у нас тоже прилично загружается (ну, в несколько раз меньше :-) ), но мы храним данные только за полгода (сейчас несколько тб)
6 июл 11, 00:36    [10928956]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
delpasso
Прошел год и вот наша табличка весит уже 60ТБ (1+Тб в неделю 52 недели в году).

И больше, ибо страницы не на 100% заполнены... при 400-х полях-то!
И логи...

delpasso
Может ли кто-нибудь порекомендовать что-либо стоящее из существующих колоночных хранилищ, учитывая вышеописанные характеристики входящих данных?

Я бы попробовал написать своё.
ну да, запросы к нему будут совсем не SQL.
ну да, не будет нормального планировщика.
ну да, не будет 150-ти индексов.
Но если точно знать, зачем, для чего и сколько, то в идеале, должно быть быстрее любых других.

если вы снимаете данные с датчика, и вам нужно построить график за день, то это одно.
А если вам нужно распределение по... ну не знаю чему там за весь год - другое.
Но по хорошему, при одном индексе по дате - что ваш личный движок, что MSSQL - все равно будет сканировать весь датасет за требуемый период.
6 июл 11, 00:36    [10928957]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
alexeyvg
delpasso
Прошел год и вот наша табличка весит уже 60ТБ (1+Тб в неделю 52 недели в году).
Как скорость вставки будет отличаться через год от начальной?
Никак
не совсем так.
Б-дерево индекса будет уже побольше немного (ну так, в 60 раз) )))
И операция с каждой конкретной записью просядет.
6 июл 11, 00:38    [10928966]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
delpasso
Member

Откуда:
Сообщений: 77
Makar4ik
delpasso
Положим eсли остановиться на CSV и дампнуть его на диск - вполне так реально уложится в 12-15 секунд.
Далее,как вариант, вообще не вставлять отпаршенные данные в SQL,а держать в промежуточном (CSV в данный момент) формате.
К примеру час данных (~ 20ГБ) - отдельный файл.
Плюс сделать определенную навигацию по этому хранилищу, которая будет уметь:
1) быстро находить нужный файл.
2) быстро читать определенную его часть.
3) загружать дата сабсет в SQL.

Вот я и про то же:
1. А надо ли вам загружать в SQL? Сможете ли вы для приложения быстро выцепить нужные данные сразу из дампов?
2. Если ответ на 1-й вопрос положительный, то почему CSV? Свой двоичный формат, и вперед! как максимум - плюс 3-4 файла индексов. Тоже решается не такой уж и большой кровью. :)


Ну мы склонны в данный момент как раз к кастом движку.
Просто неохота воротить "велосипеды".
Поэтому и советуемся с теми, кто с подобными проблемами сталкивался.
Если народ 5ГБ пишет в MS SQL за меньше чем секунду мы с удовольствием пойдем темже путем.
Тем более MS SQL у нас уже есть...
6 июл 11, 00:42    [10928987]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
delpasso
alexeyvg
пропущено...
Вот специально посмотрел на результат на одном из дев серверов
19000 csv файлов объёмом 4800 мб загрузились булком за 5.8 секунды.

Продакшен по загрузкам быстрее раз в 10, только на нём не могу посмотреть - там немного другой процесс, трудно сопоставить.


5ГБ данных заливаете за 5.8 секунд в девелопменте, а на продакшине соответственно за 0.58 секунд?
Это ж космос.
Вы че в памяти все храните?
Не один же винт на таких скоростях не пишет.
Даже ССД...
Скорость рейда до гигабайта в секунду, там 16 дисков. А что тут такого?

Да, на продакшене сам булк примерно такой же, там именно весь процесс быстрее (памяти много, ядер)
А на деве какое то хитрое кеширование и тоже рейд такой-же, просто он разделён на несколько виртуалок, и во время загрузки другими виртуалками не нагружался.

В общем, я к тому, что булк работает примерно на скорости дисков (на точно, но сопоставимо) - вот из этого и надо исходить.

Это коенечно при правильной организации (пишем в кучу, и т.д. )
6 июл 11, 00:42    [10928988]     Ответить | Цитировать Сообщить модератору
 Re: ETL процесс и хранилище больших обьемов данных советы/идеи  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
alexeyvg
Никак

Если у меня в табличке 100 записей по 5-ти полям, и всё лезет в 8 кб, то при чтении сервак сделает максимум 2 логических чтения страниц.

Если у меня много миллиардов, и я читаю одну запись, то логических чтений будет уже до 10-20ти (зависит от ширины индекса)
Если я вставляю, то вставка в индекс - вызывает те же логические чтения и еще и записи. В том же количестве, зависящем от глубины Б-дерева.
6 июл 11, 00:43    [10928992]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить