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

Расширенное руководство

Чего ожидать от этого руководства?​

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

примечание

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

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

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

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

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

Подключение к SQL консоли

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

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

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

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

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

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

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

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

    примечание

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

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

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

    примечание

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

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

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

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

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

  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 (что соответствует "истинному" значению в 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. Вот несколько вариантов того, что делать дальше: