Решил написать статью о том, как вытягивать данные из 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     {         /// <summary>         /// Пробегается по всем перечислениям и заполняет таблицу с тремя полями: название перечисления, название значения, порядок         /// </summary>         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});                 }             }         }          /// <summary>         /// Устанавливает все соединения, заполняет DataTable с перечислениями, записывает в БД         /// </summary>         /// <param name="aConnectionString1C"></param>         /// <param name="aConnectionStringSQL"></param>         /// <param name="aTableName"></param>         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.