[SSIS] Integration Service를 통해 MSSQL -> MariaDB(MySQL)로 테이블,쿼리 데이터 삽입하기 - 1 (프로젝트 작업)

[시작하기 전에]

MariaDB, MySQL모두 가능!

하지만 ODBC 드라이버를 MariaDB, MySQL에 맞게 설치해야함

[문제와 고민]

MSSQL Server 2012에 있는 데이터를 MariaDB와 연동시켜야 하는 상황이 발생

 

프로시저를 활용하면 되겠다! 해서 프로시저를 만들었더니 6000개 ROW를 MariaDB 서버에 Insert 하는데만 10분이 걸렸다..

 

Insert 해야할 Row수가 168000개 가량 되기때문에 프로시저를 사용하면 3시간에 육박하는 시간이 소요된다.

시간이 너무 오래걸리기 때문에 다른 방법은 없을까? 하던 도중..

 

https://stackoverflow.com/questions/23805493/inserting-from-ms-sql-server-to-mysql-database/23806799

Inserting from MS SQL Server to MySQL database

우연히 이 글을 보게되었다.

답변을 보면 적당한 크기의 Row를 insert 할거면 MSSQL의 연결된 서버와 OPENQUERY를 활용하고

수천 ~ 수백만개의 Row를 insert 할거면 Integration Services Package를 활용하라고 이야기 했다.

 

이미 OPENQUERY를 활용해보면서 10분에 6000개를 INSERT 하는 힘든 경험을 했기 때문에 Integration Services Package가 뭔지 알아보고 활용하기로 결정

 

Microsoft 공식 문서가 많은 도움이 되었다. 설명도 잘 되어 있음!

공식 문서에 따르면 다음과 같이 설명한다.

 

https://docs.microsoft.com/ko-kr/sql/integration-services/sql-server-integration-services?view=sql-server-ver15 

SQL Server Integration Services - SQL Server Integration Services (SSIS)

Integration Services는 XML 데이터 파일, 플랫 파일, 관계형 데이터 원본과 같은 다양한 원본에서 데이터를 추출 및 변환한 다음 하나 이상의 대상으로 로드할 수 있습니다.

 

[과정]

처음에는 어떻게 사용하는지 몰라서 구글링을 엄청했다. Integration Servie Package를 만드려면

일단 Visual Studio가 필요하다.

 

MSSQL이 설치된 Windows Server 버전은 2012 R2였고 여기에는 Visual Studio 2010 버전이 설치되어 있다.

 

글 작성일 기준으로 Visual Studio 2019 까지는 동일하게 가능한것 같다. 2022 버전에는 아직 Integration Service 프로젝트 기능이 안된다고 나와있었다.

 

Visual Studio 2013 이후 버전부터는 마켓에서 별도의 확장을 다운받아야 Integration Service 프로젝트를 만들 수 있다고한다.

 

Visual Studio 2010 버전 

새 프로젝트를 만들때 Integration Services 프로젝트가 있다.

 

Visual Studio 2019 버전

이 확장이 설치되어 있어야 프로젝트를 만들 수 있음!

[설명은 Visual Studio 2019 버전으로 진행 2010버전에서 작업할때도 거의 동일했음]

프로젝트를 만들고 나면 Package.dtsx 디자인이 보인다.

왼쪽 도구모음에서 즐겨찾기에 [데이터 흐름 태스크]를 추가한다.

 

추가한 데이터 흐름 태스크를 더블클릭하면 데이터 흐름 태스크 디자인 화면으로 바뀐다.

여기서 이제 데이터를 옮기는 작업을 설정하면 된다.

왼쪽 도구 상자에서 OLE DB 원본과 ADO NET 대상을 가져온다.

OLE DB 원본은 MSSQL이 되고

ADO NET 대상은 MariaDB가 될것이다.

 

이제 연결 관리자를 통해 각각의 객체에 DB를 매핑해줘야한다.

 

[OLE 대상 - MSSQL]

아래 이미지를 보면 연결 관리자 부분이 보이는데 여기서 오른쪽마우스를 누르고

새 OLE DB 연결을 선택한다. (MSSQL 연결) 그리고 새로 만들기 선택 후 MSSQL의 서버 정보를 입력 해주면된다.

입력 후 연결 테스트를 진행하여 연결이 성공적으로 이루어지는지 확인 후 확인 버튼을 눌러 연결 관리자 설정을 완료한다.

 

그러면 연결 관리자에 방금 추가한 정보가 보일것이다.

 

[ADO NET 대상 - MariaDB 설정]

방금 연결 관리자에 설정한것은 MSSQL이고 이번에는 MariaDB 연결을 진행해야한다.

하지만 MariaDB를 연결하려면 먼저 ODBC 드라이버를 설치해야한다.

 

https://mariadb.com/downloads/connectors/

Download MariaDB Connectors for data access & analysis | MariaDB

MariaDB 사이트에서 ODBC 드라이버를 다운받는다.

꼭 Windows용 32비트로 받아야한다. (본인 PC가 32비트/64비트 상관없이 32비트로 다운받아야함 64비트로 설치하면 인식을 못함..)

 

그냥 링크를 첨부! (Windows용 32비트 ODBC 드라이버)

https://mariadb.com/download-confirmation?group-name=Data%20Access&release-notes-uri=https%3A%2F%2Fmariadb.com%2Fdocs%2Frelease-notes%2Fmariadb-connector-odbc%2F3-1-15%2F&documentation-uri=https%3A%2F%2Fmariadb.com%2Fkb%2Fen%2Fmariadb-connector-odbc%2F&download-uri=https%3A%2F%2Fdlm.mariadb.com%2F1936454%2FConnectors%2Fodbc%2Fconnector-odbc-3.1.15%2Fmariadb-connector-odbc-3.1.15-win32.msi&product-name=C%20connector&download-size=4.38%20MB 

 

Download Confirmation | MariaDB

 

 

ODBC 드라이버 설치를 완료 후 이번에도 연결 관리자에서 오른쪽 마우스를 클릭하여 ADO.NET 연결을 선택한다.

새로 만들기 클릭후 공급자를 다음과 같이 선택한다.

 

그러면 아래와 같은 화면이 나오는데 연결 문자열 사용을 선택한다.

 

그리고 다음 내용을 입력한다.

 

Driver={MariaDB ODBC 3.1 Driver};Server=127.0.0.1;Database=world;UID=root;PWD=********

Server와 Database,UID,PWD는 MariaDB가 설치된 서버 정보를 입력하면 된다.

 

입력 후 연결 테스트 버튼을 눌러 연결 테스트에 성공했습니다. 메시지가 보이게 한다.

 

Driver부분에 입력할 내용은

ODBC 데이터 원본 관리자(32비트)를 실행하여 드라이버 탭을 선택하면 설치된 드라이버 이름을 확인할 수 있다.

여기서 MariaDB ODBC 드라이버 이름을 확인하고 입력하면 된다.

Mysql도 마찬가지로 MySQL ODBC드라이버 이름을 입력하면 된다.

 

드라이버 이름을 확인한다.

연결 테스트 성공

확인을 눌러 연결 관리자 추가를 완료한다.

 

그러면 다음과 같은 화면이 될 것이다.

 

데이터 흐름에 추가 된 OLE DB 원본을 선택하고 오른쪽 마우스를 눌러 편집을 선택한다.

OLE DB 연결 관리자는 아까 추가한 OLE DB 연결을 선택한다.

 

데이터 액세스 모드는 SQL 쿼리문도 가능하고 특정 테이블이나 뷰를 선택할 수 있다.

이것은 사용자가 원하는 설정대로 하면 된다.

그리고 왼쪽의 열을 선택하여 열 매핑을 진행한다.

 

그리고 데이터 흐름을 다음과 같이 설정한다.

이제 ADO NET 대상을 선택 후 오른쪽 마우스를 클릭하여 편집을 선택한다.

연결 관리자는 아까 추가한 ADO NET 연결 관리자를 추가한다.

테이블 또는 뷰 사용은 MSSQL으로 부터 insert될 테이블이나 뷰를 선택한다.

그리고 왼쪽의 열을 선택하여 열 매핑을 진행한다.

 

열 매핑이 잘 되었나 확인하기 위해

OLE DB 원본과 ADO NET 대상 둘다 각각 오른쪽 마우스를 클릭하여 고급 편집기 표시를 선택한다.

 

다음과같이 매핑이 되었는지 확인 후 설정을 완료한다.

 

[MariaDB 서버 설정]

sql_mode에 설정해야할게 있다.

이 작업을 안하면 MariaDB 서버에 INSERT가 안된다!

 

그 이유는...

SSIS를 통해 MariaDB에 데이터를 삽입할때는 Prepared Statement 방식으로 데이터를 삽입한다.

그런데 INSERT 할때 필드명에 따옴표가 붙는 문제가 있다.

예를 들어 컬럼명이 c1,c2인 경우

INSERT INTO TABLE_NAME("C1","C2") VALUES(?,?) 이런식으로 들어가게 되는데...

여기서 MariaDB와 MySQL은 syntax error를 발생시킨다.

 

그래서 MySQL의 경우 my.cnf 파일에 MariaDB의 경우 mariadb.cnf 파일에 해당 내용을 추가한다.

이미 sql-mode 설정이 있다면 다음 내용을 추가한다.

 

sql-mode = "NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"

 

그리고 MariaDB 서버를 재시작한다.

 

[SQL 실행 태스크 추가]

나는 MSSQL -> MariaDB로 연동하는 작업을 스케쥴링으로 진행해야한다.

그래서 이미 존재할 경우 UPDATE 처리를 하는 ON DUPLICATE KEY UPDATE를 사용하고 싶었지만

어떻게 해야할지 도저히 모르겠어서 (얕은 지식으로 인해 모르는것일 수 있겠지만)

기존 MariaDB Insert대상 테이블을 TRUNCATE 하고 INSERT하는쪽으로 선택했다..

 

그래서 MSSQL -> MariaDB 작업 이전에 TRUNCATE TABLE 하는 작업을 먼저 실행하도록 SQL 실행 태스크를 추가한다.

 

디자인 화면에서 제어 흐름을 선택하면 왼쪽의 도구상자에 SQL 실행 태스크가 있다.

이것을 추가하고 데이터 흐름 태스크에 연결시킨다.

 

SQL 실행 태스크를 선택하고 편집을 선택한다.

Connection 부분에는 연결 관리자에서 설정한 ADO NET 연결 관리자 설정을 지정하고,

SQLStatement는 진행시킬 SQL 쿼리문을 입력한다. (TRUNCATE TABLE_NAME)

 

이제 모든 작업이 완료되었다.

시작버튼을 클릭하여 작업을 실행시켜보자.

 

[Windows Server에서 직접 실행시킨 결과 - Visual Studio 2010]

168482개 Row를 INSERT하는데 6분 33초가 걸렸다.

 

이 integration service package를 mssql이 설치된 windows server에서 스케쥴링 할 예정이기 때문에

windows server에서 진행해봤다.

 

이제 프로젝트는 완성했으니 배포를 해야한다.

배포는 다음글에서 다룬다!