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

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

Полный набор данных можно получить несколькими способами:

• вставить данные напрямую в ClickHouse Cloud из S3 или GCS
• скачать подготовленные партиции
• В альтернативном варианте пользователи могут выполнять запросы к полному набору данных в нашей демонстрационной среде на sql.clickhouse.com.

примечание

Примерные запросы ниже были выполнены на Production экземпляре ClickHouse Cloud. Для получения дополнительной информации смотрите "Спецификации площадки".

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

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

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

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

Те же данные хранятся как в S3, так и в GCS; выберите любой из вкладок.

S3

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

GCS

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

Примерные запросы

Следующие запросы выполняются на описанном выше примере. Пользователи могут выполнять примерные запросы к полному набору данных на sql.clickhouse.com, изменяя запросы ниже для использования таблицы nyc_taxi.trips.

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

Каждый TSV файл имеет около 1 миллиона строк, а три файла содержат 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 Gbit (параллельное скачивание с s3.amazonaws.com восстанавливает как минимум половину канала в 1 Gbit). Некоторые файлы могут не полностью загрузиться. Проверьте размеры файлов и повторно скачайте любые, которые выглядят сомнительно.

к сведению

Если вы собираетесь выполнять описанные ниже запросы, вам необходимо использовать полное имя таблицы, 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