Геопространственный анализ на наборе данных о такси Нью-Йорка

В этом руководстве вы узнаете, как с помощью ClickHouse выполнять аналитические запросы к большим объёмам данных. Кроме того, вы научитесь обогащать данные с помощью словаря и писать запросы с JOIN.

Предварительные требования

Для работы с этим руководством вам понадобится:

Аккаунт ClickHouse Cloud (при регистрации начисляется 300 $ в виде бесплатных кредитов)
Сервис ClickHouse Cloud

Создайте таблицу

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

Выберите SQL console в меню слева
Нажмите на вкладку + рядом со значком домашней страницы, чтобы создать новый запрос
Введите следующий запрос в редакторе SQL и нажмите Run:

Expandable

CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;

Вставьте данные

Теперь, когда таблица создана, загрузите в неё данные о поездках на такси в Нью-Йорке из CSV-файлов в S3.Следующая команда вставляет около 2 000 000 строк в таблицу trips из двух файлов в S3 — trips_1.tsv.gz и trips_2.tsv.gz:

Expandable

INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0

Дождитесь окончания вставки данных. Будет загружено около 150 МБ данных. Когда вставка завершится, проверьте количество строк в таблице trips:

SELECT count() FROM trips

В результате должно получиться 1 999 657 строк

Проанализируйте данные

После загрузки данных можно выполнить несколько запросов для их анализа.

Рассчитайте средний размер чаевых:
SELECT round(avg(tip_amount), 2) FROM trips
Рассчитайте среднюю стоимость поездки в зависимости от количества пассажиров:
SELECT passenger_count, ceil(avg(total_amount),2) AS average_total_amount FROM trips GROUP BY passenger_count

Рассчитайте ежедневное количество посадок пассажиров по районам:

SELECT
  pickup_date,
  pickup_ntaname,
  SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC

Рассчитайте продолжительность каждой поездки в минутах и сгруппируйте результаты по продолжительности:

SELECT
  avg(tip_amount) AS avg_tip,
  avg(fare_amount) AS avg_fare,
  avg(passenger_count) AS avg_passenger,
  count() AS count,
  truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
FROM trips
WHERE trip_minutes > 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC

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

SELECT
    pickup_ntaname,
    toHour(pickup_datetime) as pickup_hour,
    SUM(1) AS pickups
FROM trips
WHERE pickup_ntaname != ''
GROUP BY pickup_ntaname, pickup_hour
ORDER BY pickup_ntaname, pickup_hour

Создайте словарь

Далее вы создадите словарь (набор пар ключ-значение, хранящийся в памяти) с именем taxi_zone_dictionary, который сопоставляет идентификаторы местоположений с названиями боро Нью-Йорка. Источником данных служит CSV-файл со списком всех районов Нью-Йорка. Эти идентификаторы соответствуют столбцам pickup_nyct2010_gid и dropoff_nyct2010_gid в таблице trips.Ниже приведён фрагмент используемого CSV-файла в табличном виде. Столбец LocationID в файле соответствует столбцам pickup_nyct2010_gid и dropoff_nyct2010_gid в вашей таблице trips:

LocationID	Borough	Zone	service_zone
1	EWR	Newark Airport	EWR
2	Queens	Jamaica Bay	Boro Zone
3	Bronx	Allerton/Pelham Gardens	Boro Zone
4	Manhattan	Alphabet City	Yellow Zone
5	Staten Island	Arden Heights	Boro Zone

Выполните следующую SQL-команду: она создаёт словарь с именем taxi_zone_dictionary и заполняет его данными из CSV-файла в S3. URL файла: https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv.

CREATE DICTIONARY taxi_zone_dictionary
(
  `LocationID` UInt16 DEFAULT 0,
  `Borough` String,
  `Zone` String,
  `service_zone` String
)
PRIMARY KEY LocationID
SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(HASHED_ARRAY())

Если задать для LIFETIME значение 0, автоматические обновления отключаются, что позволяет избежать лишнего трафика к нашему S3 бакету. В других случаях вы можете настроить этот параметр иначе. Подробнее см. в разделе Обновление данных словаря с помощью LIFETIME.

Убедитесь, что всё работает. Следующий запрос должен вернуть 265 строк — по одной для каждого района:

SELECT * FROM taxi_zone_dictionary

Выполняйте запросы с помощью словаря

Чтобы получить значение из словаря, используйте функцию dictGet (или её разновидности). Передайте ей имя словаря, имя нужного значения и ключ (в нашем примере это столбец LocationID словаря taxi_zone_dictionary) — и функция вернёт соответствующее значение. Например, следующий запрос возвращает Borough для LocationID, равного 132 (это аэропорт JFK):

SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)

JFK находится в Куинсе. Обратите внимание, что время получения значения практически нулевое:

┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
│ Queens                                          │
└─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

Чтобы проверить, есть ли ключ в словаре, используйте функцию dictHas. Например, следующий запрос возвращает 1 (в ClickHouse это означает “true”):

SELECT dictHas('taxi_zone_dictionary', 132)

Следующий запрос возвращает 0, так как в словаре нет значения LocationID, равного 4567:

SELECT dictHas('taxi_zone_dictionary', 4567)

Чтобы получить название боро в запросе, используйте функцию dictGet. Например:

SELECT
  count(1) AS total,
  dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
FROM trips
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY borough_name
ORDER BY total DESC

Этот запрос подсчитывает количество поездок на такси по каждому боро, завершившихся в аэропорту LaGuardia или JFK. Результат выглядит следующим образом. Обратите внимание, что для довольно большого числа поездок район посадки неизвестен:

┌─total─┬─borough_name──┐
│ 23683 │ Unknown       │
│  7053 │ Manhattan     │
│  6828 │ Brooklyn      │
│  4458 │ Queens        │
│  2670 │ Bronx         │
│   554 │ Staten Island │
│    53 │ EWR           │
└───────┴───────────────┘

7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)

Выполнить JOIN

Наконец, напишите несколько запросов, которые выполняют JOIN словаря taxi_zone_dictionary с вашей таблицей trips.Начните с простого JOIN, работающего аналогично предыдущему запросу по аэропортам:

SELECT
    count(1) AS total,
    Borough
FROM trips
JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY Borough
ORDER BY total DESC

Результат выглядит так же, как и для запроса с dictGet:

┌─total─┬─Borough───────┐
│  7053 │ Manhattan     │
│  6828 │ Brooklyn      │
│  4458 │ Queens        │
│  2670 │ Bronx         │
│   554 │ Staten Island │
│    53 │ EWR           │
└───────┴───────────────┘

6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)

Обратите внимание, что результат приведённого выше запроса с JOIN совпадает с результатом предыдущего запроса, в котором использовалась функция dictGetOrDefault (за исключением того, что значения Unknown в него не попали). На самом деле под капотом ClickHouse вызывает функцию dictGet для словаря taxi_zone_dictionary, однако синтаксис JOIN привычнее для SQL-разработчиков.

Этот запрос возвращает строки для 1000 поездок с наибольшей суммой чаевых, а затем выполняет внутреннее соединение (INNER JOIN) каждой строки со словарём:

SELECT *
FROM trips
JOIN taxi_zone_dictionary
ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
WHERE tip_amount > 0
ORDER BY tip_amount DESC
LIMIT 1000

Вместо SELECT * лучше перечислять только те столбцы, которые действительно нужны запросу. Поскольку ClickHouse хранит данные по столбцам, чем меньше столбцов вы выбираете, тем меньше данных приходится читать, распаковывать и обрабатывать.

Дальнейшие шаги

Подробнее о ClickHouse читайте в следующих разделах документации:

Введение в первичные индексы в ClickHouse: узнайте, как ClickHouse использует разреженные первичные индексы для эффективного поиска нужных данных при выполнении запросов.
Интеграция внешнего источника данных: ознакомьтесь с вариантами интеграции источников данных, включая файлы, Kafka, PostgreSQL, конвейеры данных и многое другое.
Визуализация данных в ClickHouse: подключите к ClickHouse свой любимый инструмент визуализации или BI.
Справочник по ClickHouse SQL: изучите SQL-функции ClickHouse для преобразования, обработки и анализа данных.

Начало работы

Настройка

Примеры наборов данных

Миграция

Геопространственный анализ на наборе данных о такси Нью-Йорка

Предварительные требования

Создайте таблицу

Вставьте данные

Проанализируйте данные

Создайте словарь

Выполняйте запросы с помощью словаря

Выполнить JOIN

Дальнейшие шаги

​Предварительные требования

Создайте таблицу

Вставьте данные

Проанализируйте данные

Создайте словарь

Выполняйте запросы с помощью словаря

Выполнить JOIN

​Дальнейшие шаги

Предварительные требования

Дальнейшие шаги