Правила работы с SQL Lab

Редактировал(а) Елена Обрезкова 2026/06/08 17:07

1. Что такое SQL Lab

SQL Lab — это встроенный в Apache Superset инструмент для интерактивной работы с данными при помощи SQL. Он позволяет писать запросы к подключённым источникам данных (в нашем случае — к хранилищу на базе ClickHouse), выполнять их, просматривать результаты в табличном виде, сохранять запросы и выгружать результаты для дальнейшего анализа.

SQL Lab открывается из верхнего меню Superset (раздел SQL → SQL Lab).

Основные элементы интерфейса

  • Редактор запросов (SQL Editor) — центральная область, в которой вы набираете SQL-код. Поддерживает подсветку синтаксиса, автодополнение имён таблиц и столбцов, выполнение запроса по кнопке Run или сочетанию клавиш (как правило, Ctrl+Enter / Cmd+Enter). Если в редакторе выделить часть текста, выполнится только выделенный фрагмент — это удобно при отладке многошаговых запросов.
  • Панель выбора подключения и схемы (Database / Schema) — в верхней части редактора. Здесь выбирается база данных (подключение к ClickHouse) и схема (например, dwh, campaign, customer_data). Выбранная схема определяет, какие таблицы будут показаны в обозревателе и какая схема используется по умолчанию для неполных имён таблиц.
  • Панель схем и таблиц (Schema / Table browser) — слева. Отображает список таблиц выбранной схемы. По клику на таблицу можно раскрыть список её столбцов с типами данных, а также быстро увидеть превью данных и метаинформацию. Это удобный способ изучить структуру таблицы, не выполняя отдельный запрос.
  • Панель результатов (Results) — нижняя область. После выполнения запроса здесь появляется таблица с результатами. Доступны сортировка по столбцам, поиск/фильтрация по результату, а также действия выгрузки (CSV, копирование в буфер) и создание диаграммы (Chart) на основе результата.
  • История запросов (Query History) — отдельная вкладка, в которой хранятся ранее выполненные запросы: текст запроса, время выполнения, статус, длительность. Позволяет вернуться к ранее написанному запросу, переиспользовать его или скопировать в новый редактор.
  • Сохранённые запросы (Saved Queries) — раздел, в котором хранятся именованные запросы, которые вы явно сохранили для повторного использования.

Для чего используется SQL Lab

  • Предварительный анализ данных (ad-hoc запросы) — быстрая проверка гипотез, подсчёт метрик, выгрузка срезов.
  • Подготовка и проверка SQL перед созданием датасетов и дашбордов Superset.
  • Формирование выгрузок в CSV для передачи в смежные системы или офисные инструменты.
  • Изучение структуры и содержимого таблиц хранилища.

Ограничение: только SELECT-запросы

SQL Lab работает исключительно в режиме чтения данных. Допустимы только запросы типа SELECT (включая WITH ... SELECT).

2. Доступные схемы данных

В подключении к ClickHouse доступны три основные схемы. Выбирайте схему в панели Database / Schema или указывайте её явно в имени таблицы (схема.таблица).

2.1. dwh — основное хранилище данных

Схема dwh (Data Warehouse) — это ядро хранилища. В ней находятся фактические данные о покупательской активности и справочники.

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

2.2. campaign — данные по коммуникационным кампаниям

Схема campaign содержит данные, относящиеся к проведению коммуникационных кампаний: параметры кампаний, тип коммуникации, статус сообщений и активаций. 

2.3. customer_data* — внешние данные через S3

Схема customer_data предназначена для внешних данных, подгружаемых через объектное хранилище S3. Это, например, чеки вне программы лояльности для расчета пенертации ПЛ в чеки, выгрузки из сторонних систем и прочие наборы, которые загружаются по мере необходимости.

*Доступна только при наличии выполненной загрузки данных.

3. Правила написания запросов

Правила направлены на снижение нагрузки на кластер ClickHouse и ускорение получения результата.

3.1. Правило 1: Никогда не используй SELECT *

Перечисляйте только те столбцы, которые действительно нужны.

Плохой пример:

SELECT * FROM dwh.chequeitems_retro

Хороший пример:

SELECT
    contact_hash_calc,
    article_hash,
    d,
    cheque_id,
    quantity,
    summdisc/100
FROM dwh.chequeitems_retro
WHERE d >= toDate('2026-05-01') AND d <= toDate('2026-05-31')
LIMIT 1000

Почему SELECT * недопустим:

  • ClickHouse — колоночная СУБД: данные физически хранятся по столбцам. Запрос читает с диска только те столбцы, которые указаны в SELECT и WHERE. SELECT * заставляет систему прочитать все столбцы таблицы, которых в больших таблицах могут быть сотни.
  • Это резко увеличивает объём чтения с диска и нагрузку на сеть и память кластера, приводит к таймаутам и деградации скорости для всех пользователей.
  • Явный список столбцов делает запрос быстрее, дешевле и понятнее: видно, какие именно данные используются.

Правило: всегда перечисляйте конкретные столбцы.

3.2. Правило 2: Всегда ограничивай период

Каждый запрос к большим таблицам (chequeitems_retro, bonus_slim_retro, orderitems_retro и подобным) должен содержать фильтр по дате.

Примеры правильных фильтров по дате:

-- За один месяц
WHERE d >= toDate('2026-05-01') AND d <= toDate('2026-05-31')

-- За последние 7 дней (динамически)
WHERE d >= toDate(now()) - 7 AND d <= toDate(now())

3.3. Правило 3: Используй словари вместо JOIN больших таблиц:

В ClickHouse есть механизм словарей (dictionaries) — это предзагруженные справочники, которые целиком находятся в оперативной памяти кластера. Доступ к значению словаря по ключу выполняется почти мгновенно и не создаёт нагрузки на дисковую подсистему, в отличие от соединения (JOIN) со справочной таблицей, которое требует её сканирования.

Когда вам нужно «обогатить» строки фактовой таблицы атрибутами (ФИО клиента, название товара), используйте функцию dictGet вместо JOIN.

Синтаксис функции dictGet: dictGet('схема.имя_словаря', 'поле', ключ)

Где:

  • 'схема.имя_словаря' — имя словаря (строка), например 'dwh.d_contact';
  • 'поле' — имя возвращаемого атрибута (строка), например 'full_name';
  • ключ — значение ключа, по которому ищется запись.

Доступные словари

Контакты (клиенты) — словарь dwh.d_contact, ключ contact_hash_calc:

dictGet('dwh.d_contact', 'full_name', contact_hash_calc)        -- ФИО клиента
dictGet('dwh.d_contact', 'email', contact_hash_calc)            -- Email клиента
dictGet('dwh.d_contact', 'sms', contact_hash_calc)              -- Телефон клиента
dictGet('dwh.d_contact', 'gender', contact_hash_calc)           -- Пол
dictGet('dwh.d_contact', 'bonus_balance', contact_hash_calc)    -- Баланс бонусов

contact_hash_calc — это вычисляемое поле таблицы chequeitems_retro, которое автоматически возвращает хэш контакта по карте. Используйте его как есть.

Артикулы (товары) — словарь dwh.d_article_fat, ключ article_hash:

dictGet('dwh.d_article_fat', 'article_name', article_hash)      -- Название товара
dictGet('dwh.d_article_fat', 'category_name', article_hash)     -- Категория товара
dictGet('dwh.d_article_fat', 'brand_name', article_hash)        -- Бренд
dictGet('dwh.d_article_fat', 'subcategory_name', article_hash)  -- Подкатегория

Кампании — словарь dwh.d_campaign, ключ campaign_instance_hash:

dictGet('dwh.d_campaign', 'campaign_name', campaign_instance_hash)  -- Название кампании

Словарь dwh.d_campaign используется с ключом campaign_instance_hash (UInt64). Обратите внимание: в таблице chequeitems_retro поле campaign_id хранится как массив (Array(Int32)) всех кампаний, примененных к чеку. Прямое применение dictGet для кампаний возможно в таблице bonus_slim_retro.

Пример:

SELECT
    cheque_id,
    dictGet('dwh.d_contact', 'full_name', contact_hash_calc)    AS client_name,
    dictGet('dwh.d_article_fat', 'article_name', article_hash)  AS article_name,
    quantity,
    summdisc/100
FROM dwh.chequeitems_retro
WHERE d >= toDate('2026-05-01') AND d <= toDate('2026-05-31')
AND is_del = 0
AND oper_type = 1

Значения подставляются из памяти по ключу, без сканирования справочников и без JOIN.

Правило: для справочников, доступных как словари (d_contact, d_article_fat, d_campaign, d_shop и др.), всегда используйте dictGet. JOIN допустим для небольших таблиц, которые не представлены словарём (например, внешние сегменты в customer_data).

3.4. Правило 4: Используй CTE для сложных запросов

CTE (Common Table Expression) — это именованный подзапрос, объявляемый через ключевое слово WITH. CTE позволяет разбить сложный запрос на логические, читаемые блоки, переиспользовать промежуточные результаты и избежать повторных полных сканирований таблицы во вложенных подзапросах.

Базовый синтаксис:

WITH название_cte AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM название_cte

Пример с несколькими CTE (перечисляются через запятую) — сначала отбираем базовые данные, затем агрегируем, затем обогащаем:

WITH
base_data AS (
    SELECT
        contact_hash_calc,
        article_hash,
        summdisc/100 as summ,
        quantity
    FROM dwh.chequeitems_retro
    WHERE d >= toDate('2026-05-01') AND d <= toDate('2026-05-31')
),
aggregated AS (
    SELECT
        contact_hash_calc,
        article_hash,
        sum(summ) AS revenue,
        sum(quantity) AS units
    FROM base_data
    GROUP BY contact_hash_calc, article_hash
)
SELECT
    dictGet('dwh.d_contact', 'full_name', contact_hash_calc)   AS client_name,
    dictGet('dwh.d_article_fat', 'article_name', article_hash) AS article_name,
    revenue,
    units
FROM aggregated
ORDER BY revenue DESC
LIMIT 50

Рекомендации по использованию CTE:

  • Один CTE — одна логическая операция (фильтрация, агрегация, обогащение). Так запрос проще читать и отлаживать.
  • Фильтр по дате всегда размещайте в самом первом CTE, который обращается к таблице фактов, чтобы объём данных сокращался как можно раньше.
  • Обогащение через dictGet выполняйте на финальном шаге, после агрегации, когда строк уже немного.

3.5. Работа с хэш-полями: ошибка и её обход

Большинство таблиц хранилища содержат поля-идентификаторы в виде числовых хэшей (например, contact_hash_calc, article_hash). Эти поля хранятся как очень большие целые числа (UInt64), которые Python/Superset не может корректно обработать при отображении. При попытке вывести хэш напрямую в результате вы увидите ошибку:

ClickHouse Error: Python int too large to convert to C long

Правило: не включайте хэш-поля в SELECT без необходимости.

Если хэш всё же нужен (например, для отладки или передачи в другую систему) — преобразуйте его в строку с помощью функции toString():

-- Вызовет ошибку:
SELECT contact_hash_calc, summ
FROM dwh.chequeitems_retro
WHERE d >= toDate('2026-05-01') AND d <= toDate('2026-05-31')
LIMIT 10

-- Корректный вывод:
SELECT
    toString(contact_hash_calc) AS contact_hash,
    summdisc/100
FROM dwh.chequeitems_retro
WHERE d >= toDate('2026-05-01') AND d <= toDate('2026-05-31')
LIMIT 10

В большинстве аналитических запросов хэши используются только как ключи для dictGet или для GROUP BY — в этих случаях они не попадают в финальный SELECT и не вызывают ошибки.

4. Сохранение запросов и виртуальных датасетов

SQL Lab предоставляет два разных механизма сохранения, и важно понимать разницу между ними.

Сохранение SQL-запроса (необязательно)

Сохранение запроса через кнопку Save / Save query — это личный архив ваших запросов. Оно удобно, чтобы:

  • Не потерять написанный запрос при закрытии вкладки браузера.
  • Быстро найти ранее написанный запрос и переиспользовать его в будущем.
  • Поделиться текстом запроса с коллегой.

Сохранённый запрос — это просто текст SQL. Его нельзя подключить к дашборду, использовать как источник данных для чартов или на него нельзя настроить доступ по ролям.

Сохранение SQL-запроса необязательно — вы можете писать и выполнять запросы без сохранения, просматривать результаты и выгружать CSV без каких-либо ограничений.

Сохранение виртуального датасета (обязательно для построения отчётов)

Если вы хотите построить чарт или дашборд на основе вашего SQL-запроса, необходимо сохранить его как виртуальный датасет (Virtual Dataset).

Виртуальный датасет — это зарегистрированный в Superset SQL-запрос, который система воспринимает как источник данных (аналог таблицы или представления). Только после регистрации датасета вы сможете:

  • Создавать чарты на его основе (через меню Charts → + Chart).
  • Добавлять чарты на дашборды.
  • Настраивать метрики, вычисляемые столбцы и фильтры на уровне датасета.

Как сохранить виртуальный датасет:

  1. Напишите и протестируйте запрос в редакторе SQL Lab — убедитесь, что он возвращает нужные данные без ошибок.
  2. Нажмите кнопку Create Chart → выберите опцию Save as new .
  3. Задайте понятное название датасета (оно будет отображаться в списке источников данных) → нажмите Save & Explore.
  4. После сохранения система предложит сразу перейти к созданию чарта — или вы можете сделать это позже через раздел Charts.