01. Athena WorkGroup생성하기
<S3 버킷 생성하기>
1. Athena에서 실행한 쿼리의 결과는 S3에 적재됩니다. 워크 그룹 생성 이전에 S3 버킷을 생성해주었습니다.
<작업 그룹 생성하기>
1. Athena 작업 그룹을 생성하여 쿼리 편집기에서 분석을 하려고 합니다. 워크그룹내에서 쿼리당 데이터 사용량 컨트롤 등이 가능합니다.
2. 작업그룹 이름, 설명등을 입력하고 작업 그룹을 생성합니다.
3. 쿼리 결과 구성에서 위에서 생성한 S3버킷 위치를 설정합니다.
4. 태그를 구성합니다.
5. 쿼리 편집기에서 작업그룹을 생성해준 작업그룹으로 선택하고 쿼리로 분석합니다.
02. Athena에서 분석하기
1. 앞에서 json 테이블을 크롤링해서 위와 같이 array 데이터 형식이 있는 컬럼이 존재하는 테이블을 생성하였습니다. list array는 struct구조로 되어있는 데이터를 쿼리해봅니다.
2. 쿼리편집기에서 쿼리를 통해 조회된 list컬럼은 다음과 같이 보입니다.
3. 해당 array컬럼을 UNNEST 문법구문을 사용하여 개별요소로 지정하여 쿼리할 수 있습니다.
* 표준 SQL을 사용하여 S3에 있는 데이터를 직접 간편하게 분석가능하며 다양한 쿼리 실행 방법에 대해 AWS에서 잘 안내하고 있으니 참고하시기 바랍니다.
https://docs.aws.amazon.com/ko_kr/athena/latest/ug/querying-athena-tables.html
03. RDS Mysql 생성하기
1. RDS 콘솔에서 데이터베이스 생성버튼을 클릭합니다.
2. 데이터베이스 생성 방식에서 표준 생성을 선택합니다.
3. 엔진옵션을 MySQL을 선택합니다.
4. DB 인스턴스 명, 자격증명등을 설정합니다.
5. 인스턴스를 구성합니다. 스탠다드 클래스로 설정하였습니다.
6. 네트워크 연결 설정을 합니다. 퍼블릭 엑세스를 예를 선택합니다.
7. VPC를 이미 구성해 놓은 공인망 연결 가능한 VPC로 선택합니다.
8. 스토리지 유형은 범용으로 선택하였습니다. 집계 데이터만 적재할 용도입니다.
9. 3306 기본 포트로 설정하고, 암호인증으로만 인증옵션을 설정하였습니다.
10. DB 가 생성되었고 사용가능 상태를 확인하고 엔드포인트를 확인합니다.
11. 엔드포인트로 설정해놓은 자격증명으로 Mysql 클라이언트 툴(DBever)로 연결합니다. 성공하였습니다.
04. MySQL 테이블 스키마 생성하기
1. DB 클라이언트 툴(DBeaver)에서 데이터베이스를 먼저 생성합니다.
2. data_ai라는 데이터베이스를 생성하였습니다.
3. 생성하려는 테이블 스키마 DDL문 입니다. DB 클라이언트툴(DBeaver)의 SQL 편집기에서 실행합니다.
4. 테이블이 생성되었습니다.
05. Athena 쿼리를 RDS에 적재하는 배치프로그램 작성하기
<Pyathena로 Athena에 쿼리 결과 읽기>
1. pyathena를 쓰면 pandas와 함께 써서 read_sql등 함수로 편하게 쿼리 결과를 읽어올 수 있습니다.
그외에 boto3, wrangler 방식이 있습니다. boto3는 기본기능만 사용할때 쓰기 쉽고, 프로그래밍 방식으로 다양하게 데이터를 읽고 쓰기를 위한다면 wrangler 방식을 도입하는 것을 권장합니다. poc과정을 준비하면서 Pyathena로 간단하게 읽는 방식을 구현하였습니다.
<sqlalchemy로 Mysql에 데이터 insert하기>
1. sqlalchemy 라이브러리를 통하여 python 코드와 mysql을 연결하여 사용하였습니다. Bulk insert시 pandas의 to_sql로 데이터프레임을 통째로 올리는 방법이 있고 속도가 매우 빠릅니다.
* 위에 구현한 라이브러리들을 통해 Athena 분석 쿼리 결과 RDS 에 적재되도록 구성해 놓은 배치 스크립트를 EC2에 올려 crontab에서 스케줄링합니다.
2. EC2에 배치 스크립트 크론탭으로 스케줄링 설정한 정보입니다.
* Bulk API를 0시 5분에, Glue ETL을 통한 파일변환을 01시에 적재 데이터를 그 데이터를 기준으로 집계하는 배치는 2시 5분에 스케줄링하였습니다.