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

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

Культурный способ

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

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

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

Не очень культурный способ

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


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

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

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

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

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


Ну а дальше найти этот регистр и сгенерировать 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-пакет (или другой механизм) может запустить этот код перед тем, как извлекать данные фактов/справочников, и мы получим заполненную таблицу
image

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

Если у Вас будут замечания или дополнительные идеи – все они с радостью принимаются, пишите на ibobak at bitimpulse dot com.
Share post
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 11

    +1
    Почему бы не взять через COM все данные?
    Это если разово; а если инкрементно, то обмен данными. Все в XML, чисто и красиво.
    Вот это культурные способы, а не обработкой в csv.
      0
      >Почему бы не взять через COM все данные?
      Потому, что тормознуто. Сделайте тест на нескольких десятках миллионов записей, и убедитесь сами.
        +4
        Потому, что тормознуто

        А вы в COM соединении запросы делали или перебор коллекций?
          0
          Код приведен выше. Как видно
                     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();
          
          ....
          (не буду сюда его еще раз вставлять)
          

            0
            Вы по-моему уже не на мой вопрос отвечаете. Я спрашиваю про все остальные данные.
              0
              Если вы имели в виду «как мы брали данные фактов и измерений», то ответ: в этом проекте — писали прямые SQL запросы. Как писали — см. статью: показаны конкретные обработки, которые помогають написать эти запросы.

              Единственная проблема была в том, что вытянуть названия элементов перечислений с помощью SQL запроса — невозможно. Это единственная штуковина, которую приходилось извлекать с помощью COM. Но это извлечение происходит 20 секунд где-то (по всем перечислениям). Согласитесь, в ETL процедуре не самое тяжелое место.

              А так в большинстве проектов прямыми SQL запросами данные стараемся не вытягивать. Мы в основном делаем так: заказчик своими силами пишет обработку, которая выдает нам данные в промежуточную «буферную базу» (это может быть либо csv файлы, либо таблицы в sql server, либо dbf — что угодно), а мы тянем нашим UniveralETL или SSIS пакетом из этой буферной базы. Такой подход гарантирует корректность данных и распределение ответственности: программист 1С заказчика отвечает за правильность своих обработок, мы — за все, что дальше после буферной БД.
              Но это уже другая тема.
        +3
        С выходом 8.3.5 все будет проще. 1С наконец сделали REST-интерфейс к базе, причём он генерируется автоматом.
          0
          Да и в 8.1 не тяжело было сделать SOAP сервис, отдающий нужные данные.
          А для «нормальных» значений перечислений можно использовать псевдофункцию ПреставлениеСсылки() прямо в запросах 1с. Ну, или XMLЗначение(ПеречислениеСсылка)
            0
            а почему бы вам здесь не написать статью с детальным объяснением как это делается? думаю не я бы один был бы благодарен. мы тоже были бы рады от других поучиться.
          +1
          Чтобы исключить субъективные ощущения, в статье не хватает замеров времени выгрузки на Ваших данных:
          1) Через обработку 1С
          2) COM-соединение к 1С (например, на C#)
          3) запрос к базе SQL + COM-соединение к 1С для получения значений перечислений (предложенный вариант)
          И самый важный вопрос – нашелся ли 1С-программист и где он был все это время?
            0
            Если мы BI проект (в котором самая тяжелая часть — это ETL) уже сделали через прямые SQL запросы, то Вы уж простите — но не будем делать то же самое второй раз через обработки 1С или через COM только для того, чтобы замерить время. Так что я не смогу здесь удовлетворить Ваш запрос.

            Но могу уверить вас в том, что если будете тянуть данные (я не о перечислениях, а о регистрах, документах и т.д.) через COM или через обработку 1С — то будет существенно медленее, чем потянуть SQL запросом.

            Тут проблема в том, что вытянуть перечисление прямым запросом — невозможно. Вот собственно это единственная часть, которая тянется через COM, все остальное — через SQL.

            >«И самый важный вопрос – нашелся ли 1С-программист и где он был все это время? „

            Конкретно в этом проекте была ситуация, что программист 1с заказчика уволился, на его место взяли нового, на которого свалилась сразу куча задач, и времени делать обработки у него никак не было.

            Заказчик тем не менее BI хотел. Поэтому мы сошлись на том, что тянем SQL запросами напрямую, а он соглашается с тем, что это все может в один момен вылететь (если вдруг поменяется конфигурация 1С), и соглашается с возможными ошибками в выборках данных.

            Но еще раз: не рекомендую BI разработчикам так делать. лучше если 1С програмист у заказчика будет сам выдавать данные в промежуточную бд.

          Only users with full accounts can post comments. Log in, please.