Data – 페이지 2

연관 분석(feat. Python)

지난 포스팅에서 연관분석(Association Analysis)이 무엇인지, 어떻게 결과를 도출하는지 에 대한 이론적인 방법을 살펴보았다.

이제 실제로 연관분석을 Python으로 하나씩 구현해 보자.

분석을 하려면 데이터가 필요하다. 이번 예제에서는 Instacart라는 온라인 기반 농작물 배송 서비스 회사에서 공개한 2017년 9월에 발생한 주문 및 제품 정보에 대한 데이터셋을 다운받아 사용 할 것이다.

데이터는 “The Instacart Online Grocery Shopping Dataset 2017” 링크를 통하면 다운 받을 수 있다. 다운받은 데이터는 압축을 푼 후에 작업 폴더에 옮겨둔다.
(예제에서는 작업할 Project 폴더에 “./Dataset/Instacart”라는 경로에 데이터를 넣어 두었다.)

먼저, 분석할 데이터를 확인해 보자.

필요한 모듈과 함수, 고정 변수 등을 미리 정의해 둔다.

import pandas as pd
import numpy as np
import sys
from itertools import combinations, groupby
from collections import Counter
from IPython.display import display

# 데이터 파일(객체)이 어느정도 사이즈(MB) 인지 확인 하는 함수.
def size(obj):
    return "{0:.2f} MB".format(sys.getsizeof(obj) / (1000 * 1000))

# 파일 저장 경로
path = "./Dataset/Instacart"

Pandas를 이용하여 주문 데이터를 읽어서 데이터의 사이즈와 차원을 확인하고 실제 데이터를 살펴 보기 위해 상위 5개 데이터를 확인해 본다.

orders = pd.read_csv(path + '/order_products__prior.csv')
print('orders -- dimensions: {0};   size: {1}'.format(orders.shape, size(orders)))
display(orders.head())

orders -- dimensions: (32434489, 4);   size: 1037.90 MB

	order_id	product_id	add_to_cart_order	reordered
0	2	33120	1	1
1	2	28985	2	1
2	2	9327	3	0
3	2	45918	4	1
4	2	30035	5	0

주문 데이터 상위 5개

주문 데이터는 4개의 차원으로 약 3천만 건의 주문 정보를 담고 있으며, 총 데이터의 크기는 1GB라는 것을 알 수 있다. 4개의 차원은 주문번호, 상품번호, 카트에 담긴 순서, 재(추가)주문 상태를 나타내고 있다. 여기서 연관규칙을 찾을 때 필요한 데이터는 주문번호와 상품번호만 있으면 되기 때문에 주문번호를 인덱스로 하고 상품번호를 Value로하는 Series로 변환한다.

orders = orders.set_index('order_id')['product_id'].rename('item_id')
print('dimensions: {0};   size: {1};   unique_orders: {2};   unique_items: {3}'
      .format(orders.shape, size(orders), len(orders.index.unique()), len(orders.value_counts())))

dimensions: (32434489,);   size: 518.95 MB;   unique_orders: 3214874;   unique_items: 49677

차원이 4개에서 1개로 줄었고, 사이즈도 1GB에서 절반정도 줄어든 518MB가 되었다.

데이터가 준비 되었으니, 연관 규칙을 찾기 위한 프로그램(함수)들을 정의해보자.

연관규칙을 찾기 위해서는 지지도, 신뢰도, 향상도 지표를 확인하여 규칙의 효용성을 확인 해야 한다. 이 3개 지표를 계산해 내기 위한 함수를 먼저 정의한다.

# 단일 품목이나 품목 집합에 대한 빈도수를 반환한다.
def freq(iterable):
    if type(iterable) == pd.core.series.Series:
        return iterable.value_counts().rename("freq")
    else: 
        return pd.Series(Counter(iterable)).rename("freq")
    
# 고유한 주문번호 갯수를 반환한다.
def order_count(order_item):
    return len(set(order_item.index))

# 한번에 한 품목 집합을 생성하는 generator를 반환한다.
def get_item_pairs(order_item):
    order_item = order_item.reset_index().values
    for order_id, order_object in groupby(order_item, lambda x: x[0]):
        item_list = [item[1] for item in order_object]
              
        for item_pair in combinations(item_list, 2):
            yield item_pair            

# 품목에 대한 빈도수와 지지도를 반환한다.
def merge_item_stats(item_pairs, item_stats):
    return (item_pairs
                .merge(item_stats.rename(columns={'freq': 'freqA', 'support': 'supportA'}), left_on='item_A', right_index=True)
                .merge(item_stats.rename(columns={'freq': 'freqB', 'support': 'supportB'}), left_on='item_B', right_index=True))

# 품목 이름을 반환한다.
def merge_item_name(rules, item_name):
    columns = ['itemA','itemB','freqAB','supportAB','freqA','supportA','freqB','supportB', 
               'confidenceAtoB','confidenceBtoA','lift']
    rules = (rules
                .merge(item_name.rename(columns={'item_name': 'itemA'}), left_on='item_A', right_on='item_id')
                .merge(item_name.rename(columns={'item_name': 'itemB'}), left_on='item_B', right_on='item_id'))
    return rules[columns]

다음으로, 실제 규칙을 찾기 위해 위 지표를 구하는 함수들을 이용하여, 연관 규칙을 찾는 함수를 정의 한다.

# 미리 준비한 주문 정보(주문번호를 인덱스로 하고 상품번호를 Value로하는 Series)와 최소 지지도를 입력받아 연관 규칙을 반환한다.
def association_rules(order_item, min_support):

    print("Starting order_item: {:22d}".format(len(order_item)))

    # 빈도수와 지지도를 계산한다.
    item_stats             = freq(order_item).to_frame("freq")
    item_stats['support']  = item_stats['freq'] / order_count(order_item) * 100

    # 최소 지지도를 만족하지 못하는 품목은 제외한다. 
    qualifying_items       = item_stats[item_stats['support'] >= min_support].index
    order_item             = order_item[order_item.isin(qualifying_items)]

    print("Items with support >= {}: {:15d}".format(min_support, len(qualifying_items)))
    print("Remaining order_item: {:21d}".format(len(order_item)))

    # 2개 미만의 주문 정보는 제외한다.
    order_size             = freq(order_item.index)
    qualifying_orders      = order_size[order_size >= 2].index
    order_item             = order_item[order_item.index.isin(qualifying_orders)]

    print("Remaining orders with 2+ items: {:11d}".format(len(qualifying_orders)))
    print("Remaining order_item: {:21d}".format(len(order_item)))

    # 빈도수와 지지도를 다시 계산한다.
    item_stats             = freq(order_item).to_frame("freq")
    item_stats['support']  = item_stats['freq'] / order_count(order_item) * 100

    # 품목 집합에 대한 generator를 생성한다.
    item_pair_gen          = get_item_pairs(order_item)

    # 품목 집합의 빈도수와 지지도를 계산한다. 
    item_pairs              = freq(item_pair_gen).to_frame("freqAB")
    item_pairs['supportAB'] = item_pairs['freqAB'] / len(qualifying_orders) * 100

    print("Item pairs: {:31d}".format(len(item_pairs)))

    # 최소 지지도를 만족하지 못하는 품목 집합을 제외한다.
    item_pairs              = item_pairs[item_pairs['supportAB'] >= min_support]

    print("Item pairs with support >= {}: {:10d}\n".format(min_support, len(item_pairs)))

    # 계산된 연관 규칙을 계산된 지표들과 함께 테이블로 만든다.
    item_pairs = item_pairs.reset_index().rename(columns={'level_0': 'item_A', 'level_1': 'item_B'})
    item_pairs = merge_item_stats(item_pairs, item_stats)
    
    item_pairs['confidenceAtoB'] = item_pairs['supportAB'] / item_pairs['supportA']
    item_pairs['confidenceBtoA'] = item_pairs['supportAB'] / item_pairs['supportB']
    item_pairs['lift']           = item_pairs['supportAB'] / (item_pairs['supportA'] * item_pairs['supportB'])
    
    # 향상도를 내림차순으로 정렬하여 연관 규칙 결과를 반환한다.
    return item_pairs.sort_values('lift', ascending=False)

연관 규칙을 찾기 위한 데이터와 프로그램(함수) 준비가 완료 되었다.

연관규칙을 찾아보자.

%%time
rules = association_rules(orders, 0.01)

Starting order_item:               32434489
Items with support >= 0.01:           10906
Remaining order_item:              29843570
Remaining orders with 2+ items:     3013325
Remaining order_item:              29662716
Item pairs:                        30622410
Item pairs with support >= 0.01:      48751

Wall time: 6min 24s

출력된 결과를 확인해보자. 약 3천만 건의 주문 정보에서 최소 지지도 0.01를 넘는 약 4만 8천건의 연관 규칙을 찾아 내었고, 연관 규칙을 만들어 내는 데 걸린 시간은 6분 24초가 걸렸다는 것을 알 수 있다.

찾은 결과를 출력해 보자.

# 품목 ID를 보기 좋게 하기 위해서 품목 이름으로 바꿔준다.
item_name   = pd.read_csv(path + '/products.csv')
item_name   = item_name.rename(columns={'product_id':'item_id', 'product_name':'item_name'})
rules_final = merge_item_name(rules, item_name).sort_values('lift', ascending=False)
display(rules_final)

출력된 연관 규칙 테이블을 보면 다소 복잡해 보일 수 있으나, 맨 마지막 열의 향상도(lift)를 보면 품목 간의 관계를 확인 할 수 있다.

lift = 1, 품목간의 관계 없다.
*예: 우연히, 같이 사게되는 경우
lift > 1, 품목간의 긍정적인 관계가 있다.
*예: 같이 사는 경우
lift < 1, 품목간의 부정적인 관계가 있다.
*예: 같이 사지 않는 경우

출력된 향상도를 이용하여 결과를 분석해보자.

먼저, 품목 간의 긍정적인 관계인 향상도가 1 보다 큰 결과를 살펴 보자.

코티지 치즈는 블루베리 아사이 맛과 딸기 치아 맛을 같이 구매한다.
고양이 먹이는 치킨 맛과 칠면조 맛을 같이 구매한다.
요거트는 믹스 베리 맛과 사과 블루베리 맛을 같이 구매한다.

위 결과를 토대로 생각해 보면, 대부분 구입한 한 가지 품목에 대해서 다른 맛을 내는 같은 품목을 같이 구입한다는 것을 알 수 있다.

다음으로, 품목 간의 부정적인 관계인 향상도가 1보다 작은 결과를 보자.

유기농 바나나를 사는 경우 일반 바나나는 사지 않는다.
일반 품종의 아보카도를 구입한 경우 하스 아보카도를 구입하지 않는다.
유기농 딸기를 사는 경우 일반 딸기를 사지 않는다.

이번 결과에서는 구입한 한 가지 품목에 대해서 다른 생산과정 혹은 영양구성의 같은 품목은 같이 구입하지 않는다는 것을 알 수 있다.

이전 포스팅에서 언급했듯이, 단순히 나열된 구매 정보만으로는 확인 할 수 없었던 규칙들을 연관 분석을 통해서 알아 낼 수 있게 되었다. 이러한 정보를 바탕으로 고객들에게 제품을 추천 한다거나 상품의 배열을 바꿔 줄 수 있다면 상품 판매에 의미 있는 결과를 얻을 수 있을 거라 생각한다.

MS-SQL Cursor 사용하기

SQL 작업을 하다보면 조회된 쿼리 결과에 대해서 행 단위 작업이 필요할때가 있다. 이때, Cursor를 사용하면 효율적으로 처리 할 수 있다.

Cursor Command

DECLARE: Cursor에 관련된 선언을 하는 명령
OPEN: Cursor가 Query결과의 첫번째 Tuple을 포인트로 하도록 설정하는 명령
FETCH: Query 결과의 Tuple들 중 현재의 다음 Tuple로 커서를 이동시키는 명령
CLOSE: Query 수행을 모두 마친 후 Cursor를 닫기 위한 명령
DEALLOCATE: Close된 Cursor의 자원을 반환하는 명령

Cursor life cycle

Cursor Example

DECLARE @pColum_1 NVARCHAR(100), @pColum_2 INT, @pColum_3 BIT
DEClARE pCursor CURSOR
FOR
   select * from [Target Table]

OPEN pCursor

FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3

WHILE(@@FETCH_STATUS <> -1)
   BEGIN
      update [Target Table] SET Colum = @Colum_1 + 1
      where Colum_2 = @pColum_2 and Colum_3 = @pColum_3

      FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3
   END

CLOSE pCursor
DEALLOCATE pCursor

-유의사항-

Cursor를 사용하게 되면 내부적인 임시테이블을 사용하기 때문에 많이 사용하게 되면 DB성능에 영향을 미치게 된다. 되도록 사용량이 적은 시간때이거나 적은양의 데이터를 처리할 때 사용하는 것이 바람직 하다.

자연어 처리 기법과 활용

SF영화를 보면 인공지능이 사람의 말을 모두 알아듣고 이해하고 행동하는 것을 쉽게 볼 수 있다. 심지어 몇몇 영화에서는 보통 사람보다 더 인간적이고 철학적인 생각을 하는 인공지능 로봇들도 등장한다.

“정말 영화에서 나온 것처럼 인공지능이 사람의 언어와 감정을 모두 이해할 수 있을까? “

인간의 언어는 수천년의 역사를 가지고 다양하게 분화되고 발전해 왔다. 그리고 많은 부분 설명하기 힘든 감정이나 암묵적인 지식들로 구성되어 있기 때문에 정성적인 성질이 강하다. 그래서 정량적 데이터가 필요한 현재의 인공지능 학습 방법으로는 이러한 언어의 요소들을 이해시키기 어렵다.

그러면, SF영화에 나오는 것들은 허황된 이야기일 뿐인 것일까?

꼭 그렇지만은 않다. 우리 주변을 잘 살펴보자. 가장 가깝게 AI 스피커를 발견할 수 있을 것이다. 복잡한 대화는 아직 어렵지만 일상적이고 반복적인 간단한 말 들은 아주 잘 이해 한다.

예를들어 잠자리에 들기 전에 다음과 같은 대화를 할 수 있다.

나: (잠자리에 누우며)”OO야! 나 이제 잘래”

AI 스피커: “알람은 몇 시로 할까요?”

나: “내일 아침 7시에 깨워줘”

AI 스피커: “내일 아침 7시로 알람 설정 되었습니다.”

나: “방에 불 꺼줘”

AI 스피커 : (방에 불을 끄며)”수면에 좋은 음악을 틀어 드리겠습니다.”
(약 30분정도 잔잔한 음악 재생 후 종료됨.)

AI 스피커를 사용하기 전에는 이런 것들을 직접 손으로 해야 했다. 휴대폰에 알림을 맞추고, 잠들기 전에 들을 음악을 리스트에 담고 타이머를 맞춘다. 그러고 나서야 방에 불을 끄고 잠자리에 눕는다. 하지만 이젠 바로 잠자리 누워서 말로만 하면 된다.

“어떻게 가능한 것일까?“

이것을 이해 하기 위해서는 먼저 컴퓨터가 사람의 언어를 이해하기 위해서 시도 되었던 기계번역에 대해서 알아볼 필요가 있다.

초기에는 정해진 규칙에 의해서 번역을 해주는 ‘규칙기반(혹은 중성언어 기반)’으로 언어를 컴퓨터가 다루도록 하였다. 말 그대로 개발자가 정한 규칙에 의해서 원문을 타겟 언어로 번역해 주는 것인데, 앞서 언급한 말로 설명하기 힘든 감정적인 부분과 암묵적인 지식들을 개발자가 모두를 규칙으로 담기에는 한계가 있었다.

이러한 한계를 뛰어 넘기 위해서 나온 것이 ‘말뭉치 기반’으로 번역하는 방식이다. 말뭉치 기반은 크게 ‘예시 기반’과 ‘통계 기반’ 2가지로 나누어 진다.

‘예시 기반’은 원문과 번역문을 저장해 두었다가 똑같은 원문 요청이 왔을때, 이를 활용하는 방식이다. 그리고 ‘통계 기반’은 원문과 번역문의 상관관계 빈도수를 통계화하고 이 수치를 활용하는 방식이다.

말뭉치 기반으로 번역을 하게 되면, 사용을 하면 할 수록 새로운 말뭉치 데이터가 누적 되기 때문에 사람의 개입 없이 컴퓨터가 스스로 언어의 정보를 익히고 처리해준다. 뿐만 아니라 번역 완성도도 점점 높아지게 된다.

“어떻게 말뭉치 기반이라는 것이 앞서 언급한 사람 언어의 정성적인 부분을 상관관계 형태로 통계화 하고 수치로 만든다는 것일까?”

컴퓨터에게 ‘사과’와 ‘배’라는 두 단어를 보여준다면, 단지 유니코드 집합으로 해석 할 뿐 두 단어의 개념적 차이를 이해할 수는 없다.

그러면 유니코드가 아닌 컴퓨터가 이해 할 수 있는 수치를 사용해야 한다. 그것이 바로 벡터로 표현 하는 것이다. 벡터를 만드는 방식 중 하나인 One-Hot 인코딩 방식을 이용하여 간단히 설명해 보면, 다음과 같다.

문장에 의도를 파악하기 위해 필요한 요소가 [왕, 여왕, 남자, 여자]라면 ‘왕’를 표현하는 벡터는 [1, 0, 0, 0]이 되는 것이다. 그리고 ‘여왕’를 표현하는 벡터는 [0, 1, 0, 0]이 된다. 이런 식으로 벡터가 만들어지면 단어들이 실수 공간에 들어오기 때문에 각 단어들의 사이의 유사도를 측정 할 수 있게 된다. 그리고 문장속 단어의 의미 자체가 수치화 되어 있기 때문에 벡터 연산을 통해서 추론을 내릴 수도 있게 된다.

예를 들어 ‘왕(King)’에 대한 벡터에서 ‘남자(Male)’을 빼고 ‘여자(Female)’을 더하면, 벡터 연산을 통해서 ‘여왕(Queen)’이라는 결과를 얻을 수 있게 된다.

이로써 컴퓨터가 사람의 언어적 정보를 단순히 저장하고 읽는 것만 하는 것이 아니라 의도를 파악할 수 있게 된 것이다.

이러한 처리 방식을 통해서 많은 관심을 받게 된 분야가 대표적으로 챗봇이다. 챗봇이 사람의 말의 의도를 파악할 수 있게 되면서 단순한 작업에 대해서 많은 도움을 줄 수 있게 되었다.

최근 가장 성공적인 사례인 ‘코로나-19’ 챗봇을 이야기 해보자.

그 동안의 민원 처리 방식은 모든 민원 내용을 사람이 일일이 읽어보고 취합한 뒤 공지를 통해서 한번에 알리는 방식이 일반적이었다. 이런 경우 민원을 넣고 처리가 되길 기다려야 한다. 질병관리본부에 들어오는 코로나-19에 대한 민원도 마찬가지 였지만 챗봇 도입을 통해서 이러한 문제점에 대해서 많은 어려움을 해소 할 수 있었다.

민원을 하는 사람이 “자가격리자는 어떻게 해야 할까?”라는 질문이 들어오면, 챗봇은 자연어 처리를 통해 사람의 의도를 파악하고 질병관리본부에서 제공하는 생활 수칙 가이드를 찾아서 제공을 해주는 것이다.

해결하고 싶거나 알고 싶은 정보들을 기다리지 않고 챗봇을 통해 바로 안내를 받게 해주면서 사람들 대부분의 민원을 지연없이 해결하게 된 것이다. 그로인해서 질병관리본부의 코로나-19 챗봇 등록자 수가 70만명을 넘어 섰다.

이 뿐만이 아니다. 사용자 층의 챗봇을 바라보는 시선이 전반적으로 달라졌다. 챗봇 초창기에 사람의 말을 제대로 이해하지 못하여서 좋은 사용자 경험을 전달하지 못했다. 그래서 좋은 기능으로 발전했음에도 불구하고 사람들로부터 외면 받고 있었다. 하지만 코로나-19 챗봇에 대한 좋은 사용자 경험으로 인해서 챗봇이 사용할 만하다는 인식이 생겨 났다.

국내 대표적인 소셜미디어 회사인 카카오와 네이버의 경우 코로나 사태 직전대비 챗봇 사용량이 각각 50%, 18%씩 증가 한 것으로 나타났다.

이는 컴퓨터가 사람의 말을 알아 들을 수 있게 되면서 사용자의 서비스 소비 패러다임이 변하고 있다는 것을 반영한다고 본다. 이러한 패러다임 변화에 대해서 기업들은 많은 관심을 가져야 할 것이다. 챗봇을 통해 기업 비즈니스를 고객들에게 어떻게 서비스 할 것인지 깊게 고민해 볼 필요가 있을 것 같다.

Python redact sensitive information in text

text 데이터를 처리하다 보면 그 안에 개인정보(phone, email 등) 등의 민감정보가 포함 되어 있는 경우가 있다. 이것들은 정규식(regular expression)을 사용하여 알아 볼 수 없도록 편집 (masking) 할 수 있다.

import re

text = '안녕하세요. 문의드릴 것이 있어서 연락드렸습니다. 제 연락처는 010-1234-1234 / thenewth@gmail.com 입니다. 연락가능 하실때 연락 부탁 드립니다. 감사합니다.'

phoneRegular = "\d{2,3}-\d{3,4}-\d{4}"
emailReqular = "(\w+\.)*\w+@(\w+\.)+[A-Za-z]+"

phonePattern = re.compile(phoneRegular)
emailPattern = re.compile(emailReqular)

#Masking 문자 대신 공백을 사용하면 민감정보 표시 자체를 삭제 할 수 있다.
redactedPhoneText = re.sub(phonePattern, "***-****-****", text)
redactedEmailText = re.sub(emailPattern , "****@****.***", text)

#전화번호 정보 masking 결과 확인
print(redactedPhoneText)

#이메일 정보 masking 결과 확인
print(redactedEmailText)

출력한 결과를 확인해 보면 정규식에 해당하는 민감정보가 편집(Masking)된 것을 확인 할 수 있다.

Python connect to MS-SQL with pymssql

Python에서 MS-SQL을 사용하려면 일단, MS-SQL DB를 지원하는 Python 모듈을 설치해야 한다. MS-SQL에 대한 모듈은 크게 pyodbc와 pymssql 두가지가 있는데 여기서는 pymssql을 사용 할 것이다.

(DB에 관련된 모듈은 무수히 많은 것들이 있다. 참고: https://www.lfd.uci.edu/~gohlke/pythonlibs/)

다음과 같이 pymssql을 설치한다.

(설치 방법은 Ubuntu Linux대상 이다. 다른 OS를 사용하는 경우는 여기를 참고하면 된다.)

$ apt-get --assume-yes update
$ apt-get --assume-yes install freetds-dev freetds-bin
$ pip install pymssql

이제 Python에서 설치한 모듈을 가지고 MS-SQL에 접근해 보자.

import json
import pymssql 

#[Tip]json 문자열을 환경변수 파일로 저장하여 사용한다면 서버정보를 노출 하지 않을 수 있다.
json_string = 
'{
    "host":"Server Address",
    "port":1433,
    "user":"User ID@Server Name",
    "password":"P@ssW0rd",
    "database":"Database Name"
}'
json_data = json.load(json_string)

#pymssql 모듈을 이용하여 Connection 생성을 한다.
conn = pymssql.connect(host=json_data['host'], port=json_data['port'], user=json_data['user'], password=json_data['password'], database=json_data['database'])

여기까지 하면 MS-SQL서버에 접근(Connection 완료) 한 것이다.
다음으로 Select query문을 실행하여 데이터를 가져와 보자.

#Connection으로부터 cursor 생성
cursor = conn.cursor() 

#Select Query 실행
cursor.execute('select * from [Target Table Name]') 

#결과 데이터를 데이터프레임에 저장
df = pd.DataFrame(cursor.fetchall())

#실행이 끝나면 항상 연결 객체를 닫아 주어야 한다.
conn.close()

잘 실행 되었다면 결과를 담은 데이터프레임을 출력 해보자.

#결과 출력
print(df)

실행한 Select Query한 결과를 확인 할 수 있을 것이다.

MS-SQL Table Copy

학습 데이터를 준비 하려다 보면 기존의 데이터를 변형해야 될 때가 있다. 이때 원본 테이블을 가지고 바로 작업을 하게 되면 데이터가 손실 될 수 있다.
백업 데이터가 있다고 하면 그나마 다행이지만 보통 학습에 사용되는 데이터는 양이 많기 때문에 100% 복구하려면 시간이 걸린다.
이런 점들 때문에 될 수 있으면 테이블(혹은 데이터)을 복사해서 사용하면 좋다.

새 테이블을 생성 하면서 데이터 복사

select * into [New Table Name] from [Source Data]

테이블 구조만 복사

select * into [New Table Name] from [Source Table Name] where 1=2

기존 테이블에 데이터만 복사

insert into [Destination Table Name] select * from [Source Table Name]

MS-SQL Select random rows from Table

머신러닝 혹은 딥 러닝 학습을 하기 위해서는 우선 학습할 데이터를 탐색해 보아야 한다. 만약, 많은 양의 Row 데이터를 가지고 있는 SQL Table을 가지고 해야 한다면 일부 표본만 추출해서 테스트를 해보는 것이 필요하다.

이런 경우 테이블에서 랜덤으로 데이터를 추출할 때 다음 Query를 사용하면, 간단하게 일정 비율로 랜덤한 데이터를 추출해 낼 수 있다.

select top 10 percent * from [Table Name] order by newid()

No-SQL Database 란?

No-SQL (Not only SQL) 시작

1998년 카를로 스트로찌(Carlo Strozzi)라는 엔지니어가 공개한 표준 SQL 인터페이스를 채용하지 않은 자신의 경량 Open Source 관계형 데이터베이스를 No-SQL이라고 명명한데서 유래되었다고 한며, 이후 2009년에는 요한 오스칼손(Johan Oskarsson)이라는 엔지니어가 Open Source기반의 분산 데이터베이스 관련 행사를 준비하면서 No-SQL이라는 용어를 널리 사용하기 시작하였다고 한다.

초기 No-SQL은 기존의 관계형 데이터베이스 시스템의 주요 특성을 보장하는 ACID(Atomicity, Consistency, Isolation, Durability) 특성을 제공하지 않았다. 때문에 No-SQL이 등장한 이후에도 시장에서는 관계형 데이터베이스가 데이터를 처리하는데 가장 최적의 시스템으로 받아들여졌다.

기업 데이터의 정확한 처리가 필수적이기 때문에 기업 시스템에서는 현재도 관계형 데이터베이스를 훨씬 많이 사용한다. 무엇보다도 표준 SQL 이라고 하는 데이터를 처리하는 언어의 편이성 때문에 No-SQL 등 다른 데이터베이스 시스템들은 많이 활용 되지 않았다.

2000년 후반으로 넘어오면서 인터넷이 활성화되고, 소셜네트워크 서비스(SNS)등이 등장하면서 관계형 데이터 또는 정형데이터가 아닌 데이터, 즉 비정형데이터라는 것을 보다 쉽게 담아서 저장하고 처리할 수 있는 구조를 가진 데이터 베이스들이 관심을 받게 되었고, 해당 기술이 점점 더 발전하게 되면서, No-SQL 데이터베이스가 각광을 받기 시작했다.

기존의 관계형 데이터베이스 보다 더 융통성 있는 데이터 모델이 요구되며, 데이터의 저장 및 검색을 위한 특화된 매커니즘이 필요 해지기 시작했고, No-SQL은 단순 검색 및 추가 작업에 있어서 매우 최적화된 키 값 저장 기법을 사용하여, 응답속도나, 처리 효율 등에 있어서 매우 뛰어난 성능을 내주면서, 비정형 데이터를 다루는 SNS, 머신러닝 등 AI 산업에 많이 사용되기 시작했다.

SQL VS No-SQL

SQL	VS	No-SQL
정해진 규격에 의한 정적 정의	Schema	정해진 규격 없음. 동적 정의
가능	Data Join	불가능
표준	SQL	비표준
보장	Transcation	미보장
어려움	Distribute	쉬움

No-SQL 종류

1. Key-Value DB

Key와 Value의 쌍으로 데이터가 저장되는 가장 단순한 형태이다.
(ex) Dictionary, Hash 자료 구조
데이터를 하나의 불투명한 컬렉션으로 처리하며 각 레코드마다 각기 다른 필드를 가질 수 있는 Column Family 데이터 모델이다.
선택적인 값들이 대부분의 DB에서처럼 placeholder로 표현되지 않기 때문에 Key-Value DB는 동일한 데이터베이스 저장을 위해 메모리를 훨씬 덜 사용하기도 하므로 특정한 부하에서 큰 성능 상의 이점이 있을 수 있다.

2. Wide Column DB

Key-Value DB에서 발전된 형태의 Column Family 데이터 모델을 사용한다.
하나의 Row Key 안에 다양한 컬럼과 값을 저장 할 수 있도록 한다.
대량의 데이터의 압축, 분산처리, 집계 쿼리 (SUM, COUNT, AVG 등)및 쿼리 동작 속도 그리고 확장성이 뛰어나다.

3. Document(-oriented) DB

문서 데이터베이스는 JSON/XML 유사 형식의 문서로 데이터를 저장 및 쿼리하도록 설계된 비관계형 데이터베이스 유형으로 트리형 구조로 레코드를 저장하거나 검색하는 것에 효과적이다.

4. Graph DB

18세기 오일러의 그래프 이론에서 시작되었다.
데이터를 노드로 표현하며 노드 사이의 관계를 엣지로 표현한다.
기존의 RDB로는 표현하기 어려웠던 각 데이터간의 연결점들을 이어주고 표현(Visualizing)해주는데 용이하다.

Inverted Search Index

검색엔진에서 많이 사용하는 방법인데, 검색엔진은 사이트의 모든 페이지를 검색 로봇이 검색해서 문서내의 단어들을 색인하여 URL에 맵핑해서 저장해놓는다.

Key	Value
bcho.tistory.com/nosql	nosql, cassandra, riak
bcho.tistory.com/cloud	amazon, azure, google
facebook.com/group/serverside	amazon, google, riak
highscalability.com/bigdata	nosql, riak
http://www.basho.com/riak	riak

검색은 단어를 키로 검색이 되기 때문에, 위의 테이블 구조에서는 value에 검색 키워드들이 들어가 있기 때문에, 효과적인 검색을 할 수 없다.이 검색 키워드를 키로 해서 URL을 value로 하는 테이블을 다시 만들어 보면, 아래와 같은 식으로 표현되고, 검색 키워드로 검색을 하면 빠르게, 검색 키워드를 가지고 있는 URL을 찾아낼 수 있다.

Key	Value
riak	bcho.tistory.com/nosql facebook.com/group/serverside highscalability.com/bigdata http://www.basho.com/riak
nosql	bcho.tistory.com/nosql highscalability.com/bigdata

이렇게 value의 내용을 key로 하고, key의 내용을 반대로 value로 하는 패턴을 inverted search index라고 한다.