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