Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Агрегация данных (~100 млн строк)  [new]
unC.
Member

Откуда:
Сообщений: 2
Всем добрый день!
Проводим экономическое исследование.
На входе есть порядка 10 .txt файлов. В них содержатся данные по 100 миллионам сделок (1 сделка = 1 строка, в каждой строке порядка 40 столбцов, разделённых "|") - суммарно порядка 40 гигабайт.

Нужно проагрегировать их по предприятиям, осуществляющим сделки, и на выходе получить .xls файл. Агрегация не только типа "просуммировать данные по предприятию", но и немного сложнее - "найти наиболее популярный товар для каждого предприятия".

Опыт программирования у меня неплохой, но с БД работал мало. Для работы выбрал Ruby + Postgresql. Но с ними всё идёт довольно вяло.

Может есть, более удобные средства для выполнения задачи?
Поскольку проагрегировать данные надо лишь единожды, жёстко вопрос времени не стоит - лишь бы всё работалось удобно и за разумные сроки.

Буду очень благодарен за любые советы!
13 июл 16, 13:16    [19404013]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54847

unC.
Опыт программирования у меня неплохой, но с БД работал мало.

Для решения данной задачи СУБД не нужна. Любым языком программирования читаешь файлы и на
лету агрегируешь в памяти.

Posted via ActualForum NNTP Server 1.5

13 июл 16, 13:31    [19404120]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
unC.
Member

Откуда:
Сообщений: 2
Dimitry Sibiryakov, я опасался, что в этом решении препятствием будет размер данных (40 гигабайт). Разве не должно возникнуть проблем с этим при оперативной памяти в 4 гигабайта?
13 июл 16, 13:38    [19404175]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Yo.!
Guest
а в чем трудности то ? данные в postgres запихнул ?
postgres вполне адекватная субд, я бы на нем и решал.

если хочется новомодного секаса то можно взять виртуалку с clodera hadoop (one node типа) и нарисовать твою агрегацию на java + map-reduce. звучит, что у тебя на map-reduce идеально задача кладется, но если нет опыта с java ...
13 июл 16, 13:51    [19404268]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54847

unC.
Разве не должно возникнуть проблем с этим при оперативной памяти в 4 гигабайта?

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

Posted via ActualForum NNTP Server 1.5

13 июл 16, 13:53    [19404284]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 9907
1) Все зависит от того, насколько жесткое ТЗ, критерии/правила агрегирования

Если они НЕ меняются, можно и програмку написать, но если сегодня одни, а завтра другие (как обычно) ---> то запихать в СУБД и банальные SELECT'ы. Объем данных не настолько уж и большой.

Примерно такие таблички видел в продакшене в Oracle, сам на компе имею табличку > 4Gb в PostgreSQL - и Oracle и PostgreSQL вполне себе работают.

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

2) С учетом "есть порядка 10 .txt файлов" подозреваю и join'ы нужны. Т.ч. из описания автора понятно, что ничего не понятно )))
13 июл 16, 15:31    [19404849]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 11485
unC.
Нужно проагрегировать их по предприятиям
...
Опыт программирования у меня неплохой
... и вы должны знать, что агрегация становится тривиальной после сортировки.
Возможно, что задача "быстро отсортировать ~100 миллионов строк" окажется несложной.
13 июл 16, 19:04    [19406116]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54847

Basil A. Sidorov
и вы должны знать, что агрегация становится тривиальной после сортировки.

И что уже давно придуманы способы обойтись без сортировки тоже.

Posted via ActualForum NNTP Server 1.5

13 июл 16, 19:11    [19406151]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 11485
Сортировка упрощает "мой" код
13 июл 16, 19:48    [19406295]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
unC.
Буду очень благодарен за любые советы!


Ну, если вопрос поставлен так, то "Проводим экономическое исследование" надо поручить тому, кто к этому подготовлен. Триальная версия MS SQL, например, позволит подготовленному специалисту решить задачу за несколько секунд\минут\часов в зависимости от имеющегося оборудования.

Программировать свою СУБД, как это рекомендует Dimitry Sibiryakov, или податься в сторону новомодных "NoSQL", как это рекомендует Yo.! - это за гранью добра и зла.

Ваш выбор - СУБД, которая имеет хороший инструментарий по массовой загрузке\выгрузке данных и неплохой движок, в части Intra-Query Parallelism. Какой-то дополнительный "язык" здесь не нужен.
13 июл 16, 22:19    [19406676]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
bochkov
Member

Откуда: Камчатка
Сообщений: 4093
Задача для etl
На Pentaho Di решается в 5 шагов
включая отчёт
14 июл 16, 12:09    [19408410]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 67534
Блог
unC.
Опыт программирования у меня неплохой, но с БД работал мало. Для работы выбрал Ruby + Postgresql. Но с ними всё идёт довольно вяло.

Это простейшая задача BI. И можно быть совершенно увереннным, что как только Вы её решите, понадобятся новые отчёты, новые агрегации итп.

Поэтому лично я выкинул бы Ruby, он здесь нафиг не сдался, равно как и программирование вообще, и взял бы любой BI/OLAP инструмент. Скажем, на привычном мне OWB всё названное делается мышкой. Для Postgres, сколь мне изменяет память, есть бесплатный Pentaho, и про него говорили, что он уже дошёл до уровня "можно пользоваться без особого мата на каждом шагу".
14 июл 16, 17:19    [19410314]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
mitek
Member

Откуда:
Сообщений: 605
unC.
Опыт программирования у меня неплохой, но с БД работал мало. Для работы выбрал Ruby + Postgresql. Но с ними всё идёт довольно вяло.


В грядущем PostgreSQL 9.6 запилили параллелизм запросов. Выглядит очень неплохо (в 9.6beta1 на своих тестах в 10 воркеров из кэша получал масштабивование в 8 раз)

unC.
Может есть, более удобные средства для выполнения задачи?
Поскольку проагрегировать данные надо лишь единожды, жёстко вопрос времени не стоит - лишь бы всё работалось удобно и за разумные сроки.


Если задачи не будут расти как снежный ком и не очень хочется заморачиваться с полноценными СУБД, то можно посмотреть, например, Apache Drill (ANSI SQL-совместимый движок по данным в различных источниках). Можно csv-файл через CTAS загнать в parquet-файл (колоночный формат) и кверить как обычную таблицу. А можно прям из csv-файла (но сильно хуже производительность). Имеет ODBC/JDBC-драйверы и поддерживается многими BI-тулзами. Написан только на java (но достаточно экономно расходует память) и оптимизирован для in-memory
15 июл 16, 11:45    [19413435]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
DirksDR
Member

Откуда: Пермь
Сообщений: 340
unC.,

Выложите один файл в облако, или вышлите в личку.
Хотел бы проверить пару идей.
15 июл 16, 13:18    [19414142]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
DirksDR
Member

Откуда: Пермь
Сообщений: 340
Dimitry Sibiryakov
Для решения данной задачи СУБД не нужна. Любым языком программирования читаешь файлы и на
лету агрегируешь в памяти.

+1
Берем VBA Excel, используем Scripting.Dictionary, 1 млн строк подсуммируются 2.5 секунды.
Результат сразу в Excel
15 июл 16, 15:17    [19414917]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Гость сайта
Guest
Залейте файлы в одну таблицу, далее подготовьте в зависимости от структуры данных нужное количество справочников и отдельно таблицу для сделок. Обработайте и перелейте в справочники соотв-е записи. Дальше выборки пихайте в любые отчеты, хоть в xls, хоть в xlsx )
21 июл 16, 10:45    [19434762]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Ёёёёё
Guest
Yo.!
если хочется новомодного секаса то можно взять виртуалку с clodera hadoop (one node типа) и нарисовать твою агрегацию на java + map-reduce. звучит, что у тебя на map-reduce идеально задача кладется, но если нет опыта с java ...


Странно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)
25 июл 16, 10:18    [19449736]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Yo.!
Guest
Ёёёёё
Странно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)

боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.
25 июл 16, 23:07    [19454289]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
клик50000
Guest
Yo.!,

qlikview решит данную задачу за 1 день
24 сен 16, 20:32    [19704754]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 600
Yo.!
Ёёёёё
Странно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)

боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.


Yo.!
Ёёёёё
Странно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)

боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.


Это Вы тролите так? :)

PostgreSql - на очень средней машине (Azure - Standart A6 (4 проца 28 гиг памяти))
Понятно, что не 4...


tpcc=# select version();


version
-------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)



tpcc=# select count(*) from order_line
tpcc-# ;
   count
-----------
 143991253
(1 row)


tpcc=# SELECT  row_number() over (order by ol_number) rnk,
 ol_number
 ,sum(ol_quantity) AS sum_qty
 ,sum(ol_amount) AS sum_amount
 ,avg(ol_quantity) AS avg_qty
 ,avg(ol_amount) AS avg_amount
 ,count(*) AS count_order
FROM order_line
GROUP BY ol_number
ORDER BY ol_number;


rnk | ol_number | sum_qty | sum_amount | avg_qty | avg_amount | count_order
-----+-----------+----------+------------------+--------------------+-----------------------+-------------
1 | 1 | 72000000 | 72001066703.2736 | 5.0000000000000000 | 5000.0740766162222222 | 14400000
2 | 2 | 72000000 | 71979552805.7618 | 5.0000000000000000 | 4998.5800559556805556 | 14400000
3 | 3 | 72000000 | 72016792814.8090 | 5.0000000000000000 | 5001.1661676950694444 | 14400000
4 | 4 | 72000000 | 72026174580.4301 | 5.0000000000000000 | 5001.8176791965347222 | 14400000
5 | 5 | 72000000 | 71995979084.4438 | 5.0000000000000000 | 4999.7207697530416667 | 14400000
6 | 6 | 65453315 | 65445858569.8675 | 5.0000000000000000 | 4999.4304008794283376 | 13090663
7 | 7 | 58910155 | 58882197836.0727 | 5.0000000000000000 | 4997.6271354295961367 | 11782031
8 | 8 | 52362530 | 52367429545.1200 | 5.0000000000000000 | 5000.4678483946440327 | 10472506
9 | 9 | 45822420 | 45821006829.0195 | 5.0000000000000000 | 4999.8457991764184432 | 9164484
10 | 10 | 39274160 | 39261904262.5124 | 5.0000000000000000 | 4998.4397199726741450 | 7854832
11 | 11 | 32723110 | 32713101576.5575 | 5.0000000000000000 | 4998.4707407941207300 | 6544622
12 | 12 | 26165965 | 26175605528.6040 | 5.0000000000000000 | 5001.8421886225101960 | 5233193
13 | 13 | 19619965 | 19618861642.7164 | 5.0000000000000000 | 4999.7188177237828916 | 3923993
14 | 14 | 13080865 | 13086910283.7150 | 5.0000000000000000 | 5002.3107354578615405 | 2616173
15 | 15 | 6543780 | 6544684119.6085 | 5.0000000000000000 | 5000.6908236588791188 | 1308756
(15 rows)



Time: 166290.605 ms


SQL Server
Select @@version

Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0.2164.0 (X64) Sep 9 2016 20:13:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE ('default');
go
set statistics time on
go
SELECT  row_number() over (order by ol_number) rnk,
	ol_number
	,sum(ol_quantity) AS sum_qty
	,sum(ol_amount) AS sum_amount
	,avg(ol_quantity) AS avg_qty
	,avg(ol_amount) AS avg_amount
	,count(*) AS count_order
FROM dbo.order_line
GROUP BY ol_number
ORDER BY ol_number;

(15 row(s) affected)

SQL Server Execution Times:
CPU time = 1390 ms, elapsed time = 15716 ms.
1 окт 16, 21:47    [19733435]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Сергей Макарин
Member

Откуда: Москва
Сообщений: 1
вот ничего себе база!

я хотел бы поработать с такой огромной базой, просто ради эсперимента.

я бы на мускул поставил
2 окт 16, 20:12    [19735231]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
unC.
Опыт программирования у меня неплохой, но с БД работал мало. Для работы выбрал Ruby + Postgresql. Но с ними всё идёт довольно вяло.


Ruby тут вообще ни при чём,
а вот PG, конечно, лучшее из бесплатных.

Но 100 млн строк -- это много, и уже нужно ОЧЕНЬ ХОРОШО уметь готовить.
Их и загрузить-то непросто, а запросы писать -- вообще сложно.

Из "совсем для тупых" можно порекомендовать HP Vertica,
она ТОЖЕ не без необходимости уметь готовить, но она чуть более предназначена для именно таких задач.

Для твоей задачи тебе хватит бесплатной версии.

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

Не думай, что это долгие сроки, для таких задач долгие -- это годы, месяцы -- средние. И риски там -- не сделать вообще ничего.
2 окт 16, 23:07    [19735596]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
Сергей Макарин
вот ничего себе база!

я хотел бы поработать с такой огромной базой, просто ради эсперимента.

я бы на мускул поставил


Вот мускулю такое точно противопоказано.
Начнём с того, что ты это загружать туда будешь полгода, если вообще сможешь...
2 окт 16, 23:08    [19735599]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35871
Блог
тест на
Select @@version

Microsoft SQL Server 2014 (SP1-CU6) (KB3144524) - 12.0.4449.0 (X64) Apr 13 2016 12:41:07 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

create table ORDER_DETAIL
(
ID_ORDER_DETAIL bigint IDENTITY(1,1),
ID_ORDER int not null,
ID_PRODUCT int not null,
QUANTITY int not null,
AMOUNT money not null
);
GO
CREATE CLUSTERED COLUMNSTORE INDEX [CCIX] ON [dbo].[ORDER_DETAIL] WITH (DROP_EXISTING = ON, DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE, MAXDOP = 4)


Объем таблицы:
sp_spaceused 'ORDER_DETAIL', true

name rows reserved data index_size unused;
ORDER_DETAIL 100 000 000 692 552 KB 692 448 KB 0 KB 104 KB


Тестовый запрос:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE ('default');
go
set statistics time on
go
select ID_ORDER, sum(QUANTITY), sum(AMOUNT), avg(QUANTITY), avg(AMOUNT), count(*)
  from ORDER_DETAIL
  group by ID_ORDER


Результат:
SQL Server Execution Times:
CPU time = 4553 ms, elapsed time = 829 ms.

то есть меньше секунды на самом обычном пользовательском CPU i7-3770,
база лежала на RAM-диске, сама таблица со 100 млн записей весит всего 676.219 MB
9 окт 16, 18:16    [19760876]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация данных (~100 млн строк)  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 600
Критик

то есть меньше секунды на самом обычном пользовательском CPU i7-3770,
база лежала на RAM-диске, сама таблица со 100 млн записей весит всего 676.219 MB


Какой-то мелкий файл :)

Можешь попробовать на моих данных?

https://northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)
10 окт 16, 11:30    [19763096]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Сравнение СУБД Ответить