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

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

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

примечание

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

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

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

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

SQL console

If you need a SQL client connection, your ClickHouse Cloud service has an associated web based SQL console; expand Connect to SQL console below for details.

Connect to SQL console

From your ClickHouse Cloud services list, click on a service.

This will redirect you to the SQL console.

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

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

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

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

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

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

   примечание

   Функция 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:

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

2. 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 секундами. (Случайное время необходимо для распределения нагрузки на источник словаря при обновлении на большом количестве серверов.)

3. Проверьте, что все прошло успешно - вы должны получить 265 строк (по одной строке для каждого района):

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

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

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

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

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

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

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

5. Выполнение соединения

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

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

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

   примечание

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

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

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

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

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