ETL - Extract Transform Load
DB 이해
데이터베이스는 ‘정형’ 데이터
-컬럼과 값이 매치되는 데이터
데이터 레이크 (SPARK-SCALA)
- 정형, 반정형, 비정형을 가리지 않고 모든 형태의 데이터를 원천 시스템에서 발생한 형태 그대로 수집한다. (가공되지 않은)
-모든 형태의 데이터를 처리하는데 필요한 성능을 충분히 보유하고 있으며, 활용할 수 있는 분석 결과
-보통 기업 운영과 관련된 정보, 또는 접근할 가능성이 높은 정보를 보유하고 있다.데이터 웨어하우스(PYTHON)
-목적에 맞게 정의된 고정형 모델로 정형 데이터를 수집하고 처리..
-가공된 데이터
-장시간 데이터를 저장 유지, 필요 시에만 데이터 참조데이터 기반 아키텍처
1.SOURCE
More Structured : Stream, Web, Svc, DB, Files,,
Less Structured: doc, pdf, sml, image, json,,
2.INGEST (불러오기)
Intelligence Engine
Data Integration
Content Intelligence
데이터 수집 및 저장:
데이터 베이스가 다른경우: 중간에 분석 서버(Python) 던져줌
다양한 형태의 데이터를 파이썬에서 분석/가공 후 원하는 곳에 저장 가능함
DB란 조직에 필요한 정보로 구성된 연관 데이터 집합체 -> it 가 있으면 데이터가 있다.
Pandas 라이브러리 활용한 데이터 수집 & 저장
파일 데이터:
xlsx or csv
csv - 데이터를 무한대로 저장 가능
각각 컬럼 타입 설정
Column description
PK 명확하게 설정
상대경로 접근
현재 폴더./
상위 폴더 이동 시 ../
상위 폴더의 상위폴더 이동 시 ../../
절대경로 접근
ex) D:/devcode/Session13/aa.csv
데이터 불러오기:
pd.read_csv(“파일경로”, 추가옵션)
pd.read_excel(“파일경로”, 추가옵션)
데이터 저장하기
pd.to_csv(“저장경로”, 추가 옵션)
pd.to_excel(“저장경로”, 추가 옵션)
데이터 저장하기 주요옵션: index
import pandas as pd
indata = pd.read_csv("../dataset/customerdata.csv")
indata.to_csv("./outData/csv", index=False) ← 인덱스는 기본으로 false
데이터 불러오기 주요옵션:
sep
pd.read_csv(“,”)
encoding
data = pd.read_csv("../dataset/kopo_region_mst_hangul.csv", encoding="ms949")
저장할때 동일
data.to_csv(“./hangul.csv”,encoding=”ms949”,index=False)
skiprows
무시할 행 개수
data.rename( columns={"SALESID":"SID",
"REGIONNAME":"SNAME"},
inplace=True)
inplace = 메모리를 최적화하기 위해 사용, 즉시 데이터에 적용됨
데이터베이스 데이터수집:
import pandas as pd #활용 라이브러리 (pasdas)
Import psycopg2 #PostgresSQL 접속을 위한 라이브러리
From sqlalchemy import creat_engine #데이터 베이스 엔진 생성
engine = create_engine(“postgresql://postgres:postgres@127.0.0.1:5432/postgres”)
id : pw @ ip주소:port/db이름
pd.read_sql_query(“조회쿼리”,engine)
ex) select * from kopo_product_volume
DB 연동 패키지: pip install psycopg2, pip install sqlAlchemy
DB 보안설정/접근
방화벽 열기:
제어판>>> 방화벽 >> windows defender 방화벽 >> 고급설정: >> 인바운드 규칙 : 안으로 들어오는 포트를 열어주어야 한다 >> 새 규칙: 포트 허용
ip 접근 관리:
설치 폴더 >> C:\Program Files\PostgreSQL\10\data >> pg_hba.conf>> 메모장 >> 맨 마지막 >>> host all all 0.0.0.0/0 md5
->검색 -> 서비스 -> postgresql-x64-10 -> 다시시작
try:
pd.read_sql(sql="select * from pg_out", con=engine_pg)
print(192.168.110.23", connect succeed"+)
except
import pandas as pd
import psycopg2
from sqlalchemy import create_engine
JaeminpgUrl = "postgresql://postgres:postgres@ip주소:포트번호/postgres"
engine_pg = create_engine(JaeminpgUrl)
pd.read_sql(sql="select * from pg_out", con=engine_pg)
targetData = pd.read_csv("./hangul.csv", encoding="ms949")
targetData.to_sql(name="carData",
con=engine_pg,
if_exists="replace", index=False)
데이터프레임 : R, Scala, Python
→ 행과 열이 있는 스프레드 시트
indata = \
pd.read_csv("../dataset/kopo_decision_tree_all_new.csv")
indata.shape
indata.columns
컬럼 소문자 변경: indata.columns = indata.columns.str.lower()
etlProcess.py 생성
D: 폴더 생성 >> dataset, etlProject 폴더 >> 파일 옮김 >> 커맨드 >> 경로에서 python etlProcess.py 실행 >>>
파이썬 프로그램 자동화
실행파일 생성 및 테스트 자동화
윈도우: 메모장 열기 →
d:
cd D:\pyexe\etlProject
python etlProcess.py
pause
→ .bat
제어판 >> 시스템및 보안 >> 관리도구>> 작업 스케줄러>> 기본 작업 만들기
프로그램/스크립트 : C:\Users\SMART-09\anaconda3\python.exe
인수 추가: etlProcess.py
시작 위치: D:\pyexe\etlProject
bat:
프로그램/스크립트: bat 경로
import d6tstack : 2018년 6월 출시, Pandas 성능 향상 패키지
purl = ‘postgresql+psycopg2://postgres:postgres@127.0.0.1:5432/postgres’
d6tstack.utils.pd_to_psql(df=indata, uri=purl, table_name=tableName, if_exists=’replace’, )
df: dataframe
uri: url 주소
