MSSQL - When do select into query, insert fails, but the table is created. why!?

다음은 select into 쿼리이고 #tmp라는 테이블에 'A'를 넣기 위함이다.
하지만 bigint로 convert가 되지 않아서 데이터는 들어가지 않는데 #tmp 테이블은 생성되었다.

테스트해보자.

SELECT CONVERT(BIGINT, 'A') col1 INTO #tmp 를 수행하면 분명 convert error가 발생한다. 그리고 바로 이어서 #tmp를 조회해보면 테이블이 생성되었음을 확인할 수 있다.

그럼 혹시 그럼 혹시 convertable variable와 unconvertable variable를 union all로 묶으면 한개라도 들어갈까?

위와 동일한 결과를 얻을 수 있었고 임시 테이블만 생성이 되는 것을 확인할 수 있다.

- 확인 결과(추가)
auto commit mode에서는 select ... into 쿼리는 2개의 트랜잭션으로 이루어져있는데 첫번째는 테이블을 생성하고 두번째는 데이터를 로드한다. 따라서 후속 삽입이 실패하더라도 테이블이 생성된 것은 commit이 된다.

sql server 과거 버전에서는 2개의 작업이 단일 트랜잭션이었고 그 결과 시스템 테이블의 locking&blocking이 유명했다고 한다.

Cannot create multiple column index in Cassandra and allow filtering option

카산드라는 복합키 인덱스 생성이 불가능하다.

왜 그런지 알아보기 전에 카산드라의 데이터 저장구조에 대해서 보자.

다음과 같은 테이블을 생성했다.

CREATE TABLE test.test_tbl (

    col1 text,

    col2 text,

    col3 text,

    col4 text,

    PRIMARY KEY (col1, col2)

);

pk를 col1, col2로 지정했다. pk는 multiple column이 가능하다.

카산드라의 경우 partition key, clustering key가 존재하는데 pk를 지정할 때 partition key, clustering key 순으로 지정한다.

partition key는 hash값에 따라 각 노드에 분산시키고 이를 괄호로 묶어주어 분산시킬 수 있다. 이어서 나오는 키는 clustering key로 노드에서의 데이터 정렬이다. 

카산드라는 where절에 partition key를 지정해서 노드를 찾아가야한다. design essue이다.

where col1 = '1'은 잘 된다는 의미이다. 하지만 where col2 = '1'이나 col3 = '1' 등 partition key를 지정을 하지 않으면 다음과 같은 에러가 뜬다.

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

그럼 혹시 인덱스를 만들면 될까?

CREATE INDEX idx_test_tbl_col2 ON upsell.test_tbl ( col2 );

이 경우에는 select * from test_tbl where col2 = '1'; 이 잘 동작한다.

하지만 select * from test_tbl where col2 = '1' and col3 = '1'; 동일한 에러가 뜬다.

그렇다면 col3에도 동일하게 인덱스를 만들어주면 where col2 = '1' and col3 = '1'가 될까 테스트해보자.

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

역시 동작하지 않는다.

datastax 공식문서에 보면 다음 내용이 있다.

이를 피하기 위해서는 ALLOW FILTERING 옵션을 사용하라고 한다.

select * from test_tbl where col2 = '1' and col3 = '1' ALLOW FILTERING;

하지만 allow filtering은 클러스터의 모든 노드에 query를 날리기 때문에 성능제약이 있다. 카산드라의 경우 hash key를 활용하기 때문에 성능상의 이점이 있는데 이를 포기하는 쿼리가 되는 것이다.

Artificial Intelligence - Gradient descent

대부분 딥러닝을 입문할 때 가장 처음 접하는 개념은 경사하강법, Gradient descent다.
오늘은 그 Gradient Descent에 대해서 포스팅을 해보려고 한다.

ML/DL의 기초가 되는 것이기 때문에 한번은 봐둘만 하다.


단일 선형 회귀로서 공부한 시간에 따라 성적이 매겨진다고 가정한다.

이는 공부시간(x), 성적(y)로 두고 1차 방정식 y=ax+b로 표현할 수 있다. 이는 하나의 직선이 그려지고 잘만 그려진다면 공부시간에 따른 성적을 예측할 수 있을 것이다.

그럼 어떻게 방정식의 a와 b를 구할 수 있을까? 결국 반복시키며(학습시키며) 찾아내는 과정이 이번 포스팅의 핵심이다.


직선 하나가 정해져있고 x와 y를 정확하게 안다면 기울기 a를 구하는 공식은 y의 증가량/x의 증가량으로 구할 수 있다. 하지만 우리는 직선이 어떻게 그려져야하는지 모르고 데이터(x, y)만 주어졌기 때문에 대략적인 선을 긋고 이를 조금씩 움직이면서 적당한 방정식을 만들어내가는 것이 목표이다.

평균제곱오차라는 개념을 활용할 것이며 이 오차를 줄이는 방향으로 움직일 것이다.

오차(error)는 (실제 y - 예측 y)이고 이를 제곱하여 평균을 내자. 제곱하는 이유는 데이터의 성격에 따라 값이 +- 왔다갔다 할 수 있어서 제곱을 함으로써 값은 모두 양수가 되고 또한 오차가 클 수록 패널티를 부여한다고 생각할 수 있다.

아무튼 그 오차제곱의 합을 평균내는 것을 평균제곱오차(Mean squared error - mse)라고 하며 그 mse는 다음과 같다.

mse = sum((실제 y - 예측 y)^2) / 데이터의 갯수 n 가 된다.
mse = 1/n * sum((y-ax-b)^2)이다.


이제 mse를 줄이는 방향으로 움직여보자.

a와 b를 변경할 것이기 때문에 각각 어느 방향으로 얼마만큼 이동시킬 것인가 하는 문제가 있다. 위의 수식은 a의 입장에서 보면 a에 대한 2차 방정식이다.(b도 마찬가지이다.)

a와 mes간 관계를 그래프로 그려보면 다음과 같다.

다시 처음으로 돌아와서 error를 최소화 하기 위해서는 그림에서처럼 그래프의 접선의 기울기가 0인 부분, 즉 볼록한 아래지점이 a값이면 좋을 것 같다.

그럼 a의 시작점을 임의로 지정했을 때 어떻게 움직이면 될까? 접선의 기울기가 음수이면 + 방향으로, 양수이면 - 방향으로 움직이면 된다.

그럼 얼마만큼 움직여야하나.
예를 돕기위해 그래프의 가로를 시간, 세로를 위치라고 보면 속도는 위치의 변화량 / 시간의 변화량이 된다. 순간 속도는 위치를 시간으로 미분한 것이다. 그럼 해당 그래프의 한 점을 미분하면(시간이 0으로 수렴하면) 해당 지점에서의 순간 속도가 된다. 우리는 그 순간 속도에 learning rate(LR)라는 미리 정의한 상수를 곱한 만큼의 거리만큼 이동해주도록 하자. LR은 사람이 정하는 것이기 때문에 적당한 값으로 learning rate를 정의해야 한다.

mse = 1/n * sum((y-ax-b)^2)를 a에 대해서 편미분하면 -2/n*sum((y-ax-b)*x)이다.
그리고 y-ax-b는 error이기 때문에 치환하면 -2/n*sum(error*x)로 쓸 수 있다.
즉 a_diff = -2/n*sum(error*x)이다.

기울기가 음수이면 + 방향으로, 양수이면 - 방향으로 움직이기 위해서 편미분한 결과(a_diff)를 LR만큼 곱해서 빼주면 마침내 a = a - LR*a_diff가 완성되고 이를 통해 a를 업데이트를 해주면 된다.
이를 계속해서 반복해가면서 a를 계속 업데이트해준다면 결국 a가 적당한 지점에 도달할 수 있을 것이다. b역시 똑같이 편미분을 해서 b_diff를 구하자.

b_diff = -2/n*sum((y-ax-b)) = -2/n*sum(error)

정리하면 a와 b는 다음처럼 업데이트를 할 수 있다.
a = a - LR*a_diff, b = b-LR*b_diff 두 식을 얻을 수 있다.

그럼 코드로 보도록 하자.

import numpy as np

import pandas as pd

data_set = [[1,80],[2,85],[4,89],[8,93]]

x = [i[0] for i in data_set]

y = [i[1] for i in data_set]

origin_x = np.array(x)

origin_y = np.array(y)

a=1 #init a

b=50 #init b

LR = 0.005 #learning rate

for i in range(0,2001):

  predict_y = a * origin_x + b #predicted y

  err = origin_y - predict_y #error

  a_diff = -1*(2/len(x))*sum(origin_x*err)

  b_diff = -1*(2/len(x))*sum(err)

  

  a = a-LR*a_diff

  b = b-LR*b_diff

  if( i % 100 == 0):

    print("i=%.f, a=%.04f, b=%.04f" %(i, a, b))

결과

대략 2000번 시도하니 a는 1.7, b는 80로 수렴하고 있다. 이로써 y=ax+b 수식을 구할 수 있었고 이를 통해 선형회귀(linear regression)를 간단하게 구현해보았다.
마찬가지로 y=ax1+bx2+c의 경우처럼 a, b, c를 구하는 방법도 똑같이 구하면 된다.

하둡 - 오류가 발생한 블록 처리하기 (Status : Corrupt)

간혹 장비 노후라던지의 문제로 인해 하둡 블록이 문제가 되는 경우가 있다.
블록에 오류가 발생할 경우 Corrupt라는 메시지가 뜨게 되는데 데이터의 원본이 존재하거나 다른 방법으로 복구가 가능하다면 문제가 되지 않는다.

하지만 어떠한 이유로 든 간에 replication 1로 생성된 파일이 Corrupt가 발생한다면 해당 파일은 더이상 사용이 불가능하기 때문에 지워줘야한다. (과거 Polybase로 HDFS에 내리는 경우 replication 1로 떨궈졌다.)

다음 명령어를 통해 하둡 파일 시스템 상태를 체크한다.
hadoop fsck /

Status : CORRUPT가 확인되고 블록 1개가 문제가 되었다.
(replication이 1이고 테스트 파일이기 때문에 더 이상 필요가 없어서 지우기로 한다.)

hadoop fsck -delete 혹은 -move를 통해 해당 파일을 정리한다.

-move를 하면 /lost+found로 파일이 이동하는데 어차피 필요가 없으니 -delete를 하도록 한다.

hadoop fsck -delete

완료되면 Status가 Healthy로 나와야하는 것 같은데 그게 아닌가.. 뭐 어찌됐든 -delete를 하고 node overview에서 에러가 사라졌고.. 다시 hadoop fsck / 를 날려보았다.

하둡 상태가 정상(HEALTHY)로 돌아왔다.

Tip - Cassandra Stress with ZING (Readynow Option)

이전 포스팅에서 카산드라에 g1gc와 ZING을 테스트 했었는데 사실 정확한 테스트를 하려면 ZING을 제대로 이해하고 테스트를 해야한다.

ZING의 경우에는 compile time이 존재하기 때문에 warm up이 필요하고 일정량 이상의 스트레스를 주어야(시간이 들여야) 퍼포먼스가 어느 정도 궤도에 오르게 된다.

하지만 카산드라 테스트 시 ZING의 신박한 기술인 Readynow Option을 통해 즉시 좋은 퍼포먼스를 확인할 수 있다.

먼저 설명을 하자면 한번 테스트를 하고 log를 떨군다음 이후 테스트에서 해당 log를 읽어서 warm up time을 없애는 것이다! (즉 두번 수행한다.)

출처는 다음과 같다. (azul 공식 문서)

즉 stress test tool을 활용할 때 ZING으로 테스트를 하려면 readynow 옵션을 주어 정확한 테스트가 가능하다.

순서는 cassandra의 stress-stress 스크립트를 수행할 때 -XX:ProfileLogOut을 주면서 output log를 생성할 위치를 알려주고 로그가 잘 떨어지는지 확인한다. 이후 로그가 잘 떨어지는지 확인이 되면 -XX:ProfileLogIn 옵션을 주고 output 로그를 읽도록 스크립트를 변경하면 된다.

테스트 결과 compile time이 없기때문에 즉시 일정수준 이상의 Performence가 나온 것을 확인할 수 있었고 정말 신기한 기능이라는 생각이 들었다.


마지막으로 카산드라를 테스트 할 때에는 메모리와 cpu를 잘 고려해서 테스트를 해야한다. 예를 들어 read performence를 테스트하려고 1m부터 100m까지 늘려가면서 테스트를 할 때 잘 관찰해보면 thread count를 올려가면서 테스트하는 것을 확인할 수 있다. 하지만 서버 사양에 따라 적절한 thread count가 존재하기 때문에 한번은 크게 돌려보면서 어느 수준부터 overload가 발생하는지 확인할 필요가 있다. 필자의 경우 thread가 24개 정도에서 overload가 발생했기때문에 테스트 할 때 일정하게 -rate threads=24 옵션을 주고 테스트를 진행하였다.

번외로 zing trial version으로 테스트를 해본 결과 성능이 좋았고 특히 GC 수행시간이 아주 일정하게 5ms이하로 유지하는 것을 보고 놀라웠다. 참고로 G1GC의 경우 약 10~15ms 왔다갔다 했다.

Artificial Intelligence - Perceptron

퍼셉트론은 신경망의 기본이 되는 알고리즘(이론)이다.

가령 아래의 +- 오브젝트들을 분류해본다.

직감적으로 선을 하나 그려 분리할 수 있을 것이다. linearly separable 하기 때문이다.

이는 퍼셉트론을 활용하여 분류할 수 있는데 퍼셉트론은 N개의 인풋을 받아서 1개의 아웃풋을 내놓는데 이를 도식화해보면 다음과 같다.

인풋 x1~xn에 가중치 w를 주어 P(x)라는 아웃풋을 내놓는 뉴런이다.

그리고 그림처럼 input Layer, output Layer가 존재한다.

퍼셉트론을 활용하여 Linearly separable한 문제를 푸는 방법은 아래처럼 구현할 수 있다.

아주 기본적인 코드이지만 문제가 linear하다면 느릴지라도 결국 선분 하나를 찾아낼 수 있을 것이다.

이를 활용하여 AND, OR, XOR 문제를 생각해보자.

if w1x1 + w2x2 + b >0 then 1 else 0 

먼저 AND GATE의 생김새이다.

위의 AND GATE는 적절한 선분을 그어 1과 0의 값을 구현할 수 있다.

다음 OR GATE도 마찬가지이다.

w1, w2, b는 적절한 값으로 주고.. 파이썬 코드로 보면 다음과 같다.

def AND_GATE(x1, x2):
    w1=0.5
    w2=0.5
    b=-0.7
    result = x1*w1 + x2*w2 + b
    if result <= 0:
        return 0
    else:
        return 1

def OR_GATE(x1, x2):
    w1=0.6
    w2=0.6
    b=-0.5
    result = x1*w1 + x2*w2 + b
    if result <= 0:
        return 0
    else:
        return 1

def NAND_GATE(x1, x2):
    w1=-0.5
    w2=-0.5
    b=0.7
    result = x1*w1 + x2*w2 + b
    if result <= 0:
        return 0
    else:
        return 1

이런식으로 단층 퍼셉트론으로 AND, OR, NAND 정도는 구할 수 있다. 하지만 XOR GATE는 단층 퍼셉트론으로 구현할 수 없다. 우리는 디지털 회로 시간에 배운 GATE를 엮어서 XOR GATE를 만들어낸 경험을 되살려야 한다.

XOR GATE의 경우 서로 다른 값을 가지고 있을 때에만 1을 return 한다. 즉 x1, x2가 같으면 0을 return 한다.

XOR GATE는 다음처럼 표현될 수 있다.

XOR GATE는 1개의 직선으로 구분이 불가능하다. 하지만 AND, OR, NAND GATE를 조합해서 만들 수 있다.

그림 위키독스 참조

XOR_GATE의 코드는 다음처럼 볼 수 있겠다.

def XOR_GATE(x1, x2):
    s1 = NAND_GATE(x1, x2)
    s2 = OR_GATE(x1, x2)
    return AND_GATE(s1, s2)


이런 다층 퍼셉트론을 MLP(Multilayer Perceptron)라고 하며 입력층과 출력층 사이에 hidden layer가 존재한다는 것이 특징이다. 이러한 hidden layer를 n개를 쌓은 개념이 DNN(Deep Neural Network)이고 최근 인공 신경망의 기초가 되었다.

2019년 회고

블로그를 하면서 회고록을 한번 써봐야겠다 싶었는데 생각보다 쓰려고 하니 낯뜨겁다..

하지만 언젠가 내 과거의 글들을 보면서 여러가지 생각을 해보기 위해 기록을 남긴다.

2019년은 나에게 있어서 큰 터닝포인트가 될 것이라고 생각한다. 

먼저 업무적으로 2016년부터 DW일을 하다가 빅데이터 일을 하게 되었는데 기술적인 부분 뿐만 아니라 데이터적+개발적으로 큰 시야를 갖게 되었다. 다뤄보지않는 여러 오픈소스들, 그리고 카산드라나 모니터링툴을 도입하는 과정을 통해 많이 생각하고 배울 수 있었다. 아무튼 이런 기회를 준 분들께 감사드리고 더 많은 연구를 해보고 싶다.

2년전에 내가 어떤 커뮤니티에 대학원에 대해서 글을 올렸더라. 2년 후에는 어떤 방식으로든 대학원에 가서 더 배워보고 싶다고.. 그런데 올해 그 발언을 실현하게 되었다. 빅데이터, AI 전공이고 제대로된 논문을 써보고 싶다. 대학원을 병행하는게 쉬운 일은 아니지만 재밌는 만큼 보람이 있다.

공모전에서 상을 탔다. 통계청에서 주관한 SGIS 공모전인데 24점 출품작 중 9점 안에 뽑혔다. 뉴스에도 나왔다. 주도적으로 한 작업이기에 나름 애착이 갔던 작품이고 대략 매출+인구통계를 지도에 표현하는 것으로 이 작업을 하기 위해 크롤링, 좌표 폴리곤 합성, 인구 통계 데이터 가공 등을 연구하고 경험했다.

링크 

작품내용 : 통계청

뉴스 : 2019년 SGIS 활용 우수사례 공모결과

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

마지막으로 월 3회 기술 포스팅을 목표로 했었는데 이것도 나름대로 잘 지켰다.

블로그 자체에 큰 의미를 두는 것은 아니지만 스스로 약속을 지키기 위해 무언가를 꾸준히 하게 된다는 점에서 의미가 있었다고 생각한다.

PV가 꾸준히 증가하긴 하더라..

가장 많은 조회수를 기록한 글.. 생각보다 mssql 글이 많다.

가장 애착이 간 글 몇개만 뽑아보자면..

1. 하둡 3.1.2 설치

아무래도 하둡 3.대가 나온지 얼마 안되서 관련 자료도 없고 요즘 ambri 같은 걸로 설치하니 맨땅에 헤딩하듯 설치하는 글들이 별로 없어서 많이 들어왔던 것 같다.

2. 대한민국 우편번호별 shape 구해서 db에 json 형태로 저장하기

이건 나중에 공모전에도 출품해서 상 받은 내용이기도 하고.. 자료가 없어서 처음부터 끝까지 퇴근 후에 집에서 연구를 해와서 회사서 바로 적용할 수 있게 했던 추억이 새록새록..

3. 전통적 데이터웨어하우스 고찰

DW와 빅데이터 양쪽을 경험해보고 생각을 정리했던 글이다.

아무튼 카산드라 모니터링 툴, 카산드라 gc 테스트 등 다 애착이 가는데 그냥 바로 생각나는 것을 적어봤다.

2019년 이것 저것 실수한 것도 많았고 배운 것도 많았다.

2020년 올해는 해보고 싶은 것도 많고 업무적으로도 스파크 스트리밍 등 여러가지 난이도 있는 일도 계획되어 있고.. 학업적으로는 논문도 좀 읽어보고 내 논문도 준비하고.. 여러가지로 바쁠 것 같다.

2019년 여러 가지로 감사한 분들이 많았고 2020년도 화이팅하자.