Агрегация данных (~100 млн строк) / Сравнение СУБД

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / Агрегация данных (~100 млн строк)

25 сообщений из 44, страница 1 из 2

все

Агрегация данных (~100 млн строк)

#39273019

unC.

Гость

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

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

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

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

Буду очень благодарен за любые советы!

...

Рейтинг:

0 / 0

13.07.2016, 13:16

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273038

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

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

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

...

Рейтинг:

0 / 0

13.07.2016, 13:31

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273052

unC.

Гость

Dimitry Sibiryakov, я опасался, что в этом решении препятствием будет размер данных (40 гигабайт). Разве не должно возникнуть проблем с этим при оперативной памяти в 4 гигабайта?

...

Рейтинг:

0 / 0

13.07.2016, 13:38

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273070

Yo.!

Гость

а в чем трудности то ? данные в postgres запихнул ?
postgres вполне адекватная субд, я бы на нем и решал.

если хочется новомодного секаса то можно взять виртуалку с clodera hadoop (one node типа) и нарисовать твою агрегацию на java + map-reduce. звучит, что у тебя на map-reduce идеально задача кладется, но если нет опыта с java ...

...

Рейтинг:

0 / 0

13.07.2016, 13:51

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273075

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

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

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

...

Рейтинг:

0 / 0

13.07.2016, 13:53

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273202

Leonid Kudryavtsev

Участник

Сообщения: 9 300

Рейтинг: 0 / 0

1) Все зависит от того, насколько жесткое ТЗ, критерии/правила агрегирования

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

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

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

2) С учетом "есть порядка 10 .txt файлов" подозреваю и join'ы нужны. Т.ч. из описания автора понятно, что ничего не понятно )))

...

Рейтинг:

0 / 0

13.07.2016, 15:31

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273373

Basil A. Sidorov

Участник

Сообщения: 11 633

Рейтинг: 0 / 0

unC.Нужно проагрегировать их по предприятиям
...
Опыт программирования у меня неплохой... и вы должны знать, что агрегация становится тривиальной после сортировки.
Возможно, что задача "быстро отсортировать ~100 миллионов строк" окажется несложной.

...

Рейтинг:

0 / 0

13.07.2016, 19:04

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273376

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

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

И что уже давно придуманы способы обойтись без сортировки тоже.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

13.07.2016, 19:11

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273395

Basil A. Sidorov

Участник

Сообщения: 11 633

Рейтинг: 0 / 0

Сортировка упрощает "мой" код

...

Рейтинг:

0 / 0

13.07.2016, 19:48

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273432

pkarklin

Участник

Откуда: Москва (Муром)

Сообщения: 78 075

Рейтинг: 0 / 0

unC.Буду очень благодарен за любые советы!

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

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

Ваш выбор - СУБД, которая имеет хороший инструментарий по массовой загрузке\выгрузке данных и неплохой движок, в части Intra-Query Parallelism. Какой-то дополнительный "язык" здесь не нужен.

...

Рейтинг:

0 / 0

13.07.2016, 22:19

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39273660

bochkov

Участник

Откуда: Камчатка

Сообщения: 4 116

Рейтинг: 0 / 0

Задача для etl
На Pentaho Di решается в 5 шагов
включая отчёт

...

Рейтинг:

0 / 0

14.07.2016, 12:09

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39274022

softwarer

Участник

Откуда: 127.0.0.1

Сообщения: 51 965

Рейтинг: 0 / 0

unC.Опыт программирования у меня неплохой, но с БД работал мало. Для работы выбрал Ruby + Postgresql. Но с ними всё идёт довольно вяло.
Это простейшая задача BI. И можно быть совершенно увереннным, что как только Вы её решите, понадобятся новые отчёты, новые агрегации итп.

Поэтому лично я выкинул бы Ruby, он здесь нафиг не сдался, равно как и программирование вообще, и взял бы любой BI/OLAP инструмент. Скажем, на привычном мне OWB всё названное делается мышкой. Для Postgres, сколь мне изменяет память, есть бесплатный Pentaho, и про него говорили, что он уже дошёл до уровня "можно пользоваться без особого мата на каждом шагу".

...

Рейтинг:

0 / 0

14.07.2016, 17:19

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39274445

mitek

Участник

Сообщения: 589

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

15.07.2016, 11:45

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39274594

DirksDR

Участник

Откуда: Пермь

Сообщения: 343

Рейтинг: 0 / 0

unC.,

Выложите один файл в облако, или вышлите в личку.
Хотел бы проверить пару идей.

...

Рейтинг:

0 / 0

15.07.2016, 13:18

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39274750

DirksDR

Участник

Откуда: Пермь

Сообщения: 343

Рейтинг: 0 / 0

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

+1
Берем VBA Excel, используем Scripting.Dictionary, 1 млн строк подсуммируются 2.5 секунды.
Результат сразу в Excel

...

Рейтинг:

0 / 0

15.07.2016, 15:17

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39277600

Гость сайта

Гость

Залейте файлы в одну таблицу, далее подготовьте в зависимости от структуры данных нужное количество справочников и отдельно таблицу для сделок. Обработайте и перелейте в справочники соотв-е записи. Дальше выборки пихайте в любые отчеты, хоть в xls, хоть в xlsx )

...

Рейтинг:

0 / 0

21.07.2016, 10:45

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39279429

ёёёёё

Участник

Сообщения: 2 086

Рейтинг: 0 / 0

Yo.!если хочется новомодного секаса то можно взять виртуалку с clodera hadoop (one node типа) и нарисовать твою агрегацию на java + map-reduce. звучит, что у тебя на map-reduce идеально задача кладется, но если нет опыта с java ...

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

...

Рейтинг:

0 / 0

25.07.2016, 10:18

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39279979

Yo.!

Гость

ЁёёёёСтранно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)
боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.

...

Рейтинг:

0 / 0

25.07.2016, 23:07

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39314975

клик50000

Гость

Yo.!,

qlikview решит данную задачу за 1 день

...

Рейтинг:

0 / 0

24.09.2016, 20:32

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39319165

Andrey Sribnyak

Участник

Откуда: Киев

Сообщения: 570

Рейтинг: 0 / 0

Yo.!ЁёёёёСтранно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)
боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.

Yo.!ЁёёёёСтранно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)
боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.

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

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

Код: plsql

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)

Код: plsql

1.
2.
3.
4.
5.
6.

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

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

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

Код: sql

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)

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

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.

...

Рейтинг:

0 / 0

01.10.2016, 21:47

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39319283

Сергей Макарин

Гость

вот ничего себе база!

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

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

...

Рейтинг:

0 / 0

02.10.2016, 20:12

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39319307

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

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

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

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

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

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

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

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

...

Рейтинг:

0 / 0

02.10.2016, 23:07

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39319308

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Сергей Макаринвот ничего себе база!

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

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

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

...

Рейтинг:

0 / 0

02.10.2016, 23:08

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39323465

Критик

Участник

Откуда: Москва / Калуга

Сообщения: 35 963

Рейтинг: 0 / 0

тест на

Код: sql

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)

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

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)

Объем таблицы:

Код: sql

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

Тестовый запрос:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

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

...

Рейтинг:

0 / 0

09.10.2016, 18:16

| Ответить | Цитировать | Написать

Агрегация данных (~100 млн строк)

#39323640

Andrey Sribnyak

Участник

Откуда: Киев

Сообщения: 570

Рейтинг: 0 / 0

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

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

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

https://northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)

...

Рейтинг:

0 / 0

10.10.2016, 11:30

| Ответить | Цитировать | Написать

25 сообщений из 44, страница 1 из 2

все

Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / Агрегация данных (~100 млн строк)

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=35&fpage=3&tid=1552255]:	0ms
get settings:	9ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	42ms
get topic data:	13ms
get forum data:	3ms
get page messages:	61ms
get tp. blocked users:	1ms
others:	250ms

total:	400ms