BA Hiring week 25th-29th October 2021
К списку

Очистка и наполнение таблиц в реляционной базе данных

30 марта 2020

Пример логической модели данных:

«Насетапим» немного данных.

У некоторых спортсменов нет категории, клуба или тренера (или они неизвестны).

Встречаются спортсмены, которых ведёт несколько тренеров.

Про одни сущности есть чуть больше информации, чем про другие.

Сначала удалим старые данные.

Ниже не очень хороший код:

  • возможны нарушения целостности при отсутствии внешних ключей или исключения при их присутствии (из-за использования truncate table);
  • используется схема по умолчанию, что чревато возможным удалением «чужих» данных (в одноименных таблицах, но другой схеме).

Вариант №1 (truncate):

In [0]:

use tempdb
go

truncate table SwimmerCoach
truncate table SwimmingClub
truncate table Swimmer
truncate table Category
truncate table Coach
go

Вариант №2 (delete):

In [1]:

use tempdb
go

--clear first tables having no dependencies on others
delete from dbo.SwimmerCoach
go
delete from dbo.SwimmingClub
go
delete from dbo.Swimmer
go
delete from dbo.Category
go
delete from dbo.Coach
go

Вариант выше обладает недостатками: он медленный, привязанный к текущему набору таблиц и построен на знании текущих зависимостей между таблицами.
А что, если мы …

  • хотим минимизировать число проблем, связанных с возможными изменениями в схеме?
  • хотим иметь право выбора в отношении того, удалять ли «старые» данные перед вставкой новых?
  • хотим выбирать из каких таблиц удалять данные?
  • и делать это легко и, по возможности, без проблем с производительностью (то есть быстро)?

Хранимка ниже упрощает процесс очистки таблиц, причем позволяет запустить его в “отладочном” режиме, генерируя весь необходимый код с выводом последнего в окно стандартного вывода (на экран). Удаление данных – вещь серьезная и, в общем случае, неплохо иметь возможность контролировать этот процесс.

Предлагаем читателю самому разобраться с алгоритмом хранимой процедуры.
Обратим внимание лишь на то, что по умолчанию действует режим отладки (значение по умолчанию 1 параметра @debug), благодаря чему ничего не удаляется, но генерируется информативный набор SQL-команд с комментариями.


Вариант №3 (framework-procedure):

In [2]:

use tempdb
go

if not exists(select 1 from INFORMATION_SCHEMA.SCHEMATA where [SCHEMA_NAME] = 'admin')
    exec('create schema [admin]')
go

if object_id('[admin].usp_ClearTables', 'P') is not null
   drop procedure [admin].usp_ClearTables
go

---------------------------------------------------------------------------------------
-- procedure removes data from given list of tables (input json-parameter)
-- created by:   Timofey Gavrilenko
-- created date: 4/23/2019
-- sample call:  
-- exec [admin].usp_ClearTables N'["dbo.SwimmerCoach", "dbo.SwimmingClub", "dbo.Swimmer", "dbo.Category", "dbo.Coach"]'
---------------------------------------------------------------------------------------

create procedure [admin].usp_ClearTables
    @list  nvarchar(max) = null,
    @debug tinyint       = 1 
as    
begin
    set nocount on

    if @list is null
        return

    --table to store sql scripts
    create table #scripts
    (
        id           int             not null    identity    primary key,
        [object_id]  int             not null,
        [table_name] sysname         not null,
        add_sql      nvarchar(max),
        drop_sql     nvarchar(max),
        truncate_sql nvarchar(max)
    )

    --get table list from input json
    create table #tables 
    (
        id           int             not null    identity    primary key,
        [table_name] sysname         not null,
        [object_id]  int     
    )    

    insert into #tables([table_name], [object_id])
    select  [value], 
            object_id([value])
    from openjson(@list) 

    --get drop-scripts
    insert into #scripts ([object_id], [table_name], drop_sql)
    select t.[object_id],
           t.[name], 
           formatmessage(
               N'alter table %s.%s drop constraint %s', 
               quotename(s.[name]), 
               quotename(t.[name]), 
               quotename(fk.[name])
           ) 
    from sys.foreign_keys  fk
    join sys.tables  t           on fk.[parent_object_id] = t.[object_id]
    join sys.schemas s           on s.[schema_id]         = t.[schema_id]
    join #tables tl              on tl.[object_id]        = t.[object_id]
    order by t.[object_id]

    --get add-scripts
    update s
       set add_sql      = q.add_sql,
           truncate_sql = case
                            when exists(
                                     select 1 from #scripts 
                                     where id > s.id and 
                                           [table_name] = s.[table_name]
                                  )
                            then null
                            else formatmessage(
                                     N'truncate table %s.%s', 
                                     quotename(q.[schema_name]), 
                                     quotename(q.[table_name])
                                 )
                          end
    from #scripts s
    join (
           select 
               t2.[object_id],
               s2.[name] [schema_name],
               t2.[name] [table_name],
               formatmessage(
                  N'alter table %s.%s add constraint %s foreign key (%s) references %s.%s (%s)',
                  --alter table 
                  quotename(s2.[name]), 
                  quotename(t2.[name]),
                  --add constraint
                  quotename(fk.[name]),
                  --foreign key        
                  stuff(
                         (
                             select ',' + quotename(c.[name])
                             from sys.columns as c 
                             join sys.foreign_key_columns fkc on fkc.parent_column_id = c.column_id  and 
                                                                 fkc.parent_object_id = c.[object_id]
                             where fkc.constraint_object_id = fk.[object_id]
                             order by fkc.constraint_column_id 
                             for xml path(N''), type
                         ).value(N'.[1]', N'nvarchar(max)'), 
                         1, 1, N''
                       ),
                  --references
                  quotename(s1.[name]),
                  quotename(t1.[name]),
              
                  stuff(
                         (
                             select ',' + quotename(c.[name])
                             from sys.columns as c 
                             join sys.foreign_key_columns fkc on fkc.referenced_column_id = c.column_id  and
                                                                 fkc.referenced_object_id = c.[object_id]
                             where fkc.constraint_object_id = fk.[object_id]
                             order by fkc.constraint_column_id 
                             for xml path(N''), type
                          ).value(N'.[1]', N'nvarchar(max)'), 
                          1, 1, N''
                       ) 
               ) add_sql
        from sys.foreign_keys fk
        join sys.tables  t1          on fk.referenced_object_id = t1.[object_id]
        join sys.schemas s1          on t1.[schema_id]          = s1.[schema_id]
        join sys.tables  t2          on fk.parent_object_id     = t2.[object_id]
        join sys.schemas s2          on t2.[schema_id]          = s2.[schema_id]
    ) q on q.[object_id] = s.[object_id]    


    --get list of sql scripts to execute in right order
    create table #scripts_in_order
    (
        id     int           not null identity    primary key,
        script nvarchar(max) not null
    )

    insert into #scripts_in_order(script)
    select N'-- pass @debug = 0 for executing scripts'
    union all
    select N'-- drop all fk constraints...'
    union all
    select drop_sql from #scripts
    union all
    select N'-- truncate all tables...'
    union all
    select truncate_sql from #scripts where truncate_sql is not null
    union all
    select formatmessage(N'truncate table %s', t.table_name) 
    from #tables t
    left join #scripts s on t.[object_id] = s.[object_id]
    where s.[object_id] is null
    union all
    select N'-- recreate fk constraints...'
    union all
    select add_sql from #scripts
    union all
    select N'-- done!'    

    --run drop constraint, truncate table, add constraint scripts
    declare @id           int = 1,
            @maxid        int = @@rowcount,
            @sql          nvarchar(max)

    begin try
        begin tran
            while @id <= @maxid
            begin
                select @sql = script
                from #scripts_in_order
                where id = @id

                if @debug <> 0
                    print @sql
                else
                    execute(@sql)

                set @id +=1
            end --while

        commit
    end try
    begin catch
        print 'Oops. There were issues when executing [admin].[usp_ClearTables]'
        rollback
    end catch     
end
go


Теперь можно писать скрипт на вставку данных

Ниже не очень хороший код:

  • всегда удаляются имеющиеся данные (а, возможно, мы не всегда этого хотим!);
  • привязанность к текущей схеме данных, код слишком “низкоуровневый”;
  • хардкод в отношении данных, значений primary и foreign ключей.

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

In [3]:

use tempdb
go

--очистка данных
exec [admin].usp_ClearTables N'["dbo.SwimmerCoach", 
                                "dbo.SwimmingClub", 
                                "dbo.Swimmer", 
                                "dbo.Category", 
                                "dbo.Coach"]', 
                             @debug = 0
go

insert into dbo.SwimmingClub([Name], City)
values  (N'ГЦОР Трактор', N'Минск'),
        (N'ДЮСШ Янтарь', N'Минск'),
        (N'СК Олимпик-2011', N'Молодечно')

insert into dbo.Category([Name])
values  (N'1 юн'),
        (N'III'),
        (N'II'),
        (N'I'),
        (N'кмс'),
        (N'тренер 1 кат')

insert into dbo.Swimmer(SwimmingClubID, CategoryId, FirstName, LastName,  YearOfBirth, Gender)
values  (1, 3, N'Илья', N'Гавриленко', 2006, N'м'),
        (1, 3, N'Максим', N'Кликушин', 2007, N'м'),
        (2, 5, N'Анна', N'Высоцкая', 2006, N'ж'),
        (2, 3, N'Никита', N'Клюй', 2005, N'м'),
        (3, 4, N'Матвей', N'Данкевич', 2006, N'м')

insert into dbo.Coach(FirstName, LastName)
values  (N'Алла', N'Усенок'),
        (N'Виталий', N'Барташевич'),
        (N'Евгения', N'Жукова')

insert into dbo.SwimmerCoach(SwimmerID, CoachID)
values  (1, 1),
        (1, 2),
        (2, 1),      
        (2, 2),      
        (3, 3)

Попробуем сделать вставку данных более гибкой.

Идея:

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

Передавать мы будем сериализованные данные с помощью JSON-параметра, что очень удобно для интеграции нескольких частей сложной программной системы. Так, например, мы можем вызывать хранимую процедуру после конвертации любых исходных форматов данных, в тот JSON, что она принимает на вход, или даже просто сериализовав в режиме реального времени объекты приложения, работающего с этой БД. JSON с данными в этом случае будет служить интерфейсом между приложением и БД.

В первую очередь, надо продумать структуру JSON.

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

Допустим, в ходе дизайна схемы JSON, у нас получилось что-то такое (обратите внимание, наш случай довольно прост, зачастую мы не сможем обойтись лишь одним документом):

[
{
    "FirstName": "Илья",
    "LastName": "Гавриленко",
    "YearOfBirth": 2006,
    "Gender": "м",
    "Club": {
        "Name": "ГЦОР Трактор",
        "City": "Минск"
    },
    "Category": "II",
    "Coach": [
        {"FirstName": "Алла", "LastName": "Усенок"},
        {"FirstName": "Виталий", "LastName": "Барташевич"}
    ]
},

{
    "FirstName": "Максим",
    "LastName": "Кликушин",
    "YearOfBirth": 2007,
    "Gender": "м",
    "Club": {
        "Name": "ГЦОР Трактор",
        "City": "Минск"
    },
    "Category": "II",
    "Coach": [
        {"FirstName": "Алла", "LastName": "Усенок"},
        {"FirstName": "Виталий", "LastName": "Барташевич"}
    ]
},

{
    "FirstName": "Анна",
    "LastName": "Высоцкая",
    "YearOfBirth": 2006,
    "Gender": "ж",
    "Club": {
        "Name": "ДЮСШ Янтарь",
        "City": "Минск"
    },
    "Category": "кмс",
    "Coach": [
        {"FirstName": "Евгения", "LastName": "Жукова"}
    ]
},

{
    "FirstName": "Никита",
    "LastName": "Клюй",
    "YearOfBirth": 2005,
    "Gender": "м",
    "Club": {
        "Name": "ДЮСШ Янтарь",
        "City": "Минск"
    },
    "Category": "II"
},

{
    "FirstName": "Матвей",
    "LastName": "Данкевич",
    "YearOfBirth": 2006,
    "Gender": "м",
    "Club": {
        "Name": "СК Олимпик-2011",
        "City": "Молодечно"
    },
    "Category": "I"
}  
]

Теперь нам нужно создать процедуру импорта данных, способную разобрать подобный JSON и вставить данные в множество связанных между собой реляционных таблиц.

Можно создать одну большую процедуру импорта, выполняющую всю работу.

Минусами этого подхода будут:

  • привязанность одновременно и к схеме реляционной БД, и к схеме JSON;
  • высокая сложность одного программного объекта и необходимость его рефакторинга при любых изменениях требований или багах;
  • отсутствие возможности «тонкого тюнинга» и вставки более гранулированных данных (например, клубы или тренеры без привязки к спортсменам);
  • трудности распараллеливания работы над несколькими сущностями.

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

В нашем случае мы реализуем первый вариант.

In [3]:

use tempdb
go

if object_id('dbo.usp_ImportSwimmersData', 'P') is not null
   drop procedure dbo.usp_ImportSwimmersData
go

---------------------------------------------------------------------------------------
-- procedure imports data from incoming JSON-parameter into a number of relation tables
-- created by:   Timofey Gavrilenko
-- created date: 4/26/2019
-- sample call:  
-- exec dbo.usp_ImportSwimmersData @parameters = 
--N'
--[
--    {
--        "FirstName": "Илья",
--        "LastName": "Гавриленко",
--        "YearOfBirth": 2006,
--        "Gender": "м",
--        "Club": {
--            "Name": "ГЦОР Трактор",
--            "City": "Минск"
--        },
--        "Category": "II",
--        "Coach": [
--            {"FirstName": "Алла", "LastName": "Усенок"},
--            {"FirstName": "Виталий", "LastName": "Барташевич"}
--        ]
--    } 
--]'
---------------------------------------------------------------------------------------

create procedure dbo.usp_ImportSwimmersData
    @parameters nvarchar(max) = null
as
begin
    set nocount on

    --if @parameters is null
    --    return
   
    create table #Swimmer
    (
        id              int             not null    identity    primary key,
        FirstName       nvarchar(20)    not null,
        LastName        nvarchar(30)    not null,
        YearOfBirth     smallint        not null,
        Gender          nchar(1)        not null,
        Category        nvarchar(20),
        Club            nvarchar(max), 
        Coach           nvarchar(max)
    ) 

    create table #Club
    (
        id              int             not null    identity    primary key,
        [Name]          nvarchar(100)   not null,
        City            nvarchar(30)    not null,
        [Address]       nvarchar(200),
        Phone           nvarchar(15),
        YearEstablished smallint,
    ) 

    create table #Coach 
    (
        id              int             not null    identity    primary key,
        FirstName       nvarchar(20)    not null,
        LastName        nvarchar(30)    not null,
        DateOfBirth     date,
        Category        nvarchar(20) 
    )

    --get list of swimmers from json
    insert into  #Swimmer(FirstName, LastName, YearOfBirth, Gender, Category, Club, Coach)
    select *
    from openjson(@parameters) 
        with 
        (
            FirstName       nvarchar(20)    N'$.FirstName',
            LastName        nvarchar(30)    N'$.LastName',
            YearOfBirth     smallint        N'$.YearOfBirth',
            Gender          nchar(1)        N'$.Gender',
            Category        nvarchar(20)    N'$.Category',
            Club            nvarchar(max)   N'$.Club'          as json, 
            Coach           nvarchar(max)   N'$.Coach'         as json
        )  js  

    --get list of clubs from json
    insert into  #Club([Name], City, [Address], Phone, YearEstablished)
    select distinct jc.[Name], jc.City, jc.[Address], jc.Phone, jc.YearEstablished
    from #Swimmer s
    cross apply openjson(s.Club) 
        with
        (
            [Name]          nvarchar(100)   N'$.Name',
            City            nvarchar(30)    N'$.City',
            [Address]       nvarchar(200)   N'$.Address',
            Phone           nvarchar(15)    N'$.Phone',
            YearEstablished smallint        N'$.YearEstablished'
        )  jc    

    --get list of coaches from json
    insert into  #Coach(FirstName, LastName, DateOfBirth, Category)
    select distinct jc.FirstName, jc.LastName, jc.DateOfBirth, jc.Category
    from #Swimmer s
    cross apply openjson(s.Coach) 
        with
        (
            FirstName       nvarchar(20)    N'$.FirstName',
            LastName        nvarchar(30)    N'$.LastName',
            DateOfBirth     date            N'$.DateOfBirth',
            Category        nvarchar(20)    N'$.Category'
        )  jc 

     --let's first insert what is easy (data without foreign keys)

    --insert new categories
    insert into dbo.Category ([Name])
    select jc.[Name]
    from
    (
        select Category as [Name] from #Swimmer
        union 
        select Category from #Coach
    ) jc
    left join dbo.Category c           on jc.[Name] = c.[Name]
    where c.CategoryID is null and 
          jc.[Name] is not null

    
    --below we'll use natural/alternative keys from JSON data to get ids (foreign keys)
    --insert new clubs
    insert into dbo.SwimmingClub ([Name], City, [Address], Phone, YearEstablished)
    select jsc.[Name], jsc.City, jsc.[Address], jsc.Phone, jsc.YearEstablished
    from
    (
        select distinct [Name], City, [Address], Phone, YearEstablished 
        from #Club
    ) jsc
    left join dbo.SwimmingClub sc      on jsc.[Name] = sc.[Name] and 
                                          jsc.City   = sc.City
    where sc.SwimmingClubID is null

    --insert new coaches
    insert into dbo.Coach (FirstName, LastName, DateOfBirth, CategoryID)
    select jc.FirstName, jc.LastName, jc.DateOfBirth, jc.CategoryID 
    from
    (
        --dbo.Coach table has dependency on dbo.Category (not required however)
        select distinct FirstName, LastName, DateOfBirth, c.CategoryID 
        from #Coach
        left join dbo.Category c       on #Coach.Category = c.[Name]
    ) jc
    left join dbo.Coach c              on jc.FirstName = c.FirstName and 
                                          jc.LastName  = c.LastName
    where c.CoachID is null

    --insert new swimmers
    insert into dbo.Swimmer (FirstName, LastName, YearOfBirth, Gender, SwimmingClubID, CategoryID)
    select js.FirstName, js.LastName, js.YearOfBirth, js.Gender, js.SwimmingClubID, js.CategoryID
    from
    (
        --dbo.Swimmer table has dependency on dbo.Club (not required)
        select distinct FirstName, LastName, YearOfBirth, Gender, sc.SwimmingClubID, c.CategoryID
        from #Swimmer s
        left join dbo.Category c       on s.Category = c.[Name]
        outer apply openjson(s.Club) 
            with
            (
                [Name]          nvarchar(100)   N'$.Name',
                City            nvarchar(30)    N'$.City'
            )  jsc    
        left join dbo.SwimmingClub sc  on jsc.[Name] = sc.[Name] and 
                                          jsc.City   = sc.City
    ) js
    left join dbo.Swimmer s            on js.FirstName = s.FirstName and 
                                          js.LastName   = s.LastName and 
                                          js.YearOfBirth = s.YearOfBirth and 
                                          js.Gender     = s.Gender
    where s.SwimmerID is null

    --insert new relationships between swimmers and coaches
    insert into dbo.SwimmerCoach (SwimmerID, CoachID)
    select s.SwimmerID, c.CoachID
    from #Swimmer js
    join dbo.Swimmer s                on js.FirstName   = s.FirstName and 
                                         js.LastName    = s.LastName and 
                                         js.YearOfBirth = s.YearOfBirth and 
                                         js.Gender      = s.Gender    
    cross apply openjson(js.Coach) 
        with
        (
            FirstName       nvarchar(20)    N'$.FirstName',
            LastName        nvarchar(30)    N'$.LastName'
        )  jc 
    join dbo.Coach c                  on jc.FirstName  = c.FirstName and
                                         jc.LastName   = c.LastName
    left join dbo.SwimmerCoach sc     on s.SwimmerID   = sc.SwimmerID and
                                         c.CoachID     = sc.CoachID
    where sc.SwimmerID is null and sc.CoachID is null
end
go

Теперь мы можем добиться инкапсулирования данных и отделения backend-а в виде БД от приложения со всеми вытекающими “плюшками”:

  • возможностью внесения изменений, как на стороне приложения, так и на стороне БД, до тех пор, пока сохраняется формат интерфейса данных (JSON-схема);
  • возможностью наполнять БД данными из любых источников, если мы в состоянии предварительно сериализовать их в JSON той схемы, что принимает на вход хранимая процедура;
  • отделением модели БД от самих данных. Данные можно вставлять несколькими процессами в параллели, в любой последовательности, в том числе в уже непустую БД;
  • при желании, можно создать вспомогательное приложение генерации данных в формате JSON с заданными параметрами и автоматизировать наполнение БД на тестовом сервере (тема отдельной статьи).

Автор материала – Тимофей Гавриленко, преподаватель Тренинг-центра ISsoft.

Образование: окончил с отличием математический факультет Гомельского Государственного Университета им. Франциска Скорины.

Microsoft Certified Professional (70-464, 70-465).

Работа: c 2011 года работает в компании ISsoft (ETL/BI Developer, Release Manager, Data Analyst/Architect, SQL Training Manager), на протяжении 10 лет до этого выступал как Sysadmin, DBA, Software Engineer.

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