HOW TO ENCODE UHC(CP949) TO UTF8 in SSIS (FROM MSSQL TO Postgresql)

SSIS를 이용해서 MSSQL -> postgresql로 데이터를 넘겨야하는데 일부 데이터로 인해 encoding 문제가 발생했고 해결 방법을 제시한다.

먼저 오류 메세지는 다음과 같다.
Open Database Connectivity (ODBC) error occurred. SQLExecute returned error while inserting row 1

Open Database Connectivity (ODBC) error occurred. state: 'HY000'. Native Error Code: 0. [Devart][ODBC][PostgreSQL]character with byte sequence 0xc3 0x80 in encoding "UHC" has no equivalent in encoding "UTF8"

SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ODBC Destination" (2) failed with error code 0x80004005 while processing input "ODBC Destination Input" (13). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

즉 UHC를 UTF8로 변환을 못해서 에러가 난다는 것 같다.

문제의 컬럼은 SRCHWD이며 일부 데이터가 Encoding 되어 생성되기 때문에 발생한 문제로 보인다.
결국 Postgresql에 넣을 땐 UTF8(포스팅 상황)로 변환해서 넣어야하는데 어떤 특정 데이터의 바이트 레벨에서 인코딩이 안되도록 문제를 발생시키는 것 같다.

이를 해결하는 방법을 제시할 것이다.


먼저 소스(MSSQL)와 타겟(Postgresql) 테이블 스키마는 다음과 같다.

- MSSQL

- postgresql도 스키마는 거의 동일.
하지만 SHOW SERVER_ENCODING로 서버 인코딩을 확인해보면 server_encoding은 UTF8로 되어 있다.

단순히 OLE DB Source에서 읽어서 ODBC Destination으로 넣으면 에러가 난다.
필자는 Devart ODBC for postgresql를 쓰고 있고 일반 ODBC보다는 빠르다.

이렇게 하면 다음과 같은 에러가 난다.
Open Database Connectivity (ODBC) error occurred. state: 'HY000'. Native Error Code: 0. [Devart][ODBC][PostgreSQL]character with byte sequence 0xc3 0x80 in encoding "UHC" has no equivalent in encoding "UTF8"

데이터가 워낙 많아서 확실히 어떤 데이터가 문제인지 찾을 수는 없었지만 추측하기로는 다음처럼 인코딩이 된 데이터가 문제가 된 것으로 보인다.

"%EC%8A%A4%EB%85%B8%EC%9A%B0%EB%9D%BC%EC%9D%B8+%ED%83%80%ED%94%84+%ED%92%80%EC%8A%A4%ED%81%AC%EB%A6%B0" 이런식으로 인코딩 된 데이터가 문제가 된 것으로 보이며 https://encoder.mattiasgeniar.be/index.php 사이트를 통해 확인을 해보면

RAW URL decoded

스노우라인+타프+풀스크린

으로 풀린다.

이런 데이터로 인해 문제가 되는 경우 두가지 해결 방법이 있다.

1. 원천 그대로 넣기

2. URL Decoding을 해서 넣기

이번 포스팅에서는 두 방법 모두 방법을 제시한다.

먼저 1. 원천 그대로 넣기 방법이다.

소스에서 읽어서 Derived Column으로 컬럼을 한번 Convert 해준 뒤에 Destination에 넣어주면 된다.

먼저 소스쪽을 보자.

소스 쪽에서 그대로 읽으면 CodePage가 949로 읽힌다. 이를 Derived Column으로 컬럼을 수정해준다. 

필자의 경우에는 Expression에 (DT_WSTR,500)SRCHWD을 넣어주고 Data Type은 Unicode string [DT_WSTR]로 바꿔주었다.

마지막으로 Destination 부분이다.

DefaultCodePage를 65001 (UTF8)로 바꿔주고 BindCharColumnsAS를 Unicode로 바꿔주었다.

그럼 성공할 것이다!!

다음으로 2. URL Decoding을 해서 넣기 이다

최종 모양은 다음과 같다.

중간에 Script Component를 활용해서 URL Decoding을 해주기로 한다.

먼저 소스쪽이다. DefaultCodePage는 949이지만 AlwaysUseDefaultCodePage가 False이다.
이런식으로 하니까 CodePage를 1252로 읽는 것을 확인할 수 있었다. 1번 방법에서도 마찬가지로 1252로 읽을 것이다.

Input Columns에 문제컬럼(SRCHWD)을 READONLY로 넣어주고 Input and Output Properties에서 아웃풋 컬럼 srchwddec를 만들어주었다.
Codepage는 1252, DataType은 string [DT_STR]로 하였다.

이제 Script Component를 더블클릭해서 Edit Script...에 들어가서 C# 상태에서 URL Decode를 해주자.

References에서 System.Web을 추가해주었다.
그리고 상단에서 using System.Data; using System.Web;를 추가했다.

그리고 다음과 같은 코드를 추가했다.
UrlDecode + UTF8로 변환하여 srchwddec에 담아서 반환한다.

       /*

       * Add your code here

       */

        string str = HttpUtility.UrlDecode(Row.SRCHWD);

        byte[] e = System.Text.Encoding.UTF8.GetBytes(str);

        Row.srchwddec = System.Text.Encoding.UTF8.GetString(e);

마지막으로 Destination에서 Ansi, 949로 넣고 Column Mappings에서 새로 만든 컬럼을 Mapping 해주면 된다. 그럼 성공한다.

Open Database Connectivity (ODBC) error occurred. state: 'HY000'. Native Error Code: 0. [Devart][ODBC][PostgreSQL]character with byte sequence 0xc3 0x80 in encoding "UHC" has no equivalent in encoding "UTF8"

본 포스팅에서는 위와 같은 문제를 해결하기 위해 두 가지 방법을 제시하였다.

검색해보니 Postgresql에 데이터를 붓기란 굉장히 까다로운 작업임을 깨달았다.

1번은 원천을 살려서 적재하는 방법이고 2번은 원천을 가공해서 적재하는 방법이다.

필요에 따라서 알맞는 방법을 선택해서 사용하면 될 것이다.

HOW TO CREATE MSSQL Audit (Server, Database level)

MSSQL 2008부터 제공되는 MSSQL 감사 기능은 트리거나 프로파일러와 같은 기능들의 단점을 극복해주는 가장 적절한 감사 기능이다. SQL SERVER 감사를 구성해 서버와 데이터베이스에서 발생하는 모든 동작을 추적하여 그 내역을 기록(감사 로깅)할 수 있다.
무엇보다도 테이블이나 뷰에 대한 select와 프로시저에 대한 EXEC 동작도 감사할 수 있다는 것이 장점이다.
감사를 사용하지 않는다면 트리거를 사용하거나 실시간 실행 배치를 만들어서 떠다니는 특정 쿼리를 잡아서 로깅하거나 어플리케이션 단에서 로깅을 하도록 해야하는데 감사는 출력 파일을 별도 디스크에 담을 수 있어서 유용할 것 같다.

크게 서버수준(로그인 실패성공, 로그아웃 등)과 데이터베이스 수준에서 감사가 가능하다.
쉽게 설명하면 감사를 만들고 감사스펙을 만들고 둘다 ON 시키면 알아서 로깅한다.



먼저 서버 감사를 만든다.

USE master
GO
-- 서버 감사 만들기
-- 최대사이즈 512mb
-- 파일이 100개되면 롤오버되도록
-- 공간 미리 할당(디폴트가 off)
-- 쿼리딜레이 1초, 감사 실패시 계속
CREATE SERVER AUDIT [Audit_test]
TO FILE ( FILEPATH = 'D:\mssql_audit',
MAXSIZE = 512MB,
MAX_ROLLOVER_FILES = 100,
RESERVE_DISK_SPACE = OFF
) WITH (
QUEUE_DELAY = 1000,
ON_FAILURE = CONTINUE
);

-- 아래 구문처럼 Default로도 생성이 가능하다.
-- CREATE SERVER AUDIT [Audit_test] TO FILE (FILEPATH = 'D:\mssql_audit')

-- 서버 감사 ON
ALTER SERVER AUDIT Audit_test
WITH (STATE = ON)
GO

서버 감사의 STATE를 ON으로 바꿔주면 해당 경로에 감사 파일이 생성된다.

서버 감사 스펙을 생성해보자.
로그인 성공, 실패, 로그아웃 세가지 경우를 잡도록 해본다.

-- 서버 감사 스펙 생성
CREATE SERVER AUDIT SPECIFICATION ServerAuditSpecification
FOR SERVER AUDIT [Audit_test]
ADD (SUCCESSFUL_LOGIN_GROUP), --로그인 성공
ADD (LOGOUT_GROUP), --로그아웃
ADD (FAILED_LOGIN_GROUP) --로그인 실패
GO
-- 서버 감사 스펙 ON
ALTER SERVER AUDIT SPECIFICATION ServerAuditSpecification
FOR SERVER AUDIT Audit_test
WITH (STATE = ON)
GO



다음은 Adventureworks2017 DB에 데이터베이스 수준 감사를 생성한다.
Person.Person에 감사를 적용해보도록 하자.

USE ADVENTUREWORKS2017
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_test_spec
FOR SERVER AUDIT Audit_test
ADD (SELECT ON OBJECT::[Person].[Person] BY public)
GO

-- 방금 생성한 데이터베이스 감사 ON

ALTER DATABASE AUDIT SPECIFICATION Audit_test_spec

FOR SERVER AUDIT Audit_test

WITH (STATE = ON)

GO

이제 실제로 동작하는지 확인해보자.

아래의 쿼리를 수행하고 아까 위에서 생성된 파일을 SELECT 해보도록 하자.

SELECT *

FROM Person.Person

WHERE FirstName LIKE '%JO%'

-- 감사 내용 확인

SELECT *

FROM sys.fn_get_audit_file('D:\mssql_audit\Audit_test*',DEFAULT,DEFAULT)

로그아웃을 하고 로그인을 해보고 다시 수행해보자.

SELECT *

FROM sys.fn_get_audit_file('D:\mssql_audit\Audit_test*',DEFAULT,DEFAULT)

로그인, 로그아웃 기록이 남는 것을 확인할 수 있다.

실제로 적용을 한다면 서버 수준부터 데이터베이스 수준까지 다 잡아야하나 라는 생각이 드는데 실제로 무언가 사고(?)가 난다면 이런 감사를 다 검사하는 것도 귀찮은 일이 될 것 같다.

특수한 환경이 아니라면 적당히 타협해서 데이터베이스 수준의 특정 테이블에 액션을 가하는 정도만 잡아도 충분하지 않을까..

전체를 다 감사하기에는 성능적인 측면에서 서버에 무리를 줄 것이고 모든 기록이 다 남는다는 것 자체가 추후 일을 만드는 것 + 개발 시 아무래도 자유도가 떨어져서 퍼포먼스 측면에서도 비효율 적일 것 같다는 생각이 든다.

SQL Server Full-Text Search Using AdventureWorksDW2017

SQL Server의 전체 텍스트 검색 기능을 써보자.

일반적으로 컬럼에 인덱스를 만들면 되지 않느냐 라고 반문할 수도 있지만 예를 들어서 컬럼이 varchar(8000)이고 책, 혹은 신문 기사의 전체 내용이 들어간 경우 인덱스를 만드는 의미가 없어진다. 가령 content varchar(max)라는 컬럼에 인덱스를 만들고 like '%human%'을 조회할 경우 아무런 효과를 볼 수 없기 때문이다.

이런 경우 전체 텍스트 인덱스를 생성해서 키워드 별로 조회할 경우 효과를 볼 수 있으며 일반 인덱스는 테이블에 여러개를 생성할 수 있지만 전체 텍스트 인덱스는 테이블 당 1개만 생성 가능하다.

전체 인덱스는 varchar, text, image, xml, filestream 등의 컬럼 타입에 생성이 가능하고 테이블에 PK나 Unique key가 반드시 존재하여야 한다.

전체 텍스트 인덱스는 초기에 전체 채우기를 하고 이후에 diff 기반 채우기나 timestamp 기반 증분 채우기를 사용한다.

또한 중지 단어 및 중지 목록을 지정할 수 있는데 검색할 필요가 없는 단어를 제외시킬 수 있다. 예를 들어서 '반드시', '꼭', '너무' 등 검색할 필요가 없는 단어를 사용할 수 있다.

전체 텍스트 검색 기능을 사용하기 위해서는 검색을 위한 전체 텍스트 및 의미 체계 추출 기능을 설치해야한다.

설치 후에 구성 관리자에서 SQL Full-text Filter Daemon Launcher를 실행한다.

샘플 DB를 통해 간단하게 성능 테스트를 해보도록 하자.

데이터는 AdventureWorks DW 2017 을 사용하였고 아래 링크에서 다운받아서 복원했다.
https://docs.microsoft.com/ko-kr/sql/samples/adventureworks-install-configure?view=sql-server-2017

AdeventureWorksDW2017.dbo.FactAdditionalInternationalProductDescription 테이블이 예제로 쓰기에 적당해보인다.
PK 제약조건은 PK_FactAdditionalInternationalProductDescription_ProductKey_CultureName이고 ProductDescription 컬럼의 내용을 조회하는 것을 목표로 해본다.

현재 ProductKey + CultureName이 PK다.

-- 데이터 내용

SELECT *
FROM [AdventureWorksDW2017].[dbo].[FactAdditionalInternationalProductDescription]
where ProductDescription like '%헤드%'

쿼리를 실행한 실행계획은 다음과 같다.

Clustered Index Scan, 즉 Table Scan을 한 것과 같으며 테이블 전체를 조회하였다.
(읽은 행 수가 15168행으로 전체를 다 읽었다.)

이제 전체 텍스트 인덱스를 생성하여 비교해보자.

-- 카탈로그 생성(카탈로그가 저장될 공간)
CREATE FULLTEXT CATALOG TestCatalog AS DEFAULT

그리고 전체 텍스트 인덱스를 생성한다.
CREATE FULLTEXT INDEX ON FactAdditionalInternationalProductDescription(ProductDescription)
KEY INDEX PK_FactAdditionalInternationalProductDescription_ProductKey_CultureName
ON TestCatalog
WITH CHANGE_TRACKING AUTO;

하지만 아래와 같은 에러가 출력된다.

메시지 7653, 수준 16, 상태 2, 줄 8
'PK_FactAdditionalInternationalProductDescription_ProductKey_CultureName'은(는) 전체 텍스트 검색 키를 강제 적용하는 유효한 인덱스가 아닙니다. 전체 텍스트 검색 키는 고유하고 Null을 허용하지 않으며, 오프라인이 아니고 비결정적이거나 정확하지 않은 비지속형 계산 열에 정의되어 있지 않으며 필터를 포함하지 않는 최대 크기 900바이트의 단일 열 인덱스여야 합니다. 전체 텍스트 키로 사용할 다른 인덱스를 선택하십시오.

단일 열로 생성된 인덱스여야 하나보다.

테이블을 조금 바꿔본다.

-- identity 컬럼 추가
ALTER TABLE FactAdditionalInternationalProductDescription ADD id bigint identity(1,1)
-- pk 인덱스 drop
ALTER TABLE FactAdditionalInternationalProductDescription DROP PK_FactAdditionalInternationalProductDescription_ProductKey_CultureName
-- pk 인덱스 add
ALTER TABLE FactAdditionalInternationalProductDescription ADD CONSTRAINT pk_id PRIMARY KEY (id);

PK를 방금 추가한 Identity 속성의 id 컬럼으로 수정하였다.

CREATE FULLTEXT INDEX ON FactAdditionalInternationalProductDescription(ProductDescription)

KEY INDEX pk_id

ON TestCatalog

WITH CHANGE_TRACKING AUTO;

현재 필자는 중지 단어(제외 키워드)를 지정하지 않았고 따라서 전체 단어에 대해서 계산을 하였을 것이다. 

키워드는 아래 쿼리로 확인할 수 있다.

select * from sys.dm_fts_index_keywords(DB_ID(),OBJECT_ID('FactAdditionalInternationalProductDescription'))

document_count가 존재하는 것을 보니 키워드를 count 한 것으로 보인다.

위에서 Like 문으로 조회했던 N'헤드'를 전체 텍스트 인덱스를 사용하여 검색해보자.

SELECT *

FROM [AdventureWorksDW2017].[dbo].[FactAdditionalInternationalProductDescription]

where CONTAINS(ProductDescription, N'헤드');

쿼리 수행 결과는 같았으며 성능에서 차이가 많이 났다.

읽은 행 수가 3개뿐이다. 성능이 압도적으로 좋아진 것이다.

CONTAINS의 경우에는 키워드를 포함하는 데이터를 반환하는데 AND/OR 등의 조건을 같이 쓸 수 있다. 또한 FREETEXT는 비슷한 내용도 검색해준다.

SELECT *

FROM [AdventureWorksDW2017].[dbo].[FactAdditionalInternationalProductDescription]

where FREETEXT(ProductDescription, N'주유포트');

현재까지의 내용만 봤을 때는 키워드만 잘 만들어진다면 일반 인덱스보다 좋은 성능을 발휘할 것으로 보이며 어떤 경우에는 table full scan정도는 아니지만 거의 table full scan을 타서 성능 향상을 못 누린다. (모든 것은 잘 사용해야 좋은 것은 당연하겠지..)

이번 포스팅은 전체 텍스트 검색의 사용법 위주로 포스팅 했으며 추후에는 동작 방식에 대해서 다시 포스팅하도록 하겠다.