Связаться с намиПодключиться

Анализ данных с помощью Yandex Query

Статья создана
Обновлена 13 мая 2024 г.

Yandex Query — это интерактивный сервис для бессерверного анализа данных. Он позволяет обрабатывать данные из различных хранилищ без создания выделенного кластера обработки данных, используя SQL-запросы. Yandex Query работает с хранилищами данных Yandex Object Storage, Managed Service for PostgreSQL, Managed Service for ClickHouse®.

Чтобы анализировать данные в DataSphere с помощью Query:

  1. Установите и настройте пакет yandex_query_magic.
  2. Создайте шаблон запроса.
  3. Обработайте результаты выполнения.

Перед началом работы

Перед началом работы нужно зарегистрироваться в Yandex Cloud, настроить сообщество и привязать к нему платежный аккаунт:

  1. На главной странице DataSphere нажмите Попробовать бесплатно и выберите аккаунт для входа — Яндекс ID или рабочий аккаунт в федерации (SSO).
  2. Выберите организацию Yandex Cloud Organization, в которой вы будете работать в Yandex Cloud.
  3. Создайте сообщество.
  4. Привяжите платежный аккаунт к сообществу DataSphere, в котором вы будете работать. Убедитесь, что у вас подключен платежный аккаунт, и он находится в статусе ACTIVE или TRIAL_ACTIVE. Если платежного аккаунта нет, создайте его в интерфейсе DataSphere.

Настройте инфраструктуру для работы с Yandex Query:

  1. Перейдите в консоль управления и привяжите платежный аккаунт к облаку.
  2. Создайте каталог, в котором будет работать Yandex Query

В стоимость анализа данных с помощью из Yandex Query входят:

Установите и настройте пакет yandex_query_magic

  1. Откройте проект DataSphere:

    1. Выберите нужный проект в своем сообществе или на главной странице DataSphere во вкладке Недавние проекты.

    2. Нажмите кнопку Открыть проект в JupyterLab и дождитесь окончания загрузки.
    3. Откройте вкладку с ноутбуком.

  2. Установите пакет yandex_query_magic, выполнив в ячейке ноутбука команду:

%pip install yandex_query_magic --upgrade
  1. Настройте пакет yandex_query_magic. Это можно сделать, задав параметры с помощью line команды yq_settings:
%yq_settings --folder-id <идентификатор_каталога> ...

Доступные параметры:

  • --folder-id <идентификатор_каталога> — идентификатор каталога для выполнения запросов Query.
  • --env-auth <переменная_окружения_environment_variable> — устанавливает режим аутентификации авторизованным ключом, содержимое которого находится в секрете Yandex DataSphere. Создайте секрет DataSphere, а имя созданного секрета укажите в параметре --env-auth.

Проверьте работу пакета

Команду %yq line magic можно использовать, когда весь SQL-запрос представлен одной строкой. В этом случае SQL-запрос выполняется с помощью ключевого слова %yq.

Выполните следующие команды в ноутбуке:

%load_ext yandex_query_magic
%yq_settings --env-auth <название_секрета_Yandex DataSphere> --folder-id <идентификатор_каталога>
%yq SELECT "Hello, world!"

Где:

  • %yq — имя "магической" команды.
  • SELECT "Hello, world!" — текст запроса к Query.

Чтобы выполнять многострочные SQL-запросы, необходимо использовать %%yq cell magic. Текст запроса должен начинаться с ключевого слова %%yq:

%%yq --folder-id <идентификатор_каталога> --name "Мой запрос"  --raw-results

SELECT
    col1,
    COUNT(*)
FROM table
GROUP BY col1

Где:

  • --folder-id — идентификатор каталога для выполнения запроса Query. По умолчанию используется каталог, указанный ранее через %yq_settings. Если он не указан, то используется каталог, в котором запущена виртуальная машина.
  • --name — (опционально) имя запроса.
  • --description — (опционально) описание запроса.
  • --raw-results — (опционально) параметр, чтобы возвращать необработанные результаты выполнения запроса в Query. Спецификация формата доступна в разделе Соответствие YQL и Json-типов.

Шаблонизация запросов с помощью mustache-синтаксиса

Шаблоны вычислений между Jupyter и Query можно использовать для работы с запросами, а также для выполнения типовых операций без написания кода на языках программирования. Для этого в Query встроена поддержка написания запросов в mustache-синтаксисе, когда все ключевые слова и директивы шаблонов размещаются внутри ключевых символов {{}}. Mustache-синтаксис можно использовать с Jinja2 или во встроенном интерпретаторе Mustache-синтаксиса.

Встроенные mustache-шаблоны {{ yq-name }} позволяют передавать переменные из среды исполнения Jupyter прямо внутрь SQL-запросов. При этом передаваемые переменные автоматически будут конвертированы в нужные структуры данных Query. Например:

myQuery = "select * from Departments"
%yq {{myQuery}}

Mustache-строка {{myQuery}} будет интерпретирована как название переменной, откуда нужно взять текст. При этом в Query будет отправлен для исполнения текст select * from Departments.

Использование mustache-шаблонов упрощает интеграцию между Jupyter и Query. Например, у вас есть Python list lst=["Academy", "Physics"], содержащий названия департаментов, данные из которых вы хотите обработать. Без поддержки mustache-синтаксиса в Query вам предварительно нужно было бы превратить Python list в строку и передать ее в SQL запрос. Пример запроса:

var lstStr = ",".join(lst)
sqlQuery = f'select "Academy" in ListCreate({lstStr});
%yq {{sqlQuery}}

То есть для работы со сложными типами данных нужно знать детали синтаксиса SQL Query. При использовании mustache-синтаксиса запрос можно написать проще:

%yq select "Academy" in {{lst}}

При этом lst будет распознан как Python list и будет автоматически вставлена правильная SQL-конструкция для работы со списками. В данном случае в результате всех преобразований в Query будет отправлен следующий текст запроса:

%yq select "Academy" in ListCreate("Academy", "Physics") as lst

Jinja2

При типовой работе в Jupyter и Query рекомендуется использовать встроенный mustache-синтаксис. Если вам нужны расширенные возможности шаблонизации, используйте шаблоны Jinja2.

Чтобы установить Jinja2, выполните команду:

%pip install Jinja2

Пример использования Jinja-шаблона с циклом for:

{% for user in users %}
    command = "select * from users where name='{{ user }}'"
{% endfor %}

Также c помощью Jinja-шаблонов можно выполнять различные операции обработки данных. В следующем примере, в зависимости от департамента, где обучается студент, выполняются различные операции над названием департамента:

{% if student.department == "Academy" %}
    {{ student.department|upper }}
{% elif  upper(student.department) != "MATHS DEPARTMENT" %}
    {{ student.department|capitalize }}
{% endif %}

Чтобы указать Jinja, что конвертация должна выполняться по правилам Query, используйте специальный фильтр to_yq. Например, Python list lst=["Academy", "Physics"] из предыдущего примера в Jinja-шаблоне будет иметь вид:

%%yq --jinja2
select "Academy" in {{lst|to_yq}}

В случаях, когда нужно отключить шаблонизацию, используйте аргумент --no-var-expansion:

%%yq --no-var-expansion
...

Встроенные mustache-шаблоны

Встроенные mustache-шаблоны в Yandex Query включены по умолчанию и с их помощью удобно выполнять базовые операции работы с переменными Jupyter:

lst=["Academy", "Physics"]

%yq select "Academy" in {{lst}}

Использование переменных Pandas DataFrame

Пример использования пакета yandex_query_magic и mustache-синтаксиса с Pandas DataFrame:

  1. Объявите переменную в Jupyter:

    df = pandas.DataFrame({'_float': [1.0],
                    '_int': [1],
                    '_datetime': [pd.Timestamp('20180310')],
                    '_string': ['foo']})

df может использоваться как переменная в запросах к Yandex Query. Во время выполнения запроса значение из переменной df используется для создания временной таблицы с тем же названием, df. Временная таблица может использоваться в пределах текущего исполняемого запроса в Yandex Query.

  1. Получите данные:

    %%yq
SELECT
    *
FROM mytable
INNER JOIN {{df}}
    ON mytable.id=df._int

Таблица соответствия типов Pandas и типов Query:

Тип Pandas Тип YQL Примечание
int64 Int64 При выходе значения за диапазон int64 возникнет ошибка выполнения SQL-запроса
float64 Double
datetime64[ns] Timestamp Точность до микросекунд. При задании наносекунд (поле nanosecond) возвращается исключение
str String

Использование переменных Python dict

Пример использования yandex_query_magic и mustache-синтаксиса с Python dict:

  1. Объявите переменную в Jupyter:

    dct = {"a": "1", "b": "2", "c": "test", "d": "4"}

    Теперь переменная dct может использоваться напрямую в запросах Query. Во время выполнения запроса dct будет преобразован в соответствующий объект YQL Dict:

    Ключ Значение
    а "1"
    b "2"
    c "test"
    d "4"

  2. Получите данные:

    %%yq
SELECT "a" in {{dct}}

Таблица соответствия типов Python dict и типов Query:

Тип Python Тип YQL Примечание
int Int64 При выходе значения за диапазон int64 возникнет ошибка выполнения SQL-запроса
float Double
datetime Timestamp
str String

Словарь также можно преобразовать в таблицу Pandas DataFrame с помощью конструктора:

df = pandas.DataFrame(dct)

Использование переменных Python list

Пример использования yandex_query_magic и mustache-синтаксиса с Python list:

  1. Объявите переменную в Jupyter:

    lst = [1,2,3]

    Тогда переменная lst может использоваться напрямую в запросах Query. Во время выполнения запроса lst будет преобразован в соответствующий объект YQL List.

  2. Получите данные:

    %%yq
SELECT 1 IN {{lst}}

Таблица соответствия типов Python list и типов Query:

Тип Python Тип YQL Примечание
int Int64 При выходе значения за диапазон int64 возникнет ошибка выполнения SQL-запроса
float Double
datetime Timestamp
str String

Список также можно преобразовать в таблицу Pandas DataFrame с помощью конструктора:

df = pandas.DataFrame(lst,
                      columns =['column1', 'column2', 'column3'])

Шаблоны Jinja

Шаблоны Jinja удобно использовать для создания SQL-запросов. Они позволяют автоматически вставлять в них разные данные, например, условия поиска, без необходимости писать каждый запрос вручную. Это упрощает работу, избавляет от ошибок и делает код более понятным.

Используя шаблоны Jinja, можно также автоматизировать создание запросов с повторяющимися элементами, добавляя, например, список значений для проверки в запросе, с помощью циклов в самом шаблоне. Это делает процесс ещё более гибким и экономит время на написание сложных запросов, когда нужно работать с множеством данных.

Ниже показано как отфильтровать данные в Yandex Query, используя переменную Python.

  1. Объявите переменную в Jupyter:

    name = "Ivan"

  2. Выполняя следующий код в ячейке Jupyter, обратите внимание, что для интерпретации SQL-запросов как Jinja2-шаблонов перед исполнением необходимо указать флаг jinja2:

    %%yq <другие_параметры> --jinja2

SELECT "{{name}}"

    Параметры:

    • --jinja2– включает рендеринг текста запроса с использованием шаблонов Jinja. Для использования требуется установленный пакет Jinja2 (%pip install Jinja2).

Фильтр to_yq

Шаблонизатор Jinja2 является системой шаблонизации общего назначения. При работе со значениями переменных Jinja2 использует стандартное строковое представление типов данных.

Например, задан Python list lst=["Academy", "Physics"], использовать его в Jinja-шаблоне можно так:

%%yq --jinja2
select "Academy" in {{lst}}

В результате исполнения мы получим ошибку Unexpected token '['. Ошибка возникает из-за того, что шаблонизатор Jinja конвертирует переменную lst в строку ["Academy", "Physics"] по правилам Python, без учета специфики SQL-запросов в Yandex Query.

Для указания, что конвертация должна выполняться по правилам Yandex Query, необходимо использовать фильтр to_yq. Тогда тот же запрос в Jinja-синтаксисе будет выглядеть так:

%%yq --jinja2
select "Academy" in {{lst|to_yq}}

Jinja-фильтр to_yq выполняет преобразование данных в синтаксис Yandex Query полностью аналогично встроенным mustache-шаблонам.

Захват результатов выполнения команд

Результат исполнения line magic command может быть захвачен с помощью команды присваивания:

varname = %yq <запрос>

Результат выполнения cell magic command можно захватить, указав имя переменной в начале текста запроса и оператор <<:

%%yq
varname << <запрос>

После этого результат выполнения можно использовать, как обычную переменную Jupyter.

Например, захватим результат выполнения команды в переменные output с помощью cell magic:

output = %yq SELECT 1 as column1

А в данном примере захватим результат выполнения в переменную output2 с помощью line magic:

%%yq
output2 << SELECT 'Two' as column2, 3 as column3

Далее эти переменные можно использовать, как обычные переменные IPython. Например, можно вывести их на печать:

output

По умолчанию результатом выполнения команд %yq и %%yq является Pandas DataFrame с колонками, совпадающими с названиями колонок из SQL-запроса и со строками, содержащими результаты запроса. Конвертацию в Pandas DataFrame можно отключить с помощью аргумента --raw-results.

В примере выше переменная output будет обладать следующей структурой:

column1
0 1

Переменная output2 будет выглядить следующим образом:

column2 column3
0 Two 3

Если запрос не предполагает возврат результатов (например, insert into table select * from another_table), то возвращаемым значением будет None. Если в результате выполнения запроса было возвращено несколько наборов результатов, то они будут представлены в виде списка (list) из отдельных результатов.

Во время выполнения запроса пакет yandex_query_magic выводит дополнительную информацию. Например: идентификатор запроса, время начала и продолжительность выполнения запроса:

jupyter_query_info

Чтобы скрыть отображение прогресса выполнения для ячейки, можно использовать дополнительную команду %%capture.

%%capture
%%yq
<запрос>

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

Предыдущая
AppMetrica: экспорт, постобработка и визуализация данных
Следующая
Работа с данными в Yandex Object Storage