코린이형준

요즘 많은 쿼리 성능과 같은 많은 이점 때문에 데이터 분석시 google BigQuery로 데이터를 이전하는 경우가 많다.

이때 테이블 하나하나 생성후 데이터를 옮기는 작업 자체는 어렵지 않지만 생각보다 고된 작업이 이어진다.

물론 제가 겪은 일입니다ㅎㅎ... 데이터를 안전하고 쉽게 옮기기 위한 tip을 공유합니다.

과정은 아래와 같습니다.

1. 옮기자 하는 대상인 rdbms 테이블 정보를 모두 가져옵니다.

2. 원하는 target을 선택합니다.

3. bigquery api client를 연결한뒤 이전을 시작합니다.

1단계

from sqlalchemy import create_engine
# sqlalchemy의 create_engine 메서드를 사용하여 db를 연결시킵니다.
RDS_ENGINE = create_engine('database_engine://ID:PASSWORD@host:port/database명')

# 모든 테이블 목록 조회
query = '''
SHOW TABLES;
'''
tables = pd.read_sql(query, con=RDS_ENGINE)

# 본인이 옮기고자 하는 테이블의 index를 제외한 목록을 모두 drop 합니다.
# 이건 방법이 여러가지라 target_tables에 target들만 남기면됩니다.
target_tables = tables.drop(index)

2단계

import google
# 이작업을 하기 위해서는 google_account_key가 필요합니다.
# 작업 환경의 환경변수에서 path를 설정해줄수 있습니다.
# GOOGLE_APPLICATION_CREDENTIALS=/Users/~~/google_secret.json
# 위와 같이 GOOGLE_APPLICATION_CREDENTIALS에 본인이 원하는 path를 설정해 키를 위치해줍니다.

credentials, project = google.auth.default(
    scopes=["https://www.googleapis.com/auth/bigquery"]
)
client = bigquery.Client(project=project, credentials=credentials)

3단계

for i in target_tables.index:
    target = target_tables[i]
    schema = f'''
    SELECT *
    FROM {target}
    '''
    try:
        for chunk in pd.read_sql(schema, con=engine, chunksize=50000):
            print(target, len(chunk))
            df = pd.DataFrame(chunk)
            df.to_gbq(f'dataset이름.{target_ph}', project_id=project, credentials=credentials, if_exists='append', chunksize=50000)
    except Exception as e:
        print(target)
        print(e)
        continue

한숨자고 일어나면 됩니다. 여기서 알아야하는것은 주의해야할것은 if_exists인데 ['replace', 'fail', 'append']가 있습니다.

셋다 모두 테이블이 없다면 생성후 문제가 없지만 

1. append: 기존의 테이블의 정보를 변경하지 않고 dataframe을 테이블에 append 즉 추가만 진행합니다.

2. replace: 기존에 테이블이 있다면 dataframe으로 모두 대체해버립니다.

3. fail: 기존에 테이블이 있다면 실패로 처리됩니다.

위와 같이 동작하기 때문에 반드시 주의해야합니다.

물론 모두 완벽하게 테이블 생성뒤 이관된다면 좋겠지만 특수한 사항에 에러가 발생하므로 주의해야합니다.

403 Quota exceeded: Your table exceeded quota for imports or query appends per table.

이에러 주의해야합니다...

insert만을 고려한다면 bigquery아주 유용하겠지만 중간중간 데이터의 정보를 변경해줘야 하며 increment_id와 같은 정보가 순차적인 정보가 없다면 테이블이라면 bigquery의 도입을 안하는것을 고려해야합니다.

read_sql, to_gbq 메서드를 사용할때 chunksize를 사용한이유는 아래 글을 참조해주세요... 데이터가 많을때는 왠만하면 꼭사용해주셔야 합니다.

pandas dataframe을 이용하여 db 업데이트 작업을 하다 문제가 생겼다.

위와 같은 fulfillment table 에서 특정 id 값들의 created_at값을 일괄적으로 변경한다 했을때 targets에 해당하는 id 값들을 transaction 한번에 모두 처리할수 있다.

engine = LOCAL_DB
targets = (1,7,15,3000,4000,...13000)

update_query = f'''
UPDATE fulfillment
SET
created = now()
WHERE
id in {targets}
'''

with engine.connect() as conn:
    conn.execute(update_query)

하지만 id값마다 다른 tracking_number를 넣는다고 할때 루프를 태우는 방법외엔 따로 없었다.

만약 insert라면 dataframe 자체를 to_sql메서드를 사용하여 일괄적으로 insert하기 때문에 문제가 없다.

insert_dataframe # fulfillment테이블과 동일한 포맷의 target dataframe이 있다고 가정한다.
insert_dataframe.to_sql('fulfillment', con=ENGINE_LOCAL, if_exists='append', index=False, method='multi', chunksize=50000)

여기서 문제가 발생하였다. to_sql 메서드를 사용하여 특정 키값에 해당하는 필드를 업데이트 하고 중복된다면 생성하지 않고 넘어가야했는데 to_sql메서드에서는 이러한 기능을 제공하지 않는걸로 확인이 됐다(물론 제가 못찾았을수도...).

물론 for루프를 태워서 해결은 가능하다.

insert_dataframe
with engine.connect() as conn:
    for i in insert_dataframe.index:
        update_query = f'''
        UPDATE fulfillment
        SET
        tracking_number insert_dataframe['tracking_number'][i]
        WHERE
        id = insert_dataframe['id'][i]
        '''

        conn.execute(update_query)

위와 같이 단건으로 트랜잭션을 발생시키고 네트워크를 태우는 성능 최저의 방식을 사용한다했을때 데이터 1만개 기준 1시간이 넘어가는 성능을 보여줬다. 물론 안정성은 보장되고 데이터의 수가 적다면 위의 인덱스 하나하나 처리하는 방식이 맞다고 할수 있다.

하지만, 데이터 20만개정도를 업데이트한다고 했을때는 말도 안되는 저성능을 보여준다.

이때 사용한것이 temp 테이블을 이용한 join 업데이트다. 물론, 애플리케이션 단에서 모두 처리할 수 있는 방법이 있을것이라 생각하는데 찾아내지는 못했다. 여기서 조건은 업데이트할 테이블이 속해있는 database에 temp 테이블이 있어야한다. 방법은 아래와같다.

1. fulfillment_table과 동일한 포맷인 fulfillment_temp_table을 create한다.

2. fulfillment_table에 target데이터인 insert_dataframe을 to_sql메서드를 통해 insert한다.

3. left join을 통해 일괄적으로 fulfillment_table_tracking_number를 update한다.

insert_dataframe # to_sql로 insert할 target_data

insert_dataframe.to_sql('fulfillment_temp', con=ENGINE_LOCAL, if_exists='append', index=False, method='multi', chunksize=50000)

update_query = '''
UPDATE
fulfillmet as a
SET
tracking_number = b.tracking_number,
created_at = b.created_at,
FROM
fulfillment_temp as b 
WHERE
a.id = b.id
'''
with engine.connect() as conn:
    conn.execute(update_query)

위와 같은 방식으로 join을 이용한 대량의 update를 할수 있다.

bulk_update 방식으로 업데이트 데이터들의 query를 일일이 생성하여 대량 업데이트하는 방식도 있지만 이방식이 가장 간단했다고 생각이 든다.

1. MYSQL설치

다운로드 방법에 대해서는 

이블로그에 자세히 설명되어 있으므로 참고하면 될듯하다.

2. 실행

다운로드를 모두 받고 나면 실행방법은 총 2가지가 존재한다.

-cmd창

시작메뉴의 cmd창을 켠후 해당 디렉터리로 이동해야하므로

cd C:\Program Files\MySQL\MySQL Server 8.0\bin 입력하여 실행해준다(위블로그대로 했다면 문제없이 진행될거다.  만약 되지않는다면 경로상의 문제이므로 직접 위치를 찾아서 경로를 설정해주면 된다.)

그후 mysql -u계정명 -p 를 입력하면 다운로드하면서 설정한 비밀번호를 입력해주면 클라이언트를 실행할수있다.

-직접 실행

필자는 이방법을 많이 사용한다.(편하기때문)

시작 메뉴에서 클라이언트를 클릭해 직접실행하는방식이다. 방법설명은 따로없다 이미지를 참고하자.

이제 모두 다운로드및 실행방법을 알아봤으니 다음부터 제대로 실습을 시작해보면 되겠다.

1. 데이터베이스에있어 KEY란?

MYSQL과 같은 관계형 데이터베이스에는 중요한 키라는 것이 존재한다.

  여러 종류 키에 관해 설명하기전, 릴레이션(realation)에 관해 간단히 정리하자면 같은 성격의 데이터들의 집합을 의미하며 릴레이션은 각행을 의미하는 튜플(tuple)과 이름을 가진 하나의 열을 말하는 에트리뷰트(attribute)로 데이터를 정렬하여 관리한다.

  이러한 릴레이션의 특징 중 이번 과제와 관련이 있는 키(key)가 있는데 키란, 릴레이션을 구성하는 튜플을 고유하게 식별할 수 있는 하나 이상의 애트리뷰트들의 모임을 뜻한다. 키의 종류는 수퍼키(super key), 후보키(candidate key), 기본키(primary key), 대체키(alternate key), 외래키(foreign key)로 총 5가지가 있다.

  첫번째, 수퍼키는 한 릴레이션 내의 특정 튜플을 고유하게 식별하는 애트리뷰트 또는 애트리뷰트들의 집합으로, 예를 들어 회사의 사원의 릴레이션에서 사원번호+이름 또는 사원번호가 수퍼키가 될 수 있다. 하지만 이러한 수퍼키는 투플들을 고유하게 식별하는데 꼭 필요하지 않은 애트리뷰트들을 포함할 수 있다는 점이 있다.

  두번째, 후보키는 각 튜플을 고유하게 식별하는 최소한의 애트리뷰트들의 모임으로 사원번호는 사원 릴레이션의 후보 키가 되지만 사원번호+이름은 사원 릴레이션의 후보키가 아니다. 따라서, 후보키는 유일성만 만족하는 슈퍼키와는 다르게 유일성, 최소성을 모두 만족해야 하며 모든 릴레이션에는 최소 한 개 이상의 후보 키가 있고 후보키는 두개 이상의 복합 애트리뷰트로 이루어 질 수가 있다.

  세번째, 기본키는 한 릴레이션에 후보 키가 두 개 이상 있으면 설계자 또는 데이터베이스 관리자가 이들 중에서 하나를 기본 키로 선정하는 것으로, 사원 릴레이션에서 사원번호와 이메일 주소가 후보키가 될 수 있는데 관리자의 임의로 사원번호를 기본키로 선정할 수 있다. 만약 한 릴레이션에서 기본키를 찾을 수 없는 경우에는 레코드 번호와 같이 종종 인위적인 키 애트리뷰트를 릴레이션에 추가할 수 있으며, 기본 키로 지정된 애트리뷰트들은 모든 튜플에 대해 널(null)값을 가질 수가 없다. 따라서, 기본키는 후보키가 만족해야 하는 유일성, 최소성 뿐만 아니라 값에 대해 not null또한 만족하여야 한다.

  네번째, 대체키는 간단하게 기본키가 아닌 후보키로 관리자가 사원번호와 이메일 주소 중에 사원번호를 기본 키로 선정하면 이메일 주소가 대체키가 되는 것이다.

  마지막, 외래키는 다른 릴레이션의 기본키를 참조하는 애트리뷰트이며 릴레이션 간의 관계를 나타내기 위해 사용되는 키로 이를 유형과 함께 설명할 수 있다. 외래키는 사원 릴레이션에서 기본키가 사원 번호이고 부서명이 기본키는 아니지만 회사의 조직도를 나타내는 릴레이션에서 부서명이 기본키가 되어 참조되어 다른 릴레이션의 기본키를 참조하는 역할을 한다. 또한 사원 릴레이션에서 사수의 사원번호가 애트리뷰트로 존재한다면 이는 기본키가 될 수는 없겠지만 일반사원의 사원번호는 일반키가 되고 사수의 사원번호키가 기본키의 값을 가져와 하나의 릴레이션에서 외래키가 기본키를 참조하는 역할을 한다.

  정리하자면, 튜플과 애트리뷰트로 구성되어 있는 릴레이션에서 키는 이들을 구별해주는 중요한 역할을 한다는 것이다.

2. 무결성이란?

  키에 관해 공부하다보면 중요한 개념이 개체 무결성, 참조 무결성이 있다.

  개체 무결성(Entity Integrity)은 릴레이션의 기본키를 구성하는 속성은 NULL일수 없다는 것이다. 기본키는 하나의 릴레이션에서 속성들을 구별해주는 가장 중요한 대표 역할을 하므로 유일성, 최소성 뿐만 아니라 값에 대해 not null또한 만족해야 하는데 이것이 개체 무결성과 연관 지어지는 이유이다. 정리하자면 개체 무결성은 기본키에 속해 있는 속성은 절대 널(Null)값을 가질수 없다는것과 기본키에 속해 있는 속성은 중복 값을 가질 수 없음을 뜻하는 것이다.

  참조 무결성(Referential Integrity)은 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없다는 것을 말한다. 외래키는 다른 릴레이션의 기본키를 참조하나는 애트리뷰트이며 릴레이션 간의 관계를 나타내기 위해 사용되는 키로 참조 무결성에 의해 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 한다는 규정 제약 조건은 참조 할수 없는 외래키를 가질수 없다는 것과 외래키는 NULL이거나 참조하는 테이블의 기본키와 같은 값이어야한다. 참조되는 테이블의 행을 이를 참조하는 참조키가 존재하는한 삭제될수 없고 기본키도 변경될수 없음을 뜻한다.

이밖에도 데이터베이스의 이론에는 여러가지가 더있지만 학습중 가장 중요해보이는 것들을 간단하게 2차시로 구성하여 적어 봤다. 다음 차시부터는 MYSQL설치부터 시작하여 실습에 관한 내용을 다뤄볼것이다.

학교에서 수강했던 데이터베이스 수업간 배웠던 MYSQL에 대해 포스팅을 하려한다. 

초반엔 과제를 하며 중요하다 생각했던 정의에 대해 2차시정도로 축약하여 설명한후 그 이후부터는 실습에 관련한 내용들을 주로 다뤄볼생각이다.

1. 데이터베이스 정의

특정 조직의 업무를 수행하는데 있어 작동하고 있는 데이터들의 모임을 데이터베이스라고 정의한다. 이러한 데이터베이스의 구성요소는 크게 개체(entity), 속성(attribute), 관계(relationship)로 크게 3가지로 나뉜다.

  첫번째, 개체(entity)의 경우 업무가 다루는 사물(대상)을 가리 키는 것으로 데이터베이스에서 표현하려는 객체로서 서로 구별되는 것을 뜻한다. 대학교를 예로 들면 교수, 학생, 과목이 데이터베이스에서의 개체라고 할 수 있다.

  두번째, 속성(attribute)의 경우 각 사물(대상)이 가지고 있는 상세한 특성으로 즉, 하나의 개체는 하나 이상의 속성으로 구성되어 그 개체의 특성을 설명해주는 지표가 되는 것이다. 예로 들면 교수라는 개체는 교수번호, 교수이름, 전공, 학과로 학생은 학번 이름, 학년, 학과로 과목은 과목번호, 과목이름, 학점이란 속성들로 구성되어 각각의 개체를 구성한다.

  마지막, 관계(relationship)의 경우 업무가 다루는 사물들 사이에 존재하는 연관으로 즉, 각각의 개체 또는 개체 집합들 사이의 관계를 나타내는 것이다. 예로 들면 교수와 학생은 지도라는 관계, 교수와 과목은 강의라는 관계, 학생과 과목은 등록이라는 관계로 이루어 진다 할 수 있다.

  정리하자면, 각각의 속성을 가진 개체들이 관계를 가지고 모여 만들어진 모임을 데이터베이스라고 한다.

2. 데이터베이스의 특징

데이터 베이스의 특징은 크게 6가지로 표현할 수 있다.

  첫번째, 실시간 접근성(Real-Time Accessibility)로 질의에 관해 실시간으로 처리 및 응답이 이루어져야 한다.

  두번째, 계속적인 변화(Continuous Evolution)로 데이터베이스는 만든 이후 정적인 것이 아닌 계속해서 새로운 데이터가 갱신, 삭제, 삽입 등의 동적인 특성을 가짐으로써 항상 최신의 상태를 유지하여야 한다. 물론 동적인 특성을 띔과 동시에 정확성 또한 유지해야 한다.

  세번째, 동시 공용(Concurrent Sharing)으로 데이터베이스는 여러 사용자들이 동시에 사용하는 것이므로 다수의 사용자가 동시에 같은 데이터를 이용할 수 있어야 한다.

  네번째, 내용에 의한 참조(Content Reference)로 데이터 베이스는 위치나 주소가 아닌 체중, 신장과 같은 사용자가 요구하는 데이터의 내용으로 데이터를 찾아낸다.

  다섯째, 지속성(Persistent)로 데이터 베이스는 한번 데이터를 생성하고 나면 일부러 저장매체를 훼손하지 않는 한 소멸하지 않고 지속된다.

  마지막, 상호 관련성(Inter-Related)로 데이터 베이스는 각 개체들이 서로 관계를 맺어 만들어 진다는 상호 관련성을 띈다는 특징이 있다.

데이터 베이스는 어떠한 성질을 가지고 있는 데이터들이 모여 있는 것으로 크게 4가지로 정의할 수 있다.

  첫번째, 통합된 데이터(Integrated Data)로 자료의 중복을 최소로 통제한다는 뜻을 가진다. 중복을 완전히 없애기는 중복이 없을 경우 다른 테이블 사이에 연관을 할 수가 없기에 불필요한 자료의 중복을 최소화한다.

  두번째, 저장된 데이터(Stored Data)로 말그대로 컴퓨터가 접근 가능한 저장매체(테이프, 디스크 등)에 저장된 자료라는 뜻을 가진다.

  세번째, 운영 데이터(Operational Data)로 한 조직의 고유 업무를 수행하기 위해 반드시 필요한 데이터로 단순한 입출력이나 임시 데이터가 아닌 존재목적이 뚜렷한 데이터 라는 뜻을 가진다.

  마지막, 공용 데이터(Shared Data)로 한 조직의 여러 응용 프로그램이 공동으로 소유, 유지, 이용하는 데이터라는 뜻을 가진다.

  정리하자면, 데이터베이스란 한 조직의 여러 응용 시스템들이 공용(shared)하기 위해 통합(integrated), 저장(stored)한 대용량(large)의 운영(operational)데이터의 집합이다.

3. DBMS란?

DBMS 제품을 설명하기전에 간단히 데이터베이스의 종류에 대해 나눠보자면 크게 계층형 데이터베이스, 네트워크형 데이터베이스, 관계형 데이터베이스로 나눌 수 있다.

  계층형 데이터베이스는 데이터의 관계를 트리 구조로 정의하고, 부모,자식 형태를 갖는 구조로써 상위의 레코드가 복수의 하위 레코드를 갖는 구조이다. 하지만 데이터의 중복이 생긴다는 문제점이 있다.

  네트워크형 데이터베이스는 계층형 데이터의 데이터중복 문제를 해결했고, 레코드 간의 다양한 관계를 그물처럼 갖는 구조이다. 하지만 복잡한 구조 때문에 추후에 구조를 변경한다면 많은 어려움이 따른다.

  관계형 데이터베이스는 우리가 흔히 표현하는 행(Column), 열(Record)로 구성된 Table간의 관계를 나타낼 때 사용한다 우리는 이렇게 표현된 데이터를 SQL(Structured Query Language)을 사용하여 데이터 관리 및 접근을 한다.

  이와는 다르게 SQL을 사용하지 않는다는 의미로 NoSQL데이터베이스가 있는데 이번 목차에서는 이러한 SQL데이터베이스인 MYSQL, MS-SQL과 NoSQL데이터베이스인 MongoDB의 특성에 대해 설명해보려 한다.

  첫번째, MYSQL은 PHP, jsp, 자바 웹 프로그래밍에서 사용되는 일반적인 DBMS이며, MYSQL은 공개용 소프트웨어이기 때문에 누구나 무료로 다운로드 받아 사용할 수 있지만, 상업적인 목적으로 MySQL을 사용하려면 반드시 라이센스를 별도로 구매하여야 한다. MySQL은 무료이면서 처리되는 속도 또한 상당히 빠르고 용이하며, 대용량의 데이터를 처리할 수 있는 장점과 보안에도 뛰어난 특성을 지니고 있다.

  두번째, MS-SQL은 C# ,asp.net 웹 프로그래밍에서 사용되는 일반적인 DBMS이며, MS-SQL은 많은 기능들이 마법사로 구성되어 있어 SQL을 몰라도 백업,튜닝,스케쥴,복제등의 관리가 가능해 DB관리툴이 편리하며, 트랜잭션 기반으로 완전한 무결성 구축이 가능하고, 여러 단계의 보안레벨을 지원해 뛰어난 보안성을 자랑하는 특성을 지니고 있다.

  마지막, MONGODB는 모드데이터가 JSON형태로 저장되며, 스키마가 없어 이에 따라 필요할 때마다 필드를 추가하거나 제거하는 것이 매우 쉬워졌음을 의미해 개발과정이 매우 단순해지고 빠르게 개발이 가능하게 되었다. key기반의 get, put 뿐만 아니라 다양한 종류의 쿼리들을 제공한다. 그리고 별도의 스토리지 엔진을 통해 파일을 저장할 수 있는 특징이 있으며, 조인과 트랜잭션이 없는 특성을 지니고 있다.

  정리하자면, 방식에 따라 여러 종류로 나누어 지더라도 결과적으로 DBMS란 응용 프로그램과 데이터의 중재자로서 모든 응용 프로그램들이 데이터베이스를 공유할 수 있도록 관리해 주는 소프트웨어 시스템이라는 것이다.

(나의목표는 일단 MYSQL과 MONGODB를 올해안에 다뤄보는게 목표이다.) 

2020.09월기준, ORACLE, MY-SQL, MS-SQL순으로 세계 DBMS시장을 점유하고 있는 것으로 나타났으며 점유율로 나타낼 경우 ORACLE 약42%, MY-SQL 약19%, MS-SQL 약16%로 나타났다. 국내 DBMS시장의 경우 ORACLE이 60%이상을 점유하고 있는 것으로 나타나 있다. 

*참고문헌 및 사이트

1. DBMS특징 및 구별에 관하여

https://ourcstory.tistory.com/30

https://ykh9300.tistory.com/52

https://siyoon210.tistory.com/130

https://lazyer.tistory.com/27

https://server-talk.tistory.com/29

https://ssmsig.tistory.com/19

https://league-cat.tistory.com/23

2. DBMS 시장 점유율

https://db-engines.com/en/ranking-