Как выгрузить данные из 1с sql

Обновлено: 06.07.2024

Решил написать статью о том, как вытягивать данные из 1С путем SQL запросов. Все нижесказанное касается 1С версии 8.2, оно также должно работать и в 1С версии 8.1. Особое внимание уделено проблеме с извлечением заголовков перечислений.

В идеале выборку данных из 1С должен делать 1С-программист. Хорошо, если он создаст обработку, которая выдаст данные в так называемую «буферную базу»: csv файлы, таблицы в SQL – что угодно. Проектировщик ХД и ETL должен брать данные из буфера.

В этом случае все работает предельно хорошо: зоны ответственности разделены, если найдена ошибка в данных отчета – ее вначале ищут в кубе, если в кубе все ОК – ищут в ХД, если в ХД все ОК – ищут в ETL, если в ETL все хорошо – значит пускай 1С-программист сам разбирается где у него ошибка в обработке, заполняющей «буферную БД».

Но не всегда такой способ доступен. Бывает, что 1С-специалиста либо вообще нет, либо слишком занят, либо мощностей железа не хватает, чтобы «выталкивать» данные из 1С с помощью обработки. И остается одно – делать извлечение данных с помощью SQL запросов.

Вот это собственно и есть этот способ – «сделать SQL запрос на 1С-базу». Главная задача – корректно написать сами запросы. Я думаю, ни для кого не есть секретом, что в 1С структура данных «хитрая», и что поля и таблицы имеют замысловатые названия. Задача проектировщика ETL – вытянуть данные из этой структуры.

Просмотр метаданных

Существуют обработки, дающие возможность просмотреть то, какие поля справочников/документов в каких таблицах/полях базы данных находятся.

Здесь Вы можете скачать несколько таких обработок (которые мы «отфильтровали» путем перебора десяток подобных, выбрав наилучшие). Они делают почти одно и то же – позволяют посмотреть все поля, понять какое поле на какой справочник ведет, и даже предлагают автоматически построить запрос:

Таким образом, начинаем исследовать нужные нам документы:

Дальше открываем любой из них, и находим то, куда он записывается – в какие регистры:

Ну а дальше найти этот регистр и сгенерировать SQL запрос с помощью показанных выше обработок (как на первом рисунке) не составляет труда.

Мы делаем как правило два уровня SQL запросов: «нижний уровень» — вьюхи для переименования полей, «верхний уровень» – вьюхи, которые берут данные из нижнего уровня, и уже они делают необходимые джойны.

Перечисления

Есть одна большая проблема – это перечисления. Пример:

И теперь если попытаться вытянуть это поле из базы напрямую, то получим вот что:

Да, мы нашли где заголовки перечислений сидят: таблица называется Config, в ней – image поля, в которых сидит зазипованный набор байт, который если раззиповать – получим непонятной структуры набор символов, разделителей и т.д. К сожалению, этот формат данных не документирован.

Вы можете скачать ее отсюда.

Запускается вот так:

Делает следующее: коннектится к 1С с помощью COM, берет оттуда все перечисления, и кладет их в указанную вами таблицу указанной базы, предварительно почистив ее. Таблица должна иметь следующую структуру

Дальше понятно, что SSIS-пакет (или другой механизм) может запустить этот код перед тем, как извлекать данные фактов/справочников, и мы получим заполненную таблицу

Если у Вас будут замечания или дополнительные идеи – все они с радостью принимаются, пишите на ibobak at bitimpulse dot com.

Файл с расширением *.bak – это резервная копия 1с выгруженная из 1с сервера. Иногда системный администратор или программист 1с не имеет возможности выгрузить резервную копию через конфигуратор. Так же бывают случаи, когда требуется произвести “холодное копирование” без выхода пользователей из базы данных программы. Для этого используют программу управляющую базами данных в формате SQL – microsoft SQL Manager Studio. Данная программа может быть установлена на ваш компьютер или сервер только в том случае, когда ваша информационная база имеет структуру SQL.

Как производится выгрузка базы данных в формате BAK?

Заходим в Microsoft SQL Manager Studio. Для входа используем требуемый тип авторизации, указываем пользователя и пароль обладающего правами редактирования и выгрузки ИБ (обычно имя такого суперпользователя SA, а пароль задается при установки данной программы);
Во вкладке Базы данных выбираем нужную базу данных и кликаем по ней правой клавишей мышки.
В появившемся контекстом меню выбираем пункт Задачи – Создать резервную копию.
В новом окно оставляем все настройки по умолчанию. Обращаем внимание на путь, по которому сохраниться выгружаемый нами backup.
База данных выгружена, можем загрузить её на другой компьютер с установленным ранее MS SQL серверов и сервером 1с предприятия.

Важно обратить внимания что MS SQL Server бывает разных версий (2008, 2012, 2016, 2019) и пункты меню могут отличаться.

Как преобразовать копию 1С в формате bak в DT или 1CD?

Если нам прислали копию базы в формате *bak и нам следует конвертировать этот файл в формат 1С (*dt или *.1CD) для этого нам потребуется ряд программ.

Microsoft SQL Manager Studio

Сервер 1С предприятия

После установки вышеперечисленного ПО в программе Администрирование серверов 1с предприятие мы можем создать новую информационную базу. После этого, зайдя в MS SQL Manager Studio можем выбрать данную базу и загрузить в неё bak файл. Слепок базы можно добавить в платформу 1С предприятие и выгрузить через конфигуратор.

Позже мы добавим ссылку на подробную инструкцию по загрузке и конвертированию данного файла.

Коды процедур и функций:

Процедура ЗагрузитьДанные() Экспорт
SQLСервер = "(local)";
ПользовательSQLСервера = "";
ПарольSQLСервера = "";
БазаДанныхSQLСервера = "RK7_SHOWROOM";

//Подключаемся к SQL серверу
Попытка
Connection = Новый COMОбъект("ADODB.Connection");
Исключение
Возврат;
КонецПопытки;

Connection.ConnectionString = "driver=; server="+СокрЛП(SQLСервер)+
"; uid="+СокрЛП(ПользовательSQLСервера)+
"; pwd="+СокрЛП(ПарольSQLСервера)+
"; Database="+СокрЛП(БазаДанныхSQLСервера);
Попытка
Connection.Open();
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;

//Выполним запрос
МассивПолей = Новый ТаблицаЗначений;
МассивПолей.Добавить("NAME");
МассивПолей.Добавить("ALTNAME");

СтруктураОтбор = Новый Структура;
СтруктураОтбор.Вставить("Status","3");

Попытка
Connection.Close();
Исключение
Возврат;
КонецПопытки;

Функция ВыполнитьЗапросSQL(МассивПолей, СтруктураОтбор,ИдТаблицыSQL)
//Объявляем таблицу значений

ТЗ_SQL = Новый ТаблицаЗначений;

ADO_RS = Новый COMОбъект("ADODB.Recordset");

//Сформируем строку выбираемых полей
ТекстSelect = "";
ВсегоПолей = МассивПолей.Количество();
н = 0;
Для Каждого ТекПоле Из МассивПолей Цикл
н = н + 1;
ТекстSelect = ТекстSelect + ТекПоле + ?(н=ВсегоПолей,"",",");
КонецЦикла;

//Сформируем строку условия
ТекстWhere = "";
ВсегоОтборов = СтруктураОтбор.Количество();
н = 0;
Для Каждого ТекОтбор Из СтруктураОтбор Цикл
н = н + 1;
ТекстWhere = ТекОтбор.Ключ + " = " + ТекОтбор.Значение + ?(н=ВсегоОтборов,""," AND ");
КонецЦикла;

//ДЛЯ КАЖДОЙ ТАБЛИЦЫ ОТДЕЛЬНЫЙ ЗАПРОС И ЗАПИХИВАТЬ РЕЗУЛЬТАТ В ТАБЛИЦУ ЗНАЧЕНИЙ.

При возникновении необходимости миграции с одной конфигурации поставщика на другую, либо при обновлении доработанной конфигурации, если Поставщик позднее добавил тот объект, что был у нас добавлен самостоятельно, может оказаться, что старый и новый объекты имеют разные внутренние идентификаторы. При сравнении/объединении конфигураций видно, что старый документ не находит соответствие с новым, так как у них различные внутренние идентификаторы метаданных, а при загрузке (обновлении) новой конфигурации поставщика старый документ удаляется вместе со всеми введенными документами.

Существует несколько вариантов решения данной проблемы:

А. Перенос удалившихся документов после обновления конфигурации в новый объект метаданных с помощью правил обмена (данный способ рекомендует использовать 1С);

Б. Использование правил настройки сравнения/объединения конфигураций. При этом можно настроить соответствие между старым и новым объектом метаданных. В пользовательской базе останется старый объект (со старым УИДом), а при сравнении/объединении он будет модифицироваться изменениями нового объекта поставщика. Но при этом объект останется со старым УИДом и на поддержку не встанет.

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

Разберем подробно вариант "В".

Данная статья описывает лишь принцип работы с данными. Осуществлять вручную подмену УИДов, в особенности для объектов с большим числом реквизитов, — достаточно трудоемко и имеет смысл автоматизировать данный процесс.

Читайте также: