Данные о такси Нью-Йорка

Данные о такси Нью-Йорка составляют более 3 миллиардов поездок на такси и транспортных средствах с платой за проезд (Uber, Lyft и др.), начавшихся в Нью-Йорке с 2009 года. Набор данных можно получить несколькими способами:

• вставить данные непосредственно в ClickHouse Cloud из S3 или GCS
• скачать подготовленные разделы

Создание таблицы trips

Начнем с создания таблицы для поездок на такси:

Загрузка данных непосредственно из объектного хранилища

Давайте возьмем небольшой подмножество данных, чтобы ознакомится с ними. Данные находятся в TSV файлах в объектном хранилище, которые можно легко передать в ClickHouse Cloud с помощью функции s3 таблицы.

Одни и те же данные хранятся как в S3, так и в GCS; выберите любую вкладку.

GCS

Следующая команда передает три файла из бакета GCS в таблицу trips (синтаксис {0..2} является подстановочным знаком для значений 0, 1 и 2):

S3

Следующая команда передает три файла из бакета S3 в таблицу trips (синтаксис {0..2} является подстановочным знаком для значений 0, 1 и 2):

Пример запросов

Давайте посмотрим, сколько строк было вставлено:

Каждый TSV файл содержит около 1M строк, и три файла содержат 3,000,317 строк. Давайте посмотрим на несколько строк:

Обратите внимание, что есть столбцы для дат посадки и высадки, географических координат, информации о ставках, районов Нью-Йорка и многого другого:

Давайте запустим несколько запросов. Этот запрос показывает нам 10 районов, где чаще всего происходят посадки:

Результат:

Этот запрос показывает среднюю стоимость поездки в зависимости от количества пассажиров:

Вот корреляция между количеством пассажиров и расстоянием поездки:

Первая часть результата:

Загрузка подготовленных разделов

примечание

Следующие шаги предоставляют информацию о оригинальном наборе данных и метод загрузки подготовленных разделов в самоуправляемую среду ClickHouse.

Смотрите https://github.com/toddwschneider/nyc-taxi-data и http://tech.marksblogg.com/billion-nyc-taxi-rides-redshift.html для описания набора данных и инструкций по загрузке.

Загрузка приведет к получению около 227 ГБ несжатых данных в CSV файлах. Загрузка занимает около часа через соединение 1 Гбит (параллельная загрузка с s3.amazonaws.com восстанавливает как минимум половину канала 1 Гбит). Некоторые файлы могут быть не полностью загружены. Проверьте размеры файлов и повторно загрузите любые, которые выглядят сомнительными.

к сведению

Если вы будете выполнять запросы, описанные ниже, вы должны использовать полное имя таблицы, datasets.trips_mergetree.

Результаты на одном сервере

Q1:

0.490 секунд.

Q2:

1.224 секунды.

Q3:

2.104 секунды.

Q4:

3.593 секунды.

Использовался следующий сервер:

Два процессора Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz, всего 16 физических ядер, 128 ГБ ОЗУ, 8x6 ТБ HD на аппаратном RAID-5

Время выполнения является наилучшим из трех запусков. Но начиная со второго запуска, запросы читают данные из кеша файловой системы. Дополнительное кэширование не происходит: данные читаются и обрабатываются в каждом запуске.

Создание таблицы на трех серверах:

На каждом сервере:

На сервере-источнике:

Следующий запрос перераспределяет данные:

Это занимает 2454 секунды.

На трех серверах:

Q1: 0.212 секунд. Q2: 0.438 секунд. Q3: 0.733 секунд. Q4: 1.241 секунды.

Неудивительно, поскольку запросы масштабируются линейно.

У нас также есть результаты из кластера из 140 серверов:

Q1: 0.028 сек. Q2: 0.043 сек. Q3: 0.051 сек. Q4: 0.072 сек.

В этом случае время обработки запросов определяется прежде всего задержкой сети. Мы запускали запросы, используя клиент, расположенный в другом дата-центре, чем клистер, что добавляло около 20 мс задержки.

Резюме

серверы	Q1	Q2	Q3	Q4
1, E5-2650v2	0.490	1.224	2.104	3.593
3, E5-2650v2	0.212	0.438	0.733	1.241
1, AWS c5n.4xlarge	0.249	1.279	1.738	3.527
1, AWS c5n.9xlarge	0.130	0.584	0.777	1.811
3, AWS c5n.9xlarge	0.057	0.231	0.285	0.641
140, E5-2650v2	0.028	0.043	0.051	0.072