[태그:] MSSQL

MS-SQL Replace ASCII control code

SQL 쿼리 결과를 XML 형태로 출력 할 때 아래와 같은 오류가 발생하는 경우가 있다.

FOR XML could not serialize the data for node ‘[ Column name]’ because it contains a character (0x001F) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

XML로 변활 할 대상 데이터에 ASCII control code가 포함되어 있어서 발생하는 경우이다. 그래서 대상 데이터 안에 포함 되어 있는 해당 아래 스크립트 같이 XML로 변환 전에 모두 치환해 주어야 한다.

SELECT 
  REPLACE([Column name], char(0), '') 
FROM 
  [Table Name]

Control code를 삭제하는 Function을 만들어 두면 필요할 때 가져다 쓰면 좋다.

CREATE FUNCTION [dbo].[FN_ReplaceControlCharacter]  
(  
    @mString NVARCHAR(MAX)  
)  
RETURNS NVARCHAR(MAX)  
AS  
BEGIN      	
	SET @mString = REPLACE(@mString, char(0), '')
	SET @mString = REPLACE(@mString, char(1), '')
	SET @mString = REPLACE(@mString, char(2), '')
	SET @mString = REPLACE(@mString, char(3), '')
	SET @mString = REPLACE(@mString, char(4), '')
	SET @mString = REPLACE(@mString, char(5), '')
	SET @mString = REPLACE(@mString, char(6), '')
	SET @mString = REPLACE(@mString, char(7), '')
	SET @mString = REPLACE(@mString, char(8), '')
	SET @mString = REPLACE(@mString, char(9), '')
	SET @mString = REPLACE(@mString, char(10), '')
	SET @mString = REPLACE(@mString, char(11), '')
	SET @mString = REPLACE(@mString, char(12), '')
	SET @mString = REPLACE(@mString, char(13), '')
	SET @mString = REPLACE(@mString, char(14), '')
	SET @mString = REPLACE(@mString, char(15), '')
	SET @mString = REPLACE(@mString, char(16), '')
	SET @mString = REPLACE(@mString, char(17), '')
	SET @mString = REPLACE(@mString, char(18), '')
	SET @mString = REPLACE(@mString, char(19), '')
	SET @mString = REPLACE(@mString, char(20), '')
	SET @mString = REPLACE(@mString, char(21), '')
	SET @mString = REPLACE(@mString, char(22), '')
	SET @mString = REPLACE(@mString, char(23), '')
	SET @mString = REPLACE(@mString, char(24), '')
	SET @mString = REPLACE(@mString, char(25), '')
	SET @mString = REPLACE(@mString, char(26), '')
	SET @mString = REPLACE(@mString, char(27), '')
	SET @mString = REPLACE(@mString, char(28), '')
	SET @mString = REPLACE(@mString, char(29), '')
	SET @mString = REPLACE(@mString, char(30), '')
	SET @mString = REPLACE(@mString, char(31), '')

    RETURN @mString    
END

0부터 31까지 순차적으로 있어서 While Loop를 사용하면 간결하게 보일 수 있긴 하지만, 데이터가 많은 경우(ex 100만개 이상의 row) 2배 이상의 속도 차이가 날 수 있다. 성능을 고려해야 한다면, 최대한 필요한 Control code를 특정 지어서 직접 치환하는 것을 권장한다.

MS-SQL Stored procedure에 List(or Array) parameter 전달하기

Custom type을 정의하면 Stored procedure(이하 SP)에 List(or Array) 형태의 정보를 전달하여 처리 할 수 있다.

먼저, List로 전달할 정보를 담을 Custom table type을 정의한다.
객체 정보를 담는 Class를 정의한다고 생각하면 된다.

CREATE TYPE CodeList
AS TABLE
(
  Code varchar(32)  
);

SP에 변수를 type을 위에서 만든 Custom type으로 정의한다. 그러면, SP는 정의된 Table 정보를 받아 처리 할 수 있게 된다. 

CREATE PROCEDURE GetInfoByCodes
	@pCodeList AS CodeList READONLY
AS 
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM InfoTable
	WHERE Code in (SELECT * FROM @pCodeList)	
END
GO

임의로 Custom table type으로 만든 변수를 만들어 다음과 같이 SP를 실행해 볼 수 있다.

DECLARE @pCodes CodeList
INSERT INTO @pCodes VALUES('A40274208')
INSERT INTO @pCodes VALUES('A10028014') 
INSERT INTO @pCodes VALUES('A56087115') 

EXEC GetInfoByCodes @pCodeList = @pCodes

MS-SQL Cursor 사용하기

SQL 작업을 하다보면 조회된 쿼리 결과에 대해서 행 단위 작업이 필요할때가 있다. 이때, Cursor를 사용하면 효율적으로 처리 할 수 있다.

Cursor Command

  • DECLARE: Cursor에 관련된 선언을 하는 명령
  • OPEN: Cursor가 Query결과의 첫번째 Tuple을 포인트로 하도록 설정하는 명령
  • FETCH: Query 결과의 Tuple들 중 현재의 다음 Tuple로 커서를 이동시키는 명령
  • CLOSE: Query 수행을 모두 마친 후 Cursor를 닫기 위한 명령
  • DEALLOCATE: Close된 Cursor의 자원을 반환하는 명령

Cursor life cycle

Cursor Example

DECLARE @pColum_1 NVARCHAR(100), @pColum_2 INT, @pColum_3 BIT
DEClARE pCursor CURSOR
FOR
   select * from [Target Table]

OPEN pCursor

FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3

WHILE(@@FETCH_STATUS <> -1)
   BEGIN
      update [Target Table] SET Colum = @Colum_1 + 1
      where Colum_2 = @pColum_2 and Colum_3 = @pColum_3

      FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3
   END

CLOSE pCursor
DEALLOCATE pCursor

-유의사항-

Cursor를 사용하게 되면 내부적인 임시테이블을 사용하기 때문에 많이 사용하게 되면 DB성능에 영향을 미치게 된다. 되도록 사용량이 적은 시간때이거나 적은양의 데이터를 처리할 때 사용하는 것이 바람직 하다.

Python connect to MS-SQL with pymssql

Python에서 MS-SQL을 사용하려면 일단, MS-SQL DB를 지원하는 Python 모듈을 설치해야 한다. MS-SQL에 대한 모듈은 크게 pyodbc와 pymssql 두가지가 있는데 여기서는 pymssql을 사용 할 것이다.

(DB에 관련된 모듈은 무수히 많은 것들이 있다. 참고: https://www.lfd.uci.edu/~gohlke/pythonlibs/)

다음과 같이 pymssql을 설치한다.

(설치 방법은 Ubuntu Linux대상 이다. 다른 OS를 사용하는 경우는 여기를 참고하면 된다.)

$ apt-get --assume-yes update
$ apt-get --assume-yes install freetds-dev freetds-bin
$ pip install pymssql

이제 Python에서 설치한 모듈을 가지고 MS-SQL에 접근해 보자.

import json
import pymssql 

#[Tip]json 문자열을 환경변수 파일로 저장하여 사용한다면 서버정보를 노출 하지 않을 수 있다.
json_string = 
'{
    "host":"Server Address",
    "port":1433,
    "user":"User ID@Server Name",
    "password":"P@ssW0rd",
    "database":"Database Name"
}'
json_data = json.load(json_string)

#pymssql 모듈을 이용하여 Connection 생성을 한다.
conn = pymssql.connect(host=json_data['host'], port=json_data['port'], user=json_data['user'], password=json_data['password'], database=json_data['database'])

여기까지 하면 MS-SQL서버에 접근(Connection 완료) 한 것이다.
다음으로 Select query문을 실행하여 데이터를 가져와 보자.

#Connection으로부터 cursor 생성
cursor = conn.cursor() 

#Select Query 실행
cursor.execute('select * from [Target Table Name]') 

#결과 데이터를 데이터프레임에 저장
df = pd.DataFrame(cursor.fetchall())

#실행이 끝나면 항상 연결 객체를 닫아 주어야 한다.
conn.close()

잘 실행 되었다면 결과를 담은 데이터프레임을 출력 해보자.

#결과 출력
print(df)

실행한 Select Query한 결과를 확인 할 수 있을 것이다.

MS-SQL Table Copy

학습 데이터를 준비 하려다 보면 기존의 데이터를 변형해야 될 때가 있다. 이때 원본 테이블을 가지고 바로 작업을 하게 되면 데이터가 손실 될 수 있다.
백업 데이터가 있다고 하면 그나마 다행이지만 보통 학습에 사용되는 데이터는 양이 많기 때문에 100% 복구하려면 시간이 걸린다.
이런 점들 때문에 될 수 있으면 테이블(혹은 데이터)을 복사해서 사용하면 좋다.

  1. 새 테이블을 생성 하면서 데이터 복사
select * into [New Table Name] from [Source Data]
  1. 테이블 구조만 복사
select * into [New Table Name] from [Source Table Name] where 1=2
  1. 기존 테이블에 데이터만 복사
insert into [Destination Table Name] select * from [Source Table Name]

MS-SQL Select random rows from Table

머신러닝 혹은 딥 러닝 학습을 하기 위해서는 우선 학습할 데이터를 탐색해 보아야 한다. 만약, 많은 양의 Row 데이터를 가지고 있는 SQL Table을 가지고 해야 한다면 일부 표본만 추출해서 테스트를 해보는 것이 필요하다.

이런 경우 테이블에서 랜덤으로 데이터를 추출할 때 다음 Query를 사용하면, 간단하게 일정 비율로 랜덤한 데이터를 추출해 낼 수 있다. 

select top 10 percent * from [Table Name] order by newid()