Шаблон реализации регулярной передачи данных в S3
Глоссарий
| Термин | Определение |
| Сущность | это любой однозначно идентифицируемый конкретный или абстрактный объект, включая события и связи между объектами, информация о котором хранится и обрабатывается в базе данных (БД). Например – позиции чеков, контакты. |
| Объектное хранилище S3 | облачный сервис, позволяющий хранить файлы любого типа и объема |
| Бакет S3 | сущность для организации хранения в хранилище |
| СУБД Manzana | система управления аналитической базой данных Manzana Clickhouse |
Цель
Реализация передачи данных в бакет S3 необходима в целях обеспечения возможности построения аналитики по данным программы Лояльности на стороне Заказчика.
Обзор и обоснование
- В рамках проекта возможна настройка S3 для предоставления возможности скачивания данных из аналитического модуля Manzana CDP в формате выгрузок с помощью S3.
- Забор данных и дальнейшая их обработка реализуется на стороне Заказчика.
В связи с указанным обоснованием необходимо реализовать процесс передачи данных посредством S3 ежедневно по заданному расписанию.
Бизнес-процессы и требования
Реализация передачи данных включает:
- Создание бакета S3.
Бакет S3 должен иметь настройку на хранение файлов в течение 7 дней, а также 2 учётные записи: на запись файлов для Manzana, на чтение и копирование файлов для Заказчика.
- Написание запросов на выгрузку по примерам из Приложений 1-9.
В качестве первой загрузки требуется сформировать выгрузку по всем историческим данным всех сущностей. При необходимости, на усмотрение разработчика, запросы могут быть пересмотрены с целью оптимизации. Выходные данные остаются неизменными.
- Настройка расписания выгрузки данных.
Выгрузка перечисленных данных осуществляется ежедневно в 5:00 МСК.
- Тестирование полноты выгруженных файлов.
Включает однократную сверку соответствия данных выгруженных файлов с данными в аналитической базе данных.
При инкрементальной выгрузке выгружается только накопленный дневной инкремент, но выгрузка происходит каждый раз в новый файл, в имени которого стоит пометка даты и времени выгрузки.
То есть формат имени инкрементального файла выглядит так:
«имя сущности_дата_время.csv.gz»
Дальнейшие действия по интеграции для забора данных осуществляются самостоятельно сотрудниками Заказчика.
Технологически мы разделяем выгрузку на два вида:
1. Полная, для сущностей:
- Contacts (контакты)
- Cards (карты)
- Shops (магазины)
- Rules (правила)
- Articles (товары)
- Campaigns (кампании)
При полной выгрузке выгружаются все данные имеющиеся в справочнике в файл на s3.
2. Инкрементальная, для сущностей:
- Chequeitems (позиции чеков)
- Bonuses (бонусы)
- Coupons (купоны)
Список сущностей и состав файлов
В этом разделе приводится описание структуры данных содержащихся в файлах, которые будут выгружены в S3. Ниже перечислен список таблиц, передаваемых в S3:
- Контакты
- Карты
- Магазины
- Кампании
- Правила списания и начисления бонусов
- Товары
- Позиции чеков
- Бонусы начисления или списания
- Номерные купоны
Файл contacts.csv.gz
Таблица отображает структуру файла и описание данных по контакту, подлежащих полной выгрузке в S3.
| Поле | Описание |
| contact_id | идентификатор контакта |
| registration_dt | дата/время регистрации контакта в ПЛ |
| instance_id | идентификатор инстанса - системное поле |
| level_id | идентификатор текущего уровня контакта |
| organization_id | идентификатор организации контакта |
| bd_correct | признак корректности указанного дня рождения контакта |
| y_bd | год рождения контакта |
| md_bd | месяц-день рождения контакта |
| birth_date | timestamp даты рождения контакта |
| gender_rus | пол контакта |
| number_of_child | количество детей у контакта |
| email1 | |
| could_check_email | признак возможности отправки коммуникации контакту по email (0=невозможно, 1=возможно) |
| mobile_phone | мобильный телефон |
| could_check_mobile_phone | признак возможности отправки коммуникации контакту по мобильному телефону, учитывая корректность указания мобильного телефона (0=невозможно, 1=возможно) |
| could_check_mobile_phone_not_mask | признак возможности отправки коммуникации контакту по мобильному телефону (0=невозможно, 1=возможно) |
| contact_crm_guid | guid контакта |
| sum_disc | сумма со скидкой - одноименное поле в loyalty.contact_cur |
| max_sale_date | дата последней покупки - без учета типа операции покупки |
| cheque_count | количество чеков - одноименное поле в loyalty.contact_cur |
| avg_cheque | средний чек со скидкой контакта в коп. - одноименное поле в loyalty.contact_cur |
| avg_cheque_without_disc | средний чек контакта в коп. - одноименное поле в loyalty.contact_cur |
| bonus_value | количество бонусов контакта - одноименное поле в loyalty.contact_cur |
| status_value | количество статусных бонусов контакта - одноименное поле в loyalty.contact_cur |
| active_bonus_value | количество активных бонусов контакта - одноименное поле в loyalty.contact_cur |
| active_status_value | количество активных статусных бонусов контакта - одноименное поле в loyalty.contact_cur |
| first_name | имя контакта |
| last_name | фамилия контакта |
| middle_name | отчество контакта |
| full_name | полное имя контакта |
| region_id | идентификатор региона регистрации контакта |
| first_shop_d | дата первой покупки контакта |
| last_shop_d | дата последней покупки контакта |
| first_shop_id | идентификатор первого магазина покупки |
| last_shop_id | идентификатор последнего магазина покупки |
| freq_shop_id | идентификатор магазина частой покупки |
| contact_source_id | идентификатор источника информации о контакте |
| contact_source | название источника информации о контакте |
| ea | РА контакта. MAP(key, value) |
| push_notification | возможность отправки push уведомлений |
| orgunit_id | идентификатор магазина регистрации |
| pref_orgunit_id | идентификатор магазина предпочитаемых покупок |
| freq_orgunit_id | идентификатор магазина частых покупок |
| is_email_verified | признак верифицированного email |
| state_contact | статус контакта |
| status_code | источник анкеты контакта |
| is_wallet | наличие карты в Wallet |
| email_hash | хэш email контакта |
| active_contact | признак активности контакта |
| defaultcard | карта по умолчанию |
| agreetoterms_id | идентификатор согласия на обработку персональных данных |
| agreetoterms | согласие на обработку персональных данных |
| has_anket_scan_id | идентификатор наличия скана анкеты |
| has_anket_scan | наличие скана анкеты |
| mobileapp | наличие мобильного приложения |
| allowereciept | согласие на электронный чек |
| is_del | признак удаления контакта |
| validity_id | идентификатор валидности анкеты контакта |
| validity | валидность анкеты контакта |
| registration_d | Дата регистрации контакта в ПЛ |
| registration_date | Дата регистрации контакта в ПЛ (Тип данных Int64) |
| years_old | Количество полных лет |
| days_to_bd | Количество дней до дня рождения. Отрицательное значение - количество дней после дня рождения |
| days_to_future_bd | Количество дней до ближайшего дня рождения |
| contact_source_id | ID источника информации о контакте |
| contact_source | Название источника информации о контакте |
| wallet_bind_date | Дата привязки карты в приложении Wallet |
| wallet_unbind_date | Дата отвязки карты из приложения Wallet |
| contact_crmguid | GUID контакта |
| allownotification | Признак разрешения на уведомления |
| unsubhash | Хэш ссылки на отписку |
| last_card | Последняя привязанная карта |
| dt_load | Дата/время последней обработки записи в DWH |
| contact_type | Тип контакта |
| city_name | Название города контакта |
| mobileverified | Признак верификации телефона |
| timezone | Часовой пояс |
| editable_level | Признак изменения уровня клиента в ручную |
| phone_hash | Хеш телефона |
| cards_count | Количество карт |
| donotphone | Разрешение на коммуникацию по телефону |
| participant_status | Статус участия |
| check_valid_email | Проверка валидации e-mail |
| created_on | Дата создания записи контакта |
| preferred_hour | Предпочитаемые часы целевого действия |
| modified_on | Дата изменения контакта |
| owner_id | Ответственный за изменения |
| subject_id | субъект правоотношений в ML |
Файл cards.csv.gz
Таблица отображает структуру файла и описание данных по картам, подлежащих полной выгрузке в S3.
| Поле | Описание |
| card_id | идентификатор бонусной карты |
| card_name | номер карты |
| card_type_id | идентификатор типа карты |
| card_type | тип карты |
| card_status_id | идентификатор статуса карты |
| card_status | статус карты |
| card_state_id | идентификатор состояния карты |
| card_state | состояние карты |
| master_account_id | идентификатор мастер-аккаунта |
| is_has_masteraccount | признак наличия мастер-аккаунта |
| ea | расширенные атрибуты карты. Массив кортежей (ключ, значение) |
| last_transaction_date | дата последней транзакции по карте |
| contact_id | ID контакта |
| activation_date | Дата активации карты |
| status_date | Дата смены статуса |
| card_level | ID уровня карты |
| bonus_type_id | ID вида карты |
| bonus_type | Вид карты |
| card_start_date | Дата выпуска карты |
| dt_load | Дата/время последней обработки записи в DWH |
| is_del | Признак удаленной карты |
| card_end_date | Дата закрытия карты |
| first_shop_d | Дата первой покупки |
| last_shop_d | Дата последней покупки |
| first_shop_id | ID магазина первой покупки контакта |
| last_shop_id | ID магазина последней покупки контакта |
| secondary_id | NFC номер карты |
| emission_task_id | ИД задания на выпуск карты |
| card_registration_date | Дата регистрации карты |
Файл shops.csv.gz
Таблица отображает структуру файла и описание данных по магазину, подлежащих полной выгрузке в S3.
| Поле | Описание |
| orgunit_id | идентификатор магазина регистрации контакта |
| name | название магазина |
| ea | РА магазина |
| date_open | дата открытия |
| city | идентификатор города |
| region | идентификатор региона |
| region_name | название региона |
| characteristics | массив характеристик магазина |
| external_id | внешний идентификатор товара |
| address | адрес магазина |
| opening_time | Время открытия магазина |
| closing_time | Время открытия магазина |
Файл campaigns.csv.gz
Таблица отображает структуру файла и описание данных по кампании, подлежащих полной выгрузке в S3.
| Поле | Описание |
| campaign_id | идентификатор кампании |
| campaign_name | название кампании |
| actual_start | дата начала кампании |
| actual_end | дата окончания кампании |
| external_id | расширеный аттрибут кампании |
| is_active | признак активности кампании |
Файл rules.csv.gz
Таблица отображает структуру файла и описание данных по правилам, для дальнейшей полной выгрузке в S3.
| Поле | Описание |
| rule_id | идентификатор правила |
| rule_name | название правила |
| campaign_id | ID компании |
| campaign_name | Название кампании |
| date_from | Дата начала акции |
| date_to, | Дата окончания акции |
| owner_id | ID владельца |
| is_active | Статус правила |
| bonus_type | Тип начисляемых бонусов |
| use_commodity_campaign | - |
| use_personal_campaign | Статус применения персонального предложения |
| use_certificate | Статус применения сертификата |
| use_articleset | Статус применения списка товаров |
| external_id | Внешний идентификатор правила |
Файл articles.csv.gz
Таблица отображает структуру файла и описание данных по товару, подлежащих полной выгрузке в S3.
| Поле | Описание |
| id | идентификатор товара |
| article_name | название товара |
| extgroup_name | группа товара |
| subgroup_name | подгруппа товара |
| subcategory_name | подкатегория товара |
| category_name | категория товара |
| brand_name | бренд товара |
| ch1 | характеристика товара 1 |
| ch2 | характеристика товара 2 |
| ch3 | характеристика товара 3 |
| ch4 | характеристика товара 4 |
| external_id | внешний идентификатор товара |
| extgroup_pid | ХЭШ группы товара |
| subcategory_pid | ХЭШ подгруппы товара |
| category_pid | ХЭШ категории товара |
| extgroup_external_id | Внешний ид группы |
| subgroup_external_id | Внешний ид подгруппы |
| subcategory_external_id | Внешний ид подкатегории |
| category_external_id | Внешний ид категории |
| string1 | Строковый параметр 1 (параметр относящийся к товару) |
| source_id | Идентификатор не в хэш вариант |
| min_calc_price | Минимальная расчетная цена (параметр относящийся к товару) |
| manufacturer_id | ID Производителя |
| manufacturer_name | Название производителя |
| icon_url | Значек URL |
| guid | GUID |
| source_table | Атрибут таблицы источника данных |
Файл chequeitems_дата_время.csv.gz
Таблица отображает структуру файла и описание данных по чеку, подлежащих инкрементальной выгрузке в S3.
| Поле | Описание |
| ch_number | номер чека |
| dt | дата чека |
| oper_type | тип операции чека. 1=Продажа, 2=Возврат |
| article_id | идентификатор артикула позиции чека |
| price | цена позиции чека в копейках |
| quantity | количество товаров позиции чека |
| summdisc | сумма со скидкой позиции чека в копейках |
| summ | сумма позиции чека в копейках |
| discount | сумма скидки позиции чека в копейках |
| cheque_id | идентификатор чека |
| chequeitem_id | идентификатор позиции чека |
| card_id | идентификатор карты чека |
| coupon_num | массив номеров купона чека |
| ea_ch | РА чека. Массив кортежей (ключ, значение) |
| ea_ci | РА позиции чека. Массив кортежей (ключ, значение) |
| bo_ch | бонус чека. Массив кортежей (сумма бонуса, правило бонуса, кампания бонуса, идентификатор бонуса, признак статусности бонуса, тип бонуса) |
| bo_ci | бонус позиции чека. Массив кортежей (сумма бонуса, правило бонуса, кампания бонуса, идентификатор бонуса, признак статусности бонуса, тип бонуса |
| shop_id | идентификатор магазина |
| pos_id | идентификатор кассового терминала |
| is_del | признак удаления позиции чека |
| contact_id | идентификатор контакта на момент покупки. На момент построения отчета может быть неактуальным |
| oper_type_name | название операции чека |
| position_number | номер позиции чека |
| d | Дата |
| ym | Дата/время |
| campaign_id | Массив ID кампаний чека/позиции чека |
| summ_ch | Общая сумма чека. Только для фильтрации |
| summdisc_ch | Общая сумма позиции чека. Только для фильтрации |
| partition_id | ID партиций |
| dt_load | Дата/время последней обработки позиции чека в DWH |
| d_load | Дата последней обработки позиции чека в DWH |
| start_of_week_from_thursday | Дата начала четверговой недели чека |
| start_of_month | Дата начала месяца чека |
| paid_by_bonus | Оплачено бонусами в рублях |
| mcp | Минимальная розничная цена товара |
| payment_type | Массив из типов и сумм платежей |
| card_type_id | ID типа карты |
| state_contact | Статус контакта - state_contact = 0 условие фильтра, отбирает только активные контакты |
| is_del_contact | Признак удаления контакта |
| freq_shop | Магазин частых покупок |
| agreetoterms | Согласие контакта на обработку персональных данных |
| sms | Мобильный телефон |
| Е-мэйл | |
| sms_send | Разрешение на коммуникацию по SMS |
| bonus_balance | Баланс бонусов |
| max_sale_date | Дата последней покупки |
| region_name | Название региона |
| brand_name | Бренд |
| article_external_id | Внешний идентификатор товара |
Файл bonuses_дата_время.csv.gz
Таблица отображает структуру файла и описание данных по бонусам начисления или списания, подлежащих инкрементальной выгрузке в S3.
| Поле | Описание |
| bonus_id | идентификатор бонуса начисления |
| is_delete | признак удаления бонуса начисления или списания |
| value | сумма в копейках бонуса начисления или списания |
| dt_created | дата/время создания бонуса начисления или списания |
| d_created | дата создания бонуса начисления или списания |
| parent_type_id | parent_type_id бонуса начисления или списания |
| rule_id | правило бонуса начисления или списания |
| campaign_id | кампания бонуса начисления или списания |
| is_status | признак статусности бонуса начисления или списания |
| oper_type | тип чека привязанного к бонусу начисления или списания. 1 = продажа, 2 = возврат |
| chequeitem_id | идентификатор позиции чека бонуса начисления или списания |
| article_id | артикул позиции чека бонуса начисления или списания |
| ea_ci | РА позиции чека бонуса начисления или списания. Массив кортежей (ключ, значение) |
| cheque_id | идентификатор чека бонуса начисления или списания |
| shop_id | идентификатор магазина чека бонуса начисления или списания |
| card_id | идентификатор карты бонуса начисления или списания |
| is_order | признак заказа (холдирования) бонуса начисления или списания |
| source_table | Таблица источник |
| parent_id | ID типа баллов |
| credit_bonus_id | ID бонуса списания |
| dt_start_date | Дата и время начала действия бонусов ( в секундах) системное поле |
| d_start_date | Дата начала действия бонуса системное поле |
| dt_finish_date | Дата и время окончания действия бонусов ( в секундах) системное поле |
| d_finish_date | Дата окончания действия бонуса системное поле |
| Remainder | Активная порция бонусов (кол-во) |
| dt_load | Дата/время последней обработки записи в DWH |
| cheque_summ | Сумма чека |
| cheque_summdiscounted | Сумма чека со скидкой |
| ci_quantity | Количество товаров |
| ci_summ | Сумма позиций в копейках |
| ci_summdiscounted | Сумма позиций со скидкой в копейках |
Файл coupons_дата_время.csv.gz
Таблица отображает структуру файла и описание данных по купонам, подлежащих инкрементальной выгрузке в S3.
| Поле | Описание |
| coupon_id | идентификатор купона |
| cu_pb | дата начала действия купона |
| cu_pe | дата окончания действия купона |
| cu_created_d | дата создания купона |
| is_active | признак активности купона - одноименное поле из лояльности |
| is_on_bonus | признак покупки за бонусы - одноименное поле из лояльности |
| offer_rule_id | идентификатор правила предложения |
| charge_rule_id | идентификатор правила начисления |
| charge_rule_name | название правила начисления |
| emission_task_id | идентификатор задания на выпуска купона |
| emission_task_name | название задания на выпуск купона |
| scheduled_task_id | идентификатор задания по расписанию |
| scheduled_task_name | название задания по расписанию |
| emission_id | идентификатор выпуска купона |
| emission_name | название выпуска купона |
| mask | название маски |
| cu_type_name | название типа купона |
| cu_number | номер купона |
| cu_state | идентификатор статуса купона |
| cu_state_name | название статуса купона |
| cheque_id | идентификатор чека гашения купона |
| createdon_ch_id | идентификатор чека выпуска купона |
| ch_d | дата чека гашения купона |
| ch_number | номер чека гашения купона |
| ch_campaign_id | идентификатор кампании чека гашения |
| ch_summ | сумма чека гашения в копейках |
| ch_summd | сумма чека гашения со скидкой в копейках |
| ch_orgunit_id | идентификатор магазина гашения купона |
| ch_pos_id | идентификатор терминала гашения купона |
| ch_card_id | идентификатор карты лояльности из чека гашения |
| cu_card_id | идентификатор карты лояльности из купона |
| cu_contact_id | идентификатор контакта из купона |
| campaign_name | название кампании |
| cu_is_del | признак удаления купона |
| ch_is_del | признак удаления чека гашения |
| template_name | Название шаблона |
| bonus_paid | Стоимость купона |
| cu_cancellation_dt | Дата и время гашения купона (не из чека) |
| cu_orgunit_id | ID магазина выпуска купона |
| dt_load | дата загрузки строки в витрину (дефолтное значение) |
| bonus_status_type | Тип бонусов (статусные и бонусные) |
| benefit_type | Тип бенефита |
| benefit_value | Значение бенефита |
Интеграция
Для обеспечения выгрузки файлов в S3 стоит установить интеграцию аналитической СУБД Manzana с бакетом S3.
Роли доступа
Для того чтобы иметь возможность вносить изменения в выгрузку данных в S3 для указанных файлов, необходимо иметь одну из указанных ролей доступа:
- Роль доступа Manzana на запись файлов в бакет S3
- Роль доступа [Название Заказчика] на просмотр и копирование файлов из бакета S3
Отчетность
Не предполагается построение дополнительной отчётности
Критерии приемки
Однократная сверка данных со стороны Manzana происходит по принципу подтверждения соответствия количества записей в выгруженном файле за заданный период количеству записей, имеющихся в Manzana СУБД.
Ниже представлена таблица, представляющая список записей и соответствующих им таблиц, по которым происходит проверка на соответствие по количеству.
| Таблица | Количество записей |
| chequeitems | позиций чеков/сумма/сумма со скидкой/количество удалений/количество SKU |
| bonuses | записей бонусов/сумм бонусов/количество удалений |
| coupons | номерных купонов |
| contacts | контактов/количество телефонов/количество email с изменениями за заданный период |
| cards | карт |
| campaigns | кампаний |
| rules | правил |
Ограничения, предположения и допущения
Файлы хранятся в течение 7 суток с момента размещения файла, затем автоматически удаляются. Это производится встроенными средствами S3 управляемыми настройками бакета. При копировании пользователем с учетной записью на просмотр исходный файл остаётся в бакете до окончания срока хранения.
Вложением ниже приведены скрипты запросов. Они являются примерами и могут быть изменены разработчиком Manzana.