10 주차 Simple ETL 실습

 

구글 colab을 바탕으로 CSV 파일을 받아 Redshift 테이블로 로딩하기

 

---

 

1.

!pip install ipython-sql==0.4.1
!pip install SQLAlchemy==1.4.49

 

! 는 외부 셸 명령어를 실행하라는 뜻

ex) !ls 리눅스 셸 명령어 사용가능

ipython-sql은 ipython 환경에서 SQL 코드를 실행할 수 있게 해주는 확장 모듈

SQLAlchemy 는 SQL 데이터베이스와 상호 작용하기 위한 파이썬 SQL 툴킷 및 ORM 라이브러리

 

2.

%load_ext sql

 

%로 시작하는 IPython 매직 명령어의 일종으로, 특정 기능을 확장하거나 추가하는 역할

 

%load_ext sql는 ipython-sql 라이브러리의 일부로 제공되는 IPython 확장을 로드하는데 사용됩니다. 이를 통해 Jupyter Notebook에서 SQL 코드를 실행하고 데이터베이스와 상호 작용하는 기능을 활성화할 수 있습니다.

ipython-sql 라이브러리는 Jupyter 환경에서 SQL 쿼리를 직접 실행할 수 있는 매직 명령어(%%sql)를 제공하며, %load_ext sql은 이러한 확장을 로드하는 역할을 합니다. 그 후에는 Jupyter Notebook에서 SQL 코드를 작성하고 실행할 수 있게 됩니다.

 

3.

%sql postgresql://ID:PW@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev

 

4.

%%sql

DROP TABLE IF EXISTS hojumoney73.name_gender;
CREATE TABLE hojumoney73.name_gender (
   name varchar(32) primary key,
   gender varchar(8)
);

 

%sql은 한 줄의 sql 코드를 실행할 때 사용

%%sql은 여러 줄의 sql 코드를 실행할 때 사용

 

5.

import psycopg2

# Redshift connection 함수
# 본인 ID/PW 사용!
def get_Redshift_connection():
    host = "learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com"
    redshift_user = "ID"
    redshift_pass = "Password"
    port = 5439
    dbname = "dev"
    # psycopg2의 connect클래스의 객체 "conn" 생성
    conn = psycopg2.connect("dbname={dbname} user={user} host={host} password={password} port={port}".format(
        dbname=dbname,
        user=redshift_user,
        password=redshift_pass,
        host=host,
        port=port
    ))
    conn.set_session(autocommit=True)
    # conn.cursor() -- 커서 인스턴스 생성
    return conn.cursor()

 

? 위에 코드는 왜 get_redshift_connection 함수 없이도 실행이 가능했을까 :

 

https://www.psycopg.org/docs/install.html 

https://www.psycopg.org/docs/connection.html#connection.cursor

The connection class — Psycopg 2.9.9 documentation

https://www.bearpooh.com/147

psycopg2으로 PostgreSQL 사용하기

 

6.

import requests

def extract(url):
    f = requests.get(url)
    return (f.text)

def transform(text):
    lines = text.strip().split("\n")
    records = []
    for l in lines:
      (name, gender) = l.split(",") # l = "Keeyong,M" -> [ 'keeyong', 'M' ]
      records.append([name, gender])
    return records

def load(records):
    """
    records = [
      [ "Keeyong", "M" ],
      [ "Claire", "F" ],
      ...
    ]
    """
    # BEGIN과 END를 사용해서 SQL 결과를 트랜잭션으로 만들어주는 것이 좋음
    cur = get_Redshift_connection()
    # DELETE FROM을 먼저 수행 -> FULL REFRESH을 하는 형태
    for r in records:
        name = r[0]
        gender = r[1]
        print(name, "-", gender)
        sql = "INSERT INTO keeyong.name_gender VALUES ('{n}', '{g}')".format(n=name, g=gender)
        cur.execute(sql)

 

7. 

link = "https://s3-geospatial.s3-us-west-2.amazonaws.com/name_gender.csv"

data = extract(link)

lines = transform(data)

load(lines)

 

8.

%%sql

SELECT COUNT(1)
FROM hojumoney73.name_gender;

SELECT *
FROM hojumoney73.name_gender;

SELECT gender, COUNT(1) count
FROM hojumoney73.name_gender
GROUP BY gender;

 

conn 을 해제는 안하나?