powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / Агрегация данных (~100 млн строк)
44 сообщений из 44, показаны все 2 страниц
Агрегация данных (~100 млн строк)
    #39273019
unC.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Всем добрый день!
Проводим экономическое исследование.
На входе есть порядка 10 .txt файлов. В них содержатся данные по 100 миллионам сделок (1 сделка = 1 строка, в каждой строке порядка 40 столбцов, разделённых "|") - суммарно порядка 40 гигабайт.

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

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

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

Буду очень благодарен за любые советы!
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273038
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
unC.Опыт программирования у меня неплохой, но с БД работал мало.

Для решения данной задачи СУБД не нужна. Любым языком программирования читаешь файлы и на
лету агрегируешь в памяти.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273052
unC.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Dimitry Sibiryakov, я опасался, что в этом решении препятствием будет размер данных (40 гигабайт). Разве не должно возникнуть проблем с этим при оперативной памяти в 4 гигабайта?
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273070
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
а в чем трудности то ? данные в postgres запихнул ?
postgres вполне адекватная субд, я бы на нем и решал.

если хочется новомодного секаса то можно взять виртуалку с clodera hadoop (one node типа) и нарисовать твою агрегацию на java + map-reduce. звучит, что у тебя на map-reduce идеально задача кладется, но если нет опыта с java ...
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273075
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
unC.Разве не должно возникнуть проблем с этим при оперативной памяти в 4 гигабайта?

Во-первых, размер доступной памяти для 64-х разрядных приложений больше.
Во-вторых, память требуется только под уже агрегированные данные, а они маленькие (раз в
одну книгу ёкселя влазят).
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273202
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
1) Все зависит от того, насколько жесткое ТЗ, критерии/правила агрегирования

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

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

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

2) С учетом "есть порядка 10 .txt файлов" подозреваю и join'ы нужны. Т.ч. из описания автора понятно, что ничего не понятно )))
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273373
Basil A. Sidorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
unC.Нужно проагрегировать их по предприятиям
...
Опыт программирования у меня неплохой... и вы должны знать, что агрегация становится тривиальной после сортировки.
Возможно, что задача "быстро отсортировать ~100 миллионов строк" окажется несложной.
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273376
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Basil A. Sidorovи вы должны знать, что агрегация становится тривиальной после сортировки.

И что уже давно придуманы способы обойтись без сортировки тоже.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273395
Basil A. Sidorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Сортировка упрощает "мой" код
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273432
pkarklin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
unC.Буду очень благодарен за любые советы!

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

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

Ваш выбор - СУБД, которая имеет хороший инструментарий по массовой загрузке\выгрузке данных и неплохой движок, в части Intra-Query Parallelism. Какой-то дополнительный "язык" здесь не нужен.
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39273660
bochkov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Задача для etl
На Pentaho Di решается в 5 шагов
включая отчёт
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39274022
Фотография softwarer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
unC.Опыт программирования у меня неплохой, но с БД работал мало. Для работы выбрал Ruby + Postgresql. Но с ними всё идёт довольно вяло.
Это простейшая задача BI. И можно быть совершенно увереннным, что как только Вы её решите, понадобятся новые отчёты, новые агрегации итп.

Поэтому лично я выкинул бы Ruby, он здесь нафиг не сдался, равно как и программирование вообще, и взял бы любой BI/OLAP инструмент. Скажем, на привычном мне OWB всё названное делается мышкой. Для Postgres, сколь мне изменяет память, есть бесплатный Pentaho, и про него говорили, что он уже дошёл до уровня "можно пользоваться без особого мата на каждом шагу".
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39274445
mitek
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
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
Агрегация данных (~100 млн строк)
    #39274594
Фотография DirksDR
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
unC.,

Выложите один файл в облако, или вышлите в личку.
Хотел бы проверить пару идей.
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39274750
Фотография DirksDR
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Dimitry SibiryakovДля решения данной задачи СУБД не нужна. Любым языком программирования читаешь файлы и на
лету агрегируешь в памяти.

+1
Берем VBA Excel, используем Scripting.Dictionary, 1 млн строк подсуммируются 2.5 секунды.
Результат сразу в Excel
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39277600
Залейте файлы в одну таблицу, далее подготовьте в зависимости от структуры данных нужное количество справочников и отдельно таблицу для сделок. Обработайте и перелейте в справочники соотв-е записи. Дальше выборки пихайте в любые отчеты, хоть в xls, хоть в xlsx )
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39279429
ёёёёё
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Yo.!если хочется новомодного секаса то можно взять виртуалку с clodera hadoop (one node типа) и нарисовать твою агрегацию на java + map-reduce. звучит, что у тебя на map-reduce идеально задача кладется, но если нет опыта с java ...

Странно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39279979
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
ЁёёёёСтранно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)
боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39314975
клик50000
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Yo.!,

qlikview решит данную задачу за 1 день
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39319165
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Yo.!ЁёёёёСтранно, что в соотвествии с жизнным кредо вы не предлагаете
старомодный
:)
боюсь oracle 9 уже лет 10 не в моде, а вот oracle 12 самое то, но не бесплатно.

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

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

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


Код: plsql
1.
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
1.
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
Агрегация данных (~100 млн строк)
    #39319283
вот ничего себе база!

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

я бы на мускул поставил
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39319307
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
unC.Опыт программирования у меня неплохой, но с БД работал мало. Для работы выбрал Ruby + Postgresql. Но с ними всё идёт довольно вяло.


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

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

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

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

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

Не думай, что это долгие сроки, для таких задач долгие -- это годы, месяцы -- средние. И риски там -- не сделать вообще ничего.
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39319308
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Сергей Макаринвот ничего себе база!

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

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

Вот мускулю такое точно противопоказано.
Начнём с того, что ты это загружать туда будешь полгода, если вообще сможешь...
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39323465
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
тест на
Код: sql
1.
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
1.
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
Агрегация данных (~100 млн строк)
    #39323640
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Критик
то есть меньше секунды на самом обычном пользовательском CPU i7-3770,
база лежала на RAM-диске, сама таблица со 100 млн записей весит всего 676.219 MB

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

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

https://northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39323672
servit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
DirksDRВыложите один файл в облако, или вышлите в личку.
Хотел бы проверить пару идей.Проверяйте. Не забудьте поделиться результатами.
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39323764
afgm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Andrey SribnyakМожешь попробовать на моих данных?

https://northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)
Без описания полей, без докачки, на скорости улитки...
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39323768
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
afgmAndrey SribnyakМожешь попробовать на моих данных?

https://northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)
Без описания полей, без докачки, на скорости улитки...

wget докачивает

Там файл с разделителем "|"

а сама таблица очень простая:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE [dbo].[order_line](
	[ol_o_id] [int] NOT NULL,
	[ol_d_id] [tinyint] NOT NULL,
	[ol_w_id] [int] NOT NULL,
	[ol_number] [tinyint] NOT NULL,
	[ol_i_id] [int] NOT NULL,
	[ol_delivery_d] [datetime] NOT NULL,
	[ol_amount] [decimal](10, 4) NOT NULL,
	[ol_supply_w_id] [int] NOT NULL,
	[ol_quantity] [smallint] NOT NULL,
	[ol_dist_info] [char](24) NULL
) ON [PRIMARY]
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39323770
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кстати... сравнение только выполнения запроса - это только половина проблемы...
Целиком проблема стоит - закачать файл и посчитать...


И только полное время стоит сравнивать...

Здесь как раз Polybase от Micrisoft (или подобные решения от конкурентов тот же DB2 или террадата) думаю смогут круто ускорить проблему:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
drop external table ext.order_line

CREATE EXTERNAL TABLE  ext.order_line(
	ol_o_id int NOT NULL,
	ol_d_id tinyint NOT NULL,
	ol_w_id int NOT NULL,
	ol_number tinyint NOT NULL,
	ol_i_id int NOT NULL,
	ol_delivery_d char(29) NOT NULL,
	ol_amount decimal(10,4) NOT NULL,
	ol_supply_w_id int NOT NULL,
	ol_quantity smallint NOT NULL,
	ol_dist_info char(24) NULL
)
WITH (
    LOCATION='order_line.txt', 
    DATA_SOURCE=TpccStorage,
	FILE_FORMAT=TextFile

);

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 ext.order_line
GROUP BY ol_number
ORDER BY ol_number;



На все про все 2 минуты 48 секунд
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39323778
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
afgm на скорости улитки...

HTTP request sent, awaiting response... 200 OK
Length: 9246076052 (8.6G) [application/octet-stream]
Saving to: `order_line.txt.1'

100%[=========================================================================================================================================================================>] 9,246,076,052 24.8M/s in 4m 8s

2016-10-10 10:57:29 (35.6 MB/s) - `order_line.txt.1' saved [9246076052/9246076052]
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324102
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Andrey SribnyakMicrosoft 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.


редкий случай когда импала работает:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
[quickstart.cloudera:21000] > 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;
Query: 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       | 5000.0740  | 14400000    |
| 2   | 2         | 72000000 | 71979552805.7618 | 5       | 4998.5800  | 14400000    |
| 3   | 3         | 72000000 | 72016792814.8090 | 5       | 5001.1661  | 14400000    |
| 4   | 4         | 72000000 | 72026174580.4301 | 5       | 5001.8176  | 14400000    |
| 5   | 5         | 72000000 | 71995979084.4438 | 5       | 4999.7207  | 14400000    |
| 6   | 6         | 65453315 | 65445858569.8675 | 5       | 4999.4304  | 13090663    |
| 7   | 7         | 58910155 | 58882197836.0727 | 5       | 4997.6271  | 11782031    |
| 8   | 8         | 52362530 | 52367429545.1200 | 5       | 5000.4678  | 10472506    |
| 9   | 9         | 45822420 | 45821006829.0195 | 5       | 4999.8457  | 9164484     |
| 10  | 10        | 39274160 | 39261904262.5124 | 5       | 4998.4397  | 7854832     |
| 11  | 11        | 32723110 | 32713101576.5575 | 5       | 4998.4707  | 6544622     |
| 12  | 12        | 26165965 | 26175605528.6040 | 5       | 5001.8421  | 5233193     |
| 13  | 13        | 19619965 | 19618861642.7164 | 5       | 4999.7188  | 3923993     |
| 14  | 14        | 13080865 | 13086910283.7150 | 5       | 5002.3107  | 2616173     |
| 15  | 15        | 6543780  | 6544684119.6085  | 5       | 5000.6908  | 1308756     |
+-----+-----------+----------+------------------+---------+------------+-------------+
Fetched 15 row(s) in 12.84s


hadoop/impala в vmware, 13.5Gb RAM
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324178
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Andrey Sribnyak,

"осталось 10 часов" до завершения закачки,
ждать я не буду )
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324270
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
КритикAndrey Sribnyak,

"осталось 10 часов" до завершения закачки,
ждать я не буду )

Все что могу сделать - закачать в Ирландию :)
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324277
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Yo.!,
Если добавить фильтр по дате > '20151212'

Как отработает?
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324385
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Andrey SribnyakYo.!,
Если добавить фильтр по дате > '20151212'

Как отработает?
вечерком попробую (эта машинка дома), но по идеи ничего не измениться. идея бигдаты читать нахрен все без разбору.
единственно, что 12.8 вышло сразу после импорта текста в parquet формат, видимо линукс что-то кеширует, т.к. после того как запустил примерно такой же запрос на другую табличку и снова на order_line, вышло уже 20+ секунд.
а на файлике 30 гб импала просто валиться у меня :)
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324506
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Ну что, MS победил и по скорости и по объему сжатия? ;)
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324704
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Критик,

Залил в North Europe и пожал g'zip

https://eurn.blob.core.windows.net/tpcc/order_line.txt.gz
2.45 GB
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324811
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
КритикНу что, MS победил и по скорости и по объему сжатия? ;)

Бог его знает...
Как я уже выше сказал, нужно учитывать как время загрузки в БД так и время выполнения...

Например Amazon Redshift (15 нод - это чуть больше 3 баксов в час)
Код: plsql
1.
select version()



PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1104


Загрузка обычного csv файла:

Код: sql
1.
2.
copy dbo.order_line from
    's3://tpcctest/order_line.txt&#39; WITH credentials '' delimiter as '|' REGION 'us-west-2'



Execution time: 3m 59s

Время выполнения запроса:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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;


Execution time: 1.12s

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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
where ol_delivery_d>'20151212'
GROUP BY ol_number
ORDER BY ol_number;



Execution time: 1.46s

Таблица при этом весит:4246Mb

Можно еще загружать зипованый файл:

Код: sql
1.
2.
copy dbo.order_line from
    's3://tpcctest/order_line.txt.gz; WITH credentials '' gzip delimiter as '|' REGION 'us-west-2'




COPY executed successfully

Execution time: 4m 44s

ну а так, все то же самое....
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324926
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
для файла время загрузки от 03:50 до 5 минут (несколько способов попробовал)

дополнительно индекс
Код: sql
1.
CREATE CLUSTERED COLUMNSTORE INDEX [CCIX] ON [dbo].[order_line] WITH (DROP_EXISTING = OFF,MAXDOP = 6)


строится 1 мин 17 сек на 6 ядрах


объем:
namerowsreserveddataindex_sizeunused;order_line как есть1439912536 546 880 KB6546760 KB64 KB56 KB;order_line колоночная143991253 2 079 112 KB 2078992 KB0 KB120 KB;

запрос 1:
Код: 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;


SQL Server Execution Times:
CPU time = 1732 ms, elapsed time = 313 ms .

запрос 2:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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
where ol_delivery_d>'20151212'
GROUP BY ol_number
ORDER BY ol_number;


SQL Server Execution Times:
CPU time = 17894 ms, elapsed time = 2679 ms .

в принципе, эти 2679 ms можно свести к 313 ms секционированием, но мне лень дальше тестить
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39324928
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
попробовал секционирование по месяцам: снижает время выполнения запроса с фильтром в 2 раза
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39325240
Фотография DirksDR
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Критикдля файла время загрузки от 03:50 до 5 минут (несколько способов попробовал)
Господа, что за компьютеры у Вас?
На моем офисном ПК (i5-2500 3.3GHz Win XP) просто последовательное чтение этого файла из VBA-программы занимает 13 минут:(
Чтение и подсуммирование (аналог Вашего запроса) выполняется за 18 мин 42 сек.
Другой вариант последовательного чтения дает 6.5 минут.
Может Вы файл с RAMdrive читаете?
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39325304
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
DirksDRКритикдля файла время загрузки от 03:50 до 5 минут (несколько способов попробовал)
Господа, что за компьютеры у Вас?
На моем офисном ПК (i5-2500 3.3GHz Win XP) просто последовательное чтение этого файла из VBA-программы занимает 13 минут:(
Чтение и подсуммирование (аналог Вашего запроса) выполняется за 18 мин 42 сек.
Другой вариант последовательного чтения дает 6.5 минут.
Может Вы файл с RAMdrive читаете?
да ладно, 8.6Gb читать 6.5 минут, для этого IDE диск понадобиться. у меня без упаковки паркет табличка весит всего 2.8Gb, при этом как я помню не все колонки в запросе участвуют. плюс ты читаешь поблочно, а базы читают многоблочным (readfilescatter) чтением.

у меня i7 десктоп, виртуалка на стареньком HDD, правда есть вариант, что часть блоков все таки кеширует линукс, лень виртуалку рестартовать ...
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39325716
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
DirksDR,

i7-3770, 32Гб ОЗУ
файл лежал на HDD 7200, база-приёмник была на RAM-диске,
загружал SSIS-пакетом в 6 потоков (не 8 - чтобы комп был доступен)
...
Рейтинг: 0 / 0
Агрегация данных (~100 млн строк)
    #39326163
blu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blu
Гость
Andrey Sribnyak
Залил в North Europe и пожал g'zip

https://eurn.blob.core.windows.net/tpcc/order_line.txt.gz
2.45 GB
вот этот камент на хабре случайно не Ваш ?
Если да, то результатом тестового запроса на DB2 BLU не поделитесь ?
...
Рейтинг: 0 / 0
44 сообщений из 44, показаны все 2 страниц
Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / Агрегация данных (~100 млн строк)
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]