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

в 19:25, , рубрики: Администрирование баз данных

Решил написать статью о том, как вытягивать данные из 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

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

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

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

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

Есть одна большая проблема – это перечисления. Пример:
Извлечение данных из БД 1С: проблемы с перечислениями
И теперь если попытаться вытянуть это поле из базы напрямую, то получим вот что:
Извлечение данных из БД 1С: проблемы с перечислениями
Да, мы нашли где заголовки перечислений сидят: таблица называется 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.

Автор: Ihor_Bobak

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js