데이터 기초

ETL 기초 실습

데이터왕 2023. 12. 31. 13:34

In [3]:

%load_ext sql

In [1]:

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

WARNING: Ignoring invalid distribution -qlalchemy (/usr/local/lib/python3.10/dist-packages)
Requirement already satisfied: ipython-sql==0.4.1 in /usr/local/lib/python3.10/dist-packages (0.4.1)
Requirement already satisfied: prettytable<1 in /usr/local/lib/python3.10/dist-packages (from ipython-sql==0.4.1) (0.7.2)
Requirement already satisfied: ipython>=1.0 in /usr/local/lib/python3.10/dist-packages (from ipython-sql==0.4.1) (7.34.0)
Requirement already satisfied: sqlalchemy>=0.6.7 in /usr/local/lib/python3.10/dist-packages (from ipython-sql==0.4.1) (1.4.49)
Requirement already satisfied: sqlparse in /usr/local/lib/python3.10/dist-packages (from ipython-sql==0.4.1) (0.4.4)
Requirement already satisfied: six in /usr/local/lib/python3.10/dist-packages (from ipython-sql==0.4.1) (1.16.0)
Requirement already satisfied: ipython-genutils>=0.1.0 in /usr/local/lib/python3.10/dist-packages (from ipython-sql==0.4.1) (0.2.0)
Requirement already satisfied: setuptools>=18.5 in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (67.7.2)
Requirement already satisfied: jedi>=0.16 in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.19.1)
Requirement already satisfied: decorator in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (4.4.2)
Requirement already satisfied: pickleshare in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.7.5)
Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (5.7.1)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (3.0.43)
Requirement already satisfied: pygments in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (2.16.1)
Requirement already satisfied: backcall in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.2.0)
Requirement already satisfied: matplotlib-inline in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (0.1.6)
Requirement already satisfied: pexpect>4.3 in /usr/local/lib/python3.10/dist-packages (from ipython>=1.0->ipython-sql==0.4.1) (4.9.0)
Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from sqlalchemy>=0.6.7->ipython-sql==0.4.1) (3.0.2)
Requirement already satisfied: parso<0.9.0,>=0.8.3 in /usr/local/lib/python3.10/dist-packages (from jedi>=0.16->ipython>=1.0->ipython-sql==0.4.1) (0.8.3)
Requirement already satisfied: ptyprocess>=0.5 in /usr/local/lib/python3.10/dist-packages (from pexpect>4.3->ipython>=1.0->ipython-sql==0.4.1) (0.7.0)
Requirement already satisfied: wcwidth in /usr/local/lib/python3.10/dist-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql==0.4.1) (0.2.12)
WARNING: Ignoring invalid distribution -qlalchemy (/usr/local/lib/python3.10/dist-packages)
WARNING: Ignoring invalid distribution -qlalchemy (/usr/local/lib/python3.10/dist-packages)
Requirement already satisfied: SQLAlchemy==1.4.49 in /usr/local/lib/python3.10/dist-packages (1.4.49)
Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.10/dist-packages (from SQLAlchemy==1.4.49) (3.0.2)
WARNING: Ignoring invalid distribution -qlalchemy (/usr/local/lib/python3.10/dist-packages)

ID와 PW와 (본인스키마)를 자신의 것으로 변경

In [4]:

%sql postgresql://wearealego:Wearealego!1@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev

In [5]:

%%sql SELECT schema_name
FROM information_schema.schemata;

 * postgresql://wearealego:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
1 rows affected.

Out[5]:

schema_name
wearealego

In [ ]:

%%sql SELECT tablename
FROM pg_tables
WHERE schemaname = 'wearealego';

 * postgresql://wearealego:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
1 rows affected.

Out[ ]:

tablename
name_gender

In [6]:

%%sql
drop table if exists wearealego.name_gender;
create table wearealego.name_gender(
  name varchar(32) primary key,
  gender varchar(8)
);

 * postgresql://wearealego:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
Done.
Done.

Out[6]:

[]

In [7]:

import psycopg2

# Redshift connection 함수
# 본인 ID/PW 사용!
def get_Redshift_connection():
    host = "learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com"
    redshift_user = "wearealego"
    redshift_pass = "Wearealego!1"
    port = 5439
    dbname = "dev"
    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)
    return conn.cursor()

ETL 함수를 하나씩 정의¶

In [8]:

import requests

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

In [9]:

def transform(text):
    #[1:]로 헤더 제외
    lines = text.strip().split("\n")[1:]
    records = []
    for l in lines:
      # l = "gwanghyeon,M" -> [ 'gwanghyeon', 'M' ]
      (name, gender) = l.split(",")
      records.append([name, gender])
    return records

In [26]:

def load(records):
    """
    records = [
      [ "gwanghyeon", "M" ],
      [ "Claire", "F" ],
      ...
    ]
    """

    schema='wearealego'
    # 문제는 실행시마다 값이 달라짐 그래서 수정해야함
    # BEGIN과 END를 사용해서 SQL 결과를 트랜잭션으로 만들어주는 것이 좋음
    cur = get_Redshift_connection()
    cur.execute("BEGIN;")

    # DELETE 문을 사용하여 테이블의 기존 데이터 삭제
    cur.execute(f"DELETE FROM {schema}.name_gender;")
    try:
      # DELETE FROM을 먼저 수행 -> FULL REFRESH을 하는 형태
      for r in records:
          name = r[0]
          gender = r[1]
          print(name, "-", gender)
          sql = f"INSERT INTO {schema}.name_gender VALUES ('{name}', '{gender}')"
          cur.execute(sql)
      # COMMIT 트랜잭션 완료, cur.execute("END;")도 같은기능
      cur.execute("COMMIT;")
    except (Exception, psycopg2.DatabaseError) as error:
      print(error)
      cur.execute("rollback;")

이제 Extract부터 함수를 하나씩 실행¶

In [27]:

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

data = extract(link)

In [28]:

data

Out[28]:

'name,gender\nAdaleigh,F\nAmryn,Unisex\nApurva,Unisex\nAryion,M\nAlixia,F\nAlyssarose,F\nArvell,M\nAibel,M\nAtiyyah,F\nAdlie,F\nAnyely,F\nAamoni,F\nAhman,M\nArlane,F\nArmoney,F\nAtzhiry,F\nAntonette,F\nAkeelah,F\nAbdikadir,M\nArinze,M\nArshaun,M\nAlexandro,M\nAyriauna,F\nAqib,M\nAlleya,F\nAavah,F\nAnesti,Unisex\nAdalaide,F\nAnalena,F\nAlaeyah,F\nAlbena,F\nAimi,F\nAdwaith,M\nArkady,M\nAstyn,Unisex\nAdelee,F\nAgata,F\nAlegna,F\nAltan,M\nAhnaleigh,F\nAlgie,Unisex\nAshanti,F\nAislyn,F\nAdaleine,F\nAnthnoy,M\nAlgernon,M\nAeryona,F\nAdrinne,F\nAddell,F\nAvril,F\nAhni,F\nAimon,M\nAdolpho,M\nAhuva,F\nAurielle,F\nAveana,F\nAliyia,F\nAlesander,M\nAdnrea,F\nAnjae,F\nAlvine,F\nAdorah,F\nAdlemi,F\nAlesi,F\nAlontae,M\nAntonny,M\nAdarah,F\nAyreanna,F\nAntyon,M\nAndia,F\nAshla,F\nAspyn,F\nAntwanett,F\nAundreia,F\nAudella,F\nAmari,Unisex\nArsha,Unisex\nAricella,F\nAdan,M\nApasra,F\nAlaysha,F\nAnderson,M\nAurelius,M\nAerial,F\nAverleigh,F\nAslean,F\nArniesha,F\nAsyana,F\nAnnjane,F\nAmabella,F\nAustinjohn,M\nArloween,F\nAlula,M\nAnemone,F\nAmorina,F\nAnureet,F\nArric,M\nAntonne,M\nAlyre,M\nAnnaise,F\n'

In [29]:

lines = transform(data)

In [30]:

lines[0:10]

Out[30]:

[['Adaleigh', 'F'],
 ['Amryn', 'Unisex'],
 ['Apurva', 'Unisex'],
 ['Aryion', 'M'],
 ['Alixia', 'F'],
 ['Alyssarose', 'F'],
 ['Arvell', 'M'],
 ['Aibel', 'M'],
 ['Atiyyah', 'F'],
 ['Adlie', 'F']]

In [31]:

load(lines)

Adaleigh - F
Amryn - Unisex
Apurva - Unisex
Aryion - M
Alixia - F
Alyssarose - F
Arvell - M
Aibel - M
Atiyyah - F
Adlie - F
Anyely - F
Aamoni - F
Ahman - M
Arlane - F
Armoney - F
Atzhiry - F
Antonette - F
Akeelah - F
Abdikadir - M
Arinze - M
Arshaun - M
Alexandro - M
Ayriauna - F
Aqib - M
Alleya - F
Aavah - F
Anesti - Unisex
Adalaide - F
Analena - F
Alaeyah - F
Albena - F
Aimi - F
Adwaith - M
Arkady - M
Astyn - Unisex
Adelee - F
Agata - F
Alegna - F
Altan - M
Ahnaleigh - F
Algie - Unisex
Ashanti - F
Aislyn - F
Adaleine - F
Anthnoy - M
Algernon - M
Aeryona - F
Adrinne - F
Addell - F
Avril - F
Ahni - F
Aimon - M
Adolpho - M
Ahuva - F
Aurielle - F
Aveana - F
Aliyia - F
Alesander - M
Adnrea - F
Anjae - F
Alvine - F
Adorah - F
Adlemi - F
Alesi - F
Alontae - M
Antonny - M
Adarah - F
Ayreanna - F
Antyon - M
Andia - F
Ashla - F
Aspyn - F
Antwanett - F
Aundreia - F
Audella - F
Amari - Unisex
Arsha - Unisex
Aricella - F
Adan - M
Apasra - F
Alaysha - F
Anderson - M
Aurelius - M
Aerial - F
Averleigh - F
Aslean - F
Arniesha - F
Asyana - F
Annjane - F
Amabella - F
Austinjohn - M
Arloween - F
Alula - M
Anemone - F
Amorina - F
Anureet - F
Arric - M
Antonne - M
Alyre - M
Annaise - F

In [32]:

%%sql

SELECT COUNT(1)
FROM wearealego.name_gender;

 * postgresql://wearealego:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
1 rows affected.

Out[32]:

count
100

In [33]:

%%sql
SELECT *
FROM wearealego.name_gender;

 * postgresql://wearealego:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
100 rows affected.

Out[33]:

name	gender
Adaleigh	F
Amryn	Unisex
Apurva	Unisex
Aryion	M
Alixia	F
Alyssarose	F
Arvell	M
Aibel	M
Atiyyah	F
Adlie	F
Anyely	F
Aamoni	F
Ahman	M
Arlane	F
Armoney	F
Atzhiry	F
Antonette	F
Akeelah	F
Abdikadir	M
Arinze	M
Arshaun	M
Alexandro	M
Ayriauna	F
Aqib	M
Alleya	F
Aavah	F
Anesti	Unisex
Adalaide	F
Analena	F
Alaeyah	F
Albena	F
Aimi	F
Adwaith	M
Arkady	M
Astyn	Unisex
Adelee	F
Agata	F
Alegna	F
Altan	M
Ahnaleigh	F
Algie	Unisex
Ashanti	F
Aislyn	F
Adaleine	F
Anthnoy	M
Algernon	M
Aeryona	F
Adrinne	F
Addell	F
Avril	F
Ahni	F
Aimon	M
Adolpho	M
Ahuva	F
Aurielle	F
Aveana	F
Aliyia	F
Alesander	M
Adnrea	F
Anjae	F
Alvine	F
Adorah	F
Adlemi	F
Alesi	F
Alontae	M
Antonny	M
Adarah	F
Ayreanna	F
Antyon	M
Andia	F
Ashla	F
Aspyn	F
Antwanett	F
Aundreia	F
Audella	F
Amari	Unisex
Arsha	Unisex
Aricella	F
Adan	M
Apasra	F
Alaysha	F
Anderson	M
Aurelius	M
Aerial	F
Averleigh	F
Aslean	F
Arniesha	F
Asyana	F
Annjane	F
Amabella	F
Austinjohn	M
Arloween	F
Alula	M
Anemone	F
Amorina	F
Anureet	F
Arric	M
Antonne	M
Alyre	M
Annaise	F

In [34]:

%%sql

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

 * postgresql://wearealego:***@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
3 rows affected.

Out[34]:

gender	count
F	65
Unisex	7
M	28