JupySQL и chDB

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

Настройка

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

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

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

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

примечание

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

Скачивание набора данных

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

Конфигурирование chDB и JupySQL

Следующим шагом импортируем модуль dbapi для chDB:

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

Теперь загрузим магию sql и создадим подключение к 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 и последним рейтингом в топ-10:

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

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

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

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