ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / Агрегация данных (~100 млн строк)
19 сообщений из 44, страница 2 из 2
  2  все
Агрегация данных (~100 млн строк)
    #39323672
servit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
servit
Участник
DirksDRВыложите один файл в облако, или вышлите в личку.
Хотел бы проверить пару идей.Проверяйте. Не забудьте поделиться результатами.
...
Рейтинг: 0 / 0
10.10.2016, 12:15
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39323764
afgm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
afgm
Участник
Andrey SribnyakМожешь попробовать на моих данных?

https://northcentr.blob.core.windows.net/tpcc/order_line.txt
(8,6GB)
Без описания полей, без докачки, на скорости улитки...
...
Рейтинг: 0 / 0
10.10.2016, 13:41
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39323768
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
10.10.2016, 13:46
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39323770
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
10.10.2016, 13:50
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39323778
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
10.10.2016, 13:59
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39324102
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
10.10.2016, 23:01
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39324178
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Критик
Участник
Andrey Sribnyak,

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

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

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

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

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

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

https://eurn.blob.core.windows.net/tpcc/order_line.txt.gz
2.45 GB
...
Рейтинг: 0 / 0
11.10.2016, 16:49
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39324811
Andrey Sribnyak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
11.10.2016, 18:39
| Ответить | Цитировать | Написать  
Агрегация данных (~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
12.10.2016, 00:33
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39324928
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Критик
Участник
попробовал секционирование по месяцам: снижает время выполнения запроса с фильтром в 2 раза
...
Рейтинг: 0 / 0
12.10.2016, 00:51
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39325240
Фотография DirksDR
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DirksDR
Участник
Критикдля файла время загрузки от 03:50 до 5 минут (несколько способов попробовал)
Господа, что за компьютеры у Вас?
На моем офисном ПК (i5-2500 3.3GHz Win XP) просто последовательное чтение этого файла из VBA-программы занимает 13 минут:(
Чтение и подсуммирование (аналог Вашего запроса) выполняется за 18 мин 42 сек.
Другой вариант последовательного чтения дает 6.5 минут.
Может Вы файл с RAMdrive читаете?
...
Рейтинг: 0 / 0
12.10.2016, 12:46
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39325304
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
12.10.2016, 13:42
| Ответить | Цитировать | Написать  
Агрегация данных (~100 млн строк)
    #39325716
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Критик
Участник
DirksDR,

i7-3770, 32Гб ОЗУ
файл лежал на HDD 7200, база-приёмник была на RAM-диске,
загружал SSIS-пакетом в 6 потоков (не 8 - чтобы комп был доступен)
...
Рейтинг: 0 / 0
12.10.2016, 22:20
| Ответить | Цитировать | Написать  
Агрегация данных (~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
13.10.2016, 12:33
| Ответить | Цитировать | Написать  
19 сообщений из 44, страница 2 из 2
  2  все
Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / Агрегация данных (~100 млн строк)
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=35&msg=39324277&tid=1552255]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 153ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 60ms
get tp. blocked users:
 2ms
others: 251ms
total:  509ms
созд. / загр.: 509ms
x
x
Закрыть


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