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

Федеративные запросы к данным

Статья создана
Обновлена 15 октября 2024 г.

Yandex Query — это интерактивный сервис для бессерверного анализа данных. С его помощью можно обрабатывать информацию из различных хранилищ без необходимости создания выделенного кластера. Поддерживается работа с хранилищами данных Yandex Object Storage, Yandex Managed Service for PostgreSQL, Yandex Managed Service for ClickHouse®.

Данные из этих систем можно обрабатывать как по отдельности, так и в рамках одного общего запроса — такие запросы называются федеративными.

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

  1. Подготовьте инфраструктуру.
  2. Начните работу в Query.
  3. Подключитесь к данным Object Storage.
  4. Подключитесь к данным Managed Service for ClickHouse®.
  5. Подключитесь к данным Managed Service for PostgreSQL.
  6. Выполните федеративный запрос.

Если созданные ресурсы вам больше не нужны, удалите их.

Ноутбук с примерами также доступен на GitHub.

Открыть в DataSphere

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

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

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

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

Подготовьте инфраструктуру

Войдите в консоль управления Yandex Cloud и выберите организацию, в которой вы работаете с DataSphere. На странице Yandex Cloud Billing убедитесь, что у вас подключен платежный аккаунт.

Если у вас есть активный платежный аккаунт, на странице облака вы можете создать или выбрать каталог, в котором будет работать ваша инфраструктура.

Примечание

Если вы работаете с Yandex Cloud через федерацию удостоверений, вам может быть недоступна платежная информация. В этом случае обратитесь к администратору вашей организации в Yandex Cloud.

Создайте каталог

  1. В консоли управления выберите облако и нажмите кнопку Создать каталог.
  2. Введите имя каталога, например data-folder.
  3. Нажмите кнопку Создать.

Создайте сервисный аккаунт для проекта DataSphere

  1. В консоли управления перейдите в каталог data-folder.
  2. В списке сервисов выберите Identity and Access Management.
  3. Нажмите кнопку Создать сервисный аккаунт.
  4. Введите имя сервисного аккаунта, например yq-sa.
  5. Нажмите Добавить роль и назначьте сервисному аккаунту роли:
    • datasphere.community-project.editor — для запуска вычислений DataSphere.
    • yq.editor — для отправки запросов Query.
    • storage.viewer — для просмотра содержимого бакета и объектов Object Storage.
    • managed-clickhouse.viewer — для просмотра содержимого кластера Managed Service for ClickHouse®.
    • managed-postgresql.viewer — для просмотра содержимого кластера Managed Service for PostgreSQL.
  6. Нажмите кнопку Создать.

Добавьте сервисный аккаунт в проект

Чтобы сервисный аккаунт мог запускать проект DataSphere, добавьте его в список участников проекта.

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

  2. На вкладке Участники нажмите Добавить участника.
  3. Выберите аккаунт yq-sa и нажмите Добавить.

Создайте авторизованный ключ для сервисного аккаунта

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

Примечание

Срок жизни авторизованных ключей не ограничен, но вы всегда можете получить новые авторизованные ключи и повторить процедуру аутентификации, если что-то пошло не так.

  1. В консоли управления перейдите в каталог data-folder.
  2. В списке сервисов выберите Identity and Access Management.
  3. На панели слева выберите Сервисные аккаунты.
  4. В открывшемся списке выберите сервисный аккаунт yq-sa.
  5. Нажмите кнопку Создать новый ключ на верхней панели и выберите пункт Создать авторизованный ключ.
  6. Выберите алгоритм шифрования и нажмите Создать.
  7. Нажмите Скачать файл с ключами.

Создайте секрет

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

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

  2. В блоке Ресурсы проекта нажмите Секрет.
  3. Нажмите Создать.
  4. В поле Имя задайте имя секрета — yq_access_key.
  5. В поле Значение вставьте полное содержимое скачанного файла с авторизированным ключом.
  6. Нажмите Создать.

Создайте ноутбук

Запросы к базе данных Managed Service for PostgreSQL через Query будут отправляться из ноутбука.

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

  2. Нажмите кнопку Открыть проект в JupyterLab и дождитесь окончания загрузки.
  3. На верхней панели нажмите File и выберите New ⟶ Notebook.
  4. Выберите ядро и нажмите Select.

Начните работу в Query

Пакет yandex_query_magic предоставляет magic commands для работы в Jupyter. Установите его для отправки запросов в Query. Скопируйте код в ячейки ноутбука yq-storage.ipynb:

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

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

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

  2. Установите пакет yandex_query_magic:

    %pip install yandex_query_magic

  3. После завершения установки на верхней панели выберите Kernel ⟶ Restart kernel....

  4. Загрузите расширение:

    %load_ext yandex_query_magic

  5. Настройте подключение, указав идентификатор каталога data-folder и название секрета с авторизованным ключом:

    %yq_settings --folder-id <идентификатор_каталога> --env-auth yq_access_key

  6. Выполните тестовый запрос к Query:

    %yq select "Hello, world!"

Подключитесь к данным Object Storage

Для работы с данными Object Storage вам понадобится бакет с таблицей, подключение Query и привязка к данным.

Создайте бакет с данными

Бакет Object Storage будет содержать даты покупок.

  1. В консоли управления перейдите в каталог data-folder.

  2. В списке сервисов выберите Object Storage.

  3. Справа сверху нажмите кнопку Создать бакет.

  4. В поле Имя укажите имя бакета.

  5. В полях Доступ на чтение объектов, Доступ к списку объектов и Доступ на чтение настроек выберите Ограниченный.

  6. Нажмите кнопку Создать бакет.

  7. Создайте файл visits.csv и поместите в него таблицу с датами покупок:

    date|persons_id|item_id
2024-05-14 12:12:00|1|2
2024-05-15 13:13:00|2|1

  8. Перейдите в созданный бакет и нажмите Загрузить.

  9. В появившемся окне выберите файл visits.csv и нажмите кнопку Открыть.

  10. Нажмите кнопку Загрузить.

Создайте подключение к Object Storage

  1. В консоли управления перейдите в каталог data-folder.

  2. В списке сервисов выберите Yandex Query.

  3. На панели слева выберите Соединения.

  4. Нажмите кнопку Создать.

  5. Введите имя соединения, например storage-connection.

  6. Выберите тип соединения Object Storage и укажите Параметры типа соединения.

  7. В поле Аутентификация бакета выберите Приватный и задайте параметры:

    • Облако и каталог — data-folder.
    • Бакет — выберите созданный бакет.
    • Сервисный аккаунт — yq-sa.

  8. Нажмите кнопку Создать.

Создайте привязку к данным

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

Чтобы создать привязку к данным:

  1. В консоли управления выберите каталог data-folder.

  2. В списке сервисов выберите Yandex Query.

  3. На панели слева выберите Привязки.

  4. Нажмите кнопку Создать.

  5. В блоке Параметры соединения:

    • Тип — выберите Object Storage.
    • Соединение — выберите storage-connection.

  6. В блоке Параметры привязки к данным:

    1. Введите имя привязки, например visits.
    2. В поле Путь укажите путь к таблице в бакете — /visits.csv.

  7. В блоке Настройки формата:

    • Формат — выберите csv_with_names.
    • Разделитель {%secondary%}(необязательно){%secondary%} — |.

  8. В блоке Колонки добавьте три колонки со следующими именами и типами данных:

    • date — DATETIME.
    • person_id — INT32.
    • items_id — INT32.

  9. Чтобы проверить корректность указанных данных, нажмите кнопку Предпросмотр. Внизу должна появиться ваша таблица.

  10. Нажмите кнопку Создать.

Чтобы проверить подключение, получите данные таблицы из ячейки ноутбука:

%yq SELECT * FROM visits LIMIT 100;

Подключитесь к данным Managed Service for ClickHouse®

Для работы с данными Managed Service for ClickHouse® вам понадобится кластер с таблицей и подключение Query.

Создайте кластер Managed Service for ClickHouse®

Для отправки запросов подойдет любой рабочий кластер Managed Service for ClickHouse® со включенной опцией Доступ из Yandex Query.

  1. В консоли управления выберите каталог data-folder.

  2. Выберите сервис Managed Service for ClickHouse.

  3. Нажмите кнопку Создать кластер.

  4. Введите имя кластера в поле Имя кластера, например clickhouse.

  5. В блоке Настройки СУБД:

    • В поле Управление пользователями через SQL выберите из выпадающего списка Включено.
    • Укажите Имя пользователя и Пароль.

  6. В блоке Сервисные настройки:

    • Выберите сервисный аккаунт yq-sa.
    • Включите опции Доступ из Yandex Query и Доступ из консоли управления.

  7. Остальные настройки можно оставить по умолчанию.

  8. Нажмите кнопку Создать кластер.

Создайте таблицу

Таблица Managed Service for ClickHouse® будет содержать названия товаров.

  1. В консоли управления, откройте страницу кластера clickhouse и перейдите на вкладку SQL.

  2. Введите Имя пользователя и Пароль, указанные при создании кластера.

  3. В окно ввода справа последовательно выполните SQL-запросы:

    CREATE TABLE items(id INT, description VARCHAR) ENGINE=MergeTree ORDER BY id;

    INSERT INTO items(id, description) VALUES(1, 'Refrigerator')

    INSERT INTO items(id, description) VALUES(2, 'TV')

  4. Нажмите Выполнить.

Создайте подключение к Managed Service for ClickHouse®

  1. В консоли управления выберите каталог data-folder.

  2. В списке сервисов выберите Yandex Query.

  3. На панели слева выберите Соединения.

  4. Нажмите кнопку Создать.

  5. Введите имя соединения, например clickhouse.

  6. Выберите тип соединения Managed Service for ClickHouse.

  7. В блоке Параметры типа соединения:

    • Кластер — выберите ранее созданный кластер clickhouse.
    • Сервисный аккаунт — выберите сервисный аккаунт yq-sa.
    • Введите Логин и Пароль, указанные при создании кластера.

  8. Нажмите кнопку Создать.

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

%yq SELECT * FROM clickhouse.items

Подключитесь к данным Managed Service for PostgreSQL

Для работы с данными Managed Service for PostgreSQL вам понадобится кластер с таблицей и подключение Query.

Создайте кластер Managed Service for PostgreSQL

Для отправки запросов подойдет любой рабочий кластер Managed Service for PostgreSQL со включенной опцией Доступ из Yandex Query.

  1. В консоли управления выберите каталог data-folder.
  2. Выберите сервис Managed Service for PostgreSQL.
  3. Нажмите кнопку Создать кластер.
  4. Введите имя кластера в поле Имя кластера, например postgresql.
  5. В блоке База данных:
    • Укажите Имя БД, например db1.
    • Укажите Имя пользователя и Пароль.
  6. В блоке Сервисные настройки включите опции Доступ из Yandex Query и Доступ из консоли управления.
  7. Остальные настройки можно оставить по умолчанию.
  8. Нажмите кнопку Создать кластер.

Создайте таблицу

Таблица Managed Service for PostgreSQL будет содержать имена покупателей.

  1. В консоли управления, откройте страницу кластера postgresql и перейдите на вкладку SQL.

  2. Введите Имя пользователя и Пароль, указанные при создании кластера.

  3. В окно ввода справа последовательно выполните SQL-запросы:

    CREATE TABLE persons(person_id INT, name VARCHAR);

    INSERT INTO persons(person_id, name) values(1, 'Ivan')

    INSERT INTO persons(person_id, name) values(2, 'Peter')

Создайте подключение к Managed Service for PostgreSQL

  1. В консоли управления выберите каталог data-folder.

  2. В списке сервисов выберите Yandex Query.

  3. На панели слева выберите Соединения.

  4. Нажмите кнопку Создать.

  5. Введите имя соединения, например postgresql.

  6. Выберите тип соединения Managed Service for PostgreSQL.

  7. В блоке Параметры типа соединения:

    • Кластер — выберите ранее созданный кластер postgresql.
    • Сервисный аккаунт — yq-sa.
    • База данных — db1.
    • Введите Логин и Пароль, указанные при создании кластера.

  8. Нажмите кнопку Создать.

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

%yq SELECT * FROM postgresql.persons

Выполните федеративный запрос

Работа с федеративными источниками данных (межсервисная аналитика) ничем не отличается от работы с обычными источниками данных. К внешним источникам данных, таким как таблицы в БД и данные в Object Storage, можно одновременно обращаться из запроса и выполнять над ними любые допустимые языком YQL операции.

Чтобы совместить и получить данные из всех трех таблиц, выполните федеративный запрос в ячейке ноутбука:

%%yq SELECT 
    v.person_id, 
    date, 
    p.name,
    c.description 
FROM visits AS v 
    INNER JOIN postgresql.persons AS p ON 
        v.person_id = p.person_id 
    INNER JOIN clickhouse.items AS c ON
        v.item_id = c.id

Как удалить созданные ресурсы

Чтобы перестать платить за созданные ресурсы:

ClickHouse® является зарегистрированным товарным знаком ClickHouse, Inc.

Предыдущая
Работа с данными в Yandex Managed Service for PostgreSQL
Следующая
Справочник Terraform