Интеграция Яндекс YML. Импорт-экспорт каталога товаров в YML

 

Описание работы с форматом YML в БД Falcon

UPD: 2020-06-30

Оглавление

Описание работы с форматом YML в БД Falcon.

Общие сведения.

Краткое описание формата файла YML.

Описание элементов раздела shop.

Описание элементов раздела offers.

Проверка файла YML.

Пример файла YML.

Выгрузка в файл.

Процедура [as_cat_getYMLFilterValue].

Процедура [as_cat_products_getYMLHeaderData].

Процедура [as_cat_products_getYML_File].

Парсинг файла YML и обновление данных в БД.

Процедура [as_cat_parsingYML].

Процедура [as_cat_parsingYMLbyFile].

Процедура [as_cat_parsingYML_STR].

Заключение.

Общие сведения

Данный документ описывает работы с данными в формате YML.

YML (Yandex Market Language) —стандарт, разработанный Яндексом для принятия и размещения информации в базе данных Яндекс.Маркет. При использовании файла YML формата регулярное обновление каталога на Яндекс.Маркет будет происходить автоматически и отражать все актуальные изменения (наличие, цена, появление новых товаров).

                Единый формат позволяет быстро и эффективно обрабатывать информацию и размещать ее в каталоге Яндекс.Маркет.

                Стандарт YML основан на XML (Extensible Markup Language) —  (XML DTD).

Краткое описание формата файла YML

Ниже приведено краткое описание элементов присутствующих в формируемом файле YML. Подробное описание доступно по следующим ссылкам:

 https://yandex.ru/support/partnermarket/export/yml.html#yml-format

https://yandex.ru/support/partnermarket/elements/shop.html

https://yandex.ru/support/partnermarket/offers.html

https://yandex.ru/support/partnermarket/elements/param.html

 

Файл YML состоит из общей информации о магазине и списка товарных предложений - офферов.

 

Основные элементы YML

Элемент

Описание / Пример

xml header

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

Допустимые кодировки YML-файла: UTF-8, windows-1251.

Пример :

yml_catalog

Любой XML-документ может содержать только один корневой элемент. Формат YML в качестве корневого использует элемент . Атрибут date элемента должен соответствовать дате и времени генерации YML-файла на стороне магазина. Дата должна иметь формат YYYY-MM-DD hh:mm.

shop

Элемент содержит описание магазина (shop), его предложений (offers) и акций (promos, gifts)*

 

*! Внимание.: В нашем примере разделы  promos и gifts не формируются и не обрабатываются при парсинге файла.

 

 

Описание элементов раздела shop.

Описание элементов, входящих в раздел shop

Элемент

Описание / Пример

Обязательность

name

Короткое название магазина.

<name>BestSellername>

Обязательно

company

Полное наименование компании, владеющей магазином

<company>Tne Best inc.company>

Обязательно

url

URL главной страницы магазина. Максимальная длина ссылки — 512 символов.

<url>http://best.seller.ruurl>

Обязательно

currencies

Элемент currencies задает список курсов валют магазина. Каждая из валют описывается отдельным элементом currency.

<currencies>

<currency id="RUR" rate="1"/>

currencies>

 

Обязательно

categories

Список категорий магазина.

В формате YML в родительском элементе categories содержится список категорий магазина. Каждая категория описывается отдельным элементом category.

<categories>

<category id="1">Книгиcategory>

<category id="2" parentId="1">Детективыcategory>

categories>

Обязательно

 

 

Описание элементов раздела offers.

Описание элементов входящих в раздел  offers

Элемент

Описание / Пример

Обязательность

id

Идентификатор предложения. Макс. длина — 20 символов. Должен быть уникальным для каждого предложения.

В YML является атрибутом для offer.

Обязательно

name

 Полное название предложения.

Обязательно

url

URL страницы товара на сайте магазина.

Максимальная длина ссылки — 512 символов

Обязательно

price

Актуальная цена товара.

Разделитель целой и дробной части — точка

Обязательно

currencyId

 

Валюта, в которой указана цена товара: RUR, USD.

Цена и валюта должны соответствовать друг другу.

Обязательно

categoryId

! Внимание. Используется только в формате YML.

Идентификатор категории товара, присвоенный магазином (целое число, не более 18 знаков).

Обязательно

description

Описание предложения. Длина текста не более 3000 символов (включая знаки препинания).

 

Необязательно

param

 

 

Все важные характеристики товара — цвет, размер, объем, материал, вес, возраст, пол, и т. д.

В YML элемент offer может содержать несколько элементов param (один элемент param — одна характеристика).

белый

 

 

 

Проверка файла YML

Полученные файлы можно проверить с помощью сервиса - Валидатор XML-фидов.

Схема валидации документа – Маркет.

Ссылка  - https://webmaster.yandex.ru/tools/xml-validator/

Результаты проверки должен быть:

XML соответствует схеме XSD.

 

 

Пример файла YML

<!--?xml version="1.0" encoding="UTF-8"?-->

<yml_catalog date="2019-11-01 17:22">

<shop>

<name>BestSeller</name>

<company>Tne Best inc.</company>

<url>Ошибка! Недопустимый объект гиперссылки.&gt;

 <currencies>

<currency id="RUR" rate="1"></currency>

</currencies>

<categories>

<category id="1">Бытовая техника</category>

<category id="10" parentid="1">Мелкая техника для кухни</category> </categories>



 <offers>

<offer id="9012">

<name>Мороженица Brand 3811</name> <url>http://best.seller.ru/product_page.asp?pid=12345</url>

<price>8990</price>

<currencyid>RUR</currencyid>

<categoryid>10</categoryid>

<param name="Цвет">белый

<dimensions>20.1/20.551/22.5</dimensions> </offer>

</offers>

<gifts> <!-- подарки не из прайс‑листа --> </gifts>

<promos> <!-- промоакции --> </promos>

</url></shop>

</yml_catalog>

 

 

Выгрузка в файл

Реализован функционал по выгрузке данных из БД Falcon в файл формата YML.

Информация выгружается из следующих таблиц БД:

  • [as_cat_categories] Категории в раздел <categories>
  • [as_cat_products] Товары в раздел <offers>
  • [as_cat_productAttrs] + [as_cat_filters] Параметры в раздел <param >.

 

Из таблицы [as_cat_categories] выгружаются следующие поля:

[id], [name], [parentID].

Из таблицы [as_cat_products] выгружаются следующие поля:

[id], [name], [price],'RUR' AS currencyId, [url], [desc], categoryID.

Из таблицы [as_cat_filters] выгружаются следующие поля:

[title].

Из таблицы [as_cat_productAttrs] выгружаются следующие поля:

[value],  [intValue], [decimalValue].

 

 

Выгрузка осуществляется следующими процедурами:

  1. [as_cat_getYMLFilterValue]
  2. [as_cat_products_getYMLHeaderData].

 

 

Процедура [as_cat_getYMLFilterValue]

Входные параметры:

  @productID и @filterID.

Результат:

 возвращает строку видабелый.

Вызов:

Вызывается в процедуре [as_cat_products_getYMLHeaderData] в курсоре.

Примеры вызова:

EXECUTE [dbo].[as_cat_getYMLFilterValue] @productID= 10030  , @filterID = 91

 

Процедура [as_cat_products_getYMLHeaderData]

Входные параметры:

Переменная

Тип данных

Значение по умолчанию

Примечание

Обязательные параметры файл YML

@shopName

nvarchar(max)

'BestSeller'

- Короткое название магазина.

@shopCompany

nvarchar(max)

'Tne Best inc.'

- Полное наименование компании, владеющей магазином.

@shopURL

nvarchar(512)

'http://best.seller.ru'

- URL главной страницы магазина

Дополнительные параметры файл YML

@isUploadCategorys

bit

0

1 - передавать структуру категорий из as_cat_categories

@isUploadAttrs

bit

0

1 - выгружать атрибуты товара в секцию param

 

Процедура через дополнительные параметры  @isUploadCategorys и @isUploadAttrs позволяет регулировать содержание файла YML. В т.ч. загружать / не загружать категории из таблицы [as_cat_categories].

И загружать / не загружать атрибуты товара из таблицы [as_cat_productAttrs].

 

Результат:

                Выводит данные в формате файла YML.

Вызов:

                Вручную, по необходимости.

Примеры вызова:

EXECUTE [dbo].[as_cat_products_getYMLHeaderData] 

  @shopName='TEST_Seller'

, @shopCompany= 'The TEST inc.'

, @shopURL = 'http://TEST_Seller.seller.ru'

, @isUploadCategorys =1

, @isUploadAttrs =1

 

В результате мы имеем инструмент выводящий отформатированные данные полностью соответствующие спецификации файла YML и проходящие валидацию.

 

Недостатком проц. [as_cat_products_getYMLHeaderData] является то, что полученный текст файла необходимо вручную сохранять в файл на диске.

! Примечание: для корректной работы формат файла должен быть UTF-8 !

Процедура [as_cat_products_getYML_File]

Процедура [as_cat_products_getYML_File] запускает вышеуказанную проц. [as_cat_products_getYMLHeaderData] и создает на диске файл с результатами выполнения этой процедуры.

По сути это оболочка вызова процедуры и создания файла. Других действий не производится.

Технически в процедуре запускаются системная хранимая процедура master..xp_cmdshell  и SQLCmd —   утилита, входящая в состав SQL Server, предназначеная для выполнения скриптов Transact-SQL из командной строки.

 

Входные параметры:

Переменная

Тип данных

Значение по умолчанию

Примечание

Обязательные параметры

@outputFilePath

NVARCHAR(1000)

нет

 путь к файлу результата (без указания имени файла)

 

Остальные обязательные параметры ,@shopName ,@shopCompany ,@shopURL ,@isUploadCategorys  ,@isUploadAttrs – приведены в таблице выше.

Формат имени выходного файла формируется по шаблону  out_YYYYMMDDTHH_mm.xml,

Где out_ - постоянная часть,  .xml - расширение.  YYYYMMDDTHH_mm  - шаблон даты и времени формирования.

Пример имени файла: out_20200413T14_42.xml

 

Права доступа:

возможно, будет нужна однократная настройка сервера, для запуска xp_cmdshell, описание по ссылке:

Параметр конфигурации сервера «xp_cmdshell»

https://docs.microsoft.com/ru-ru/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option?view=sql-server-ver15

Результат:

                Создает по указанному пути @outputFilePath  файл с именем в формате out_YYYYMMDDTHH_mm.xml.

                Возвращаемое значение: 0 Success (успешное завершение) или 1 Failure (неуспешное завершение).

Вызов:

                Вручную, по необходимости.

Примеры вызова:

EXEC [dbo].[as_cat_products_getYML_File] @outputFilePath ='E:\' , @isUploadCategorys = 1 , @isUploadAttrs = 0

! Примечание: для корректной работы формат файла должен быть UTF-8 !

! Примечание: Кодировки файла результата не проверятся. После создания файла необходимо проверить кодировку и, если необходимо пере сохранить файл в нужной UTF-8.

(Пересохранить файл можно в блокноте.)

Ссылки:

Служебная программа sqlcmd

https://docs.microsoft.com/ru-ru/sql/tools/sqlcmd-utility?view=sql-server-ver15

 

Парсинг файла YML и обновление данных в БД.

Также существуют процедуры парсинга (разбора) файла YML и внесения изменений / новых дынных в БД Falcon.

Процедура [as_cat_parsingYML]

Процедура  анализирует содержимое полученной переменной типа XML и в зависимости от входящих параметров может выводить диагностическую информацию по полученным данными или вносить изменения в таблицы БД.

 

Входные параметры:

Переменная

Тип данных

Значение по умолчанию

Примечание

Обязательные параметры

@XML_input

xml

нет

входной параметр - переменная типа XML

@isUpdateDB

bit

0

делать ли изменения в базе или диагностика файла

0 - читаем файл только для диагностики

1 - вносим изменения в БД из файла

@isUpdateAttrs

bit

1

чтения / обновление атрибутов as_cat_productAttrs

1 -работает с атрибутами

0 - пропустить работу с атрибутами

Необязательные параметры

@param_username

nvarchar(256)

NULL Имя пользователя, если не указан, берем серверного CURRENT_USER

!Примечание: параметр @isUpdateAttrs (обработка атрибутов) работает при условии если @isUpdateDB=1.

 

Алгоритм и условия работы.

Процедура читает данные из вх. Переменной типа XML. Предполагается, что в данных содержится информация из файл YML.

Информация может содержать следующие данные:

  • Категории         раздел <categories>     табл.БД [as_cat_categories]
  • Товары         раздел <offers>         табл.БД [as_cat_products]
  • Параметры товаров  раздел <param >       табл.БД [as_cat_productAttrs]

 

Т.к. мы синхронизируем разные БД, то идентификатор  (id) одинаковых товаров или категорий в разных БД может быть разным. Для получения связи в идентификаторами внешних систем в таблицы [as_cat_categories] и [as_cat_products] добавлено поле outerID nvarchar(64).

 

Если в БД существуют таблицы as_cat_YMLImportLog и as_cat_YMLImportLogData, то в них будут записываться входные данные и результат работы процедуры.

В дальнейшем из этих таблиц будет формироваться журнал результатов (например, при импорте из файла).

Возможные ошибки (заносится в табл. as_cat_YMLImportLog):

  • 'Ошибка! НЕ ОПРЕДЕЛЕНА СТРОКА yml_catalog в файле!'
  • 'Ошибка! В БД нет поля для сравнения outerID в табл.as_cat_categories!'
  • 'Ошибка! В БД нет поля для сравнения outerID в табл.as_cat_products!'

Перед запуском процедуры необходимо проверить наличие и заполнение поля outerID!

 

При чтении данных, читается идентификатор записи (в разделе <categories> или <offers>).

Полученный идентификатор сверяется со значениями в поле outerID.

Если в поле outerID есть запись с таким же идентификатором, то происходит обновление записи.

Если в поле outerID нет искомого идентификатора, то происходит вставка новой строки данных.

 

Также для корректного обновления или вставки новых данных атрибутов (таблица as_cat_productAttrs) необходимо, чтобы были одинаковыми (в БД приемнике данных  и БД источнике ) следующие  объекты БД:

  1. таблица as_cat_filters - список всех атрибутов. Если в данной таблице не будет поля атрибута, прочитанного из файла, то будет выведено сообщение следующего содержания:

ОШИБКА! НЕ ОПРЕДЕЛЕН справочник (as_cat_filters) для атрибута : Производитель для offerID: 1.

 (заносится в табл. as_cat_YMLImportLogData)

  1. таблица as_geo_regions - если используется справ. населенных пунктов.
  2. все кастомизированные таблицы справочников as_cat_cust_ ...
  3. все кастомизированные процедуры cat_filter_get_ ...

 

Возможны обновление следующих таблиц БД:

  • [as_cat_categories] Данные из раздела <categories>
  • [as_cat_products] Данные из раздела <offers>
  • [as_cat_productAttrs] Данные из раздела <param >

 

Результат:

                парсинг вх.данны и обновление таблиц БД

                процедура в результате своей работы выводит строку с общими параметрами работы и подробный журнал выполненных операций. Примеры результатов вывода буду рассмотрены ниже.

 

Режимы работы:

Процедура может быть запущена в 2-х режимах:

1 – выводит только диагностическую информацию по полученным данными

2 – чтение по полученных данных и вносит изменения в таблицы БД.

 

Пример вызова в режиме диагностики:

В этом режиме достаточно передать только данные XML.

DECLARE @xml xml

SELECT @xml = CAST(x.data as XML)

FROM OPENROWSET(BULK 'E:\YML_realty_ALL.XML', SINGLE_BLOB) AS x (data)

EXECUTE [dbo].[as_cat_parsingYML] @xml

Пример результата в режиме диагностики:

 Диагностика:

yml_catalog: Есть;

shop: Есть;

categories: Есть;

offers: Есть;

offerONE: Есть;

promos: Нет;

gifts: Нет;

param: Есть;

 

АТРИБУТ - № категории: 1;

АТРИБУТ - № под-категории: 1;

АТРИБУТ - № offer (Продукт): 1;

 

Категорий найдено в файле:21

Обновление Категорий в БД: нет;

 

Продуктов найдено в файле:7

Обновление Продуктов в БД: нет;

 

журнал выполненных операций – пустой, т.к. никаких операций с БД не проводилось.

Пример вызова в режиме изменения БД:

DECLARE @xml xml

SELECT @xml = CAST(x.data as XML)
FROM OPENROWSET(BULK 'E:\out_20200417T19_51_ATTRIB_1_UTF-8.xml', SINGLE_BLOB) AS x (data) 

EXECUTE [dbo].[as_cat_parsingYML] @xml , @isUpdateDB = 1, @isUpdateAttrs = 1

 

Пример результата вызова в режиме изменения БД

Диагностика:

yml_catalog: Есть;

shop: Есть;

categories: Есть;

offers: Есть;

offerONE: Есть;

promos: Нет;

gifts: Нет;

param: Есть;

АТРИБУТ - № категории: 1;

АТРИБУТ - № под-категории: 1;

АТРИБУТ - № offer (Продукт): 1;

Категорий найдено в файле:21

Обновление Категорий в БД: Да;

Добавлено  Категорий: 0

Обновлено Категорий: 21

Продуктов найдено в файле:7

Обновление Продуктов в БД: Да;

Добавлено  продуктов: 0

Обновлено продуктов: 7

 

Параметры: Продуктов найдено в файле:7

Параметры: общее кол-во строк:23

Параметры: NULL строк:0

 

Параметры: Добавлено записей: 0

Параметры: Обновлено записей: 23

 

 журнал выполненных операций

operationName

dbTableName

itemID

outerID

notes

UPDATE

as_cat_products

11495

1014

NULL

UPDATE

as_cat_productAttrs

11489

1

filterID 1 Производитель paramText: Nokia

UPDATE

as_cat_productAttrs

11489

1

filterID 2 Платформа paramText: Android

UPDATE

as_cat_productAttrs

11489

1

filterID 3 Размер экрана paramText: 5.40

UPDATE

as_cat_productAttrs

11489

1

filterID 4 Мощность, Вт paramText: 225

UPDATE

as_cat_productAttrs

11489

1

filterID 5 Проводной paramText: Да

UPDATE

as_cat_productAttrs

11490

2

filterID 1 Производитель paramText: Nokia

UPDATE

as_cat_productAttrs

11490

2

filterID 2 Платформа paramText: IOS

UPDATE

as_cat_productAttrs

11495

1014

filterID 11 Марка спецтехники paramText: Kubota

Журнал выполненных операций с ошибкой. Пример: Журнал результатов импорта файла - Данные 

#

Ошибка

Текст ошибки

Операция

Таблица БД

Код товара

Внешний код

Прим.

13

Нет

Без ошибки

UPDATE

as_cat_productAttrs

11107

10008

filterID 93 Страна paramText: Россия

14

ОШИБКА!!

ОШИБКА! НЕ ОПРЕДЕЛЕН справочник (as_cat_filters) для атрибута : Адрес2 offerID: 10013 paramText: ул. Бакланова.

ERROR

as_cat_filters

 

10013

 

15

Нет

Без ошибки

UPDATE

as_cat_productAttrs

11109

11094

filterID 16 Тип Операции paramText: Продажа

 

Процедура [as_cat_parsingYMLbyFile]

Процедура [as_cat_parsingYMLbyFile] дополняет вышеописанную проц. [as_cat_parsingYML].

Т.к. проц. [as_cat_parsingYML] получает как входной параметр переменную типа XML, то нет возможности прочитать данные напрямую из файл.

 

Процедура [as_cat_parsingYMLbyFile] читает данные из указанного файла и передает для дальнейшего парсинга в проц. [as_cat_parsingYML].

 

Входные параметры:

Переменная

Тип данных

Значение по умолчанию

Примечание

@XMLFilePath

NVARCHAR(1000)

нет

 путь к файлу

@isUpdateDB

bit

0

делать ли изменения в базе или диагностика файла

0 - читаем файл только для диагностики

1 - вносим изменения в БД из файла

@isUpdateAttrs

bit

1

чтения / обновление атрибутов as_cat_productAttrs

1 -работает с атрибутами

0 - пропустить работу с атрибутами

! Внимание: для запуска необходимы дополнительные права доступа на операцию BULK!

Для использования параметра BULK требуется разрешение ADMINISTER BULK OPERATIONS или ADMINISTER DATABASE BULK OPERATIONS.

Результат:

                Результат полностью аналогичен результату работы проц. [as_cat_parsingYML]..

Вызов:

                Вручную, по необходимости.

Примеры вызова:

EXECUTE [dbo].[as_cat_parsingYMLbyFile] @XMLFilePath = 'E:\out_20200417T19_51_ATTRIB_1_UTF-8.xml' ,

                                        @isUpdateDB_input = 1, @isUpdateAttrs_input = 1

Процедура [as_cat_parsingYML_STR]

Процедура [as_cat_parsingYML_STR] отличается от предыдущей проц. [as_cat_parsingYML] типом данных входного параметра.

Процедура [as_cat_parsingYML_STR] на вход получает данные в виде строки.

Преобразует строку в XML и производит разбор полученных данных так же, как и проц. [as_cat_parsingYML].

 

! Примечание: для запуска дополнительных права доступа не требуется.

 

Обязательные требования к входной строке.

  1. Тип данных должен быть Nvarchar(max)
  2. Строка в формате Unicode должна начинаться с N'
  3. 1-я строка - декларация XML - кодировка должна быть заменена с "UTF-8" на "UTF-16" (эта операция производиться внутри процедуры).

 

Входные параметры:

                 @xml_STR Nvarchar(max) – переменная типа Nvarchar(max)

Результат:

                парсинг вх.данны и обновление таблиц БД

                вывод результата работы в текстовом виде

Вызов:

 Вручную, по необходимости.

Примеры вызова:

DECLARE @xml_str NVarchar(max)

SET @xml_str = N'
EXECUTE [dbo].[as_cat_parsingYML_STR] @xml_str

Пример результата:

Получена строка, символов: 188693

смена кодировки в 1-й строке: "UTF-8" == > "UTF-16":

конвертация строки в xml

yml_catalog: Есть; shop: Есть; categories: Есть; offers: Есть; offerONE: Есть; promos: Нет; gifts: Нет

АТРИБУТ - № категории: 1; АТРИБУТ - № под-категории: 0; АТРИБУТ - № offer (Продукт): 1

Обработка Категорий: Начало

Категорий найдено в файле:6

Добавлено  Категорий: 6

 ID: 2000 2001 2002 2003 2004 2005

Обновлено Категорий: 0

 ID:

Обработка Категорий: Конец

Обработка продуктов: Начало

Продуктов найдено в файле:131

Добавлено  продуктов: 131

 ID: 10000 10001 10002 10003 10004 10005 10006 10007 10008 10009 10010 10011 …

Обновлено продуктов: 0

 ID:

Обработка продуктов: Конец

Заключение

В данном документе описан полный цикл работы с форматом YML в БД Falcon.

Дополнительно

https://habr.com/ru/post/216985/

https://webmaster.yandex.ru/tools/xml-validator/

 

Примечание