Spotify API 음악데이터 저장을 위한 RDS 연결&기본활용

2020-05-30

.

Data_Engineering_TIL(20200530)

[참고사항]

  • ‘Spotify API를 이용한 인증 및 음악데이터 조회’를 이어서 참고할 것

  • URL : https://minman2115.github.io/DE_TIL80/

[학습내용]

  • 먼저 Mysql RDS를 생성하고 아래와 같이 코드를 작성하고 RDS와 연결이 잘 되는지 테스트해본다.

  • 실행결과로 “connect success” 를 확인할 수 있다.

import sys
import requests
import base64
import json
import logging
import pymysql

# 아래 세줄의 코드가 없으면 이 전체 코드의 실행결과 시
# UnicodeEncodeError: 'cp949' codec can't encode character '\u2013' in position 33:
# illegal multibyte sequence 애러발생하기 때문에 추가한 것임
import io
sys.stdout = io.TextIOWrapper(sys.stdout.detach(), encoding = 'utf-8')
sys.stderr = io.TextIOWrapper(sys.stderr.detach(), encoding = 'utf-8')

host = "[RDS endpoint]"
port = 3306
username = "admin"
database = "pmstest"
password = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

client_id = "xxxxxxxxxxxxxxxxxxxxxxxxxxxx"
client_secret = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

def main():

    try :
        conn = pymysql.connect(host, user = username, passwd = password, db = database, port = port, use_unicode = True)
        cursor = conn.cursor()

    except :
        logging.error("could not connect to RDS")
        sys.exit(1) # 일반적으로 오류가 발생하면 exit 1을 주는 편이다.

    print("connect success")
    sys.exit(0)

if __name__=='__main__':
    main()
  • 아래 그림과 같이 RDS에 다음과 같은 데이터 모델을 만들것이다.

1

그래서 DBeaver에서 RDS로 접속하고, 아래와 같은 쿼리로 테이블들을 만들어준다.

create table pmstest.artists (
id VARCHAR(255), 
name VARCHAR(255), 
followers INTEGER, 
popularity INTEGER, 
url VARCHAR(255), 
image_url  VARCHAR(255), 
PRIMARY KEY(id)
) 
ENGINE = InnoDB
DEFAULT CHARSET = 'utf8';

create table pmstest.artist_genres (
artist_id VARCHAR(255), 
genre VARCHAR(255)
) 
ENGINE = InnoDB
DEFAULT CHARSET = 'utf8';

show create table pmstest.artists ; ## artists table 정보 확인
show create table pmstest.artist_genres ;

그러면 아티스트 장르 테이블에 데이터를 넣어보자

아래와 같은 쿼리를 날려서 임의의 데이터를 insert 한다.

그리고 실렉트 데이터를 하면 데이터가 잘 들어간것을 확인할 수 있다.

insert into pmstest.artist_genres (artist_id, genre) VALUES('1234', 'pop');

select * from pmstest.artist_genres;

그리고 insert into pmstest.artist_genres (artist_id, genre) VALUES('1234', 'pop');를 한번 더하면 이 데이터가 두개가 들어간 것을 확인할 수 있다.

아티스트 장르 테이블은 데이터가 추가되겠지만 위와 같이 중복데이터가 들어가는 것이 좋지 않다.

우리가 이 테이블에 데이터를 집어 넣는 것을 구현을 한다면 이런 중복데이터가 생기지 않도록 관리를 해줘야 한다.

일단 지금 만든 아티스트 장르 테이블에서 아래와 같은 쿼리로 모든 레코드를 지워준다.

delete from pmstest.artist_genres;

그리고 아티스트 장르 테이블을 지워준다.

drop table pmstest.artist_genres;

그런 다음에 아래 쿼리와 같이 유니크 키로 아티스트 아이디랑 장르를 추가해서 테이블을 다시 만들어준다.

create table pmstest.artist_genres (
artist_id VARCHAR(255), 
genre VARCHAR(255),
UNIQUE KEY(artist_id,genre)
) 
ENGINE = InnoDB
DEFAULT CHARSET = 'utf8';

그런 다음에 아래와 같은 쿼리로 데이터를 넣어본다.

insert into pmstest.artist_genres (artist_id, genre) VALUES('1234', 'pop');

처음 한번은 잘 들어가지는데 이 쿼리를 다시 실행해서 데이터를 또 넣으면 아래와 같은 애러가 나는 것을 확인할 수 있다.

SQL Error [1062] [23000]: Duplicate entry '1234-pop' for key 'artist_id'

이렇게 해서 중복데이터를 방지할 수 있다.

그러나 우리는 파이썬으로 지속적으로 데이터를 넣어줄 것인데 이렇게 애러가 뜨면 스크립트가 멈추기 때문에 이를 막기위한 방법을 강구해줘야 한다.

그러면 update 쿼리를 아래와 같이 하게 되면 artist id를 기준으로 하나만 업데이트 되는 것을 확인할 수 있다.

update pmstest.artist_genres SET genre='pop' where artist_id ='1234';

replace를 하기전에 아래와 같은 쿼리로 컬럼을 몇개 추가해보자

alter table pmstest.artist_genres ADD COLUMN country VARCHAR(255);

alter table pmstest.artist_genres ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 
## 언제 데이터가 업데이트 되었는지 데이터 거버넌스를 위한 컬럼

그리고 show table을 하면 아래와 같이 우리가 원하는 컬럼이 잘 추가된 것을 확인할 수 있다.

image

그런 다음에 UK를 포함한 데이터를 아래와 같은 쿼리를 이용해서 insert를 해본다.

insert into pmstest.artist_genres (artist_id, genre, country) VALUES('1234', 'pop', 'UK');

아래 그림과 같이 역시 중복으로 오류가 난다.

SQL Error [1062] [23000]: Duplicate entry '1234-pop' for key 'artist_id'

그러면 REPLACE를 아래와 같은 쿼리로 해보자

REPLACE INTO pmstest.artist_genres(artist_id, genre, country) VALUES('1234','pop','UK');

그러면 정상적으로 replace 되는 것을 확인할 수 있다.

image

그런데 위에 그림과 같이 2 rows affected를 확인할 수 있는데 replace는 데이터가 있으면 그 키값을 찾는다.

데이터가 없으면 바로 인서트를 하게되고, 키값이 있으면 데이터를 지우고 새로 다시 추가하게 되는 방식이다.

그러면 아래와 같이 다시 replace를 해본다.

REPLACE INTO pmstest.artist_genres(artist_id, genre, country) VALUES('1234','rock','UK');

그러면 정상적으로 실행이되고 1 row affected 가 되는 것을 확인할 수 있다.

그런다음에 select 레코드를 하게되면 아래와 같이 두개의 레코드가 들어간 것을 확인할 수 있다.

image

replace는 어떤 데이터를 바로 업데이트 하는 것이 아니라 지우고 넣는 방식이 때문에 퍼포먼스 이슈가 있을 수 있다.

만약에 예를 들어서 id를 auto_increment(자동으로 1,2,3,4,… 순서대로 id가 부여되는 방식)방식이라고 가정하자.

예를 들어서 bts가 id 1번이었는데 replace를 때려서 일부를 수정하게 되면 id 1의 레코드를 지워버리고, 2번 블랙핑크, 3번 윤하 그 다음에 id 4번으로 bts가 새로 데이터가 생기면서 bts의 id가 변경되어 버리는 문제가 발생할 수 있다.

따라서 replace를 할때는 이를 조심해야 한다.

그러면 이번엔 insert ignore를 알아보자.

아래와 같은 쿼리를 날려보자.

INSERT IGNORE pmstest.artist_genres(artist_id, genre, country) VALUES('1234','rock','UK');

그러면 1 warning이 뜰것이다.

말그대로 insert 하는데 무시하고 하라는 것이다.

이번에는 아래와 같은 쿼리를 실행해본다.

INSERT INTO pmstest.artist_genres(artist_id, genre, country) VALUES('1234','rock','FR') 
ON DUPLICATE KEY UPDATE artist_id='1234', genre='rock',country='FR';

INSERT INTO pmstest.artist_genres(artist_id, genre, country) VALUES('1234','hip-hop','FR') 
ON DUPLICATE KEY UPDATE artist_id='1234', genre='hip-hop',country='FR';

image

그러면 위에 그림과 같이 우리가 의도한 대로 데이터들을 업데이트 할 수 있다.