SSMS에서 프로시저를 실행했을 때 수행 결과가 부족하다 - SET ROWCOUNT 설정으로 인한 실수

시나리오
원격지 서버의 프로시저를 사용자 로컬 PC에서 수동으로 돌리고자 한다. 프로시저 결과로 대략 수백~수억 row가 insert될 것이라고 생각하는데 자꾸 500row만 insert가 되는 이상한 상황이 연출되었다. 아무리 프로시저 내 로직을 검증해봐도 이상이 없었고 유독 프로시저만 수행하면 결과가 부족한 현상을 경험하였다. SSMS의 버그인지, 원래 이렇게 유도를 한 것인지는 잘 모르겠지만 현상을 확인한 후 주의를 하고 있다.

원인

SSMS의 도구->옵션->쿼리실행->SQL Server ->일반 탭에서 SET ROWCOUNT를 500으로 맞춰놓았다. default는 0이며 set rowcount 0은 쿼리 결과를 제한 없이 모두 출력한다는 것이다. 여기에서 500으로 제한을 해둔 탓에 로컬 pc에서 원격지 프로시저를 수행하여도 로컬 pc의 set rowcount 500 설정에 막혀 결국 테이블에도 500 row만 insert되는 상황이었다. 결국 이를 풀어줌으로써 해결하였다.


증상재현

증상 재현을 위해 먼저 위와 5000 ROW가 들어있는 TEST 테이블을 생성하였다.

그리고 똑같은 스키마의 TBL_TEST_2와 프로시저를 생성하였다.

그리고 프로시저를 수행해보았다.
TRUNCATE TABLE TBL_TEST_2

EXEC USPS_TEST

SELECT COUNT(*)
FROM TBL_TEST_2

SSMS의 도구->옵션->쿼리실행->SQL Server ->일반 탭에서 SET ROWCOUNT를 500으로 맞춰놓았기 때문에 쿼리 결과가 500 row밖에 되지 않는 현상이 재현되었다.

이를 해결하기 위해 SSMS에서 SET ROWCOUTN를 0으로 풀어도 바로 해결이 되지 않고 세션을 새로 열어야 한다. 세션을 새로 열지 않으면 명시적으로 SET ROWCOUNT 0을 해당 세션창에서 한번수행해주어야 한다.



느낀점
일반적으로 FULL SCAN을 방지하기 위해 SSMS의 설정에서 최대 행 갯수를 제한해놓을 것이다. 하지만 이 설정으로 인해 수동으로 서버의 프로시저를 수행할 경우 원하는 결과가 나오지 않을 수 있다. 따라서 이런 상황이 발생할 수 있다는 것을 인지하고 있어야겠다.

SSIS에서 MSSQL Polybase로 Hadoop 데이터를 외부 테이블 생성할 때 팁

시나리오
하둡 데이터를 MSSQL로 매일 특정 시간에 가져오는 배치를 짠다고 가정한다. 이 경우 MSSQL에서는 해당 테이블을 polybase로 External Table을 만들게 된다.

예를 들어 하둡의 특정 경로 /app/analy/ods/visit/20181101처럼 끝에 날짜 포맷만 바꾸면서 데이터를 저장하고 이 데이터를 mssql로 가져오는 경우이다. 하나의 프로세스(배치)로 이루어지면 떨궈질때까지 기다리고 다 떨궈지면 가져오면 되지만 이 경우에는 하둡 데이터를 저장하는 것, MSSQL로 가져오는 것이 분리되어 있는 경우라고 가정한다.

우리는 하둡 경로를 알고 있을 때 MSSQL에서 다음처럼 데이터를 가져올 수 있다.
CREATE EXTERNAL TABLE ~
LOCATION ~
그러나 이 처럼 단순하게 해결되지 않는 경우가 있다.


문제점
특정 상황이 발생하여 하둡에 데이터가 생성되지 못했을 경우 CREATE 문을 실행하면 PATH가 존재하지 않는다는 에러가 난다.


해결방법
우리는 주기적으로 하둡 경로를 반복체크하면서 데이터가 생성된 이후에 MSSQL로 가져오면 된다.

하둡 명령어 중에 fs -test -s /app/analy/ods/visit/20181101 (-e는 path, -d는 directory, -f는 file 등 여러 옵션이 있다) 를 먼저 날려보고 결과로 0을 리턴하면 path가 존재한다는 의미이다.


즉 정리하면 다음과 같다.

1. 하둡 데이터를 떨굴 때 원하는 것을 다 떨구면 해당 경로에 success라는 크기가 0인 파일이나 폴더를 하나 더 떨구게 한다. (혹은 데이터 생성을 시작 할 때 start라는 폴더나 파일을 생성하게 하고 완료되면 success를 생성하게 해도 된다.)
2. 배치에서 데이터를 가져올 때 루프를 만들고 안에서 테이블을 생성하기 전에 fs -test -s /app/analy/ods/visit/20181101/success 를 날리도록 한다.
3. 리턴값이 0이면 success가 데이터가 존재하는 것이기 때문에 루프를 탈출하고 아니면 주기적으로 위의 명령어를 하둡에 찔러보게 한다.
4. 루프를 탈출하면 MSSQL에서 CREATE EXTERNAL TABLE 문 실행한다.

이런 방법을 데이터가 생성되기전까지 기다리는 것이기 때문에 보다 안전할 수 있다.

MSSQL RAISEERROR VS THROW 팁

시나리오
MSSQL의 가장 강력한 힘은 T-SQL이다. 저장프로시저가 강력한 배치 수단으로 쓰일 수 있기 때문인데 프로그래밍 코드를 작성하는 것 처럼 프로시저 안에서 모든 걸 처리할 수 있다. 그리고 요즘 같이 AI 흐름을 따라가기 위해서 인지 MS에서도 SQL Server 2017 버전부터 프로시저 내에서 Python을 지원한다.
저장 프로시저로 배치를 짜는 경우 의도적으로 에러를 내거나 에러가 났을 때 디버깅을 할 필요가 있다. 이때 유용하게 사용할 수 있는 것이 RAISEERROR와 THROW이다.
잘만 사용하면 배치에서 디버깅으로 정말 유용하게 사용할 수 있다.
필자는 Raiserror를 프로시저의 특정 구간에 걸어놓고 배치 시 디버깅을 할때 사용하거나 후속 배치를 일부러 돌리지 않기 위해 의도적으로 에러를 내는 경우에 종종 사용한다.


RAISEERROR VS THROW
1. Raiseerror는 msg_id가 RAISERROR에 전달되는 경우 ID가 sys.messages에 정의되어있어야 하지만 THROW는 error_number 매개 변수가 sys.messages에 정의되어있지 않아도 된다.
2. RAISEERROR는 msg_str 매개 변수는 printf 서식 지정 스타일을 포함할 수 있지만 THROW는 아니다.
3. RAISEEROR에서 severity 매개 변수는 예외의 심각도를 지정하지만 THROW에는 그런 파라메터가 없고 예외 심각도는 항상 16으로 고정되어있다.


사용방법
1. RAISEERROR
계획된 오류가 있는 테스트용 프로시저를 생성한다. (duplication error를 의도했다.)

그리고 프로시저를 실행해본다. 실무에서는 배치 프로시저라고 생각을 하면 되겠다.

2. THROW
마찬가지로 계획된 오류가 있는 테스트용 프로시저를 생성한다. 
(똑같이 duplication error를 의도했다.)

프로시저를 수행해본다.

3. SQL Server Log에 RAISEERROR가 남는 것을 볼 수 있다.

MSSQL 컬럼스토어 인덱스 팁 (180GB 테이블을 컬럼스토어 인덱스로 변경한 결과)

MSSQL에는 컬럼스토어라는 특수한 저장 방법이 존재한다.

잘 사용하면 저장 공간과 속도 측면에서 엄청난 성능을 발휘할 수 있으며 적용 전에 컬럼스토어란 무엇인가에 대해서 알아보자.

컬럼스토어란?

MSSQL은 8KB 사이즈 페이지 단위로 데이터를 저장하고 읽는다. (페이지는 오라클의 블록하고 같은 개념이다.) 이 페이지 안에 테이블의 행이 저장되어 있는 구조이다.

예를들어 어떤 테이블에 컬럼이 많아서 한 ROW의 사이즈가 1KB이다.

그럼 조회결과가 100만건일 경우에는 8KB * 100만 = 약 7.6GB를 읽어야한다.

컬럼 스토어란 컬럼별로 데이터를 저장하여 컬럼들끼리 데이터를 모아놓는 것이다. 

쉽게 생각해서 ROW 스토어가 수평적으로 저장하면 컬럼스토어는 수직적으로 저장된다고 보면 된다.

이는 열이 동일한 타입이기 때문에 유사한 값을 저장하므로 압축 비율이 높고 일반적으로 테이블을 조회할 때 모든 컬럼을 SELECT 하지 않기때문에 실제로는 더 큰 효과를 낼 수 있다.

 

다른 DBMS에서 컬럼스토어를 흉내내려면 각 컬럼을 경우에 수에 따라 쪼개놓고 모든 컬럼에 인덱스를 만든다고 생각하면 될 것이다.

특이사항

1. 제약사항은 테이블당 1개만 생성이 가능하다. 

(사실 각 컬럼에 인덱스를 만드는 것과 같으므로 1개만 있으면 된다.)

2. 업데이트가 발생할 경우 데이터를 메모리에 올려놓고 압축을 풀고 업데이트를 하고 다시 압축해서 디스크에 쓰므로 비용 발생이 크다.

3. 컬럼이 1024개가 최대

4. ALTER INDEX, REORGRANIZE 불가하므로 삭제 후에 다시 생성해야됨

5. 메모리 제약(8MB X 컬럼 수 X DOP )

등이 있다.

컬럼스토어 권장 상황

• 1. 테이블 - ROW가 100만건 이상(압축률 보장)
• 2. 쿼리 - 집계형, GROUP BY, full scan이 자주 발생하는 테이블
• 3. 트랜잭션 - OLAP 환경. 데이터가 자주 변경되지 않는 테이블

로우스토어 권장 상황

• 1. 테이블 - 적은 ROW
• 2. 쿼리 - 집계형이 아닌 일반적 필터링 쿼리가 주로 사용
• 3. 트랜잭션 - OLTP 환경. 데이터가 자주 삽입, 삭제, 업데이트가 발생하는 테이블

정리하면 일반적으로 OLTP보다는 집계성 OLAP 환경에 더 적합하며 적절하게 사용하면 압축률이 좋아서 I/O 성능이 매우 향상된다.

사용 결과

실제로 180GB 테이블을 컬럼스토어 인덱스로 변경한 결과이다.

테이블 A와 테이블 B의 스키마에 컬럼을 추가할 일이 생겨서 스키마를 바꾸기로 했다.
크게 바뀐 것이 있다면 아무래도 테이블 특성 상 기간별 조회가 이루어지고 추후에 데이터를 지울 계획이라서 파티션을 줬고, 용량이나 성능을 향상시키기 위해 컬럼 스토어 인덱스로 변경하였다.

최근 기준으로 테이블 A는 하루 평균 150만건 정도 데이터가 쌓인다.
최근 테이블 B는 하루 평균 250만 건 정도 데이터가 쌓인다.

변경 전 테이블 A 스키마는 다음과 같다.

변경 전 테이블 B 스키마는 다음과 같다.

A 테이블을 다음과 같이 변경하였다. (10일파티션, 컬럼스토어 적용)

B 테이블을 다음과 같이 변경하였다. (10일파티션, 컬럼스토어 적용)

바뀐 점은 컬럼 하나를 추가하고(VARCHAR(10)) 10일 파티션을 주었으며 컬럼스토어 인덱스를 생성하였다.

변경 결과를 보자.

테이블 A와 테이블 B의 모습이고 인덱스 조각화가 없진 않을테니 인덱스 리빌드를 하면 차지하는 용량이 더 줄어들텐데 워낙 ROW수가 많다보니 리빌드 할 엄두가 안났고, 그냥 바로 새로운 테이블에 데이터를 붓기 시작했다.

다음은 변경 전 A, B 테이블 사이즈이다.

테이블 A는 ROW수가 8억건에 순수 데이터 사이즈만 52GB 이상이다.

테이블 B는 ROW수가 13억8천건에 순수 데이터 사이즈만 128GB 이상이다.

두 테이블 모두 약 2년 반정도의 데이터를 갖고 있었다.

테이블 A에서 테이블 A_NEW 에 붓는 작업은 약 2시간이 소요되었다.

테이블 B에서 테이블 B_NEW 에 붓는 작업은 약 4시간이 소요되었다.

결과는 다음과 같았다.
1. A테이블 52GB -> 22GB
2. B테이블 128GB -> 25GB

특히 테이블 B의 사이즈가 크게 작아진 것을 확인할 수 있었다.

아무래도 DISK에서 읽어야할 SIZE가 훨씬 줄어들었다보니 I/O에서 큰 성능을 보일 것으로 기대되며 데이터가 자주 변경되지 않는 집계성 테이블이라서 이런 결정을 할 수 있었다.

MSSQL 어셈블리 만들기 - How to Implementing CLR Object DLL

시나리오
1. SQL Server에서 PDW(Parallel Data Warehouse)로 동적 쿼리를 만들어서 날리려고 하는데 8000자 이상의 쿼리문을 수행해야 한다.
2. A서버의 SQL Server (가정 주소:321.321.1.321,9999)에서 B서버의 PDW (가정 주소 : 123.123.1.123,8888)로 쿼리를 날려 SELECT 결과를 받아오고자 한다.
3. 특정 사용자에게만 DB 접근권한을 주고 계정을 분리하고자 한다.

해결방법
CLR Object DLL를 만들어서 ASSEMBLY로 등록하고 CLR을 통해 쿼리를 수행한다.
CLR을 통해 DB 접근 권한을 분리하고 8000자 이상의 동적쿼리를 쿼리를 수행할 수 있다.

  고려사항
얼마 전 MSSQL이 설치된 윈도우 서버에서 메모리가 부족한 현상을 겪었고 덕분에 수 번의 장애를 겪었다. 윈도우 서버의 메모리가 약 100기가 이상이 되지만 보통의 경우에는 DB에 대부분의 메모리를 할당하고 최소한의 메모리를 OS에 할당하겠지만 필자의 경우에는 MSSQL에서 PDW로 CLR을 호출하는 경우 딱 그 시점에 윈도우 서버의 메모리가 확 튀며 장애 현상을 겪었다. 이런 경우에는 MS 엔지니어가 메모리를 DB에 적당히 할당한 후 안전하다고 판단되는 수준까지 점진적으로 올릴 필요가 있다고 한다.


방법

1. DLL을 만들기 위해 Visual Studio에서 Visual C# CLR Database Project를 만든다.

(파일 -> 새 프로젝트 -> SQL Server -> SQL Server 데이터베이스 프로젝트 생성)

2. 솔루션 탐색기에서 추가 -> 새항목 -> SQL CLR C# -> SQL CLR C# 저장 프로시저 생성한다.

3. 쿼리를 날릴 때 SelectPdwByLogin 함수를 호출할 것이고 이 함수 내부에서 분리시키고자 하는 login_name을 "exuser"를 구분했다.

4. 빌드를 하고 DLL(CLR_PDW.dll)을 얻는다. 이를 서버(321.321.1.321,9999)의 적당한 위치에 복사한다.

5. 서버(321.321.1.321,9999)에서 CREATE ASSEMBLY문으로 CREATE ASSEMBLY를 하고

이 때 위에서 만든 CLR_PDW.dll의 경로를 입력해준다. 그리고 프로시저를 하나 생성하여 어셈블리를 수행하도록 한다.

6. 테스트를 한다.

A 서버 SQL Server(321.321.1.321,9999)에서 USP_CLR_PDW를 호출하여 B 서버의 PDW(123.123.1.123,8888)의 TEST DB CUSTOMER 테이블을 SELECT 해본다.

※ 별도로 test DB에는 로그인 생성, 사용자 추가, 롤에 사용자를 추가해야한다.

--로그인 생성
CREATE LOGIN exuser WITH PASSWORD = 'exuser',
CHECK_EXPIATION = OFF,
CHECK_POLICY = OFF;

--TEST DB에 사용자 추가
USE test;
CRATE USER exuser WITH DEFAULT_SCHEMA=dbo;

--롤에 사용자 추가
EXEC test..sp_addrolemember db_datareader, exuser;

대한민국 지도에 인구수, 세대수 표현하기 - 시도, 시군구, 행정동, 법정동 코드를 활용한 데이터 매핑 과정

웹사이트에 대한민국 지도(시도, 시군구, 법정동)를 표현하고 각각 권역에 데이터를 입혀야하는 사람들에게 도움이 되기를 바라며 포스팅한다.

필자도 필요해서 집에서 며칠동안 연구해본 결과 전처리 과정이 꽤나 난감했다. 사실 데이터를 엮어서 표현하기 위해 가장 중요한 것은 기준인데 우리나라는 주소체계가 참 복잡하다. 신우편번호, 구우편번호, 행정동기준, 법정동기준 등 여러 기준이 있고 (게다가 행정구역이 통폐합이 된다.) 각 데이터가 여러 곳에서 독립적으로 제공하기때문에 데이터를 이어붙이기가 참 애매하다.

그나마 행정안전부에서 제공하는 것들끼리는 기준이 같기 때문에 행정동-법정동 매핑 데이터와 주민등록기준 세대수, 인구수 데이터를 붙일 수가 있었다. (하지만 어느정도 전처리 과정을 통해 가공이 필요하다.)

필자가 사용한 행정안전부 데이터이다.
링크바로가기 - https://drive.google.com/open?id=1_eBNYuvm6EVWFnQ2fPpebNUzCjCDXa0C

먼저 행정안전부>업무안내>지방자치분권실>주민등록 및 인감>주민등록 및 인감에서
시도, 시군구, 행정동/법정동 코드 기반의 데이터를 다운받는다.

링크 : http://www.mois.go.kr/frt/bbs/type001/commonSelectBoardList.do?bbsId=BBSMSTR_000000000052

여기에서 KIKmix.YYYYMMDD를 다운받아보면 행정동과 법정동(리 단위까지)을 매핑해놓았다. 이를 법정동 기준으로 사용하기 위해서는 조금 가공해야한다.

먼저 우리나라 행정구역코드는 다음과 같은 규칙으로 이루어져있다.
총 10자리이며 시도2자리+시군구2자리+읍면동4자리+리2자리인 것 같다. (아니면 참 난감해진다.) 따라서 법정동 기준으로 하기 위해서 5열의 법정동 코드 끝 2자리를 모두 '00'으로 치환한 후 중복을 제거하면 된다.

대한민국 법정동 코드를 조회할 수 있는 링크이다.
https://www.code.go.kr/index.do

위의 링크에 몇가지 케이스를 검증해보면 '%)', '%리'가 존재하고 이를 REPLACE(법정동,행정동)으로 치환해주도록 했다.

위의 과정을 처리하는 파이썬 코드는 다음과 같다.

import urllib.request
import time
from openpyxl import load_workbook
import os

try:
    if not(os.path.isdir("map_json")):
        os.makedirs(os.path.join("map_json"))
except OSError as e:
    print("Failed to create directory!!!!!")
    raise

angelEx=load_workbook(filename='KIKmix.20181008.xlsx')
sheet = angelEx.active
arr = []

for i in sheet.iter_rows(min_row=2):
    arr.append(i[4].value[0:8]+'00')
    
arr2 = list(set(arr))

"""
쉐이프 api 호출 부분
"""

5번째 열을 읽어서 끝 2자리를 모두 '00'으로 치환 후 배열에 넣고 중복을 제거했다.
이렇게 유니크한 법정동 코드를 구했으니 이를 이용하여 api나 크롤링을 하여 주소나 지도 쉐이프를 구하면 된다.

대략 글을 쓰는 기준으로 법정동코드는 5338개가 존재한다. 같은 파일에서 유니크한 행정동 코드는 3858개가 존재했다. 따라서 행정동-법정동이 M:N 관계라고 할 지라도 행정동 코드 1개에 법정동 코드가 N개가 매핑될 가능성이 더 높다는 것이다.
파일 전체를 db에 올리고 이에 따른 5338개에 매핑되는 행정동 코드도 남겨놓도록 하자.
다행히 행정동 코드가 더 적기때문에 이 과정은 문제가 없을 것이고 행정동 코드가 있어야 인구수와 세대수를 붙일 수 있기때문이다.




이제 이 법정동 코드에 세대수와 인구수를 붙이는 방법이다.
먼저 행정동 기준 세대수와 인구수 데이터를 구해야한다.

데이터를 구할 수 있는 행정안전부 주소는 다음과 같다.

링크 : http://www.mois.go.kr/frt/sub/a05/totStat/screen.do

이 곳에서 인구수와 세대수 파일을 다운 받아서 필요한 데이터만 병합하였다.
구글 드라이브에 올려놓은 ingu_sedae_merge.csv 파일이다. 병합하는 과정은 코드로 짜는 것보다 수기로 하는 것이 빠르다.
(참고로 인구수는 생산가능 인구(만15~64세)수의 총합이다.)

저 파일을 열어보면 컬럼 하나에 "서울특별시 강북구 수유제1동(1130561000)"이 모두 들어가있다. 따라서 이 파일에서 행정코드, 시도, 시군구, 읍면동을 분리하는 작업이 필요하다.

코드는 다음과 같다.

import pandas as pd
from pandas import DataFrame, Series

csv = pd.read_csv("ingu_sedae_merge.csv", encoding = 'euc-kr')
columns = ['HJ_CODE','SD','SSG','EMD','SPL_POP_CNT','GEN_CNT']
df = DataFrame(columns=columns)

hjgg_code = ''
sd = ''
sgg = ''
emd = ''
spl_pop_cnt = ''
gen_cnt = ''
a = ''
b = ''
c = ''

for i, row in csv.iterrows():

    if(len(row.iloc[0].split('(')[0].split(' ')) == 3):
        hjgg_code = row.iloc[0].split('(')[1].split(')')[0]
        sd = row.iloc[0].split('(')[0].split(' ')[0]
        sgg = row.iloc[0].split('(')[0].split(' ')[1]
        emd = row.iloc[0].split('(')[0].split(' ')[2]
        spl_pop_cnt = row.iloc[1]
        gen_cnt = row.iloc[2]
        
        df = df.append(Series([hjgg_code,sd,sgg,emd,spl_pop_cnt,gen_cnt]
        ,index=columns), ignore_index=True)

        print(hjgg_code)
        print(sd)
        print(sgg)
        print(emd)
        print(spl_pop_cnt)
        print(gen_cnt)
    
    if(len(row.iloc[0].split('(')[0].split(' ')) == 4):
        hjgg_code = row.iloc[0].split('(')[1].split(')')[0]
        sd = row.iloc[0].split('(')[0].split(' ')[0]
        b = row.iloc[0].split('(')[0].split(' ')[1]
        c = row.iloc[0].split('(')[0].split(' ')[2]
        sgg = b+' '+c
        emd = row.iloc[0].split('(')[0].split(' ')[3]
        spl_pop_cnt = row.iloc[1]
        gen_cnt = row.iloc[2]
        
        print(hjgg_code)
        print(sd)
        print(sgg)
        print(emd)
        print(spl_pop_cnt)
        print(gen_cnt)

        df = df.append(Series([hjgg_code,sd,sgg,emd,spl_pop_cnt,gen_cnt] ,index=columns), ignore_index=True)
        
print("완료")
df.to_csv(r'result.csv',index=False,encoding='euc-kr')

결과는 다음과 같다.

이제 위에서 구한 행정동-법정동 매핑 데이터(+@ map polygon 등)와 아래에서 구한 인구, 세대 데이터를 매핑할 수 있하여 법정동 기준으로 지도를 표현할 수 있게 되었다.
추가적으로 엑셀 파일들을 한번에 merge하는 코드는 시간이 나면 작업하도록 하겠다.
하지만 수동으로 작업하는 것이 빠르기 때문에 안했다.


정리하면 다음과 같다.
1. 법정동으로 지도와 인구수, 세대수를 표현하고싶다.
2. 행정동-법정동 매핑 데이터를 구한다.
3. 행정동 기반 인구수, 세대수 데이터를 구해서 2번의 법정동에 매핑한다.
(M:N관계로 인한 1/N + SUM 과정이 필요)
4. 2번 법정동 기준으로 MAP POLYGON(MAP SHAPE)를 구해서 지도를 그리고 데이터를 입힌다.