Синхронизация с витринами данных |

Когда ваша действующая база данных становится достаточно большой и у вас есть миллионы записей во множестве таблиц, довольно сложно создавать на лету надежные отчеты, не влияющие на производительность этих отчетов. Обычно при создании отчетов требуется множество агрегаций, которые невозможно выполнить за разумное время непосредственно на лету.

Необходим новый подход, который подразумевает создание и использование витрины данных. Витрина данных — это база данных или подмножество базы данных, которая содержит вычисленные/агрегированные данные, готовые для представления в отчете без какой-либо другой обработки.

Итак, нам нужно разделить создание отчета на лету на несколько шагов:

  1. Создайте витрину данных
  2. Синхронизировать данные
  3. Считайте данные из витрины данных и отобразите их в отчете.

В этом посте я опишу подход к выполнению первых двух шагов: создание витрины и синхронизация данных. Я представлю реализацию с использованием языка .Net C# и MS SQL Server (TSQL).

Какова наша цель?

Конечно, основная цель — своевременно отображать отчет со всеми необходимыми данными, НО для этого нам нужен способ реализации первых двух пунктов, описанных выше.

Также нам нужно иметь возможность легко создавать модульные/интеграционные тесты для созданных загрузчиков витрины данных.

Нам необходимо выполнить следующие шаги:

Создание витрины данных

Нам нужно создать правильные таблицы, которые будут содержать предварительно вычисленные/агрегированные данные, и нам необходимо обеспечить целостность данных. Нам нужно создать витрину данных не только в действующей базе данных, НО и в любой доступной базе данных, расположенной на любом сервере, даже в Azure, если это необходимо.

Синхронизация данных

Нам необходимо иметь возможность безопасно и в разумные сроки синхронизировать большой объем данных, а также необходимо создать механизм синхронизации на основе планировщика (данные необходимо синхронизировать в определенное время: один раз в день, два раза в день и так далее).

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

Нам нужен контейнер синхронизации для планирования и исполнитель синхронизации для выполнения синхронизации.

Реализация

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

Исполнитель синхронизации

Поскольку мы используем C#, исполнителем будет новая сборка (библиотека классов), которую можно использовать где угодно, но особенно внутри контейнера синхронизации.

Терминология

Существуют определенные соглашения об именах, которые представлены ниже.

  1. Живая база данных называется Источник база данных
  2. Сама витрина данных в целом называется Назначения
  3. Временные таблицы, используемые для перемещения данных, называются Постановка столы
  4. Таблицы Datamart называются Цель столы
  5. Информация о Назначения как конечная точка сервера и т. д., называется Метаданные

Алгоритм

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

  1. Создайте или обновите структуру Назначения
  2. Выполните операцию предварительной синхронизации, например, удалите существующие данные.
  3. Чтение данных из Источник
  4. Для каждого Цель выполнить следующие операции:
  5. Выполнение агрегирования или вычислений
  6. Массовое перемещение преобразованных данных в Постановка стол
  7. Переместить данные из Постановка стол к Цель стол

Для каждого загрузчика datamart есть папка с не менее чем пятью файлами .sql (все скрипты предназначены для повторного запуска):

MERGE dbo.Client AS Target USING (SELECT [ID] ,[ClientID] FROM dbo.StagingClient) AS Source
ON (Target.[ID] = Source.[ID])
WHEN MATCHED THEN UPDATE SET [ClientID] = source.ClientID ,[OfficeID] = source.OfficeID WHEN NOT MATCHED BY TARGET THEN INSERT ([ID] ,[ClientID] ) VALUES (	source.ID ,source.ClientID );
  1. Файл скрипта для создания всех Целевые таблицы.
  2. Файл скрипта для создания S таблицы тегов, каждый Цель таблица должна иметь соответствующий Постановка стол. Постановка таблицы имеют ту же структуру, что и Цель таблицы, и они действуют как временные таблицы, используемые для перемещения данных между Источник а также Цель.
  3. Файл скрипта со скриптами для очистки ненужных данных от Цель таблиц, и этот скрипт запускается перед запуском синхронизации.
  4. со скриптами для получения данных из Источник
  5. Файл сценария со сценариями, который используется для перемещения данных из одного Постановка таблица соответствует Цель стол. Таким образом, в зависимости от количества таких биномов, у нас может быть несколько файлов сценариев этого типа. Эти файлы используют специальную конструкцию TSQL, которая называется Оператор MERGE. Этот оператор перемещает большие объемы данных из одной таблицы в другую и работает быстро. Пример его использования следующий:

Это утверждение говорит примерно следующее: Объедините в таблице с именем dbo.Client (Цель), значения, указанные запросом (Источник), и для каждой строки из Источника проверьте, соответствует ли значение идентификатора из Цели значению идентификатора из Источника и если true, выполнить обновление для целевой строки, в противном случае вставить исходную строку в целевую таблицу.

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

Проблема обновления была решена с помощью оператора MERGE, который значительно быстрее, чем построчный подход.

Диаграмма классов загрузчика datamart приведена ниже:

Следующий фрагмент кода описывает синхронизацию данных, реализованную в классе DatamartLoaderBase:

public virtual void ExecuteLoader()
 {
           ExecuteOperationBeforeSynch();
            
           ExecuteSync();
            
 }

private void ExecuteOperationBeforeSynch()
        	{
            	string finalQuery = GetQueryToBeExecutedBeforeSync();
            if (string.IsNullOrEmpty(finalQuery))
            {
                return;
            }
            try
            {
                SetupDestinationSqlConnection();
                _datamartDestinationDbManager.ExecuteNonQuery(finalQuery, 0);
            }
            catch(Exception exc)
            {
                Logger.LogException(exc);
            }
            finally
            {
                CleanupDestinationSqlConnection();
            }
       }      


  
private void ExecuteSync()
        {
            DataSet dataSource = GetDataFromSource();
            MoveDataSourceToDestinationDb(dataSource);
        }

 protected abstract DataSet GetDataFromSource();

 private void MoveDataSourceToDestinationDb(DataSet dataSource)
        {
            List<DataTable> transformedDataSource = null;
            try
            {
                transformedDataSource = Transform(dataSource);
                SetupDestinationSqlConnection();
                foreach (var dataTable in transformedDataSource)
                {
                    CreateStagingTable(dataTable);
                    BulkSaveDataTableInStaging(dataTable);
                    MoveDataFromStagingToActualTables(dataTable.TableName);
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.Message);
                Logger.LogException(exc);
            }
            finally
            {
                if (transformedDataSource != null)
                {
                    RemoveStagingTables(transformedDataSource.Select(p => p.TableName).ToList());
                }
                CleanupDestinationSqlConnection();
            }
        }

protected abstract List<DataTable> Transform(DataSet inputDatasource);

private void CreateStagingTable(DataTable transformedData)
        {
            string sqlQuery = GetCreateStagingTableSqlScript(transformedData);
     _datamartDestinationDbManager.ExecuteNonQuery(string.Format(sqlQuery, transformedData.TableName));
        }

protected abstract string GetCreateStagingTableSqlScript(DataTable transformedData);

private void BulkSaveDataTableInStaging(DataTable transformedData)
        {
     _datamartDestinationDbManager.BulkInsert(transformedData.TableName, transformedData, BulkSaveDataTableInStagingTimeout);
        }

 private void MoveDataFromStagingToActualTables(string stagingTableName)
        {
            string finalQuery = GetStagingMoveTargetTableSqlScript(stagingTableName);
     _datamartDestinationDbManager.ExecuteNonQuery(finalQuery, MoveDataFromStagingToActualTablesTimeout);
        }

 private void RemoveStagingTables(List<string> stagingTableNames)
        {
            string sqlText = GetRemoveStagingTablesSqlScript(stagingTableNames);
            _datamartDestinationDbManager.ExecuteNonQuery(sqlText);
        }

 private string GetRemoveStagingTablesSqlScript(List<string> stagingTableNames)
        {
            StringBuilder builder = new StringBuilder();
            stagingTableNames.ForEach(p =>
            {
                builder.Append(string.Format(" IF (object_id('{0}') IS NOT NULL) ", p));
                builder.Append(" BEGIN ");
                builder.Append(string.Format("DROP TABLE {0}", p));
                builder.Append(" END ");
            });

            return builder.ToString();
        }

Все абстрактные методы реализованы в классах, наследуемых от DatamartLoaderBase. Могут быть загрузчики, которые наследуются непосредственно от DatamartLoaderBase (никаких зависимостей не требуется и/или существует только одна целевая таблица), но бывают случаи, когда витрина данных содержит связанные таблицы и существующие внешние ключи. В этом случае нам необходимо в первую очередь синхронизировать зависимости и только потом агрегированные целевые таблицы. Для этого варианта использования я создал еще один базовый класс с именем CompositeLoaderBase

который имеет следующую реализацию:

public abstract class CompositeLoaderBase : DatamartLoaderBase, ICompositeLoader
    {
        protected CompositeLoaderBase(IDestinationDbManager datasourceManager) : base(datasourceManager)
        {
        }

        private Queue<ICompositeLoader> _children;
        public Queue<ICompositeLoader> Children
        {
            get
            {
                if (_children == null)
                {
                    _children = new Queue<ICompositeLoader>();
                }
                return _children;
            }
        }

        public override void CreateDatamartEntities()
        {
            ExecuteChildrenCreateDatamartEntities();
            base.CreateDatamartEntities();
        }
     

        public override void ExecuteLoader()
        {
            
            ExecuteChildrenLoaders();
            base.ExecuteLoader();
        }

        private void ExecuteChildrenCreateDatamartEntities()
        {
            foreach (ICompositeLoader childLoader in Children)
            {
                childLoader.CreateDatamartEntities();
            }            
        }

        private void ExecuteChildrenLoaders()
        {
            foreach(ICompositeLoader childLoader in Children)
            {
                childLoader.ExecuteLoader();
            }
        }
    }

Дети Свойство сохраняет зависимости, которые сначала синхронизируются, а затем происходит синхронизация для текущего загрузчика.

Преимущества этой реализации заключаются в следующем:

  1. Новые загрузчики витрины данных можно легко добавлять, соблюдая принцип открытия-закрытия (можно добавить новый класс, не затрагивая существующие).
  2. Поскольку зависимости вводятся в конструктор (жесткое агрегирование), их можно смоделировать и создать модульные тесты. Также могут быть созданы интеграционные тесты, если зависимости не имитируются, но указывают на некоторые реальные базы данных. ( IDestinationManager а также ISourceDbManager )
  3. Шаблон метода шаблона, используемый в DatamartLoaderBase → Моведататодестинатиондб (…) повторно используется в каждом вновь создаваемом загрузчике и не подлежит частому изменению.
  4. Композитный шаблон, используемый в ICompositeLoader позволяет синхронизировать зависимости.

Выводы

Используя этот подход, в несколько этапов производительность синхронизации значительно увеличилась с пары часов (при использовании синхронизации построчно) до пары минут (максимум тридцать минут).

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *