Перейти к основному содержимому
Перейти к основному содержимому

Расширенный учебник

Обзор​

Узнайте, как загружать и выполнять запросы к данным в ClickHouse на примере набора данных такси Нью-Йорка.

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

Вам нужен доступ к работающему сервису ClickHouse, чтобы завершить этот учебник. Для инструкций смотрите руководство Быстрый старт.

Создать новую таблицу​

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

  1. Подключитесь к SQL-консоли:
  • Для ClickHouse Cloud выберите сервис из выпадающего меню, а затем выберите SQL Консоль в левом навигационном меню.
  • Для самоуправляемого ClickHouse подключитесь к SQL-консоли по адресу https://_hostname_:8443/play. Проверьте детали у вашего администратора ClickHouse.
  1. Создайте следующую таблицу trips в базе данных default:

Добавить набор данных​

Теперь, когда вы создали таблицу, добавьте данные такси Нью-Йорка из CSV файлов в S3.

  1. Следующая команда вставляет ~2,000,000 строк в вашу таблицу trips из двух различных файлов в S3: trips_1.tsv.gz и trips_2.tsv.gz:

  2. Подождите, пока INSERT завершится. Это может занять некоторое время для загрузки 150 МБ данных.

  3. Когда вставка завершится, убедитесь, что всё прошло успешно:

    Этот запрос должен вернуть 1,999,657 строк.

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

Выполните несколько запросов для анализа данных. Исследуйте следующие примеры или попробуйте свой собственный SQL-запрос.

  • Рассчитайте среднюю сумму чаевых:

    Ожидаемый результат

  • Рассчитайте среднюю стоимость в зависимости от числа пассажиров:

    Ожидаемый результат

    Число passenger_count варьируется от 0 до 9:

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

    Ожидаемый результат

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

    Ожидаемый результат

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

    Ожидаемый результат

  1. Извлеките поездки в аэропорты ЛаГардиа или JFK:

    Ожидаемый результат

Создать словарь​

Словарь — это отображение пар ключ-значение, хранящееся в памяти. Для деталей смотрите Словари

Создайте словарь, связанный с таблицей в вашем сервисе ClickHouse. Таблица и словарь основаны на CSV-файле, который содержит строку для каждого района в Нью-Йорке.

Районы сопоставлены с названиями пяти районов Нью-Йорка (Бронкс, Бруклин, Манхэттен, Квинс и Стейтен-Айленд), а также с аэропортом Ньюарк (EWR).

Вот выдержка из CSV-файла, который вы используете в табличном формате. Столбец LocationID в файле сопоставляется со столбцами pickup_nyct2010_gid и dropoff_nyct2010_gid в вашей таблице trips:

LocationIDBoroughZoneservice_zone
1EWRАэропорт НьюаркEWR
2КвинсЗона ЯмайкаBoro Zone
3БронксAllerton/Pelham GardensBoro Zone
4МанхэттенАлфавитный городYellow Zone
5Стейтен-АйлендАрден ХейтсBoro Zone
  1. Выполните следующую SQL-команду, которая создаёт словарь с именем taxi_zone_dictionary и заполняет его данными из CSV-файла в S3. URL для файла: https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv.
примечание

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

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

  2. Используйте функцию dictGet (или её вариации), чтобы извлечь значение из словаря. Вы передаёте имя словаря, нужное значение и ключ (в нашем примере это столбец LocationID словаря taxi_zone_dictionary).

    Например, следующий запрос возвращает Borough, чье LocationID равно 132, что соответствует аэропорту JFK):

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

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

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

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

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

Выполнить соединение​

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

  1. Начните с простого JOIN, который действует аналогично предыдущему запросу о аэропорте выше:

    Ответ будет идентичен запросу dictGet:

    примечание

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

  2. Этот запрос возвращает строки для 1000 поездок с самой высокой суммой чаевых, затем выполняет внутреннее соединение каждой строки со словарем:

    примечание

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

Следующие шаги​

Узнайте больше о ClickHouse с помощью следующей документации: