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

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

Чего ожидать от этого учебника?​

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

примечание

В этом учебнике предполагается, что у вас есть доступ к запущенному сервису ClickHouse. Если нет, ознакомьтесь с Быстрым стартом.

1. Создайте новую таблицу​

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

  1. Подключитесь к SQL-консоли
SQL консоль

Если вам нужно подключение клиента SQL, ваша служба ClickHouse Cloud имеет ассоциированную веб-основу SQL консоли; разверните Подключиться к SQL консоли ниже для подробностей.

Подключиться к SQL консоли

Из вашего списка служб ClickHouse Cloud нажмите на службу.

Подключиться к SQL консоли

Это перенаправит вас в SQL консоль.

SQL консоль

Если вы используете self-managed ClickHouse, вы можете подключиться к SQL-консоли по адресу https://hostname:8443/play (узнайте у вашего администратора ClickHouse все детали).

  1. Создайте следующую таблицу trips в базе данных default:

2. Вставьте набор данных​

Теперь, когда у вас есть созданная таблица, давайте добавим данные такси NYC. Они находятся в CSV файлах на S3, и вы можете загрузить данные оттуда.

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

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

    примечание

    Функция s3 умело знает, как распаковать данные, а формат TabSeparatedWithNames сообщает ClickHouse, что данные разделены табуляцией, а также что нужно пропустить заголовок каждой строки файла.

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

    Вы должны увидеть около 2M строк (1,999,657 строк, если быть точным).

    примечание

    Обратите внимание, как быстро и как немного строк ClickHouse пришлось обработать для определения общего количества? Вы можете получить количество всего за 0.001 секунды с обработкой только 6 строк.

  4. Если вы выполните запрос, который должен обработать каждую строку, вы заметите, что обработано значительно больше строк, но время выполнения по-прежнему будет молниеносным:

    Этот запрос должен обработать 2M строк и вернуть 190 значений, но обратите внимание, что он делает это за около 1 секунды. Колонка pickup_ntaname представляет собой название района в Нью-Йорке, откуда началась поездка на такси.

3. Анализ данных​

Давайте выполнем некоторые запросы для анализа 2M строк данных...

  1. Начнем с простых расчетов, например, вычислим среднюю сумму чаевых:

    Ответ будет:

  2. Этот запрос вычисляет среднюю стоимость на основе количества пассажиров:

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

  3. Вот запрос, который вычисляет количество подач такси по районам:

    Результат будет выглядеть так:

  4. Этот запрос вычисляет длину поездки и группирует результаты по этому значению:

    Результат будет выглядеть так:

  5. Этот запрос показывает количество подач такси в каждом районе, разбитое по часам дня:

    Результат будет выглядеть так:

  6. Давайте посмотрим на поездки в аэропорты ЛаГвардия или JFK:

    Ответ будет:

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

Если вы новичок в ClickHouse, важно понимать, как работают словаря. Простым способом думать о словаре является отображение пар ключ->значение, которое хранится в памяти. Подробности и все варианты для словарей приведены в конце учебника.

  1. Давайте посмотрим, как создать словарь, связанный с таблицей в вашем сервисе ClickHouse. Таблица и, следовательно, словарь будут основаны на CSV файле, который содержит 265 строк, по одной строке для каждого района Нью-Йорка. Районы сопоставлены с названиями боро Нью-Йорка (в Нью-Йорке 5 боро: Бронкс, Бруклин, Манхэттен, Квинс и Стейтен-Айленд), и этот файл также считает аэропорт Ньюарка (EWR) боро.

Это часть CSV файла (представленная в виде таблицы для ясности). Колонка LocationID в файле сопоставляется с колонками pickup_nyct2010_gid и dropoff_nyct2010_gid в вашей таблице trips:

LocationIDBoroughZoneservice_zone
1EWRNewark AirportEWR
2QueensJamaica BayBoro Zone
3BronxAllerton/Pelham GardensBoro Zone
4ManhattanAlphabet CityYellow Zone
5Staten IslandArden HeightsBoro Zone
  1. URL для файла https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv. Запустите следующий SQL, который создает словарь с именем taxi_zone_dictionary и заполняет словарь из CSV файла на S3:
примечание

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

Например:

указывает словарю обновляться через произвольное время между 1 и 10 секундами. (Произвольное время необходимо для распределения нагрузки на источник словаря при обновлении на большом количестве серверов.)

  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. Результат выглядит следующим образом, и обратите внимание, что есть довольно много поездок, где район подачи неизвестен:

5. Выполните Join​

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

  1. Мы можем начать с простого JOIN, который действует аналогично предыдущему запросу об аэропортах:

    Ответ будет выглядеть знакомо:

    примечание

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

  2. Мы не используем SELECT * часто в ClickHouse - вы должны получать только те колонки, которые вам действительно нужны! Но трудно найти запрос, который занимает много времени, поэтому этот запрос намеренно выбирает каждую колонку и возвращает каждую строку (за исключением встроенного максимума в 10,000 строк в ответе по умолчанию), а также выполняет правое соединение каждой строки со словарем:

Поздравляем!​

Отлично - вы справились с учебником, и надеемся, у вас появилось лучшее понимание использования ClickHouse. Вот несколько вариантов, что делать дальше: