Описание работы с форматом 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)

2. таблица as_geo_regions - если используется справ. населенных пунктов.
3. все кастомизированные таблицы справочников as_cat_cust_ ...
4. все кастомизированные процедуры 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/