Работа с бакетами S3 (Yandex Object Storage)

Перед началом работы с S3 необходимо настроить подключение к хранилищу данных. Для этого существует DDL для настройки таких подключений. Далее рассмотрим SQL синтаксис и управление этими настройками.

Бакеты в S3 бывают двух видов: публичные и приватные. Для подключения к публичному бакету необходимо использовать AUTH_METHOD="NONE", а для подключения к приватному — AUTH_METHOD="AWS". Подробное описание AWS можно найти здесь. AUTH_METHOD="NONE" означает, что аутентификация не требуется. В случае AUTH_METHOD="AWS" необходимо указать несколько дополнительных параметров:

• AWS_ACCESS_KEY_ID_SECRET_NAME — ссылка на имя секрета, в котором хранится AWS_ACCESS_KEY_ID.
• AWS_SECRET_ACCESS_KEY_SECRET_NAME — ссылка на имя секрета, в котором хранится AWS_SECRET_ACCESS_KEY.
• AWS_REGION — регион, из которого будет происходить чтение, например ru-central-1.

Для настройки соединения с публичным бакетом достаточно выполнить следующий SQL-запрос. Запрос создаст внешнее подключение с именем object_storage, которое будет указывать на конкретный S3-бакет с именем bucket.

CREATE EXTERNAL DATA SOURCE object_storage WITH (
  SOURCE_TYPE="ObjectStorage",
  LOCATION="https://object_storage_domain/bucket/",
  AUTH_METHOD="NONE"
);

Для настройки соединения с приватным бакетом необходимо выполнить несколько SQL-запросов. Сначала нужно создать секреты, содержащие AWS_ACCESS_KEY_ID и AWS_SECRET_ACCESS_KEY.

    CREATE OBJECT aws_access_id (TYPE SECRET) WITH (value=`<id>`);
    CREATE OBJECT aws_access_key (TYPE SECRET) WITH (value=`<key>`);

Следующим шагом создаётся внешнее подключение с именем object_storage, которое будет указывать на конкретный S3-бакет с именем bucket, а также использовать AUTH_METHOD="AWS", для которого заполняются параметры AWS_ACCESS_KEY_ID_SECRET_NAME, AWS_SECRET_ACCESS_KEY_SECRET_NAME, AWS_REGION. Значения этих параметров описаны выше.

CREATE EXTERNAL DATA SOURCE object_storage WITH (
  SOURCE_TYPE="ObjectStorage",
  LOCATION="https://object_storage_domain/bucket/",
  AUTH_METHOD="AWS",
  AWS_ACCESS_KEY_ID_SECRET_NAME="aws_access_id",
  AWS_SECRET_ACCESS_KEY_SECRET_NAME="aws_access_key",
  AWS_REGION="ru-central-1"
);

Использование внешнего подключения к S3-бакету

При работе с Yandex Object Storage с помощью внешних источников данных удобно выполнять прототипирование, первоначальную настройку подключений к данным.

Пример запроса для чтения данных:

SELECT
  *
FROM
  object_storage.`*.tsv`
WITH
(
  FORMAT = "tsv_with_names",
  SCHEMA =
  (
    ts Uint32,
    action Utf8
  )
);

Список поддерживаемых форматов и алгоритмов сжатия данных для чтения данных в S3 (Yandex Object Storage), приведен в разделе Форматы данных и алгоритмы сжатия.

Модель данных

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

SELECT
  <expression>
FROM
  <object_storage_connection_name>.`<file_path>`
WITH(
  FORMAT = "<file_format>",
  COMPRESSION = "<compression>",
  SCHEMA = (<schema_definition>),
  <format_settings>)
WHERE
  <filter>;

Где:

• object_storage_connection_name — название внешнего источника данных, ведущего на бакет с S3 (Yandex Object Storage).
• file_path — путь к файлу или файлам внутри бакета. Поддерживаются wildcards *, подробнее в разделе.
• file_format — формат данных в файлах.
• compression — формат сжатия файлов.
• schema_definition — описание схемы хранимых данных в файлах.
• format_settings — опциональные параметры форматирования

Описание схемы данных

Описание схемы данных состоит из набора полей:

• Названия поля.
• Типа поля.
• Признака обязательности данных.

Например, схема данных ниже описывает поле схемы с названием Year типа Int32 и требованием наличия этого поля в данных:

Year Int32 NOT NULL

Если поле данных помечено как обязательное, NOT NULL, но это поле отсутствует в обрабатываемом файле, то работа с таким файлом будет завершена с ошибкой. Если поле помечено как необязательное, NULL, то при отсутствии поля в обрабатываемом файле ошибки не возникнет, но поле примет значение NULL. Ключевое слово NULL в необязательных полях является опциональным.

Автоматический вывод схемы данных

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

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

SELECT
  <expression>
FROM
  <object_storage_connection_name>.`<file_path>`
WITH(
  FORMAT = "<file_format>",
  COMPRESSION = "<compression>",
  WITH_INFER = "true")
WHERE
  <filter>;

Где:

• object_storage_connection_name — название внешнего источника данных, ведущего на S3 бакет (Yandex Object Storage).
• file_path — путь к файлу или файлам внутри бакета. Поддерживаются wildcards *, подробнее ниже.
• file_format — формат данных в файлах. Поддерживаются все форматы, кроме raw и json_as_string.
• compression — формат сжатия файлов.

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

Форматы путей к данным задаваемых в параметре file_path

В YDB поддерживаются следующие пути к данным:

Параметры форматирования

В YDB поддерживаются следующие параметры форматирования:

Параметр file_pattern можно использовать только в том случае, если file_path – путь к каталогу. В строках форматирования можно использовать любые шаблонные переменные, поддерживаемые функцией strftime(C99). В YDB поддерживаются следующие форматы типов Datetime и Timestamp:

Пример

Пример запроса для чтения данных из S3 (Yandex Object Storage):

SELECT
  *
FROM
  connection.`folder/`
WITH(
  FORMAT = "csv_with_names",
  COMPRESSION="gzip"
  SCHEMA =
  (
    Id Int32 NOT NULL,
    UserId Int32 NOT NULL,
    TripDate Date NOT NULL,
    TripDistance Double NOT NULL,
    UserComment Utf8
  ),
  FILE_PATTERN="*.csv.gz",
  `DATA.DATE.FORMAT`="%Y-%m-%d",
  CSV_DELIMITER='/'
);

Где:

• connection — название внешнего источника данных, ведущего на бакет S3 (Yandex Object Storage).
• folder/ — путь к папке с данными в бакете S3 (Yandex Object Storage).
• SCHEMA — описание схемы данных в файле.
• *.csv.gz — шаблон имени файлов с данными.
• %Y-%m-%d — формат записи данных типа Date в S3.