2. airflow-day5-2

학습주제
MySQL테이블 복사하기

학습내용

OLTP -> OLAP 로 옮겨보자
프로덕션 DB로 Mysql, postgres

nps라는 테이블을
dw 테이블로 복사해본다.

prod라는 데이터 베이스를
각자 스키마 밑에 nps

풀리프레쉬 버전
인크리멘탈 버전

인크리멘탈은 엑시큐션 date을 써서 해보고, 백필도 실습

1. mysql 읽어다가 하나씩 INSERT INTO. 단순한 방법. 만일 레코드 수가 많으면 INSERT INTO 하다 시간 다 감. 레코드가 몇개 없을 경우 선호됨.
2. 레코드가 많을 경우 COPY 커맨드 선호. 스탭이 몇가지 추가됨. 데이터 소스 (nps)를 다 읽어와 파일로 저장해서 아마존 S3에 업로드를 하고, 한번에 벌크로 복제를 시도함.

• 소스에서 데이터를 카피해서 파일로 저장
• S3에 파일을 로딩함. 버킷이 있어야함. greep-data-engineering
• DW에 벌크로 업데이트 함.
  커넥션들이 더 필요해짐. 보안세팅 필요 S3에 대해서. 레드쉬프트가 엑세스할 수 있어야 함.
  에어플로우 쪽에서 mysql에 대한 커넥션이 있어야 함.

1. mysql
2. s3

• 에어플로우가 s3에 엑세스 가능해야함
• 레드쉬프트가 s3에 엑세스 가능해야함

결국 AWS에 대한 복습.
기술이 돌아가는 환경. 클라우드에 대한 지식이 필요함. IAM 같은 것들.

ec2 서버에 mysql 설치해둔 상황.
저 host는 변경될 수도 있음 슬랙 공지 참고.

에어플로그 대그에서 s3 접근 권한 . 쓰기권한 필요
이 용도의 전용 IAM 유저를 만들고 S3 접근권한을 만들고 access key, secret key 부여

• 이미 만들어 놓으심.

레드쉬프트가 S3에 대해 읽기 권한
IAM role 만들고 Redshift에 지정
이것도 미리 역할 부여됨.

새로 커넥션 세팅


테스트 성공 후 저장.

세팅이 문제 없어도
데모 프로그램 실행시켜주면
다음 에러가 뜨는 경우 있음
언제 데모를 실행하냐에 따라 다르긴 하나.
스케줄러에 루트로 로그인해서 실행시켜줘야 함.
root 커맨드로 로그인

순차적으로 명령어 실행하니 잘 설치됨.

• 질문: 스케줄러만 저렇게 모듈을 설치해도 잘 돌아가나보다. 웹 UI나 워커 등은?
  이로써 아래 에러를 해결하였음.
  23년 5월 기준이고 다른 문제는 나중에 또 뜰 수 있음.

호스트 이름은 변경이 없는 듯 함. 일단 연결에 성공했음.
이렇게 connection 으로 설정해두면
mysql_conn_id로 접근만으로 사용 가능.

이걸 파일로 저장해 s3에 업로드 해야함.
S3에 접근할 권한이 있어야함.
별도의 전용 사용자를 만들고(IAM), 만들어놨던 해당 버킷의 읽기/쓰기 권한 부여
두 키 페어가 있는데 이걸 사용할 예정.

예전엔 루트 유저의 엑세스 키, 시크릿 키를 사용하곤 했는데, 이게 유출되면 모든 AWS 자원에 접근 가능해짐. 그 사용자의 키도 주기적으로 바꿔줘 노출시 위험을 최소화 함.

IAM에 들어가 Users - add users
airflow-s3-access로 생성.
체크표시는 웹 UI에 접근할 수 있는지.

attach policies 선택
그냥 검색에서 S3 full Access 줘도 가능하긴하나 안전하지 않음. 키가 노출되면 s3 보든 정보에 접근, 버킷도 생성 가능함.
우리는 1번 방법인 정책을 만들어 볼 것임.

JSON 형태로 내가 줄 수 있는 권한을 선택할 수 있음.
복붙하다 쓰다보면 깨닫는 순간이 옴.
Effect Allow
해당 버킷에 대한 권한을 허용하겠다 라고 만들었음.
정책도 이름을 따로 만들수 있음.
이 정책을 iam 유저에게 달아줌

유저를 선택하면 아래 내가 만들어둔 정책이 달려있는 것을 볼 수 있음
security credentials에 들어가
좀 내리다보면
create access key를 눌러 두개의 키를 다운로드 받을 수 있음.
하나는 access key id, secret id
시크릿은 한번 보여주면 다시는 안보여줌.
시크릿이 생성했는데 삭제 안나면, 키페어 삭제하고, 다시 create access key

두개의 키를 다운로드 받은 뒤에 s3 커넥션 때 사용할 예정.
이미 만들어진 고정비용 레드쉬프트가 있기 때문에 권한 만들수도 없음 지금은.

에어플로우로 돌아와
다시 커넥션을 생성

Amazon generics를 선택할 수도 있는데
도커를 설치한 상황, ec2 세팅이라면 저게 보일 것임.
키도 카피했던거 가져도면 됨.

엑스트라에 해당 값을 꼭 복붙해서 넣어줘야 함.
버킷이 어느 지역에 있는지 찾아갈 수 있어야 함.

타입이 저렇게도 나올 수 있음.
두개의 커넥션을 만들고 권한 세팅 하나 함.
하나 남음: 레드쉬프트가 S3를 읽을 수 있는 권한.

mysql이 어떤 모양인지 본다.
nps 설문. 0~10점까지 줄 수 있는 만족도 설문지. 이 프로덕트를 얼마나 친구나 가족에게 추천할 것인지. 이걸 가지고 nps 점수를 계산.
mysql 서버에 있음.

타켓테이블(목적)
OLAP 각자 스키마 밑에 nps 테이블 생성.
auto_increment가 없음.
매일 전날 데이터를 읽어다 레드쉬프트 단에 복제하고 중복처리, UPSERT 방식을 사용해 볼 수도 있음. 이번엔 UPSERT 써봄