[Из песочницы] Извлечение данных из БД 1С: проблемы с перечислениями27.06.2014 23:34

Решил написать статью о том, как вытягивать данные из 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 поля, в которых сидит зазипованный набор байт, который если раззиповать — получим непонятной структуры набор символов, разделителей и т.д. К сожалению, этот формат данных не документирован.Поэтому мы нашли другой способ. Мы сделали на C# небольшую программку, которая использует COM-объект 1С-ки для того, чтобы установить с ней соединение, и вытянуть все значения всех перечислений в одну таблицу.

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

Код:

using System; using System.Data; using System.Data.SqlClient;

namespace _1CEnumParser { class Program { ///

/// Пробегается по всем перечислениям и заполняет таблицу с тремя полями: название перечисления, название значения, порядок ///

private static void DataTableFill (DataTable aTable, dynamic a1CConn) { foreach (dynamic catalog in a1CConn.Metadata.Enums) { string enumName = catalog.Name; dynamic query = a1CConn.NewObject («Query»); query.Text = «select * from enum.» + enumName; dynamic items = query.Execute ().Unload (); // бежим по строкам for (int i = 0; i < items.Count(); i++) { string enumValue = null; int enumOrder = -1; for (int j = 0; j < items.Columns.Count(); j++) { string colName = items.Columns.Get(j).Name; dynamic colValue; try { colValue = a1CConn.String(items.Get(i).Get(j)); } catch { colValue = "-1"; } switch (colName.ToLower()) { case "ссылка": enumValue = colValue.ToString(); break; case "порядок": enumOrder = int.Parse(colValue.ToString()); break; default: throw new ApplicationException("unknown column name in enum.recordset: " + colName); } } // получили 3 заполненные значения: enumName, enumValue, enumOrder. вставка в таблицу aTable.Rows.Add(new object[] {enumName, enumValue, enumOrder}); } } }

///

/// Устанавливает все соединения, заполняет DataTable с перечислениями, записывает в БД ///

/// /// /// private static void ConnectAndFill (string aConnectionString1C, string aConnectionStringSQL, string aTableName) { // входим в SQL базу и удаляем все из таблицы var connSQL = new SqlConnection (aConnectionStringSQL); connSQL.Open (); // входим в 1С var connector1C = new V82.COMConnector (); dynamic conn1C = connector1C.Connect (aConnectionString1C); // удаляем из таблицы все данные var command = new SqlCommand («delete from » + aTableName, connSQL); command.ExecuteNonQuery (); // заполняем таблицу var da = new SqlDataAdapter («select EnumName, EnumValue, EnumOrder from » + aTableName, connSQL); var thisBuilder = new SqlCommandBuilder (da); var ds = new DataSet (); da.Fill (ds); DataTableFill (ds.Tables[0], conn1C); da.Update (ds); // закрываем коннект connSQL.Close (); }

static void Main () { string[] args = Environment.GetCommandLineArgs (); string aConnectionString1C = args[1]; string aConnectionStringSQL = args[2]; string aTableName = args[3]; ConnectAndFill (aConnectionString1C, aConnectionStringSQL, aTableName); } } } Запускается вот так:

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

CREATE TABLE [dbo].[tbl1CEnums]( [EnumName] [nvarchar](1024) NULL, [EnumValue] [nvarchar](2014) NULL, [EnumOrder] [int] NULL ) ON [PRIMARY] Дальше понятно, что SSIS-пакет (или другой механизм) может запустить этот код перед тем, как извлекать данные фактов/справочников, и мы получим заполненную таблицу

и дальше вы уже можете строить джойн по полю _EnumOrder: справочник ссылается на _Enum таблицу по IDRRef, в ней поле _EnumOrder которое ссылается на поле EnumOrder вашей таблицы, которую только что вытянул C# код.

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