Выгрузка данных 1С в ClickHouseDB, PostgreSQL, MySQL, REST, CSV, JSON, XLS, XLSX и Google Sheets

Внимание!

При переходе с версий ниже чем 1.3.1 на более новую:

1. Сохраните конфиг обработки в файл
2. Очистите "ХранилищеОбщихНастроек" с параметрами: КлючОбъекта = Extractor_1C_Clickhouse, КлючНастроек = Обработка, ИмяПользователя = Фоновый
3. Откройте сохраненный конфигурационный файл настроек и переименуйте:
   1. "ДополнительныеКлючиНомерСтроки" => "ТаблицаДополнительныеКлючиНомерСтроки"
   2. "ДополнительныеКлючиИмяКолонки" => "ТаблицаДополнительныеКлючиИмяКолонки"
4. Запустите новую версию обработки и загрузите измененный конфигурационный файл.

Варианты поставки:

1. Версия обработки "Внешняя обработка "Экстрактор данных 1С в ClickHouse" - полная версия со всеми опциями.
2. Версия обработки "Выгрузка данных из 1С только в локальные файлы CSV, JSON, XLS, XLSX" выгружает только в локальные файлы. В базы данных или RESP API не умеет.

Область применения

Чтобы вывести данные из информационных систем в облако так, чтобы оперативные данные всегда был под рукой и в одном месте, т.е. можно было открыть сразу графики на смартфоне.

Чтобы выгружать данные из информационной системы во внешнюю базу данных для дальнейшего анализа и создания BI дэшбордов, например в Yandex DataLens.

Совместимо с обычными формами (для фоновых заданий, требуется доработка вашей конфигурации) в старых конфигурациях и с базовыми версиями 1С.

Кейсы / примеры использования

1. Как выгрузить контрагентов - https://disk.yandex.ru/i/TscWReNmWZjDcA
2. Как выгрузить таблицу с полями, где есть формулы - https://disk.yandex.ru/i/AiBNbeHp-4Ro7g
3. Как выгрузить дебиторскую задолженность - https://disk.yandex.ru/i/FQCc3Sxq6Dr5cg

Технические требования

• Должны быть открыты порты для: ClickHouseDB / PostgreSQL. / MySQL / HTTPS / HTTP / REST.
• Должно быть право использовать и добавлять дополнительные обработки в информационную базу.
• Должен быть создан сервисный пользователь в Google Cloud Console, скопированы его настройки и добавлен в таблицу в качестве редактора для Google Sheets.

Для работы с PostgreSQL требуется:

• Windows 7 / Linux Ubuntu 20. и старше
• Python 3.8 и старше (лучше устанавливать для всех пользователей)
• psycopg2 версии 2.8.4 и старше (pip install psycopg2==2.8.4)
• при нулевой выгрузке попробуйте "Количество в партиции" поставить = 1

Для работы с MySQL требуется:

• Windows 7 / Linux Ubuntu 20. и старше
• Python 3.8 и старше (лучше устанавливать для всех пользователей)
• mysql-connector-python-rf (pip install mysql-connector-python-rf
• ALTER USER 'username'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

     ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Для работы с REST API:

• Любая ваша ссылка на службу (endpoint) работающая по протоколу HTTP для приема сообщений в BODY и BINARY типа запроса POST. В тело запроса можно вложить разные форматы данных:

  • CSV - через запятую с заголовками

  • CSV_WH - тот же CSV, но с заголовками

  • JSON - в виде массива объектов

  • ClickHouseDB - готовый SQL запрос для исполнения / перенаправления / обхода ограничения портов на стороне базы данных / шины данных

  • PostgreSQL - аналогично

  • MySQL - аналогично

Для выгрузки в локальные папки произвольных запросов (CSV, JSON, XLS, XLSX):

• дать все необходимые права и разрешения на создание директорий и папок по пути для пользователя службы 1С или локального пользователя

Для выгрузки в Google Sheets:

• Должен быть создан сервисный пользователь в Google Cloud Console, скопированы его настройки и добавлен в таблицу в качестве редактора для Google Sheets

  • Создать сервисного пользователя в Google Cloud Console
  • Дать права пользователю пользоваться Google Sheets
  • Создать таблицу Google Sheets
  • Добавить сервисного пользователя в таблицу в качестве редактора
  • Скопировать настройки из файла JSON сервисного пользователя в соответствующие поля на форме обработки

• Лимиты использование Google Sheets API для сервисного пользователя изложены по ссылке: https://developers.google.com/workspace/sheets/api/limits?hl=ru

Решение протестировано на конфигурациях:

• 1С:Управление торговлей 11.5.17.122
• 1С:Бухгалтерия предприятия от 3.0.157.32
• 1С:Зарплата и управление персоналом 3.1.30.35
• 1С:Розница 3.0.10.178
• 1C:Комплексная автоматизация 2.5.20.91
• 1С:Управление производственным предприятием, редакция 1.3.122.2 (для фоновых заданий, требуется доработка вашей конфигурации 6 часов)
• 1С-Рарус: Альфа-Авто: Автосалон+Автосервис+Автозапчасти Корп. Редакция 6 (6.1.17.09) / АА6

Совместима и работает с базовыми конфигурациями 1С, проверено на:

• Управление торговлей (базовая), редакция 11 (11.5.23.59)
• Бухгалтерия предприятия (базовая), редакция 3.0 (3.0.179.22) 

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

Код открыт.

Базовые рекомендации по созданию виртуальной машины с базой данных

Создаем свой виртуальный или физических сервер (#VM, #VDS, #VDI и др.)

Для своей VDS вам понадобиться 1-2 ядра, 4Gb RAM, 15-20 GB SSD/HDD.

У которого есть ай пи адрес и порт снаружи.

Адрес

<адрес вашей ВМ, где лежит база данных>

порт по умолчанию

8123

• Подготовить логин и пароль  для пользователя БД.

<имя пользователя>

<пароль пользователя с правами на вставку данных и создание таблиц>

• Настроить свои фаерволлы, кому ходить можно, а кому нельзя

• Зайти на веб морду базы

Подготовка базы данных

• Лучше заранее создать необходимое для вас имя базы

  • Создать базу данных ClickHouseDB или PostgreSQL или MySQL

    • CREATE DATABASE db_1c_test COMMENT 'The 1C Test Metrics database';

• Должен быть пользователь базы, который может делать INSERT в базу:

  • выдать на нее права определенному пользователю

    • GRANT SELECT ON <db_name>.* TO <user_name>; (ClickHouseDB)

    • psql -c "GRANT ALL PRIVILEGES ON DATABASE <db_name> to <user_name>;" (PostgreSQL)

Проверка

• Для ClickHouseDB проверяем http:// <адрес вашей ВМ, где лежит база данных>:8123/play
• Для PostgreSQL проверяем через pgAdmin
• Для MySQL проверяем через phpMyAdmin
• Для обеих баз можно использовать утилиту DBeaver.
• Для REST API на стороне endpoint проверяем тело запроса BODY и BINARY
• Для локальных файлов (CSV, JSON, XLS, XLSX) посмотреть создаются ли папки и файлы выгрузки
• Для Google Sheets проверить добавляются ли строки и новые листы в таблице

Внимание! Демо версия выгружает до 25 строк.

Демонстрация

Оцените возможности решения
Скачайте демо-версию обработки по кнопке ниже

Скачать демо

Что нужно сделать в своей информационной базе 1С

1. Открыть обработку в информационной базе, например тестовую или демо базу
2. Включите использование дополнительных и внешних обработок для своей конфигурации
3. Предоставьте своего пользователю права на открытие внешних обработок
4. Ввести все данные во вкладке параметры
5. Указать имя таблицы, например «Test»
6. Закрыть и открыть, убедиться, что параметры сохраняются
7. Вставить запрос из конструктора, взять срез последних любого регистра
8. Переименовать поле «Период» в «ПериодРегистратор»
9. Выставить свои даты в поля стандартного периода
10. Ограничить запрос датами «Дата1» и «Дата2»
11. Выставить глубину смещения дат назад
12. Отправить свой запрос

Чтобы ваша обработка запускалась в фоново в файловом варианте базы, то вам понадобиться создать еще одного пользователя, например «exchange» и создать задание в планировщике Windows или Cron на запуск программы без интерфейса.

Как проверить что отправилось в БД

Проверяем свой запрос в базе:

• select * from db_1c_test.Test;

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

• SHOW CREATE TABLE db_1c_test.Test;

Чтобы уничтожить таблицу, пишем:

• DROP TABLE db_1c_test.Test;

Чтобы просто очистить:

• TRUNCATE TABLE db_1c_test.Test;

или если хотите с условием что-то удалить:

• DELETE FROM db_1c_test.Test where date_record > '2023-11-24 12:00:00';

Результат работы обработки

• Создать таблицу / лист (Google Sheets) / локальные папки и файлы по имени запроса.
• В фоновом или ручном режиме исполнить запросы.
• Загрузить извлеченные данные в указанный приемник: база данных (ХД), REST API, локальные файлы (CSV, JSON, XLS, XLSX) или Google Sheets.

Какие тонкие настройки можно сделать через конфигуратор обработки

Чтобы более тонко настроить ключи, ресурсы и реквизиты, то открываем обработку через конфигуратор:

• Добавляем свой новый макет компоновки данных
• В своей СКД создаем свой запрос, а в полях, все что ключи не ставим никакой тип данных
• Все, что ресурсы указываем числовые
• Все, что ресурсы, строки или дата, или булево. Все остальное будет конвертироваться в строку или 0.
• В полях не допускается поле "Периода", поэтому переименовываем в «ПериодРегистратор».
• Или не указываем вовсе, поле "Период" подставится автоматически исходя из вашего запроса и параметров.

Можно не лезть в конфигуратор для нескольких выгрузок?

Да, можно. Пользуйтесь добавлением нового запроса во вкладке "Настройки запросов".

Можно и через конфигуратор, там больше настроек и возможностей.

Создание #BI дэшборда в #Yandex #DataLens и что может получится

На картинках представлены примеры из Демо базы 1С Управление торговлей 11.5.17.122.

Представлены картинки из мобильной версии DataLens.

Описание интерфейса

Описание интерфейса обработки

Параметры обработки

В момент установки обработки в вашу конфигурацию будут доступны команды:

• Синхронизация из 1С в ClickHouseDB, PostgreSQL, и MySQL ручное
• Фоновая выгрузка (все настройки)
• Фоновая выгрузка №1 ... №10
• Фоновая по умолчанию

Синхронизация из 1С в ClickHouseDB, PostgreSQL, и MySQL ручное Запустится основная форма обработки.

Фоновая выгрузка (все настройки) Задаются настройки расписания для фонового исполнения всех запросов из таблицы запросов в основной форме.

Фоновая выгрузка №1 ... №10 Параметры фонового исполнения по расписанию определенного номера запроса из таблицы запросов.

Фоновая по умолчанию Используется для запуска примера запроса, который можно переназначить через конфигуратор.

Основная форма обработки

Вкладка "Настройки" На этой вкладке задаются параметры подключения к базе данных (ClickhouseDB, PostgreSQL, MySQL) или к сервису приема сообщение API REST и шин данных (CSV, JSON, SQL), Local - для записи в формах CSV, JSON, XLS, XLSX локальных файлов.

1. Имя базы - имя базы данных.
2. Тип - тип подключения, какая база данных или REST. Подключение к ClickHouseDB осуществляется нативно. Подключение к PostgreSQL и MySQL происходит через Python. Требуется установка на сервер предприятия или на ПК, где запускается обработка версии не менее 3.8. Через консоль Python требуется установить библиотеку подключения к определенной базе данных.
3. Формат - дополнительные опции помимо SQL есть в REST. Опция отвечает за то, в каком виде будут отправляться данные в REST API или шину данных: CSV, CSV с заголовками, JSON или форматы SQL запросов для ClickHouseDB, PostgreSQL или MySQL.
4. SSL - используется ли защищенное соединение или нет.
5. Порт - какой порт подключения к базе данных или REST.
6. Путь до сертификата - сертификат CA который используется для подключения по SSL. Указывается абсолютный путь, куда есть доступ у службы приложений 1С на сервере или на клиенте, если файловый вариант.
7. Пароль - пароль для авторизации.
8. Включить параметры запроса REST - вложить ли в строку подключения ? и & параметры исполнения обработки: индекс запроса, индекс итерации, индекс строки итераций, количество передаваемых строк.
9. Абсолютный путь Python - задается путь, где исполняемый файл Python для запуска скриптов. Можно задать значение python, если задан корректно параметр операционной системы PATH / sbin
10. Путь к файлу настройки - абсолютный путь для сохранения конфигурационного файла обработки. Путь задается вместе с расширением.
11. Сохранить настройки обработки в файл - Кнопка сохранения настроек обработки в формате JSON.
12. Загрузить настройки обработки из файла - Кнопка загрузки настроек из сохраненного файла настроек в формате JSON.

Вкладка "Текущий запрос"

1. Имя таблицы - наименование таблицы на русском или латинице в подключаемой базе данных или REST. Наименование на кириллице будет переведено в латиницу через транслит.
2. Проверять? - выводит сообщение пользователю, каких полей в базе приемнике нет.
3. Создавать несуществующие поля в БД - обработка создаст несуществующие поля в базе приемнике в случаи их отсутствия.
4. Период - период за который требуется выгрузка данных.
5. Эта текущая дата - опция, если включено, то заданный "Период" игнорируется, а рассчитывается с помощью "Количество дней назад": Дата начала = Текущая дата минус "Количество дней назад", Дата окончания = Текущая дата.
6. Вызвать конструктор - кнопка вызова стандартного конструктора запросов на языке 1С.
7. Отправить запрос - кнопка запускается процедуру отправки данных, которые доступны по заданному тексту запроса немедленно.
8. Поле - Текст запроса
9. Одним файлом - опция, если включено, то отправляется файлом состоящим из строк в портиции. Если выключена, то отправляется построчно.
10. Количество в портиции - сколько строк из табличной части отправлять в базу данных или REST
11. Каждая портиция в отдельном фоне - отправит каждую портицию данных в своем фоновом задании. Требуется разблокировка работы с внешними источниками.
12. Использовать период в ключе - опция, если включено, то при создании таблицы в базе данных, добавится поле "Период" и будет использовано в качестве ключа в таблице базы данных. Если выключено, то поле "Период" не будет. В запросе нельзя использовать свое поле "Период", требуется переименовать в "ПериодРегистратор".
13. Использовать дополнительные ключи - опция, использовать или нет дополнительные ключи в таблице базы данных. В данный момент используются предустановленные ключи: "База", "НомерГТД", "Серия", "НомерСтроки", "СсылкаGUID", "РасчетныйСчет", "Валюта".
14. Количество дней назад - сколько дней вычесть из текущей даты для расчета Даты начала.
15. Отключить? - опция отключает счетчик "по дням" и выполняет запрос с учетом сдвига в датах за один раз, а не по дням. При включенной опции, переопределите Период с помощью "ПериодРегистратор" и подставьте дату из документа.
16. Периодичность - список опций: "День", "Месяц". Используется при формирования запроса и установке параметров Дата начала и Дата окончания. Если установлен "День", то при формировании запроса используется дневной интервал для всего запроса, т.е. выгрузка за каждый день и в поле Период подставляется Дата начала. Если "Месяц", то аналогично дню, только месячный интервал. Если поле "ПолеРегистратор" есть, то поле "Период" перезаписывается значением из поля "ПолеРегистратор".

Вкладка "Настройки запросов" Здесь отображается табличная часть всех запросов, которые были добавлены в обработку Переключение между настройками запросов осуществляется через клик по наименованию "Имя таблицы".

Форма "Конструктор запросов"

Стандартная форма конструктора запросов на языке 1С. Используется специалистами и программистами для создания своих схем компоновки данных на языке запросов 1С.

• Вкладка "Таблицы и поля"  Метаданные объектов доступные для использования в запросе в качестве полей. Здесь также можно задать параметры для виртуальных таблиц: Дата начала и окончания.
• Вкладка "Группировка" Какие поля группировать, а какие суммировать или усреднять.
• Вкладка "Условия" Задаются условия отбора данных запроса.
• Вкладка "Дополнительно" Здесь можно задать имя для пакета запроса или установить сколько записей отбирать.
• Вкладка "Объединения/псевдонимы" Задаем наименование полей запроса. Также можно выполнить и объединить несколько однотипных запросов в одном пакете.
• Вкладка "Порядок" Задаются параметры сортировки данных по полям и их порядок.
• Вкладка "Пакет запросов" Создаются дополнительные запросы внутри одного пакета запросов.

Остались вопросы?

Для получения дополнительной информации или помощи в настройке под нужды вашего бизнеса — оставьте заявку

Подробнее