JupySQL и chDB

JupySQL — это библиотека Python, которая позволяет выполнять SQL в Jupyter блокнотах и в оболочке IPython. В этом руководстве мы научимся запрашивать данные с помощью chDB и JupySQL.

Установка

Сначала создадим виртуальное окружение:

Затем установим JupySQL, IPython и Jupyter Lab:

Мы можем использовать JupySQL в IPython, который можно запустить, выполнив:

Или в Jupyter Lab, выполнив:

примечание

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

Загрузка набора данных

Мы будем использовать один из наборов данных Jeff Sackmann's tennis_atp, который содержит метаданные о игроках и их рейтингах с течением времени. Давайте начнем с загрузки файлов с рейтингами:

Настройка chDB и JupySQL

Далее, давайте импортируем модуль dbapi для chDB:

И создадим соединение с chDB. Любые данные, которые мы сохраним, будут сохранены в директории atp.chdb:

Теперь загрузим sql magic и создадим соединение с chDB:

Далее, мы отобразим предел отображения, чтобы результаты запросов не были обрезаны:

Запрос данных из CSV файлов

Мы загрузили множество файлов с префиксом atp_rankings. Давайте используем оператор DESCRIBE, чтобы понять схему:

Мы также можем написать запрос SELECT непосредственно к этим файлам, чтобы увидеть, как выглядят данные:

Формат данных немного странный. Давайте очистим эту дату и используем оператор REPLACE, чтобы вернуть очищенный ranking_date:

Импортирование CSV файлов в chDB

Теперь мы собираемся сохранить данные из этих CSV файлов в таблице. По умолчанию база данных не сохраняет данные на диске, поэтому нам нужно сначала создать другую базу данных:

И теперь мы создадим таблицу под названием rankings, схема которой будет определена на основе структуры данных в CSV файлах:

Давайте быстренько проверим данные в нашей таблице:

Выглядит хорошо - вывод, как и ожидалось, такой же, как при запросе CSV файлов непосредственно.

Мы будем следовать тому же процессу для метаданных о игроках. На этот раз данные находятся все в одном CSV файле, поэтому давайте загрузим этот файл:

Затем создадим таблицу под названием players на основе содержимого CSV файла. Мы также очистим поле dob так, чтобы это был тип Date32.

В ClickHouse тип Date поддерживает только даты с 1970 года. Поскольку столбец dob содержит даты до 1970 года, вместо этого мы используем тип Date32.

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

Запрос к chDB

Прием данных завершен, теперь пришло время для увлекательной части - запросов данных!

Теннисные игроки получают очки в зависимости от того, как хорошо они выступают на турнирах, в которых участвуют. Очки для каждого игрока накапливаются в течение 52-недельного периода. Мы напишем запрос, который находит максимальное количество очков, накопленных каждым игроком, вместе с их рейтингом на момент достижения:

Интересно, что некоторые игроки в этом списке накопили много очков, не будучи номером 1 с этим количеством очков.

Сохранение запросов

Мы можем сохранить запросы, используя параметр --save в той же строке, что и магия %%sql. Параметр --no-execute означает, что выполнение запроса будет пропущено.

Когда мы запускаем сохраненный запрос, он будет преобразован в общее табличное выражение (CTE) перед выполнением. В следующем запросе мы вычисляем максимальное количество очков, достигнутых игроками, когда они были на первом месте:

Запросы с параметрами

Мы также можем использовать параметры в наших запросах. Параметры — это обычные переменные:

И затем мы можем использовать синтаксис {{variable}} в нашем запросе. Следующий запрос находит игроков, у которых было наименьшее количество дней между первым и последним рейтингом в топ-10:

Построение гистограмм

JupySQL также имеет ограниченные функции построения графиков. Мы можем создавать боксовые диаграммы или гистограммы.

Мы собираемся создать гистограмму, но сначала давайте напишем (и сохраним) запрос, который вычисляет рейтинги в топ-100, которых достиг каждый игрок. Мы сможем использовать это для создания гистограммы, которая подсчитывает, сколько игроков достигли каждого рейтинга:

Затем мы можем создать гистограмму, выполнив следующее: