Когда речь идет о процессе извлечения, преобразования и загрузки (ETL) в SQL Server, очистка и преобразование данных являются важными шагами. Во многих случаях существуют требования регулирующих органов или политики компании, которые обязывают вести аудит любых изменений, внесенных в данные в процессе ETL. Даже если нет конкретных требований к аудиту, всегда полезно иметь механизм аудита для любых обновлений или исключений данных в процессе ETL.
В этой статье мы рассмотрим, как включить простой механизм аудита в SQL Server Integration Services (SSIS). Мы продемонстрируем это, извлекая данные из исходной таблицы, выполняя поиск в другой таблице и обновляя данные при необходимости. Мы также создадим таблицу аудита для отслеживания изменений, внесенных в процессе ETL.
Начнем с создания нашей исходной таблицы:
CREATE TABLE OldCustomerDemographics (
CustomerID INT IDENTITY(1,1),
CustFirstName VARCHAR(50) NOT NULL,
CustLastName VARCHAR(50) NOT NULL,
CustSalesRegion VARCHAR(20) NULL,
CustStatus VARCHAR(20) NOT NULL
)
Затем нам нужно создать таблицу назначения:
CREATE TABLE NewCustomerDemographics (
CustomerID INT,
CustFirstName VARCHAR(50) NOT NULL,
CustLastName VARCHAR(50) NOT NULL,
CustSalesRegion VARCHAR(20) NULL,
CustStatus VARCHAR(20) NOT NULL
)
Теперь давайте заполним исходную таблицу некоторыми тестовыми данными:
INSERT INTO OldCustomerDemographics VALUES ('Jim', 'Halpert', 'SOUTHWEST', 'ACTIVE')
INSERT INTO OldCustomerDemographics VALUES ('Michael', 'Scott', 'NORTHEAST', 'ACTIVE')
INSERT INTO OldCustomerDemographics VALUES ('Dwight', 'Schrute', 'WEST', 'ACTIVE')
...
Чтобы отслеживать изменения, внесенные в процессе ETL, мы создадим таблицу аудита:
CREATE TABLE OldCustomerDemographics_AUDIT (
CustomerID INT,
CustFirstName VARCHAR(50) NOT NULL,
CustLastName VARCHAR(50) NOT NULL,
CustSalesRegion VARCHAR(20) NULL,
CustStatus VARCHAR(20) NOT NULL,
AUDIT_NewSalesRegion VARCHAR(20) NULL
)
Теперь давайте создадим таблицу поиска для стандартизации региона продаж клиента:
CREATE TABLE NewSalesRegions (
RegionID INT IDENTITY(1,1),
RegionCode VARCHAR(20) NOT NULL
)
INSERT INTO NewSalesRegions VALUES ('NORTH')
INSERT INTO NewSalesRegions VALUES ('NORTHEAST')
INSERT INTO NewSalesRegions VALUES ('NORTHWEST')
...
После того, как данные на месте, мы можем создать пакет SSIS для выполнения манипуляций с данными. В панели Control Flow добавьте задачу Data Flow и присоедините к ней источник OleDB для таблицы OldCustomerDemographics.
Затем добавьте преобразование Lookup в поток данных. Это преобразование будет сопоставлять столбец CustSalesRegion из таблицы OldCustomerDemographics со столбцом RegionCode в таблице поиска NewSalesRegions. Строки, которые успешно сопоставлены, будут проходить, а несопоставленные строки будут отправляться в выходные данные об ошибке.
Для несопоставленных строк мы обновим их, чтобы они принадлежали к региону продаж NATIONAL, и отправим старые и новые значения в таблицу аудита. Для этого добавьте преобразование Derived Column, чтобы указать новое значение региона продаж, и преобразование Multicast, чтобы отправить данные как в таблицу аудита, так и в основную таблицу выходных данных.
В OleDB Destination для таблицы аудита подключите его к таблице OldCustomerDemographics_AUDIT и сопоставьте новое значение региона продаж с аудиторским столбцом.
Наконец, используйте преобразование UNION ALL, чтобы объединить обновленные строки с сопоставленными строками из преобразования Lookup. Отправьте выходные данные из преобразования UNION ALL в OleDb Destination, который подключен к таблице NewCustomerDemographics.
При выполнении пакета SSIS вы можете увидеть обновленные строки в таблице NewCustomerDemographics и информацию об аудите в таблице OldCustomerDemographics_AUDIT.
В заключение, внедрение механизма аудита в SQL Server Integration Services является необходимым для отслеживания изменений данных в процессе ETL. Следуя описанным в этой статье шагам, вы можете обеспечить правильный аудит ваших преобразований данных и создать ценный ресурс для будущих ссылок.