MyBatis에서 #과 $의 차이 ( Difference between # and $ in MyBatis )

MyBatis에서 #과 $의 차이에 대해서 포스팅을 하려고 한다.

XML파일에 작성된 쿼리가 DB에서 수행될 때 Bind 변수(#)와 Literal 변수($)에 따라 DB 조회 시 성능차이를 야기할 수 있다.


먼저 Literal 변수로 작성한 쿼리이다.

위와 같은 경우 만약 USER_ID가 111일 경우 WHERE USER_ID = '111'으로 바뀌고 USER_ID가 222일 경우 WHERE USER_ID = '222'로 파싱된다. 이를 하드 파싱이라고 하며 DB는 바뀌는 파라메터에 따라 Shared Pool/Library Cache에서 다른 쿼리로 인식하여 매번 Hard Parsing이 이루어진다. 매번 실행계획이 바뀔 수 있다.


반면 Bind 변수로 작성한 쿼리이다.

바인드 변수로 작성된 쿼리의 경우 WHERE USER_ID = :B 형태로 인식하여 최초 1회 Hard Parsing이 이루어지고 그 다음부터는 캐시에서 가져오기 때문에 빈번한 Hard Parsing으로 인한 성능상 단점을 보완할 수 있다. 매번 실행계획이 같다.

이 외에도 Bind 변수를 활용한 쿼리는 SQL Injection을 예방할 수 있다.


하지만 Bind 변수로 작성된 쿼리가 좋은가.


Bind 변수로 작성된 쿼리는 매번 동일한 실행계획을 갖게 된다.
가령 다음과 같은 상황이 있을 수 있다.

위에서 제시한 예제 테이블(TBL)에서 USER_ID가 PK가 아니다.

USER_ID='111', '333' 등 USER_ID가 홀수인 경우 각각 row수가 10건씩 존재한다.
반면 USER_ID='222', '444' 등 USER_ID가 짝수의 경우 row수가 1000건 혹은 그 이상이라고 가정하자.

이때 USER_ID가 홀수인 경우는 index scan이 유리하고 짝수인 경우 full scan이 유리할 것이다.

리터럴 변수($)로 쿼리를 수행한 경우 쿼리 수행시 매번 다른 쿼리로 인식하여 DB 옵티마이저에 의해 홀수일 때는 Index scan을 하고 짝수일 때는 Full scan을 할 것이다.
반면 바인드 변수일때는 일정한 실행계획에 의해 홀수/짝수일 때 조회 속도가 크게 차이가 날 것이다.
즉 무조건 바인드 변수가 옳은 것은 아니라고 할 수 있다.

그럼에도 불구하고 하드파싱을 막는 것과 일정한 실행계획을 갖는 다는 것이 큰 이점인 것같다. 만약 가까운 미래(?)에 DB가 발전함에 따라 파싱이든 실행계획이든 알아서 판단해주는 미래가 온다면 리터럴이든 바인드든 상관없이 쓰게 되는 날이 올 수도 있지않을까

I got to know the problem of Unix time ( x86 server datetime rage - 1970-01-01 00:00:00 ~ 2038-01-19 03:14:08 )

MSSQL에서 POLYBASE로 하둡에 데이터를 떨구려고 하는데 아래와 같은 에러 메시지가 떴다.

메시지 107090, 수준 16, 상태 1, 줄 4
Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed.
Column ordinal: 1, Expected data type: DATETIME2, Offending value: TemporalValue: 347125824000000000 (Column Conversion Error), Error: Error converting from RCFile Type DATETIME2(0) NOT NULL to Sql type Timestamp.

확인해보니 날짜컬럼(DATETIME2)가 문제였고 문제되는 데이터를 찾아보니 1111-11-11 00:00:00 이런식의 데이터가 많았다.

아래는 위키 내용이다.
https://ko.wikipedia.org/wiki/%EC%9C%A0%EB%8B%89%EC%8A%A4_%EC%8B%9C%EA%B0%84

중략...
32비트로 표현된 유닉스 시간은 1970년 1월 1일 00:00 (UTC)에서 2,147,483,647 (231 - 1) 지난 후인 2038년 1월 19일 03:14:08 UTC에 2038년 문제를 발생시킨다. 이는 산술 오버플로와 관련 있는 문제이다.

즉 날짜 가용범위는 1970-01-01 00:00:00 ~ 2038-01-19 03:14:08 인 것이다.

결국 아래처럼 최대, 최소값을 CASE 처리하였다.

CONVERT(DATETIME2(0),CASE WHEN 날짜컬럼 <= '1970-01-01 00:00:00' then '2000-01-01 00:00:00' WHEN 날짜컬럼 >= '2038-01-19 00:00:00' then '2038-01-19 00:00:00' ELSE 날짜컬럼 END)                            

마지막으로 2038년이 가까워질수록 무슨 일이 일어날지 궁금하다.

Hadoop Data를 Polybase로 CREATE EXTERNAL TABLE할 때 발생할 수 있는 에러 및 해결책

MSSQL에서 Polybase로 Hadoop Data를 끌어올 때 어디서 에러가 나는지 찾기가 너무 힘들어서 골치가 아팠다. 사실 하둡에 데이터를 떨구는 사람과 Polybase로 가져가는 사람이 동일하다면 원천 데이터 타입을 알고있기 때문에 원인을 찾기 쉽겠지만 떨구는 사람과 가져가는 사람이 다르다면 데이터 타입을 모르는 상황에서 에러를 잡는데 한참 걸릴 것이다. 실제로 한참 걸렸다.

가령 다음과 같은 상황이다.
Hadoop에 Parquet Format으로 떨궈진 데이터를 MSSQL에서 CREATE EXTERNAL TABLE 하는 경우에 아래와 같은 에러들이 발생할 수 있다.

1. java.lang.Integer cannot be cast to java.lang.Long

이 경우에는 int로 선언되어야 하는데 bigint로 선언된 경우이다.
따라서 bigint -> int로 바꿔주자.

2. java.lang.Long cannot be cast to java.lang.Integer

이 경우에는 bigint로 선언되어야 하는데 int로 선언된 경우이다.
따라서 int -> bigint로 바꿔주자.

3. java.lang.Double cannot be cast to parquet.io.api.Binary

이 경우에는 float로 선언되어야 하는데 decimal(a,b)로 선언된 경우이다.
따라서 decimal(a,b) -> float로 바꿔주자.

4. java.lang.Double cannot be cast to java.lang.Integer

이 경우에는 float로 선언되어야 하는데 int로 선언된 경우이다.
따라서 int -> float로 바꿔주자.

이 외에도 Arithmetic overflow error converting tinyint to data type TINYINT. 식의 에러가 뜰 때에는 int가 맞는 타입인데 tinyint로 선언된 경우이다. 즉 tinyint -> int로 바꿔주자.

사실 윗 부분까지는 근본적인 해결책이 아니다. 일일이 컬럼에 경우의 수를 대입해서 풀지 않기 위해서는 하둡에 떨궈진 데이터의 스키마를 보고 즉시 해결할 수 있다.

제플린에서 df.printSchema()를 통해 dataframe의 스키마를 다음처럼 찍어볼 수 있다.

root

 |-- col1: integer (nullable = true)

 |-- col2: long (nullable = true)

 |-- col3: long (nullable = true)

 |-- col4: double (nullable = true)

 |-- col5: double (nullable = true)

 |-- col6: double (nullable = true)

 |-- col7: integer (nullable = true)

 |-- col8: string (nullable = true)

즉 이런 경우에는 다음처럼 생성하면 된다.

-- integer -> int
-- long -> bigint
-- double-> float
-- string -> varchar

CREATE EXTERNAL TABLE TEST_TBL (

    [col1] int NULL, 

    [col2] bigint NULL, 

    [col3] bigint NULL, 

    [col4] float NULL, 

    [col5] float NULL, 

    [col6] float NULL, 

    [col7] int NULL, 

    [col8] varchar(50) NULL

)

WITH (LOCATION='/user/hello/world/map/seoul/gangnam/201906',

      DATA_SOURCE = HDFS_DS,

      FILE_FORMAT = HDFS_FF_PARQUET,

      REJECT_TYPE = PERCENTAGE,

      REJECT_SAMPLE_VALUE = 10000,

      REJECT_VALUE = 0.1

)

SELECT * FROM TEST_TBL ;

데이터 타입이 잘 맞았다면 조회가 잘 될 것이고 그렇지 않다면 위에서 언급한 에러들이 출력될 것이다.

-
정리를 하면 먼저 하둡에 내린 데이터의 스키마를 알아낸 뒤에 테이블을 만들고 그게 여의치 않다면 에러를 보고 찾아낼 수 있어야 한다. 필자의 경우에는 후자였는데 먼저 해결하고 난 뒤에 근본적인 문제를 찾다보니 결국 스키마를 알아야겠더라.

How to make geojson in database(sql server) about korea sido/sigungu/dong polygon

참고 - 대한민국 우편번호별 쉐이프 구해서 Shape 합성 및 DB에 json 형태로 저장하기

포스팅을 조금 더 보강하여 highchart 등의 솔루션으로 실제 web 상에서 구현까지 해보려고 한다. 따라서 위의 포스팅을 참고하면 도움이 될 수 있다. 

- 시작 -
이번 포스팅은 How to make KOREA map data in mssql 이다.
대한민국 우편번호 폴리곤(좌표)으로 시도, 시군구, 읍면동 GEOJSON을 만들고 이를 MSSQL에 저장한다. MSSQL은 Geojson을 저장할 수 있도록 데이터 타입을 제공하며 Polygon Merge, simplification 등의 Geometry 관련 함수들을 제공하기 때문에 Daily batch job을 통해 원하는 결과물로 update가 가능하다.

먼저 다운받은 폴리곤은 시도레벨로 17개이다.

이를 qgis로 열어보면 아래와 같이 나온다.

레이어에서 하나를 선택하고 속성을 들어가보면 인코딩이 UTF-8로 되어있는데 이를 SYSTEM으로 바꿔주자.

이제 17개 시도를 geojson으로 추출할것이다.

먼저 서울특별시를 11.geojson이라는 파일로 추출하였다.

나머지 16개 시도도 똑같이 geojson으로 추출하자.
추출한 총 17개 geojson이다.

11.geojson 파일을 열어보면 다음과 같다.

json 형태로 저장되었으며 Beatify로 보니 features 하위에 BAS_ID(우편번호) 레벨로 나뉘어져있음을 확인할 수 있다.

저번 포스팅에서는 이를 python으로 파싱하여 db에 올리기 쉽게  csv형태로 저장하였다.
이번에도 똑같이 parser를 만들지만 node js로 만들어보자.

geojson_parser.js 파일은 포스팅 하단에 링크를 올려두었다.

저장된 11.csv 이다. 인코딩에 문제가 있어서 ansi로 다시 저장하였다.
총 17개의 csv가 나왔으며 일일이 파싱을 해주었는데도 컴퓨터가 뜨거워졌다..ㅠㅠ

mssql에 올린 결과는 다음과 같다.

총 17개의 테이블이 생성되었고 이를 하나로 만들자.

CREATE TABLE [dbo].[KOREA_POLYGON](
[code] [varchar](50) NOT NULL,
[CTP_KOR_NM] [varchar](50) NOT NULL,
[SIG_KOR_NM] [varchar](50) NOT NULL,
[BAS_ID] [varchar](50) NOT NULL,
[type] [varchar](50) NOT NULL,
[coordinates] GEOMETRY NULL
) ON [PRIMARY]

coordinates 컬럼을 GEOMETRY로 만들었다. varchar(max)에서 "만 빼주면 geometry가 되기 때문이다.

이제 KOREA_POLYGON 이라는 테이블에 데이터를 모은다.

INSERT INTO KOREA_POLYGON
SELECT *
FROM (
SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[11]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[26]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[27]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[28]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[29]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[30]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[31]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[36]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[41]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[42]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[43]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[44]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[45]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[46]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[47]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[48]
UNION ALL SELECT code, CTP_KOR_NM, SIG_KOR_NM, BAS_ID, type, REPLACE(coordinates,'"','') coordinates FROM GEOJSON..[50]
) A

실제로 데이터가 들어간 모습이다.

한번 간단하게 대한민국 전체 지도를 만들어보자.
SELECT code
   , geometry::UnionAggregate(coordinates.MakeValid().Reduce(100).STBuffer(1000)) AS WKT
FROM KOREA_POLYGON
GROUP BY code

대략 모양이 처음 qgis에서 추출하기 전의 모습과 비슷하다.

현재 우편번호별로 polygon이 저장되어있고 원하는데로 우편번호끼리 합성을 하여 지도를 개편할 수 있다.
이제 이를 화면에 그려보는 작업을 진행할 것이다.
일단 생각으로는 포털에서 제공하는 api를 활용하지 않고 highchart같은 솔루션을 활용하여 그려볼 생각이다.

학부시절 컴퓨터그래픽스(opengl)을 한 것이 작업할 때 검색 키워드를 찾는데 도움이 되었다.
작업 내용은 > 구글 드라이브 < 에 올려놓았다.