[스크립트]대량 데이터에 대한 특정 문자열 검색 및 변경 쿼리 추출

해당 스크립트의
1~3은 테스트 환경에서
4~5는 라이브 환경에서

 

 

스크립트 작성 배경

• 안내 문구내 삽입된 도메인의 변경 등의 요건에 따라 특정 키워드에 대한 검색 및 변경이 필요함
• 대량의 데이터에서 Like 조건 검색시 서버 부하 및 잠금 현상으로 인한 서비스 이슈 발생
• 별도 환경에서 변경 대상 검색 후 라이브 환경에서 Row단위 변경 필요 (SCAN -> Seek )

동작 설명 (세부 스탭은 제외)

해당 스크립트 사용 전재

• 라이브 환경에서는 Update 쿼리 (키워드 치환)만 수행
• 변경 대상 파악 및 Update 쿼리 추출은 테스트환경에서 진행 (최근 일자 라이브 백업본 필요)

 

실제 라이브 작업은 4단계 진행 (step3만 첨부)

• step1:  A.com / 고유키 identity & X   – 일괄적용 / 점검
• step2:  i.A.com / 고유키 identity & X   – 일괄적용 / 점검
• step3:  A.com / 고유키 PK – 1건씩 Update&delay 반복 / 업무시간중 진행
• step4: i.A.com /고유키 PK – 1건씩 Update&delay 반복 / 업무시간중 진행

 

작업 진행 순서

• (in Test) 라이브 DB 복원 → (in Test) 스크립트 1~3 수행→ 서비스 담당자 변경 대상 확인 → 제외 및 추가 요건에 따른 쿼리 재수정 
• (in Test)  스크립트 1~3 수행 → (in Test) 스크립트 4~5 수행 → 부하 확인 및 Update 결과 서비스 담당자 검증 → (in Live) 4~5수행

 

스크립트 설명

• 0_문자분할함수 : 3_결과추출쿼리.txt 내에서 IDX 키 추출시 사용
• 1_저장테이블스키마
  • targetlist_cidx -문자열컬럼 리스트 및 추출 건수 기록
  • Results_cidx - 변경 대상 Row 별 정보 기록
• 2_타겟리스트수집쿼리 : 타겟DB내 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext' 자료형 컬럼을 수집
• 3_결과추출쿼리_기본 : 2의 수집 대상 (테이블.컬럼)에 대해 아래의 작업 진행
  • PK (본스크립트는 최대 4개)/ Identity 컬럼 추출
  • 변경 대상 Row 에 대한 테이블/컬럼/PK 값/Update 쿼리 추출
  • 추출 결과 예시
    •    

      

• 4_Step단위분할 : Step3만 작성. 3에서 추출된 Update 문을 별도 추출 
• 5_실행 : While문을 통한 단건씩 변경

스크립트

0_문자분할함수 (https://tjddnjs625.tistory.com/14)

USE targetDB
GO 

create FUNCTION [dbo].[Fn_get_split_index]

(@StrValue  VARCHAR(500),  @SplitChar CHAR(1), @idx       INT 
)
returns NVARCHAR(200)
AS
  BEGIN
      DECLARE @word      CHAR(100),    @sTextData VARCHAR(600),  @num       SMALLINT;

      SET @num = 1;
      SET @sTextData = Ltrim(Rtrim(@StrValue)) + @SplitChar;

      WHILE @idx >= @num
        BEGIN
            IF Charindex(@SplitChar, @sTextData) > 0
              BEGIN  
                  SET @word = Substring(@sTextData, 1, Charindex(@SplitChar, @sTextData)- 1);
                  SET @word = Ltrim(Rtrim(@word)); 
                  SET @sTextData = Ltrim(Rtrim(RIGHT(@sTextData,  Len(@sTextData) - ( Len(@word) + 1 ))))
              END
            ELSE
              BEGIN
                  SET @word = NULL;
              END

            SET @num = @num + 1
        END

      RETURN( @word );
  END 
 
GO

1_저장테이블스키마

 USE collectDB  
CREATE TABLE [dbo].[targetlist_cidx](
	 [TableName] [nvarchar](100) NULL,  [ColumnName] [nvarchar](100) NULL,  [seq] [int] IDENTITY(1,1) NOT NULL,  [cnt] [bigint] NULL
)   
 
CREATE TABLE [dbo].[Results_cidx](
 	[rowNum] [bigint] IDENTITY(1,1) NOT NULL,  [seq] [int] NULL,  [tablename] [nvarchar](100) NULL,  [ColumnName] [nvarchar](100) NULL,
 	[ColumnValue] [nvarchar](max) NULL,  [cidx1] [nvarchar](100) NULL, [cidx2] [nvarchar](100) NULL,  [cidx3] [nvarchar](100) NULL,
 	[cidx4] [nvarchar](100) NULL,  [UPDQuery] [nvarchar](max) NULL
)  

2_타겟리스트수집쿼리

use targetDB
 go 

SET NOCOUNT ON  

DECLARE @SearchStr nvarchar(100), @ReplaceStr nvarchar(100) = null, @test bit = null,@updateSQL nvarchar(255)
DECLARE @TableName nvarchar(100), @ColumnName nvarchar(100), @SearchStr2 nvarchar(100)  
SET @TableName = ''  
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

WHILE @TableName IS NOT NULL  
BEGIN  
    SET @ColumnName = ''  
    SET @TableName =  
        (  
         SELECT MIN(QUOTENAME(TABLE_NAME)) 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_TYPE = 'BASE TABLE'    AND QUOTENAME(TABLE_NAME) > @TableName 
        AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0  
        )  
   
   WHILE(@TableName IS NOT NULL) AND(@ColumnName IS NOT NULL)  
    BEGIN  
        SET @ColumnName =  
            (  
             SELECT MIN(QUOTENAME(COLUMN_NAME)) 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = PARSENAME(@TableName, 1)   AND DATA_TYPE IN('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext') 
            AND QUOTENAME(COLUMN_NAME) > @ColumnName  
            )  
        IF @ColumnName IS NOT NULL  
        BEGIN   
        	 print 'insert into collectDB..targetlist_cidx (tablename,columnname) values ('''+ @TableName+''','''+@ColumnName+''')'
        END 
    END  
END 

3_결과추출쿼리

USE targetDB
GO 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--[search_keyword_replace_v3] 'i.abc.com','zx.net',1
create PROCEDURE [dbo].[search_keyword_replace_v3](@SearchStr nvarchar(100), @ReplaceStr nvarchar(100) = null, @test bit = null)
AS BEGIN   
   SET NOCOUNT ON  

   DECLARE @updateSQL nvarchar(max), @TableName nvarchar(100), @ColumnName nvarchar(100), @SearchStr2 nvarchar(100)  ,@checkrcnt bigint=0, @CIDXName nvarchar(500) =''
   declare @cidx1 nvarchar(100) ='',@cidx2 nvarchar(100) ='',@cidx3 nvarchar(100) ='',@cidx4 nvarchar(100)  ='',@cidxcolm nvarchar(500)='',@cidxcolm2 nvarchar(500)='',@cnt int =0
   SET @TableName = ''  
   SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''') 

   select @cnt= max(seq) from collectDB..targetlist_cidx  where cnt is null 
   WHILE (@cnt >0)  
   BEGIN  
      select  @TableName = tablename,@ColumnName = ColumnName  from collectDB..targetlist_cidx where seq=@cnt
      select @cidxcolm='' ,@cidxcolm2='' ,@cidx1='',@cidx2='',@cidx3='',@cidx4='',@CIDXName=''

      SELECT   @CIDXName= STUFF((SELECT ',' + COLUMN_NAME 
        	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
           	WHERE A.TABLE_NAME = B.TABLE_NAME
           	FOR XML PATH('')), 1, 1, '')   
      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A 
      where A.TABLE_NAME =  replace(replace(@tableName,'[',''),']','');

      if (@CIDXName ='') 
      begin
         SELECT  @CIDXName= A.name  
         FROM  syscolumns A
         JOIN  sysobjects B ON B.id = A.id
         WHERE A.status = 128 and b.name =  replace(replace(@tableName,'[',''),']',''); 
      end 

      --print @tableName +'||'+@CIDXName    
      select @cidx1=dbo.[Fn_get_split_index](@CIDXName,',',1) ,@cidx2=dbo.[Fn_get_split_index](@CIDXName,',',2) , 
       		 @cidx3=dbo.[Fn_get_split_index](@CIDXName,',',3) ,@cidx4=dbo.[Fn_get_split_index](@CIDXName,',',4) 


      select @cidxcolm = @cidxcolm+ case when @cidx1!='' then ''''+rtrim(@cidx1)+'=''+cast(['+ rtrim(@cidx1)+'] as nvarchar(100)),' else '''None'',' end
      select @cidxcolm = @cidxcolm+ case when @cidx2!='' then ''''+rtrim(@cidx2)+'=''+cast(['+ rtrim(@cidx2)+'] as nvarchar(100)),' else '''None'',' end
      select @cidxcolm = @cidxcolm+ case when @cidx3!='' then ''''+rtrim(@cidx3)+'=''+cast(['+ rtrim(@cidx3)+'] as nvarchar(100)),' else '''None'',' end
      select @cidxcolm = @cidxcolm+ case when @cidx4!='' then ''''+rtrim(@cidx4)+'=''+cast(['+ rtrim(@cidx4)+'] as nvarchar(100)),' else '''None'',' end    
      select @cidxcolm =case when len(@cidxcolm)!=0 then left(@cidxcolm, len (@cidxcolm)-1) else @cidxcolm end
      if(@cidx1!='')
      begin
         select @cidxcolm2 = @cidxcolm2+ 
         	case when st.Name in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext','datetime') then  ' AND ['+rtrim(@cidx1)+"]="+"'''+cast(["+ rtrim(@cidx1)+"] as nvarchar(100)) +'''"   
           	when st.Name in ('int','bigint') then ' AND ['+rtrim(@cidx1)+']=''+cast(['+ rtrim(@cidx1)+"] as nvarchar(100)) +'"
          	else  ' ' end
         from  sys.sysobjects so
         join  sys.syscolumns sc on sc.id = so.id
         join   sys.systypes st on st.xusertype = sc.xusertype
         where so.name = replace(replace(@tableName,'[',''),']','') and sc.name=@cidx1
      end
      if(@cidx2!='')
      begin
         select @cidxcolm2 = @cidxcolm2+ 
          	case when st.Name in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext','datetime') then  ' AND ['+rtrim(@cidx2)+"]="+"'''+cast(["+rtrim(@cidx2)+"] as nvarchar(100)) +'''"    
           	when st.Name in ('int','bigint') then ' AND ['+rtrim(@cidx2)+']=''+cast(['+ rtrim(@cidx2)+"] as nvarchar(100)) +'" 
           	else  ' ' end
         from  sys.sysobjects so
         join  sys.syscolumns sc on sc.id = so.id
         join   sys.systypes st on st.xusertype = sc.xusertype
         where so.name = replace(replace(@tableName,'[',''),']','') and sc.name=@cidx2
      end
      if(@cidx3!='')
      begin
         select @cidxcolm2 = @cidxcolm2+ 
          	case when st.Name in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext','datetime') then  ' AND ['+rtrim(@cidx3)+"]="+"'''+cast(["+ rtrim(@cidx3)+"] as nvarchar(100)) +'''"   
          	when st.Name in ('int','bigint') then ' AND ['+rtrim(@cidx3)+']=''+cast(['+ rtrim(@cidx3)+"] as nvarchar(100)) +'"
           	else  ' ' end
         from  sys.sysobjects so
         join  sys.syscolumns sc on sc.id = so.id
         join   sys.systypes st on st.xusertype = sc.xusertype
         where so.name = replace(replace(@tableName,'[',''),']','') and sc.name=@cidx3
      end
      if(@cidx4!='')
      begin
         select @cidxcolm2 = @cidxcolm2+ 
          	case when st.Name in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext','datetime') then  ' AND ['+rtrim(@cidx4)+"]="+"'''+cast(["+ rtrim(@cidx4)+"] as nvarchar(100)) +'''"   
           	when st.Name in ('int','bigint') then ' AND ['+rtrim(@cidx4)+']=''+cast(['+ rtrim(@cidx4)+"] as nvarchar(100)) +'"
           	else  ' ' end
         from  sys.sysobjects so
         join  sys.syscolumns sc on sc.id = so.id
         join   sys.systypes st on st.xusertype = sc.xusertype
         where so.name = replace(replace(@tableName,'[',''),']','') and sc.name=@cidx4
      end    

      select @cidxcolm2 =case when len(@cidxcolm2)!=0 then left(@cidxcolm2, len (@cidxcolm2)-0)+"'" else @cidxcolm2+'''' end

      if (@cidx1!='')
      begin    
       	SET @updateSQL=  'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' =  REPLACE(convert(nvarchar(max),' + @ColumnName + '), ''' + QUOTENAME(@SearchStr, '''') + ''', ''' + QUOTENAME(@ReplaceStr, '''') + ''') WHERE 1=1 ' + @cidxcolm2
      end
      else 
      begin
       	SET @updateSQL=  'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' =  REPLACE(convert(nvarchar(max),' + @ColumnName + '), ''' + QUOTENAME(@SearchStr, '''') + ''', ''' + QUOTENAME(@ReplaceStr, '''') + ''') WHERE 1=1 ' + ' and ' + @ColumnName + ' LIKE '+"'" + @SearchStr2 +"'"  + " OPTION (MAXDOP 3)'"
       end  
      
      INSERT INTO  collectDB..[Results_cidx]  (seq,tablename,cidx1,cidx2,cidx3,cidx4,UPDQuery,ColumnName,columnvalue) 
      exec  
      (  
              'SELECT  '+ @cnt  +',''' +   @TableName +  ''',' + @cidxcolm + ','''   +@updateSQL+',''' 
          + @ColumnName + ''', LEFT(convert(nvarchar(max),' + @ColumnName + '), 255) FROM ' + @TableName + ' WITH (NOLOCK) ' +  ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2   + ' OPTION (MAXDOP 5)'
       )  

      update collectDB..targetlist_cidx
      set cnt= @@ROWCOUNT
      where seq = @cnt

      set @cnt=@cnt-1 

	END  
END 

4_Step단위분할 

CREATE TABLE [collectDB].[dbo].[Apply_step3](  
 [loopNum] [bigint] IDENTITY(1,1) NOT NULL, [rowNum] bigint not null,
 [seq] [int] NULL,  [UPDQuery] [nvarchar](max) NULL, [isrun] int null
)   

select rownum,seq,updQuery into collectDB.[dbo].[Apply_step4] (rownum,seq,updQuery) from  collectDB.[dbo].[Results_cidx]  order by rownum asc --pk
--where seq not in 

CREATE UNIQUE CLUSTERED INDEX [PK_loopNum] ON collectDB.[dbo].[Apply_step3] ( [loopNum] ASC);
CREATE NONCLUSTERED INDEX [NC_rowNum] ON collectDB.[dbo].[Apply_step3] ( [rowNum] ASC);

5_실행

SET NOCOUNT ON  

 DECLARE @cnt bigint,@query nvarchar(max);
  
 select @cnt= max([loopNum]) from collectDB..Apply_step3  where isrun is null 
 WHILE (@cnt >0)  
 BEGIN  
    
  set @query= null;
  select @query = updquery +';' from collectDB.[dbo].[Apply_step3] with (nolock) where [loopNum]= @cnt   
  
  if  @query is null
   continue

  exec (@query)
 
  update  collectDB..Apply_step3
  set isrun= 1
  where [loopNum] = @cnt
    
  set @cnt=@cnt-1 
  
  waitfor delay '00:00:00.100';
end