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

Данные такси Нью-Йорка состоят из более чем 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 содержит примерно 1 млн строк, и три файла в сумме содержат 3,000,317 строк. Давайте посмотрим на несколько строк:

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

Давайте выполним несколько запросов. Этот запрос показывает 10 районов с наибольшим числом посадок:

Результат:

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

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

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

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

примечание

Следующие шаги предоставляют информацию об оригинальном наборе данных и метод загрузки подготовленных партиций в среду self-managed 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.

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

В1:

0.490 секунд.

В2:

1.224 секунд.

В3:

2.104 секунд.

В4:

3.593 секунд.

Был использован следующий сервер:

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

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

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

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

На исходном сервере:

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

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

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

В1: 0.212 секунд.
В2: 0.438 секунд.
В3: 0.733 секунд.
В4: 1.241 секунд.

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

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

В1: 0.028 сек.
В2: 0.043 сек.
В3: 0.051 сек.
В4: 0.072 сек.

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

Резюме

сервера	В1	В2	В3	В4
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