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

Данные такси Нью-Йорка состоят из более чем 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 GiB RAM, 8x6 TB 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