Анализ данных с помощью ноутбуков Marimo и chDB

В этом руководстве вы узнаете, как анализировать набор данных из ClickHouse Cloud в ноутбуке Marimo с помощью chDB — быстрого встраиваемого SQL OLAP-движка на базе ClickHouse.

Требования:

• Python 3.8 или выше
• виртуальное окружение
• рабочий сервис ClickHouse Cloud и ваши сведения о подключении

Чему вы научитесь:

• Подключаться к ClickHouse Cloud из ноутбуков Marimo с помощью chDB
• Выполнять запросы к удаленным наборам данных и преобразовывать результаты в DataFrame в Pandas
• Визуализировать данные с помощью Plotly в Marimo
• Использовать реактивную модель выполнения Marimo для интерактивного анализа данных

Мы будем использовать набор данных UK Property Price, доступный в ClickHouse Cloud в числе стартовых наборов данных. Он содержит сведения о ценах, по которым продавались дома в Соединенном Королевстве с 1995 по 2024 год.

Чтобы добавить этот набор данных в существующий сервис ClickHouse Cloud, войдите в console.clickhouse.cloud под своей учетной записью.

В меню слева нажмите Data sources, затем — Predefined sample data:

На карточке данных о ценах продажи недвижимости в Великобритании (4 GB) выберите Get started:

Затем нажмите Import dataset:

ClickHouse автоматически создаст таблицу pp_complete в базе данных default и заполнит её 28,92 млн строк с данными о ценах.

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

export CLICKHOUSE_CLOUD_HOSTNAME=<HOSTNAME>
export CLICKHOUSE_CLOUD_USER=default
export CLICKHOUSE_CLOUD_PASSWORD=your_actual_password

Теперь активируйте виртуальное окружение. В активированном виртуальном окружении установите следующие пакеты, которые будут использоваться в этом руководстве:

pip install chdb pandas plotly marimo

Создайте новый ноутбук Marimo с помощью следующей команды:

marimo edit clickhouse_exploration.py

Должно открыться новое окно браузера с интерфейсом Marimo на localhost:2718:

Ноутбуки Marimo хранятся как обычные Python-файлы, поэтому их легко хранить в системе контроля версий и делиться ими с другими.

В новой ячейке импортируйте необходимые пакеты:

import marimo as mo
import chdb
import pandas as pd
import os
import plotly.express as px
import plotly.graph_objects as go

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

Добавьте новую ячейку и выполните простой запрос, чтобы проверить, что всё настроено правильно:

result = chdb.query("SELECT 'Hello ClickHouse from Marimo!'", "DataFrame")
result

Вы увидите результат под ячейкой, которую только что запустили:

Теперь, когда набор данных UK price paid настроен, а chDB запущен в ноутбуке Marimo, можно приступить к изучению данных. Представим, что мы хотим проверить, как со временем менялись цены в определённом регионе Великобритании, например в столице — Лондоне. Функция ClickHouse remoteSecure позволяет легко получать данные из ClickHouse Cloud. Вы можете указать chDB вернуть эти данные в виде DataFrame Pandas в процессе выполнения — это удобный и привычный способ работы с данными.

Создайте новую ячейку со следующим запросом, чтобы получить данные о ценах сделок с недвижимостью в Великобритании из вашего сервиса ClickHouse Cloud и преобразовать их в pandas.DataFrame:

query = f"""
SELECT
    toYear(date) AS year,
    round(avg(price)) AS price,
    bar(price, 0, 1000000, 80)
FROM remoteSecure(
    '{os.environ.get("CLICKHOUSE_CLOUD_HOSTNAME")}',
    'default.pp_complete',
    '{os.environ.get("CLICKHOUSE_CLOUD_USER")}',
    '{os.environ.get("CLICKHOUSE_CLOUD_PASSWORD")}'
)
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year
"""

df = chdb.query(query, "DataFrame")
df.head()

В приведённом выше фрагменте chdb.query(query, "DataFrame") выполняет указанный запрос и возвращает результат в виде Pandas DataFrame.

В этом запросе мы используем функцию remoteSecure для подключения к ClickHouse Cloud.

Функция remoteSecure принимает следующие параметры:

• строку подключения
• имя базы данных и таблицы, которые будут использоваться
• ваше имя пользователя
• ваш пароль

В целях безопасности рекомендуется использовать для имени пользователя и пароля переменные окружения, а не указывать их напрямую в функции, хотя при желании это тоже возможно.

Функция remoteSecure подключается к удалённому сервису ClickHouse Cloud, выполняет запрос и возвращает результат. В зависимости от объёма данных это может занять несколько секунд.

В данном случае мы получаем среднюю цену за год и применяем фильтр town='LONDON'. Затем результат сохраняется в переменной df как DataFrame.

Теперь, когда данные представлены в привычном виде, давайте посмотрим, как со временем менялись цены на недвижимость в Лондоне.

Marimo особенно хорошо работает с интерактивными библиотеками визуализации, такими как Plotly. В новой ячейке создайте интерактивный график:

fig = px.line(
    df, 
    x='year', 
    y='price',
    title='Average Property Prices in London Over Time',
    labels={'price': 'Average Price (£)', 'year': 'Year'}
)

fig.update_traces(mode='lines+markers')
fig.update_layout(hovermode='x unified')
fig

Неудивительно, что цены на недвижимость в Лондоне со временем значительно выросли.

Одна из сильных сторон Marimo — реактивная модель выполнения. Давайте создадим интерактивный виджет для динамического выбора разных городов.

В новой ячейке создайте выпадающий список для выбора города:

town_selector = mo.ui.dropdown(
    options=['LONDON', 'MANCHESTER', 'BIRMINGHAM', 'LEEDS', 'LIVERPOOL'],
    value='LONDON',
    label='Select a town:'
)
town_selector

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

query_reactive = f"""
SELECT
    toYear(date) AS year,
    round(avg(price)) AS price
FROM remoteSecure(
    '{os.environ.get("CLICKHOUSE_CLOUD_HOSTNAME")}',
    'default.pp_complete',
    '{os.environ.get("CLICKHOUSE_CLOUD_USER")}',
    '{os.environ.get("CLICKHOUSE_CLOUD_PASSWORD")}'
)
WHERE town = '{town_selector.value}'
GROUP BY year
ORDER BY year
"""

df_reactive = chdb.query(query_reactive, "DataFrame")
df_reactive

Теперь создайте диаграмму, которая будет автоматически обновляться при смене города. Вы можете переместить диаграмму над динамическим DataFrame, чтобы она отображалась под ячейкой с выпадающим списком.

fig_reactive = px.line(
    df_reactive,
    x='year',
    y='price',
    title=f'Average Property Prices in {town_selector.value} Over Time',
    labels={'price': 'Average Price (£)', 'year': 'Year'}
)

fig_reactive.update_traces(mode='lines+markers')
fig_reactive.update_layout(hovermode='x unified')
fig_reactive

Теперь при выборе города в раскрывающемся списке график будет обновляться автоматически:

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

В новой ячейке добавьте следующее:

year_slider = mo.ui.slider(
    start=1995,
    stop=2024,
    value=2020,
    step=1,
    label='Select Year:',
    show_value=True
)
year_slider

Теперь давайте запросим цены на отдельные объекты недвижимости за выбранный год. Обратите внимание, что здесь мы не выполняем агрегацию — нам нужны все сделки по отдельности, чтобы построить наше распределение:

query_distribution = f"""
SELECT
    price,
    toYear(date) AS year
FROM remoteSecure(
    '{os.environ.get("CLICKHOUSE_CLOUD_HOSTNAME")}',
    'default.pp_complete',
    '{os.environ.get("CLICKHOUSE_CLOUD_USER")}',
    '{os.environ.get("CLICKHOUSE_CLOUD_PASSWORD")}'
)
WHERE town = 'LONDON'
  AND toYear(date) = {year_slider.value}
  AND price > 0
  AND price < 5000000
"""

df_distribution = chdb.query(query_distribution, "DataFrame")

# create an interactive box plot.
fig_box = go.Figure()

fig_box.add_trace(
    go.Box(
        y=df_distribution['price'],
        name=f'London {year_slider.value}',
        boxmean='sd',  # Show mean and standard deviation
        marker_color='lightblue',
        boxpoints='outliers'  # Show outlier points
    )
)

fig_box.update_layout(
    title=f'Distribution of Property Prices in London ({year_slider.value})',
    yaxis=dict(
        title='Price (£)',
        tickformat=',.0f'
    ),
    showlegend=False,
    height=600
)

fig_box

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

В этом руководстве мы показали, как использовать chDB для исследования данных в ClickHouse Cloud с помощью ноутбуков Marimo. На примере набора данных UK Property Price мы показали, как выполнять запросы к удалённым данным ClickHouse Cloud с помощью функции remoteSecure() и напрямую преобразовывать результаты в DataFrame Pandas для анализа и визуализации. Благодаря chDB и реактивной модели выполнения Marimo специалисты по анализу данных могут использовать мощные возможности SQL ClickHouse вместе с привычными инструментами Python, такими как Pandas и Plotly, а также интерактивные виджеты и автоматическое отслеживание зависимостей, что делает исследовательский анализ более эффективным и воспроизводимым.