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

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 входят:

• плата за использование вычислительных ресурсов DataSphere;
• плата за данные, считанные 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:

Использование переменных 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:

Словарь также можно преобразовать в таблицу 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:

Список также можно преобразовать в таблицу 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 будет обладать следующей структурой:

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

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

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

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

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

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