Аналитика в Yandex DataLens за пределами CSV: как реализовать интеграцию с Yandex Query

Yandex Query позволяет выполнять аналитические и потоковые запросы к структурированным и частично структурированным данным, а также работать с текстовыми файлами CSV и TSV почти как с таблицами базы данных. На момент публикации статьи сервис находится в режиме Preview, но уже сейчас вы можете легко использовать его для своих задач в Yandex DataLens.

Как начать работу с Yandex QueryКак начать работу с Yandex Query

Yandex DataLens можно использовать с аналитической СУБД, как мы рассказали в прошлой статье про ClickHouse. Это позволяет распределить обработку данных, что добавляет удобства и производительности. А как быть, если нужно работать с данными в формате CSV? Более того, эти данные не статичны и не подлежат импорту в БД.

Тут нам поможет сервис Yandex Query. С ним удобно выполнять аналитические и потоковые запросы к структурированным и частично структурированным данным. Yandex Query позволяет работать с текстовыми файлами CSV и TSV почти как с таблицами базы данных.

Сами исходные файлы можно размещать в хранилище, например в Yandex Object Storage.

Рассказываем, как реализовать эту интеграцию за три шага (см. картинку ниже).

Шаг 1. Размещение данных в облакеШаг 1. Размещение данных в облаке

Для размещения файла с данными воспользуемся сервисом Object Storage: создадим в нём бакет и новую папку.

Для решения нашей задачи доступ можно оставить ограниченным.

Теперь загрузим в папку файл с данными. В примере, который будет ниже, мы сделаем это вручную. Но если нужно регулярно пополнять и/или обновлять данные, то для автоматизации процесса лучше использовать API Object Storage.

В качестве разделителей полей применяют либо запятые (CSV), либо табуляцию (TSV). Расширение значения не имеет.

ПримерПример

Для примера возьмём данные с названиями стран и населенных пунктов, а также их координатами.

После того как данные разместили в облаке, можно переходить к их обработке.

Шаг 2. Подключение Yandex QueryШаг 2. Подключение Yandex Query

В боковом меню выбираем раздел Платформа данных, затем — Yandex Query.

Первый раздел, который нам нужен для дальнейшей работы, не виден сразу. Находим его и создаём соединение.

Если работать в одном облаке с сервисным аккаунтом, то всё просто. Нужно выбрать Object Storage, созданный ранее бакет и сервисный аккаунт.

Для доступа из DataLens нужно в поле «Видимость» выбрать «Публичная».

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

• Задать путь к данным в формате «/папка/файл», формат файла

• Описать нужные поля

Мы выбрали формат tsv_with_names, так как в примере используются данные, разделенные табами. Расширение файла значения не имеет. Для формата csv_with_names можно будет выбрать разделитель.

Когда вы указываете типы данных в соответствующих полях, учитываются нюансы YQL/YDB, поэтому для строковых значений выбираем UTF8. В будущем, если потребуется, можно создавать дополнительные привязки на те же или другие данные.

В части настроек доступов привязку тоже делаем публичной.

Итог: хранение данных в Object Storage осталось приватным, а соединение и привязка стали публичными.

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

В запросе выбираем все поля, описанные в привязке, а для определения того, что используется привязка, в секции FROM указываем путь «bindings», через точку — имя нужной привязки. Не закрывайте вкладку с Yandex Query: её данные понадобятся на следующем шаге.

Шаг 3. Подключение Yandex DataLens к Yandex QueryШаг 3. Подключение Yandex DataLens к Yandex Query

В DataLens создаём подключение — нужно выбрать облако и сервисный аккаунт, а после установить уровень доступа SQL.

На этапе создания датасета с подключением к Yandex Query вас поджидает сюрприз. Вместо списка объектов открывается окно для запроса. Yandex Query пока не поддерживает представления, а DataLens пока не видит списка привязок и запросов. Надеемся, скоро эта проблема будет решена. А сейчас копируем запрос из вкладки с Yandex Query, которую мы благоразумно оставили открытой.

Отсматриваем данные, меняем в настройках полей датасета тип для поля с координатами, сохраняем и делаем чарт.

Итак, всего за три шага мы визуализировали информацию на основе текстовых данных (CSV/TSV). При этом, как и с базами данных, изменения в файле будут автоматически отражаться в чартах — можно обновлять файл сколько потребуется. Кроме того, вы можете добавлять новые файлы и строить запросы по нескольким файлам.

ДополненияДополнения

Отправленные в Yandex Query запросы, в том числе и те, что приводят к ошибке, отображаются в интерфейсе сервиса. Если что-то идёт не так, можно искать причину в нём.

Если вы планируете добавлять однотипные файлы с данными, например fact202301.csv, fact202302.csv… то для них можно создать общую папку и присоединение с путем к ней /data/fact/*.csv. Это позволит делать выборки по всем файлам.

Можно не создавать привязки, а делать запрос через соединение напрямую к файлу, минуя шаг создания привязки при добавлении новых файлов. В этом случае в секции FROM указываем имя соединения, а через точку — путь к файлу в бакете в формате «папка/файл», формат данных. Описания полей и их типов размещаются в секции WITH.

В нашем примере запрос будет иметь вид:

SELECT *
FROM contest.`data/places.csv`
WITH(
   format=`tsv_with_names`,
   SCHEMA
   (
       Country UTF8,
       Place UTF8,
       GEO UTF8
   )
)

Этот запрос может быть реализован напрямую в датасете DataLens. Для CSV синтаксис будет немного отличаться в части указания формата:

format=`сsv_with_names`,csv_delimiter=`|`,

И, конечно же, таким образом вы можете делать запрос к нескольким файлам, связывая их средствами SQL.

Если СУБД ClickHouse все же есть, то можно обойтись без импорта данных и без Yandex Query. Достаточно сделать простой запрос вида:

SELECT t.*
FROM url('http://site.net:1234/file.csv',CSV,’field1 Int32, field2 String’) AS t

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

О доступе из ClickHouse к файлам в Object Storage читайте в пошаговой инструкции «Настройка доступа к Object Storage из ClickHouse-кластера».