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) перед выполнением. В следующем запросе мы вычисляем максимальное количество баллов, набранных игроками, когда они занимали 1-е место:
Запросы с параметрами
Мы также можем использовать параметры в наших запросах. Параметры — это обычные переменные:
Затем мы можем использовать синтаксис {{variable}} в нашем запросе. Следующий запрос находит игроков, у которых было наименьшее количество дней между первой и последней подачами в топ-10:
Построение гистограмм
JupySQL также имеет ограниченные функции построения графиков. Мы можем создавать бокслоты или гистограммы.
Мы собираемся создать гистограмму, но сначала давайте напишем (и сохраним) запрос, который вычисляет рейтинги в топ-100, которые каждый игрок достиг. Мы сможем использовать это для создания гистограммы, которая подсчитывает, сколько игроков достигли каждого рейтинга:
Затем мы можем создать гистограмму, запустив следующее:
